Module 8: Project Kioskos Dbt Warehouse

Assembling the complete project structure

Next →

Description

Lesson 3 wrote five new .sql files, each verified individually. This lesson steps back and looks at the complete project: kiosko_analytics/'s final file tree, models/marts/'s _models.yml with the five new pieces' descriptions and data_tests: (the piece lesson 3 deliberately left pending), and dbt ls confirming, mart by mart, that the dependency graph ended up exactly as the previous lesson's diagram predicted. None of this runs any model yet — it's the review confirming the structure is complete and correct before running dbt build over the whole project, in lesson 5.

Connection to the module. Lesson 3 built; this lesson organizes and declares. The pattern is the same one module 4 already followed with fact_orders's tests: first the model works with an individual query, then the data_tests: get declared that confirm — automatically, repeatably — it's going to keep working even if the data changes.

An analogy: the final blueprint, after every box is already on its shelf

Lesson 3 was moving-day: putting each box in its place. This lesson is the moment someone walks through the complete warehouse with the official blueprint in hand, confirming every shelf has the right label, that the warehouse's index (_models.yml) mentions every new box, and that no aisle was left with a box out of place. Nothing gets moved — it gets confirmed that what already got moved is where the blueprint says it should be.

models/marts/'s complete tree

After lesson 3, models/marts/ has eight .sql files and two configuration files:

kiosko_analytics/
├── dbt_project.yml
├── profiles.yml
├── raw_data/kiosko/            <- unchanged since module 5 (7 CSV + 7 JSONL + stores + products_v1/v2)
├── models/
│   ├── staging/kiosko/         <- 4 staging models, untouched since module 2
│   │   ├── _sources.yml
│   │   ├── _models.yml
│   │   ├── stg_orders.sql
│   │   ├── stg_events.sql
│   │   ├── stg_stores.sql
│   │   └── stg_products.sql
│   └── marts/
│       ├── _models.yml         <- UPDATED this lesson
│       ├── _docs.md            <- unchanged since module 7
│       ├── dim_store.sql       <- unchanged since module 3
│       ├── dim_date.sql        <- unchanged since module 3
│       ├── fact_orders.sql     <- unchanged since module 7
│       ├── dim_category.sql        <- NEW (lesson 3)
│       ├── dim_order_flags.sql     <- NEW (lesson 3)
│       ├── fact_sessions.sql       <- NEW (lesson 3)
│       ├── fact_store_activity.sql <- NEW (lesson 3)
│       └── mart_daily_sales_obt.sql <- NEW (lesson 3)
├── macros/                     <- 2 macros, unchanged since module 7
├── snapshots/                  <- 1 snapshot, unchanged since module 5
└── tests/                      <- 3 singular tests (1 inherited + 2 NEW this lesson)
    ├── assert_no_negative_revenue.sql
    ├── assert_unique_store_activity_date.sql
    └── assert_obt_revenue_matches_fact_orders.sql

Twelve .sql models in total (four staging, eight marts), exactly the number lesson 1's Exercise 2 asked you to predict. Notice something deliberate: raw_data/kiosko/, models/staging/kiosko/, macros/, and snapshots/ don't show up marked as modified — none of the five new marts needed to touch them, the same observation lesson 1 already made in its "Why the order matters" section.

The two new singular tests

Besides the data_tests: declared in _models.yml (the next section), this lesson adds two new files in tests/ — the same singular-test mechanism module 4 already taught you with assert_no_negative_revenue.sql.

tests/assert_unique_store_activity_date.sql confirms a cumulative table design's central property: exactly one row per store-and-date combination, never more than one.

-- tests/assert_unique_store_activity_date.sql

-- fact_store_activity is a cumulative table design: exactly one row per
-- store_id + activity_date combination, never more than one. This query
-- returns the duplicate combinations -- if it returns zero rows, the test passes.
select
    store_id,
    activity_date,
    count(*) as n_rows
from {{ ref('fact_store_activity') }}
group by store_id, activity_date
having count(*) > 1

tests/assert_obt_revenue_matches_fact_orders.sql confirms the business rule lesson 3 already previewed: aggregating to a coarser grain can change the row count, but never the total revenue.

-- tests/assert_obt_revenue_matches_fact_orders.sql

-- mart_daily_sales_obt groups fact_orders to a coarser grain (day + store +
-- product), but the total revenue should never change along the way. This
-- query returns a row if the two totals don't match -- zero rows, test passes.
with fact_total as (
    select round(sum(revenue), 2) as total_revenue
    from {{ ref('fact_orders') }}
),

obt_total as (
    select round(sum(revenue), 2) as total_revenue
    from {{ ref('mart_daily_sales_obt') }}
)

select
    fact_total.total_revenue as fact_orders_revenue,
    obt_total.total_revenue  as obt_revenue
from fact_total
cross join obt_total
where fact_total.total_revenue != obt_total.total_revenue

Neither test is a generic test — they don't apply to a single column with an off-the-shelf rule like unique or not_null — both capture a business rule specific to a specific mart, exactly the criteria module 4 already taught you to use to decide when a singular test is the right tool.

Complete _models.yml: descriptions and data_tests for the five new pieces

This is models/marts/_models.yml's complete file, with the three inherited entries (dim_store, dim_date, fact_orders, with no change at all) and the five new ones:

# models/marts/_models.yml
version: 2

models:
  - name: dim_store
    description: "Store dimension, a dbt model over stg_stores via ref()."

  - name: dim_date
    description: "Date dimension, a fixed August 2026 range generated with a deterministic recursive CTE."

  - name: fact_orders
    description: "Sales fact, order-line grain. Joins stg_orders with dim_store and dim_date via ref()."
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: product_id
        data_tests:
          - accepted_values:
              arguments:
                values: ['P001', 'P002', 'P003', 'P004']
      - name: store_id
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_store')
                field: store_id
      - name: quantity
        data_tests:
          - is_positive:
              arguments:
                strict: true
      - name: unit_price
        data_tests:
          - is_positive:
              arguments:
                strict: false
      - name: revenue
        description: "{{ doc('fact_orders_revenue') }}"

  - name: dim_category
    description: "Category dimension, normalized from stg_products (snowflake schema). Includes health-snacks, P002's current category after the change dim_product_snapshot historizes."
    columns:
      - name: category_id
        data_tests:
          - unique
          - not_null
      - name: category_name
        data_tests:
          - not_null

  - name: dim_order_flags
    description: "Junk dimension: the precomputed cartesian product of payment_method (cash/card/wallet) and channel (in_store/app), 6 fixed rows."
    columns:
      - name: flag_key
        data_tests:
          - unique
          - not_null
      - name: payment_method
        data_tests:
          - accepted_values:
              arguments:
                values: ['cash', 'card', 'wallet']
      - name: channel
        data_tests:
          - accepted_values:
              arguments:
                values: ['in_store', 'app']

  - name: fact_sessions
    description: "Accumulating snapshot of the session funnel (page_view -> add_to_cart -> purchase) over Kiosko's 32 canonical events."
    columns:
      - name: session_id
        data_tests:
          - unique
          - not_null
      - name: store_id
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_store')
                field: store_id

  - name: fact_store_activity
    description: "Cumulative table design: daily revenue by store with 7- and 30-day rolling arrays, recalculated with a SQL window function."
    columns:
      - name: store_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_store')
                field: store_id
      - name: activity_date
        data_tests:
          - not_null

  - name: mart_daily_sales_obt
    description: "Daily sales OBT: fact_orders + dim_store + dim_product_snapshot (point-in-time join) + dim_date, all flattened, day + store + product grain."
    columns:
      - name: sale_date
        data_tests:
          - not_null
      - name: store_id
        data_tests:
          - not_null
      - name: product_id
        data_tests:
          - not_null

Count the new data_tests:: dim_category adds 3 (unique and not_null on category_id, not_null on category_name); dim_order_flags adds 4 (unique/not_null on flag_key, accepted_values on payment_method and on channel); fact_sessions adds 3 (unique/not_null on session_id, relationships on store_id); fact_store_activity adds 3 declared in YAML (not_null on store_id and activity_date, relationships on store_id) plus the previous section's singular test; mart_daily_sales_obt adds 3 declared (not_null on the grain's three columns) plus its own singular test. Eighteen new data_tests in total, on top of the fifteen that already existed since module 4 — lesson 5 confirms this exact number when you run dbt build.

Notice a pattern repeating three times: store_id's relationships against ref('dim_store'), declared on fact_orders (module 4), fact_sessions, and fact_store_activity (this lesson). All three columns test the same thing — that no store_id points at a store that doesn't exist — over three different facts that share the same conformed dimension. This is, precisely, what "conformed dimension" means in practice: the same generic test, reused with no change at all, on any fact that shares that dimension.

Confirming the structure with no execution: dbt parse

Before running any model, confirm dbt can read the complete project with no YAML or Jinja syntax error:

dbt parse

What to expect. No error line at all — dbt parse only fails if something in the project's structure (a badly indented YAML, an unclosed {{ }} brace) stops it from building the complete graph. A silent run is, by itself, confirmation that the eight .sql files and the updated _models.yml are syntactically valid.

Inspecting each new mart's graph with dbt ls

With the structure confirmed, check each new mart's real dependency — the same check module 3's lesson 7 already taught you, now applied to new pieces:

dbt ls --select +dim_category

What to expect.

kiosko_analytics.marts.dim_category
kiosko_analytics.staging.kiosko.stg_products
source:kiosko_analytics.kiosko_raw.products
kiosko_analytics.marts.not_null_dim_category_category_id
kiosko_analytics.marts.not_null_dim_category_category_name
kiosko_analytics.staging.kiosko.not_null_stg_products_product_id
kiosko_analytics.marts.unique_dim_category_category_id
kiosko_analytics.staging.kiosko.unique_stg_products_product_id

dbt ls --select +dim_order_flags

What to expect.

kiosko_analytics.marts.dim_order_flags
kiosko_analytics.marts.accepted_values_dim_order_flags_channel__in_store__app
kiosko_analytics.marts.accepted_values_dim_order_flags_payment_method__cash__card__wallet
kiosko_analytics.marts.not_null_dim_order_flags_flag_key
kiosko_analytics.marts.unique_dim_order_flags_flag_key

Five resources in total, and none of them is a source or a stg_* — visual confirmation that dim_order_flags really is the only mart with no external dependency at all: its ancestor tree is empty, only its own tests show up.

dbt ls --select +fact_store_activity

What to expect.

kiosko_analytics.marts.dim_date
kiosko_analytics.marts.dim_store
kiosko_analytics.marts.fact_orders
kiosko_analytics.marts.fact_store_activity
kiosko_analytics.staging.kiosko.stg_orders
kiosko_analytics.staging.kiosko.stg_stores
source:kiosko_analytics.kiosko_raw.orders
source:kiosko_analytics.kiosko_raw.stores
[... the data_tests inherited from fact_orders, dim_store, and fact_store_activity, via indirect selection ...]

This tree is the largest of the four you just inspected, and for a concrete reason: fact_store_activity depends on fact_orders, which in turn depends on dim_store and dim_date — three levels deep, the same chain you already saw in module 3, now with one more level on top.

Common mistakes

Declaring a data_tests: over a column the model doesn't have. What happens: someone copies the relationships pattern for store_id in mart_daily_sales_obt, but misspells the column name — store instead of store_id — with no text editor flagging it as an error. Why it happens: YAML doesn't validate column names against a model's real schema — any text is syntactically valid. How to spot it: dbt build (or dbt test) fails with a compilation error mentioning the column that doesn't exist, as soon as it tries to run that specific test — a late error, at the execution stage, not at dbt parse. How to fix it: after declaring any new data_tests:, run dbt run --select <model> first to confirm the model compiles, and only then dbt test --select <model> to confirm the declared tests point at real columns.

Thinking dbt parse certifies the models are correct. What happens: someone runs dbt parse, sees no error, and assumes the five new marts are going to work correctly once run. Why it happens: "no errors" feels like a complete validation, but dbt parse only confirms the project can be read — valid YAML syntax, well-formed Jinja, the ref() graph can be built — never that the underlying SQL is correct against the real engine. How to spot it: a badly written JOIN, like the ones you already broke on purpose in earlier modules, passes dbt parse with no problem at all — it's syntactically valid — and only fails (or worse, produces an incorrect result with no error at all) when it really runs against DuckDB. How to fix it: dbt parse is the first filter, not the only one — lesson 3 already ran every model individually with dbt run --select, and lesson 5 runs them all together; neither replaces the other.

Forgetting to update _models.yml when adding a new model, and not noticing the omission. What happens: someone writes lesson 3's five .sql files, runs them successfully with dbt run --select, and calls the module done without adding any new entry to _models.yml. Why it happens: a model with no entry in _models.yml runs perfectly well — dbt doesn't require documentation or tests for a model to work, so there's no error flagging the omission. How to spot it: if dbt build reports fewer than 33 total data_tests, or if dbt docs generate (module 7) shows a model with no description at all, that's a sign _models.yml was left incomplete. How to fix it: this module's discipline is the same one module 4 already insisted on — every new model needs its own entry in _models.yml, with at least a description:, before considering the work done.

Exercises

Exercise 1 — Count how many data_tests each new mart has, with no execution. Using only this lesson's _models.yml, count how many data_tests: each of the five new marts declares (not counting tests/'s singular tests), and confirm the sum is 16.

See solution

dim_category: 3 (unique + not_null on category_id, not_null on category_name). dim_order_flags: 4 (unique + not_null on flag_key, accepted_values on payment_method and on channel). fact_sessions: 3 (unique + not_null on session_id, relationships on store_id). fact_store_activity: 3 declared in YAML (not_null on store_id and activity_date, relationships on store_id). mart_daily_sales_obt: 3 (not_null on sale_date, store_id, product_id). Total: 3+4+3+3+3 = 16 declared in YAML, plus tests/'s 2 singular tests (assert_unique_store_activity_date, assert_obt_revenue_matches_fact_orders) gives 18 total — the same number this lesson's _models.yml section already previewed.

Exercise 2 — Predict what would happen to dbt ls --select +mart_daily_sales_obt if dim_order_flags didn't exist yet. Without running any command, reason: would dim_order_flags show up in +mart_daily_sales_obt's tree? Why or why not?

See solution

It wouldn't show up. dbt ls --select +mart_daily_sales_obt lists only mart_daily_sales_obt's ancestors — the nodes it depends on, directly or indirectly — and mart_daily_sales_obt.sql never declares any ref('dim_order_flags'). The two marts are completely independent of each other: dim_order_flags exists for future payment-method and channel analysis, not to complete the daily-sales wide table — the same independence lesson 3 already explained when building them separately.

Exercise 3 — Argue why reusing the store_id relationships test (across three different models) is evidence of good design, not repeated code. In 2-3 sentences, explain why declaring the same generic test three times — on fact_orders, fact_sessions, and fact_store_activity — isn't the same as copying and pasting logic with no thought.

See solution

Each relationships declaration tests a different relationship even though it uses the same generic mechanism: fact_orders.store_id against dim_store is a different check, on a different model, from fact_sessions.store_id against dim_store — both share the rule ("every store_id must exist in the conformed dimension"), but each one protects its own fact from a real, independent problem. This is exactly what makes a conformed dimension valuable (the concept data-modeling-for-analytics-guide already introduced): the same generic test, with zero new code to write, gets reused on any fact that shares that dimension — the repetition here is a sign the design is working, not a symptom of mindlessly duplicated code.

Summary and next step

In this lesson you organized and declared, with no new model run yet: kiosko_analytics/'s complete tree with twelve .sql models, models/marts/'s complete _models.yml with eighteen new data_tests over lesson 3's five pieces, and two new singular tests in tests/. You confirmed with dbt parse that the complete project can be read with no error at all, and inspected with dbt ls four of the five new marts' real dependency tree, confirming it matches exactly what lesson 3's diagram predicted.

Before moving on you should be able to: count how many new data_tests this lesson added (eighteen); and explain why dim_order_flags is the only one of the five new marts that doesn't show up in any other model's ancestor tree in the project.

Lesson 5 finally runs the complete command: dbt build over the whole project — twelve models, one snapshot, thirty-three data_tests — in a single pass.

Resources

  • dbt Developer Hub — "Add data tests to your DAG," already cited in module 4, the central reference for the data_tests: syntax this lesson applies to five new marts. docs.getdbt.com/docs/build/data-tests. In English.
  • dbt Developer Hub — "dbt ls," already cited in modules 2 and 3, again confirming the --select +<model> syntax used in this lesson to inspect each dependency tree. docs.getdbt.com/reference/commands/list. In English.
  • dbt Developer Hub — "dbt parse," the reference for the command that confirms the project's structural validity with no model run at all. docs.getdbt.com/reference/commands/parse. In English.
← PreviousPorting the rest of data-modeling's martsNext →Running `dbt build` end to end