Slowly Changing Dimensions (SCD) in Azure Synapse Analytics

Slowly changing dimensions (SCD) are tables in a dimensional model that handle changes to dimension values over time.

Describe slowly changing dimensions

A slowly changing dimension (SCD) is one that appropriately manages the change of dimension members over time.

It applies when the values of a business entity change over time, and not on a set schedule.

The common design approach in these instances is to store rapidly changing attribute values in a fact table measure.

When handling data changes, the table design varies depending on if you choose to update values without history or to track each version of history.

· Type 1 SCD

o Always reflects the latest values, and when changes in source data are detected, the dimension table data is overwritten.

· Type 2 SCD

o Supports versioning of dimension members.

o In this case, the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member.

o It also includes columns that define the date range validity of the version (for example, StartDate and EndDate) and possibly a flag column (for example, IsCurrent) to easily filter by current dimension members.

· Type 3 SCD

o Supports storing two versions of a dimension member as separate columns.

o The table includes a column for the current value of a member plus either the original or previous value of the member.

o So Type 3 uses additional columns to track one key instance of history, rather than storing additional rows to track each change like in a Type 2 SCD.

· Type 6 SCD

o Combines Type 1, 2, and 3.

o When a change happens to a Type 2 member you create a new row with appropriate StartDate and EndDate.

o In Type 6 design you also store the current value in all versions of that entity so you can easily report on the current value or the historical value.