dbt Singular Tests徹底ガイド: 個別SQLによる検証で精密なデータ品質を担保する

dbtのテストは大きくgeneric testとsingular testに分かれます。genericがパラメトリックに再利用する型であるのに対し、singularは1つのSQLで“失敗行”を明示的に返す個別検証です。

この記事では、Analytics Engineer試験の出題観点にも触れつつ、singular testの設計・実装・運用の勘所を短時間で押さえます。

singular testの基礎とgeneric testの違い

dbtのsingular testは、tests/配下に置く1つのSQLファイルです。そのSQLが“失敗している行”を返すように書くのがルールで、実行時は0行でパス、1行以上で失敗となります。複雑な結合や条件、時間的整合性など、generic testで表現しづらい要件に最適です。

generic testは再利用可能なマクロベースの検証で、not_nullやuniqueやrelationshipsなどが代表例です。singularは使い捨ての個別ロジックに向き、where句やウィンドウ関数、結合など自由度が高いのが特徴です。

• パス条件: クエリ結果が0行
• 失敗条件: クエリ結果が1行以上
• 配置場所: プロジェクトのtests/配下（任意のサブディレクトリ可）
• 参照: {{ ref() }} や {{ source() }} を使ってモデル・ソースを参照
• 設定: configブロックでseverityやtags、store_failuresなどを指定可能

最小のsingular test例（負の金額が存在しないことを検証）

{{ config(severity='error', tags=['dq','critical']) }}

-- tests/no_negative_amounts.sql
with invalid as (
  select order_id, amount
  from {{ ref('orders') }}
  where amount < 0
)
select * from invalid

ファイル構成と命名、コンパイルのかかり方

singular testはtests/配下に配置します。命名は検証対象と違反内容がわかるように、<対象>__<ルール>.sql のようなスキームが現場で読みやすく、試験でも“tests/配下の*.sqlがテストになる”という理解が重要です。

Jinjaを使ってref/sourceや変数、マクロを呼び出せます。重複するロジックはmacros/に切り出してテストSQLから呼ぶと保守が楽です。

• 命名例: customers__email_unique_conditionally.sql
• 共通ロジックはmacros/へ分離
• DML/DDLは書かない（検証クエリのみ）
• 安定実行のため非決定的関数・現在時刻依存は避ける

構成例（テストとマクロ）

# プロジェクト構成（抜粋）
models/
  mart/
    orders.sql
macros/
  test_helpers.sql
tests/
  orders__no_negative_amounts.sql

-- macros/test_helpers.sql
{% macro flag_negative_amounts(model_name) %}
select order_id, amount
from {{ ref(model_name) }}
where amount < 0
{% endmacro %}

-- tests/orders__no_negative_amounts.sql
{{ config(severity='error', tags=['dq']) }}
{{ flag_negative_amounts('orders') }}

代表ユースケース: 個別SQLでしか書けない検証

条件付き一意性（例: status='active' の行だけ顧客メールが一意）や、期間の重複（SCD2/有効期間のオーバーラップ）、クロスモデルの業務ルール（注文が出荷後にキャンセルされていないか）などは、singular testが得意です。

以下は、有効期間の重複を検出するsingular testの例です。行が返れば重複＝失敗です。

• 条件付き一意性: 条件で絞ったうえで重複キーを検出
• 期間整合性: 有効期間の重複・ギャップ検出（SCD2）
• クロスモデル整合: 事実とディメンションのタイムスライス整合

期間オーバーラップ検出（SCD2の有効期間が重複しないこと）

{{ config(severity='error', tags=['dq','temporal']) }}

with scoped as (
  select surrogate_key, valid_from, valid_to
  from {{ ref('dim_customer_scd2') }}
),
ordered as (
  select
    surrogate_key,
    valid_from,
    coalesce(valid_to, '9999-12-31') as valid_to
  from scoped
),
overlaps as (
  select a.surrogate_key, a.valid_from as a_from, a.valid_to as a_to,
         b.valid_from as b_from, b.valid_to as b_to
  from ordered a
  join ordered b
    on a.surrogate_key = b.surrogate_key
   and a.valid_from < b.valid_to
   and b.valid_from < a.valid_to
   and (a.valid_from, a.valid_to) <> (b.valid_from, b.valid_to)
)
select * from overlaps

実行・セレクタ・結果の扱い

基本の実行は dbt test です。個別に実行するにはセレクタを使います。特にsingular testはファイルパスやタグでの選択が実務的で確実です。失敗行の永続化が必要なら、テストに store_failures: true を設定します。

セレクタ例: path:tests/orders__no_negative_amounts.sql や tag:dq など。CIでは変更影響のあるテストだけを実行するために --select state:modified+ とpathの併用が有効です。

• 個別実行: dbt test -s path:tests/orders__no_negative_amounts.sql
• タグ実行: dbt test -s tag:dq
• 失敗行の永続化: config(store_failures=true)
• 警告で止めない: config(severity='warn')

dbt test 実行フロー（singular）

CLI(db t test)
   |
   v
Compile Jinja -> SQL
   |
   v
Run query (returns failing rows)
   |
   |-- zero rows --> PASS
   |
   `-- >0 rows  --> FAIL
                 |
                 `-- store_failures=true ? persist failing rows : no persist

CLIとconfig例（タグ選択・警告扱い・失敗行の保存）

# 個別ファイルを実行
$ dbt test -s path:tests/orders__no_negative_amounts.sql

# タグで実行
$ dbt test -s tag:dq

# テストファイル先頭の設定例
{{ config(
  tags=['dq','nightly'],
  severity='warn',
  store_failures=true
) }}

ベストプラクティスとパフォーマンス

singular testは自由度が高い分、重いSQLになりがちです。対象を最小限に絞り、必要な列のみを返し、結合条件とインデックス（クラスタリング/ソートキー）を意識します。重複検出はまず集約で候補キーを絞り、サンプルのみ詳細列を返すと軽くなります。

テストは決定的であるべきです。現在時刻やランダム値に依存させず、環境差に敏感な関数・ヒントは極力避けます。

• 列は必要最小限（デバッグ用のキー・理由カラム）だけ返す
• まず集約で絞り込み→詳細取得の2段構えにする
• 大きな事実テーブル同士の結合は避け、ディメンションでガイド
• 非決定的関数・UDF依存を避ける（移植性低下）
• 長時間テストはnightly実行に寄せ、PR時はスコープを絞る

重複検出を軽くするパターン（候補を先に絞る）

{{ config(severity='error', tags=['dq','perf']) }}

with active as (
  select customer_id, email
  from {{ ref('dim_customer') }}
  where status = 'active'
),
candidates as (
  select email
  from active
  group by email
  having count(*) > 1
),
details as (
  select a.customer_id, a.email
  from active a
  join candidates c using(email)
)
select * from details

試験対策チェックリスト（Analytics Engineer向け）

singular testは“失敗行を返すSQL”であること、tests/配下の*.sqlがテストになること、configでseverityやtags、store_failuresを設定できることは頻出ポイントです。genericとの使い分けを言語化できると有利です。

セレクタは path: と tag: を押さえ、ピンポイント実行のオプションを選べるようにしておきましょう。

• singularは0行=パス、>0行=失敗というルール
• tests/配下に置き、{{ ref() }} や {{ source() }} が使える
• config(severity, tags, store_failures)をテスト先頭で宣言できる
• genericで表現困難な複雑ロジックはsingularで書く
• セレクタ: path:tests/... と tag:... を使い分ける

条件付き一意性（activeユーザーのメールは一意）

{{ config(severity='error', tags=['dq','exam']) }}

with active as (
  select user_id, email
  from {{ ref('users') }}
  where is_active = true
),
dups as (
  select email
  from active
  group by email
  having count(*) > 1
)
select a.*
from active a
join dups d using(email)

問題で確認

よくある質問