🛡 Quality, Testing & Ops · Chapter 3

Great Expectations and dbt tests in practice

A dbt test is just a SELECT that should return zero rows. Once you see that, every test — built-in, package or hand-written — is the same idea wearing different clothes.

Study this lesson free in the app →

The basics

dbt tests and Great Expectations (GX) are the two most common tools for writing data tests — automated assertions that run on your real data every time the pipeline runs.

Why they exist: writing a hundred hand-rolled SQL checks, wiring them into the scheduler and remembering which ones block publishing gets messy fast. These tools give you a declarative way to say “ride_id is unique, fare is between 0 and 20,000, every driver_id exists in drivers” and handle running, reporting and failing.

The mental model:

• dbt test = a SQL query that returns the bad rows. Zero rows → pass. Lives in YAML next to the model it protects. Runs inside the warehouse.
• GX expectation = a named assertion (ExpectColumnValuesToBeBetween) evaluated against a batch of data — a DataFrame, a file, or a table — producing a validation result and human-readable docs.

Rule of thumb: transforming in dbt? Test in dbt. Validating files or DataFrames before they reach the warehouse? GX.

Words to know: generic test, singular test, severity, mostly, expectation suite, checkpoint.

Explained

Meghalaya Cabs had a fct_rides model and no tests. One Friday a driver-app retry storm wrote 6,200 duplicate rides. Payouts went out ₹3.1 lakh too high.

The fix that stopped it ever happening again was four lines of YAML:

- name: ride_id
  data_tests: [unique, not_null]

The next retry storm failed the build at 02:14, and nothing downstream refreshed with bad numbers.

The exam answer key

A teacher checking 300 exam papers doesn't re-read each answer from scratch. She has a list of rules: answer 4 must be between 10 and 12; name must be filled in; roll number must exist in the class register.

She doesn't write down the papers that are fine. She only pulls out the ones that break a rule. If the pile of pulled papers is empty, the batch passes.

That is exactly a dbt test: collect the rule-breakers; an empty pile is a pass.

dbt: the four generic tests, plus the knobs that matter

# models/marts/schema.yml
models:
  - name: fct_rides
    columns:
      - name: ride_id
        data_tests: [unique, not_null]
      - name: status
        data_tests:
          - accepted_values:
              values: ['requested', 'accepted', 'completed', 'cancelled']
      - name: driver_id
        data_tests:
          - not_null:
              config:
                where: "status = 'completed'"   # null is legit for cancelled
          - relationships:
              to: ref('dim_drivers')
              field: driver_id
              config:
                severity: error
                warn_if: ">0"
                error_if: ">50"               # a few late drivers are tolerable

data_tests: is the current key (older projects say tests:; both work). The where, warn_if and error_if configs are what make tests survive real data.

dbt: a singular test is just SQL

-- tests/assert_rides_reconcile_with_payments.sql
-- returns a row for any day where rides and captured payments differ by > 0.5%
WITH r AS (
  SELECT ride_date AS d, sum(fare_inr) AS rides_inr
  FROM {{ ref('fct_rides') }} WHERE status = 'completed' GROUP BY 1
), p AS (
  SELECT pay_date AS d, sum(amount_inr) AS paid_inr
  FROM {{ ref('fct_payments') }} WHERE state = 'captured' GROUP BY 1
)
SELECT r.d, rides_inr, paid_inr
FROM r JOIN p USING (d)
WHERE abs(rides_inr - paid_inr) > 0.005 * rides_inr
  AND r.d >= current_date - 7

Anything a generic test can't express goes here. Packages like dbt_utils and dbt_expectations add more generics (expression_is_true, expect_column_values_to_be_between) so you write fewer of these.

What dbt build does when a test fails

dbt run builds models and runs no tests. dbt build interleaves them in DAG order, which is what makes a test a gate rather than a report. Note that fct_rides itself was already rebuilt — the gate protects downstream models, not the tested one.

Monday 02:14 — a test fails for a good reason

The product team launches scheduled rides on Friday. Monday's run fails: accepted_values on fct_rides.status returns 1,870 rows with status 'scheduled'.

Three wrong reactions: delete the test, set it to warn forever, or add 'scheduled' without asking anything.

The right one, in daylight:

1. Confirm with the app team that scheduled is a real, permanent status.
2. Ask what it means for revenue — a scheduled ride isn't completed, so finance models filtering on status = 'completed' are fine, but the ops ‘rides requested’ count needs updating.
3. Add the value to the YAML in the same PR that updates the downstream models.

The test did its job: it turned a silent semantic change into a conversation. A failing accepted_values test is often a product launch nobody told the data team about.

Great Expectations (GX Core 1.x) on a landing file

import great_expectations as gx
import pandas as pd

df = pd.read_parquet("s3://mc-landing/partner_rides/2026-11-04.parquet")

context = gx.get_context()
source  = context.data_sources.add_pandas("partner")
asset   = source.add_dataframe_asset(name="partner_rides")
batch_def = asset.add_batch_definition_whole_dataframe("daily")

suite = context.suites.add(gx.ExpectationSuite(name="partner_rides"))
suite.add_expectation(gx.expectations.ExpectColumnValuesToNotBeNull(column="ride_id"))
suite.add_expectation(gx.expectations.ExpectColumnValuesToBeUnique(column="ride_id"))
suite.add_expectation(gx.expectations.ExpectColumnValuesToBeBetween(
    column="fare_inr", min_value=0, max_value=20000, mostly=0.999))
suite.add_expectation(gx.expectations.ExpectTableRowCountToBeBetween(
    min_value=5000, max_value=40000))

result = batch_def.get_batch(batch_parameters={"dataframe": df}).validate(suite)
if not result.success:
    raise SystemExit("partner file failed validation — not loading")

This runs before the file touches the warehouse, which dbt cannot do.

Which tool where

                      dbt tests                 Great Expectations
─────────────────────────────────────────────────────────────────────
runs on               warehouse tables          DataFrames, files, tables
written in            YAML + SQL                Python (or JSON suites)
best at               models you build in dbt   data before/outside dbt
pass condition        query returns 0 rows      expectation satisfied
partial tolerance     warn_if / error_if counts mostly= fraction
blocks downstream     yes, via dbt build        yes, if you raise on fail
human report          run results / artifacts   Data Docs (HTML)

Meghalaya Cabs: GX at the S3 landing zone for partner files; dbt tests on every model in the warehouse. One check, one place — never both on the same rule.

Words to know

In two minutes

• A dbt test is a SELECT returning failing rows; zero rows means pass.
• Generic tests (unique, not_null, accepted_values, relationships) are declared in YAML on columns; singular tests are hand-written SQL files.
• Severity, warn_if / error_if thresholds and where filters turn a brittle test into a useful one.
• dbt build runs each model then its tests; an error-severity failure skips everything downstream.
• Great Expectations validates a batch — DataFrame, file or table — against a suite of named expectations.
• GX's mostly parameter allows a tolerated failure fraction instead of all-or-nothing.
• Use dbt tests for warehouse models, GX for data before or outside the warehouse; don't run both on the same check.

Test yourself

There are 8 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.