Jaco van der Laan

Time Travel vs. Bi-Temporal SCD2: Choosing the Right Approach for Historization in a Modern Data Platform

26/09/2025  ·  Data Architecture  ·  Data Engineering  ·  Data Modeling

databricks scd2 snowflake sql temporal
‹ ›

Snowflake and Databricks offer built-in time travel — but does it really replace bi-temporal SCD2?

Summary

Cloud platforms like Snowflake and Databricks Delta Lake promise easy “time travel”: the ability to query your tables as they were at any point in time.
At first glance, this seems to make slowly changing dimension type 2 (SCD2) modeling — and its bi-temporal extensions — obsolete.

But if you’re designing a governed, long-lived data platform where history must be accurate, auditable, and performant, the picture changes.

This article explores the pros and cons of time travel vs. bi-temporal SCD2, shows hands-on SQL examples for both Snowflake and Delta, and gives you an incremental PIT (Point-in-Time) build pattern you can adopt today.

Why Time Travel Looks Tempting

Snowflake and Delta Lake let you query table history natively:

-- Snowflake
SELECT * FROM customer AT (TIMESTAMP => '2025-01-01');

-- Delta Lake
SELECT * FROM customer VERSION AS OF 37;

Advantages:

  • Less engineering overhead — no SCD pipelines or surrogate keys.
  • Quick rollback & debugging — restore a table state after a bad load.
  • Fast initial delivery — ideal for MVPs or small teams.
  • Operational safety net — recover deleted data or reproduce a previous snapshot.

Where Time Travel Falls Short

When you need business-valid history, time travel shows its limits:

  1. No business validity — captures when data was loaded, not when it became true.
  2. Short retention — Snowflake: default 1 day, max 90; Delta: log cleanup can delete history.
  3. No stable join keys — every query must recompute “as-of” logic; brittle for consumers.
  4. No PIT optimization — repeated time-range scans hurt performance.
  5. Weak governance — valid_from/valid_to are visible to catalogs; time travel isn’t.

Decision Framework

Rule of thumb:Use time travel for operational safety nets.
Build bi-temporal SCD2 for long-term, governed, and performant history.

Hands-On: Time Travel vs. SCD2 + PIT

Time Travel Query — Snowflake

SELECT f.order_id, f.customer_id, d.segment, d.address
FROM orders f
JOIN customer AT (TIMESTAMP => '2025-01-31 23:59:59')
  AS d
  ON f.customer_id = d.customer_id;

Simple, but all facts see one single snapshot — not their true “as-was”.

Bi-Temporal SCD2 Range Join

“`SELECT f.order_id, f.customer_id, d.segment, d.address FROM orders f JOIN customer_scd2 d ON d.customer_id = f.customer_id AND f.order_date >= d.valid_from AND f.order_date < d.valid_to;


> _Accurate per row but can be slow at scale._

## Precomputed PIT Table (Recommended)

```SELECT f.order_id, f.customer_id, d.segment, d.address
FROM orders f
JOIN pit_customer_day p
  ON p.customer_id = f.customer_id
 AND p.pit_date    = f.order_date
JOIN customer_scd2 d
  ON d.customer_sk = p.customer_sk;

Fast, deterministic “as-was” joins for any consumer.

Incremental PIT Build (Daily)

A practical pattern for Snowflake & Delta Lake:

  • Tracks late-arriving changes automatically.
  • Idempotent — safe to rerun daily.
  • Scales — only rebuilds affected dates.

See full incremental PIT build SQL for Snowflake & Delta →

Key Takeaways

  • Time travel ≠ business-valid history.
    Great for recovery & debugging, but doesn’t solve semantics.
  • Retention matters.
    If you need >90 days, time travel alone is risky.
  • SCD2 + PIT unlocks performance & governance.
    Stable keys, lineage, and fast as-was queries.
  • Best of both worlds:
    Use time travel as an operational safety net and SCD2 for long-term truth.

Related Reading

References