top of page

SCD Type 2 Databricks: Why APPLY CHANGES INTO Replaced 200 Lines of MERGE Logic

  • Jun 24
  • 7 min read

A practitioner's comparison of hand-rolled SCD Type 2 MERGE logic versus Lakeflow's APPLY CHANGES INTO — with the gotchas that don't show up in the demo.


Introduction

If you are building SCD Type 2 Databricks pipelines today, you have likely already hit at least one of these edge cases.


For most of my career, building Slowly Changing Dimension Type 2 logic meant the same ritual every time. Write a MERGE statement. Get the end-dating logic wrong on the first attempt. Fix it. Get the current-flag logic wrong on the second attempt. Fix that too. Test it against a dataset with same-day updates and watch it break in a new way. By the time it actually worked, you had somewhere between 150 and 250 lines of SQL or PySpark that one other person on the team understood, and that person had usually left.


SCD Type 2 — keeping full history of changes to a dimension by inserting new rows rather than overwriting old ones, with start and end dates marking each row's validity period — is one of the oldest patterns in data warehousing. It is also one of the most consistently mis-implemented, because the logic has more edge cases than it looks like it should.


Databricks' Lakeflow Declarative Pipelines (the current name for what was Delta Live Tables) includes a feature called apply_changes — the Python API successor to the APPLY CHANGES INTO SQL syntax — that implements SCD Type 1 and Type 2 as a declarative statement. What used to take a few hundred lines now takes about twelve.


This article walks through both approaches side by side, the specific gotchas I have hit migrating manual SCD2 pipelines to apply_changes, and where the declarative approach still needs help from you.


The Manual Approach: What SCD Type 2 Databricks Implementations Require

Before looking at the declarative version, it is worth being precise about what correct SCD Type 2 logic has to handle, because this is where most hand-rolled implementations go wrong.


A correct SCD Type 2 MERGE needs to:


  1. Detect which incoming records represent a genuine attribute change versus a duplicate or no-op

  2. Close out the previous "current" row for any changed record — setting its end date and current flag to false

  3. Insert a new row for the changed record with the new attribute values, a start date, a null end date, and current flag set to true

  4. Insert entirely new rows for records that did not previously exist

  5. Handle late-arriving or out-of-order records without corrupting the timeline

  6. Handle deletes, if the source supports them, without losing history


Here is a representative hand-rolled implementation — the kind of MERGE you will find in production Databricks environments built before apply_changes matured:



This works — until you account for the edge cases. What happens if customer_updates contains two changes for the same customer in the same batch? The two-step MERGE above will process them independently and can produce duplicate "current" rows or an incorrect end date, depending on execution order. What happens if a record arrives out of sequence — a correction for last Tuesday arriving after this Friday's update has already been applied? Nothing in the logic above accounts for that. You need an explicit sequence comparison, and most hand-rolled implementations either skip it or get it subtly wrong.


I have debugged production SCD2 pipelines where the bug was exactly this: two updates to the same customer in one micro-batch produced two rows both marked is_current = true, and nobody noticed until a downstream report double-counted that customer's revenue for three weeks.


The Declarative Approach: apply_changes

Lakeflow's apply_changes function (the Python equivalent of the APPLY CHANGES INTO SQL statement) takes a source of change records — typically from a CDC feed via Debezium, Qlik Replicate, or Delta CDF — and handles steps 1 through 5 above automatically, with explicit, declarative configuration for the parts that require business logic.



That is the entire implementation. A few things are happening here that are worth understanding rather than just copying.


keys defines the natural key of the dimension — the column or columns that identify "the same entity" across versions. This replaces the ON target.customer_id = source.customer_id join condition from the manual MERGE.


sequence_by is the single most important parameter in the whole function, and it is the parameter most people get wrong on their first attempt. It tells Lakeflow which column determines the correct order of changes — not the order records arrive in the stream, but the order they actually happened in the source system. This directly solves the out-of-order problem that the manual MERGE above does not handle. If two changes for the same customer arrive in the same micro-batch, or arrive out of sequence relative to an earlier batch, apply_changes uses sequence_by to determine which one is actually the most recent and processes them in the correct order regardless of arrival order.


apply_as_deletes defines which incoming records represent deletions of the dimension member, rather than updates. For SCD Type 2, a "delete" closes out the current row (sets the end date) without inserting a new current row.


except_column_list excludes CDC metadata columns — the operation type, the sequencing column itself — from being written into the target table as if they were dimension attributes.


stored_as_scd_type="2" is the switch that tells Lakeflow to keep full history rather than overwrite in place. Change this single parameter to "1" and the exact same pipeline becomes a Type 1 dimension — no MERGE rewrite required, no new logic. This is something worth sitting with: the difference between SCD1 and SCD2, which in hand-rolled SQL is an entirely different implementation, is a single string value in the declarative API.


Lakeflow automatically adds two columns to the target table: __START_AT and __END_AT, populated from the sequence_by column. The current row for each key has a null __END_AT. There is no separate "is_current" boolean to maintain — __END_AT IS NULL is the current-row predicate, and Lakeflow guarantees it is always correct.


The Gotchas That Don't Show Up in the Tutorial

Sequence column ties

If two change records for the same key have the identical sequence_by value, apply_changes needs a deterministic way to break the tie, or behaviour is undefined across re-runs. The fix is to make your sequence column a composite that cannot tie — typically a struct of the business timestamp plus a monotonically increasing CDC offset or commit version:



I added this after a production incident where two updates landed in the same CDC batch with identical second-level timestamps, and the resulting row order differed between a full backfill and the incremental run that followed — producing two different versions of "history" for the same customer depending on which path you queried.


except_column_list needs to be exhaustive

Anything you do not explicitly exclude becomes a dimension attribute and gets versioned. This sounds obvious until your CDC source adds a new metadata column upstream — a Debezium transaction ID, a new audit field — and suddenly every change to that column triggers a new SCD2 row, even though nothing business-relevant changed. I now maintain an explicit allow-list of expected attribute columns and validate against it rather than relying on an except-list, specifically to catch this.


Backfills behave differently to incremental runs unless you are careful

Running apply_changes against a full historical CDC extract for the first time, and running it incrementally afterward, are not automatically guaranteed to produce identical history if your sequence_by column is not strictly monotonic across the entire change history — not just within a single batch. I learned this migrating a dimension with five years of change history: the backfill produced subtly different __START_AT boundaries to what the equivalent manual MERGE pipeline had produced over five years of incremental runs, because the original sequence column had a timezone inconsistency that incremental processing had been silently tolerant of and the full backfill was not.


Schema evolution on the target requires explicit handling

If the source CDC feed adds a new column, Lakeflow will not automatically add it to an SCD2 target with existing history — you need dlt.apply_changes configured with schema_tracking_location or you handle it as a deliberate migration. This is the same category of problem as schema evolution in the CDF article — the fix is the same discipline: never assume an additive schema change is free in a system that is tracking history.


When the Manual Approach Still Makes Sense

apply_changes is not a universal replacement. There are cases where I still write the MERGE by hand:


When the SCD logic depends on business rules that are not expressible as a simple attribute comparison — for example, only versioning a customer record when their segment changes but not when their contact details change, with different retention rules for each. apply_changes versions on any attribute change within the column list; splitting that logic requires either two separate apply_changes calls against different column subsets, or a manual MERGE with explicit conditional logic.


When you need point-in-time correction logic that retroactively adjusts historical rows — apply_changes is designed for forward-moving change application, not for rewriting a historical version of a row after the fact.


For the majority of standard dimension tables — customers, products, accounts, employees — apply_changes with a well-chosen sequence_by column is the right default, and the manual MERGE pattern should be treated as the exception that requires justification, not the other way around.


What This Looks Like as a Production Pattern

Pulling this together, a production SCD2 pipeline using Lakeflow has a consistent shape: a CDC source feeding a bronze table, a sequence column that is genuinely monotonic across the full history and tie-resistant within a batch, an exhaustive attribute allow-list rather than an except-list, and an explicit schema evolution strategy agreed upfront rather than discovered the first time a column gets added upstream.


None of this is complicated once you have seen it fail once. The value of apply_changes is not that it removes the need to understand SCD2 — it is that it removes 200 lines of MERGE logic where the bugs used to hide, and concentrates your attention on the four or five decisions that actually matter.


If this was useful

The MERGE-based implementation and the apply_changes implementation above, along with the sequence-tie fix and a schema validation helper, are



This article was written by Keith Jenneke, Principal Consultant & Practice Lead at Cypher Agency. Keith leads our data platform and AI engineering practice, building on Azure Databricks across professional services, resources, and government.

Comments

bottom of page