Generic tests: `unique` and `not_null` on `fact_orders`

Description

unique and not_null already live in Kiosko's project, since module 2, protecting order_id in stg_orders and event_id in stg_events. But notice something you may not have noticed: no mart has, yet, a single data_tests of its own. dim_store, dim_date, and fact_orders — the three models module 3 built with ref() and real JOINs — have been running for a whole module with no automated safety net at all on their own columns.

This isn't an oversight — it's, precisely, this module's starting point. This lesson declares the marts layer's first data_tests: unique and not_null on fact_orders.order_id, Kiosko's star schema's central fact's primary key. You're going to see why this declaration matters more here than on stg_orders — fact_orders went through two real JOINs since it left staging, and a badly written JOIN is, precisely, the kind of error that can introduce duplicates that never existed in the original source.

Connection to the module. Lesson 2 showed you what SQL is behind unique and not_null, using the tests that already existed on stg_orders. This lesson applies that exact same logic — with no change in the mechanism — over a new column, in a new model, with a different motivation: it isn't a "preview" like it was in module 2, it's the marts layer's first real piece of the test suite.

Why fact_orders.order_id needs its own test, even though stg_orders.order_id already has one

It would be reasonable to ask yourself: if stg_orders.order_id is already unique (module 2 confirms it with dbt test), why would fact_orders.order_id need another test for the same property, if fact_orders selects that same order_id straight from stg_orders, with no transformation?

The answer is in what happens between stg_orders and fact_orders: two INNER JOINs. A well-written JOIN, like fact_orders.sql's, shouldn't duplicate any row — that model's two JOINs exist to validate, not to enrich, as module 3's lesson 5 already explained. But "shouldn't" isn't the same as "guaranteed by the SQL language." If someday someone edits fact_orders.sql and that join stops being 1-to-1 — for example, if dim_store ever ended up with a duplicate row per store — the result would be repeated order_ids in fact_orders, with stg_orders never having changed at all. stg_orders's test would keep passing; the problem would be one floor up, invisible with no test of its own on that floor.

flowchart LR
    A["stg_orders.order_id\nunique: PASS (module 2)"] --> B["2 INNER JOINs\n(dim_store, dim_date)"]
    B --> C["fact_orders.order_id\nNO test until this lesson"]
    C -.->|"a badly written JOIN\ncould duplicate here"| D["invisible problem\nif you only test stg_orders"]

This is the concrete — not abstract — reason why you "widen" test coverage at every layer of the project, instead of trusting that a test in staging automatically protects everything built on top of it.

Worked example: declaring the tests in models/marts/_models.yml

Extend the file that already exists since module 3 — the same underscore pattern, the same folder — by adding a columns: section to fact_orders's entry:

# 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

Notice what did not change: dim_store and dim_date keep exactly the same description:, with no new columns: — this module, following DISEÑO.md's scope, concentrates its test coverage on fact_orders, the star schema's model with the most logic and the most risk of silent error. And the syntax itself is identical to what you already wrote in module 2 — data_tests: as a list, unique and not_null with no additional parameter — now applied to a different model.

Running the new test

dbt test --select fact_orders

What to expect.

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

Concurrency: 4 threads (target='dev')

1 of 2 START test not_null_fact_orders_order_id ................................ [RUN]
2 of 2 START test unique_fact_orders_order_id .................................. [RUN]
1 of 2 PASS not_null_fact_orders_order_id ...................................... [PASS in 0.03s]
2 of 2 PASS unique_fact_orders_order_id ........................................ [PASS in 0.03s]

Finished running 2 data tests in 0 hours 0 minutes and 0.09 seconds (0.09s).

Completed successfully

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

Notice the header's Found 7 models, 10 data tests: 8 inherited from module 2, plus these 2 new ones, 10 total — the same kind of cumulative count you already saw grow in the previous modules. And notice each test's autogenerated name: unique_fact_orders_order_id, not_null_fact_orders_order_id — the same <type>_<model>_<column> pattern you already know, now applied to the mart's name instead of the staging model's name.

Confirm the rest of the project's test suite is still passing, with this change having affected nothing:

dbt test

What to expect.

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

Ten out of ten — module 2's eight, plus the two you just declared.

Going deeper: unique and not_null are independent, even over the same column

You already saw this in module 2 (lesson 7, "Common mistakes"), but it's worth repeating now that both tests live on fact_orders: unique and not_null are two completely separate queries, each evaluating a different property. A repeated order_id fails unique but not not_null — duplicating a row introduces no empty value. An empty order_id fails not_null but not necessarily unique — in fact, unique doesn't even count it, because its compiled SELECT, as you saw in lesson 2, explicitly filters where order_id is not null before grouping. This independence is a deliberate design decision, not a coincidence: each generic test checks a single property, so that when something fails, the name of the failed test tells you, unambiguously, which of the two properties broke.

Common mistakes

Thinking declaring unique on fact_orders.order_id makes stg_orders.order_id's test redundant, and deleting the latter. What happens: someone, reasoning that "if fact_orders already tests it, there's no need to test it twice," deletes unique/not_null from models/staging/kiosko/_models.yml. Why it happens: redundancy feels like waste, especially in a project where both tests pass over the same data today. How to spot it: this module's lesson 4 (and lesson 8) show you a real scenario where a duplicate order_id in the raw file fails both tests at once — staging's and marts' — and that redundancy is exactly the signal that tells you at which exact layer the problem showed up. How to fix it: keep both tests. They aren't the same check applied twice — one confirms the source is clean, the other confirms no later step (fact_orders's JOINs) dirtied it along the way; deleting either one loses real information about which layer the problem is in the day one shows up.

Expecting unique_fact_orders_order_id to also validate store_id or product_id. What happens: someone assumes that, since order_id "represents" the whole row, a unique test on that column somehow also protects the row's other columns. Why it happens: order_id feels like an order line's "main" identifier, and it's easy to generalize that importance to the whole row. How to spot it: the compiled SQL you already saw in lesson 2 only reads and groups by one column — order_id, in this case — it never looks at store_id, product_id, quantity, or anything else. How to fix it: every data_tests: you declare protects exactly the column where you put it, nothing more — lesson 4 declares new, specific tests for product_id and store_id, precisely because unique/not_null on order_id says absolutely nothing about those other two columns.

Forgetting to run dbt test with no --select after a change, and not noticing a regression somewhere else. What happens: someone declares the new test, runs dbt test --select fact_orders, sees PASS=2, and considers the work done without running the complete suite. Why it happens: --select is fast and feels enough when the change was targeted. How to spot it: if some accidental change in another file (for example, in stg_orders.sql) broke something in the staging layer at the same time, dbt test --select fact_orders would never show it — that command doesn't even touch stg_orders's tests. How to fix it: use --select to iterate fast while working on a specific model, but run dbt test (with no --select at all) before considering any module closed — exactly the habit you already built in modules 2 and 3's mini-projects, and that this module's lesson 8 applies again over the complete suite.

Exercises

Exercise 1 — Confirm with dbt compile that the SQL is what you expect. Using lesson 2's same pattern, run dbt compile --select unique_fact_orders_order_id and read the resulting file in target/compiled/. How does it differ from unique_stg_orders_order_id's compiled SQL that you already saw?

dbt compile --select unique_fact_orders_order_id
cat target/compiled/kiosko_analytics/models/marts/_models.yml/unique_fact_orders_order_id.sql

Exercise 2 — Break dim_store's JOIN and watch which test catches it first. Temporarily repeat module 3's lesson 5, exercise 1: change fact_orders.sql's JOIN against dim_store so it compares o.store_id against a made-up value ('S99'), run dbt run --select fact_orders, and then dbt test --select fact_orders. Does unique_fact_orders_order_id fail? Why or why not?

-- broken version, temporary, for this exercise
inner join {{ ref('dim_store') }} as ds
    on 'S99' = ds.store_id

Exercise 3 — Argue why "widening" isn't the same as "duplicating." In 2-3 sentences, and using this lesson's diagram, explain the difference between having the same generic test declared at two different layers of the project (staging and marts) and having, literally, the same test repeated twice with no purpose.

Summary and next step

In this lesson you declared the marts layer's first data_tests: unique and not_null on fact_orders.order_id, using the exact same syntax you already knew from module 2, but with a new motivation — protecting the central fact's primary key after it went through two real JOINs. You confirmed with dbt test --select fact_orders (PASS=2) and with the complete suite (PASS=10) that the new coverage broke nothing that already existed, and you saw, with dbt compile, that the SQL behind these two tests is structurally identical to what you already inspected in lesson 2 — only the table changes.

Before moving on you should be able to: explain why fact_orders.order_id needs its own test even though stg_orders.order_id already has one; and predict, without running anything, what would happen to unique_fact_orders_order_id if fact_orders ended up empty from a broken JOIN.

Lesson 4 completes the out-of-the-box generic test quartet on fact_orders: accepted_values on product_id (a categorical column, with a closed, known catalog) and relationships on store_id (referential integrity against dim_store, expressed as an explicit test instead of relying only on the INNER JOIN).

Resources

• dbt Developer Hub — "Add data tests to your DAG," the unique and not_null section, already cited in module 2 and in this module's lesson 2. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "How we structure our dbt projects: marts," on why the marts layer is where most of a dbt project's business logic lives — and, by extension, where it's most worth concentrating test coverage. docs.getdbt.com/best-practices/how-we-structure/4-marts. In English.
• dbt Developer Hub — "dbt test," the complete reference for the command, including the selection options (--select) used in this lesson. docs.getdbt.com/reference/commands/test. In English.