Type 2 SCD in Delta Lake with MERGE

What is SCD Type 2?

In an SCD Type 2 implementation, a change to a dimension attribute triggers two actions:

1. The existing, current record is marked as "inactive" or "expired."
2. A new record is inserted with the updated information and marked as "current."

This creates a complete historical trail of all versions of a given record. To manage this, we typically add several metadata columns to our dimension table:

• A boolean flag like is_current or active_status to quickly identify the current version.
• Date/timestamp columns like start_date and end_date to define the period during which a version was valid.
• A surrogate key (e.g., a unique integer id) because the natural business key (e.g., employee_id) will now have duplicates.

The image below illustrates this process. When John's address changes, the original record is expired (its valid_to date is set), and a new, current record is inserted.

To understand the business value and context of SCD Type 2, let's watch a short segment from a Databricks tech chat.

Implementing SCD Type 2 with MERGE

As the video explained, the MERGE statement is the key to performing an SCD Type 2 update in a single, atomic transaction. The challenge is that for each incoming change, we need to perform both an UPDATE (on the old record) and an INSERT (for the new version).

We will explore two common patterns to achieve this. Since you are proficient in both Python and SQL, we'll look at a PySpark-centric approach and a SQL-centric one.

Pattern 1: A Programmatic PySpark Approach

This pattern involves a series of transformations to prepare a source DataFrame that can then drive the MERGE command. The core idea is to create a DataFrame containing records that will match for an update, and other records that will not match, forcing an insert.

A great way to understand this is to walk through a detailed, step-by-step example.

This method is powerful because it gives you fine-grained control over the logic within your Python code, which can be beneficial when building complex ETL pipelines or libraries.

Pattern 2: The SQL UNION ALL Approach

Another common and elegant pattern, especially in SQL, involves constructing a source for the MERGE statement using a UNION ALL. This source table contains two sets of records:

1. Records for updates: The incoming changes with their natural keys. These will match existing records in the target table.
2. Records for inserts: A UNION of the same incoming changes, but with their merge keys set to NULL. These will never match, forcing them into the WHEN NOT MATCHED THEN INSERT clause.

Let's explore this pattern using a practical article that implements SCD Type 2 on Databricks SQL.

Here is a simplified version of the SQL MERGE logic from the article, which you can use as a template:

MERGE INTO silver_dimension AS target
USING (
  -- Records to find matches for expiring
  SELECT 
    updates.natural_key AS merge_key, 
    updates.*
  FROM updates
  
  UNION ALL
  
  -- Records to insert new versions
  SELECT 
    NULL AS merge_key, 
    updates.*
  FROM updates
) AS source
ON target.natural_key = source.merge_key AND target.is_current = true

-- Clause 1: Expire old records
-- A record from the first part of the UNION matches an existing, current record.
WHEN MATCHED AND target.dimensional_attribute <> source.dimensional_attribute THEN
  UPDATE SET 
    target.is_current = false, 
    target.end_date = source.event_timestamp

-- Clause 2: Insert new records
-- A record from the second part of the UNION (with NULL key) does not match.
WHEN NOT MATCHED THEN
  INSERT (
    natural_key,
    dimensional_attribute,
    is_current,
    start_date,
    end_date
  )
  VALUES (
    source.natural_key,
    source.dimensional_attribute,
    true,
    source.event_timestamp,
    NULL
  );

This pattern is very explicit and readable within a single SQL statement, making it a popular choice for SQL-based transformations and Databricks SQL workflows.

Practical Considerations

Implementing SCD Type 2 effectively involves a few more details that are crucial in a production environment.

1. Surrogate Key Generation
As noted, when you start keeping history, your natural business key is no longer unique. You need a surrogate key. Traditional databases often have an AUTO_INCREMENT feature. Delta Lake's MERGE statement doesn't support this directly. A common approach is to use a window function like ROW_NUMBER() to generate IDs.

In modern Databricks, you can also define columns with GENERATED ALWAYS AS IDENTITY when you create the table, which handles this automatically for INSERT operations (though you must list columns explicitly and not use INSERT *).

2. Creating Views for Users
Your dimension table now contains all historical versions, which can be confusing for business users who typically only want to see the current state. The best practice is to provide a clean, simple view on top of the SCD table.

This view filters for only the active records, hiding the complexity of the historical data and improving query performance through partition pruning if you partition by the is_current flag.

CREATE OR REPLACE VIEW silver_dimension_current AS
SELECT * 
FROM silver_dimension
WHERE is_current = true;

Conclusion

In this lesson, you've moved beyond simple data updates to master a fundamental data warehousing technique for preserving history. You now have the tools to build dimension tables that provide a complete and auditable record of changes over time.

Key Takeaways:

• SCD Type 2 preserves history by expiring old records and inserting new ones, enabling point-in-time analysis.
• The implementation requires adding metadata columns to your dimension table, such as is_current, start_date, and end_date.
• Delta Lake's MERGE statement is the atomic and reliable tool for implementing SCD Type 2 logic.
• You learned two powerful patterns for structuring the MERGE operation: a programmatic PySpark approach and a SQL UNION ALL approach.
• In production, you should create a view that exposes only the current records to simplify access for downstream users.

Preview of the Next Lesson:

We have now covered how to build dimensions that are always current (SCD Type 1) and dimensions that track full history (SCD Type 2). The next logical step is to combine these dimensions with our transactional data. In the next lesson, we will construct a star schema with fact and dimension tables in the gold layer, bringing together all the modeling concepts we've learned to build a powerful analytical model.