Module 5: Snapshots And Scd Type 2
Querying the historized `dim_product`
Description
Having archived history is useless if you don't know how to ask it anything. This lesson teaches the two query patterns a historized catalog makes possible — and that a non-historized catalog could never answer: "what's the current version of each product, right now?" and "what version of a product was current on any date in the past?" Both patterns rest on the same two columns, dbt_valid_from and dbt_valid_to, read with a different criterion depending on the question.
Connection to the module. Lessons 5 and 6 built the data — dim_product_snapshot, with 5 rows and P002 in two versions. This lesson builds access to that data: without it, dim_product_snapshot would be a curious table nobody knows how to query correctly. It's also the lesson where this module points out, precisely, where the comparison with data-modeling-for-analytics-guide ends and a convention difference worth knowing begins.
Pattern 1: each product's current version
The most common question you're going to ask a historized catalog is, by far, "what's the current state?" — the same question a non-historized dim_product answered, with no effort, before this module. With dim_product_snapshot, the answer is a filter on a single column:
select product_id, category, unit_cost
from {{ ref('dim_product_snapshot') }}
where dbt_valid_to is null
order by product_idWhat to expect.
dbt show --inline "select product_id, category, unit_cost from {{ ref('dim_product_snapshot') }} where dbt_valid_to is null order by product_id"Previewing inline node:
| product_id | category | unit_cost |
| ---------- | ------------- | --------- |
| P001 | beverages | 0.40 |
| P002 | health-snacks | 0.68 |
| P003 | beverages | 0.35 |
| P004 | electronics | 2.10 |
Four rows — one per product — each the most recent version. Notice P002: health-snacks/0.68, the version that opened on August 15, not the original. dbt_valid_to IS NULL is, precisely, the automatic equivalent of WHERE is_current = true in data-modeling-for-analytics-guide's dim_product_scd — the same question, solved with the column dbt generates instead of one you maintain by hand.
Pattern 2: a product's version on a specific date in the past
This is the question dim_product never could answer before this module: "what were P002's category and cost on August 10, 2026?" — a date before the change, but after the catalog already existed. The answer needs to compare the date of interest against both validity columns, not just one:
select product_id, category, unit_cost, dbt_valid_from, dbt_valid_to
from {{ ref('dim_product_snapshot') }}
where product_id = 'P002'
and dbt_valid_from <= date '2026-08-10'
and (dbt_valid_to is null or dbt_valid_to > date '2026-08-10')What to expect.
dbt show --inline "select product_id, category, unit_cost, dbt_valid_from, dbt_valid_to from {{ ref('dim_product_snapshot') }} where product_id = 'P002' and dbt_valid_from <= date '2026-08-10' and (dbt_valid_to is null or dbt_valid_to > date '2026-08-10')"Previewing inline node:
| product_id | category | unit_cost | dbt_valid_from | dbt_valid_to |
| ---------- | -------- | --------- | --------------- | ------------- |
| P002 | snacks | 0.60 | 2026-08-01 | 2026-08-15 |
Exactly the old version — snacks/0.60 — the one current on August 10. This is the question that justifies this whole module's existence: with no historizing, this answer simply wouldn't exist anywhere, because dim_product with no snapshot only knows its present.
The complete condition, explained piece by piece
dbt_valid_from <= '<date>'
and (dbt_valid_to is null or dbt_valid_to > '<date>')dbt_valid_from <= '<date>'— the version already existed on that date (it didn't start later).dbt_valid_to IS NULL— the version is still current today, so any date afterdbt_valid_fromincludes it.dbt_valid_to > '<date>'— the version hadn't ended yet on that date.
The OR's two conditions cover the two possible cases: querying a date within a still-current version's range, or querying a date within an already-closed version's range. Without the OR, a query about P001's historical version — which never closed, dbt_valid_to always NULL — would fail, because NULL > any_date is never true in standard SQL.
Going deeper: the range's exact boundary, and a real difference from data-modeling-for-analytics-guide
It's worth examining exactly what happens on the day of the change, August 15, 2026 — because it reveals a real convention difference between dbt and the sibling guide's manual MERGE INTO, not a mistake in either.
dbt show --inline "select product_id, category, unit_cost, dbt_valid_from, dbt_valid_to from {{ ref('dim_product_snapshot') }} where product_id = 'P002' and dbt_valid_from <= date '2026-08-15' and (dbt_valid_to is null or dbt_valid_to > date '2026-08-15')"What to expect.
Previewing inline node:
| product_id | category | unit_cost | dbt_valid_from | dbt_valid_to |
| ---------- | ------------- | --------- | --------------- | ------------- |
| P002 | health-snacks | 0.68 | 2026-08-15 | |
August 15 already returns the new version (health-snacks/0.68), not the old one. This happens because dbt closes the old row by setting its dbt_valid_to exactly equal to the new row's dbt_valid_from — 2026-08-15 in both — and the old row's condition dbt_valid_to > '2026-08-15' gives false (2026-08-15 isn't greater than itself), so the old row gets excluded on that exact day; the new row, with dbt_valid_from <= '2026-08-15' true, does get included. dbt's validity interval is, in technical terms, half-open: [dbt_valid_from, dbt_valid_to), where the start is inclusive and the end is exclusive.
Compare it against data-modeling-for-analytics-guide: there, MERGE INTO closed the old row with valid_to = change_date - INTERVAL 1 DAY (2026-08-14, one day before the change), not the change's exact date. Both conventions are internally consistent and correct — neither has a "bug" — but they aren't interchangeable: if you wrote a point-in-time query against dim_product_snapshot using the same closed-range logic (dbt_valid_to >= '<date>', instead of >) you'd use against the sibling guide's dim_product_scd, you'd get a wrong result exactly on the day of the change. The practical rule: always confirm, before writing a history query, whether the system you're querying uses an open or closed interval at its far end — it's a different design decision in every SCD type 2 implementation, dbt included.
Diagram: P002's complete timeline
2026-08-01 2026-08-15
│ │
──────────────────┼──────────────────────────────┼──────────────────────────►
│ snacks / 0.60 │ health-snacks / 0.68
│ valid_from=2026-08-01 │ valid_from=2026-08-15
│ valid_to=2026-08-15 │ valid_to=NULL
│ [includes 08-01 .. 08-14] │ [includes 08-15 onward]
Query 2026-08-10 -> falls in the first segment -> snacks / 0.60
Query 2026-08-15 -> falls in the second segment -> health-snacks / 0.68 (day of the change)
Query 2026-08-20 -> falls in the second segment -> health-snacks / 0.68
Common mistakes
Using dbt_valid_to >= '<date>' instead of >, copying data-modeling-for-analytics-guide's convention without adjusting it. What happens: someone who already wrote point-in-time queries against dim_product_scd in the sibling guide — where valid_to was inclusive of the last current day — reuses the same comparison logic (>=) against dim_product_snapshot. Why it happens: both tables solve the same problem, with almost identical column names, so it's natural to assume the comparison logic is identical too. How to spot it: if you query dim_product_snapshot for the exact date 2026-08-15 with dbt_valid_to >= '2026-08-15', you'd get two rows — the old one and the new one — because the condition would stop correctly excluding the closed row. How to fix it: for dim_product_snapshot, always use > (strict), not >= — dbt's interval is half-open, as this lesson's Going deeper section explained; always verify this convention against the real source of the table you're querying, instead of assuming it out of habit.
Forgetting the parentheses around dbt_valid_to is null or dbt_valid_to > '<date>'. What happens: someone writes the condition with no grouping around the OR, something like where dbt_valid_from <= '<date>' and dbt_valid_to is null or dbt_valid_to > '<date>'. Why it happens: it's easy to forget that AND has higher precedence than OR in SQL, so without explicit parentheses, the query gets interpreted in a way completely different from the original intent. How to spot it: without the parentheses, the query would return rows that shouldn't appear — any row with dbt_valid_to > '<date>', regardless of whether dbt_valid_from falls outside the range you were looking for. How to fix it: always group the OR in parentheses, exactly as in this lesson's worked example — it's a general SQL rule, not specific to snapshots, but the cost of forgetting it here is a silently wrong result, not a visible error.
Querying dim_product_snapshot with source() instead of ref(). What happens: someone, remembering that the snapshot's definition uses source('kiosko_raw', 'products') (lesson 4), tries to query it from another model with the same function. Why it happens: it's easy to confuse "where the snapshot reads from" with "how the snapshot gets referenced from elsewhere." How to spot it: {{ source('kiosko_raw', 'dim_product_snapshot') }} would fail — that name doesn't exist as a table in the kiosko_raw source, which still has exactly the four original tables (orders, events, stores, products). How to fix it: a snapshot, once created, gets consumed with ref('dim_product_snapshot') from anywhere else in the project — the same function you've already used to reference any model since module 3, with no exception for snapshots.
Exercises
Exercise 1 — Query a single product's current version. Without using dbt_valid_to IS NULL over the whole table, write a query that returns only P003's current version.
See solution
dbt show --inline "select product_id, category, unit_cost from {{ ref('dim_product_snapshot') }} where product_id = 'P003' and dbt_valid_to is null"Expected output:
Previewing inline node:
| product_id | category | unit_cost |
| ---------- | --------- | --------- |
| P003 | beverages | 0.35 |
Adding product_id = 'P003' to Pattern 1's same condition filters to a single product, with no change to the "current" logic — combining an identity filter and a currency filter is the most common pattern you're going to use against any historized table.
Exercise 2 — Verify P001 answers a point-in-time query correctly, even though it never changed. Using the Going deeper section's pattern, query P001's version on 2026-08-20 (a date well after any catalog change). Explain why the query works, even though P001 has dbt_valid_to = NULL.
See solution
dbt show --inline "select product_id, category, unit_cost, dbt_valid_from, dbt_valid_to from {{ ref('dim_product_snapshot') }} where product_id = 'P001' and dbt_valid_from <= date '2026-08-20' and (dbt_valid_to is null or dbt_valid_to > date '2026-08-20')"Expected output:
Previewing inline node:
| product_id | category | unit_cost | dbt_valid_from | dbt_valid_to |
| ---------- | --------- | --------- | --------------- | ------------- |
| P001 | beverages | 0.40 | 2026-08-01 | |
It works because the OR's dbt_valid_to IS NULL branch is designed exactly for this case: a version that's still current covers, by definition, any date from dbt_valid_from onward, no matter how far into the future that date is. Without that branch of the OR, any point-in-time query against a product that never changed would fail — the exact reason the OR is mandatory, not optional, in this pattern.
Exercise 3 — Explain, in your own words, why dbt's half-open interval is a reasonable design choice, not an oversight. In 2-3 sentences, argue why dbt_valid_to being equal to — not one day before — the next row's dbt_valid_from makes sense, even though it differs from data-modeling-for-analytics-guide's convention.
See solution
A half-open interval ([valid_from, valid_to)) guarantees the two dates — one version's close and the next one's open — are literally the same instant, with no "gap" or "overlap" of a day between them; this simplifies the logic of any point-in-time JOIN, because you never have to subtract or add a day to make the ranges fit perfectly one after the other. data-modeling-for-analytics-guide's convention (valid_to = change_date - 1 day) is also valid, but requires that explicit "minus one day" adjustment to achieve the same non-overlap effect — neither is objectively better, but dbt chose not to depend on that subtraction.
Summary and next step
This lesson gave you the two query patterns that make a historized catalog useful: the current version (dbt_valid_to IS NULL) and the version of any past date (dbt_valid_from <= date AND (dbt_valid_to IS NULL OR dbt_valid_to > date)). You confirmed, with evidence, a real convention difference from data-modeling-for-analytics-guide: dbt's interval is half-open, so the exact day of the change already returns the new version, not the old one — neither convention is wrong, but they aren't interchangeable without adjusting the comparison operator.
Before moving on you should be able to: write this lesson's two query patterns from memory; and explain, without looking at the lesson, what would happen if you used >= instead of > against dim_product_snapshot.
Lesson 8 closes the module: it rebuilds the complete project from module 4's state, confirms the 15 inherited data_tests are still green, runs dbt build end to end — snapshot included — and adds Kiosko project's fifth commit.
Resources
- dbt Developer Hub — "Add snapshots to your DAG," which documents
dbt_valid_from/dbt_valid_to's exact behavior across successive runs — the basis for this lesson's Going deeper section. docs.getdbt.com/docs/build/snapshots. In English. data-modeling-for-analytics-guide, lesson "Implementing SCD type 2 with MERGE INTO" (module 4) — the closed-interval convention (valid_to = change_date - 1 day), compared here against dbt's half-open interval. Sibling guide in the same ecosystem.- PostgreSQL — documentation on range types (
range types) — although this guide doesn't use them directly, the distinction between open and closed intervals that documentation explains is the same one that justifies this lesson's strict>. postgresql.org/docs/current/rangetypes.html. In English.