Module overview: from a star schema that runs to a star schema you trust

Description

Module 3 closed with seven models — four staging views, three marts materialized as tables — chained with ref(), and with dbt ls --select +fact_orders showing the complete DAG, literally, with no surprise. fact_orders has 40 rows and 106.15 total revenue, the same numbers you already knew from data-engineering-foundations-guide and data-modeling-for-analytics-guide. The project runs. But "runs with no errors" and "produces the correct result" are two different claims, and module 3 already warned about it in its own mini-project: a badly written JOIN — comparing a timestamp against a date with no cast() — runs perfectly, ends in PASS=1, and returns zero rows with no one warning you. dbt run confirms the SQL is valid. It doesn't confirm the result is the one you expected.

This module closes that gap with the piece the project was missing: a suite of data tests, run with dbt test, that confirm — every time anyone runs the project, not just the first time you checked it by hand — that the columns still hold the properties they're supposed to hold. You're going to meet the four out-of-the-box generic tests dbt-core ships with (unique, not_null, accepted_values, relationships), you're going to write one of your own (test_is_positive, a reusable range rule), and you're going to declare a singular test for a business rule no generic test captures on its own (assert_no_negative_revenue). You're going to see, with real, deliberately broken data, what a real FAIL looks like — not as an abstract warning, but as a specific row, in a specific table, for a specific reason.

Connection to the module. You already know the data_tests: key since module 2 — unique and not_null on order_id and event_id, in the staging layer, "just a preview" as that module's lesson 7 called it. This module picks up exactly on that promise: it expands coverage from two generic tests on two staging columns to all four complete generic tests, applied in depth over fact_orders, the star schema's central mart. Nothing you declared in module 2 gets touched or redefined — the eight data_tests in models/staging/kiosko/_models.yml stay exactly the same, and you're going to confirm it in this module's lesson 8, by running the project's complete suite.

An analogy: quality control at the end of the assembly line

A factory that assembles phones can have a perfect assembly line — every screw in place, every solder joint clean — and still ship a broken product, if no one checks the box before it goes out the door. Quality control at the end of the line doesn't care whether the code of the robot that soldered the board is well written; it cares about one thing: whether the phone that came out is the one that was promised. It turns on, it has battery, the screen responds. If any of those properties fails, it doesn't matter how elegant the process that assembled it was — the phone doesn't ship.

A generic test is the rule you apply to many phones on the same line, with no exception: "every phone that leaves this factory has to turn on" — you apply it to model 12, to model 13, to whichever one passes through that station. A singular test is the alarm you install for one specific situation in your factory, one no generic rule covers: "if the solder oven's temperature sensor reads above 300°C, something's burning" — a condition that makes no sense to apply to any other assembly line, because it comes from concrete knowledge of how this particular factory works. This module teaches you to install both kinds of check: the generic rules you apply over and over on different columns (lessons 3, 4, and 5), and the specific alarm that only makes sense for Kiosko's business (lesson 6).

This module's map

Lesson    Question it answers
────────  ──────────────────────────────────────────────────────────
L1        (this one) What this module is about, and how a generic
          test relates to a singular one
L2        What a data test does, exactly -- the SELECT behind
          each one
L3        unique and not_null, now applied in depth
          over fact_orders
L4        accepted_values and relationships -- the two tests
          missing from the factory quartet
L5        Writing your own generic test: test_is_positive
L6        A singular test for a business rule: revenue
          never negative
L7        Reading dbt test's report -- PASS, WARN, FAIL, and
          why each one matters differently
L8        Project: Kiosko's complete test suite, with a
          deliberately broken batch of data

The progression repeats the pattern you already know from modules 2 and 3: first the mechanisms, in isolation and with concrete examples (lessons 2 through 6), then how to read the result with judgment (lesson 7), and last the project that brings everything together over real data, including a scenario where something actually is broken (lesson 8) — the same structure module 3's lesson 7 gave dbt ls before its own mini-project.

What you're going to build, previewed

By the end of this module, fact_orders is going to have six data_tests of its own — none existed before this module — plus a seventh test that lives outside any column:

Test	Type	Column	What it checks
unique	out-of-the-box generic	order_id	no order_id repeats
not_null	out-of-the-box generic	order_id	no order_id is empty
accepted_values	out-of-the-box generic	product_id	only P001–P004, nothing made up
relationships	out-of-the-box generic	store_id	every store_id exists in dim_store
is_positive	custom generic	quantity, unit_price	no non-positive (or null) value
assert_no_negative_revenue	singular	(over all of fact_orders)	no order line with negative revenue

Notice the "Type" column: the first four already exist inside dbt-core, with you writing not a single line of SQL to define them — you only declare them in YAML. The fifth one you write yourself, once, as a reusable Jinja macro in macros/test_is_positive.sql, and then apply it to as many columns as you want, with a parameter (strict) that adjusts its behavior without duplicating code. The sixth one doesn't apply to any particular column — it lives as an independent .sql file in tests/, with a complete hand-written query, for a rule no generic test expresses naturally.

By the end of the module, dbt test over the complete project is going to report 15 data tests — the 8 you already knew from module 2, plus these 7 new ones — with PASS=15 WARN=0 ERROR=0 SKIP=0, run over the same 40 orders and 106.15 revenue as always. And in lesson 8 you're going to see, with real data, what happens when that figure stops being 15 out of 15: you're going to add a batch of orders with three deliberately broken rows — the same orders_2026-08-10.csv batch data-engineering-foundations-guide and data-modeling-for-analytics-guide already used — and you're going to see the exact FAIL report it produces, row by row.

The boundary: what does NOT belong in this module

These tests are local to this dbt project, not a data governance system. Every one of this module's six tests runs against kiosko.duckdb, on your machine, when you (or whoever clones the project) types dbt test. There's no published contract another team could check without running the project, no alert that wakes you up at three in the morning if a test starts failing in production, no access control over who can see which column. That layer — published data contracts and observability at scale — is data-reliability-and-governance-guide's complete territory, the sibling guide that picks up exactly where this module ends: what to do when "running dbt test by hand" stops being enough because the project no longer fits inside one person watching the terminal.

Nor does any of the following belong in this module — it arrives in the following ones: snapshots to historize dim_product with automated SCD type 2 (module 5), incremental models so fact_orders doesn't get rebuilt whole on every run (module 6), or transformation macros and generated documentation beyond the test macro you write in lesson 5 (module 7, with calculate_revenue). This module focuses on one narrow, concrete question: how do you confirm, automatically and repeatably, that the data stays what you promised it would be?

Common mistakes

Thinking "writing tests" is a later, optional step, for "once the project is more mature." What happens: someone builds module 3's seven models, watches them run with no errors, and postpones tests indefinitely because "it works for now." Why it happens: a small project, with data you personally checked by hand, feels reliable without needing any automated safety net — the illusion holds as long as no one else touches the project and the data doesn't change. How to spot it: the right question isn't "does it work today?" but "what happens the day someone else edits stg_orders.sql, or Kiosko adds a fifth store, and I'm not looking over their shoulder?" — with no tests, that day the error can take weeks to notice. How to fix it: tests aren't a luxury for after a project is "mature" — they're, precisely, what separates a script you ran once from a real software project, the same distinction module 1's lesson 2 already drew about what analytics engineering is.

Confusing a generic test with "a test that applies to everything." What happens: someone, hearing "generic," assumes a generic test automatically applies to any column in the project, with no one declaring it. Why it happens: the word "generic" sounds like "universal" in everyday language. How to spot it: if you expect dbt test to check a column you never declared with data_tests:, and that column doesn't show up in any report, it isn't a bug — dbt never tests anything you didn't explicitly ask it to. How to fix it: "generic" describes the reuse of logic (the same unique macro serves order_id, event_id, store_id, or any other column), not automatic application — every test, generic or singular, has to be declared on purpose, column by column or file by file, exactly as you already did with unique/not_null in module 2.

Expecting this module to teach published data contracts or observability alerts. What happens: someone who already knows "data quality" is a broader industry topic — with monitoring tools, freshness SLAs, automatic alerts — expects to find that here. Why it happens: "testing" and "data observability" share vocabulary and sometimes get presented together in the industry. How to spot it: if you look in this module for how to publish a versioned data contract or how to configure a Slack alert when a test fails in production, you're not going to find it — this lesson's boundary already warned about it. How to fix it: that territory explicitly belongs to data-reliability-and-governance-guide — this module builds the local foundation (the tests themselves) that guide builds on top of afterward.

Exercises

Exercise 1 — Classify the table's six tests. Without having read lessons 3 through 6 yet, look at this lesson's "What you're going to build" table and answer: which of the six tests could, in principle, also apply to a completely different dbt project (a different company, different data), with not a single line of its logic changed? Which one couldn't?

See solution

unique, not_null, accepted_values, relationships, and the is_positive you're going to write in lesson 5 are all fully reusable — none mentions anything specific to Kiosko in its logic, they only take a column's name (and, for is_positive, a parameter) at the moment they're declared. assert_no_negative_revenue, on the other hand, couldn't be copied to another project with no changes: it explicitly mentions fact_orders and the revenue column, which only exist in Kiosko's project. That's, precisely, the difference between a generic test (reusable logic, declared over different data each time) and a singular one (a specific query, written for one particular table and rule).

Exercise 2 — Predict the final count. Module 2 left the project with 8 data_tests. Using this lesson's table (6 new tests on fact_orders columns, plus 1 singular test), predict what total number of data_tests dbt test is going to report at the end of this module.

See solution

8 (inherited from module 2, unchanged) + 6 (the five column tests on fact_orders: unique, not_null, accepted_values, relationships, and is_positive applied twice — to quantity and to unit_price, which count as two separate tests even though they share the same macro) + 1 (assert_no_negative_revenue, the singular one) = 15. This module's lesson 8 confirms this exact number with dbt test run over the complete project.

Exercise 3 — Argue the analogy in your own words. In 2-3 sentences, and using this lesson's quality-control analogy, explain why it makes sense for assert_no_negative_revenue to be a singular test and not one of the four out-of-the-box generic ones.

See solution

The four out-of-the-box generic tests solve universal properties any identifier or reference column might need — uniqueness, non-nullity, membership in a list, referential integrity — without knowing anything about the specific business that data describes. But "an order line's revenue should never be negative" is a rule born from understanding how Kiosko's particular business works — no one pays Kiosko for buying something — the same way the solder oven's alarm in the analogy only makes sense for that specific factory. There's no way to express that rule as a generic property of "any numeric column" — it needs to explicitly know that revenue is quantity * unit_price and what it means for that product to be negative, so a singular test, with a hand-written query, is the right tool.

Summary and next step

This module closes the distance between "the project runs" and "I trust what the project produces." You saw the map of the 8 lessons, the complete table of the seven tests you're going to declare or write — four out-of-the-box generic ones, one custom generic applied twice, one singular — and the hard boundary: these tests are local to Kiosko's project, not a published data governance system, data-reliability-and-governance-guide's territory.

Before moving on you should be able to: name the four out-of-the-box generic tests dbt-core ships with, with no need to write them; and explain, in your own words, the difference between a generic test and a singular one, without having seen either one's code yet.

Lesson 2 stops at the most basic question of all, before touching any specific test: what, exactly, is a data test to dbt? — the answer isn't a list of magic rules, it's a SELECT like any other, with a very simple convention about what it means for that SELECT to "fail."

Resources

• dbt Developer Hub — "Add data tests to your DAG," this whole module's central reference: the four out-of-the-box generic tests, the data_tests: key, and singular tests as .sql files. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "Upgrading to v1.8," which documents renaming tests: to data_tests: since dbt-core 1.8 — already cited in module 2, relevant again now that this module declares data_tests: in depth. docs.getdbt.com/docs/dbt-versions/core-upgrade/upgrading-to-v1.8. In English.
• data-reliability-and-governance-guide — the sibling guide that picks up, at organization scale, what this module leaves as a local test suite over a single project. Internal document of the data-engineering-ecosystem ecosystem.