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.
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.
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.
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 happens | When / result |
|---|---|
| Customer 7 moves from Pune to Delhi | 10 Mar 2026 (business time) |
| The CRM extract with the move lands and is loaded | 15 Mar 2026 (commit time, snapshot 2) |
| Order 101 placed by customer 7 | 12 Mar 2026 |
| SCD2 join: order date between valid_from and valid_to | Delhi (right) |
| Time travel: dim_customer as of 12 Mar | Pune (wrong: the move was not loaded yet) |
| Time travel, 3 months later, after snapshots older than 5 days expired | Error: 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
| Condition | Why it is needed |
|---|---|
| Every change is loaded the same day it happens, and never backdated | Then 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 for | Otherwise expiry deletes the history; default expiry and VACUUM keep only days. |
| The table is small and changes rarely | So keeping every rewritten file costs little. |
| No duty to delete one person's data from history | Otherwise you must destroy snapshots for everyone. |
| At most one change per key between loads | Otherwise 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
| Problem | Why it bites |
|---|---|
| Wrong clock | Time 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 expires | Iceberg'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 explodes | A 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 lost | Time 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 slow | You 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 changes | A backfill, a schema change or a full reload creates a new snapshot where nothing changed in the business. |
| Tools cannot express it | BI tools, metric views and Genie query tables, not snapshot ids. valid_from and valid_to are plain columns any tool can filter. |
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.
| Operation | What it does to snapshots | If time travel is your SCD2 | Explicit SCD2 table |
|---|---|---|---|
| Reprocessing a day or a backfill | A 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 table | One 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 VACUUM | Old 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 data | A 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. |
Question 3
Is it clean design? No
| Clean-design test | Time travel as SCD2 | Explicit 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 concerns | Mixes storage concerns (files, retention) with business concerns (history). | Keeps them apart. |
Where it is right
What time travel is good for
| Use | How | Verdict |
|---|---|---|
| Audit and debugging | What did this table look like when last Tuesday's report ran? | Pursue |
| Rollback after a bad load | Roll the table back to the snapshot before the bad commit. | Pursue |
| Reproducing an ML training set or a past report | Record the snapshot id with the model or report; re-read it later (within retention, or tag it). | Pursue |
| Finding what changed since the last run | Incremental 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 dimensions | Wrong clock, expiry, no as-of join, storage cost. | Drop |
The pattern
Use the snapshots to feed SCD2
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
| Situation | Use | Verdict |
|---|---|---|
| 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 MERGE | Pursue |
| Only the current value matters | SCD Type 1 (overwrite); time travel covers short-term debugging | Pursue |
| Need the table exactly as it was at each month-end, for regulators | Iceberg tags (or Delta shallow clones) at each period end, with long retention | Pursue |
| Small reference table, a few changes a year, nobody joins facts to history | Time travel for audit only, with retention set long enough | Pursue 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