0Pricing
SQL Academy · Lesson

Testing SQL: dbt tests, Great Expectations

Write data quality tests with dbt (unique, not_null, accepted_values) and richer assertions with Great Expectations.

Testing SQL: dbt tests, Great Expectations is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Test SQL?

Bad data is invisible until it causes a bad decision. Tests catch:

  • Schema drift (column dropped, type changed)
  • Data quality (NULLs where forbidden, duplicates, out-of-range)
  • Referential issues (orphan rows)
  • Business invariants (revenue ≥ 0, totals add up)

dbt Tests: Built-In

Declare per-column in YAML:

# models/orders.yml
version: 2
models:
  - name: orders
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: user_id
        tests:
          - not_null
          - relationships:
              to: ref('users')
              field: id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'cancelled']

Running dbt Tests

Execute all tests:

dbt test
dbt test --select orders
dbt build  # = run + test

Custom Singular Tests

A test is just a SELECT that returns rows on failure:

-- tests/no_negative_totals.sql
SELECT id, total
FROM {{ ref('orders') }}
WHERE total < 0

Generic Tests

Parameterised tests for reuse across many columns:

-- tests/generic/test_in_range.sql
{% test in_range(model, column_name, min, max) %}
SELECT * FROM {{ model }}
WHERE {{ column_name }} < {{ min }} OR {{ column_name }} > {{ max }}
{% endtest %}

-- Use it in YAML:
columns:
  - name: age
    tests:
      - in_range:
          min: 0
          max: 120

dbt-utils Package

Adds more tests: unique combinations, expression_is_true, equal_rowcount, etc.:

- dbt_utils.expression_is_true:
    expression: "revenue >= 0"
- dbt_utils.equal_rowcount:
    compare_model: ref('staging_orders')

Great Expectations

Heavier alternative — a Python framework with extensive expectation library and rich reports:

import great_expectations as ge

suite = context.add_or_update_expectation_suite('orders')
batch = context.get_validator(batch_request, expectation_suite_name='orders')
batch.expect_column_values_to_not_be_null('id')
batch.expect_column_values_to_be_between('total', 0, 10000)

results = context.run_checkpoint('orders_checkpoint')

When to Use Each

  • dbt tests — first choice when you're already using dbt; lightweight, SQL-only
  • Great Expectations — when you need rich reports, broader assertions, beyond-SQL formats (CSV/parquet)

Test Severity

dbt 1.0+ supports warn vs error severity:

- accepted_values:
    values: [...]
    config:
      severity: warn
      warn_if: ">100"
      error_if: ">1000"

CI Integration

Run tests in every PR — block merge on failure:

# GitHub Actions
- run: dbt deps && dbt build --profiles-dir profiles
- if: failure()
  run: echo "::error::dbt tests failed"

Data Quality Dashboards

dbt-utils + dbt-expectations + Elementary can turn test results into a Grafana / Looker dashboard.

Schema Tests vs Row Tests

Catch schema regressions with dbt's "snapshot" of column types; row tests catch data bugs.

Recap

Testing turns SQL from "trust me" into "verified".

  • dbt tests for declarative, in-warehouse
  • Great Expectations for broader / richer
  • Run in CI; block on failure
  • Track results over time

Quick Check

You want a dbt test that fails if any row has total < 0. What's the simplest approach?

Frequently asked questions

Is the “Testing SQL: dbt tests, Great Expectations” lesson free?

Yes — the full text of “Testing SQL: dbt tests, Great Expectations” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Testing SQL: dbt tests, Great Expectations”?

Write data quality tests with dbt (unique, not_null, accepted_values) and richer assertions with Great Expectations. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Testing SQL: dbt tests, Great Expectations” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. DBT (Data Build Tool) Fundamentals
  2. SQLFluff and Linting
  3. Testing SQL: dbt tests, Great Expectations
  4. CI for Schema Changes (GitHub Actions)
← Back to SQL Academy