SCD Type 3, When and How to Use It

Why Type 3 gets skipped over so often

If you look at how often each SCD type actually shows up in real projects, Type 1 and Type 2 dominate, and Type 3 tends to get mentioned briefly and then forgotten. This is not because Type 3 is unimportant, it is because its use case is genuinely narrower than the other two, and a lot of learning resources rush past it as a result. Understanding exactly when it fits, rather than just knowing it exists, is what actually makes it useful.

What Type 3 actually does, one more time with a clearer example

Type 3 adds a column to store the immediately previous value alongside the current one, right in the same row, rather than creating new rows like Type 2 does. Let's use a slightly different example than the customer city one, since Type 3 fits more naturally in scenarios where you specifically want to compare a before and after state.

Say you are tracking a sales representative's assigned territory, and the business wants to compare performance right before and right after a territory reassignment.

existing_data = [
    (201, "Vikram Rao", "North Region", None)
]

columns = ["rep_id", "rep_name", "current_territory", "previous_territory"]

df_existing = spark.createDataFrame(existing_data, columns)

df_existing.write.format("delta") \
    .mode("overwrite") \
    .save("/mnt/datalake/gold/dim_sales_rep_scd3/")

Vikram starts out in North Region, with no previous territory recorded yet, since this is his first assignment.

Implementing the change

When Vikram gets reassigned to South Region, the previous_territory column should capture what current_territory was right before this change, and current_territory should update to the new value.

from delta.tables import DeltaTable

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

incoming_update = spark.createDataFrame(
    [(201, "South Region")],
    ["rep_id", "new_territory"]
)

delta_table.alias("target").merge(
    incoming_update.alias("source"),
    "target.rep_id = source.rep_id"
).whenMatchedUpdate(
    condition="target.current_territory <> source.new_territory",
    set={
        "previous_territory": "target.current_territory",
        "current_territory": "source.new_territory"
    }
).execute()

After this runs, the table looks like this.

Now a report can directly compare Vikram's performance in South Region against his previous performance in North Region, using a single row, without needing to filter or join based on effective dates the way a Type 2 table would require.

Why the condition in the MERGE matters here specifically

Notice the update condition checks that current_territory is actually different from the new incoming value. This matters more with Type 3 than it might first appear, because if this pipeline runs again with the same South Region value, without this condition, it would incorrectly overwrite previous_territory with South Region again, effectively losing the North Region history that was still meaningful. The condition protects against this by only shifting values when a genuine change is happening.

What happens on a second change, and why this matters

Here is the detail that defines Type 3's real limitation. Say Vikram gets reassigned again, this time from South Region to East Region.

incoming_update_2 = spark.createDataFrame(
    [(201, "East Region")],
    ["rep_id", "new_territory"]
)

delta_table.alias("target").merge(
    incoming_update_2.alias("source"),
    "target.rep_id = source.rep_id"
).whenMatchedUpdate(
    condition="target.current_territory <> source.new_territory",
    set={
        "previous_territory": "target.current_territory",
        "current_territory": "source.new_territory"
    }
).execute()

Notice North Region is now completely gone from this row. There was only ever room for one previous value, and it just got overwritten by South Region. If the business ever needed to know that Vikram was originally in North Region before any of this happened, that information no longer exists anywhere in this table.

This is exactly why Type 3 is described as tracking limited history rather than full history. It is a deliberate trade off, not an oversight, but it means Type 3 is genuinely the wrong choice if there is any real chance the business will eventually want more than one level of history back.

Where Type 3 genuinely fits, beyond the textbook example

A few realistic scenarios where Type 3 tends to be a reasonable fit. Tracking a customer's previous versus current subscription tier, specifically to support a report comparing behavior immediately before and after an upgrade or downgrade, without needing a full subscription history log. Tracking a product's previous versus current price, when the business specifically wants to flag recent price changes for a short review window, but a separate, more complete price history table already exists elsewhere for anything requiring deeper historical tracking. Tracking an employee's previous versus current manager, immediately after a reporting line change, for a short term transition reporting need.

In each of these cases, the defining feature is that the business genuinely only cares about a single step back, not an ongoing history, and there is often a reasonable expectation that this comparison window is temporary or narrow in scope.

A practical warning worth taking seriously

Before implementing Type 3 for a real dimension, it is worth explicitly asking whoever requested it whether they are confident they will never need more than one level of history. In practice, business requirements shift, and a stakeholder who initially says they only care about the immediately previous value sometimes comes back later wanting a fuller history, at which point the table needs a structural redesign rather than a simple adjustment. If there is genuine uncertainty about this, defaulting to Type 2 instead, even though it is more work upfront, tends to avoid a much more painful migration later.

Interview angle

If asked about SCD Type 3, being able to clearly explain its core limitation, that it only retains a single previous value and any change beyond that is permanently lost, shows a deeper understanding than simply describing the mechanism. If asked when you would actually choose Type 3 over Type 2, a strong answer references the deliberate trade off between the significantly reduced complexity of a single row update versus the real risk of the business eventually needing history that Type 3 cannot provide.

Quick recap

SCD Type 3 stores only the immediately previous value alongside the current value in the same row, making it simpler to implement and query than Type 2, but genuinely limited to a single step of history. It fits narrow, specific scenarios like a direct before and after comparison following a recent change, but is the wrong choice whenever there is real uncertainty about whether deeper historical tracking might eventually be needed, since that would require redesigning the table entirely rather than adjusting it.