Mapping Tables and Columns
Place mapping tables in the schema: Map
This makes it easy to distinguish them from dimension tables which might have similar names
Name mapping tables [Dimension]_[Source System]
By grouping the maps by the dimension they map to, it becomes easy to skim for all maps for a single dimension
Name the tables: [Source System]_[Dimension]
While this allows grouping by source system easily, it is not task oriented. Mapping tables are used by ETL developers when loading dimension and facts. When doing so, what you know is what dimension you need to map – so it is convenient to organise the maps by that.
The following columns always appear in maps
| Column | Purpose | Data Type | Default Value | NULL'able |
|---|---|---|---|---|
| EK_[Dimension] | The entity key in the dimension table that is mapped. Forms a key together with ValidFrom | 4 byte or 8 byte integer | None | No |
| ValidFrom | The first time this instance of the key appeared | Date (optionally time) | Current Date/Time (UTC) | No |
| ValidTo | The time this key is no longer valid | Date (optionally time) | 9999-12-31 | No |
| IsCurrent | Easy way to locate the currently active may | 1 byte integer | 1 | No |
| [Source Key] | The key in the source system. Can be multiple columns. Use the same naming as in the source system. Forms a key together with ValidFrom | The same as in source | None | No |
