SCD Type 2 with Apache Iceberg: The MERGE INTO Pattern

Last Updated: July 2026 | 15 min read

Quick Answer: SCD Type 2 with Apache Iceberg uses a MERGE INTO statement to preserve dimension history on your data lake: when a tracked attribute changes, you expire the current row (set valid_to and is_current = false) and insert a new version row in one atomic transaction. Because a single MERGE can only touch each target row once, the trick is to feed it two copies of each changed record — one that matches and closes the old row, one that doesn't match and is inserted as the new version. Iceberg's ACID guarantees make the whole operation safe, re-runnable, and time-travellable.

Overwrite a dimension value and the old truth is gone forever — your "revenue by customer region" report silently rewrites history every time someone moves. Slowly Changing Dimensions Type 2 is how you stop that, and until recently doing it well meant a real data warehouse. Apache Iceberg changed that: you now get warehouse-grade MERGE INTO directly on files in S3. This guide is the hands-on implementation — the exact Spark SQL and PySpark, the one-MERGE gotcha that trips everyone up, hash-based change detection, and how to keep it idempotent. For the concept itself, see our SCD Type 2 explainer; here we build it for the lakehouse.

Why Apache Iceberg makes SCD Type 2 practical on a data lake

Apache Iceberg is an open table format that adds ACID transactions, row-level MERGE INTO, and immutable snapshots to plain files on object storage — so you can run true SCD Type 2 on a data lake without a separate warehouse. Before Iceberg (and its peers Delta Lake and Hudi), updating a single dimension row in a lake meant rewriting whole partitions by hand, with no atomicity if the job died halfway.

Iceberg gives you three things SCD Type 2 depends on:

• Atomic MERGE INTO — expire-old and insert-new commit together or not at all. No half-applied dimension.
• Snapshots — every write is an immutable snapshot, a second history layer independent of your SCD rows (more on this below).
• Hidden partitioning — partition by a transform of a column without exposing it, so current-row lookups stay cheap as the table grows.

If you're weighing formats, our Delta Lake vs Iceberg comparison covers the trade-offs; the SCD Type 2 pattern here works on any of them with minor syntax changes.

The SCD Type 2 table shape

A SCD Type 2 dimension carries a surrogate key, the business (natural) key, the tracked attributes, and three bookkeeping columns — valid_from, valid_to, and is_current — so every version of a record is one row with its own validity window. Here's the Iceberg table:

CREATE TABLE lake.dim.customer (
    customer_sk    BIGINT,        -- surrogate key (unique per version)
    customer_id    STRING,        -- business key (stable across versions)
    name           STRING,
    city           STRING,        -- a tracked attribute
    tier           STRING,        -- a tracked attribute
    row_hash       STRING,        -- hash of tracked attributes (change detection)
    valid_from     TIMESTAMP,
    valid_to       TIMESTAMP,     -- NULL / far-future for the current version
    is_current     BOOLEAN
)
USING iceberg
PARTITIONED BY (is_current);      -- current-row lookups scan a small slice

The rule: one business key can have many rows, but only one with is_current = true. When city changes for a customer, the current row is closed and a new current row is opened.

The core problem: one MERGE can't do both halves

Here's the gotcha that trips up almost everyone the first time. SCD Type 2 needs two things to happen for a single changed customer:

1. Expire the existing current row — UPDATE ... SET valid_to = now(), is_current = false
2. Insert a brand-new row for the new version — INSERT ... is_current = true

But MERGE INTO matches each target row at most once. For a given customer_id, it can run the WHEN MATCHED branch (the update) or the WHEN NOT MATCHED branch (the insert) — not both. So a naive single MERGE either closes the old row without adding the new one, or vice versa. Your history breaks.

There are two correct solutions.

Solution A — the union trick (one atomic MERGE)

Duplicate each changed source row so the target sees two source rows per key: one that matches (to close the old version) and one that deliberately doesn't match (to insert the new version). You do this by giving the "insert" copy a null match key.

MERGE INTO lake.dim.customer AS t
USING (
    -- staged source: each CHANGED customer appears TWICE
    SELECT
        src.customer_id AS merge_key,   -- real key → will MATCH and close old row
        src.*
    FROM staged_changes src
    WHERE src.change_type = 'update'

    UNION ALL

    SELECT
        NULL AS merge_key,              -- NULL key → NEVER matches → inserts new row
        src.*
    FROM staged_changes src
    WHERE src.change_type IN ('update', 'new')
) AS s
ON t.customer_id = s.merge_key AND t.is_current = true

WHEN MATCHED AND t.row_hash <> s.row_hash THEN UPDATE SET
    t.valid_to   = s.valid_from,        -- close the old version
    t.is_current = false

WHEN NOT MATCHED THEN INSERT (
    customer_sk, customer_id, name, city, tier, row_hash,
    valid_from, valid_to, is_current
) VALUES (
    s.customer_sk, s.customer_id, s.name, s.city, s.tier, s.row_hash,
    s.valid_from, NULL, true
);

The merge_key column is the whole trick: the real-key copy matches the current row and closes it; the NULL-key copy can never match, so it falls through to WHEN NOT MATCHED and is inserted as the new current version. Both happen in one atomic Iceberg transaction.

Solution B — two statements (simpler to reason about)

If the union trick feels too clever, split it. Expire first, then insert. Wrap both in the same job so a failure re-runs cleanly:

-- 1. Close current rows whose tracked attributes changed
MERGE INTO lake.dim.customer AS t
USING staged_changes AS s
ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND t.row_hash <> s.row_hash THEN UPDATE SET
    t.valid_to = s.valid_from, t.is_current = false;

-- 2. Insert the new versions (changed + brand-new customers)
INSERT INTO lake.dim.customer
SELECT customer_sk, customer_id, name, city, tier, row_hash,
       valid_from, CAST(NULL AS TIMESTAMP), true
FROM staged_changes
WHERE change_type IN ('update', 'new');

Which to use? The union trick is one atomic commit (no window where a row is closed but its replacement is missing). Two statements are easier to read and debug but leave a sub-second gap between the two commits. On Iceberg, prefer Solution A for production; use Solution B while you're learning the pattern.

Detecting change with a row hash

The cleanest way to decide whether a record changed is to hash its tracked columns and compare the hash — one equality check instead of a long col1 <> col1 OR col2 <> col2 … chain. Build the staged source in PySpark:

from pyspark.sql import functions as F, Window

tracked = ["name", "city", "tier"]
incoming = (spark.table("staging.customer_raw")
    .withColumn("row_hash", F.sha2(F.concat_ws("||", *tracked), 256))
    # dedupe: one row per business key (protects idempotency)
    .withColumn("rn", F.row_number().over(
        Window.partitionBy("customer_id").orderBy(F.col("ingested_at").desc())))
    .filter("rn = 1").drop("rn"))

current = spark.table("lake.dim.customer").filter("is_current = true") \
              .select("customer_id", F.col("row_hash").alias("cur_hash"))

staged = (incoming.join(current, "customer_id", "left")
    .withColumn("change_type", F.when(F.col("cur_hash").isNull(), "new")
                                .when(F.col("cur_hash") != F.col("row_hash"), "update")
                                .otherwise("unchanged"))
    .filter("change_type <> 'unchanged'"))     # skip no-ops → cheap, idempotent

staged.createOrReplaceTempView("staged_changes")

Two details that matter for correctness:

• Deduplicate the source to one row per business key before the MERGE. Late-arriving duplicates are the #1 cause of accidental duplicate versions.
• Filter out unchanged rows. This is what makes the pipeline idempotent: re-run the same batch and every row hashes equal, so the MERGE is a clean no-op.

Two histories: SCD Type 2 rows vs Iceberg snapshots

Iceberg gives you history twice, and they answer different questions — don't conflate them.

You want both. SCD Type 2 rows are your analytical history. Snapshots are your operational safety net — if a bad batch opens wrong versions, you CALL rollback_to_snapshot(...) and try again. Query business-time history like this:

-- What did every customer look like on 2026-06-01?
SELECT * FROM lake.dim.customer
WHERE valid_from <= TIMESTAMP '2026-06-01'
  AND (valid_to > TIMESTAMP '2026-06-01' OR valid_to IS NULL);

Common mistakes (and how to avoid them)

1. Expecting one plain MERGE to close and insert. It can't — use the union trick or two statements.
2. No source deduplication. Two rows for the same key in one batch open two versions. Dedupe to one row per business key first.
3. Comparing every column by hand. Brittle and slow. Hash the tracked columns.
4. No change guard on the update. Without AND t.row_hash <> s.row_hash, an unchanged re-run closes and re-opens every row — history explodes and idempotency dies.
5. Overwriting instead of versioning nulls. A tracked attribute going NULL is still a change; concat_ws handles nulls, but confirm your hash treats NULL distinctly from empty string.
6. Forgetting to expire snapshots. SCD rows grow forever by design; unbounded Iceberg snapshots don't have to — schedule expire_snapshots so metadata stays lean.
7. Partitioning by a high-cardinality key. Partition by is_current (or a bucket) so the MERGE's match against current rows scans a small slice, echoing the same file-layout thinking in our partitioning strategies guide.

See how SolutionGigs can help

Standing up a lakehouse dimension layer and want a second pair of eyes on the MERGE pattern, partitioning, or snapshot hygiene before it hits production? SolutionGigs connects you with vetted data engineers who've built Iceberg and Delta pipelines at scale. See how SolutionGigs can help →

Frequently Asked Questions

Can a single MERGE INTO statement do SCD Type 2?

Not directly. SCD Type 2 needs two actions for one changed key — expire the current row and insert a new version — but a plain MERGE touches each target row at most once. The union trick solves it: duplicate each changed source row so one copy matches and closes the old row while the other has a NULL match key and is inserted as the new version, all in one atomic transaction. The alternative is two statements: a MERGE to expire, then an INSERT of new versions.

Why use Apache Iceberg for SCD Type 2 instead of a data warehouse?

Iceberg brings ACID transactions and row-level MERGE INTO to files on object storage, so you get warehouse-grade SCD Type 2 on your data lake with no separate warehouse to load. It also keeps immutable snapshots of every write — a second, independent history layer. Your SCD rows model business-time history for analytics, while snapshots give you table-state time travel for audits and rollbacks.

How do I detect which dimension rows actually changed?

Hash the tracked attribute columns on both the incoming source and the current dimension row, then compare. If the hashes differ for a matching business key, the record changed and needs a new version. Hashing with something like Spark's sha2 over concatenated columns is faster and cleaner than comparing every column individually, and it reduces your change-detection logic to a single equality check inside the MERGE.

What is the difference between SCD Type 2 rows and Iceberg snapshots?

They track different histories. SCD Type 2 rows model business time — "what was this customer's city on June 1?" — using valid_from, valid_to, and is_current columns you control. Iceberg snapshots model transaction time — "what did the whole table look like after a given write?" — and power audits, rollbacks, and time travel. Use SCD Type 2 for analytics and snapshots for operational safety; they complement, not replace, each other.

Is an Iceberg SCD Type 2 MERGE idempotent?

It can and should be. With hash-based change detection, re-running the same batch finds no hash difference for already-applied rows, so the MERGE is a no-op and opens no duplicate versions. The risks are running without the hash guard or on un-deduplicated late-arriving rows. Always deduplicate the source to one row per business key and gate the update on a real change, so a re-run is always safe.

How should I partition an Iceberg SCD Type 2 table?

Partition on what your queries filter by while keeping current-row lookups cheap. Partitioning by is_current (or a bucket of the business key) means the MERGE's match against current rows scans a small slice, and a days(valid_from) transform helps time-range queries. Use Iceberg hidden partitioning so partition columns are never exposed to query writers and you can evolve partitioning later without rewriting data.

Does this pattern work on Delta Lake too?

Yes. The union trick and hash-based change detection are format-agnostic — they work on Delta Lake and Apache Hudi with minor syntax changes. Delta's MERGE has the same one-touch-per-row limitation, so the two-copy source approach applies identically. The main differences are DDL syntax, partitioning/clustering options, and snapshot/time-travel commands.

Conclusion

SCD Type 2 on a lakehouse used to mean a warehouse and a lot of custom partition-rewriting. Apache Iceberg collapses that into a single atomic MERGE INTO on files you already own — but only if you respect the one rule that trips everyone up: a MERGE touches each row once, so closing the old version and inserting the new one needs two source copies, not one.

The pattern that holds up in production:

• Model the table with a surrogate key, business key, row_hash, and the valid_from / valid_to / is_current triple.
• Detect change with a hash, and dedupe the source to one row per business key.
• Use the union trick so expire-and-insert commit atomically.
• Guard the update on a real hash difference — that's what makes re-runs idempotent.
• Lean on snapshots for rollbacks, and expire them on a schedule.

Get those right and your dimension layer records history you can trust — with Iceberg's time travel as a safety net underneath. For the broader picture, this fits alongside our guides on Apache Iceberg tables in PySpark and dimensional modeling. And if you'd like an expert to review your lakehouse design before it ships, get matched with a vetted data engineer on SolutionGigs — it's free to post your project.

Mohammed Yaseen

Founder, SolutionGigs

Mohammed builds Apache Iceberg lakehouse pipelines on AWS at Telemetrix, running SCD Type 2 dimension loads with Spark MERGE INTO over hundreds of millions of rows on S3. He's debugged the duplicate-version bugs and snapshot bloat this guide warns about — in production, not in theory. LinkedIn →