Author: Paul Schuliger
For data warehouse implementations one of the initial data design considerations is to determine the customer requirement for tracking historical information in their data warehouse. This decision determines the design and implementation of the dimension tables, which are filled with descriptive data that provides context to the fact tables. The question that needs to be answered is, “Do the dimension tables retain history?”
- If no, then the historical data can be overwritten. (SCD Type 1)
- If yes, then the historical data needs to be retained and maintained. (SCD Type 2)
Slowly Changing Dimensions
Ralph Kimball, one of the fathers of data warehousing, popularized the Slowly Changing Dimension (SCD) methodology for the tracking of history within dimensions in a traditional star-schema data warehouse populated via a batch process. Databricks, being a modern data platform, has created functionality in their streaming data pipelines, using change data capture (CDC), to manage the complexity of slowly changing dimensions with a single line of code. As a quick refresher here are the definitions and an example of slowly changing dimensions type 1 and type 2.
SCD Type 1
The SCD Type 1 does not track historical information because the previous version of the data is overwritten with the latest data.
For example, a customer’s ZIP code was incorrectly added into the source system. If the ZIP code is corrected in the source system and flows into the data warehouse with the corrected value, is there value to the company to retain the original Zip code? SCD Type 1 dimensions overwrite the value because it is determined retaining the original value is not meaningful.
SCD Type 2
The SCD Type 2 does track historical information. It does so by adding a new record for the latest information and putting an ending timestamp on the previous record.
For example, if a customer’s subscription for a streaming service goes from a free account to a paid subscription that information is beneficial for the company to understand customer behavior, and with that information they at a minimum know when it happened.
Databricks Makes SCD Easy
ETL developers spent hours implementing SCD logic via ETL tools or stored procedures, especially the SCD Type 2 solution. Historically the data warehouse implementations were done in batch processing, but Databricks is now making the handling of slowly changing dimensions in a streaming process easy…
On June 11, 2025, Databricks announced AUTO-CDC (Change Data Capture) is now available for Lakeflow Declarative Pipelines (formerly Delta Live Tables). Within the implementation of the Change Data Capture in conjunction with streaming data pipelines is the ability to track changes over time. And the implementation of the Slowly Changing Dimension (or out of sequence data) is managed with a single line of code.
Change Data Capture
In a streaming context, the Change Data Capture (CDC) functionality uses a Change Data Feed (CDF) to send a list of INSERTs, UPDATEs, and DELETEs to the target table. The definition of the target table determines how the CDF is processed.
In the creation of the table the SCD Type is a requirement, either SCD Type 1 or SCD Type 2. Along with the SCD type there must be a field that represents the correct sequence of the data to manage late arriving data. That field is used in the SEQUENCE BY clause of the table definition.
Slowly Changing Dimension Type 1
Below is the flow creation script that implements a slowly changing dimension type 1, which means that the current data is overwritten by the latest data for the record with the same primary key. This is where an incorrect value in the Zip code example would be updated, and the original value permanently removed.
— SCD Type 1
CREATE OR REFRESH STREAMING TABLE target;
CREATE FLOW flow
AS AUTO CDC INTO target
FROM stream(cdc_data.users)
KEYS (userId)
APPLY AS DELETE WHEN operation = “DELETE”
SEQUENCE BY sequenceNum
COLUMNS * EXCEPT (operation, sequenceNum)
STORED AS SCD TYPE 1;
The example below shows the Original Value of the Zip Code and the update to the value after the SCD Type 1 process is run on the table via the Change Data Feed. The original value is overwritten and replaced with the latest value.
SCD Type 1 Example: 
Slowly Changing Dimension Type 2
The flow creation script for a slowly changing dimension type 2 is like SCD Type 1, with the change being in the SCD Type clause. However, the target table does have to include fields that account for the starting point and the ending point of the record’s history. From the Databricks documentation (reference #2): For SCD type 2 tables, when specifying the schema of the target table, you must also include the _START_AT and _END_AT columns with the same data type as the sequence_by field.
— SCD Type 2
CREATE OR REFRESH STREAMING TABLE target;
CREATE FLOW flow
AS AUTO CDC INTO target
FROM stream(cdc_data.users)
KEYS (userId)
APPLY AS DELETE WHEN operation = “DELETE”
SEQUENCE BY sequenceNum
COLUMNS * EXCEPT (operation, sequenceNum)
STORED AS SCD TYPE 2
TRACK HISTORY ON * EXCEPT (city);
With the single line of code the streaming table, using AUTO-CDC, manages the SCD Type 2 scenario by
- Adding a new record with the most current data and,
- Updating the previous record with the proper timestamp to indicate that it is no longer the most current record.
The example below shows the results of the SCD Type 2 functionality in the AUTO-CDC process. The original value in the Subscription Type (“Free”) is maintained and available for historical reporting purposes while the current Subscription Type (“Paid”) represents the customers current Subscription Type status with the _START_AT date representing the starting point of the change.
SCD Type 2 Example: 
Conclusion
Databricks manages the complexity of slowly changing dimensions in streaming data pipelines for us through the AUTO-CDC process. Prior to this, we would have to implement the functionality with MERGE statements, considering INSERTs, UPDATEs, DELETEs, and the ability to manage records that were out of sequence. Databricks has removed that need and made the slowly changing dimension processing as easy as the press of a button.
Databricks References
- The AUTO CDC APIs: Simplify change data capture with Lakeflow Declarative Pipelines – Azure Databricks | Microsoft Learn
- AUTO CDC INTO (Lakeflow Declarative Pipelines) – Azure Databricks | Microsoft Learn