Type 1 SCD in Delta Lake: Overwriting Data

Understanding Slowly Changing Dimensions

Slowly Changing Dimensions are a core concept in data warehousing for managing the history of dimension attributes. When an attribute for a record changes, an SCD strategy defines how that change is stored. There are several types, each with a different approach to handling history.

For this lesson, our focus is SCD Type 1.

In an SCD Type 1 implementation, when a dimension attribute changes, you overwrite the existing record with the new information. This is the simplest SCD type and is ideal when you don't need to track the historical values of an attribute.

The MERGE Statement: The Engine for SCD Type 1

In Databricks, the cornerstone for implementing SCDs is the MERGE statement. You may know this operation by another name: upsert. It's a single, atomic operation that combines UPDATE, INSERT, and DELETE logic.

For SCD Type 1, we are interested in two main clauses:

• WHEN MATCHED THEN UPDATE: If a record from our source data (the updates) finds a matching record in our target dimension table (based on a business key like customer_id), we update the target record's fields.
• WHEN NOT MATCHED THEN INSERT: If a record from our source is new and doesn't exist in the target table, we insert it as a new row.

Let's watch a video that explains this concept clearly.

As the video explained, the MERGE statement elegantly handles the logic required for an SCD Type 1 load. It checks for the existence of a record and then performs the correct action—update or insert—all within a single, transactional command. This is crucial for maintaining data consistency.

Hands-On: Implementing SCD Type 1 in Databricks

Now that you understand the concept, let's walk through a practical implementation. Your preference for hands-on, code-centric learning will be well-served here. We will see how to construct and execute the MERGE query in a Databricks notebook.

Code Examples

To solidify your understanding, here are the essential MERGE patterns for SCD Type 1 in both SQL and Python. Given your proficiency in both, you can appreciate the flexibility Databricks offers.

These examples assume you have a target Delta table (dim_employees) and a source of updates (new_employee_data), both containing an employee_id column as the unique key.

Spark SQL

This is the most direct and readable way to perform a merge. The * syntax is a convenient shorthand to update or insert all columns, assuming the schemas match.

MERGE INTO dim_employees AS target
USING new_employee_data AS source
  ON target.employee_id = source.employee_id
WHEN MATCHED THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *

Python (PySpark)

You can also perform the same operation programmatically using the DeltaTable class in PySpark. This is particularly useful when building complex data pipelines or integrating this logic into a larger Python application.

from delta.tables import DeltaTable




# Assume 'spark' is your SparkSession and the table 'dim_employees' exists
delta_target = DeltaTable.forName(spark, "dim_employees")




# Assume 'new_employee_data_df' is your source DataFrame of updates
delta_target.alias("target").merge(
    source=new_employee_data_df.alias("source"),
    condition="target.employee_id = source.employee_id"
  ) \
  .whenMatchedUpdateAll() \
  .whenNotMatchedInsertAll() \
  .execute()

Both of these code blocks achieve the exact same SCD Type 1 outcome. They find matching records based on employee_id and update them, or insert new records if no match is found.

SCD Type 1 in Declarative Pipelines (Delta Live Tables)

For streaming or incremental use cases, Delta Live Tables (DLT) provides a declarative way to manage SCDs, which can greatly simplify your pipeline code. While MERGE gives you fine-grained control for batch jobs, DLT can automate the process.

You can instruct a DLT pipeline to handle SCD Type 1 logic automatically.

The key part of the DLT code is:

dlt.create_auto_cdc_flow(
  target = "products_scd1",
  source = "products",
  keys = ["product_id"],



  # ... other parameters
  stored_as_scd_type = 1 
)

By setting stored_as_scd_type = 1, you are telling the DLT engine to apply the MERGE (upsert) logic automatically, abstracting away the manual WHEN MATCHED/WHEN NOT MATCHED clauses. This is a powerful feature for building robust, modern data pipelines.

Conclusion

In this lesson, you have learned how to manage changes in dimensional data using the SCD Type 1 pattern in Databricks. This is a fundamental skill for maintaining clean, current, and reliable data in your Lakehouse.

Key Takeaways:

• SCD Type 1 is a strategy for handling dimension changes by overwriting the old data with the new data. It does not preserve history and is used when only the current state is important.
• The MERGE statement is the primary tool for implementing SCD Type 1 in Delta Lake. Its WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT clauses provide the necessary "upsert" logic in a single atomic operation.
• You can implement MERGE logic using both Spark SQL for clarity and PySpark's DeltaTable class for programmatic control.
• For advanced streaming pipelines, Delta Live Tables can automate SCD Type 1 processing declaratively, simplifying your code.

Preview of the Next Lesson:

SCD Type 1 is perfect for corrections, but what if you need to track the history of changes? For example, what if you need to analyze sales performance based on a customer's address at the time of purchase, even if they have since moved? In our next lesson, we will tackle this by learning how to implement Type 2 slowly changing dimensions, where we preserve history by creating new versions of records.