data-quality

Data Quality

Testing patterns for dbt projects on analytical warehouses such as BigQuery. This workflow uses dbt tests exclusively — no external data-quality framework required.

Test Types

1. Generic Tests (in YAML files)

Defined in .yml files alongside models. Use data_tests: (not tests:):

# models/core/shared/dim_teams.yml
models:
  - name: dim_teams
    columns:
      - name: id
        description: Surrogate key
        data_tests:
          - unique
          - not_null

      - name: natural_id
        description: Natural key from source
        data_tests:
          - unique
          - not_null

      - name: plan_code
        description: Subscription plan code
        data_tests:
          - accepted_values:
              values: "{{ get_plan_code_values() }}"  # Use macros, not hardcoded lists

2. Singular Tests (assert_*.sql files)

Business logic tests live in tests/ as assert_*.sql files. They pass when they return zero rows:

-- tests/teams/assert_teams_created_before_deleted.sql
with teams as (
    select * from {{ ref('dim_teams') }}
)

select
    teams.natural_id,
    teams.created_at,
    teams.deleted_at
from teams
where teams.deleted_at < teams.created_at

-- tests/academy/assert_students_with_completed_modules_are_enrolled.sql
with completed as (
    select distinct student_id from {{ ref('fct_academy_course_modules_completed') }}
),

enrolled as (
    select distinct student_id from {{ ref('fct_academy_course_enrollments') }}
),

orphaned as (
    select completed.student_id
    from completed
    left join enrolled
        on completed.student_id = enrolled.student_id
    where enrolled.student_id is null
)

select * from orphaned

Organize singular tests in subdirectories matching the domain: tests/accounts/, tests/billing/, tests/content/, etc.

Generic Test Patterns

Primary Key Tests

Every model's primary key (id) must have both tests:

- name: id
  data_tests:
    - unique
    - not_null