Running the first snapshot

Description

It's time to run dbt snapshot for the first time. There's still no real change to historize yet — products_v1.csv is still Kiosko's original catalog, without P002's update, which only arrives in lesson 6 — but this first run is the one that creates the dim_product_snapshot table and archives the first frame of each of the four products. Without this first frame, there'd be nothing to compare against once the catalog does change.

Connection to the module. Lesson 4 left dim_product_snapshot.yml declared, recognized by dbt, but with no real table yet. This lesson closes that gap, and along the way confirms, with executed evidence, the central reproducibility promise this module made since lesson 1: dbt_valid_from is going to show the data's date (2026-08-01), not the date you run this command.

Worked example: dbt snapshot

From kiosko_analytics/'s root, with products_v1.csv still as the source (unchanged since module 2):

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.07s]

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

Completed successfully

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

PASS=1 — the snapshot ran with no errors. But notice the [WARNING] showing up right before the OK: it's worth stopping on it before moving on, because it's exactly the kind of warning module 2 already taught you to read carefully, not to fear on instinct.

Reading the WARNING, without panicking

[WARNING]: Data type of snapshot table timestamp columns (TIMESTAMP) doesn't match derived column 'updated_at' (DATE). Please update snapshot config 'updated_at'.

This warning exists because dbt, as a general convention, expects a snapshot's updated_at column to be of type TIMESTAMP (full date and time) — most dbt projects historize systems where "when something changed" gets recorded with second-level precision. product_updated_at, by contrast, is of type DATE (only the day, no time) — a deliberate decision by this guide, because Kiosko never needed more precision than "which day the catalog changed." dbt detects that type mismatch and warns you, in case it were an oversight on your part.

In this project, it isn't: confirm it yourself, by inspecting the real type of every column in the just-created table:

import duckdb
con = duckdb.connect("kiosko.duckdb")
print(con.sql("describe dim_product_snapshot"))

What to expect.

┌─────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│     column_name     │ column_type │  null   │   key   │ default │  extra  │
│       varchar       │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ product_id          │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ product_name        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ category            │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ unit_cost           │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │
│ product_updated_at  │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │
│ dbt_scd_id          │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ dbt_updated_at      │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │
│ dbt_valid_from      │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │
│ dbt_valid_to        │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

There's the confirmation: dbt_updated_at, dbt_valid_from, and dbt_valid_to ended up as DATE, not TIMESTAMP — DuckDB, when creating the table for the first time, adopted the same type product_updated_at already had, instead of forcing TIMESTAMP. The WARNING is dbt's way of saying "this isn't the most common setup, check it" — and you, by checking it, confirm it's exactly what you wanted: a simple date column, no time, for a piece of data that at Kiosko never needed more precision than the day. This WARNING is going to reappear, identical, on every future dbt snapshot run over this project (you're going to see it again in lesson 6) — it's expected and benign, not a sign something's broken.

Verifying the content: the four rows, all open

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
"""))

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      │ NULL          │
│ P003       │ beverages   │      0.35 │ 2026-08-01          │ 2026-08-01      │ NULL          │
│ P004       │ electronics │       2.1 │ 2026-08-01          │ 2026-08-01      │ NULL          │
└────────────┴─────────────┴───────────┴─────────────────────┴─────────────────┴───────────────┘

Four rows — one per product — all with dbt_valid_to = NULL, exactly what's expected of a first frame: nothing changed yet, so nothing closed. Notice dbt_valid_from: 2026-08-01, in all four rows — the same date product_updated_at brings in products_v1.csv, not the date you ran this command (which could be weeks or months after this guide was written). That's the concrete proof, with real data, of what lesson 3 already told you: strategy: timestamp uses the updated_at column's value to stamp dbt_valid_from, never the system clock.

About this "What to expect" block's reproducibility

It's worth being explicit about something you may have noticed: unlike other "What to expect" blocks in this guide — which show variable execution times (OK in 0.07s) with no harm done — the business columns and dbt's three metadata columns (dbt_valid_from, dbt_valid_to, dbt_updated_at) are, in this specific project, completely reproducible byte for byte, with no redaction or manual adjustment: they don't depend on which date you run this command, only on the fixed date products_v1.csv brings. This isn't a coincidence — it's the direct consequence of two decisions you already made: strategy: timestamp (lesson 3, which never queries the system clock) and product_updated_at as a DATE column, not TIMESTAMP (a decision that also explains this lesson's benign WARNING). The only column that does vary between your run and anyone else's is dbt_scd_id — a hash computed internally by dbt, whose exact value doesn't matter for any purpose in this module, only that it's unique per row version.

Diagram: the first archived frame

flowchart LR
    subgraph fuente["raw_data/kiosko/products_v1.csv"]
        P1["P001 beverages 0.40"]
        P2["P002 snacks 0.60"]
        P3["P003 beverages 0.35"]
        P4["P004 electronics 2.10"]
    end
    subgraph tabla["dim_product_snapshot (just created)"]
        R1["P001, valid_from=2026-08-01, valid_to=NULL"]
        R2["P002, valid_from=2026-08-01, valid_to=NULL"]
        R3["P003, valid_from=2026-08-01, valid_to=NULL"]
        R4["P004, valid_from=2026-08-01, valid_to=NULL"]
    end
    P1 --> R1
    P2 --> R2
    P3 --> R3
    P4 --> R4

Four arrows, with no crossing — the first frame is simply a faithful copy of the current catalog, with the metadata columns added. The history doesn't exist yet: it only starts existing from lesson 6 onward, when the catalog really changes.

Common mistakes

Running dbt snapshot expecting to see something other than "four rows identical to the source." What happens: someone, after lessons 2 and 3 on compare-and-archive, expects the first run to already show some kind of "history," with multiple versions. Why it happens: it's easy to forget that a history needs, at minimum, two points in time to exist — the first point, by definition, has nothing earlier to compare against. How to spot it: if after running dbt snapshot for the first time you expect to see any row with dbt_valid_to different from NULL, review lesson 2 — the three-branch diagram there explains that a new product (and, in the first run, all four products are "new" to the snapshot) always opens, never closes. How to fix it: any snapshot's first run always produces exactly one row per source row, all open — the real history only starts building from the second run onward, exactly what you're going to see in lesson 6.

Worrying about the type WARNING and trying to "fix" it by changing product_updated_at to TIMESTAMP. What happens: someone, seeing this lesson's WARNING, edits stg_products.sql or products_v1.csv to force product_updated_at to be TIMESTAMP instead of DATE, thinking that "fixes" the warning. Why it happens: any WARNING in a terminal feels, on instinct, like something to eliminate. How to spot it: if you change product_updated_at's type, you're going to introduce arbitrary hours and minutes (typically 00:00:00) that add no real information — Kiosko never had, or needed, an exact catalog-update time — and you risk those arbitrary hours ending up depending on how the file was generated, breaking the reproducibility this lesson just confirmed. How to fix it: leave product_updated_at as DATE — it's the correct type for the data it represents, and the WARNING is, as this lesson explained, expected and benign in this specific project.

Running dbt run instead of dbt snapshot, and not understanding why nothing happens. What happens: someone, mixing this lesson's command with modules 1 through 4's habit, runs dbt run expecting that to create or update dim_product_snapshot. Why it happens: dbt run is the command you've already used dozens of times to build models — it's easy to assume it does "everything there is to build." How to spot it: dbt run ends at PASS=7 (the usual seven models), with no mention of dim_product_snapshot anywhere — the snapshot doesn't even show up in its output. How to fix it: as lesson 1 already warned, a snapshot has its own command, dbt snapshot, completely separate from dbt run. (dbt build, which you're going to use in lesson 8, does run both resource types in a single command — but dbt run by itself does not.)

Exercises

Exercise 1 — Confirm the total count with dbt show. Without using Python or duckdb directly, use dbt show --inline to confirm dim_product_snapshot has exactly 4 rows after this first run.

dbt show --inline "select count(*) as total_rows from {{ ref('dim_product_snapshot') }}"

Previewing inline node:
| total_rows |
| ---------- |
|          4 |

Exercise 2 — Verify the four rows share the same dbt_valid_from. Write a query that confirms, with a single number, that dim_product_snapshot's four rows have exactly the same date in dbt_valid_from.

dbt show --inline "select count(distinct dbt_valid_from) as distinct_dates from {{ ref('dim_product_snapshot') }}"

Previewing inline node:
| distinct_dates |
| --------------- |
|               1 |

Exercise 3 — Explain, in your own words, why this lesson insists so much on the result's reproducibility. In 2-3 sentences, and without literally repeating the lesson's text, explain why dbt_valid_from showing 2026-08-01 (and not the real date you ran the command) is a valuable property for a guide like this one, beyond "looking tidy."

Summary and next step

This lesson ran dbt snapshot for the first time: four new rows in the dim_product_snapshot table, all with dbt_valid_to = NULL and dbt_valid_from = 2026-08-01 — the data's date, not the clock's. You carefully read the type WARNING that shows up on every run — benign, explained by product_updated_at's deliberate DATE type — and confirmed, with evidence, that this block's complete result is reproducible byte for byte.

Before moving on you should be able to: explain why any snapshot's first run never closes any row; and describe, without looking at the lesson, why this lesson's type WARNING doesn't indicate any real problem in this project.

Lesson 6 introduces the real change: you're going to add products_v2.csv to the project, change products's external_location to point at it, and run dbt snapshot a second time — the first time in this whole module you're going to see a row close.

Resources

• dbt Developer Hub — "Add snapshots to your DAG," section on the first run's behavior (every row gets inserted as new, with no prior comparison possible). docs.getdbt.com/docs/build/snapshots. In English.
• DuckDB — "Date Types," official reference for the DATE type that explains why product_updated_at (and, as a result, dbt_valid_from/dbt_valid_to) end up with no time component. duckdb.org/docs/stable/sql/data_types/date. In English.
• dbt Developer Hub — "dbt show," already cited in earlier modules, used in this lesson's Exercise 1 to verify the count without leaving dbt's flow. docs.getdbt.com/reference/commands/show. In English.