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

ColumnPurposeData TypeDefault ValueNULL'able
EK_[Dimension]The entity key in the dimension table that is mapped. Forms a key together with ValidFrom4 byte or 8 byte integerNoneNo
ValidFromThe first time this instance of the key appearedDate (optionally time)Current Date/Time (UTC)No
ValidToThe time this key is no longer validDate (optionally time)9999-12-31No
IsCurrentEasy way to locate the currently active may1 byte integer1No
[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 ValidFromThe same as in sourceNoneNo