SCD Type 1 vs Type 2 vs Type 3, Choosing the Right Strategy

Why dimensions changing at all is even a problem

Here is the situation that makes this topic necessary. You have a Dim_Customer table. One of your customers, say Rahul, moves from Bengaluru to Pune. Simple enough, you just update his city, right.

Except now think about what that does to your historical sales reports. Every sale Rahul made while he lived in Bengaluru is tied to his customer_id. If you just overwrite his city to Pune, every historical report that ever grouped sales by city now silently shows all of Rahul's past purchases, the ones he made while actually living in Bengaluru, as if they happened in Pune. Your historical numbers just quietly became wrong, and nobody would notice unless they specifically went looking.

This exact problem is what slowly changing dimensions, usually shortened to SCD, is about. It is a set of strategies for handling changes to dimension data over time, and each strategy makes a different trade off between simplicity and preserving history.

SCD Type 1, overwrite and move on

Type 1 is the simplest approach. When a dimension value changes, you just overwrite the old value with the new one. No history is kept at all.

Before the update, this row said Bengaluru. After the update, it just says Pune, and there is no record anywhere that Bengaluru ever existed for this customer.

from delta.tables import DeltaTable

delta_table = DeltaTable.forPath(spark, "/mnt/datalake/gold/dim_customer/")

delta_table.update(
    condition="customer_id = 501",
    set={"city": "'Pune'"}
)

When Type 1 makes sense Type 1 is the right call when the historical value genuinely does not matter, or when keeping it would actually be incorrect for reporting purposes. A good example is correcting a data entry mistake, like fixing a customer's misspelled name. Nobody wants historical reports preserving a typo just because it happened to be there at some point. Similarly, if a business genuinely never cares about a customer's past city and only ever wants to see current attributes, Type 1 keeps things simple and avoids unnecessary complexity.

The downside is obvious. Once overwritten, that old value is gone, and any report that needed to reflect it accurately as of a past date can no longer do so correctly.

SCD Type 2, keep full history with new rows

Type 2 solves the history problem by never overwriting existing rows. Instead, when a dimension value changes, a brand new row gets inserted, and the old row gets marked as no longer current, usually with an effective date range and a flag showing whether it is the active record.

Now, a sale that happened back in 2023 can be correctly joined to the Bengaluru version of Rahul's record, since that row's date range covers that time period, while a sale happening today correctly joins to the Pune row.

delta_table.alias("target").merge(
    updates.alias("source"),
    "target.customer_id = source.customer_id AND target.is_current = true"
).whenMatchedUpdate(
    condition="target.city <> source.city",
    set={
        "end_date": "source.effective_date",
        "is_current": "false"
    }
).execute()

# followed by a separate insert for the new current row
new_row.write.format("delta").mode("append").save("/mnt/datalake/gold/dim_customer/")

When Type 2 makes sense Type 2 is the standard choice whenever historical accuracy actually matters for reporting. This covers a lot of real business scenarios, like tracking a customer's loyalty tier over time, an employee's department history for HR reporting, or a product's price history so past sales can correctly reflect the price at the time of sale rather than today's price.

The trade off is added complexity. Your dimension table grows over time since every change creates a new row instead of updating in place, and every query joining to this table needs to account for the effective date logic to pick the correct version, rather than just joining on the customer_id alone.

SCD Type 3, keep limited history in the same row

Type 3 sits between the other two. Instead of creating a new row for every change, or losing history entirely, it adds an extra column to store the previous value alongside the current one, right in the same row.

delta_table.update(
    condition="customer_id = 501 AND current_city <> 'Pune'",
    set={
        "previous_city": "current_city",
        "current_city": "'Pune'"
    }
)

When Type 3 makes sense Type 3 works when you specifically need to compare a value's current state against its immediately previous state, but do not need the full change history going back indefinitely. A common example is tracking a sales rep's most recent territory change, where the business wants to compare current versus previous performance right after a reassignment, but does not need a complete historical log of every territory that rep has ever had.

The limitation is right there in the design. If the city changes again, from Pune to Mumbai, the Bengaluru value is gone completely, since there is only room for one previous value, not an unlimited history. This makes Type 3 unsuitable for anything requiring true long term historical tracking.

Comparing the three directly

How to actually decide which one to use

The decision usually comes down to one honest question. Does anyone need to accurately report on how this value looked at a specific point in the past. If the answer is genuinely no, Type 1 is the simplest and most maintainable choice. If the answer is yes, and the business needs full historical accuracy, like knowing exactly what a customer's tier was on any given date in the past, Type 2 is almost always the right call, despite the added complexity. Type 3 tends to be reserved for narrower cases where only a single before and after comparison matters, and even then, it is worth double checking whether the business might actually want more history later, since Type 3 does not leave much room to expand into that without a redesign.

In real Azure projects using Delta Lake, Type 2 tends to be the most common pattern for dimensions that genuinely matter for historical reporting, since Delta Lake's MERGE command makes implementing it fairly manageable, and the storage cost of extra rows is rarely a real concern given how cheap ADLS Gen2 storage is.

Interview angle

If asked to explain SCD types, walking through a concrete example, like a customer changing cities, and showing how each type handles that same change differently, demonstrates real understanding far better than reciting definitions. If asked which one you would use for a specific scenario, the strongest answer explicitly connects the choice back to whether historical accuracy is actually required for that specific dimension, rather than picking a type by default.

Quick recap

SCD Type 1 overwrites old values with no history kept, best for correcting mistakes or when history genuinely does not matter. SCD Type 2 preserves full history by inserting new rows and tracking effective date ranges, and is the standard choice whenever accurate historical reporting matters. SCD Type 3 keeps only the immediately previous value in the same row, useful for narrow before and after comparisons but not for full historical tracking. In most real Azure Delta Lake pipelines, Type 2 ends up being the most commonly used pattern for dimensions that genuinely need historical accuracy.