Topic: Part 13. Increments, Snapshots and History
Difficulty level: Medium
Estimated study time: 2-3 hours
Prerequisites: Basic understanding of dbt (data build tool) and data models
SQL proficiency at the level of writing queries and understanding window functions
Understanding of data warehouse concepts and ETL/ELT processes
Familiarity with the banking reporting domain (clients, payments, loans, consents)
Basic understanding of the concept of data reproducibility
Learning objectives: Distinguish three modes of data history (full refresh, incremental, snapshot) and understand when to apply each
Formulate correct specifications for models with history, including key, event date and replay policy
Define a set of validation facts proving the safety of model replay
Analyze requirements for late-arriving data and formulate contractual constraints
Apply acquired knowledge to choose a history strategy in the banking context (clients, cards, loans, consents)
Overview: Data history is not just a technical detail, but a contract about time between source and consumer. In banking reporting, the inability to restore yesterday's state of a table makes audit impossible and undermines trust in models. The chapter introduces three history modes (full refresh, incremental, snapshot) as different "promises about time," shows what a correct specification of a model with history should contain, describes minimal validation facts and replay policy. Special attention is paid to late-arriving data, which changes result reproducibility and therefore must be described before writing SQL. The reader will learn to see where history will be needed in the production context and to formulate explicit decisions instead of leaving them "to the discretion of the agent."
Key concepts: Full refresh: A mode in which the table is completely rebuilt from sources on every run. Provides a simple, always-fresh picture "as of today," but does not preserve yesterday's state. Suitable for educational examples, small tables and infrequently changing reference tables. In the production context, it becomes expensive and unsafe if the table is large and depends on other models.
Incremental model: A mode in which dbt adds or updates only changed rows by unique key and date window. Requires an explicit strategy (insert, merge, delete+insert), a unique key and a replay policy. Saves resources, but creates hidden obligations: what to do with late rows, how long to keep the window, whether it is possible to recompute the past.
Snapshot: A mode that stores the change of an attribute over time. Each version of a row receives an action timestamp (dbt_valid_from, dbt_valid_to), which allows answering the question "what did we know yesterday." Suitable for statuses, segments, consents and slowly changing dimensions where audit traceability is important.
Unique key: An attribute or set of attributes that uniquely identifies a row in the model. Without an explicit key, neither updating, nor correct snapshot, nor duplicate check is possible. The key must be stable over time and not depend on changeable business fields.
Event date: A field that defines the point in time to which the fact relates. Differs from the row load date: an operation may occur yesterday and arrive in the system today. The event date is the basis for replay windows, incremental model filters and snapshot SCD logic.
Replay policy: An explicit description of whether and for what period the past can be recomputed. Without it, any model rerun can silently change historical aggregates. The policy answers: which rows are allowed to be updated retroactively, which ones require a PatchSpec and reviewer confirmation.
Late-arriving data: Rows whose event date is older than the last date in the incremental model. Their handling is the main source of hidden semantic drift. Decisions (whether to accept them into aggregates, for what window, what to do with already built reports) cannot be left inside is_incremental() — this is a contractual matter.
Patchspec: A formal request to change an already fixed historical fact. Used when a late correction touches a period outside the replay window and requires reviewer confirmation and explicit description of consequences.
Validation facts: A minimal set of statements proving the correctness of the model's history: repeated dbt build does not change the result without a raw change, the unique key is not duplicated, the event date is not null, the replay window is described, the reviewer manually confirmed the semantics of history.
Reproducibility: A property of a model whereby its result can be restored from a fixed set of inputs and a specification. It is destroyed by any implicit overwriting of the past, including full refresh of a table that requires history.
Important dates: Basel III agreement (2010-2011): Tightening of requirements for banking reporting reproducibility, including risk reporting and client segmentation; increases the significance of snapshot approaches
Entry into force of GDPR (May 25, 2018): Strengthened requirements for recording and auditing client consents, making the history of consents mandatory rather than desirable
IFRS 9 standard (introduced in stages from 2018): Requires tracking credit risk stages (Stage 1/2/3) and delinquency over time, which is impossible without explicit history
Requirements of Federal Law 152-FZ (in force since 2014): Russian law on personal data, obligating the storage of information about processing and consents in historical perspective
Practice exercises: Name: Choosing a history strategy for a source
Problem: You are given a table raw.transactions with columns transaction_id, client_id, transaction_date, amount, status, load_dttm. Over 30 days the table contains about 5 million rows, the average volume of daily new operations is 150,000. The operation status can change retroactively (for example, from PENDING to CONFIRMED or DECLINED) within 14 days from the operation date. Determine a suitable history strategy (full refresh, incremental or snapshot), write down the unique key, event date, replay policy and rules for handling late-arriving data.
Solution: 1. We choose an incremental model, because full refresh on 5 million rows is too expensive, and snapshot is redundant — we need current operation statuses, not the history of their changes. 2. The unique key is transaction_id (stable, does not depend on status). 3. The event date is transaction_date, not load_dttm. 4. Replay policy: rows whose transaction_date falls within the last 14 days from the current date are allowed to be updated; for older rows, a PatchSpec is required. 5. Late-arriving data: if load_dttm is today's and transaction_date is older than 14 days, the row is accepted, but aggregates for that day are not recomputed without a reviewer. 6. Incremental strategy: merge on transaction_id, filter in is_incremental() on transaction_date >= current_date - 14. 7. Validation facts: a repeated dbt build does not change the final table without new raws, the uniqueness of transaction_id is maintained, transaction_date is not null.
Complexity: intermediate
Name: Composing a specification for a model with history
Problem: Rewrite a poorly formulated specification into a good one. Poor formulation: "Make incremental loading for the client_segments model." Additional context: the model stores the client segment (MASSE, VIP, STANDARD, RISK); the segment is reviewed quarterly, but may change more often upon major events (default, regulatory complaint).
Solution: Poor formulation: "Make incremental loading." Good formulation: "The client_segments model uses client_id as the unique key and segment_effective_date as the event date. The client segment is determined for each segment_effective_date. Changes within a quarter (default, regulatory complaint) may update the client record retroactively, but only within the current quarter. Changes for past quarters require a PatchSpec and reviewer confirmation. For segment audit over time, use the snapshot client_segments_history with fields dbt_valid_from and dbt_valid_to, where the snapshot key is client_id, the strategy is timestamp. The semantics of history are confirmed by the head of risk reporting before the first release."
Complexity: intermediate
Name: Diagnosing a history problem
Problem: The risk reporting team complains: "Yesterday the client was in the VIP segment on the dashboard, today they are not." The client_segments model runs in full refresh mode. What happened? How should the model have been structured?
Solution: What happened: full refresh rebuilt the table, and the client's current segment changed (for example, the client stopped meeting VIP criteria). The history of the previous state was not preserved. The dashboard shows only the current snapshot and cannot answer the question "what did we know yesterday." Solution: the client_segments model should have either a snapshot (with fields dbt_valid_from, dbt_valid_to and key client_id) or an incremental mode with full history of changes. For analytical segment marts, the correct choice is snapshot, because the segment changes rarely, but each change is significant for audit. The dashboard should refer to the mart with a filter by dbt_valid_from <= report_date < dbt_valid_to, not to the current table. Additionally, the replay policy must be described in the specification: whether it is possible to retroactively change the segment for past periods, or whether this requires a PatchSpec.
Complexity: intermediate
Name: Qwen query for auditing models
Problem: Formulate a query to the Qwen assistant that will find in your dbt project all models where history is required but not described. Formulate what the assistant should return for each such model so that the result can be turned into tasks.
Solution: Query text: "Read specs and marts. Find models where history is needed, incremental strategy or snapshot. For each, return the key, event date, replay policy and open questions. Do not change SQL." The assistant should return a table of the form: model_name | unique_key | event_date | history_strategy | replay_window | late_data_policy | open_questions. For example, for client_segments: client_id | segment_effective_date | snapshot | N/A (current quarter) | changes in current quarter — yes, earlier — only via PatchSpec | "snapshot strategy not described", "no validation fact about reproducibility". Based on this response, a task backlog is formed: add a specification, implement snapshot, write tests for key duplicates and non-null event date, agree on the policy with the model owner.
Complexity: beginner
Case studies: Name: Loss of client consent history in Open API
Scenario: A large Russian bank implemented Open API for partner services (insurance, brokers, marketplaces). Each partner request requires an active client consent for data transfer. Consents are stored in the consents model, which runs in full refresh mode: each run completely rebuilds the table, and only the latest status (active or revoked) remains in it.
Challenge: In June 2024, a client complained to the Central Bank that a partner broker had transferred their data to an insurance company without permission. The bank attempted to restore history: "what consent was active on May 15, when the partner sent the request?" It turned out that the consents table for May 15 had already been rebuilt — the current version shows only today's status. An audit log exists, but it is not linked to the model via unique key and event date. The regulator requested restoration of history for 6 months, but the data does not exist. In parallel, the risk department discovered that some operations in March had the status "consent active," although in fact the client had revoked it in February — full refresh replaced "revoked" with "active" for all past periods. Reporting reproducibility is broken, audit is impossible.
Solution: The data team implemented the following changes over 6 weeks. 1) A snapshot model consents_history was created with unique key (client_id, partner_id, scope) and timestamp strategy on the updated_at field; the table now stores pairs of dbt_valid_from / dbt_valid_to. 2) The current consents mart remained full refresh, but is supplied with an explicit mark "do not use for past audit." 3) A specification was written for each model with history: key, event date (consent_updated_at), replay policy (changes are active retroactively within 30 days, beyond that only via PatchSpec with compliance confirmation), rules for handling late-arriving data (if updated_at < 30 days — accept; if older — write to exceptions, do not recompute aggregates). 4) A test for key uniqueness in the snapshot and a test "repeated dbt build does not change the result without new raws" were introduced. 5) Restoration of history for 6 months was performed from the audit log with manual reconciliation across 200 random clients; restoration accuracy was 99.4% (discrepancies in 0.6% of cases were explained and documented).
Result: The bank provided the regulator with the restored consent history and a detailed report on the storage architecture. Penalties were mitigated through transparency and prompt response. After the incident, the team codified a regulation: "no model containing a client status or segment is released without an explicit history policy and validation facts." Over the next quarter, a similar scheme was applied to the client_segments, credit_risk_stages, card_status models — everywhere full refresh with history loss had previously been used. The average time to restore history upon a regulator request was reduced from 3 weeks to 2 days.
Lessons learned: The materialized='incremental' mode alone does not solve history — it only changes the way of writing. The meaning of history is set by keys, event dates and replay policy
Full refresh is permissible only for models where the past does not matter; for everything concerning statuses, consents and segments, a snapshot or incremental model with explicit history is needed
Late-arriving data is a contractual, not a technical problem: its handling cannot be left "to the discretion of is_incremental()"; the window and exceptions must be explicitly described
Reporting reproducibility is a property of the system as a whole; even if individual tables are correct, loss of the connection between them through time makes audit impossible
The specification of a model with history must answer 5 questions: key, event date, possibility of recomputation, late data policy, the team proving the safety of replay
Related concepts: Snapshot
Incremental model
Late-arriving data
Replay policy
Validation facts
Specification of a model with history
Name: Late-arriving payment and broken aggregates
Scenario: A retail bank calculates daily aggregates on card operations: spending volume, average check, share of declines. Aggregates are built by the incremental model daily_card_aggr, which at 02:00 takes all operations for yesterday and updates the corresponding aggregate. The unique key is operation_id, the event date is operation_date, the replay window is 3 days.
Challenge: On the morning of June 10, the fraud monitoring service discovered that the report for June 5 was missing a large operation (180,000 ₽) that was processed only on June 9. The reason is that on the night of June 5 to 6, processing returned a delayed operation, and the dbt build on June 6 no longer recomputed the aggregate for June 5 (outside the 3-day window). The fraud monitoring service made a decision based on incomplete data. In parallel, a similar situation arose with an operation on June 7 that arrived on the 10th: the aggregate for the 7th was updated, while for the 8th and 9th it remained without neighboring contextual operations. The team could not explain whether the aggregates for the past week were "correct" in principle.
Solution: The team revised the replay policy. 1) The replay window was increased from 3 to 7 days for large operations (amount > 100,000 ₽), and a branch was added to the dbt model: "if amount > threshold, extend the window to 7 days." 2) For all late-arriving operations outside the window, a separate staging layer late_operations was created, which does not edit aggregates but goes into the exceptions report. 3) The model specification was supplemented with a clause: "operations with an event date older than 7 days may be included in the aggregate only through a PatchSpec indicating the reason and agreed with the analyst." 4) A daily test was introduced: "the sum of operations in raw for the last 7 days equals the sum of operations in the aggregate for the same 7 days," which catches discrepancies before consumers notice them. 5) The fraud monitoring service was given priority access to late_operations.
Result: Over the next 3 months, 47 cases of late-arriving operations older than 7 days were recorded. All cases were processed according to the regulation: 38 were accepted into aggregates via PatchSpec, 9 were recognized as processing errors and rejected. The fraud monitoring service stopped complaining about "lost" operations and began to trust the reports. The average response time to a major anomaly was reduced from 24 hours to 6 hours. The team formulated a principle: "the replay window must be agreed with the maximum reasonable data delay in the source, not with the convenience of dbt build."
Lessons learned: The size of the replay window is a contractual decision, not a technical parameter; it must reflect real delays in sources, not the desire to save time on build
Late-arriving data must not be "silently discarded," but explicitly recorded in a separate layer and communicated to reporting owners
PatchSpec is not a "crutch for errors," but a normal mechanism for making retroactive changes with explicit reviewer responsibility
Validation facts must include reconciliations between raw and marts, not only tests for uniqueness and non-null values
The semantics of history (whether it is possible to change the aggregate for a past day) is more important than the way of writing (merge, insert, snapshot)
Related concepts: Incremental model
Late-arriving data
Replay policy
PatchSpec
Validation facts
Study tips: Start with the table of three history modes (full refresh, incremental, snapshot) and for each mode formulate the "promise about time" in one phrase — this will help you quickly choose a strategy in practice
For each model in your project, try to answer the 5 specification questions (key, event date, whether the past can be recomputed, what to do with late data, which team proves the safety of replay) — if you cannot answer at least one question, history is not described
Do not confuse the event date and the load date (load_dttm): these are two different values, and the incremental model must filter by the event date, otherwise you will lose late-arriving data
Formulate a query to Qwen (or any LLM assistant) on your project following the template from the chapter — this will give a quick slice of models where history is not described and turn an abstract topic into a concrete backlog
Maintain a separate document "History Policy" in your project following the template from the chapter (Client Attributes, Card Operations, Loans, Open API consents, Replay and Late Data) and update it with every release of a new model with a status or segment
Write validation facts as executable checks, not as a paragraph of text: a test for key uniqueness, a test "repeated dbt build == dbt build," reconciliation of sums between raw and marts over a window
When choosing between incremental and snapshot, think not about the size of the table, but about consumer questions: if they ask "how many now" — incremental, if "what was yesterday" — snapshot
A typical trap: set materialized='incremental' and assume history is solved. Always ask the question "what will happen if an update for a row from the past month comes into raw?" — if the answer is not described, history is not solved
Learn to read specifications critically: the formulation "make incremental loading" is a red flag requiring clarification of all 5 points
To prepare for control questions, go through each of them with a real example from your project: incremental vs snapshot, facts of replay safety, contract vs SQL for late data
Additional resources: dbt documentation on incremental models: https://docs.getdbt.com/docs/build/incremental-models — official guide on configuring is_incremental(), merge/insert/delete+insert strategies, working with unique key and date windows
dbt documentation on snapshots: https://docs.getdbt.com/docs/build/snapshots — guide on SCD Type 2, timestamp and check strategies, dbt_valid_from / dbt_valid_to fields, snapshot dependency graph
Book 'Fundamentals of Data Engineering' (Joe Reis, Matt Housley): Chapter on incremental loading and CDC, including patterns for late-arriving data and replay windows
Book 'Data Quality: The Accuracy Dimension' (Jack Olson): Chapters on data reproducibility and audit, applicable to banking reporting
IFRS 9 standard: https://www.ifrs.org — official text of the standard explaining requirements for credit risk stage and delinquency history
Basel III agreement: Documents of the Basel Committee on Banking Supervision (BCBS) — requirements for risk reporting reproducibility
Dbt-utils package: https://github.com/dbt-labs/dbt-utils — ready-made tests and macros, including surrogate_key, non-null value checks and reconciliations between models
dbt labs blog 'How We Structure Our dbt Projects': Section on staging / intermediate / marts layers and description of contracts between layers, including history
Summary: Data history is a contract about time, not a technical implementation detail. The chapter introduces three modes (full refresh, incremental, snapshot) as different "promises about time": rebuild the current, add/update what changed, preserve the attribute change. The choice between them must be recorded in the specification before SQL: which key defines the row, which field is the event date, whether the past can be recomputed, what to do with late-arriving data, which team proves the safety of replay. Late-arriving data is the main source of hidden semantic drift, and their handling belongs to the contract, not to is_incremental(). In the banking context, history is mandatory for client attributes and segments, card operations, credit stages and Open API consents. Minimal validation facts (repeated dbt build does not change the result, key uniqueness, non-null event date, described window, reviewer confirmation) turn history from an intention into a measurable property of the model. The main practical takeaway: a "History Policy" document must exist in every project and explicitly fix the strategy for each entity, even if the first version remains a simple full refresh.