In traditional data modeling, we usually add "created date", "last modified date", "created by" and "last modified by" columns in all tables to capture the audit information. In dimensional data modeling, such design may still be ok for the dimension table since they are usually short. However, it could be a problem if we manage our fact table in this way. It is why the audit dimension is introduced. Fact table contains millions of records or even more. These columns share the same values in all the rows from the same batch of data loading. So, we could have a better design to consolidate the audit information by audit dimension.
The audit dimension may look like:
- Audit Key (Audit Surrogate key)
- Job Name
- Job Version
- Batch ID
- Load Date
- Source File Name
- Number of processed records
- Status
- Start Time
- End Time
- Executed by
No comments:
Post a Comment