where filter: subset testing

dbt-tests applied к whole model by default. На huge tables (millions of rows) это дорого. where config позволяет ограничить test subset rows — performance, focused testing, conditional tests.

В этом уроке — practical patterns where filter.

Базовое использование

columns:
  - name: customer_email
    data_tests:
      - not_null:
          config:
            where: "order_date >= current_date - 30"

Test runs SELECT * FROM model WHERE customer_email IS NULL AND order_date >= current_date - 30.

Только последний месяц tested. Historical NULLs ignored (могут быть legacy data quality issues).

Performance optimization

- unique:
    config:
      where: "created_at >= current_date - 7"  # last week only
      tags: ['ci_fast']

Last week — fast. Full table test раз в weekly schedule.

# CI (fast)
dbt test --select tag:ci_fast

# Weekly (slow, comprehensive)
dbt test --select tag:weekly_full

Tradeoff: faster CI vs less coverage. Acceptable если recent data — what matters в production.

Recent data focus

Most data quality issues occur с new data. Old data (validated long ago) — likely stable.

- not_null:
    config:
      where: "order_date >= current_date - 30"
      severity: error
- not_null:
    config:
      where: "order_date < current_date - 30"
      severity: warn  # historical — relaxed

Two tests:

• Recent (30 days): error severity, block CI.
• Historical: warn, don’t block.

Layered quality management.

Exclude PII / sensitive

- expect_column_values_to_match_regex:
    regex: '^[\\w._%+-]+@[\\w.-]+\\.[A-Za-z]{2,}$'
    config:
      where: "is_test_account = false"  # exclude test accounts
      severity: error

Test accounts могут have malformed emails (e.g., ‘test@’, ‘user@localhost’). Exclude из validation чтобы не fail legitimately tests.

- not_null:
    config:
      where: "deleted_at IS NULL"  # exclude soft-deleted rows

Soft-deleted records — don’t care about their data quality.

Conditional tests

- expect_column_mean_to_be_between:
    column_name: amount
    min_value: 100
    max_value: 500
    config:
      where: "customer_country = 'US'"  # US-specific expectations

Different expectations per segment:

- expect_column_mean_to_be_between:
    column_name: amount
    min_value: 100
    max_value: 500
    config:
      where: "customer_country = 'US'"

- expect_column_mean_to_be_between:
    column_name: amount
    min_value: 50
    max_value: 200
    config:
      where: "customer_country = 'IN'"

US: $100-500 expected. India:$50-200. Multiple tests с different conditions.

Complex where conditions

- not_null:
    config:
      where: |
        order_date >= current_date - 30
        AND status != 'cancelled'
        AND customer_country = 'US'

Multiline. Complex filters. Same SQL syntax as model SELECT WHERE.

where с Jinja

- not_null:
    config:
      where: "order_date >= '{{ var('test_start_date', '2026-01-01') }}'"

Variable-driven:

dbt test --vars 'test_start_date: 2026-05-01'

Or target-conditional:

- not_null:
    config:
      where: "{{ \"order_date >= current_date - 7\" if target.name == 'ci' else \"1=1\" }}"

CI: subset. Prod: full table.

tags + where combination

- unique:
    config:
      where: "order_date >= current_date - 30"
      tags: ['recent']
- unique:
    config:
      tags: ['full']  # no where

dbt test --select tag:recent  # fast PR check
dbt test --select tag:full   # full coverage daily

Multi-level testing strategy.

Common production patterns

Pattern 1: Recent-only PR checks

- unique:
    config:
      where: "order_date >= current_date - 7"
      tags: ['pr_check']
- not_null:
    config:
      where: "order_date >= current_date - 7"
      tags: ['pr_check']

# PR CI
dbt test --select tag:pr_check  # fast

PRs check last 7 days. Recent issues caught quickly. Historical — separate suite.

Pattern 2: Per-environment subsets

- not_null:
    config:
      where: "{{ \"order_date >= current_date - 30\" if target.name == 'dev' else \"1=1\" }}"

Dev: 30 days subset (fast iteration). Prod: all data.

Pattern 3: Hot-data vs cold-data

- unique:
    config:
      where: "order_date >= current_date - 30"
      tags: ['hot_data']
      severity: error
- unique:
    config:
      where: "order_date < current_date - 30"
      tags: ['cold_data']
      severity: warn

Hot data critical. Cold data relaxed (historical, less actionable).

Pattern 4: Sample testing

- unique:
    config:
      where: "MOD(customer_id, 10) = 0"  # 10% sample
      tags: ['sample']

10% sample for fast tests. Full coverage в weekly.

Pattern 5: Tier-specific

- premium_revenue_threshold:
    config:
      where: "tier = 'premium'"
- standard_revenue_threshold:
    config:
      where: "tier = 'standard'"

Different rules per tier. Cleaner than one mega-test со логикой.

Gotchas

1. where с NULL

- unique:
    config:
      where: "customer_email IS NOT NULL"

This excludes NULL emails from uniqueness test. Если NULL emails are duplicates — not caught.

Be intentional. Add separate not_null test:

- not_null:
- unique:
    config:
      where: "customer_email IS NOT NULL"  # composite logic

Both tests cover full picture.

2. where on derived columns

- unique:
    config:
      where: "DATE(created_at) >= current_date - 30"

DATE() function in WHERE — runtime computation. May not use index. Slower.

Better: compare to TIMESTAMP:

- unique:
    config:
      where: "created_at >= (current_date - 30)::timestamp"

Or pre-compute в model:

-- В модели
SELECT *, DATE(created_at) AS created_date FROM ...

- unique:
    config:
      where: "created_date >= current_date - 30"

Pre-computed column — faster WHERE.

3. where не applies к all tests

- relationships:
    to: ref('dim_customers')
    field: customer_id
    config:
      where: "order_date >= current_date - 30"

Test relationships subset. But what about FK violations в older orders? Not tested.

Decide:

• Recent FK is what matters (real-time integrity).
• Or historical FK too (full coverage).

Layered approach: where=recent (error) + no where (warn) для full coverage.

4. where в singular tests

Singular tests (custom SQL в tests/ directory) don’t get auto where. Add manually:

-- tests/orders_amount_consistency.sql
SELECT 1 WHERE 1=0  -- placeholder
-- where filtering done в test SQL itself, not via config

For generic tests с where config — works. For singular — manual.

Performance impact

where filter helps when:

• Table is large (10M+ rows).
• Recent subset much smaller (10K rows last week vs 10M total).
• Index on filtered column (e.g., order_date).

Doesn’t help когда:

• Table small (менее 100K rows).
• Filtered column not indexed.
• Most rows pass filter (no real subset reduction).

Measure: EXPLAIN test query to verify pruning.

DuckDB specifics

• DuckDB optimizer handles WHERE well, often partition pruning if data clustered.
• For huge tables -> make sure column is sorted / clustered.

# dbt_project.yml
models:
  +cluster_by: ['order_date']

Some adapters (Snowflake, BigQuery) support clustering. DuckDB has automatic optimization based on access patterns.

Попробуй сам

В labs:

1. Add where к test: where: “order_date не меньше current_date - 30”. Run test, измените where, observe what changes в compiled SQL.
2. Create dual tests: recent (error) + historical (warn).
3. Compare run times: with vs without where on large model.
4. Add target-conditional: dev subset, prod full.
5. Sample test: where MOD(id, 100) = 0 — 1% sample, fast.

Ключевые выводы

1. where config ограничивает test subset rows. Performance + focused.
2. Recent-only для PR CI fast (last 7-30 days).
3. Tier-specific tests с where per segment.
4. Per-environment: dev subset, prod full.
5. Tags + where для multi-level testing strategy.
6. Layered: recent error + historical warn для balanced approach.
7. Gotchas: NULL exclusion может miss issues, derived columns slow, не all tests support where same way.
8. Performance: where helps на large tables с indexed filter columns.