ModelingGENERALFORMAT-SPECIFICSCALE-SPECIFIC

Snapshot Tables

Capture the state of every entity at the end of every period. account_balance_daily answers "what was it on the 14th" with a lookup instead of a fold over all history.

What actually happens How to build it Can I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

How do you answer "what was the state of everything on a given date" without replaying every change that ever happened?

Who needs this

Finance reporting balances at a period end, a product team measuring how many accounts sat in each status yesterday, and a data scientist building features that must reflect only what was known at a point in time. All three want a state as of a date, and none of them wants to write a fold over an event log to get it.

What one row is

One row is one entity in one period — one account on one day, one subscription in one month, one inventory item at one week end. The period is part of the key, which is what makes the table grow with time rather than with activity (Grain: What Does One Row Represent?).

The obvious build

Do not build one. Keep the transaction facts and compute state on demand: sum every posting up to the requested date and you have the balance. It is exactly correct, requires no extra table, and never goes stale.

Why it breaks

The fold scans all history for every query. Answering "balance on 14 March" reads every posting since the account opened, and a dashboard showing twelve month-ends does it twelve times (Scan Cost).

How it breaks with real data

The fold scans all history for every query. Answering "balance on 14 March" reads every posting since the account opened, and a dashboard showing twelve month-ends does it twelve times (Scan Cost).
Not every state has a transaction behind it. A subscription's status changes by an administrative action that produced no event, and an inventory level changes by a stock count that reconciles rather than posts. There is nothing to fold (Event vs Snapshot Modeling).
A late-arriving correction changes a balance that was already reported for a closed period, because the fold recomputes from the source every time. The March figure moves in April, correctly and unhelpfully (Late-Arriving Data).
A machine-learning feature computed by folding history leaks information: the fold uses whatever the source says today, including facts that were not known at the time the label was assigned (Feature Pipelines).
Nobody can answer "how many accounts were dormant at the end of each of the last twenty-four months" in under a minute, so the question stops being asked.

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

A periodic snapshot writes one row per entity per period, capturing the state at the period boundary. The table is append-only by partition: yesterday's partition is written once and never changes, which is what makes a past period reproducible.
It converts a fold into a lookup. "Balance on 14 March" goes from summing every posting since account opening to reading one partition with a predicate. The work is done once per period at load time instead of once per query (Partition Pruning).
Its size is entities times periods, and it grows whether or not anything changed. A million accounts snapshotted daily produce a million rows a day forever, and the growth is independent of business activity — which is a very different cost curve from a transaction fact (Storage Lifecycle).
The measures in a snapshot are usually semi-additive: a balance adds across accounts and is meaningless added across days. This is the most common source of wrong numbers from snapshot tables and it is the reason the fact-types lesson insists on the distinction (Fact Tables).
The period is the resolution limit. Two changes between snapshots collapse into one, and a change that was reverted before the next snapshot never happened as far as the table is concerned. A snapshot is a sampling of state, and sampling loses what happens between samples.
Density is a design choice. A dense snapshot has a row for every entity every period, which makes joins and gap-free reporting trivial. A sparse one writes a row only when something changed, which is far smaller and makes every query a "most recent row at or before this date" problem — cheaper storage, harder queries (Window Functions).

account_balance_daily

The canonical example is a balance, because a balance is the clearest case of a state that no single transaction describes. It is the accumulated result of every posting since the account opened, and asking for it on an arbitrary date is asking for a fold.

The snapshot does that fold once per day, for every account, and writes the result. Afterwards, "what was the balance on 14 March" is a partition read with a predicate, and "how did total deposits move across twenty-four month ends" is twenty-four partition reads rather than twenty-four folds over all history.

The columns worth noticing are the two timestamps. snapshot_date is the period the state refers to; captured_at is when the job actually ran. They are usually close and occasionally are not, and when a report looks wrong the gap between them is often the explanation.

A dense daily snapshot, and the query it makes cheap

1CREATE TABLE account_balance_daily (
2  snapshot_date   DATE    NOT NULL,   -- the period this state refers to
3  account_key     BIGINT  NOT NULL,
4  customer_key    BIGINT  NOT NULL,
5  balance         DECIMAL(18,2) NOT NULL,  -- SEMI-ADDITIVE: sums across
6                                           -- accounts, NOT across days
7  available_credit DECIMAL(18,2),          -- semi-additive
8  status          VARCHAR,                 -- 'active','dormant','frozen'
9  days_since_last_txn INT,
10  captured_at     TIMESTAMP NOT NULL   -- when the job ran, not what it means
11)
12PARTITION BY (snapshot_date);
13-- grain: ONE ROW PER ACCOUNT PER DAY.
14-- size:  accounts x days, growing on a schedule, not with activity.
15 
16-- The question the snapshot exists for: one partition, one predicate.
17SELECT SUM(balance) AS total_balance          -- correct: sums across accounts
18FROM   account_balance_daily
19WHERE  snapshot_date = DATE '2026-03-14';
20 
21-- Twenty-four month-ends: 24 partitions, not 24 folds over all history.
22SELECT snapshot_date, SUM(balance)
23FROM   account_balance_daily
24WHERE  snapshot_date IN (SELECT month_end_date FROM dim_date WHERE ...)
25GROUP  BY 1;
26 
27-- WRONG, and it is the query a BI tool offers by default:
28SELECT SUM(balance) FROM account_balance_daily
29WHERE  snapshot_date BETWEEN '2026-03-01' AND '2026-03-31';
30--   Returns roughly 31x the balance. Each account's balance is counted
31--   once per day it was observed. There is no error and the number is
32--   large, stable and plausible ([[fact-tables]]).
33 
34-- The same question WITHOUT a snapshot, folded from postings:
35SELECT SUM(amount)
36FROM   fct_postings
37WHERE  posted_at < DATE '2026-03-15';
38--   Exactly correct, and it reads every posting ever made, on every run,
39--   for every date anyone asks about ([[scan-cost]]).

The third query is the trap and the reason balance needs a semi-additive label wherever consumers see it. The fourth is what the snapshot replaces — and it is worth keeping the postings anyway, because they are the only thing that can rebuild a snapshot partition that was lost (Keeping Raw History: The Recovery Position and the Liability).

The layout is the whole performance story

GENERALPartition pruning on the snapshot date is the mechanism in every columnar system, but how it is expressed differs: Hive-style directory partitioning, warehouse-native partition metadata and table-format manifest statistics all prune, and only the first is visible as a directory path like the one shown here.

A snapshot table is only cheap to query if the engine can skip the periods you did not ask for. Partitioned by snapshot_date, a point-in-time query reads one partition. Not partitioned, the same query reads every day ever captured, which is strictly worse than the fold it was built to replace.

This is the most common way a snapshot disappoints. The modelling was right, the table is correct, and the query is slow and expensive because the layout does not match the access pattern (Physical Data Layout).

The layout below is the same table under one query. Read the read column: three partitions out of a year, because the query asked for three month-ends. That ratio is the entire value proposition, and it evaporates if the predicate is on captured_at instead of snapshot_date, or if the date is wrapped in a function the engine cannot push down (Predicate Pushdown).

account_balance_daily partitioned by snapshot_date

SELECT snapshot_date, SUM(balance) FROM account_balance_daily WHERE snapshot_date IN ('2026-01-31','2026-02-28','2026-03-31') GROUP BY 1

snapshot_date=2026-01-30/one row per account · 8 files · skipped
snapshot_date=2026-01-31/one row per account · 8 files · read
snapshot_date=2026-02-28/one row per account · 8 files · read
snapshot_date=2026-03-14/one row per account · 8 files · skipped
snapshot_date=2026-03-31/one row per account · 8 files · read
… 361 further daily partitionsone row per account each · 8 files · skipped

3 of 6 shown paths are read.

Three partitions read out of a year of them. The same query against an unpartitioned snapshot reads every row the table has ever held — which costs more than folding the transaction facts would have, and is why a snapshot without a matching layout is a table that pays for itself twice.

What a snapshot costs to keep

Snapshot tables are the one place in a warehouse where storage grows on a schedule rather than in response to anything. A million accounts snapshotted daily produce a million rows tomorrow whether or not a single balance moved, and they will do so every day until somebody sets a retention policy.

That decoupling from activity is what makes the cost surprising. Every other table in the platform grows when the business does; this one grows when the calendar does, and it therefore does not show up in any conversation about scaling for growth.

The two levers are density and retention, and they act on different terms of the same product. Sparse capture reduces rows per period; roll-up reduces periods retained. Most platforms need both eventually, and applying either after the table is enormous is a migration rather than a config change.

What drives the cost of a periodic snapshot, relative to each other

Entity count × periods retained

The dominant term and a pure product. Doubling either doubles the table. It grows on a schedule regardless of business activity, which is why it is rarely forecast.

Period granularity

Daily versus month-end is roughly a thirtyfold difference in rows for the same coverage. Choosing daily by default and never revisiting it is the single largest avoidable cost here.

Row width

Every column is stored once per entity per period. A wide snapshot multiplies the width penalty by the period count, so columns nobody queries are far more expensive here than on a transaction fact.

Density: rows for entities that did not change

In a mostly static population the majority of rows restate yesterday. Sparse capture removes them and moves the cost into every point-in-time query, which then needs a "latest at or before" resolution.

Compute per load

Proportional to entity count rather than to change volume — the job does the same work on a quiet day as on a busy one.

Query scan, given partition pruning

Small when the layout matches the access pattern, and unbounded when it does not. This driver is a step function controlled by the previous section rather than by anything in this list.

Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.

Relative weights for a dense daily snapshot, shown to establish an ordering rather than as measurements. The teaching is the first two rows: the cost is a product of two numbers that are both usually chosen once, by default, and never revisited.

How to build it

Most important first.

Partition by the snapshot date and make it the first predicate every query uses. A snapshot table without partition pruning is the worst of both worlds: it costs the storage of a snapshot and scans like a fold (Partitioning).
Write each period's partition once, atomically, and never mutate it. Immutable past partitions are what make a closed period reproducible (Atomic Publish).
Record the state, the period boundary the state refers to, and the time the snapshot was taken. When those two differ — a snapshot job that ran late — you need both to explain the numbers (Event Time).
Label semi-additive measures explicitly and expose the correct time aggregation in the metrics layer, so SUM over days is not the default thing a BI tool offers (The Metrics Layer).
Choose density deliberately. Dense for entity counts under a few million and daily periods; sparse plus a "latest at or before" resolution when the entity count or the retention makes dense impractical.
Set a retention and roll up rather than keeping every daily snapshot forever. Daily for a recent window, month-end beyond it, is the usual shape and it must be a decision rather than a default (Data Retention).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A snapshot guarantees the state as the pipeline observed it at the period boundary, which is not the same as the state that was true at that instant if the source reports changes late (Late-Arriving Data).
It guarantees reproducibility of a closed period, provided past partitions are never rewritten. The moment a backfill mutates an old partition, that guarantee is gone and nobody is told (What Backfills Break).
It explicitly guarantees nothing about what happened between snapshots. A status that flipped and flipped back is invisible, and no query against the table can detect that it happened.
It does not guarantee additivity across time. Summing a balance column over a date range is a valid SQL expression and a meaningless number (Fact Tables).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

Check row count per partition against the expected entity count. A dense snapshot should have almost exactly the same number of rows each period, so a drop is a partial load and a rise is a duplicate — both immediately visible (Volume Anomalies).
Check continuity: for a sample of entities, assert that a row exists in every period between their first and last appearance. A gap in a dense snapshot is an entity that appears to have ceased existing for a day (Data Tests).
Reconcile the snapshot against a fold of the transaction facts for a closed period. If account_balance_daily on the 14th does not equal opening balance plus postings through the 14th, one of the two is wrong and you now know to look (Reconciliation).
What these miss: everything that happened between two snapshots. A status that changed twice in a day, or changed and reverted, passes every check because the table is internally perfect — it simply never observed the intermediate state. Only an event stream can answer that, which is why snapshots and events are complements rather than alternatives (Event vs Snapshot Modeling).
They also miss a silently mutated past partition. Row counts and continuity look identical after a rewrite, so reproducibility needs its own guard — a checksum per closed partition, compared on a schedule (Validating a Backfill Before You Publish).

Freshness

A snapshot is exactly as fresh as its period, by construction. A daily snapshot cannot answer an intraday question no matter how fast the job runs, and consumers routinely misread a snapshot dashboard as live (The Freshness SLO).
A snapshot taken at a fixed wall-clock time captures whatever the source had at that moment, so upstream lateness shifts what "end of day" means without changing anything visible in the table.
Late corrections force a choice: restate the affected partition, which breaks reproducibility, or carry the correction forward into the current period, which keeps history stable and makes the two periods inconsistent. Both are defensible and the choice must be documented (Reprocessing vs Retrying).

When the schema or meaning changes

Adding a column means history has it as null unless you backfill from retained source state, and backfilling a snapshot requires the source state as it was — which usually does not exist (Keeping Raw History: The Recovery Position and the Liability).
Changing the definition of a snapshotted measure creates a discontinuity at the change date with no schema signature, and every chart spanning the boundary is comparing two different things (Semantic Changes).
Changing the period — daily to weekly, or the snapshot hour — changes what every row means. It is a grain change and deserves a new table rather than a quiet cutover (Grain: What Does One Row Represent?).
Adding entities is free; the table simply gets wider per period. Removing an entity type retroactively is not, because the historical partitions still contain it.

How to re-run this safely

A snapshot partition can be rebuilt only if the source state for that instant is recoverable. For most sources it is not, which makes a snapshot one of the few artefacts in a warehouse that is genuinely irreplaceable (Keeping Raw History: The Recovery Position and the Liability).
Where the state is derivable from transaction facts, a partition can be recomputed as a fold. That is the argument for keeping the transaction facts even after the snapshot exists (Event vs Snapshot Modeling).
Never repair a snapshot by mutating a closed partition without announcing it. The whole value of the table is that a past period does not change, and a silent rewrite converts it into a table that merely looks reproducible (Data Incidents).

What can go wrong

Failure modes

Semi-additive measures summed across dates, producing totals proportional to the number of days selected (Fact Tables).
A snapshot job that missed a day, leaving a gap that queries interpolate over without noticing (Missing Rows).
A backfill that rewrote historical partitions with today's source state, silently restating every closed period (What Backfills Break).
A dense snapshot whose storage growth was never modelled, becoming one of the largest tables in the warehouse without anyone deciding it should be (Storage Lifecycle).
The mitigation failing: a retention policy that rolls daily snapshots into month-end but runs before the current month closes, deleting the days that a month-to-date report needs.

Misreads

"A snapshot is a backup." A backup restores a system. A snapshot is a modelled, queryable observation of state at a boundary, usually of a subset of columns, and it is not restorable to anything.
"We can always rebuild the snapshot later." Only if the source state for that instant is recoverable, which for most sources it is not. The snapshot is often the only record that a state existed (Keeping Raw History: The Recovery Position and the Liability).
"Summing the daily balance gives the monthly total." It gives roughly thirty times the balance. Balances are semi-additive and the correct monthly figure is a closing or average value (Fact Tables).
"Snapshots replace event history." They answer state questions and cannot answer transition questions. Most platforms need both, and the events are also what lets a snapshot be rebuilt (Event vs Snapshot Modeling).
"Daily is the right period." Daily is the default, not the answer. The right period is the one the questions need, and month-end is sufficient for a surprising share of financial reporting.

Privacy, retention and access

A snapshot multiplies personal data by the number of periods retained: one customer's address held daily for three years is over a thousand copies of it, each of which a deletion request must reach (Deletion Requests).
Retention on a snapshot is a modelling decision with a legal consequence, and it is usually made implicitly by never setting one (Data Retention).
A daily snapshot of status attributes is a longitudinal behavioural record, which is more sensitive than any single row in it and often warrants a stricter classification than the source table (Data Classification).

Operating it

How you see it in production

Rows per partition per period, plotted over time. For a dense snapshot this is nearly a flat line and every deviation is meaningful (Pipeline Metrics).
Snapshot completion time against the period boundary, so a job that ran hours late is visible as a reason numbers look odd (Freshness Monitoring).
Cumulative storage of the snapshot table against the transaction facts it derives from. The crossover point is a useful moment to revisit retention (Cost Attribution).
A checksum per closed partition, recomputed on a schedule, so a silent rewrite of history is detectable (The Data Quality Dashboard).

What changes at 10x and 100x

At 10x entities, dense daily snapshots start to dominate warehouse storage and the roll-up policy stops being optional.
At 100x, sparse snapshots plus a "latest at or before" resolution become the norm, which trades cheap storage for more complex queries and a window function in every point-in-time lookup (Window Functions).
Longer retention scales linearly and forever. A daily snapshot kept for seven years is 2,555 copies of the entity population, and nobody decided that — it is what happens when no retention is set (Data Retention).

What drives cost here

Storage is entities times periods times row width, and it grows on a fixed schedule regardless of activity. This is the one cost curve in analytics that is completely decoupled from business volume (Storage Lifecycle).
Query cost is low if the snapshot date is the partition key and queries filter on it. Without pruning, a point-in-time query scans every period ever taken (Partition Pruning).
Compute per load is proportional to entity count, not to change volume, so a snapshot of a mostly static population is almost entirely wasted work — which is the argument for sparse snapshots (Compute Waste).
Rolling up old daily snapshots to month-end is the highest-leverage cost action available here and is usually deferred until the table is already enormous (Storage Lifecycle).

What this approach costs

A snapshot buys cheap point-in-time answers and reproducible closed periods, and costs storage proportional to entities times periods plus a permanent loss of everything that happened between samples.
Dense buys simple queries and gap-free reporting and costs rows for entities that did not change. Sparse inverts both.
Immutable past partitions buy reproducibility and cost the ability to correct history quietly — which is a feature, and it does mean corrections become announcements.

Modeling lab — one grain, ten questions

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

Modeling lab — one grain, ten questions

Pick the grain of the fact table. The questions do not change; what the table can honestly say about them does.

Fact grain

The transaction and its total. The individual products are gone.

Answered

With care

Confidently wrong

Unanswerable

4 of these questions get an answer at this grain that is wrong, and none of them raise an error. That is the whole difficulty: the unanswerable ones announce themselves, and these do not.

Business question	At this grain	Why
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice.	answered	The measure is additive at this grain and each order is counted once.
What is the average order value? Wants: One order. An average over lines answers a different question entirely.	answered	The denominator is orders, which is exactly what one row is.
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed.	WRONG no history	With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces.
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain.	answered	Refunds net into the measure, or sit beside it as a signed fact at the same grain.
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained.	WRONG	Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis.
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month.	WRONG	Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers.
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session.	unanswerable	No source system emits a session. Without a session window over events there is no denominator to divide by.
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets.	unanswerable	The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away.
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed.	with care	The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00.
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied.	WRONG no history	Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is.

answeredThe grain is the thing the question is about.

with careIt works, and there is one specific way to get it wrong.

WRONGIt returns a plausible number that is not the answer, and nothing raises.

unanswerableThe resolution needed was aggregated away. No query recovers it.

SIMPLIFIEDA single fact table against ten questions. A real model has several, and a question that one answers badly another may answer exactly — which is the argument for more than one fact table, not for a finer one.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALPeriodic state capture, its entities-times-periods growth curve and the semi-additive trap are properties of the model rather than of any engine, and appear identically in a warehouse table, a partitioned Parquet dataset and a set of daily CSV extracts.
FORMAT-SPECIFICOpen table formats that keep their own snapshot history let you time-travel to a past table state, which answers "what did this table say last Tuesday" — a different question from "what was the balance last Tuesday", and conflating the two is common. Table-format time travel is bounded by metadata retention; a modelled snapshot is bounded by your own retention policy.
SCALE-SPECIFICDense daily snapshots are entirely reasonable below a few million entities and become the largest table in the warehouse above that, at which point sparse capture or a roll-up to month-end stops being an optimisation and becomes a requirement.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasewindow-functions aggregation-and-grouping partitioning-and-sharding mvcc

Securitydata-classification

Observabilityscan-vs-index-performance cardinality

Cloudobject-storage storage-lifecycle

DSAprefix-sum binary-search

Domains that do not exist yet

— DevOps / Production Engineering owns the retention and lifecycle policy that stops a daily snapshot growing forever, and the review that should have required one before the table was created.