🧪 Testing — Catch Bugs Before Your Boss Does

-- dbt runs something like this behind the scenes:

SELECT customer_id
FROM your_model
GROUP BY customer_id
HAVING COUNT(*) > 1

-- If this returns ANY rows → TEST FAILS (duplicates found!)
-- If this returns 0 rows  → TEST PASSES ✅

-- dbt runs something like this behind the scenes:

SELECT *
FROM your_model
WHERE customer_id IS NULL

-- If this returns ANY rows → TEST FAILS (nulls found!)
-- If this returns 0 rows  → TEST PASSES ✅

-- dbt runs something like this behind the scenes:

SELECT status
FROM your_model
WHERE status NOT IN ('active', 'inactive', 'suspended')

-- If this returns ANY rows → TEST FAILS (unexpected values!)
-- If this returns 0 rows  → TEST PASSES ✅

-- dbt runs something like this behind the scenes:

SELECT o.customer_id
FROM orders o
LEFT JOIN customers c
    ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL

-- If this returns ANY rows → TEST FAILS (orphan records!)
-- If this returns 0 rows  → TEST PASSES ✅

version: 2

models:
  - name: stg_customers
    description: "Cleaned customer data from raw source"
    columns:
      - name: customer_id
        description: "Primary key — unique identifier for each customer"
        tests:
          - unique           # No duplicate customer IDs
          - not_null         # Every row must have an ID

      - name: email
        tests:
          - not_null         # Every customer must have an email

      - name: status
        tests:
          - accepted_values:
              values: ['active', 'inactive', 'suspended']

  - name: stg_orders
    description: "Cleaned order data"
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

      - name: customer_id
        tests:
          - not_null
          - relationships:    # Every order's customer must exist!
              to: ref('stg_customers')
              field: customer_id

# Run ALL tests in your project
$ dbt test

Running tests...
PASS  unique_stg_customers_customer_id          # ✅ No duplicates
PASS  not_null_stg_customers_customer_id        # ✅ No nulls
PASS  not_null_stg_customers_email              # ✅ All have emails
PASS  accepted_values_stg_customers_status      # ✅ Valid statuses only
PASS  unique_stg_orders_order_id                # ✅ No duplicates
PASS  not_null_stg_orders_customer_id           # ✅ No orphan orders
PASS  relationships_stg_orders_customer_id      # ✅ All customers exist

Completed successfully. 7 passed, 0 failed.

# Run tests for a specific model only
$ dbt test --select stg_customers

# Run tests for everything downstream of a model
$ dbt test --select stg_customers+

-- This test FAILS if any order has a negative or zero amount.
-- Think about it: can you buy something for -$50? Nope!

SELECT
    order_id,
    customer_id,
    amount
FROM
    {{ ref('stg_orders') }}
WHERE
    amount <= 0

-- If this returns rows like:
--   order_id=9912, customer_id=45, amount=-12.50
-- → TEST FAILS ❌ (we found bad data!)
--
-- If this returns 0 rows → TEST PASSES ✅

-- Orders can't be placed in the future!
-- If we see order_date > today, something is wrong with our ETL.

SELECT
    order_id,
    order_date
FROM
    {{ ref('stg_orders') }}
WHERE
    order_date > CURRENT_DATE

-- Found an order dated 2030-01-01? That's a time traveler.
-- Or more likely, a bug. TEST FAILS ❌

-- If daily revenue suddenly jumps to $10M when it's usually $50K,
-- something is probably duplicated. This is a "sanity check" test.

SELECT
    order_date,
    SUM(amount) AS daily_revenue
FROM
    {{ ref('stg_orders') }}
GROUP BY order_date
HAVING
    SUM(amount) > 1000000   -- Flag days with > $1M revenue

-- If your business normally does $50K/day, a $1M day is suspicious.
-- Investigate before it reaches the dashboard!

version: 2

sources:
  - name: raw_shop
    database: raw_production
    schema: public

    freshness:                          # Default for ALL tables in this source
      warn_after: {count: 12, period: hour}  # ⚠️ Warn if > 12 hours old
      error_after: {count: 24, period: hour} # ❌ Error if > 24 hours old
    loaded_at_field: _etl_loaded_at      # The timestamp column dbt checks

    tables:
      - name: customers
        # Inherits the default freshness above

      - name: orders
        freshness:                        # Override for this specific table
          warn_after: {count: 6, period: hour}   # Orders should be fresher!
          error_after: {count: 12, period: hour}

      - name: payments
        loaded_at_field: updated_at       # Different timestamp column

$ dbt source freshness

Running source freshness checks...

PASS   source raw_shop.customers    # Last loaded 2 hours ago  ✅ Fresh!
WARN   source raw_shop.orders       # Last loaded 8 hours ago  ⚠️ Getting stale...
ERROR  source raw_shop.payments     # Last loaded 30 hours ago ❌ STALE!

Done. PASS=1  WARN=1  ERROR=1

packages:
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

# Install the package
$ dbt deps

Installing calogica/dbt_expectations...
Successfully installed 1 package.

models:
  - name: stg_orders
    columns:
      - name: amount
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0        # No negative amounts
              max_value: 100000   # No absurdly high amounts

          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 10       # Average order should be at least $10
              max_value: 500      # ...and no more than $500

    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000     # We should have at least 1,000 orders
          max_value: 10000000 # ...but not 10 million (probably dupes)

# Build your model
$ dbt run --select my_new_model

# Test it immediately
$ dbt test --select my_new_model

# Or do both at once with "build" (run + test)
$ dbt build --select my_new_model

Running model my_new_model... OK
Running tests...
PASS  unique_my_new_model_id
PASS  not_null_my_new_model_id
ERROR accepted_values_my_new_model_status  # ← Uh oh!

# Fix the issue, then re-run
$ dbt build --select my_new_model

tests:
  my_project:
    +store_failures: true    # Save failing rows to a table
    +severity: warn          # Warn instead of error (optional)

-- After a test fails, dbt creates a table with the bad rows:
SELECT *
FROM your_schema_dbt_test__audit.not_null_stg_orders_customer_id

-- Result:
-- order_id | customer_id
-- 9912     | NULL          ← This order has no customer!
-- 10045    | NULL          ← This one too!