Data Quality & Testing

Trust is the product

Here's a hard truth that takes most engineers a painful incident to learn: the output of a data platform is not tables — it's trust. A pipeline can be elegantly modeled, perfectly orchestrated, and lightning fast, and still be worthless the moment a stakeholder catches a single wrong number.

And the damage is not local. When someone opens a dashboard, sees revenue that's obviously too high, and traces it to a duplicated row, they don't conclude "that one metric is broken." They conclude "I can't trust any of these numbers" — and they go back to their own spreadsheet. One bad cell poisons confidence in the whole platform, and rebuilding that confidence takes months. Trust is expensive to earn and cheap to destroy.

The dimensions of quality

"Good data" sounds vague until you break it into the specific ways data goes bad. Each failure mode is a dimension of quality, and each one is something you can write an assertion about. Here's where they sit along the pipeline you already built:

Six dimensions cover the vast majority of real incidents. Learn them as a checklist — when something breaks, it's almost always one of these:

Notice these map directly onto problems you've already seen: uniqueness is why deduplication mattered in the medallion silver layer; referential integrity is what dimensional modeling's keys are for. Testing isn't a new topic bolted on — it's verifying the very properties your modeling promised.

Testing in the pipeline

A test in data engineering is a query that returns the rows that violate a rule. Zero rows returned means the test passes; any rows returned means it fails. That's the entire mental model, and dbt builds on it. You met four built-in tests in Chapter 07 — here they are as the front line of defense:

models:
  - name: fct_rentals
    columns:
      - name: rental_id
        tests:
          - not_null          # every fact must have a key
          - unique            # …and it must appear exactly once
      - name: customer_id
        tests:
          - not_null
          - relationships:    # referential integrity
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:  # validity — the only allowed values
              values: ['active', 'completed', 'cancelled']

Those four — not_null, unique, relationships, accepted_values — handle the common cases declaratively. But some rules don't fit a column dropdown. For those, dbt gives you two escape hatches.

A singular test is just a hand-written SQL file in tests/ that selects the bad rows. Want to assert revenue is never negative? Write the query that finds negatives:

-- Passes only if this returns zero rows.
-- Any row here is a rental with impossible revenue.
select
    rental_id,
    revenue
from {{ ref('fct_rentals') }}
where revenue < 0

A generic test wraps that pattern in a reusable macro so you can apply it by name to any column (e.g. a non_negative test) — handy once you're writing the same check in several models. And separate from both is unit testing your transform logic: feeding a model a tiny set of fixed input rows and asserting the exact output, so you catch a broken CASE statement or off-by-one window function regardless of what today's real data happens to look like:

unit_tests:
  - name: revenue_is_hours_times_rate
    model: fct_rentals
    given:
      - input: ref('stg_rentals')
        rows:
          - {rental_id: 1, hours: 2, hourly_rate: 5.00}
    expect:
      rows:
        - {rental_id: 1, revenue: 10.00}   # 2 × 5.00

The final piece is when tests run. Tests sitting in your repo that nobody runs are decoration. The professional move is to run dbt build (which runs models and their tests, stopping on failure) in CI — the automated check on every pull request, from your Git & GitHub chapter. Now a teammate literally cannot merge a change that breaks a test. Bad data is caught before it ships, not after a stakeholder reports it.

Data observability — the unknown-unknowns

Every test above guards a rule you thought of. But the failures that hurt most are the ones nobody anticipated: a vendor quietly changes a timezone, a marketing campaign triples sign-ups overnight, a column starts arriving as a string instead of a number. You can't write a test for a problem you haven't imagined.

Data observability fills that gap. Instead of asserting specific rules, observability tools watch your tables over time and learn what normal looks like, then alert when reality drifts:

Observability typically watches the same dimensions from the table above — freshness (did this table update on schedule?), volume (is today's row count within the normal band?), and schema (did columns or types change?) — but as automated monitors rather than hand-written assertions. When a metric strays far enough from its learned baseline, it fires an alert. You don't need a paid platform to start: even a scheduled query that logs daily row counts and flags a sudden 50% drop is observability in spirit.

Data contracts — pushing quality left

Everything so far reacts to bad data after it enters your pipeline. The best quality strategy is to stop it at the door. That's a data contract: an explicit, versioned agreement with the team (or system) that produces a source you depend on.

A contract spells out, in writing and in code, three things about a source:

• Schema — the exact columns, types, and which fields are guaranteed non-null. (The customer_id will always be a non-null integer.)
• Semantics — what the values mean. (status = 'completed' means the rental ended and was billed — not "in progress".)
• SLA — the freshness and availability promise. (This table updates by 06:00 UTC daily; we'll give 30 days' notice before any breaking change.)

The point of writing it down is that an upstream change now becomes a contract violation — something the producing team agreed not to do silently — instead of a surprise that breaks your dashboard at 2 a.m. Where tests catch bad data after it's loaded, a contract catches a breaking change before it's even published. That's what people mean by "shifting quality left": moving the check as close as possible to where data is produced, where it's cheapest to fix.

Hands-on: add a test suite, then break it

Time to make this real. You'll add a suite of tests to the marketplace project, then deliberately introduce a bad row and watch the suite catch it — the full fail→fix→pass loop that defines test-driven data work.

Step 1 — declare the tests. Add (or extend) the schema file for your marts so the keys, the relationship, and the value set are all covered:

version: 2

models:
  - name: fct_rentals
    description: One row per GPU rental, with revenue.
    columns:
      - name: rental_id
        tests: [not_null, unique]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'completed', 'cancelled']

Step 2 — add a freshness check. dbt's source freshness watches a raw source's load time. Point it at your rentals source and set a threshold:

sources:
  - name: marketplace
    tables:
      - name: rentals
        loaded_at_field: loaded_at      # a timestamp column on the table
        freshness:
          warn_after:  {count: 12, period: hour}
          error_after: {count: 24, period: hour}   # fail if no load in 24h

Step 3 — run the suite on clean data. Run the tests and the freshness check:

dbt test                    # run all data tests
dbt source freshness        # run the freshness check

Step 4 — introduce a bad row. Insert a duplicate rental_id into the source so the unique test should catch it:

# duplicate an existing rental_id straight into the source table
docker compose exec -T postgres psql -U postgres -c \
  "INSERT INTO rentals (rental_id, customer_id, status, hours, hourly_rate)
   SELECT rental_id, customer_id, status, hours, hourly_rate
   FROM rentals ORDER BY rental_id LIMIT 1;"

dbt build                   # rebuild models, then run their tests

FAIL 1 unique_fct_rentals_rental_id
  Got 1 result, configured to fail if != 0
Done. PASS=5 WARN=0 ERROR=1 SKIP=0 TOTAL=6

Step 5 — fix it and confirm green. Remove the duplicate and re-run:

# delete the duplicate, keeping one copy of each rental_id
docker compose exec -T postgres psql -U postgres -c \
  "DELETE FROM rentals a USING rentals b
   WHERE a.ctid < b.ctid AND a.rental_id = b.rental_id;"

dbt build                   # rebuild + re-test

Wrapping up the craft

Take a moment, because you just finished something substantial. Look back at what's now sitting in your mini-griddp repo:

That is not a toy. It's every component of a real data platform — the same shape as systems running in production at companies you've heard of, just scaled to your laptop. You designed the data's shape, got it in reliably, cleaned it with version-controlled SQL, stored it in open formats, handled it as both batch and stream, scheduled it to run on its own, and proved it correct. The craft is no longer abstract; it's in your repo and it works.

Two courses remain, and they change register. Course 5 (Tooling) hardens the professional workflow around these components — environments, packaging, CI/CD, secrets, the engineering hygiene that turns "it runs on my machine" into "it runs reliably for a team." Course 6 (Capstone) then assembles everything you've built into one coherent, end-to-end system you can put your name on.

✓ Check yourself

• Can you name the six dimensions of data quality, and give an incident each one catches?
• What's the difference between a data test and a unit test — which one would catch a logic bug on a day when the data is perfectly clean?
• Why do tests and observability complement each other rather than compete?
• What does a data contract do that a downstream test can't, and why is catching a problem "to the left" cheaper?

-- (1) Passes only if zero rows come back.
select rental_id, revenue
from {{ ref('fct_rentals') }}
where revenue < 0

# (2) Fail if the newest load is more than a day old.
sources:
  - name: marketplace
    tables:
      - name: rentals
        loaded_at_field: loaded_at
        freshness:
          error_after: {count: 24, period: hour}   # nothing loaded today -> ERROR

# prove the revenue test catches a bad value
docker compose exec -T postgres psql -U postgres -c \
  "UPDATE rentals SET hourly_rate = -5 WHERE rental_id = (SELECT min(rental_id) FROM rentals);"
dbt build       # -> assert_revenue_non_negative FAILs (1 result)

# fix it and re-run
docker compose exec -T postgres psql -U postgres -c \
  "UPDATE rentals SET hourly_rate = 5 WHERE hourly_rate < 0;"
dbt build       # -> PASS again

# prove freshness: an old loaded_at trips the check
dbt source freshness   # ERROR if loaded_at is older than 24h

Next

That's a wrap on Course 4 — The DE Craft. You've built every component of a data platform with your own hands and proven it correct. Genuinely: well done. The next stop is Course 5 — Tooling & the Modern Stack, which hardens the professional workflow around everything you've built — and it's ready now. → Continue to Tooling