Data Quality Testing with dbt — Schema Tests to CI/CD

This lesson on Data Quality Testing with dbt — Schema Tests to CI/CD is hands-on and example-driven. You will implement automated data quality gates in dbt using built-in schema assertions, custom SQL business logic, and source freshness monitors. You will also learn to optimize tests for incremental workloads and enforce quality standards within CI/CD pipelines to block corrupted data from downstream consumers.

What You'll Be Able To Do

• Configure built-in schema assertions (unique, not_null, accepted_values, relationships) in YAML model files.
• Author custom generic and singular SQL tests to validate complex business rules.
• Define source freshness thresholds in configuration files to detect stale ingestion pipelines.
• Scope test execution on incremental models to validate only newly ingested records.
• Automate dbt test runs inside a GitHub Actions CI/CD workflow to gate pull request merges.
• Develop reusable dbt macros to standardize dynamic testing logic across models.

Detailed Concept Walkthrough

1. Built-In Schema Tests and Declarations

dbt provides four out-of-the-box generic tests (unique, not_null, accepted_values, relationships) declared declaratively inside YAML specification files. These assertions compile directly into SQL queries that count failing records.

• Mechanism: Tests are attached to specific columns or models in YAML files; when executed, dbt wraps each test into a validation query that selects rows violating the condition.
• Under the Hood: A dbt test passes if the compiled query returns zero rows; any non-zero record count causes the test to fail or warn depending on configured severity.
• Best Practice: Always apply unique and not_null to primary keys, and restrict categorical fields using accepted_values to prevent schema drift.

version: 2

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']

Key Takeaway: Built-in schema tests turn declarative YAML constraints into SQL queries where zero returned rows denotes success.

2. Custom SQL and Business Logic Testing

Custom tests express domain-specific business rules that out-of-the-box assertions cannot capture, written as standalone SQL queries stored in the tests/ directory.

• Mechanism: Developers write a query in tests/ that selects records failing the business assertion (e.g., transactions timestamped in the future).
• Execution Flow: During dbt test, dbt runs each SQL file in the tests/ path; if any rows are returned, the assertion is marked as failed.
• Best Practice: Parameterize reusable logic into generic test macros when the same business assertion applies to multiple models or columns across the project.

-- tests/not_in_future.sql
-- Fails if any order_date is ahead of the current timestamp
select
    order_id,
    order_date
from {{ ref('orders') }}
where order_date > current_date

Key Takeaway: Custom SQL tests follow the dbt assertion contract: return zero rows on success, or return violating records on failure.

3. Source Freshness and Ingestion Monitoring

Source freshness checks monitor ingestion latency by evaluating the timestamp of the latest record against defined SLA thresholds before downstream models run.

• Mechanism: Freshness blocks are defined under the sources key, specifying a loaded_at_field column and warning/error time windows.
• Under the Hood: Executing dbt source freshness queries the maximum timestamp of the specified field and compares the difference against current_timestamp.
• Execution Flow: If data ingestion has stalled beyond the error threshold, pipelines can halt early before running downstream transformation models.

version: 2

sources:
  - name: raw_ecommerce
    tables:
      - name: raw_orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}

Key Takeaway: Source freshness gates catch pipeline ingestion failures before running downstream transformations on stale raw data.

4. Incremental Test Scoping and Performance

Incremental testing avoids full-table scans on massive datasets by restricting data quality assertions strictly to newly ingested records.

• Mechanism: Custom tests apply filter predicates that restrict query scope using ingestion timestamps or metadata filters relative to recent execution windows.
• Under the Hood: Rather than scanning hundreds of millions of historical rows, the query planner prunes partitions and only reads records modified since the previous run.
• Best Practice: Combine incremental scoping with severity configuration to fail builds immediately on new data errors without re-flagging static legacy data.

-- tests/generic/test_new_records_only.sql
-- Validates non-negative amounts only for data ingested in the last 3 days
select
    order_id,
    amount
from {{ ref('orders') }}
where _loaded_at >= dateadd('day', -3, current_date)
  and amount < 0

Key Takeaway: Scoping tests to recent records prevents expensive full-table scans and keeps test execution times bounded on growing datasets.

5. CI/CD Integration and Dynamic Macros

Integrating tests into CI/CD prevents bad code and regressions from merging to main, while macros allow parameterized, dynamic testing logic across tables.

• Mechanism: Continuous integration pipelines execute dbt test against staging environments on pull requests to validate code changes against data contracts.
• Execution Flow: A non-zero exit code from a failing dbt test blocks the pull request from merging, preventing corrupted transformations in production.
• Best Practice: Use Jinja macros to encapsulate repetitive validation queries or wrap external framework integrations such as Great Expectations.

# .github/workflows/dbt_tests.yaml
name: dbt Quality Gate
on: [pull_request]
jobs:
  run_tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - run: pip install dbt-snowflake
      - run: dbt deps
      - run: dbt test --target staging

Key Takeaway: Automating dbt tests in CI/CD ensures pull requests are blocked before schema or logic errors reach the production data warehouse.

Topics Covered in Data Quality Testing with dbt — Schema Tests to CI/CD

1. Built-in Schema Tests (01:33 - 03:39) — Configuring unique, not null, accepted values, and relationship constraints inside model YAML files.
2. Custom SQL Tests (03:39 - 05:32) — Writing standalone SQL assertions in the tests directory to enforce business logic like date boundaries.
3. Source Freshness Checks (05:32 - 07:11) — Defining latency SLAs and alert thresholds to monitor data ingestion staleness.
4. Incremental Model Testing (07:11 - 08:27) — Restricting test execution to newly ingested data windows to avoid costly full-table scans.
5. CI/CD Workflow Integration (08:27 - 09:54) — Configuring GitHub Actions to automate dbt test execution and gate pull request merges.
6. Dynamic Testing with Macros (09:54 - 10:55) — Using Jinja macros to create reusable test functions and connect external validation tools.

Data Engineering Cheat Sheet

• dbt test — Executes all configured schema and custom SQL tests

  dbt test
  

• dbt source freshness — Checks source table data recency against SLAs

  dbt source freshness
  

• dbt test --select <model> — Runs tests only for a specified model

  dbt test --select orders
  

• unique — Asserts all column values are distinct

  tests: [unique]
  

• not_null — Asserts column contains no null values

  tests: [not_null]
  

• accepted_values — Asserts column values match an allowed list

  tests: [accepted_values: {values: ['a', 'b']}]
  

• relationships — Validates referential integrity against foreign key table

  tests: [relationships: {to: ref('users'), field: id}]
  

Comparison Table

Common Pitfalls

• Mistake: Writing custom tests that return passing records instead of failing records. Avoid: Ensure custom SQL selects only violating rows so empty result sets represent passing tests.
• Mistake: Running unbounded full-table scans on massive incremental models during routine testing. Avoid: Scope custom test queries to recently loaded records using timestamp filters.
• Mistake: Treating source freshness warnings as successful silent runs. Avoid: Configure error thresholds that halt downstream job steps when ingestion pipelines exceed latency SLAs.

FAQs

• How does dbt know whether a custom SQL test passed or failed? dbt executes the query and checks the returned row count. If zero rows are returned, the test passes; if one or more rows are returned, it fails.
• What is the difference between warning and error severities in dbt tests? A warning logs an alert but allows pipeline execution to continue, whereas an error returns a non-zero exit code and halts dependent steps.
• Where should source freshness be configured in a dbt project? Source freshness is configured under the sources: block inside model YAML files or dbt_project.yml using loaded_at_field and threshold rules.