D8LooP - Practise Data Engineering

01 · One subscriber, four nightly runs

Run It Four Times, Watch History Accumulate

delete it, re-run it, and you get the same table back. A snapshot is a function of its inputs AND of when you happened to look at them. It is the one node in a dbt project whose contents encode the history of your cron schedule — and almost everything else in this chapter follows from that one asymmetry.

⏱ 4 min · Topic 1 of 10

wavelength_app.subscriptions overwrites a subscriber's row in place. When S-106 upgrades from monthly to annual, the old plan is not archived anywhere — it is gone from the source the instant the UPDATE commits. A snapshot is the one dbt node that keeps it.

Step the four nightly 06:00 UTC runs below. Watch the source mutate, watch the run fire, watch the table grow 6 → 7 → 9 → 10.

Four nightly runs of `dbt build` at 06:00 UTC — step the clock

run fires at2026-07-19 06:00 UTCsnapshots.snap_subscriptions

rows in the snapshot0 → 6

open rows (dbt_valid_to is null)6one per live key — at most, never guaranteed exactly

wavelength_app.subscriptions — S-106 right now

the source row, as the run sees it

subscription_id  S-106
listener_id      L-9006
plan             monthly
amount_usd       8.00
status           active
updated_at       2026-07-11 14:22

overwritten in place since the previous run

(nothing yet — this is the first run, and the snapshot table does not exist)

the run

dbt build — run 1

$ dbt build -s snap_subscriptions
06:00:01 Running with dbt=1.12.0
06:00:02 1 of 1 START snapshot snapshots.snap_subscriptions ............ [RUN]
06:00:04 1 of 1 OK snapshotted snapshots.snap_subscriptions ........... [SUCCESS 6 in 4.12s]4.12s
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
# the table did not exist — 6 source rows inserted, 0 updated, dbt_valid_to NULL on all 6

snapshots.snap_subscriptions after run 1 — 6 rows

subscription_id	listener_id	plan	amount_usd	status	dbt_updated_at	dbt_valid_from	dbt_valid_to	this run
S-101	L-9001	monthly	8.00	active	2026-05-14 09:31	2026-05-14 09:31	NULL	INSERTED
S-102	L-9002	annual	80.00	active	2026-06-01 07:44	2026-06-01 07:44	NULL	INSERTED
S-103	L-9003	monthly	8.00	canceled	2026-06-25 16:12	2026-06-25 16:12	NULL	INSERTED
S-104	L-9004	monthly	8.00	active	2026-07-02 12:05	2026-07-02 12:05	NULL	INSERTED
S-105	L-9005	annual	80.00	active	2026-07-03 15:48	2026-07-03 15:48	NULL	INSERTED
S-106	L-9006	monthly	8.00	active	2026-07-11 14:22	2026-07-11 14:22	NULL	INSERTED

Why this run wrote what it wroteInitial capture. There is nothing to compare against, so every source row is an insert: dbt copies updated_at into BOTH dbt_updated_at and dbt_valid_from, and leaves dbt_valid_to NULL. S-106 is still monthly · $8.00 here — its upgrade is four hours away, at 10:05 this morning. This run is also the only moment the whole table shares one shape.

snapshots/snap_subscriptions.yml — the whole fileworked example

YAML

Input data

wavelength_app.subscriptions — the source, at 2026-07-22 06:00 (6 rows on every one of these four runs)6 rows

subscription_id	listener_id	plan	amount_usd	status	updated_at
S-101	L-9001	annual	80.00	active	2026-07-20 11:50
S-102	L-9002	annual	80.00	active	2026-06-01 07:44
S-103	L-9003	monthly	8.00	canceled	2026-06-25 16:12
S-104	L-9004	monthly	8.00	active	2026-07-02 12:05
S-105	L-9005	annual	80.00	active	2026-07-03 15:48
S-106	L-9006	annual	72.00	canceled	2026-07-21 19:30

The source never grows across these four runs — six rows in, six rows out. Every change S-101 and S-106 made is invisible here — this table only ever tells you about now.

snapshots:
  - name: snap_subscriptions
    relation: source('wavelength_app', 'subscriptions')
    config:
      schema: snapshots
      unique_key: subscription_id
      strategy: timestamp
      updated_at: updated_at

analytics.snapshots.snap_subscriptions after run 4 — 10 rows

subscription_id	plan	amount_usd	status	dbt_updated_at	dbt_valid_from	dbt_valid_to
S-101	monthly	8.00	active	2026-05-14 09:31	2026-05-14 09:31	2026-07-20 11:50
S-101	annual	80.00	active	2026-07-20 11:50	2026-07-20 11:50	NULL
S-102	annual	80.00	active	2026-06-01 07:44	2026-06-01 07:44	NULL
S-103	monthly	8.00	canceled	2026-06-25 16:12	2026-06-25 16:12	NULL
S-104	monthly	8.00	active	2026-07-02 12:05	2026-07-02 12:05	NULL
S-105	annual	80.00	active	2026-07-03 15:48	2026-07-03 15:48	NULL
S-106	monthly	8.00	active	2026-07-11 14:22	2026-07-11 14:22	2026-07-19 21:40
S-106	annual	72.00	active	2026-07-19 21:40	2026-07-19 21:40	2026-07-20 08:15
S-106	annual	72.00	active	2026-07-20 08:15	2026-07-20 08:15	2026-07-21 19:30
S-106	annual	72.00	canceled	2026-07-21 19:30	2026-07-21 19:30	NULL

Six source rows became ten. S-101 has 2 versions, S-102 through S-105 have 1 each, S-106 has 4. Read the timestamps for what is missing: 06:00, the hour all four runs fired at, appears nowhere.

Eight lines, and one of the highest-leverage files in a dbt project. It reads the raw source Chapter 5 declared, not a model; it lands in its own schema; and it keys on subscription_id. This chapter adds one node to the Wavelength graph — the graph Chapter 3 introduced at 14 nodes and Chapter 5 grew to 16 gains a 17th — and it is a root node, because a snapshot that reads a source has no model parents: nothing has to be built before it.

Core mental model

An incremental model is a function of its inputs: delete it, re-run it, and you get the same table back. A snapshot is a function of its inputs AND of when you happened to look at them. It is the one node in a dbt project whose contents encode the history of your cron schedule — and almost everything else in this chapter follows from that one asymmetry.

Why it matters

Every question that starts with "as of" is unanswerable against a table that overwrites itself. What plan was S-106 on when it churned? What did we charge S-101 in June? Which subscribers were on the discounted annual tier during the Q3 promotion? None of those can be reconstructed later — the answer either got captured while it was true, or it did not exist to capture. A snapshot is the cheapest possible insurance against a question nobody has asked yet.

snapshot: A dbt resource that reads a mutable relation on a schedule and writes an append-mostly history table: one row per (key, version), with a validity interval attached. It is the dbt implementation of SCD Type 2 and nothing else.
The four generated columns: dbt_scd_id (the row's surrogate key), dbt_updated_at, dbt_valid_from and dbt_valid_to. A fifth, dbt_is_deleted, appears only under hard_deletes: new_record. Section 3 derives each one from the SQL that produces it.
Half-open intervals: A version is valid from dbt_valid_from INCLUSIVE to dbt_valid_to EXCLUSIVE, and the current version carries dbt_valid_to = NULL. The closed row's dbt_valid_to equals its successor's dbt_valid_from exactly — they share an instant, and that instant belongs to the later row. This convention differs from the one the Data Modeling KB teaches; the callout below reconciles them, and Section 9 ships the predicate.
S-106: The new subscriber this chapter follows: listener L-9006, signed up 2026-07-11 on a 14-day free trial, first invoice due 2026-07-25. It cancels on 2026-07-21, before any charge is raised — so it has no rows in stripe.payments and never will. That fact matters later, in Section 9.
snap_subscriptions: The new node. stg_subs is deliberately NOT re-pointed at it — Chapter 4's fct_subscription_revenue depends on stg_subs staying one row per subscription. Building dim_subscription on top of the snapshot is the obvious next PR, and Chapter 12 owns it.

Incremental model vs snapshot — the difference that generates every other difference

	Incremental model (Chapter 9)	Snapshot (this chapter)
What it is	A function of its inputs	A function of its inputs and of when you looked
Drop the table	Re-run it, get the same table back	Permanent data loss — nothing can rebuild it
--full-refresh	The escape hatch when anything goes wrong	Not a flag on dbt snapshot at all; a silent no-op on snapshot nodes
Keeps the old value?	No — a merge overwrites it by design	Yes — that is the entire point
Which command runs it	dbt run, dbt build	dbt snapshot, dbt build — never dbt run

Chapter 2's command-coverage widget already says the last row out loud. It is worth re-reading now that it has consequences: a team orchestrating "dbt run && dbt test" has a snapshot that has never executed, silently, and will not find out until someone queries history that is not there.

Common mistake

Orchestrating the project as dbt run && dbt test and assuming snapshots are covered. dbt run does not execute snapshots — only dbt snapshot and dbt build do. The pipeline stays green for months, and the snapshot table either does not exist or is frozen at whatever day someone last ran it by hand. There is no error, no warning, and no way to backfill the missing history afterwards.

Waiting until someone asks for history before building the snapshot. The request always arrives in the form "what did this look like in Q2," and Q2 is exactly the period no snapshot covers. The correct move is to start snapshotting the mutable sources the year before anyone asks — a snapshot nobody queries costs one small table, and a snapshot nobody started costs the answer.

Better habit

When you meet a new source table, ask one question first: does a row here ever change after it is written? If yes, it is a snapshot candidate, whether or not anyone has asked for history yet.

Read a snapshot table as a claim about intervals, not about rows — "S-106 was annual · $72.00 from 21:40 on the 19th until 08:15 on the 20th" is the unit of meaning, not the row.

Put snapshots in their own schema on day one. It is dbt's own advice and it makes the blast radius of a careless DROP SCHEMA visible to everyone.

SCD theory lives in the Data Modeling KB — this is the dbt implementation

A dbt snapshot implements exactly Type 2 and offers no choice about it. The taxonomy of types 0 through 7, how to choose between them, per-attribute policy, Type 6 hybrids, mini-dimensions, late-arriving dimensions and the abstract statement of the interval invariants all live in the Data Modeling KB. This chapter assumes Type 2 is already the right answer for wavelength_app.subscriptions and shows what dbt actually writes when you ask for it.

Slowly Changing Dimensions (SCD 0–7) — Data Modeling KB

"Snapshot" is two different words across these KBs

The Data Modeling KB's temporal chapter uses "snapshot" for a periodic full-state copy — a complete photograph of the table every day, which is the opposite storage shape from effective dating. dbt's snapshot resource IS effective dating: one row per version, with a validity interval. Same word, opposite shape. Bitemporality, valid-time vs transaction-time, SQL:2011 and the storage arithmetic between the two shapes belong to that chapter, not this one.

Temporal and historical modeling — Data Modeling KB

Closing Chapter 9's open debt, precisely

Chapter 9 ended its drift section by saying "Chapter 10's hard_deletes config is the mechanism" for a hard DELETE on wavelength_app.listens. Here is the precise version: hard_deletes is a SNAPSHOT config, not a model config. It does not retroactively rescue fct_listens and it never will. It solves the delete-detection problem only for a key you are already snapshotting — which is a real answer, and a narrower one than that sentence implied.

Remember this

A snapshot is the only dbt node whose contents depend on when you ran it, which is why it is both the only way to keep history and the only thing in your project you cannot rebuild.

Snapshots and Slowly Changing Dimensions in dbt