Sample Stage Table
To make life easy, all audit columns in stage tables have default values. The only thing you need to change is the default value for SourceSystem.
Column Name
Data Type
Function
ETLKey
uniqueidentifier
The primary key of staging tables uses a GUID as a key as opposed to an auto incrementing number. Using a GUID ensures uniqueness across all stage tables and comes in very handy when tracking the provenance of records. There can be no mistake that a certain record came from a certain table.
UniqueDims
varbinary(35)
The primary key of a fact table is the unique combination of all the dimensions attached to that fact table including any degenerate dimensions.
The values of the foreign keys and degenerate dimensions can be hashed to provide a single atomic value for efficiently identifying a specific row in a fact table. Used in combination with ETLKey, this column is used to determine if a record in a stage table made it to the fact table ok.
UniqueRows
varbinary(16)
There are rare instances that arise, usually as a result of a quirk of the source system, where uniqueness can't be determined by the dimensions of the measures and the measures themselves have to be involved in the determination of uniqueness.
In those cases, UniqueRows holds a hash of those measures that are either required to determine uniqueness or used to detect change in a value.
When this happens, UniqueDims and UniqueRows is used to determine if a record made it to the fact table ok.
SourceSystem
nvarchar(255)
The system where the data came from. This value should be short, plain, and obvious. An example would be BankOfAmerica. You want to Pascal case it for when the value winds up in code as is the case when you have master data and need to filter on source system.
Cleansed
bit
This is a binary value that indicates whether or not the data set has been cleansed.
ErrorRecord
bit
This is a binary value that indicates whether or not this record errored out.
ErrorReason
nvarchar(255)
If a record errors, you can use this column to provide diagnostic information.
Processed
bit
This is a binary value that indicates whether or not this record made it to the fact table.
RunDate
datetime
The time stamp the record was loaded.
Last updated