Mini-project: Kiosko's complete staging layer

Description

Time to close the module by completing what's missing and running everything together, end to end. Lessons 2 through 4 declared Kiosko's four sources and connected them to their physical files. Lessons 5 and 6 defined a staging model's shape and name. Lesson 7 built the first two: stg_orders and stg_events. This mini-project completes the two that are missing — stg_stores and stg_products — runs the complete layer with dbt run --select staging, verifies all four tables have exactly the same row count as their raw files, and adds this module's new piece: Kiosko project's second commit, on top of the first one module 1 left behind.

Connection to the module. This mini-project introduces no new dbt concept — it's the synthesis of lessons 2 through 7, executed with no interruptions — plus the exact continuation of the version-control habit module 1 started. By the end of this lesson, kiosko_analytics/ has a complete, verified, versioned staging layer — the foundation module 3 is going to build the first real mart on top of.

An analogy: the warehouse's first complete inventory

Go back, one last time in this module, to lessons 2 through 4's warehouse. You already registered the four suppliers (source(), lesson 3), already confirmed each dock's exact address (external_location, lesson 4), and already started receiving merchandise from the two biggest suppliers (stg_orders, stg_events, lesson 7). This mini-project is the day merchandise arrives from the two remaining suppliers — stores and products — and, for the first time, someone walks the whole warehouse with a clipboard, counting box by box, confirming that what's on the shelf matches exactly what the receiving log says should be there. That final count, signed and filed in the warehouse's logbook, is exactly what you're going to do by closing this lesson with a git commit.

The material: the two missing staging models

Complete models/staging/kiosko/ with the two files left pending since lesson 7:

-- models/staging/kiosko/stg_stores.sql
select
    store_id,
    store_name,
    city
from {{ source('kiosko_raw', 'stores') }}

-- models/staging/kiosko/stg_products.sql
select
    product_id,
    product_name,
    category,
    cast(unit_cost as decimal(10, 2)) as unit_cost,
    cast(product_updated_at as date) as product_updated_at
from {{ source('kiosko_raw', 'products') }}

stg_stores.sql needs no cast() at all — its three columns are already text in the raw file, and text is exactly what they should stay. stg_products.sql casts unit_cost to decimal(10, 2) for the same reason unit_price did in stg_orders (lesson 7: never money as float), and casts product_updated_at to date — not timestamp — because that column represents a whole day, no time of day, the date module 5 is going to use to trigger dim_product's snapshot.

And complete _models.yml with the minimal tests for the two missing catalogs:

# models/staging/kiosko/_models.yml
version: 2

models:
  - name: stg_orders
    description: "One row per order, types already cast, no joins or aggregations."
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

  - name: stg_events
    description: "One row per clickstream event, types already cast."
    columns:
      - name: event_id
        data_tests:
          - unique
          - not_null

  - name: stg_stores
    description: "Store catalog, untransformed."
    columns:
      - name: store_id
        data_tests:
          - unique
          - not_null

  - name: stg_products
    description: "Product catalog version 1, untransformed."
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

The reference solution, verified

Part 1 — Run the complete layer

dbt run --select staging

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 4 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 4 START sql view model main.stg_events .................................... [RUN]
2 of 4 START sql view model main.stg_orders .................................... [RUN]
3 of 4 START sql view model main.stg_products .................................. [RUN]
4 of 4 START sql view model main.stg_stores .................................... [RUN]
4 of 4 OK created sql view model main.stg_stores ............................... [OK in 0.07s]
1 of 4 OK created sql view model main.stg_events ............................... [OK in 0.07s]
3 of 4 OK created sql view model main.stg_products ............................. [OK in 0.07s]
2 of 4 OK created sql view model main.stg_orders ............................... [OK in 0.07s]

Finished running 4 view models in 0 hours 0 minutes and 0.18 seconds (0.18s).

Completed successfully

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

Four models, four OK, running in parallel — dbt has no reason to order them relative to each other, since none depends on another, the exact guarantee of lesson 5's "no joins" rule. If this run fails, don't move on to Part 2 — go back to whichever lesson matches the file that's causing it: _sources.yml (lessons 2-4), the .sql's syntax (lessons 5 and 7), or dbt_project.yml (lesson 6).

Part 2 — Run the complete test suite

dbt test --select staging

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 4 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 8 START test not_null_stg_events_event_id ................................. [RUN]
2 of 8 START test not_null_stg_orders_order_id ................................. [RUN]
3 of 8 START test not_null_stg_products_product_id ............................. [RUN]
4 of 8 START test not_null_stg_stores_store_id ................................. [RUN]
1 of 8 PASS not_null_stg_events_event_id ....................................... [PASS in 0.05s]
4 of 8 PASS not_null_stg_stores_store_id ....................................... [PASS in 0.05s]
3 of 8 PASS not_null_stg_products_product_id ................................... [PASS in 0.05s]
5 of 8 START test unique_stg_events_event_id ................................... [RUN]
6 of 8 START test unique_stg_orders_order_id ................................... [RUN]
7 of 8 START test unique_stg_products_product_id ............................... [RUN]
2 of 8 PASS not_null_stg_orders_order_id ....................................... [PASS in 0.07s]
8 of 8 START test unique_stg_stores_store_id ................................... [RUN]
8 of 8 PASS unique_stg_stores_store_id ......................................... [PASS in 0.02s]
7 of 8 PASS unique_stg_products_product_id ..................................... [PASS in 0.03s]
5 of 8 PASS unique_stg_events_event_id ......................................... [PASS in 0.04s]
6 of 8 PASS unique_stg_orders_order_id ......................................... [PASS in 0.04s]

Finished running 8 data tests in 0 hours 0 minutes and 0.18 seconds (0.18s).

Completed successfully

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

Eight tests — two for each of the four tables — eight PASS. No duplicate identifier, no null identifier, in any of Kiosko's four tables.

Part 3 — The reconciliation: raw versus staging, table by table

This is the check that actually matters — the one no PASS=4 or PASS=8 can replace: confirming, with a query, that each staging model's row count matches its corresponding raw file's exactly.

import duckdb

con = duckdb.connect("kiosko.duckdb")
raw = {
    "orders": con.sql("select count(*) from read_csv_auto('raw_data/kiosko/orders_*.csv')").fetchone()[0],
    "events": con.sql("select count(*) from read_ndjson_auto('raw_data/kiosko/events_*.jsonl')").fetchone()[0],
    "stores": con.sql("select count(*) from read_csv_auto('raw_data/kiosko/stores.csv')").fetchone()[0],
    "products": con.sql("select count(*) from read_csv_auto('raw_data/kiosko/products_v1.csv')").fetchone()[0],
}
staged = {
    "orders": con.sql("select count(*) from stg_orders").fetchone()[0],
    "events": con.sql("select count(*) from stg_events").fetchone()[0],
    "stores": con.sql("select count(*) from stg_stores").fetchone()[0],
    "products": con.sql("select count(*) from stg_products").fetchone()[0],
}

print(f"{'table':<10}{'raw':>8}{'staging':>10}{'match':>8}")
for t in ["orders", "events", "stores", "products"]:
    m = "OK" if raw[t] == staged[t] else "MISMATCH"
    print(f"{t:<10}{raw[t]:>8}{staged[t]:>10}{m:>8}")

What to expect.

table        raw   staging   match
orders          40        40      OK
events          32        32      OK
stores           3         3      OK
products         4         4      OK

Four rows, four OK. Notice something deliberate about this script: it counts the "raw" side by reading the files directly with read_csv_auto/read_ndjson_auto — without going through source() or any staging model — and the "staging" side by querying the views dbt run already built. They're two completely independent paths to the same file on disk; that they match exactly confirms the complete conversion — from raw CSV/JSONL, to source(), to stg_* — didn't lose or duplicate a single row at any point in the chain.

Part 4 — Confirm the project's complete listing

dbt ls --select staging
dbt ls --select source:kiosko_raw

What to expect.

kiosko_analytics.staging.kiosko.stg_events
kiosko_analytics.staging.kiosko.stg_orders
kiosko_analytics.staging.kiosko.stg_products
kiosko_analytics.staging.kiosko.stg_stores

source:kiosko_analytics.kiosko_raw.events
source:kiosko_analytics.kiosko_raw.orders
source:kiosko_analytics.kiosko_raw.products
source:kiosko_analytics.kiosko_raw.stores

Four sources, four staging models — a perfect one-to-one correspondence, exactly as lesson 6 promised. No source without its staging model; no staging model without its source.

Part 5 — The project's second commit

With everything verified, it's time to version the module's work. First review what changed, without adding anything yet:

git status --short

What to expect.

 M dbt_project.yml
 D models/example/my_first_dbt_model.sql
?? models/staging/
?? raw_data/

Four kinds of change, each with its own story: dbt_project.yml modified (lesson 6's staging: +materialized: view block), models/example/my_first_dbt_model.sql deleted (module 1's trivial model, removed in lesson 2), and two new, untracked folders, models/staging/ and raw_data/. Notice that no generated file shows up in this list — not kiosko.duckdb, not target/, not logs/ — because the .gitignore you wrote in module 1 is still doing exactly its job, with no need for you to touch it.

Add the files and commit:

git add raw_data models dbt_project.yml
git commit -m "Module 2: declare Kiosko sources and build the staging layer"

What to expect.

[master a1b2c3d] Module 2: declare Kiosko sources and build the staging layer
 24 files changed, 91 insertions(+), 7 deletions(-)
 create mode 100644 models/staging/kiosko/_models.yml
 create mode 100644 models/staging/kiosko/_sources.yml
 create mode 100644 models/staging/kiosko/stg_events.sql
 create mode 100644 models/staging/kiosko/stg_orders.sql
 create mode 100644 models/staging/kiosko/stg_products.sql
 create mode 100644 models/staging/kiosko/stg_stores.sql
 delete mode 100644 models/example/my_first_dbt_model.sql
 create mode 100644 raw_data/kiosko/events_2026-08-03.jsonl
 create mode 100644 raw_data/kiosko/orders_2026-08-03.csv
 ...

(The commit's short identifier, a1b2c3d in this example, is going to be different on your machine — as you already saw in module 1, it's a hash generated from the exact content and the moment of the commit.) Confirm the complete history and a clean working tree:

git log --oneline
git status

What to expect.

a1b2c3d (HEAD -> master) Module 2: declare Kiosko sources and build the staging layer
af0b710 First dbt project: kiosko_analytics scaffolding

On branch master
nothing to commit, working tree clean

Two commits, each with a message describing, in one line, what changed and why — a teammate who runs git log years after this guide ends is going to be able to read, with no ambiguity, exactly when the project went from having one trivial model to having real sources and staging models.

Diagram: the complete project at the end of module 2

flowchart LR
    subgraph raw["raw_data/kiosko/ (16 files)"]
        O["orders_*.csv (40 rows)"]
        E["events_*.jsonl (32 rows)"]
        S["stores.csv (3 rows)"]
        P["products_v1.csv (4 rows)"]
    end
    subgraph src["sources.yml (kiosko_raw)"]
        SO["source: orders"]
        SE["source: events"]
        SS["source: stores"]
        SP["source: products"]
    end
    subgraph stg["models/staging/kiosko/"]
        GO["stg_orders (view, 40 rows)"]
        GE["stg_events (view, 32 rows)"]
        GS["stg_stores (view, 3 rows)"]
        GP["stg_products (view, 4 rows)"]
    end
    O --> SO --> GO
    E --> SE --> GE
    S --> SS --> GS
    P --> SP --> GP

Four independent arrows, with no crossing between them — the exact visual representation of lesson 5's rule: each staging model depends on exactly one source, without combining with any other.

Common mistakes

Forgetting git add on raw_data/, thinking it's "just test data." What happens: someone, used to kiosko.duckdb not being versioned (module 1's lesson 8), reflexively assumes raw_data/ shouldn't be versioned either, and leaves it out of the commit. Why it happens: both folders contain "data," and it's easy to generalize the rule incorrectly. How to spot it: if you cloned this repository on a new machine and ran dbt run --select staging without raw_data/, it would fail immediately with the same IO Error: No files found you already saw in lesson 4 — because, unlike kiosko.duckdb, raw_data/ isn't a derived artifact dbt can regenerate; it's the original source everything else depends on. How to fix it: the correct rule isn't "data yes/no gets versioned" — it's "can this file be rebuilt from another already-versioned file?" kiosko.duckdb can (it's rebuilt with dbt run); raw_data/kiosko/*.csv and *.jsonl can't — they're the starting point — so they do get versioned.

Confusing dbt run with dbt run --select staging once there are more than four models. What happens: someone, in a future module with marts on top of staging models, runs dbt run --select staging expecting the marts that depend on those staging models to run too. Why it happens: it's easy to assume "running staging" implies, in cascade, running everything that depends on staging. How to spot it: if you query a mart after running only --select staging and see stale data or the mart doesn't exist yet, check exactly what you selected. How to fix it: --select staging selects only the models inside the models/staging/ folder (and its subfolders) — nothing that depends on them runs automatically, unless you use the + operator (--select staging+), something you're going to meet in module 3, when ref() makes that cascade make sense for the first time.

Interpreting PASS=8 in dbt test as "Kiosko's data has no problems at all." What happens: someone sees the eight tests in green and concludes Kiosko's complete dataset is free of any inconsistency. Why it happens: eight PASS feels exhaustive, but only two kinds of test (unique, not_null) were declared on four specific columns (the primary identifiers) — nothing more. How to spot it: ask yourself which columns and which rules don't have any test yet — for example, nothing validates yet that event_type only contains page_view/add_to_cart/purchase, or that quantity is never negative. How to fix it: this lesson deliberately declared the minimum needed to close out the module — the whole of module 4 is dedicated to expanding that coverage with the other two out-of-the-box generic tests, a custom test, and singular tests for specific business rules.

Exercises

Exercise 1 — Rebuild the reconciliation for a hypothetical fifth file. If Kiosko added deliveries.csv (from lesson 6's exercise) with 15 rows, and you built stg_deliveries correctly, what new line would you add to Part 3's script's raw dictionary and staged dictionary, and what would you expect to see in the final output?

raw["deliveries"] = con.sql("select count(*) from read_csv_auto('raw_data/kiosko/deliveries.csv')").fetchone()[0]
staged["deliveries"] = con.sql("select count(*) from stg_deliveries").fetchone()[0]

Exercise 2 — Trigger a failed reconciliation on purpose. Temporarily modify stg_stores.sql to have a WHERE city != 'Lima' (filtering out store S02), and run Part 3's reconciliation script again. What changes in the output?

Exercise 3 — Argue, in your own words, why this mini-project is the right starting point for module 3. In 2-3 sentences, explain what guarantees having all four staging tables complete, tested, and versioned gives you, before you start building dim_store, dim_date, and fact_orders.

Summary and next step

In this mini-project you completed Kiosko project's staging layer: stg_stores and stg_products, the two models that were missing, run alongside stg_orders and stg_events with dbt run --select staging (PASS=4) and tested with dbt test --select staging (PASS=8). You verified, with an independent reconciliation between the raw file and the staging view, that all four tables — 40, 32, 3, and 4 rows — didn't lose or duplicate a single row at any point in the chain. And you closed the module with the project's second commit, extending the version-control habit module 1 started.

With this you close module 2. You now have four sources declared and verified, four staging models clean, tested, and versioned, and a kiosko_analytics/ project with two commits in its history — each documenting, in one line, a real, verifiable milestone of the project.

Where you go next. Module 3 introduces the ref() function — the one that builds the dependency graph dbt resolves for you — and uses stg_orders and stg_stores as the first two models a real mart depends on: you're going to rebuild dim_store, dim_date, and fact_orders, the complete star schema data-modeling-for-analytics-guide designed by hand, now as chained dbt models. The staging layer you built in this module doesn't change at all from here on; the only thing that changes is that, for the first time, something else in the project is going to depend on it.

Resources

• dbt Developer Hub — "About dbt projects," the overview confirming the complete structure — sources, staging, and what follows — this mini-project consolidates. docs.getdbt.com/docs/build/projects. In English.
• dbt Developer Hub — "dbt ls," again the reference for the command used in Part 4 to confirm the one-to-one correspondence between sources and staging models. docs.getdbt.com/reference/commands/list. In English.
• Git — official git log documentation, including formatting options (--oneline, used in this lesson) for reviewing a project's commit history. git-scm.com/docs/git-log. In English.