Part 13. Increments, Snapshots, and History

Data history is one of the main sources of hidden semantic drift. A table may be correct "as of today" but unsuitable for audit if yesterday's state cannot be restored. In a banking context, this is especially important: reports, consents, overdue debt, and risk flags must be reproducible.

Three History Modes

1. Full refresh — rebuild everything from sources. Fine for a tutorial

example, bad for large production tables.

1. Incremental model — add or update changed rows. Requires

a strategy, a unique key, and a replay policy.

1. Snapshot — store attribute changes over time. Useful for statuses,

segments, consent state, and slowly changing dimensions.

In the tutorial example, we don't complicate all models with incremental logic, but we must understand where it will appear in the production pipeline.

What to Write in the Spec

For a model with history, the spec must answer:

• which key defines a row;
• which field defines the time of the event or change;
• whether the past can be recalculated;
• what to do with late-arriving data;
• which command proves replay safety.

Bad formulation:

Implement incremental loading.

Good:

The model uses `transaction_id` as the unique key and `transaction_date` as
the event date. Late rows within 7 days may update aggregates. Older
corrections require a PatchSpec and reviewer approval.

Verification Facts

Minimum facts:

• a repeated dbt build does not change the result without raw changes;
• the unique key is not duplicated;
• the event date is not null;
• the replay window is described;
• the reviewer manually confirmed that the history semantics have not changed.

Qwen Query

Read the specs and marts.
Find models where history, incremental strategy, or snapshot is needed.
For each, return the key, event date, replay policy, and open questions.
Do not modify SQL.

Minimal Output

After the chapter, create a note:

# History Policy

## Customer Attributes
## Card Transactions
## Loans
## Open API Consents
## Replay and Late Data

If a model is not historical, explicitly state why.

Reader's Analysis

Data history seems like a technical topic until the question "what did we know at that moment?" arises. For reporting and risk, this is critical. Today's recalculation may produce a correct current table but an incorrect historical answer. If yesterday the consent was active and today it is revoked, a simple overwrite of the state will destroy an important fact.

An incremental model, a snapshot, and a full refresh are not just ways to write a table. They are different promises about time. A full refresh says: we rebuild the current state from the source. An incremental model says: we add or update changed rows by key and replay window. A snapshot says: we preserve the attribute change over time. The choice between them must be recorded before implementation, otherwise the agent will pick the most convenient option.

Late-arriving data is especially dangerous. If a transaction for last week arrives today, can we alter already-built aggregates? If yes, for what period? If not, where is the exception recorded? Such questions cannot be decided inside is_incremental() on a whim. They belong to the contract, because they change result reproducibility.

The tutorial example is not required to implement all history modes. But the reader should learn to see where history will be needed in the production pipeline. For customers, these are attributes and segments. For payments — transaction time and late arrivals. For consents — activity and revocation. For loans — stage and overdue. A good specification at least names these boundaries, even if the first version remains a simple full refresh.

Practice

Pick one source and decide whether it needs a full refresh, an incremental model, or a snapshot. Record the key, event date, replay policy, and what to do with late data.

Typical Mistake

Thinking that materialized='incremental' solves history. That is only a write mode. The meaning of history is defined by keys, timestamps, replay policy, and reviewer facts.

Review Questions

1. How does an incremental model differ from a snapshot?
2. What facts prove replay safety?
3. Why must late-arriving data be described before SQL?