Jaco van der Laan

SCD2 with PIT Tables vs Snapshots: The Scalable Choice for Data Warehouses

14/09/2025  ·  Data Architecture

article base/historisation-temporal
‹ ›

SCD2 with PIT Tables vs Snapshots- The Scalable Choice for Data Warehouses

Why snapshots aren’t enough for consistent history tracking.

The Easy Way: Snapshots

When new data arrives from a source system, the simplest thing to do is to store a snapshot — a full copy of the data exactly as it looked on that day.

It feels straightforward: every day, a new file, a new copy.

But soon the problems appear:

  • Storage explodes because every snapshot is nearly identical to the last.
  • History is hard to analyze — to answer “What was this customer’s address last month?”, we have to sift through every copy.
  • Performance suffers because rebuilding usable datasets from snapshots takes heavy compute power.

Think of snapshots as taking a daily group photo of the office. If you want to know when John grew a beard, you’d have to flip through every picture.

A Smarter Way: SCD2

To avoid endless duplicates, we use a technique called Slowly Changing Dimension Type 2 (SCD2).

Instead of storing the whole dataset every day, we only store changes. Each record is given a start and end date, so we always know which version was valid at a point in time.

This is like giving every person in the office a personal file. When something changes — a new phone number, a new address — we just add a new page. If nothing changes, the file stays the same.

The benefit:

  • Storage is under control.
  • Historical questions become easy to answer.

The Catch: SCD2 Isn’t Free

SCD2 brings efficiency, but it also comes with a cost:

  • Every time we get a new snapshot from the source, we must compare it to the existing records to detect changes.
  • This comparison is compute-intensive and slow.
  • And when someone later asks for the data as a snapshot again, we have to rebuild it, which is also expensive.

So we’ve simply shifted the cost:

  • Snapshots are cheap to load, expensive to use.
  • SCD2 is expensive to load, cheaper to use.

The Middle Ground: PIT Tables

This is where Point-in-Time (PIT) tables come in.

PIT tables act like an index card system. For each reporting date (or snapshot ID), they point to the correct version of each record in the SCD2 history.

Now, when someone asks “Give me the snapshot for May 1st”, we don’t have to rebuild it from scratch. We just look it up in the PIT table.

This gives us the best of both worlds:

  • Storage efficiency from SCD2.
  • Fast snapshot access thanks to PIT tables.
  • Lower total costs compared to raw snapshotting.

Special Case: Fast-Changing Data

Not all data behaves the same.

  • Slowly changing data (like a customer profile) benefits from SCD2.
  • But some data changes almost completely every time:
  • Monthly balances (due to currency fluctuations).
  • Exchange rates (which change daily).

For large datasets where most of the records always change, running a comparison is wasteful. We already know everything is new.

A Flexible, Metadata-Driven Approach

To handle this, we use a metadata-driven framework:

  • Each table is defined in metadata as either:
  • SCD2 (slow-changing) — only store differences.
  • Snapshot (fast-changing) — just load the snapshot, no comparison.

Both still use the same dual SCD2 physical structure, but with different loading rules:

  • For snapshot tables, we simply insert the new records.
  • The valid_from and valid_to values are identical, meaning the records are “closed” immediately.
  • Tables are partitioned on valid_from, which makes redeliveries efficient.

If a source re-sends the same reporting date, we do compare — but only within that partition. Any differences are handled by logically deleting and replacing records.

Why PIT Tables Still Matter

Even in this flexible setup, PIT tables are essential:

  • They provide consistent, snapshot-shaped views of the data.
  • They ensure correctness in case of redeliveries.
  • They keep downstream queries fast and reliable.

The Business Impact

By combining SCD2, snapshots, and PIT tables in one consistent framework, we achieve:

  • Lower storage costs — no endless duplicates.
  • Lower compute costs — no unnecessary comparisons.
  • Faster performance — both for history queries and snapshot reporting.
  • Simplicity — every table has the same structure, but the loading logic adapts to the type of data.

Key Takeaways

  • Snapshots are simple, but wasteful.
  • SCD2 is efficient, but costly to maintain.
  • PIT tables bridge the gap, keeping queries fast.
  • And with a metadata-driven approach, we treat each dataset appropriately — slow-changing data with SCD2, fast-changing data with snapshots — all under one common structure.