This is Part 1 of a 3-part series on dbt unit testing. This article covers the fundamentals: syntax, setup, BigQuery considerations, and CI/CD integration.

You’ve written a model with complex business logic—maybe a customer lifetime value calculation, a sessionization algorithm, or an attribution model. It works in development. It passes code review. Then three weeks later, someone discovers edge cases are producing wrong results in production.

Before dbt 1.8, catching these logic bugs meant either building elaborate testing frameworks with external packages or discovering problems after bad data had already propagated downstream. That changed in May 2024 when dbt introduced native unit testing.

Unit tests let you validate transformation logic with mocked, static inputs—before your models ever touch production data. In this guide, we’ll build a complete unit testing setup from scratch, with particular attention to BigQuery-specific considerations.

Understanding Unit Tests in dbt

A unit test answers a simple question: “If I feed this model exactly these input rows, does it produce exactly these output rows?”

Unlike data tests (which run on actual warehouse data after materialization), unit tests run on mocked data during the build phase. They test your logic, not your data.

# A simple unit test
unit_tests:
  - name: test_is_valid_email
    model: mrt__core__customers
    given:
      - input: ref('base__crm__customers')
        rows:
          - {customer_id: 1, email: "valid@example.com"}
          - {customer_id: 2, email: "invalid-email"}
    expect:
      rows:
        - {customer_id: 1, is_valid_email: true}
        - {customer_id: 2, is_valid_email: false}

This test doesn’t care what’s in your actual base__crm__customers table. It creates mock data, runs your model’s SQL against it, and checks if the output matches expectations.

When you run dbt test --select test_type:unit, dbt executes only unit tests. When you run dbt test --select test_type:data, it runs only data tests (generic and singular tests). This separation is crucial for CI/CD workflows, as we’ll see later.

YAML Syntax Deep Dive

Required Elements

Every unit test needs four things: a name, a target model, input data, and expected output.

unit_tests:
  - name: test_customer_status_logic      # Unique identifier
    model: mrt__core__customers            # Model being tested
    given:                                 # Mock inputs
      - input: ref('base__crm__customers')
        rows:
          - {customer_id: 1, status: "active"}
    expect:                                # Expected output
      rows:
        - {customer_id: 1, is_active: true}

The given section accepts ref() for models, source() for sources, and this for self-references in incremental models. You only need to specify columns that your logic actually uses; dbt handles the rest.

Input Formats

dbt supports three formats for defining test data: dict, csv, and sql.

Dict format (default) is the most readable for small datasets:

given:
  - input: ref('base__shopify__orders')
    format: dict
    rows:
      - {order_id: 1, amount: 100.00, status: "completed"}
      - {order_id: 2, amount: 50.00, status: "pending"}

CSV format works well for larger datasets or when you want external fixture files:

given:
  - input: ref('base__shopify__orders')
    format: csv
    rows: |
      order_id,amount,status
      1,100.00,completed
      2,50.00,pending

Or reference an external file:

given:
  - input: ref('base__shopify__orders')
    format: csv
    fixture: order_test_data

This looks for tests/fixtures/order_test_data.csv in your project.

SQL format is required for ephemeral models and empty table scenarios:

given:
  - input: ref('base__shopify__orders')
    format: sql
    rows: |
      select 1 as order_id, 100.00 as amount, 'completed' as status
      union all
      select 2 as order_id, 50.00 as amount, 'pending' as status

For empty tables (testing zero-row scenarios):

given:
  - input: ref('base__shopify__orders')
    format: sql
    rows: |
      select
        cast(null as int64) as order_id,
        cast(null as float64) as amount
      where false

Optional Configuration

Beyond the basics, you can add metadata, tags, and conditional enablement:

unit_tests:
  - name: test_revenue_calculation
    model: mrt__finance__orders
    description: "Validates gross revenue calculation including tax"

    config:
      tags: ["critical", "finance"]
      meta:
        owner: "data-team"
        ticket: "DATA-1234"
      enabled: "{{ target.name != 'prod' }}"  # v1.9+ only

    given:
      - input: ref('base__shopify__orders')
        rows:
          - {order_id: 1, subtotal: 100.00, tax_rate: 0.08}
    expect:
      rows:
        - {order_id: 1, gross_revenue: 108.00}

Tags enable selective test runs (dbt test --select tag:critical), while the enabled config lets you skip tests in certain environments.

BigQuery-Specific Considerations

How Unit Tests Execute on BigQuery

When you run a unit test, dbt generates a query with CTEs containing your mocked data, then executes your model’s SQL against those CTEs. The output is compared row-by-row against your expected results.

This means unit tests do consume BigQuery slots (they’re not free). However, you can minimize costs by using the --empty flag when building parent models:

# Build schema-only versions of upstream models
dbt run --select +model_with_unit_tests --empty

# Then run unit tests
dbt test --select test_type:unit

The --empty flag creates tables with correct schemas but zero rows, which is sufficient for unit test compilation.

Known Quirks and Workarounds

STRUCT fields must be complete. Unlike regular columns where you can specify only what you need, STRUCT fields require all nested fields:

# This won't work if address has more fields
given:
  - input: ref('base__crm__customers')
    rows:
      - {customer_id: 1, address: {city: "Paris"}}  # Missing other fields!

# Specify all fields
given:
  - input: ref('base__crm__customers')
    rows:
      - {customer_id: 1, address: {street: "123 Rue Example", city: "Paris", postal_code: "75001", country: "FR"}}

STRUCT and ARRAY comparisons fail with standard equality. BigQuery doesn’t support EXCEPT operations on complex types. Use column_transformations to convert them to comparable strings:

unit_tests:
  - name: test_event_aggregation
    model: int__users_activity_summary
    given:
      - input: ref('base__ga4__events')
        rows:
          - {user_id: 1, event_type: "click", properties: {page: "/home", duration: 30}}
    expect:
      format: dict
      rows:
        - {user_id: 1, activity_details: {total_events: 1, event_types: ["click"]}}
      column_transformations:
        activity_details: "to_json_string(##column##)"

The ##column## placeholder gets replaced with the actual column reference.

ARRAY columns in CSV format can be problematic. Stick to dict format for arrays, or use sql format for complex scenarios.

Mocking Dependencies

Mocking Multiple Refs and Sources

Most models join multiple tables. You need to provide mock data for each dependency:

unit_tests:
  - name: test_order_enrichment
    model: mrt__sales__orders
    given:
      - input: ref('base__shopify__orders')
        rows:
          - {order_id: 1, customer_id: 100, product_id: 500, quantity: 2}
      - input: ref('int__customers_enriched')
        rows:
          - {customer_id: 100, customer_segment: "enterprise"}
      - input: ref('base__shopify__products')
        rows:
          - {product_id: 500, unit_price: 49.99}
    expect:
      rows:
        - {order_id: 1, customer_segment: "enterprise", order_value: 99.98}

You only need to include columns your model actually references. If int__customers_enriched has 50 columns but you only join on customer_id and select customer_segment, those two columns are sufficient.

Overriding Macros and Variables

Many models use macros for timestamps or conditional logic. Override them for deterministic tests:

unit_tests:
  - name: test_created_date_logic
    model: mrt__core__customers
    overrides:
      macros:
        dbt_utils.current_timestamp: "'2024-06-15 10:00:00'"
      vars:
        days_threshold: 30
    given:
      - input: ref('base__crm__customers')
        rows:
          - {customer_id: 1, created_at: "2024-06-01 09:00:00"}
    expect:
      rows:
        - {customer_id: 1, is_recent: true}

This is particularly important for testing time-based logic where current_timestamp() would otherwise make tests non-deterministic.

The this Keyword for Incremental Models

For incremental models, this represents the current state of the target table:

unit_tests:
  - name: test_incremental_dedup
    model: int__events_deduplicated
    overrides:
      macros:
        is_incremental: true
    given:
      - input: ref('base__ga4__events')
        rows:
          - {event_id: 1, event_time: "2024-06-15"}
          - {event_id: 2, event_time: "2024-06-16"}
      - input: this
        rows:
          - {event_id: 1, event_time: "2024-06-15"}  # Already exists
    expect:
      rows:
        - {event_id: 2, event_time: "2024-06-16"}  # Only new row

Part 2 covers incremental testing patterns in depth.

Project Setup and File Organization

Where to Put Unit Tests

Co-locate unit tests with their models using a _unit_tests.yml file:

models/
├── base/
│   ├── crm/
│   │   ├── base__crm__customers.sql
│   │   └── _crm__models.yml
│   └── shopify/
│       ├── base__shopify__orders.sql
│       └── _shopify__models.yml
├── intermediate/
│   ├── int__customers_enriched.sql
│   └── _intermediate__models.yml
├── marts/
│   ├── core/
│   │   ├── mrt__core__customers.sql
│   │   ├── _core__models.yml
│   │   └── _unit_tests.yml       # Unit tests for core marts
│   └── finance/
│       ├── mrt__finance__orders.sql
│       └── _finance__models.yml
tests/
└── fixtures/
    ├── customer_fixture.csv
    └── large_order_dataset.csv

This keeps tests close to the code they’re testing, making maintenance easier. External fixtures go in tests/fixtures/ for reusability.

Naming Conventions

Adopt a consistent naming pattern:

unit_tests:
  # Pattern: test_<model>_<scenario>
  - name: test_mrt_core_customers_email_validation
  - name: test_mrt_core_customers_null_handling
  - name: test_mrt_finance_orders_discount_calculation
  - name: test_mrt_finance_orders_zero_quantity_edge_case

Descriptive names make test failures immediately understandable in CI logs.

Running Unit Tests

CLI Commands

# Run all unit tests
dbt test --select test_type:unit

# Run unit tests for a specific model
dbt test --select mrt__core__customers,test_type:unit

# Run unit tests with a specific tag
dbt test --select tag:critical,test_type:unit

# Run a specific unit test by name
dbt test --select test_mrt_core_customers_email_validation

# Build models and run all tests (unit + data)
dbt build

# Build but exclude unit tests (for production)
dbt build --exclude-resource-type unit_test

Interpreting Output

A passing test shows:

PASS test_mrt_core_customers_email_validation

A failing test shows a diff:

FAIL test_mrt_core_customers_email_validation
Got:
  customer_id | is_valid_email
  1           | false
Expected:
  customer_id | is_valid_email
  1           | true

For detailed debugging, add --debug to see the generated SQL.

CI/CD Integration with GitHub Actions

Here’s a production-ready workflow for BigQuery:

name: dbt CI

on:
  pull_request:
    branches: [main]

env:
  DBT_PROFILES_DIR: ./
  GOOGLE_APPLICATION_CREDENTIALS: ${{ secrets.GCP_SA_KEY_PATH }}

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.11"

      - name: Install dbt
        run: pip install dbt-bigquery

      - name: Set up GCP credentials
        run: echo '${{ secrets.GCP_SA_KEY }}' > /tmp/gcp-key.json
        env:
          GCP_SA_KEY: ${{ secrets.GCP_SA_KEY }}

      - name: Create CI dataset name
        run: |
          echo "CI_DATASET=ci_$(date +'%Y%m%d_%H%M%S')_${GITHUB_SHA::7}" >> $GITHUB_ENV

      - name: Build upstream models (empty)
        run: |
          dbt run --select +test_type:unit --empty --target ci
        env:
          CI_DATASET: ${{ env.CI_DATASET }}

      - name: Run unit tests
        run: |
          dbt test --select test_type:unit --target ci
        env:
          CI_DATASET: ${{ env.CI_DATASET }}

      - name: Cleanup CI dataset
        if: always()
        run: |
          bq rm -r -f ${{ env.CI_DATASET }}

Key patterns:

• Unique dataset per CI run prevents conflicts
• --empty flag minimizes BigQuery costs
• Cleanup runs even if tests fail (if: always())
• Unit tests run separately from data tests

Excluding Unit Tests from Production

Since unit tests use mocked data, running them in production adds no value. Exclude them:

# In your production deployment script
dbt build --exclude-resource-type unit_test

Or set an environment variable:

export DBT_EXCLUDE_RESOURCE_TYPES=unit_test
dbt build

Troubleshooting Common Errors

Version Compatibility Notes

Unit testing syntax has evolved across dbt versions:

• dbt 1.8 (May 2024): Unit testing introduced. The tests: key was renamed to data_tests: to distinguish from unit tests.
• dbt 1.9: Added enabled config option for conditional test execution. New --resource-type and --exclude-resource-type flags.
• dbt 1.11: Unit tests for disabled models are now automatically disabled.

If you’re upgrading from pre-1.8, rename any tests: blocks in your YAML to data_tests: to avoid conflicts.

What’s Next

You now have everything needed to set up unit testing in your dbt project: the YAML syntax, BigQuery-specific workarounds, and a CI/CD workflow that keeps costs low.

Part 2 applies these fundamentals to real-world scenarios: testing incremental models, snapshot logic, window functions, and marketing analytics patterns like GA4 sessionization and attribution modeling.