Home › Data engineering › Time travel vs SCD Type 2

Data engineering · free guide · one page

Iceberg time travel as SCD Type 2: is it a good pattern?

Iceberg (and Delta) can read a table as it was at an earlier snapshot. So why build a Slowly Changing Dimension Type 2 table at all? This page shows, with one small example, why time travel is not a replacement, where it does help, and the pattern to use instead.

Will it work? Only in a narrow case: a small table, changes loaded the day they happen, long retention, and nobody joining facts to past versions (when it works). When does it not work? Late or backdated data, snapshot expiry, facts that need the value on their own date, data deletion duties, storage cost (nine cases), and every rerun, backfill, reload, compaction or rollback, which time travel records as if the business changed (technical operations). Is it clean design? No: it hides business history inside a storage housekeeping setting (why).

So: not as a replacement. Time travel remembers when we wrote data, keeps it only for days, and gives one version of the table per query. SCD Type 2 remembers when something was true in the business, keeps it as long as you need, and lets each fact join to the version valid on its own date. Use time travel to feed SCD2 (read only what changed), for audit and rollback, and use tags for frozen month-end copies.

In plain words

Two ways to keep history

SCD Type 2 (Slowly Changing Dimension, Type 2) keeps every version of a record as its own row, with the dates it was valid: valid_from, valid_to and is_current. When customer 7 moves city, the Pune row is closed and a Delhi row is added. See the SCD concept page.

Time travel is a table-format feature. Every commit to an Iceberg or Delta table creates a new snapshot (version), and you can read an old one: SELECT * FROM dim_customer TIMESTAMP AS OF '2026-03-12'. It looks like free history. It is history, but of a different kind.

SCD Type 2: history as rowsTime travel: history as copies of the tablecustomer 7 · Pune · from 1 Jan 2025 · to 10 Mar 2026customer 7 · Delhi · from 10 Mar 2026 · to (open) · currentboth versions sit in one table, side by side;a fact joins to the row valid on its own datesnapshot 1 · written 1 Jan 2025 · Punesnapshot 2 · written 15 Mar 2026 · Delhieach version is the whole table at one commit;one query reads one snapshot

SCD2 keeps versions as rows you can join. Time travel keeps versions as whole copies you can read one at a time.

The core difference

Two clocks

Every change has two times. Business time: when it became true in the world (the customer moved on 10 March). Commit time: when our pipeline wrote it (the file arrived and loaded on 15 March). Reports, finance and regulators care about business time. Time travel only knows commit time.

1 Mar10 Mar12 Mar15 Marcustomer movesto Delhi (business time)order 101placedour load writes it(commit time)SCD2 knows the move happened on 10 Mar. Time travel only knows the table changed on 15 Mar.

Late data is normal: files arrive late, corrections are backdated, reloads happen. Commit time is the wrong clock for reporting.

The example, with real dates

What happensWhen / result
Customer 7 moves from Pune to Delhi10 Mar 2026 (business time)
The CRM extract with the move lands and is loaded15 Mar 2026 (commit time, snapshot 2)
Order 101 placed by customer 712 Mar 2026
SCD2 join: order date between valid_from and valid_toDelhi (right)
Time travel: dim_customer as of 12 MarPune (wrong: the move was not loaded yet)
Time travel, 3 months later, after snapshots older than 5 days expiredError: no snapshot for 12 Mar

The SCD2 answer was checked by running the SQL below on Spark 3.5.1 with Delta 3.2.0; the same SQL runs on Iceberg tables in Spark.

Question 1

Will it work? Only when all of these are true

ConditionWhy it is needed
Every change is loaded the same day it happens, and never backdatedThen commit time is close enough to business time.
Readers only ask 'show me the whole table as of date X'One snapshot per query is fine; no fact needs its own date's version.
Snapshot retention is set longer than the history anyone will ask forOtherwise expiry deletes the history; default expiry and VACUUM keep only days.
The table is small and changes rarelySo keeping every rewritten file costs little.
No duty to delete one person's data from historyOtherwise you must destroy snapshots for everyone.
At most one change per key between loadsOtherwise the in-between values never existed in any snapshot.

If even one is false, you get wrong answers or lost history, usually months later, when someone asks for an old report.

Question 2

When it does not work: nine cases

ProblemWhy it bites
Wrong clockTime travel records when we wrote the data (commit time). Reports need when it was true in the business (effective time). Late files, backdated corrections and reloads all put the change on the wrong date.
History expiresIceberg's snapshot expiry (by default it keeps about 5 days) and Delta's VACUUM (7 days by default) delete old versions on purpose. Managed tables run it for you. Keep snapshots forever and storage grows without end.
Facts cannot join 'as of their own date'A fact table with a million orders on 400 different days needs 400 versions of the dimension in one query. Time travel gives one version per table reference. SCD2 does it with one range join.
Storage cost explodesA one-row change rewrites a whole data file (copy-on-write), and compaction rewrites files again. Every old file is kept for time travel. SCD2 adds one small row per change.
Changes inside one load are lostTime travel only sees one state per commit. If an address changed twice between loads, the middle value never existed for it. A changelog or CDC feed with timestamps keeps both.
Answering 'when did this change?' is slowYou must diff snapshots one pair at a time. In SCD2 it is a WHERE clause on valid_from.
Deleting a person's data (GDPR, DPDP)Removing one customer from old snapshots means expiring those snapshots for everyone, which wipes all history. In SCD2 you delete or mask that customer's rows.
Reloads look like changesA backfill, a schema change or a full reload creates a new snapshot where nothing changed in the business.
Tools cannot express itBI tools, metric views and Genie query tables, not snapshot ids. valid_from and valid_to are plain columns any tool can filter.
The one that ends the debate: a report joins a million orders, placed on hundreds of different days, to the customer's region on the day of each order. SCD2 does that in one join. Time travel would need one query per day.

Question 2, continued

What technical operations do to time-travel 'history'

A table changes for technical reasons all the time: reruns, backfills, reloads, compaction, rollbacks, migrations. Each one writes or removes snapshots. Time travel cannot tell a technical change from a business change, so every one of them corrupts or deletes the history. An explicit SCD2 table built with an idempotent MERGE ignores them.

OperationWhat it does to snapshotsIf time travel is your SCD2Explicit SCD2 table
Reprocessing a day or a backfillA new snapshot, even if the business values are the same; with overwrite, every row in the rewritten partitions looks new.Fake changes dated on the rerun day; real history now mixed with technical noise.No new rows: the MERGE compares a hash of tracked columns, so unchanged values are skipped.
Full reload or INSERT OVERWRITE of the tableOne snapshot where the whole table is 'replaced'.Every record appears to change on reload day; earlier corrections can be undone silently.Same as above: only real differences make new rows, dated by the source effective date.
Correcting old data (a wrong city from January fixed in March)The fix lands in a March snapshot; January snapshots still hold the wrong value.Old reports keep the wrong value forever, or you lose the fix if you read as of January.Update the January row's attribute or valid dates on purpose, with an audit column; the change is visible and tested.
Compaction (Iceberg rewrite_data_files, Delta OPTIMIZE)A new snapshot with the same rows in fewer files; old files kept until expiry.Snapshot list fills with no-change versions; storage doubles until expiry runs.No effect on rows.
Snapshot expiry (expire_snapshots) or VACUUMOld snapshots and their files are deleted.History is gone. This runs automatically on managed tables.No effect: maintenance never removes rows.
Rollback (Iceberg rollback_to_snapshot, Delta RESTORE)Iceberg makes an older snapshot current; Delta writes a new version equal to an old one.Everything written after that point drops out of the 'history', or appears twice.Rerun the SCD2 build from the restored source; idempotent MERGE gives the same table.
Schema change (add, rename or drop a column)New snapshots carry the new schema; old ones keep theirs.Queries across versions see different columns; renamed columns break comparisons.Add the column with NULL for old rows; one schema for all history.
Re-creating the table (DROP and CREATE, moving catalog or region, migration to a new format)A new table starts with one snapshot; old history does not come along (a deep clone copies only the current version).All history lost at the move.Rows move with the data; history survives.
Deleting one person's dataA delete commit; old snapshots still hold the person until they expire.To truly delete, expire those snapshots, which wipes everyone's history.Delete or mask that person's rows only.
In practice: after the first reprocessing, time-travel "history" mixes real business changes with rerun noise, and nobody can separate them afterwards.

Question 3

Is it clean design? No

Clean-design testTime travel as SCD2Explicit SCD2 table
Is the meaning explicit in the data model?No: history lives in table metadata, not in columns. Nobody reading the schema can see it.Yes: valid_from, valid_to, is_current are columns in the contract.
Who owns it?Whoever sets snapshot expiry or VACUUM, often a platform team or the managed service, for storage reasons.The data product owner, for business reasons.
Can a housekeeping change break it silently?Yes: one expire_snapshots run or a shorter retention deletes years of history.No: maintenance never deletes rows.
Does it use the right clock?Commit time only.Business effective time from the source.
Is it portable across engines and tools?Snapshot ids and syntax differ by engine and catalog; BI tools, metric views and Genie cannot use them.Plain columns and a range join work everywhere.
Can it be tested?Hard: depends on how many snapshots happen to exist.Yes: assert no overlaps, one current row per key, gaps closed.
Separation of concernsMixes storage concerns (files, retention) with business concerns (history).Keeps them apart.
The clean-design rule: history the business relies on is data, with its own columns, owner, contract and tests. Time travel is a storage feature for audit, rollback and incremental reads. Do not let one pretend to be the other.

Where it is right

What time travel is good for

UseHowVerdict
Audit and debuggingWhat did this table look like when last Tuesday's report ran?Pursue
Rollback after a bad loadRoll the table back to the snapshot before the bad commit.Pursue
Reproducing an ML training set or a past reportRecord the snapshot id with the model or report; re-read it later (within retention, or tag it).Pursue
Finding what changed since the last runIncremental reads and changelogs use snapshots to feed downstream jobs, including the SCD2 build.Pursue
Period-end frozen copies (month-end balances)Tag the snapshot with a long retention: a named, kept version, separate from SCD2.Pursue, with tags
Replacing SCD Type 2 dimensionsWrong clock, expiry, no as-of join, storage cost.Drop

The pattern

Use the snapshots to feed SCD2

silver.customer(Iceberg or Delta)changelog since thelast processed snapshotSCD2 MERGEclose old row, add newgold.dim_customervalid_from · valid_to · is_currenttime travel's real job here: read only what changedtag month-end snapshotsfor frozen period copies

Snapshots find what changed; the MERGE turns it into business-dated history; tags keep the month-end copies.

1. Read only what changed (Iceberg, Spark)

-- Iceberg Spark procedure: a view of rows changed between two snapshots
CALL lake.system.create_changelog_view(
  table => 'silver.customer',
  options => map('start-snapshot-id', '<last processed snapshot>'),
  identifier_columns => array('customer_id'),   -- turns delete+insert pairs into updates
  net_changes => true,                           -- one net change per key
  changelog_view => 'customer_changes');

Iceberg's Spark syntax; not run on this page. On Delta, read the change data feed with table_changes() instead. Store the last processed snapshot id in your metadata store.

2. Apply SCD2 with the business date from the source

-- Close the current row when an attribute changed
MERGE INTO gold.dim_customer d
USING changes c
  ON d.customer_id = c.customer_id AND d.is_current
WHEN MATCHED AND d.city <> c.city THEN
  UPDATE SET d.valid_to = c.effective_date, d.is_current = false;

-- Add the new current row (new customers and changed ones)
INSERT INTO gold.dim_customer
SELECT c.customer_id, c.city, c.effective_date, DATE'9999-12-31', true
FROM changes c
LEFT JOIN gold.dim_customer d
  ON d.customer_id = c.customer_id AND d.is_current
WHERE d.customer_id IS NULL;

Run on Spark 3.5.1 + Delta 3.2.0: the Pune row closed on 2026-03-10 and a current Delhi row was added. effective_date comes from the source system, never from the load time.

3. Join facts as of their own date

SELECT f.order_id, f.order_date, d.city
FROM fact_orders f
JOIN gold.dim_customer d
  ON f.customer_id = d.customer_id
 AND f.order_date >= d.valid_from
 AND f.order_date <  d.valid_to;
-- order 100 (20 Feb) -> Pune,  order 101 (12 Mar) -> Delhi

Output from the same run. One join, every fact gets the version valid on its own date.

4. Keep frozen month-end copies with tags

-- Iceberg (Spark SQL): keep this snapshot for 7 years even when others expire
ALTER TABLE gold.account_balances CREATE TAG `eom-2026-10` RETAIN 2555 DAYS;

SELECT * FROM gold.account_balances VERSION AS OF 'eom-2026-10';

Iceberg's Spark syntax; not run on this page. On Delta, a shallow clone per period end does the same job.

Pick SCD2 changes with a hash of the tracked columns for wide tables, handle several changes per key in one batch by ordering on the effective date, and keep the SCD2 build idempotent so a rerun gives the same table. The bronze-to-silver page has a full SCD2 MERGE with these cases.

Choosing

Which to use, by situation

SituationUseVerdict
Dimension attributes that change and facts must see the value at their own date (customer region, product price band, employee grade)Explicit SCD2 table built by MERGEPursue
Only the current value mattersSCD Type 1 (overwrite); time travel covers short-term debuggingPursue
Need the table exactly as it was at each month-end, for regulatorsIceberg tags (or Delta shallow clones) at each period end, with long retentionPursue
Small reference table, a few changes a year, nobody joins facts to historyTime travel for audit only, with retention set long enoughPursue only for audit, not reporting
Keep every snapshot forever and call it SCD2—Drop

FAQ

Questions people ask

Can Iceberg time travel replace SCD Type 2?

No. Time travel keeps versions by commit time, not business effective time; old snapshots are expired on purpose; and one query reads one snapshot, so facts cannot join to the dimension value valid on each fact's own date. Build an explicit SCD2 table and use time travel for audit, rollback and incremental reads.

Is Delta Lake time travel any different?

No, for this question. Delta keeps versions by commit too, and VACUUM removes old files (7 days by default). The same reasons apply.

How do I use Iceberg snapshots to build SCD2?

Read only what changed since the last processed snapshot (Iceberg's create_changelog_view procedure in Spark, or Delta's change data feed), then run an SCD2 MERGE that closes the current row and inserts the new one, using the business effective date from the source.

What are Iceberg tags good for?

Keeping a named snapshot, for example month-end, for a set number of days even when normal snapshots expire. Good for frozen period copies and reproducible reports; not a replacement for SCD2.

Does reprocessing affect time travel history?

Yes. Every rerun, backfill, full reload, compaction or rollback writes or replaces snapshots, so time travel shows technical changes as if the business changed, and expiry or table re-creation removes history. An SCD2 table built by an idempotent MERGE that compares a hash of tracked columns ignores reruns and compaction.

Why not just keep snapshots forever?

Every rewrite and compaction keeps old data files, so storage grows with every change to any row in a file; deleting one person's data means destroying history; and you still have commit time, not business time.

Sources

Checked against

Related

Where it connects

Slowly changing dimensions · Bronze to silver, with SCD2 code · Delta vs Iceberg · Who maintains your tables (snapshot expiry, VACUUM) · All data engineering