Writing Effective SQL Unit Tests: Validating Query Logic, Edge Cases, and Data Contracts in CI/CD Pipelines

Most data teams discover the value of SQL testing the hard way — a revenue report breaks in production, a key metric double-counts records after a schema change, or a downstream dashboard silently starts showing wrong numbers because someone refactored a CTE without realizing it changed the row-level behavior for NULLs. By then, the damage is done.

The instinct after incidents like these is to add more manual QA, more stakeholder reviews, more "eyes on it." But that doesn't scale, and it doesn't catch regressions before they ship. What actually solves the problem is treating your SQL logic the same way a software engineer treats application code: with automated unit tests that run on every change, before anything reaches production.

This lesson teaches you to do exactly that. We'll build a complete testing strategy from scratch — covering how to validate query logic, construct meaningful edge cases, enforce data contracts, and wire everything into a CI/CD pipeline. By the end, you'll have a reproducible pattern you can apply to any analytics codebase.

What you'll learn:

• How to structure SQL unit tests using fixture data and expected-output assertions
• How to test edge cases specific to SQL: NULLs, duplicates, type coercions, and boundary conditions
• How to define and enforce data contracts (schemas, referential integrity, business rules) as executable tests
• How to integrate SQL tests into a CI/CD pipeline using dbt and pytest
• How to diagnose and fix the most common failures in SQL test suites

---

Prerequisites

You should be comfortable writing multi-table SQL (JOINs, CTEs, window functions, aggregations). You've worked with at least one data warehouse (Snowflake, BigQuery, Redshift, or Postgres). Some exposure to dbt is helpful but not required. Basic familiarity with Python and command-line tools will help for the CI/CD sections.

---

Why SQL Unit Tests Are Different from Application Unit Tests

When a software engineer unit-tests a function, they can mock dependencies, isolate the function, and call it directly. SQL doesn't work that way. A SQL query isn't a callable function — it's a declarative expression that runs against a database engine with real data. That creates three problems that don't exist in traditional unit testing:

1. State dependency. Your query logic is inseparable from the data it runs against. A SUM() that works correctly on clean data can fail silently on data with unexpected NULLs. The only way to test specific behaviors is to control the input data precisely.

2. No natural isolation. A single SQL model might reference five upstream tables. To test it in isolation, you need to either mock those tables with fixture data or accept that your test environment reflects production data — which makes your tests non-deterministic.

3. Silent wrongness. Application code throws exceptions. SQL usually succeeds and returns wrong results. A join that produces a Cartesian product won't raise an error; it'll just return ten times too many rows. Your tests have to be designed to catch this class of problem explicitly.

The practical implication: SQL unit tests are fundamentally about controlled inputs and verified outputs. You define exactly what data goes in, run your query logic against it, and assert that the output matches your expectation. Everything else — test frameworks, CI integration, contracts — builds on that foundation.

---

Building Your First SQL Unit Test

Let's start with a concrete scenario. You're working on a metric that tracks monthly recurring revenue (MRR) by customer. The core logic lives in a view called customer_mrr:

-- customer_mrr view definition
SELECT
    customer_id,
    DATE_TRUNC('month', subscription_start_date) AS mrr_month,
    SUM(monthly_amount) AS total_mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY 1, 2

Simple enough. Now ask yourself: what could go wrong with this?

• What if a customer has two overlapping active subscriptions? (Double-counting risk)
• What if monthly_amount is NULL for some rows? (SUM over NULLs returns NULL in some edge cases)
• What if subscription_start_date is NULL? (The row gets bucketed into NULL month)
• What if the same customer_id appears in the input twice due to an upstream deduplication bug?

To test these, you need a testing pattern with three parts: fixture data, the logic under test, and assertions.

The Fixture-Logic-Assert Pattern

Here's a self-contained SQL unit test written in pure SQL using a CTE-based approach that works in most warehouses:

-- test_customer_mrr_basic.sql
WITH
-- FIXTURE: controlled input data
raw_subscriptions AS (
    SELECT 1 AS customer_id, DATE '2024-01-15' AS subscription_start_date, 100.00 AS monthly_amount, 'active' AS status
    UNION ALL
    SELECT 1, DATE '2024-01-28', 50.00, 'active'     -- same customer, same month, two subscriptions
    UNION ALL
    SELECT 2, DATE '2024-01-10', 200.00, 'active'
    UNION ALL
    SELECT 3, DATE '2024-01-05', 75.00, 'cancelled'  -- should be excluded by WHERE clause
    UNION ALL
    SELECT 4, DATE '2024-02-01', 120.00, 'active'    -- different month
),

-- LOGIC: replicate the view logic using the fixture instead of the real table
customer_mrr AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', subscription_start_date) AS mrr_month,
        SUM(monthly_amount) AS total_mrr
    FROM raw_subscriptions
    WHERE status = 'active'
    GROUP BY 1, 2
),

-- EXPECTED: what we expect the output to be
expected AS (
    SELECT 1 AS customer_id, DATE '2024-01-01' AS mrr_month, 150.00 AS total_mrr
    UNION ALL
    SELECT 2, DATE '2024-01-01', 200.00
    UNION ALL
    SELECT 4, DATE '2024-02-01', 120.00
),

-- ASSERTION: find rows that differ between actual and expected
failures AS (
    -- rows in actual but not in expected
    SELECT 'unexpected_row' AS failure_type, customer_id, mrr_month, total_mrr
    FROM customer_mrr
    EXCEPT
    SELECT 'unexpected_row', customer_id, mrr_month, total_mrr
    FROM expected

    UNION ALL

    -- rows in expected but not in actual
    SELECT 'missing_row' AS failure_type, customer_id, mrr_month, total_mrr
    FROM expected
    EXCEPT
    SELECT 'missing_row', customer_id, mrr_month, total_mrr
    FROM customer_mrr
)

-- The test passes if this query returns zero rows
SELECT * FROM failures;

This is the core of every SQL unit test you'll write. If failures returns zero rows, the test passes. If it returns any rows, you have your diagnostics right there — you can see exactly which rows are wrong and whether they're unexpected or missing.

Why use EXCEPT instead of a JOIN for comparison? EXCEPT handles NULL equality correctly — two NULL values are treated as equal for the purposes of set comparison, which is what you want in an assertion. A JOIN on a.col = b.col will never match NULL = NULL.

---

Testing Edge Cases in SQL

Edge cases in SQL are different from edge cases in application code. The ones that cause the most production incidents fall into four categories.

NULLs That Change Aggregation Behavior

Consider this variation of our MRR query where the amount column can be NULL:

WITH
raw_subscriptions AS (
    SELECT 1 AS customer_id, DATE '2024-01-15' AS subscription_start_date, NULL AS monthly_amount, 'active' AS status
    UNION ALL
    SELECT 1, DATE '2024-01-20', 100.00, 'active'
),

customer_mrr AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', subscription_start_date) AS mrr_month,
        SUM(monthly_amount) AS total_mrr
    FROM raw_subscriptions
    WHERE status = 'active'
    GROUP BY 1, 2
),

expected AS (
    -- SUM ignores NULLs, so this should be 100, not NULL
    SELECT 1 AS customer_id, DATE '2024-01-01' AS mrr_month, 100.00 AS total_mrr
),

failures AS (
    SELECT 'unexpected_row' AS failure_type, customer_id, mrr_month, total_mrr FROM customer_mrr
    EXCEPT
    SELECT 'unexpected_row', customer_id, mrr_month, total_mrr FROM expected
    UNION ALL
    SELECT 'missing_row' AS failure_type, customer_id, mrr_month, total_mrr FROM expected
    EXCEPT
    SELECT 'missing_row', customer_id, mrr_month, total_mrr FROM customer_mrr
)

SELECT * FROM failures;

This test documents a behavior guarantee: even when some rows have NULL amounts, the SUM should still produce a numeric result by ignoring the NULL rows. If someone later changes the query to use monthly_amount + 0 instead of leaving it as-is, they might accidentally convert NULLs to 0 — which would change the result. Your test catches that.

Fan-Out JOINs Causing Unexpected Row Multiplication

A join fan-out is one of the most common silent data bugs. It happens when you join on a non-unique key and the cardinality multiplies in ways you didn't expect.

-- test_no_join_fanout.sql
WITH
orders AS (
    SELECT 1 AS order_id, 101 AS customer_id, 500.00 AS order_amount
    UNION ALL
    SELECT 2, 102, 300.00
),

-- Imagine a bug upstream: customer 101 appears twice in the customers table
customers AS (
    SELECT 101 AS customer_id, 'Acme Corp' AS customer_name
    UNION ALL
    SELECT 101, 'Acme Corp'   -- duplicate row!
    UNION ALL
    SELECT 102, 'Beta LLC'
),

joined AS (
    SELECT
        o.order_id,
        o.customer_id,
        c.customer_name,
        o.order_amount
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
),

-- ASSERTION: row count should equal the number of distinct order_ids
row_count_check AS (
    SELECT
        COUNT(*) AS actual_rows,
        COUNT(DISTINCT order_id) AS distinct_orders,
        CASE
            WHEN COUNT(*) = COUNT(DISTINCT order_id) THEN 'PASS'
            ELSE 'FAIL: join fan-out detected, ' || COUNT(*)::TEXT || ' rows for ' || COUNT(DISTINCT order_id)::TEXT || ' orders'
        END AS result
    FROM joined
)

SELECT * FROM row_count_check WHERE result != 'PASS';

This pattern — asserting that a count equals a count-distinct — is one of your most powerful tools for catching fan-out bugs.

Type Coercion and Implicit Casting

Different warehouses handle type coercion differently. BigQuery will happily cast '123' to 123 in some contexts. Snowflake might error where Redshift coerces silently. Write explicit tests for these boundaries:

-- test_id_types_are_consistent.sql
WITH
orders AS (
    SELECT '1001' AS order_id_string, 1001 AS order_id_int  -- simulating a mixed-type join key scenario
),

type_check AS (
    SELECT
        CASE
            WHEN order_id_string::INTEGER = order_id_int THEN 'PASS'
            ELSE 'FAIL: type mismatch in join key'
        END AS result
    FROM orders
)

SELECT * FROM type_check WHERE result != 'PASS';

Boundary Conditions in Date Logic

Date logic is fragile. Off-by-one errors in BETWEEN, DATEDIFF, and fiscal period calculations cause incorrect metrics that can persist for weeks before anyone notices.

-- test_date_boundary_conditions.sql
WITH
events AS (
    -- Test all the boundary cases for a "last 30 days" filter
    SELECT CURRENT_DATE AS event_date, 'today' AS label
    UNION ALL
    SELECT CURRENT_DATE - INTERVAL '29 days', '29_days_ago'  -- should be included
    UNION ALL
    SELECT CURRENT_DATE - INTERVAL '30 days', '30_days_ago'  -- boundary: depends on definition
    UNION ALL
    SELECT CURRENT_DATE - INTERVAL '31 days', '31_days_ago'  -- should be excluded
),

-- Logic: "last 30 days" means strictly greater than 30 days ago
filtered AS (
    SELECT label
    FROM events
    WHERE event_date > CURRENT_DATE - INTERVAL '30 days'
),

expected AS (
    SELECT 'today' AS label
    UNION ALL
    SELECT '29_days_ago'
    -- NOTE: 30_days_ago is excluded because we use >, not >=
    -- This is a deliberate business rule we're encoding as a test
),

failures AS (
    SELECT label FROM filtered EXCEPT SELECT label FROM expected
    UNION ALL
    SELECT label FROM expected EXCEPT SELECT label FROM filtered
)

SELECT * FROM failures;

Pro tip: Document your boundary decisions in test comments. "Why is 30 days ago excluded?" is a question that will come up in code review. Your test comment is the authoritative answer.

---

Defining and Enforcing Data Contracts

A data contract is a formal agreement about what a dataset guarantees to its consumers. It covers:

• Schema: column names, types, nullability
• Uniqueness: which columns (or combinations) form a unique key
• Referential integrity: foreign keys that must exist in parent tables
• Business rules: domain-specific constraints (amounts must be positive, status must be in a known set, etc.)

Data contracts are the difference between "I think this column is always populated" and "this column being populated is a tested, enforced guarantee."

Schema Contract Tests

-- test_schema_contract_customer_mrr.sql
-- Validates that the output of customer_mrr has the expected structure and types

WITH
schema_check AS (
    SELECT
        column_name,
        data_type,
        is_nullable
    FROM information_schema.columns
    WHERE table_schema = 'analytics'
      AND table_name = 'customer_mrr'
),

expected_schema AS (
    SELECT 'customer_id'  AS column_name, 'integer'                  AS data_type, 'NO'  AS is_nullable
    UNION ALL
    SELECT 'mrr_month',                    'date',                     'NO'
    UNION ALL
    SELECT 'total_mrr',                    'numeric',                  'YES'
),

failures AS (
    SELECT 'unexpected_column' AS issue, column_name, data_type, is_nullable
    FROM schema_check
    EXCEPT
    SELECT 'unexpected_column', column_name, data_type, is_nullable
    FROM expected_schema
    UNION ALL
    SELECT 'missing_column' AS issue, column_name, data_type, is_nullable
    FROM expected_schema
    EXCEPT
    SELECT 'missing_column', column_name, data_type, is_nullable
    FROM schema_check
)

SELECT * FROM failures;

This test will catch a column being renamed, a type being changed from INTEGER to BIGINT, or a column being added without updating the contract.

Uniqueness and Referential Integrity Contracts

-- test_referential_integrity.sql
-- Tests that every customer_id in orders exists in the customers table
-- and that orders.order_id is unique

WITH
orphaned_orders AS (
    SELECT o.order_id, o.customer_id
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
    WHERE c.customer_id IS NULL
),

duplicate_orders AS (
    SELECT order_id, COUNT(*) AS occurrences
    FROM orders
    GROUP BY order_id
    HAVING COUNT(*) > 1
),

failures AS (
    SELECT 'orphaned_customer_reference' AS failure_type, order_id::TEXT AS key_value, customer_id::TEXT AS detail
    FROM orphaned_orders

    UNION ALL

    SELECT 'duplicate_order_id' AS failure_type, order_id::TEXT, occurrences::TEXT
    FROM duplicate_orders
)

SELECT * FROM failures;

Business Rule Contracts

-- test_business_rules.sql
WITH
violations AS (
    -- Rule 1: MRR amounts must be positive
    SELECT 'negative_mrr' AS rule, customer_id::TEXT AS context, total_mrr::TEXT AS value
    FROM customer_mrr
    WHERE total_mrr <= 0

    UNION ALL

    -- Rule 2: mrr_month must not be in the future
    SELECT 'future_mrr_month', customer_id::TEXT, mrr_month::TEXT
    FROM customer_mrr
    WHERE mrr_month > DATE_TRUNC('month', CURRENT_DATE)

    UNION ALL

    -- Rule 3: every customer in MRR must exist in the customers table
    SELECT 'unknown_customer', m.customer_id::TEXT, m.total_mrr::TEXT
    FROM customer_mrr m
    LEFT JOIN customers c ON m.customer_id = c.customer_id
    WHERE c.customer_id IS NULL
)

SELECT * FROM violations;

---

Structuring Tests with dbt

If your team uses dbt, you get a testing infrastructure nearly for free. dbt has two categories of tests: generic tests (built-in or from packages) and singular tests (custom SQL files like the ones we've been writing).

dbt Generic Tests

In your schema.yml files, you can declare contracts directly on your models:

# models/analytics/schema.yml
version: 2

models:
  - name: customer_mrr
    description: Monthly recurring revenue aggregated by customer
    columns:
      - name: customer_id
        description: Unique identifier for the customer
        tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id

      - name: mrr_month
        description: First day of the month for this MRR record
        tests:
          - not_null

      - name: total_mrr
        description: Sum of active subscription amounts for the month
        tests:
          - not_null

    tests:
      - unique:
          column_name: "(customer_id || '-' || mrr_month::TEXT)"
      - dbt_utils.expression_is_true:
          expression: "total_mrr > 0"

These tests run via dbt test and produce clear pass/fail output with row counts for failures.

dbt Singular Tests for Complex Logic

For the logic-heavy tests we wrote earlier, put them in the tests/ directory:

-- tests/test_mrr_no_cancelled_subscriptions.sql
-- Passes if zero rows returned

WITH
cancelled_in_mrr AS (
    SELECT m.customer_id, m.mrr_month
    FROM {{ ref('customer_mrr') }} m
    INNER JOIN {{ ref('subscriptions') }} s
        ON m.customer_id = s.customer_id
        AND DATE_TRUNC('month', s.subscription_start_date) = m.mrr_month
    WHERE s.status = 'cancelled'
)

SELECT * FROM cancelled_in_mrr;

The dbt test contract: Any singular test that returns zero rows passes. Any test that returns one or more rows fails, and dbt reports those rows as the failure evidence. Design your tests accordingly — make the failure output diagnostic, not just boolean.

Using dbt-utils for Extended Test Coverage

The dbt-utils package provides tests that would otherwise require custom SQL:

# Test that a column contains only expected values
- name: subscription_status
  tests:
    - accepted_values:
        values: ['active', 'cancelled', 'paused', 'trial']

# Test that column values are within a range
- name: monthly_amount
  tests:
    - dbt_utils.accepted_range:
        min_value: 0
        max_value: 100000
        inclusive: false

# Test row count is within expected bounds (catches truncation)
tests:
  - dbt_utils.recency:
      datepart: day
      field: created_at
      interval: 1

---

Integrating SQL Tests into CI/CD Pipelines

Writing tests is only half the job. The tests need to run automatically on every change, before that change can be merged or deployed. Here's how to wire this up.

The Pipeline Architecture

A standard CI/CD pipeline for a dbt project looks like this:

1. Developer pushes a branch with model or test changes
2. CI runner triggers (GitHub Actions, GitLab CI, etc.)
3. Pipeline runs dbt in a test environment pointing at a staging schema
4. dbt build compiles models and runs all tests
5. If any test fails, the pipeline fails and the PR cannot be merged
6. On merge to main, the pipeline runs again for production deployment

GitHub Actions Configuration

# .github/workflows/dbt-ci.yml
name: dbt CI

on:
  pull_request:
    branches: [main]
    paths:
      - 'models/**'
      - 'tests/**'
      - 'macros/**'
      - 'schema.yml'

jobs:
  dbt-test:
    runs-on: ubuntu-latest
    environment: staging

    steps:
      - name: Checkout code
        uses: actions/checkout@v3

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'

      - name: Install dbt
        run: |
          pip install dbt-snowflake==1.7.0
          dbt deps

      - name: Write dbt profiles
        run: |
          mkdir -p ~/.dbt
          cat > ~/.dbt/profiles.yml << EOF
          analytics:
            target: ci
            outputs:
              ci:
                type: snowflake
                account: ${{ secrets.SNOWFLAKE_ACCOUNT }}
                user: ${{ secrets.SNOWFLAKE_CI_USER }}
                password: ${{ secrets.SNOWFLAKE_CI_PASSWORD }}
                role: CI_ROLE
                database: ANALYTICS_CI
                warehouse: CI_WAREHOUSE
                schema: dbt_ci_${{ github.event.pull_request.number }}
          EOF

      - name: Run dbt build (compile + test)
        run: |
          dbt build --target ci --select state:modified+

      - name: Upload test results
        if: always()
        uses: actions/upload-artifact@v3
        with:
          name: dbt-test-results
          path: target/run_results.json

A few things worth calling out in this config:

Schema isolation per PR: The schema name dbt_ci_${{ github.event.pull_request.number }} means each PR gets its own schema. Tests don't interfere with each other when multiple PRs are open simultaneously.

state:modified+: This dbt selector runs only models that changed in this PR, plus all their downstream dependents. This keeps CI fast — you're not rebuilding the entire warehouse on every PR.

dbt build over dbt run + dbt test: dbt build runs models and their tests together in dependency order. If a test on an upstream model fails, it doesn't try to build downstream models from bad data.

Adding pytest for Python-Orchestrated Tests

Sometimes you want more control than dbt's test runner gives you — parameterized tests, rich assertions, integration with external data validation libraries. pytest integrates cleanly:

# tests/test_sql_contracts.py
import pytest
import snowflake.connector
import os

@pytest.fixture(scope="session")
def conn():
    """Snowflake connection shared across all tests in this session."""
    connection = snowflake.connector.connect(
        account=os.environ["SNOWFLAKE_ACCOUNT"],
        user=os.environ["SNOWFLAKE_CI_USER"],
        password=os.environ["SNOWFLAKE_CI_PASSWORD"],
        database="ANALYTICS_CI",
        schema=f"DBT_CI_{os.environ.get('PR_NUMBER', 'local')}",
        warehouse="CI_WAREHOUSE"
    )
    yield connection
    connection.close()

def run_test_query(conn, sql):
    """Returns rows from a test query. An empty result = pass."""
    cursor = conn.cursor()
    cursor.execute(sql)
    return cursor.fetchall()

class TestCustomerMRRContracts:

    def test_no_negative_mrr(self, conn):
        """MRR amounts must always be positive."""
        rows = run_test_query(conn, """
            SELECT customer_id, total_mrr
            FROM customer_mrr
            WHERE total_mrr <= 0
        """)
        assert len(rows) == 0, f"Found {len(rows)} customers with non-positive MRR: {rows[:5]}"

    def test_no_orphaned_customers(self, conn):
        """Every customer_id in MRR must exist in the customers table."""
        rows = run_test_query(conn, """
            SELECT m.customer_id
            FROM customer_mrr m
            LEFT JOIN customers c ON m.customer_id = c.customer_id
            WHERE c.customer_id IS NULL
        """)
        assert len(rows) == 0, f"Found {len(rows)} orphaned customer IDs in MRR: {rows[:5]}"

    def test_mrr_uniqueness(self, conn):
        """Each customer should appear only once per month in MRR."""
        rows = run_test_query(conn, """
            SELECT customer_id, mrr_month, COUNT(*) AS occurrences
            FROM customer_mrr
            GROUP BY customer_id, mrr_month
            HAVING COUNT(*) > 1
        """)
        assert len(rows) == 0, f"Found {len(rows)} duplicate customer-month combinations: {rows[:5]}"

    @pytest.mark.parametrize("months_back,expected_min_customers", [
        (1, 100),   # Last month: expect at least 100 paying customers
        (3, 50),    # 3 months ago: at least 50
    ])
    def test_mrr_volume_expectations(self, conn, months_back, expected_min_customers):
        """MRR should have a reasonable number of customers each month."""
        rows = run_test_query(conn, f"""
            SELECT COUNT(DISTINCT customer_id) AS customer_count
            FROM customer_mrr
            WHERE mrr_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '{months_back} months')
        """)
        count = rows[0][0]
        assert count >= expected_min_customers, (
            f"Expected at least {expected_min_customers} customers "
            f"{months_back} month(s) ago, found {count}"
        )

This pytest approach shines when you need parameterized thresholds, custom assertion messages, and test organization that mirrors your model hierarchy.

---

Hands-On Exercise

You're a data engineer at a SaaS company. Your team has built a model called monthly_active_users (MAU) that tracks unique users who performed at least one event in a given month. The SQL logic is:

-- monthly_active_users model
SELECT
    DATE_TRUNC('month', event_date) AS activity_month,
    COUNT(DISTINCT user_id) AS mau_count
FROM user_events
WHERE event_type NOT IN ('bot_ping', 'test_event')
GROUP BY 1

Your task: Write a complete test suite for this model covering:

1. Logic test: Using fixture data, verify that:

   • Bot pings and test events are excluded
   • A user who fires 5 events in one month counts as 1 MAU
   • A user who fires events in two different months counts toward both months separately

2. Edge case test: Verify behavior when user_id is NULL (these should be excluded from the distinct count — or document whether they should be included)

3. Data contract test: Assert that:

   • activity_month is never in the future
   • mau_count is always greater than zero (no months with zero users should appear in the table)
   • The table has data for at least the last 12 consecutive months

Starter template for test 1:

-- tests/test_mau_logic.sql
WITH
raw_events AS (
    -- YOUR FIXTURE DATA HERE
    -- Include: normal events, bot_pings, test_events, duplicate user events, multi-month events
),

mau_logic AS (
    -- REPLICATE THE MODEL LOGIC HERE using raw_events instead of user_events
),

expected AS (
    -- YOUR EXPECTED OUTPUT HERE
),

failures AS (
    SELECT 'unexpected' AS type, activity_month, mau_count FROM mau_logic
    EXCEPT SELECT 'unexpected', activity_month, mau_count FROM expected
    UNION ALL
    SELECT 'missing' AS type, activity_month, mau_count FROM expected
    EXCEPT SELECT 'missing', activity_month, mau_count FROM mau_logic
)

SELECT * FROM failures;

Work through all three tests before looking at common mistakes below. The edge case around NULL user_id is deliberately ambiguous — the right answer depends on your business definition, and documenting that decision in your test is as important as the test logic itself.

---

Common Mistakes & Troubleshooting

Mistake 1: Testing the Database, Not the Logic

This test is wrong:

-- BAD: This tests whether production data is clean, not whether the logic is correct
SELECT COUNT(*) FROM customer_mrr WHERE total_mrr < 0;

The problem is that this test depends on what's currently in the production table. If production data is clean today, the test passes. If someone loads bad data tomorrow, the test fails — but not because the logic is wrong. You now have a flaky test that's hard to reason about.

Fix: Tests that validate production data quality are data quality monitors, not unit tests. They belong in a monitoring layer (like dbt's --select on production), not in your CI/CD logic tests. Unit tests should use fixture data.

Mistake 2: Not Testing What You Think You're Testing

-- This test passes trivially if your WHERE clause is too aggressive
WITH fixture AS (SELECT 1 AS x WHERE 1=0),  -- empty dataset
logic AS (SELECT * FROM fixture WHERE x > 0),
expected AS (SELECT 1 AS x WHERE 1=0)  -- also empty
...
-- FAILS to catch bugs because both sides are empty and EXCEPT of two empty sets = empty

Always include at least one row in your expected output. If your expected result is truly empty (like "this filtered output should have zero rows"), use a COUNT assertion instead of EXCEPT:

SELECT COUNT(*) AS failure_count FROM logic WHERE <condition that should not exist>;
-- Then assert in your test runner: failure_count = 0

Mistake 3: Ignoring NULL Semantics in Assertions

In standard SQL, NULL != NULL. This means:

SELECT NULL AS col EXCEPT SELECT NULL AS col;
-- Returns: 1 row (the NULL)  -- wait, actually...

Actually, EXCEPT does treat NULLs as equal for set comparison purposes in most warehouses. But JOIN-based comparison does not:

-- This join will NEVER match NULL values
SELECT * FROM actual a
JOIN expected e ON a.col = e.col  -- NULL = NULL is FALSE in a JOIN

If you ever write comparison tests using JOINs instead of EXCEPT, you'll get false positives for NULL columns. Stick with EXCEPT for set comparison, and write dedicated NULL tests using IS NULL / IS NOT NULL.

Mistake 4: Tests That Are Too Slow to Run in CI

A test that scans 500M rows to validate a contract will time out in CI and get disabled. If your dataset is large:

• Use LIMIT + ORDER BY in your fixture queries when testing logic (not contracts)
• Write contract tests that sample recent partitions: WHERE created_at >= CURRENT_DATE - 7
• Use TABLESAMPLE for statistical validation on large datasets

-- Faster referential integrity check on large tables using sampling
SELECT o.order_id, o.customer_id
FROM orders TABLESAMPLE BERNOULLI(1)  -- 1% sample
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Mistake 5: Not Encoding the Business Decision in the Test

When you write a boundary test and choose > instead of >=, or choose to include vs. exclude a status, that's a business decision. If you don't document it, someone will "fix" your test in six months and introduce a silent regression.

-- BAD: Just a condition with no explanation
WHERE event_date > CURRENT_DATE - INTERVAL '30 days'

-- GOOD: Explain why this boundary exists
-- Business rule (confirmed with product 2024-03-12, ticket DATA-447):
-- "Last 30 days" means events from yesterday back 30 days; today's events are excluded
-- because they are incomplete. Use strict > not >= for the 30-day boundary.
WHERE event_date > CURRENT_DATE - INTERVAL '30 days'

Troubleshooting: "My test fails in CI but passes locally"

The most common causes:

1. Different data in CI vs. local: You're testing against live data, not fixtures. Migrate to fixture-based tests.
2. Schema name differences: Your CI schema is dbt_ci_42 but your test hardcodes analytics. Use {{ target.schema }} in dbt or environment variables in pytest.
3. Timezone handling: CURRENT_DATE behaves differently in UTC environments vs. your local timezone. Pin your test dates to fixed values or use explicit AT TIME ZONE casts.
4. Warehouse-specific functions: DATE_TRUNC works in Postgres and Snowflake but not BigQuery (use DATE_TRUNC(field, MONTH) in BigQuery). Abstract these in dbt macros.

---

Summary & Next Steps

You now have a complete mental model for SQL testing at the practitioner level. Let's consolidate the key ideas:

The Fixture-Logic-Assert pattern is the foundation of every SQL unit test. Control your inputs, replicate your logic, define your expected output, and use EXCEPT-based comparison to find discrepancies. This approach is warehouse-agnostic and works in pure SQL without external tools.

Edge cases have SQL-specific shapes. NULL handling, join fan-outs, type coercions, and date boundaries are the categories most likely to bite you in production. Design tests for each of these categories for every model that matters.

Data contracts formalize implicit assumptions. Schema, uniqueness, referential integrity, and business rules should be executable tests, not wiki documentation. Tests that live in version control get maintained; documentation doesn't.

CI/CD integration is what makes testing worthwhile. Tests that only run when someone remembers to run them don't catch regressions. Wire your dbt tests into GitHub Actions (or your CI platform of choice), use schema isolation per PR, and use state:modified+ to keep CI fast.

Where to go from here:

• Explore the dbt-expectations package, which brings pytest-style assertions to dbt's testing layer
• Study the Great Expectations framework for warehouse-native data quality validation with richer assertion types and historical tracking
• Learn about contract testing patterns from software engineering (particularly consumer-driven contract testing) and how they apply to data pipelines with multiple consumers
• Build a test coverage tracker: a query that reports which models in your dbt project have zero tests, which have only generic tests, and which have custom logic tests — then use it in sprint planning to prioritize test debt

The investment in SQL testing pays off faster than most teams expect. The first time CI catches a fan-out bug before it hits your revenue dashboard, you'll have made back the time you spent writing tests — several times over.