Severity и thresholds: error_if, warn_if

В junior уроке вы видели basic severity: error (default) или warn. На middle нужна granular control: ‘0-5 violations OK, 6-20 warn, 20+ error’, + per-environment tuning, + tag-based selectors, + incident response.

dbt тестам это даёт через error_if / warn_if thresholds + Jinja в config. Этот урок — production-grade тюнинг, начиная с tag-based selectors и заканчивая incident response workflow.

Recap semantics в одной таблице

Идея graduated response: терпимость к маленькому шуму, alert на средний уровень, block CI на серьёзный.

Tag-based selectors: критично для middle

Это первый production-инструмент, которым пренебрегают junior-команды. Без тегов любой PR прогоняет весь test suite — на крупных проектах это 10-30 минут. С тегами PR-CI проверяет только critical, остальное — на ночных runs.

columns:
  - name: order_id
    data_tests:
      - unique:
          config:
            tags: ['critical', 'pk']
      - not_null:
          config:
            tags: ['critical']
  - name: revenue
    data_tests:
      - dbt_expectations.expect_column_mean_to_be_between:
          min_value: 50
          max_value: 200
          config:
            tags: ['statistical', 'monitoring']
            severity: warn
            warn_if: ">5"

Production-расписание:

Принцип: tag = роль теста, не модель. Тег critical живёт на PK/FK/business-invariants. Тег statistical — на drift-checkах с natural variance. Тег monitoring — non-blocking observability.

Real use cases

Statistical tests (natural noise)

- dbt_expectations.expect_column_mean_to_be_between:
    min_value: 50
    max_value: 200
    config:
      severity: error
      warn_if: ">1"  # any violation = warn
      error_if: ">5"  # 5+ violations = error

Statistical naturally flaky. Tolerance prevents false positives.

Business rule violations

- premium_tier_revenue_threshold:
    min_revenue: 10000
    config:
      severity: error
      warn_if: ">5"  # 5+ premium customers без enough revenue — warn
      error_if: ">50"  # 50+ — real problem

5-50: investigate but not block deploys. 50+: structural problem, block.

Data quality on imperfect sources

columns:
  - name: customer_email
    data_tests:
      - dbt_expectations.expect_column_values_to_match_regex:
          regex: '^[\\w._%+-]+@[\\w.-]+\\.[A-Za-z]{2,}$'
          config:
            severity: warn
            warn_if: ">100"  # 100+ malformed emails — concerning
            error_if: ">1000"  # 1000+ — severe

Some emails always malformed (user input). Tolerance до threshold.

Freshness/recency

- dbt_expectations.expect_table_row_count_to_be_between:
    min_value: 100
    max_value: 50000
    config:
      where: "date = current_date - 1"
      severity: warn
      error_if: ">5"  # 5+ days with bad row count = systematic issue
      warn_if: ">2"

One bad day OK. Five — pattern needs attention.

Threshold operators

Beyond >, support:

config:
  warn_if: ">10"  # default
  error_if: "between 100 and 1000"

Supported operators (depending on dbt version):

- not_null:
    config:
      warn_if: ">0"  # any null — warn
      error_if: ">=10"  # 10+ — error

Severity per environment

Often dev vs prod have different tolerance.

Per-target severity:

- dbt_expectations.expect_column_mean_to_be_between:
    min_value: 50
    max_value: 200
    config:
      severity: "{{ 'warn' if target.name == 'dev' else 'error' }}"

Dev — warn (just observation). Prod — error (block deploys).

Jinja в config — dynamic per-environment.

Per-target thresholds:

- not_null:
    config:
      severity: error
      error_if: "{{ '>1000' if target.name == 'dev' else '>100' }}"
      warn_if: "{{ '>100' if target.name == 'dev' else '>10' }}"

Dev — looser tolerance. Prod — strict.

Or через vars:

- not_null:
    config:
      severity: error
      error_if: ">{{ var('null_threshold', 100) }}"

dbt test --vars 'null_threshold: 1000'  # custom threshold per run

Incident response: severity warn навечно — antipattern

Реальный сценарий: тест на дубликаты упал, кто-то поставил severity: warn чтобы разблокировать deploy. Прошло 6 месяцев. Дубликаты копятся, downstream marts молча неправильные.

Tuning workflow для теста, который упал:

1. Day 0 — тест упал в CI. Не ставить severity: warn! Открыть incident-ticket, оставить CI красным.
2. Day 0-1 — investigation: store_failures: true, найти root cause.
3. Day 1-3 — fix (см. урок 03 этого модуля).
4. Day 3 — CI снова зелёный, тест на severity: error.

Если fix невозможен срочно (зависит от source-команды), есть time-bounded warn pattern:

- unique:
    config:
      severity: "{{ 'warn' if var('temp_warn_unique_orders', false) else 'error' }}"
      meta:
        warn_expires: "2026-06-01"
        ticket: "DATA-1234"
        owner: "data-team@"

При запуске:

dbt test --vars 'temp_warn_unique_orders: true'  # явно временно

Плюс CI gate на warn_expires:

# scripts/check_warn_expirations.py
# Падает если warn_expires в прошлом — заставляет либо fix, либо renew

Без gate severity: warn становится permanent. С gate — заставляет команду либо fix, либо явно renew ticket.

Combining severity with where

- not_null:
    config:
      severity: error
      where: "order_date >= current_date - 30"  # only recent data
      error_if: ">10"

This tests not_null на subset (last 30 days). Если > 10 NULLs в last month — error.

Older NULL не trigger this test (excluded по where). May have separate test для historical data:

- not_null:
    config:
      severity: warn  # historical — only warn
      where: "order_date < current_date - 30"

Two tests with different sensitivity for recent vs historical.

Performance: test subset для CI

- unique:
    config:
      severity: error
      where: "order_date >= current_date - 7"  # only last week — fast PR check
      tags: ['ci_fast']
- unique:
    config:
      severity: error
      tags: ['daily_full']  # all data — slower daily test

PR runs CI fast. Daily comprehensive check separately.

store_failures для debugging

When test fails, failing rows can be saved:

- not_null:
    config:
      severity: error
      store_failures: true

Failed rows stored в audit.dbt_test_failures (or similar schema). Examine after CI:

SELECT * FROM audit.test_results.not_null_customer_email_failures;

Useful для debugging без re-running test. Combined с error_if:

- dbt_expectations.expect_column_values_to_be_between:
    min_value: 0
    max_value: 10000
    config:
      severity: error
      error_if: ">5"
      store_failures: true  # save all failures для investigation

Module 07 has detailed lesson on store_failures.

Production thresholds tuning

How to set thresholds:

Step 1: Run test without threshold (severity warn).

- not_null:
    config:
      severity: warn  # observe

Observe для 2 weeks: 0 failures? 5? 50? 500?

Step 2: Set thresholds based on observation.

If typically 5-15 NULLs (noise):

- not_null:
    config:
      severity: error
      warn_if: ">20"  # above typical noise
      error_if: ">100"  # significant degradation

Step 3: Iterate. Если warns triggering часто -> re-evaluate. Maybe loosen или fix root cause.

Step 4: Tighten over time. As data quality improves, lower thresholds.

# Year 1
error_if: ">100", warn_if: ">20"

# Year 2 (data improved)
error_if: ">50", warn_if: ">10"

# Year 3 (target zero)
error_if: ">0", warn_if: ">0"  # no tolerance

Progressive tightening.

Anti-patterns

1. Severity warn used as ‘skip’

- unique:
    config:
      severity: warn  # don't want to fix this

Warn is для observation / soft monitoring. Не для ‘ignore’. If unique IS critical но not currently working — fix data, не downgrade severity.

2. Thresholds too lax

- not_null:
    config:
      severity: error
      error_if: ">100000"  # 100K NULLs allowed?!

If you tolerate 100K NULLs — what’s the point of test? Either tests is critical -> tight thresholds. Or it’s not -> don’t test.

3. No thresholds в production

- unique:
    config:
      severity: error  # blocks CI on ANY violation

For most tests это correct. But для statistical tests с natural variance — single violation can be acceptable. Tune thresholds.

4. Mixing severity и thresholds illogical

- not_null:
    config:
      severity: warn  # warns by default
      error_if: ">10"  # but errors if > 10?

Confusing. Pick one approach:

• Always warn (no error_if).
• Severity error with thresholds.

Не warn-base + error_if (conflicting).

DuckDB specifics

• Threshold logic works identically.
• Performance: thresholds computed via COUNT() after test query — minimal overhead.
• Store_failures: DuckDB creates schema dbt_test__audit for failures.

Попробуй сам

В labs:

1. Add severity warn + threshold к existing test:

- not_null:
    config:
      severity: warn
      warn_if: ">5"

2. Observe runs: how many failures появляются typically?
3. Tune thresholds based on observation.
4. Add tags: ‘critical’ для critical tests, ‘statistical’ для exploratory.
5. Test selective runs: dbt test --select tag:critical — should run faster чем full suite.

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

1. Severity: error (default) blocks CI; warn logs only.
2. error_if / warn_if — counter-based thresholds для graduated response.
3. Per-environment severity: Jinja conditionals based on target.name.
4. Tags + selectors for selective testing (CI fast, daily full).
5. Combine с where: subset testing с different sensitivity.
6. store_failures: saves failing rows для debugging.
7. Tuning workflow: observe -> set baseline -> tighten over time.
8. Anti-patterns: warn ≠ skip, thresholds too lax, mixed severity logic.