Incremental Models Slowly Changing Dimensions and Snapshots

Published by Programming Line in Data Engineering in Depth. Last updated .

Purpose and outcome

Incremental models, slowly changing dimensions, and snapshots solve the same practical problem from three angles: source systems keep changing, but analytical queries need both the latest state and a trustworthy history. In a data engineering warehouse, rerunning every transformation from the beginning is often too slow and too expensive. An incremental model limits each run to new or changed records. A slowly changing dimension, usually shortened to SCD, records how descriptive attributes such as a customer’s plan, territory, or account manager changed over time. A snapshot captures historical versions of source rows even when the source only stores the current version.

The outcome of this lesson is a concrete modeling pattern: use incremental loading for volume, SCD Type 2 dimensions for business history, and snapshots when the upstream application does not provide a reliable history table. These patterns sit inside SQL and data modeling because they decide grain, keys, validity ranges, and how facts join to dimensions at analysis time.

How the mechanism works

An incremental model keeps a target table and appends, updates, or merges only the subset of rows that may have changed since the previous successful run. The most common boundary is a watermark such as updated_at, an increasing ingestion timestamp, or a batch identifier. During a full refresh the model rebuilds all rows. During an incremental run it reads records at or after the saved watermark, transforms them, then writes them into the target.

The hard part is not the filter; it is correctness at the edges. Late-arriving rows can have old business timestamps but new ingestion timestamps. Replayed source events can create duplicates. Updates can change rows that were already loaded. For this reason production incremental models usually combine a lookback window, a stable unique key, and an idempotent write operation such as merge. Idempotent means the same input batch can be processed twice and still produce the same target rows.

A dimension table stores descriptive context. In SCD Type 1, a changed attribute overwrites the old value, so the dimension always shows current truth. In SCD Type 2, a changed attribute closes the old row and inserts a new version. The table has a natural key such as customer_id, a surrogate key such as customer_sk, validity columns such as valid_from and valid_to, and usually an is_current flag. Facts can then join to the dimension version that was valid when the event happened.

A snapshot is a managed SCD-like capture of a source row over time. Instead of hand-writing all change detection, the snapshot process compares selected columns or a timestamp on each run. When a tracked value changes, it expires the previous version and inserts the new one with validity metadata. Snapshots are especially useful when an operational database exposes only current rows, for example the current status of an order, but analysts need to ask what the status was last week.

Table anatomy

A practical SCD Type 2 customer dimension needs explicit grain: one row per customer version. Its columns separate identity, attributes, and validity metadata.

create table dim_customer_scd2 (
  customer_sk bigint generated always as identity,
  customer_id varchar not null,
  email varchar,
  plan_name varchar,
  valid_from timestamp not null,
  valid_to timestamp,
  is_current boolean not null,
  row_hash varchar not null
);

customer_id is the business key from the source. customer_sk is the warehouse key used by facts. row_hash is a compact comparison value over the tracked attributes. If email or plan_name changes, the hash changes, the current row is closed, and a new row is inserted. The open-ended current row has valid_to as null.

An incremental staging model starts with a watermark. The exact timestamp should come from the target model, not from memory in an external scheduler, because the table itself is the durable record of what was loaded.

select customer_id, email, plan_name, updated_at
from raw_customer_events
where updated_at >= (select coalesce(max(updated_at), timestamp '1900-01-01') from stg_customer_events);

Many teams subtract a small lookback interval from the watermark to catch delayed updates. That makes the input overlap previous runs, so downstream writes must deduplicate by key and choose the newest record deterministically.

Worked example 1: append and merge facts

Suppose fact_orders is one row per order. New orders arrive every few minutes, and existing orders can change status from pending to paid or cancelled. An append-only incremental load would duplicate changed orders. A merge uses order_id as the unique key and updates only the fields that can change.

merge into fact_orders as target
using stg_orders as source
on target.order_id = source.order_id
when matched and target.status != source.status then update set
  status = source.status,
  updated_at = source.updated_at
when not matched then insert (order_id, customer_id, status, amount, updated_at)
values (source.order_id, source.customer_id, source.status, source.amount, source.updated_at);

If the source batch contains a new order O-200, the merge inserts it. If it contains existing order O-100 with status changed from pending to paid, the target row is updated in place. The expected output is not a printed message; it is a target table with exactly one row per order_id. This is correct for a fact table whose history of status transitions is not required.

Worked example 2: SCD Type 2 customer history

Now the customer plan is analytically important. If a customer was on the free plan in January and upgraded in February, January usage should still join to the free-plan version. The following executable Python mirrors the SCD Type 2 decision process using a hash over tracked attributes.

import hashlib

rows = [
    {"customer_id": "C1", "email": "a@example.com", "plan_name": "free", "updated_at": "2026-01-01"},
    {"customer_id": "C1", "email": "a@example.com", "plan_name": "pro", "updated_at": "2026-02-10"},
]
current = None
history = []
for row in rows:
    payload = f"{row['email']}|{row['plan_name']}"
    row_hash = hashlib.sha256(payload.encode()).hexdigest()
    if current is None:
        current = {**row, "valid_from": row["updated_at"], "valid_to": None, "is_current": True, "row_hash": row_hash}
        history.append(current)
    elif current["row_hash"] != row_hash:
        current["valid_to"] = row["updated_at"]
        current["is_current"] = False
        current = {**row, "valid_from": row["updated_at"], "valid_to": None, "is_current": True, "row_hash": row_hash}
        history.append(current)

print([(r["customer_id"], r["plan_name"], r["valid_from"], r["valid_to"], r["is_current"]) for r in history])

The deterministic output is [('C1', 'free', '2026-01-01', '2026-02-10', False), ('C1', 'pro', '2026-02-10', None, True)]. The first version is closed at the instant the second version begins. Queries for events on 2026-01-20 join to free; queries for events after 2026-02-10 join to pro.

Worked example 3: snapshotting source state

Consider an application table orders that always overwrites status. On Monday, order O-100 is pending. On Tuesday, the same row becomes paid. By Friday, the operational database has no Monday row. A snapshot run each day preserves those states in snapshot_orders.

select order_id, status, amount, dbt_valid_from, dbt_valid_to
from snapshot_orders
where order_id = 'O-100'
order by dbt_valid_from;

The expected result is a timeline: one row for pending with dbt_valid_to set to the Tuesday snapshot time, then one current paid row with dbt_valid_to null. The exact timestamp depends on the snapshot run time, but the ordering and non-overlapping validity intervals are deterministic when each run sees one state per order.

Design choices and trade-offs

The first design choice is the change boundary. updated_at is simple, but only works if the source reliably updates it for every meaningful change. An ingestion timestamp is safer for late-arriving events, but it may reload many rows when a connector resyncs. A monotonically increasing log sequence number is strongest when available because it gives an ordered source-of-truth boundary.

The second choice is overwrite versus history. SCD Type 1 is cheaper and simpler for corrections, such as fixing a misspelled country name. SCD Type 2 is necessary when the previous value changes analysis, such as sales territory, subscription plan, or employee department. Not every column belongs in Type 2 tracking. Volatile fields such as last_login_at can create excessive versions without improving analysis.

The third choice is hand-built SCD logic versus snapshots. Hand-built dimensions give precise control over surrogate keys, conformed dimensions, and fact joins. Snapshots are faster to add and useful near raw sources, but they can become large and may reflect extraction timing instead of business-effective timing. A mature warehouse often uses snapshots to preserve raw history, then builds curated SCD dimensions from them.

Failure modes and troubleshooting

Symptom: duplicate fact rows after an incremental run. The likely cause is an append strategy used for mutable records, or a merge key that is not unique. Diagnose it by grouping the target by the declared unique key and counting keys with more than one row. Correct it by changing the write strategy to merge and deduplicating the source batch before merge with a deterministic rule, such as the latest updated_at per key.

Symptom: historical reports change unexpectedly. The cause is often a Type 1 dimension used where Type 2 was required. Diagnose by checking whether the dimension has validity ranges and whether facts join on event time as well as the natural key. Correct it by adding SCD Type 2 versions and backfilling facts to the proper surrogate keys where source history allows it.

Symptom: missing late records. The watermark probably advanced past records that arrived after their business timestamp. Diagnose by comparing ingestion time with business time in rejected or raw records. Correct it by filtering incremental input on ingestion time, or by adding a lookback window and merge-based idempotency.

Symptom: overlapping SCD versions. The cause is usually multiple changes for the same natural key in one batch processed without ordering. Diagnose with a window query that compares each row’s valid_to with the next row’s valid_from.

with overlaps as (
  select customer_id, valid_from, valid_to,
         lead(valid_from) over (partition by customer_id order by valid_from) as next_valid_from
  from dim_customer_scd2
)
select *
from overlaps
where valid_to is not null and valid_to != next_valid_from;

The correction is to order changes within each natural key, close each prior version at the next version’s start, and enforce at most one current row per natural key.

Reliability and performance implications

Incremental models reduce scan cost and run time, but they trade simplicity for state. The model is only as reliable as its watermark, unique key, and recovery behavior after a failed run. A failed run should not advance the durable target watermark unless the target writes committed successfully. Warehouse transactions, temporary tables, and atomic swaps help keep partial data out of published tables.

SCD Type 2 tables grow with each tracked change. Partitioning or clustering by natural key and validity dates improves point-in-time joins. Current-row indexes or clustering on is_current speed common dimension lookups. For snapshots, the schedule defines temporal resolution: hourly snapshots can see short-lived states that daily snapshots miss, but they also store more versions. Security matters because snapshots preserve old values that the source may later overwrite or delete. Sensitive attributes should be excluded, tokenized, or governed with the same retention policy as other historical data.

Hands-on lab

Prerequisites: a SQL warehouse or local analytical database, a raw customer change table, and permission to create staging and dimension tables. Use a small development schema, not a shared production schema.

  1. Create raw_customer_events with columns customer_id, email, plan_name, and updated_at. Insert two rows for one customer with different plans and increasing timestamps.
  2. Create dim_customer_scd2 using the schema shown earlier.
  3. Build a staging query that deduplicates to one ordered change stream per customer_id and computes a hash over email and plan_name.
  4. Load the first event. Verify there is one current dimension row with valid_to null.
  5. Load the second event. Expire the previous current row by setting valid_to to the second event’s updated_at and is_current to false. Insert the new current row.
  6. Run the overlap diagnostic query. It should return zero rows.
  7. For cleanup, drop the development tables or roll back the transaction if your database supports transactional DDL.

Verification should include three checks: one current row per customer, no overlapping validity intervals, and a point-in-time join from a sample fact date to the expected customer version.

Assessment exercises

  1. A source sends order updates with both created_at and connector ingested_at. Which column should drive the incremental watermark when late events are common, and what write strategy must accompany it?
  2. You track last_login_at in an SCD Type 2 customer dimension and the table grows ten times faster than expected. Explain the modeling mistake and propose a better location for that attribute.
  3. A report counting revenue by sales territory changes for last quarter after a salesperson is reassigned this quarter. Describe the likely join mistake and how the fact table should join to the dimension.
  4. Design a test that proves a snapshot table has not produced two current rows for the same source key.
  5. When would you rebuild an incremental model with a full refresh instead of relying on the next incremental run?

Summary

Incremental models make warehouse transformations efficient by processing only changed records, but they require durable watermarks, stable keys, and idempotent writes. SCD Type 2 dimensions preserve attribute history with surrogate keys and validity ranges so facts can be analyzed in the context that was true at event time. Snapshots preserve source states when upstream systems overwrite history. Used together, these patterns let data engineering teams serve current metrics and historical analysis from the same modeled warehouse without pretending that business entities never change.