The need for a Data Dictionary in Data Migration
At a bare minimum the data dictionary needs to contain the following attributes
Source Data Type
Target Data Type.
optionally adding fields to the data dictionary for
This can be implemented in many ways – depending partly on how public the data needs to be – for example, it can be stored as an excel workbook, an access database or in SQL server itself (possibly with an access frontend to allow simple updates to the underlying data)
It’s advisable to make the data dictionary publicly available – for example by publishing a report on the data dictionary or by making the access / excel data available.
In all cases, I would recommend change tracking – which lends itself more to the SQL server stored data even if it’s then made visible via Excel as a linked source to all relevant SME’s.
Common transformation rules could include
Cast as datatype
format datetime 112