View that provides SCD Type2 with only one datetime field

Basically, if you keep your history just exposing the datetime when the record was inserted or updated, it is possible to get a view of your table in the shape of a Slowly Change Dimension Type 2. The way to achieve it is to mention your LoadDateTime as StartDate and then create a partition over the business key to identify the next LoadDateTime, and if there is no more, expose year 9999 or 2100 (no end). The following code will explain that in a real scenario. Note: ignore the audit fields that I used to create in the History Staging Area, which I will explain in my next post.

CREATE VIEW [hstg].[vw_Table_History]
AS
SELECT
ETL_EXECUTION_DT AS START_DATE,
ISNULL(lead (ETL_EXECUTION_DT) OVER (PARTITION BY  BusinessKey ORDER BY ETL_EXECUTION_DT), ‘9999-12-31 00:00:00.0000000’) AS END_DATE,
CASE WHEN lead (ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT) IS NULL THEN ‘Y’  ELSE ‘N’ END AS [CURRENT_RECORD],
[BusinessKey],
[Field1],
[Field2],

ETL_BATCH_DT AS ETL_ROW_START_DT,
S.ETL_BATCH_ROW_ID,
CONVERT (DATETIME, ‘99991231’) AS ETL_ROW_END_DT,
ETL_ROW_HASH,
CONVERT (NVARCHAR (512), ‘AdvWorks’) AS ETL_ROW_SOURCE,
ETL_CDC_OPERATION,
ETL_EXECUTION_DT
FROM [hstg].[Table] S


Did you like this? Share it:

Leave a Reply

*