Singular tests: один тест — одна SQL-проверка

В прошлом модуле мы познакомились с четырьмя встроенными generic-тестами: unique, not_null, accepted_values, relationships. Они декларативны и применяются к колонкам через YAML. Но что делать, если нужна бизнес-проверка, которую нельзя выразить через четыре стандартных шаблона? Например: «сумма всех payment.amount по конкретному заказу должна совпадать с orders.total», или «ни один customer не может иметь больше одного active = true договора». Здесь начинается территория singular tests.

Singular test — это просто SQL-запрос, который кладётся в каталог tests/ отдельным .sql-файлом. Философия максимально проста: запрос возвращает строки — тест провален. Сколько строк — столько failing records. Пустой результат означает «всё ок». Это та же модель «зелёный SELECT = тест прошёл», которой пользуются Great Expectations, Soda и любой инженер, который когда-либо писал ad-hoc QA-запрос в Jupyter.

Где живут singular tests

В корне dbt-проекта по умолчанию есть директория tests/. Внутри неё — .sql-файлы. Структура полностью на ваше усмотрение, dbt сканирует её рекурсивно:

my_project/
├── models/
│   ├── staging/
│   └── marts/
├── tests/
│   ├── assert_orders_total_equals_payment_sum.sql
│   ├── assert_one_active_contract_per_customer.sql
│   └── business_logic/
│       └── assert_revenue_positive.sql
└── dbt_project.yml

Имя файла = имя теста. Соглашение assert_* пришло из community best practices и помогает читать имена тестов в логе dbt test. Не обязательно, но рекомендуется.

Анатомия singular test

Возьмём конкретный пример. У нас есть модели stg_jaffle__orders и stg_jaffle__payments. Каждый заказ имеет total_amount, каждый платёж — amount. Бизнес-инвариант: сумма всех платежей по заказу должна совпадать с total_amount заказа.

Пишем файл tests/assert_orders_match_payment_sum.sql:

-- tests/assert_orders_match_payment_sum.sql
-- Возвращает заказы, у которых сумма платежей не совпадает с total_amount

with order_payments as (
    select
        order_id,
        sum(amount) as paid_amount
    from {{ ref('stg_jaffle__payments') }}
    where status = 'success'
    group by order_id
),

mismatched as (
    select
        o.order_id,
        o.total_amount,
        coalesce(p.paid_amount, 0) as paid_amount,
        o.total_amount - coalesce(p.paid_amount, 0) as diff
    from {{ ref('stg_jaffle__orders') }} o
    left join order_payments p on o.order_id = p.order_id
    where abs(o.total_amount - coalesce(p.paid_amount, 0)) > 0.01
)

select * from mismatched

Запускаем:

$ dbt test --select assert_orders_match_payment_sum
13:42:15  Running with dbt=1.10.2
13:42:16  1 of 1 START test assert_orders_match_payment_sum ............ [RUN]
13:42:18  1 of 1 PASS assert_orders_match_payment_sum .................. [PASS in 1.92s]
13:42:18  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

PASS значит, что запрос вернул 0 строк. Если бы вернул, например, 3 строки — было бы:

13:42:18  1 of 1 FAIL 3 assert_orders_match_payment_sum ................ [FAIL 3 in 1.94s]

Цифра 3 — количество failing rows. Это главный сигнал singular test: не «прошёл/упал», а сколько проблемных строк вы поймали.

Как dbt технически это запускает

Заглянуть в скомпилированную версию своего теста можно так:

$ dbt compile --select assert_orders_match_payment_sum
$ cat target/compiled/jaffle_shop/tests/assert_orders_match_payment_sum.sql

Там будет ваш SQL с подставленными именами таблиц вместо {{ ref() }}. Очень полезно для отладки: можно скопировать в DuckDB CLI и запустить руками, чтобы увидеть конкретные failing rows.

store_failures для singular test

Так же, как и для generic test, у singular можно включить store_failures через config-блок прямо в SQL-файле:

-- tests/assert_orders_match_payment_sum.sql
{{ config(store_failures=true, severity='warn') }}

with order_payments as (
    ...
)

select * from mismatched

После запуска failing rows материализуются в схему dbt_test__audit (имя настраивается). Можно открыть в DuckDB и посмотреть конкретные проблемные заказы:

-- Из DuckDB CLI
SELECT * FROM dbt_test__audit.assert_orders_match_payment_sum LIMIT 10;

Это существенно ускоряет дебаг бизнес-багов: вместо «3 заказа сломаны где-то в данных» вы видите конкретные order_id.

severity: error vs warn

Параметр severity контролирует, как dbt реагирует на ненулевой результат:

Пример:

{{ config(severity='warn', warn_if='>= 1', error_if='>= 100') }}

select ...
from ...
where ...

warn_if и error_if принимают SQL-expressions, по которым dbt сравнивает количество failing rows. Это даёт интересный паттерн: до 99 проблемных строк — варнинг, 100+ — ошибка. Полезно, когда нулевая толерантность недостижима, но рост проблем должен будить алерты.

Singular vs Generic: когда что

Singular tests — одноразовая бизнес-проверка. Generic tests — переиспользуемое правило. Если вы пишете singular, а потом ловите себя на том, что повторяете его логику с заменой имён таблиц/колонок — настало время превратить его в generic. Этому посвящён следующий урок.

Конкретные примеры:

Хорошие практики

1. Один тест — одна проверка. Если в singular test вы проверяете три вещи через UNION, при FAIL не понятно, что именно сломалось. Лучше три отдельных файла.

2. Имя должно отвечать на вопрос «что проверяем». assert_orders_match_payment_sum лучше, чем test_orders_2. Чтение лога dbt test превращается в чтение бизнес-правил.

3. Комментарий в начале файла. Что инвариант проверяет, почему он важен, откуда требование. Через год вы (или коллега) скажете спасибо.

4. Используйте {{ ref() }} и {{ source() }}. Не хардкодьте имена таблиц. Иначе тест не попадает в DAG и при rename модели сломается тихо.

5. Тесты идемпотентны. Запуск 10 раз подряд должен давать одинаковый результат. Никаких current_timestamp вместо параметризованной даты.

Попробуй сам

Допустим, у вас есть модели marts.orders и marts.refunds. Бизнес-инвариант: сумма refund по заказу не может превышать order.total_amount. Напишите singular test assert_refund_not_exceeding_order_total.sql, который возвращает заказы, где это нарушено.

Подсказка к скелету:

-- tests/assert_refund_not_exceeding_order_total.sql

with refunded as (
    select order_id, sum(refund_amount) as total_refund
    from {{ ref('marts__refunds') }}
    group by order_id
)

select
    o.order_id,
    o.total_amount,
    r.total_refund
from {{ ref('marts__orders') }} o
inner join refunded r on o.order_id = r.order_id
where r.total_refund > o.total_amount

Запустите dbt test --select assert_refund_not_exceeding_order_total. Затем намеренно сломайте данные (например, через INSERT в seed-таблицу) и убедитесь, что тест поймал.

Итоги

• Singular tests — SQL-файлы в tests/, философия «вернул строки = упал».
• Имя файла = имя теста, конвенция assert_* помогает читать лог.
• severity='error' (default) и warn, плюс пороги warn_if/error_if.
• store_failures=true материализует failing rows в dbt_test__audit для отладки.
• Используйте singular для уникальных бизнес-инвариантов между двумя-тремя моделями.
• Используйте {{ ref() }} и {{ source() }} — тест попадает в DAG.

В следующем уроке мы научимся превращать повторяющуюся проверку в custom generic test — переиспользуемое правило, которое подключается через YAML к любой колонке любой модели.