Building `stg_orders` and `stg_events`

Description

Lessons 5 and 6 defined a staging model's shape, name, and configuration — but so far, none of those rules live in a real .sql file. This lesson writes the first two: stg_orders.sql and stg_events.sql, the staging models for Kiosko's two largest tables. It runs them with dbt run, verifies each one's row count matches its raw file's exactly — 40 and 32, respectively — and adds the project's first data_tests: a minimal check that order_id and event_id are unique, never-null values, the first preview of a topic module 4 is going to cover in depth.

Connection to the module. Everything before this in the module was declaration, analysis, and configuration — sources, the source() function, the staging rule, the name, and the materialization. This is the first lesson where Kiosko's project gains real, persistent, versionable models. Lesson 8 completes the work with stg_stores and stg_products, closing out the whole layer.

Worked example: stg_orders.sql

Inside models/staging/kiosko/ (already created since lesson 2), create the project's first real file:

-- models/staging/kiosko/stg_orders.sql
select
    order_id,
    store_id,
    product_id,
    cast(quantity as integer) as quantity,
    cast(unit_price as decimal(10, 2)) as unit_price,
    cast(order_ts as timestamp) as order_ts
from {{ source('kiosko_raw', 'orders') }}

Each cast(...) answers a concrete decision, not an automatic reflex:

• quantity → integer. DuckDB already inferred it as a number when reading the CSV (you saw this in lesson 4), but declaring it explicitly inside the model documents the intent — "this is always an integer" — without depending on the automatic inference continuing to guess right if, someday, the raw file's format changes slightly.
• unit_price → decimal(10, 2). A money value should never live as a floating-point number (float/double) in a real analytics project — floating-point rounding errors are exactly the kind of silent bug a fixed-precision decimal avoids by design. decimal(10, 2) means "up to 10 total digits, 2 after the decimal point" — more than enough for any Kiosko price.
• order_ts → timestamp. The raw file has it as ISO 8601 text (2026-08-03T08:14:00); casting it to timestamp gives any future model access to real date functions — extracting the day, comparing ranges — without having to re-parse text every time.

Notice what does not change: order_id, store_id, and product_id are selected as-is, with no explicit cast() — they're already text (varchar) in the raw file, and text is exactly what they should stay (they're identifiers, not numbers you're going to do arithmetic with).

Worked example (continued): stg_events.sql

-- models/staging/kiosko/stg_events.sql
select
    event_id,
    event_type,
    session_id,
    cast(event_ts as timestamp) as event_ts
from {{ source('kiosko_raw', 'events') }}

Simpler than stg_orders, because events itself is a simpler table: four columns, and only event_ts needs an explicit cast, for the same reason order_ts did in the previous model. event_type stays as text — page_view, add_to_cart, purchase — with no validation yet that those are the only three possible values; that specific validation (accepted_values) is, again, module 4's territory.

Running the two models

dbt run --select stg_orders stg_events

What to expect.

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

Concurrency: 4 threads (target='dev')

1 of 2 START sql view model main.stg_events .................................... [RUN]
2 of 2 START sql view model main.stg_orders .................................... [RUN]
1 of 2 OK created sql view model main.stg_events ............................... [OK in 0.05s]
2 of 2 OK created sql view model main.stg_orders ............................... [OK in 0.05s]

Finished running 2 view models in 0 hours 0 minutes and 0.13 seconds (0.13s).

Completed successfully

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

(You're going to see 4 data tests in the initial Found ... because, as part of this lesson, you also add _models.yml — the next step. If you haven't written it yet, that number is going to say 0 data tests; that's not an error, you just haven't declared any yet.) The two models run in parallel — dbt has no reason to wait for one to finish before starting the other, since neither depends on the other — and both end OK, with no errors.

Verifying the row count: this lesson's real test

PASS=2 confirms the SQL is valid — but a staging model's real guarantee, the one that actually matters, is that the row count didn't change relative to the raw file. Verify it:

import duckdb
con = duckdb.connect("kiosko.duckdb")
print(con.sql("select count(*) as n_rows from stg_orders"))
print(con.sql("select count(*) as n_rows from stg_events"))

What to expect.

┌────────┐
│ n_rows │
│ int64  │
├────────┤
│     40 │
└────────┘

┌────────┐
│ n_rows │
│ int64  │
├────────┤
│     32 │
└────────┘

40 and 32 — exactly the same numbers you computed by adding rows file by file in lesson 2, and the same ones you confirmed straight from the sources in lesson 4. That's the result that makes a staging model reliable: not one row more, not one row less, at any point in the chain — from the raw file, to the source, to the staging model. You can get the same result without leaving dbt, with dbt show --inline:

dbt show --inline "select count(*) as n_rows from stg_orders"
dbt show --inline "select count(*) as n_rows from stg_events"

And, since stg_events has an interesting categorical column, also confirm the breakdown by event type — a preview of the kind of check you're going to automate with accepted_values in module 4:

print(con.sql("select event_type, count(*) as n from stg_events group by event_type order by event_type"))

What to expect.

┌─────────────┬───────┐
│ event_type  │   n   │
│   varchar   │ int64 │
├─────────────┼───────┤
│ add_to_cart │     9 │
│ page_view   │    17 │
│ purchase    │     6 │
└─────────────┴───────┘

9 + 17 + 6 = 32 — the breakdown adds up to exactly the total you already confirmed, and the three values are, precisely, the three event types Kiosko's design declares as valid (page_view, add_to_cart, purchase).

Worked example: the project's first data_tests

Before closing this lesson, declare the project's first automatic check — something minimal, just a preview of the full module 4: that order_id and event_id are unique, never-null values, the most basic property expected of any identifier.

# 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

Notice the key is data_tests:, not tests: — the legacy tests: key still works in dbt-core 1.12.2 for backward compatibility, but data_tests: is the current name since dbt-core 1.8, chosen precisely to tell these tests (which run against the data) apart from dbt's more recent unit tests (which run against a model's logic, without touching the warehouse — outside this guide's scope). This guide always uses data_tests:, with no exceptions.

unique and not_null are two of the four out-of-the-box generic tests dbt-core ships with — you didn't write them, they come included; module 4 covers them in depth, along with the other two (accepted_values, relationships) and how to write your own. Run the tests:

dbt test --select stg_orders stg_events

What to expect.

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

Concurrency: 4 threads (target='dev')

1 of 4 START test not_null_stg_events_event_id ................................. [RUN]
2 of 4 START test not_null_stg_orders_order_id ................................. [RUN]
3 of 4 START test unique_stg_events_event_id ................................... [RUN]
4 of 4 START test unique_stg_orders_order_id ................................... [RUN]
1 of 4 PASS not_null_stg_events_event_id ....................................... [PASS in 0.05s]
3 of 4 PASS unique_stg_events_event_id ......................................... [PASS in 0.05s]
4 of 4 PASS unique_stg_orders_order_id ......................................... [PASS in 0.06s]
2 of 4 PASS not_null_stg_orders_order_id ....................................... [PASS in 0.06s]

Finished running 4 data tests in 0 hours 0 minutes and 0.14 seconds (0.14s).

Completed successfully

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

Four tests, four PASS. Notice how dbt names each test automatically — unique_stg_orders_order_id, not_null_stg_events_event_id — combining the test type, the model's name, and the column's name: a consistent pattern that makes every test name self-explanatory, with no need for you to come up with it yourself.

Diagram: what exists at the end of this lesson

kiosko_analytics/
├── dbt_project.yml          <- with the staging: +materialized: view block (lesson 6)
├── profiles.yml
├── raw_data/kiosko/         <- 16 files (lesson 2)
└── models/
    └── staging/
        └── kiosko/
            ├── _sources.yml   <- 4 sources, with external_location (lesson 4)
            ├── _models.yml    <- data_tests for stg_orders and stg_events (this lesson)
            ├── stg_orders.sql   <- NEW, 40 rows
            └── stg_events.sql   <- NEW, 32 rows

stg_stores.sql and stg_products.sql still don't exist — lesson 8 completes them, along with the final run of the whole module.

Common mistakes

Casting unit_price to float instead of decimal. What happens: someone, used to other languages where "number with decimals" automatically means float, writes cast(unit_price as float). Why it happens: float/double are the best-known numeric types with decimals, and it isn't always clear why a money value needs something different. How to spot it: repeated sums of float values can accumulate tiny but real rounding errors (for example, 0.1 + 0.2 doesn't always give exactly 0.3 in floating point) — a problem that rarely shows up with few rows, but becomes real with thousands of chained sums in a financial report. How to fix it: any column representing money always gets cast to decimal(precision, scale), never to float/double — as stg_orders.sql does in this lesson.

Forgetting _models.yml uses the models: key, not sources:. What happens: someone, used to the previous lessons' _sources.yml, tries to declare stg_orders's test inside the same sources: block, or creates a separate file but repeats the wrong key. Why it happens: both files live in the same folder and use a similar YAML structure (version: 2, nested lists with name and columns), so it's easy to copy the wrong key out of habit. How to spot it: if dbt parse or dbt run fail with a schema validation error, or if dbt test reports Found 0 data tests when you expected 4, check that the file has models: as its top-level key, not sources:. How to fix it: sources: describes metadata for raw data (with meta.external_location); models: describes metadata for models you wrote yourself (with data_tests, among other things) — they're two different dbt resources, and each has its own top-level key, even though they live in the same folder.

Running dbt run with no --select and being surprised at what runs. What happens: someone, after writing stg_orders.sql and stg_events.sql, runs plain dbt run (with no --select), expecting only those two models to run. Why it happens: in a project with few models, plain dbt run and dbt run --select stg_orders stg_events feel interchangeable — and, in fact, at this exact point in the module, if those are the only two models that exist, the result is identical. How to spot it: the difference becomes real in lesson 8, once four staging models exist — plain dbt run would run all four, while --select stg_orders stg_events still runs only two, no matter how many more models the project has. How to fix it: get used, starting now, to using --select explicitly whenever you want a subset — it's a habit that becomes essential in any real project, where running the whole project on every iteration would be slow and unnecessary.

Exercises

Exercise 1 — Add a test to a column that doesn't have one yet. Extend _models.yml so stg_orders.quantity has a not_null test (no unique, since several orders can legitimately have the same quantity). Run dbt test --select stg_orders and confirm you now see 3 tests instead of 2 for that model.

  - 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: quantity
        data_tests:
          - not_null

Exercise 2 — Break a test on purpose and read the FAIL report. Temporarily change stg_orders.sql so order_id isn't unique, by adding a union all that repeats a row known by its exact order_id (don't use limit for this — with no parentheses, limit applies to the whole combined query, not just the second half, and doesn't produce the duplicate you're after):

-- at the end of stg_orders.sql, temporarily
union all
select
    order_id,
    store_id,
    product_id,
    cast(quantity as integer) as quantity,
    cast(unit_price as decimal(10, 2)) as unit_price,
    cast(order_ts as timestamp) as order_ts
from {{ source('kiosko_raw', 'orders') }}
where order_id = 'ORD-1001'

Run dbt run --select stg_orders and then dbt test --select stg_orders, and observe the result.

2 of 2 FAIL 1 unique_stg_orders_order_id ....................................... [FAIL 1 in 0.09s]
1 of 2 PASS not_null_stg_orders_order_id ....................................... [PASS in 0.09s]

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in test unique_stg_orders_order_id (models/staging/kiosko/_models.yml)
  Got 1 result, configured to fail if != 0

Exercise 3 — Argue why the row count is a stronger test than "zero errors." In 2-3 sentences, explain the difference between trusting that dbt run ended with PASS=2 and trusting that stg_orders has exactly 40 rows — what kind of error would the second one catch that the first would never catch?

Summary and next step

In this lesson you wrote the project's first two real .sql files: stg_orders.sql and stg_events.sql, with an explicit cast() on every column that needed one. You ran them with dbt run --select stg_orders stg_events, confirmed each one's row count — 40 and 32 — exactly matches its raw file's, and added the project's first data_tests: unique and not_null on order_id and event_id, verified with dbt test.

Before moving on you should be able to: write a staging model with at least two cast columns from memory; and explain why data_tests: (not tests:) is the correct key for declaring checks on a model.

Lesson 8 closes the module by completing stg_stores and stg_products — the two missing catalogs — running the complete staging layer with dbt run --select staging, and confirming all four tables' counts at once.

Resources

• DuckDB — "Numeric Types," official reference for the DECIMAL(width, scale) type used to cast unit_price in this lesson's stg_orders.sql. duckdb.org/docs/stable/sql/data_types/numeric. In English.
• dbt Developer Hub — "Add data tests to your DAG," the official reference for the four out-of-the-box generic tests and the data_tests: key — all of module 4 builds on this page. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "Upgrading to v1.8," which documents renaming tests: to data_tests: and why both keys still work today. docs.getdbt.com/docs/dbt-versions/core-upgrade/upgrading-to-v1.8. In English.