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.

USE ODS

DROP TABLE IF EXISTS YourSchemaName.YourStageTableNameData
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE YourSchemaName.YourStageTableNameData(
[ETLKey] [uniqueidentifier] NOT NULL,
[UniqueDims] [varbinary](35) NULL,
[UniqueRows] [varbinary](16) NULL,
[SourceSystem] [nvarchar](255) NULL,
[Cleansed] [bit] NULL,
[ErrorRecord] [bit] NULL,
[ErrorReason] [nvarchar](255) NULL,
[Processed] [bit] NULL,
[RunDate] [datetime] NULL,
 CONSTRAINT [PK_YourStageTableNameData] PRIMARY KEY CLUSTERED 
(
       [ETLKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD  CONSTRAINT [DF_YourStageTableNameData_ETLKey]  DEFAULT (newid()) FOR [ETLKey]
GO

ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD  CONSTRAINT [DF_YourStageTableNameData_SourceSystem]  DEFAULT (N'DatabaseName') FOR [SourceSystem]
GO

ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD  CONSTRAINT [DF_YourStageTableNameData_Cleansed]  DEFAULT ((0)) FOR [Cleansed]
GO

ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD  CONSTRAINT [DF_YourStageTableNameData_ErrorRecord]  DEFAULT ((0)) FOR [ErrorRecord]
GO

ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD  CONSTRAINT [DF_YourStageTableNameData_Processed]  DEFAULT ((0)) FOR [Processed]
GO

ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD  CONSTRAINT [DF_YourStageTableNameData_RunDate]  DEFAULT (getdate()) FOR [RunDate]
GO

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.