One shape, three dialects: SCD Type 2

A customer's city was unknown, then Lyon, then unknown again. A slowly changing dimension should hold three versions of that customer. Most hand-written SCD Type 2 loads hold one, because the change check is d.city <> u.city, and in SQL NULL <> 'Lyon' is not true. It is NULL, so the row is skipped, the load succeeds and the history is quietly wrong.

SCD Type 2 is one of the ten pipeline shapes Emolysis builds from templates. This post takes one shape through three platforms: what stays the same, what each dialect does differently, and how one set of golden cases checks all three.

The cases that break SCD Type 2

Here are three batches of customer updates, one a month. Each batch holds at most one row per customer.

Each customer tests one rule. Customer 42 changes from NULL to a value and back, which a plain <> misses. Customer 7 changes tier and then reverts, so the third version has the same values as the first and must still be a new version. Customer 9 arrives in run 2 with a newer timestamp and the same values, which is not a change, and is absent from run 3, which does not close it. There is one more rule that no table shows: running any batch twice must change nothing the second time.

After the three runs, the dimension holds exactly this:

Validity is a half-open range: a version is valid from valid_from up to, not including, valid_to, and the current version has no end.

The shape is the same everywhere

On every platform the load does two things: close the current version of each key whose tracked attributes changed, then open a current version for each key in the batch that has none. New keys and the keys just closed both lack a current version, so the second step handles them together.

Two steps sound fragile, but this order makes them safe to repeat. Re-run a batch and the first step finds nothing changed, because the current versions already hold the batch's values; the second finds every key already current. If a run fails between the two steps, the next run finds those keys closed, closes nothing more and opens their new versions.

Databricks and Microsoft Fabric: <=> on a struct

On Databricks, and on Microsoft Fabric lakehouse tables, which run the same Spark SQL on Delta tables, the first step is a MERGE. The change check compares structs with the null-safe equality operator:

MERGE INTO dim_customer AS d
USING latest_updates AS u
  ON d.customer_id = u.customer_id AND d.is_current
WHEN MATCHED AND NOT (struct(d.city, d.tier) <=> struct(u.city, u.tier)) THEN
  UPDATE SET valid_to = u.updated_at, is_current = false;

<=> treats two NULLs as equal and a NULL and a value as different, so customer 42's changes are seen. Comparing structs keeps the check one expression however many columns are tracked, which matters when the list of tracked columns is a template slot rather than two names. The second step is an insert with a LEFT ANTI JOIN against the current versions:

INSERT INTO dim_customer (customer_id, city, tier, valid_from, valid_to, is_current)
SELECT u.customer_id, u.city, u.tier, u.updated_at, NULL, true
FROM latest_updates AS u
LEFT ANTI JOIN (SELECT customer_id FROM dim_customer WHERE is_current) AS c
  ON u.customer_id = c.customer_id;

latest_updates is the batch reduced to its newest row per key, a ROW_NUMBER() over the key ordered by updated_at.

Snowflake: EXCEPT, QUALIFY and a transaction

On Snowflake, the same logic reads differently. QUALIFY filters on a window function directly, so the newest row per key is one clause. EXCEPT compares whole rows and treats NULLs as equal, so it is a null-safe change list in one statement:

CREATE OR REPLACE TEMPORARY TABLE latest_updates AS
SELECT customer_id, city, tier, updated_at
FROM customer_updates
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

CREATE OR REPLACE TEMPORARY TABLE changed_keys AS
SELECT customer_id FROM (
  SELECT customer_id, city, tier FROM latest_updates
  EXCEPT
  SELECT customer_id, city, tier FROM dim_customer WHERE is_current
);

New keys appear in the change list too, because they match no current version. Then both steps run in one transaction, so a reader never sees a key with no current version or with two:

BEGIN TRANSACTION;

UPDATE dim_customer
SET valid_to = u.updated_at, is_current = FALSE
FROM latest_updates AS u
WHERE dim_customer.customer_id = u.customer_id
  AND dim_customer.is_current
  AND dim_customer.customer_id IN (SELECT customer_id FROM changed_keys);

INSERT INTO dim_customer (customer_id, city, tier, valid_from, valid_to, is_current)
SELECT customer_id, city, tier, updated_at, NULL, TRUE
FROM latest_updates
WHERE customer_id IN (SELECT customer_id FROM changed_keys);

COMMIT;

The order of the statements is the dialect lesson. In Snowflake, a DDL statement such as CREATE TABLE commits on its own, and inside an open transaction it commits that transaction too. The temporary tables are therefore built before BEGIN. A failed run leaves only temporary tables behind, and the next run builds them again from the same inputs.

One set of golden cases for all three

The batches above are the kind of case Emolysis keeps for every shape: input tables for each run and the exact table expected after it. Each platform's template carries the same SCD Type 2 cases. Its SQL is translated into DuckDB's dialect and run against them on your machine, run by run, and the output is compared with the expected table row for row. A template whose output differs by one row, one NULL or one timestamp fails. The golden tests docs describe the format.

That catches the logic bugs above on every platform before anything deploys, without a warehouse.

What this does not prove

A golden run on DuckDB checks the template's logic as DuckDB executes the translation. It does not check that Spark compares structs exactly as DuckDB does, that Snowflake's UPDATE ... FROM behaves the same at scale, or that your account may write the table. Those are checked by running the same cases on the platform itself.

Status: Databricks, In development. Snowflake and Microsoft Fabric, Planned.

Cost differs by platform too. A Delta MERGE rewrites the data files that hold matched rows, or marks them with deletion vectors where those are on; a Snowflake update rewrites the micro-partitions that hold the changed keys' current versions. On both, the change check reads every current version once per run, so clustering or partitioning the dimension by its business key keeps that read small. And the dimension only grows: every change adds a row, so queries for the current state filter on is_current.

The shape stays the same, the null-safe comparison changes with the dialect, and one set of golden cases checks all three.

Emolysis is in private development. Nothing described here is released yet.