dbt Unit Tests BigQuery Workarounds

dbt unit tests work across all supported warehouses, but BigQuery introduces a handful of quirks that will bite you if you’re not prepared. Most stem from BigQuery’s handling of complex types (STRUCTs and ARRAYs) and its slot-based execution model. None are dealbreakers, but they require specific workarounds.

How Unit Tests Execute on BigQuery

When you run a unit test, dbt generates a SQL 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.

The important implication: unit tests consume BigQuery slots. They’re not free. Each test is a real query that gets executed in your warehouse. For a handful of tests this is negligible, but if you have hundreds of unit tests running in CI, the cost adds up.

You can minimize this by using the --empty flag when building parent models before running unit tests:

# 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. This is sufficient for unit test compilation because unit tests use their own mocked data — they just need the upstream schemas to exist so the SQL compiles.

STRUCT Fields Must Be Complete

Unlike regular columns where you can specify only the fields your logic uses, STRUCT fields require all nested fields to be present in your mock data:

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

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

This is a BigQuery-specific requirement. When dbt generates the CTE for your mocked STRUCT, it needs to construct a valid STRUCT literal, and BigQuery doesn’t allow partial STRUCT construction. Every field needs a value (even if it’s null).

This gets tedious fast for deeply nested STRUCTs like those in GA4 event data, where the event_params STRUCT has many fields. The practical advice: if your model only reads event_params.value.string_value, you still need to include all sibling fields in the STRUCT definition for the mock. Use null for fields you don’t care about.

STRUCT and ARRAY Comparison Failures

BigQuery doesn’t support EXCEPT operations on complex types. When dbt compares your model’s output to the expected rows, it uses set difference operations — and those fail on columns containing STRUCTs or ARRAYs.

The fix is column_transformations, which lets you convert complex types to comparable strings before the comparison:

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 at execution time. to_json_string() is the go-to function here — it serializes any complex type into a comparable string representation.

You can apply column_transformations to multiple columns:

expect:
  rows:
    - {user_id: 1, tags: ["premium", "active"], address: {city: "Paris"}}
  column_transformations:
    tags: "to_json_string(##column##)"
    address: "to_json_string(##column##)"

One subtle gotcha: the JSON serialization must produce identical strings for the comparison to pass. If your model produces ["active", "premium"] but your expected output has ["premium", "active"], the test fails because the JSON strings differ even though the arrays contain the same elements. Be mindful of ordering.

ARRAY Columns in CSV Format

Don’t use CSV format for inputs that contain ARRAY columns. CSV has no native way to represent arrays, and the parsing behavior is unreliable. Stick to dict format for arrays:

# Good: dict format handles arrays naturally
given:
  - input: ref('base__events')
    rows:
      - {event_id: 1, tags: ["click", "mobile"]}

# Bad: CSV can't represent arrays cleanly
# Avoid this combination

For complex scenarios involving ARRAYs of STRUCTs (common in GA4 data), use SQL format instead:

given:
  - input: ref('base__ga4__events')
    format: sql
    rows: |
      select
        1 as event_id,
        [struct('page_location' as key, struct('/home' as string_value, cast(null as int64) as int_value) as value)] as event_params

Verbose, but explicit and reliable.

Troubleshooting Common Errors

These errors come up repeatedly when writing BigQuery unit tests:

The “Not able to get columns” error is the most common for teams new to unit testing. It’s not a bug — dbt needs the upstream table schemas to exist in the database so it can compile the unit test SQL. The --empty flag creates those schemas without processing any data.

The type mismatch error is particularly sneaky. If your model produces a FLOAT64 but your mock data provides an INT64, the comparison fails even if the values are numerically equal. When in doubt, use SQL format for the expect block so you can explicitly cast types.

Cost Optimization

For teams running many unit tests against BigQuery, a few practices keep costs manageable:

1. Use --empty for upstream builds as described above. This is the single biggest cost saver.
2. Keep mock datasets small. Four to six rows per input is usually sufficient to test edge cases. You’re testing logic, not load.
3. Run unit tests on a separate, smaller reservation if you’re using BigQuery editions. Unit tests are lightweight queries that don’t need production-grade slot capacity.
4. Tag unit tests by priority so you can run critical tests frequently and less critical tests on a schedule.

The CI/CD workflow section covers the full pattern for minimizing BigQuery costs in automated pipelines.