Mini-project: Kiosko's test suite

Description

Time to bring this module's seven lessons together into a single flow, start to finish. Lessons 2 through 6 declared or wrote seven new tests on fact_orders — the four out-of-the-box generic ones, one custom one applied twice, one singular one. Lesson 7 taught you to read the complete report they produce. This mini-project runs the complete suite twice, side by side, with the exact same pattern data-engineering-foundations-guide's module 5 mini-project already used: first over the same, familiar, clean data — to confirm the suite doesn't invent problems where none exist — and then over the same batch with three deliberately broken rows that guide and data-modeling-for-analytics-guide already used — to confirm, with a real FAIL report, that the suite catches exactly the problems it promises to catch.

And it closes with this module's new piece: Kiosko project's fourth version-control commit, on top of the three modules 1, 2, and 3 left behind.

Connection to the module. This project introduces no new concept — it's the complete synthesis of lessons 2 through 7, applied end to end over a scenario with real, broken data, something no previous lesson in this module did with the complete batch at once.

An analogy: the inspector's complete shift, again

data-engineering-foundations-guide already used this analogy for its own quality mini-project: a chief inspector doesn't check a single piece at the end of the day and decide, from that, whether the whole factory worked well — they review the complete shift, and close with a two-part report: confirmation of the expected (most pieces pass with no problem) and the real alert signal (a new batch brings defective pieces). This mini-project is exactly that same structure, applied now to the dbt test suite instead of validate_orders(): Part 1 confirms 15 of 15 tests pass over the already-known data; Part 2 confirms that, over a batch with three deliberately broken rows, the suite reports the exact FAILs it's expected to report — not one more, not one less.

The material: the three files you declared in this module

If you already completed lessons 3 through 6 in order, kiosko_analytics/ already has these three files. If you're jumping straight into this mini-project, this is the complete material that needs to be in place before starting.

models/marts/_models.yml, with fact_orders's six columns that gained data_tests in lessons 3, 4, and 5 (dim_store and dim_date still have none, unchanged since module 3):

# 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, 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

macros/test_is_positive.sql, lesson 5's custom generic test:

-- macros/test_is_positive.sql
{% test is_positive(model, column_name, strict=true) %}

with validation as (

    select {{ column_name }} as value_to_check
    from {{ model }}

),

validation_errors as (

    select value_to_check
    from validation
    where value_to_check is null
        or {% if strict %}
            value_to_check <= 0
        {% else %}
            value_to_check < 0
        {% endif %}

)

select *
from validation_errors

{% endtest %}

tests/assert_no_negative_revenue.sql, lesson 6's singular test:

-- tests/assert_no_negative_revenue.sql

-- An order line's revenue (quantity * unit_price) should never be negative:
-- there's no such thing as a sale with "negative revenue" in Kiosko's business.
-- This query returns the lines that violate that rule -- if it returns zero
-- rows, the test passes.
select
    order_id,
    quantity,
    unit_price,
    revenue
from {{ ref('fact_orders') }}
where revenue < 0

And this mini-project's new material: Kiosko's eighth day, with three deliberately broken rows — the same file, with the exact same rows, data-engineering-foundations-guide (module 5) and data-modeling-for-analytics-guide already used over this same case.

-- raw_data/kiosko/orders_2026-08-10.csv
order_id,store_id,product_id,quantity,unit_price,order_ts
ORD-8001,S01,P002,2,1.20,2026-08-10T08:05:00
ORD-8002,S02,P001,4,0.55,2026-08-10T08:20:00
ORD-8003,S01,P001,0,0.55,2026-08-10T08:35:00
ORD-8004,S03,P003,1,0.75,2026-08-10T08:50:00
ORD-8005,S02,P004,2,,2026-08-10T09:10:00
ORD-8006,S01,P003,3,0.75,2026-08-10T09:25:00
ORD-8007,S03,P002,1,1.20,2026-08-10T09:40:00
ORD-8001,S02,P001,2,0.55,2026-08-10T09:55:00

Eight rows, five valid and three broken, each with a different flaw: ORD-8003 has quantity=0; ORD-8005 has an empty unit_price (the double comma, 2,,2026-08-10..., with no value between them); the last row repeats order_id=ORD-8001, the same identifier as the file's first row. Notice this file does not join the project permanently — it's a test scenario, which you're going to add and remove within this same mini-project, exactly as you already did with similar temporary files in lessons 4, 6, and 7.

The reference solution, verified

Part 1 — The complete suite over already-known data

Before touching the new batch, confirm the 15 tests pass over the usual 40 orders:

dbt test

What to expect.

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

Concurrency: 4 threads (target='dev')

[... 15 PASS lines, one for each test ...]

Finished running 15 data tests in 0 hours 0 minutes and 0.25 seconds (0.25s).

Completed successfully

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

Fifteen out of fifteen — the eight inherited from module 2 (staging), plus this module's seven (six column ones on fact_orders, plus the singular one). Also confirm the count and revenue are still the usual ones:

dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('fact_orders') }}"

What to expect.

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

If this part fails, don't move on to Part 2 — go back to whichever lesson matches the specific test that's failing: lessons 3 through 6 cover, in order, each of this module's seven new tests.

Part 2 — Adding the broken batch, and running the complete suite again

dbt run

What to expect. dbt run runs no test at all, so this command ends in PASS=7 — the seven models, including fact_orders rebuilt with the new file — with no ERROR: an INSERT with quantity=0 or an empty unit_price is perfectly valid SQL, even though it violates business rules no SELECT on its own detects.

dbt test

What to expect.

Found 7 models, 15 data tests, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 15 START test accepted_values_fact_orders_product_id__P001__P002__P003__P004  [RUN]
2 of 15 START test assert_no_negative_revenue .................................. [RUN]
3 of 15 START test is_positive_fact_orders_quantity__True ...................... [RUN]
4 of 15 START test is_positive_fact_orders_unit_price__False ................... [RUN]
2 of 15 PASS assert_no_negative_revenue ........................................ [PASS in 0.06s]
4 of 15 FAIL 1 is_positive_fact_orders_unit_price__False ....................... [FAIL 1 in 0.09s]
3 of 15 FAIL 1 is_positive_fact_orders_quantity__True .......................... [FAIL 1 in 0.09s]
1 of 15 PASS accepted_values_fact_orders_product_id__P001__P002__P003__P004 .... [PASS in 0.09s]
5 of 15 START test not_null_fact_orders_order_id ............................... [RUN]
6 of 15 START test not_null_stg_events_event_id ................................ [RUN]
7 of 15 START test not_null_stg_orders_order_id ................................ [RUN]
8 of 15 START test not_null_stg_products_product_id ............................ [RUN]
5 of 15 PASS not_null_fact_orders_order_id ..................................... [PASS in 0.03s]
9 of 15 START test not_null_stg_stores_store_id ................................ [RUN]
6 of 15 PASS not_null_stg_events_event_id ...................................... [PASS in 0.04s]
8 of 15 PASS not_null_stg_products_product_id .................................. [PASS in 0.04s]
10 of 15 START test relationships_fact_orders_store_id__store_id__ref_dim_store_  [RUN]
11 of 15 START test unique_fact_orders_order_id ................................ [RUN]
7 of 15 PASS not_null_stg_orders_order_id ...................................... [PASS in 0.07s]
12 of 15 START test unique_stg_events_event_id ................................. [RUN]
9 of 15 PASS not_null_stg_stores_store_id ...................................... [PASS in 0.05s]
13 of 15 START test unique_stg_orders_order_id .................................. [RUN]
10 of 15 PASS relationships_fact_orders_store_id__store_id__ref_dim_store_ ..... [PASS in 0.05s]
11 of 15 FAIL 1 unique_fact_orders_order_id ..................................... [FAIL 1 in 0.05s]
14 of 15 START test unique_stg_products_product_id .............................. [RUN]
15 of 15 START test unique_stg_stores_store_id .................................. [RUN]
12 of 15 PASS unique_stg_events_event_id ........................................ [PASS in 0.03s]
14 of 15 PASS unique_stg_products_product_id .................................... [PASS in 0.02s]
15 of 15 PASS unique_stg_stores_store_id ........................................ [PASS in 0.02s]
13 of 15 FAIL 1 unique_stg_orders_order_id ...................................... [FAIL 1 in 0.07s]

Finished running 15 data tests in 0 hours 0 minutes and 0.41 seconds (0.41s).

Completed with 4 errors, 0 partial successes, and 0 warnings:

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

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

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

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

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

PASS=11, ERROR=4, out of 15 tests total. Read this with the same discipline lesson 7 taught: four tests in ERROR, and the number of failures — counting FAIL 1 on each one — matches exactly the three broken rows you already know, even though one of them gets caught by two different tests:

Three broken rows, four tests in ERROR — ORD-8001's duplicate gets caught by two layers of the project at once, staging and marts, exactly the defense in depth lesson 3 already explained. And notice the tests that do not fail, and why: accepted_values_fact_orders_product_id passes, because this batch's eight product_ids (P001, P002, P003, P004, repeated) are all valid; relationships_fact_orders_store_id passes, for the same reason lesson 4 already demonstrated — this batch's store_ids (S01, S02, S03) are also valid; and assert_no_negative_revenue passes, because none of the three broken rows actually produces a negative revenue: ORD-8003 gives revenue = 0 (not negative, though invalid for is_positive), ORD-8005 gives revenue = NULL (neither negative nor positive). Three tests catching the same batch from three different angles, and two tests correctly confirming those two specific columns in this batch have no problem at all.

Part 3 — Confirm the exact rows, with dbt show

dbt show --inline "select order_id, quantity from {{ ref('fact_orders') }} where quantity <= 0 or quantity is null"
dbt show --inline "select order_id, unit_price from {{ ref('fact_orders') }} where unit_price < 0 or unit_price is null"
dbt show --inline "select order_id, count(*) as n from {{ ref('fact_orders') }} group by order_id having count(*) > 1"

What to expect.

Previewing inline node:
| order_id | quantity |
| -------- | -------- |
| ORD-8003 |        0 |

Previewing inline node:
| order_id | unit_price |
| -------- | ---------- |
| ORD-8005 |            |

Previewing inline node:
| order_id |  n |
| -------- | -- |
| ORD-8001 |  2 |

Three queries, three rows, each confirming exactly what dbt test's report had already told you — the blank unit_price row for ORD-8005 is the visual representation of NULL in a dbt show table.

Part 4 — Revert the batch, and confirm the project returns to its clean state

rm raw_data/kiosko/orders_2026-08-10.csv
dbt run
dbt test

What to expect. dbt run ends in PASS=7 (fact_orders rebuilt, back to 40 rows). dbt test ends in PASS=15 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=15 — the complete suite, green again. Confirm the count and revenue one last time:

dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('fact_orders') }}"

What to expect. 40 rows, 106.15 total revenue — the same usual numbers, with no trace of the temporary batch you just added and removed.

Part 5 — The project's fourth commit

Review what changed since module 3's commit:

git status --short

What to expect.

 M models/marts/_models.yml
?? macros/
?? tests/

One modified file (_models.yml, with fact_orders's six new data_tests) and two new folders, not yet tracked (macros/, with your first custom generic test; tests/, with the first singular test). Notice raw_data/kiosko/orders_2026-08-10.csv does not show up in this list — you deleted it in Part 4, so it never even existed in the working tree at the moment of this commit.

git add macros models/marts tests
git commit -m "Module 4: add data tests to fact_orders, a custom generic test and a singular test"

What to expect.

[master d4e5f6a] Module 4: add data tests to fact_orders, a custom generic test and a singular test
 3 files changed, 62 insertions(+)
 create mode 100644 macros/test_is_positive.sql
 create mode 100644 tests/assert_no_negative_revenue.sql

(The commit's short identifier, d4e5f6a in this example, is going to be different on your machine — as you already saw in the previous modules, 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.

d4e5f6a (HEAD -> master) Module 4: add data tests to fact_orders, a custom generic test and a singular test
c3d4e5f Module 3: rebuild dim_store, dim_date and fact_orders with ref()
a1b2c3d 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

Four commits, each documenting a real, verifiable milestone: the project's scaffolding, the staging layer, the star schema, and now the test suite that confirms that star schema stays correct every time someone runs dbt test.

Diagram: the mini-project's complete flow

flowchart TD
    A["state at the end of module 3\n7 models, 8 data tests, 3 commits"] --> B["_models.yml: 6 new data_tests\non fact_orders (L3, L4, L5)"]
    B --> C["macros/test_is_positive.sql (L5)"]
    C --> D["tests/assert_no_negative_revenue.sql (L6)"]
    D --> E["dbt test over clean data -> PASS=15"]
    E --> F["add orders_2026-08-10.csv\n(3 broken rows)"]
    F --> G["dbt run -> PASS=7 (no SQL errors)"]
    G --> H["dbt test -> PASS=11 ERROR=4"]
    H --> I["dbt show: confirm the exact 3 rows"]
    I --> J["remove the broken file"]
    J --> K["dbt test -> PASS=15 again"]
    K --> L["git add + commit -> fourth commit"]

Common mistakes

Leaving orders_2026-08-10.csv in the project by accident, and getting confused in module 5. What happens: someone completes this mini-project's Part 2, sees the expected FAIL, but forgets Part 4 — deleting the file — before moving forward. Why it happens: after confirming the test suite works, it's easy to feel the exercise is "already done," without noticing the temporary file is still in raw_data/kiosko/. How to spot it: if in any later module of this guide fact_orders has 48 rows instead of 40, or a total revenue other than 106.15, first check whether orders_2026-08-10.csv still exists in raw_data/kiosko/. How to fix it: this mini-project's Part 4 isn't optional — it's as much a part of the exercise as Part 2, precisely because this entire guide depends on the project's "clean" state (40 rows, 106.15) staying stable from module to module.

Committing the broken temporary file, without noticing. What happens: someone runs git add . (instead of naming folders explicitly, as module 1 already warned) after Part 2 but before Part 4, and the file with the three broken rows ends up permanently versioned. Why it happens: it's easy to lose track of the exact step order while focused on reading the FAIL report. How to spot it: git status would show raw_data/kiosko/orders_2026-08-10.csv as a new, untracked file (or, worse, already in the commit) at the moment of Part 5's git add/git commit. How to fix it: follow this mini-project's exact order — revert (Part 4) always before committing (Part 5) — and explicitly name what you add (git add macros models/marts tests, as in the worked example), never git add . without first confirming with git status --short which files exist.

Thinking PASS=11 ERROR=4 is a "worse" result than PASS=15, instead of a correct one. What happens: someone sees ERROR=4 in Part 2 and feels something went wrong with the tests' configuration, instead of recognizing the suite is doing exactly its job. Why it happens: any nonzero number in the ERROR column instinctively feels like a sign something's broken in the project. How to spot it: the right question isn't "is there any ERROR?" but "does the number and identity of the ERRORs match what I already know is broken in the data?" — in this case, yes: three rows you yourself know are invalid, caught by four tests, not one more row flagged as a problem. How to fix it: a FAIL/ERROR over data you know is broken is the suite working correctly, not a defect — the result that should actually worry you is a PASS=15 over this same broken batch, because it would mean the suite is catching nothing.

Exercises

Exercise 1 — Rebuild the suite in a practice folder. Without looking at this lesson's worked example, write from memory (or with your own notes) the three files — _models.yml with fact_orders's six data_tests, test_is_positive.sql, assert_no_negative_revenue.sql — in a practice copy of the project, and confirm with dbt test that you reach PASS=15.

Exercise 2 — Combine the broken batch with the negative-revenue demonstration. Add, in this mini-project's same orders_2026-08-10.csv file, a ninth row with quantity=-1 and any positive unit_price (for example, S03,P004,-1,4.50). Run dbt run and dbt test, and predict how many new ERRORs show up, beyond the four you already know.

-- new line, added to orders_2026-08-10.csv
ORD-8008,S03,P004,-1,4.50,2026-08-10T10:10:00

dbt run
dbt test

Exercise 3 — Argue why this mini-project is the right starting point for module 5. In 2-3 sentences, explain what guarantees having a 15-test suite, already tested both over clean data and over broken data, gives you before module 5 starts historizing dim_product with snapshots.

Summary and next step

In this mini-project you brought together the seven tests you declared or wrote throughout the module: the six column ones on fact_orders (unique, not_null, accepted_values, relationships, is_positive twice) plus the singular test assert_no_negative_revenue. You confirmed PASS=15 over the usual clean data, added the already-known three-broken-row batch from data-engineering-foundations-guide and data-modeling-for-analytics-guide, and read a real FAIL report: PASS=11 ERROR=4, with the exact failure count matching the three broken rows — one of them caught by two layers of the project at once. You reverted the batch, confirmed the project returns to its clean state (40 rows, 106.15 revenue), and closed the module with the fourth version-control commit.

With this you close module 4. You now have a kiosko_analytics/ project with 15 data_tests running green over seven models, a suite verified both against healthy data and against deliberately broken data, and four commits in its history, each documenting a real, verifiable milestone.

Where you go next. Module 5 switches questions: until now, every Kiosko model reflects the data's current state — if products.csv changed tomorrow, stg_products and anything that depends on it would simply show the new value, with no trace of the previous one left behind. You're going to meet dbt's snapshots, the automated way to historize changes with SCD type 2 — exactly the same pattern data-modeling-for-analytics-guide built by hand with MERGE INTO — applied to dim_product, over the same real price-and-category change that guide already used. The test suite you built in this module doesn't change at all from here on; the only thing that changes is that, for the first time, Kiosko's project is going to remember its own past.

Resources

• dbt Developer Hub — "Add data tests to your DAG," this whole module's central reference, already cited in every previous lesson. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "About dbt build," again lesson 7's reference on the complete execution order (models, tests) this mini-project puts into practice with dbt run followed by dbt test. docs.getdbt.com/reference/commands/build. In English.
• Git — official git log documentation, including the formatting options (--oneline) used in this mini-project's Part 5, already cited in modules 1, 2, and 3. git-scm.com/docs/git-log. In English.