Changing the source and snapshotting again

Description

This is the module's central lesson — the one that turns everything before it into a real history. You're going to add products_v2.csv to Kiosko's project — the same catalog, with the same real P002 change data-modeling-for-analytics-guide already used — change products's external_location to point at it, and run dbt snapshot a second time. In the end, dim_product_snapshot is going to have five rows: the three products that didn't change, plus two versions of P002, one closed and one current.

Connection to the module. Lesson 5 archived the first frame — four rows, all open, nothing to compare yet. This lesson is the first time in the whole module that the compare-and-archive mechanism, which lessons 2 and 3 explained with their own SQL, actually activates inside dbt. Lesson 7 is going to query the exact result this lesson produces.

The real change: the same one you already know

data-modeling-for-analytics-guide already declared, with executed evidence, exactly what changes in Kiosko's catalog on August 15, 2026: P002 (Energy Bar) has its cost go up (0.60 → 0.68) and its category change (snacks → health-snacks), both changes in the same update. This module uses that exact same change, with one additional piece data-modeling-for-analytics-guide didn't need: the change's date now lives inside the data itself — product_updated_at — not as an external parameter someone passes by hand when running a script.

Worked example: products_v2.csv

Create the catalog's second file, inside raw_data/kiosko/ (alongside products_v1.csv, which still exists unchanged):

-- raw_data/kiosko/products_v2.csv
product_id,product_name,category,unit_cost,product_updated_at
P001,Bottled Water 600ml,beverages,0.40,2026-08-01
P002,Energy Bar,health-snacks,0.68,2026-08-15
P003,Instant Coffee Sachet,beverages,0.35,2026-08-01
P004,Phone Charger Cable,electronics,2.10,2026-08-01

Notice what changes and what doesn't, compared to products_v1.csv: P001, P003, and P004 are identical, row for row, including their product_updated_at — they're still dated 2026-08-01, the original date, because nothing changed in them. Only P002 has three new values: category (health-snacks), unit_cost (0.68), and product_updated_at (2026-08-15, the change's real date). That third column is what makes strategy: timestamp detect the change — without it updated, dbt would have no signal that anything changed, no matter that category and unit_cost really are different.

Worked example (continued): changing external_location

The snapshot needs no change — it still points at source('kiosko_raw', 'products'), exactly as in lesson 4. What changes is which physical file that source points at, in models/staging/kiosko/_sources.yml, with the same mechanism you already used in module 2:

# models/staging/kiosko/_sources.yml (only the products table changes)
      - name: products
        description: "Product catalog, version 2 (with P002's price and category change)."
        meta:
          external_location: "raw_data/kiosko/products_v2.csv"

One conceptual character changed — products_v1.csv to products_v2.csv — but it's worth noting the cascading effect: stg_products (which reads from this same source) is also going to reflect the new catalog the next time you run dbt run, and anything that depended on stg_products would inherit the change too. The snapshot, by contrast, doesn't lose the old version — that's precisely the difference between a view that only knows the present and a security camera that archives every frame.

Worked example (continued): the second run

dbt snapshot

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 1 snapshot, 15 data tests, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 1 START snapshot main.dim_product_snapshot ................................ [RUN]
[WARNING]: Data type of snapshot table timestamp columns (TIMESTAMP) doesn't match derived column 'updated_at' (DATE). Please update snapshot config 'updated_at'.
1 of 1 OK snapshotted main.dim_product_snapshot ................................ [OK in 0.12s]

Finished running 1 snapshot in 0 hours 0 minutes and 0.20 seconds (0.20s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

PASS=1, with the same benign WARNING from lesson 5 — it's still the same type mismatch, for the same reason, and it's still not a problem. Nothing in this output, yet, tells you how many rows changed: dbt snapshot reports success or failure at the whole-resource level, not a count of affected rows — an important difference from the RETURNING data-modeling-for-analytics-guide used with MERGE INTO, where you did see, row by row, what was modified. To see the real effect, you have to query the table.

Verifying the result: five rows, P002 with two versions

import duckdb
con = duckdb.connect("kiosko.duckdb")
print(con.sql("""
    select product_id, category, unit_cost, product_updated_at, dbt_valid_from, dbt_valid_to
    from dim_product_snapshot
    order by product_id, dbt_valid_from
"""))

What to expect.

┌────────────┬───────────────┬───────────┬─────────────────────┬─────────────────┬───────────────┐
│ product_id │   category    │ unit_cost │ product_updated_at  │ dbt_valid_from  │ dbt_valid_to  │
├────────────┼───────────────┼───────────┼─────────────────────┼─────────────────┼───────────────┤
│ P001       │ beverages     │       0.4 │ 2026-08-01          │ 2026-08-01      │ NULL          │
│ P002       │ snacks        │       0.6 │ 2026-08-01          │ 2026-08-01      │ 2026-08-15    │
│ P002       │ health-snacks │      0.68 │ 2026-08-15          │ 2026-08-15      │ NULL          │
│ P003       │ beverages     │      0.35 │ 2026-08-01          │ 2026-08-01      │ NULL          │
│ P004       │ electronics   │       2.1 │ 2026-08-01          │ 2026-08-01      │ NULL          │
└────────────┴───────────────┴───────────┴─────────────────────┴─────────────────┴───────────────┘

Five rows. Stop on P002, because it's this lesson's core: two rows, not one. The first — snacks/0.6, dbt_valid_from = 2026-08-01, dbt_valid_to = 2026-08-15 (no longer NULL) — is the version this same snapshot archived in lesson 5, now closed: dbt found it, noticed the source's product_updated_at (2026-08-15) was more recent than the already-archived date (2026-08-01), and stamped it with "until when it was current." The second — health-snacks/0.68, dbt_valid_from = 2026-08-15, dbt_valid_to = NULL — is the new row, the version current right now. P001, P003, and P004, by contrast, remain exactly as in lesson 5 — one row each, dbt_valid_to = NULL — because their product_updated_at didn't change between products_v1.csv and products_v2.csv.

Confirm the total count, and P002's specifically:

print(con.sql("select count(*) as total_rows from dim_product_snapshot"))
print(con.sql("select count(*) as p002_rows from dim_product_snapshot where product_id = 'P002'"))

What to expect.

┌────────────┐
│ total_rows │
├────────────┤
│          5 │
└────────────┘

┌───────────┐
│ p002_rows │
├───────────┤
│         2 │
└───────────┘

Five total rows, two of them P002's — exactly what lesson 1 promised, exactly what data-modeling-for-analytics-guide already produced with MERGE INTO. The difference: there you ran a MERGE with hand-written logic, column by column; here you ran dbt snapshot, writing no UPDATE or INSERT at all.

Verifying idempotency: a third run, with no changes at all

Before closing this lesson, confirm something you already know in theory since lesson 2, but that's worth seeing with evidence: running dbt snapshot again, with nothing changed in products_v2.csv, shouldn't add any more rows.

dbt snapshot

What to expect. The same PASS=1, the same usual WARNING. Confirm the count didn't change:

print(con.sql("select count(*) as total_rows from dim_product_snapshot"))

┌────────────┐
│ total_rows │
├────────────┤
│          5 │
└────────────┘

Still at 5 — not one extra row. strategy: timestamp compared each product's product_updated_at against what was already archived, and for all four, the source's date is no longer more recent than the archived one (they're the same date) — so no row got closed or opened again. It's the same idempotency property data-modeling-for-analytics-guide already demonstrated with its MERGE: running the same command twice over data with no real change is completely safe.

Diagram: the second frame, compared against the first

flowchart TD
    subgraph v1["Frame 1 (lesson 5)"]
        A1["P002: snacks, 0.60\nvalid_from=2026-08-01, valid_to=NULL"]
    end
    subgraph v2fuente["products_v2.csv (new source)"]
        B1["P002: health-snacks, 0.68\nproduct_updated_at=2026-08-15"]
    end
    A1 -->|"comparison: 2026-08-15 > 2026-08-01?"| C{"YES, changed"}
    B1 --> C
    C --> D["CLOSES A1: valid_to=2026-08-15"]
    C --> E["OPENS new row:\nvalid_from=2026-08-15, valid_to=NULL"]

Common mistakes

Forgetting to change external_location before running dbt snapshot. What happens: someone creates products_v2.csv, but forgets to update _sources.yml — the products source still points at products_v1.csv — and runs dbt snapshot expecting to see the change. Why it happens: creating the new file feels like "the important step"; updating the source's configuration feels like an easy-to-skip detail. How to spot it: if after running dbt snapshot the total count is still 4 (not 5), and P002 still has just one row, first check which file external_location declares at this moment — it's the same kind of mistake you already saw in module 2 with a badly written glob, only here it produces no error message, the snapshot simply detects no change because it's still reading the same file as always. How to fix it: confirm with dbt show --inline "select * from {{ source('kiosko_raw', 'products') }} where product_id = 'P002'" that the source already returns health-snacks/0.68 before running dbt snapshot — if it still shows snacks/0.60, external_location didn't get updated.

Expecting dbt snapshot to report how many rows changed, the way RETURNING did with MERGE INTO. What happens: someone looks, in this lesson's dbt snapshot output, for some number saying "1 row historized" or similar, like the RETURNING data-modeling-for-analytics-guide did show. Why it happens: it's reasonable to expect the same level of detail between two tools solving the same problem. How to spot it: this lesson's dbt snapshot output only says OK snapshotted main.dim_product_snapshot — no count of affected rows, unlike the manual MERGE INTO. How to fix it: to know how many rows changed, query the table directly after running the command — exactly what this lesson did with SELECT * FROM dim_product_snapshot; dbt doesn't expose that detail in the command's own output.

Modifying products_v1.csv instead of creating products_v2.csv. What happens: someone, looking for the shortest path, edits products_v1.csv directly to reflect P002's change, instead of creating a new file and changing external_location. Why it happens: it seems simpler to edit a file that already exists than to create a new one and touch the source's configuration. How to spot it: if you did this, dbt snapshot still detects the change correctly — dbt doesn't care whether the filename changed, only what source() resolves at that moment — so the mistake isn't technical, it's project discipline: you lost the ability to go back to products_v1.csv as the original catalog, without P002's change, something you keep if you maintain the two files separately. How to fix it: always keep products_v1.csv intact — the _v1/_v2 suffix in the filename exists precisely to preserve each catalog version as an independent, queryable artifact, the same immutable-raw-data discipline module 2 already established for orders and events.

Exercises

Exercise 1 — Run dbt snapshot a fourth time and predict the result. With nothing else changed in the project, run dbt snapshot once more. Before running it, predict: how many rows is dim_product_snapshot going to have at the end?

Exercise 2 — Simulate a third change: P002's cost goes up again, on 2026-08-25, to 0.72, with no category change. Create products_v3.csv with that change (the other products unchanged), update external_location, run dbt snapshot, and confirm how many versions P002 has at the end.

-- raw_data/kiosko/products_v3.csv
product_id,product_name,category,unit_cost,product_updated_at
P001,Bottled Water 600ml,beverages,0.40,2026-08-01
P002,Energy Bar,health-snacks,0.72,2026-08-25
P003,Instant Coffee Sachet,beverages,0.35,2026-08-01
P004,Phone Charger Cable,electronics,2.10,2026-08-01

# models/staging/kiosko/_sources.yml
      - name: products
        meta:
          external_location: "raw_data/kiosko/products_v3.csv"

dbt snapshot

print(con.sql("select category, unit_cost, dbt_valid_from, dbt_valid_to from dim_product_snapshot where product_id = 'P002' order by dbt_valid_from"))

┌───────────────┬───────────┬─────────────────┬───────────────┐
│   category    │ unit_cost │ dbt_valid_from  │ dbt_valid_to  │
├───────────────┼───────────┼─────────────────┼───────────────┤
│ snacks        │       0.6 │ 2026-08-01      │ 2026-08-15    │
│ health-snacks │      0.68 │ 2026-08-15      │ 2026-08-25    │
│ health-snacks │      0.72 │ 2026-08-25      │ NULL          │
└───────────────┴───────────┴─────────────────┴───────────────┘

Exercise 3 — Explain why P001, P003, and P004 don't show up duplicated, even though the snapshot processed them too in the second run. In 2-3 sentences, explain why those three products still have exactly one row each after the second run, even though the snapshot "checked" them just like P002.

Summary and next step

This lesson did what the whole module promised: you added products_v2.csv with P002's real change, changed external_location, and ran dbt snapshot a second time. The result — 5 total rows, P002 with two versions, one closed (dbt_valid_to = 2026-08-15) and one current (dbt_valid_to = NULL) — is identical, number for number, to what data-modeling-for-analytics-guide produced with MERGE INTO. You also confirmed that a third run, with no changes in the source, adds no more rows — the same idempotency you already saw in the sibling guide.

Before moving on you should be able to: explain, without looking at the lesson, what determines that a row closes and a new one opens; and reproduce from memory the expected count (5 total rows, 2 of P002) after the second run.

Lesson 7 stops on how to query this result with judgment: the current version of any product, and the version it had on any date in the past — the two questions a non-historized catalog could never answer.

Resources

• dbt Developer Hub — "Add snapshots to your DAG," section on runs after the first one (how dbt decides to close and open rows on every subsequent run). docs.getdbt.com/docs/build/snapshots. In English.
• data-modeling-for-analytics-guide, lesson "Implementing SCD type 2 with MERGE INTO" (module 4) — the same final result (5 rows, P002 with 2 versions), produced with hand-written MERGE INTO. Sibling guide in the same ecosystem.
• DuckDB — "Multiple Files" and "CSV Loading," already cited in module 2, relevant again here to confirm how DuckDB reads products_v2.csv with no prior loading step. duckdb.org/docs/stable/data/csv/overview. In English.