Designing a Slowly Changing Dimension (SCD) in Azure Data Factory using SQL Server Temporal Tables

Temporal tables were introduced as a new feature in SQL Server 2016. Temporal tables also known as system-versioned tables are available in both SQL Server and Azure SQL databases. Temporal tables automatically track the history of the data in the table allowing users insight into the lifecycle of the data.

Traditionally, data warehouse developers created Slowly Changing Dimensions (SCD) by writing stored procedures or a Change Data Capture (CDC) mechanism. Temporal tables enable us to design an SCD and data audit strategy with very little programming. Temporal tables store the data in combination with a time context so that it can easily be analyzed for a specific time period.

Use Cases of Temporal Tables

Slowly changing dimensions – Temporal tables follow a Type 2 SCD which keep a history of dimension table value changes in the database.
Data audit – System-versioned temporal tables help audit all data changes throughout the dimension’s lifetime and enable detailed auditing and reporting on the changes.
Time travel – They allow us to travel across time periods to analyze the state of the data at any time and to get insights into trends over time.
Repair record-level corruption – Act as a backup mechanism to restore data from the history table without any loss of data.

Designing a Slowly Changing Dimension (SCD) in Azure Data Factory using SQL Server Temporal Tables

Temporal Table Creation

We can either create a new temporal table or convert an existing table into a temporal table by following the steps outlined below.

Creating a new Temporal Table

When a temporal table is created in the database, it will automatically create a history table in the same database, to capture the historical records. We can specify the name of the history table at the time of temporal table creation. If not, it is created with the naming convention CUST _TemporalHistoryFor_xxx.

Syntax:

Active records reside in the CustTemporal Table:

Historical records (Deleted, Modified) will be captured in the history table CustHistoryTemporal:

Key points to note before creating the temporal table (refer highlighted areas in the syntax)

A temporal table must contain one primary key.
The period for system time must be declared with proper valid to and from fields with datetime2 datatype.
System Versioning should be set to ON.
If you are specific about the name of the history table, mention it in the syntax, else the default naming convention will be used.
Other optional parameters like data consistency check, retention period etc can be defined in the syntax if needed.
The history table is page compressed.

The history table cannot have any table constraints. Indexes or Statistics can be created for performance optimization.

Converting an existing table to a Temporal Table

Converting an existing table to a temporal table can be done by setting SYSTEM_VERSIONING to ON, on the existing table. Enabling DATA_CONSISTENCY_CHECK enforces data consistency checks on the existing data. Given below are the steps to be followed for the conversion.

Define a primary key on the table, if not defined earlier
Add Valid To and Valid From time period columns to the table
Alter Valid To and Valid From time period columns to add NOT NULL constraint
Declare System Period column
Enable System Versioning on the table

Define Primary Key on the existing table:

Add Valid To and Valid From time period columns to the table:

Add NOT NULL constraint:

Declare System Period column:

Enable System Versioning on the table:

Changing Schema or Dropping the Temporal Table

Schema changes or dropping the temporal table is possible only after setting System Versioning to OFF.

Loading data into a Temporal Table from Azure Data Factory

Copy activity in Azure Data Factory has a limitation with loading data directly into temporal tables. So, we would need to create a stored procedure so that copy to the temporal table works properly, with history preserved. Given below is a sample procedure to load data into a temporal table.

Retention Policy

Temporal Tables may increase database size more than regular tables, due to retaining of historical data for longer periods or due to constant data modification. Hence, the retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table. If a retention policy is defined, Azure SQL database checks routinely for historical rows that are eligible for automatic data clean-up.