singular tests：ビジネスルールを SQL で断言

Stage 2 — 第4章 | dbt入門カリキュラム 推定学習時間：35〜45分 | 難易度：★★★★☆

この章で学ぶこと

singular test は tests/ 配下の SQL ファイルです。 dbt は build 時にこの SQL を実行し、結果 0 行 → 成功、1 行でも返る → 失敗 という単純な契約で動きます。

前章 の schema test が「列単位の宣言的チェック」なら、singular test は schema test では書きにくいビジネスルール を SQL で断言する層です。 範囲チェック、grain の一意性、seed との被覆——手続き的・横断的 な条件はこちらに回します。

まず dbt の作法を整理し、続けて DS Playground の assert_skill_check_total_score_valid を中心に、プロジェクト内 singular test 群の 読み方・設計意図 を学びます。

この章を終えると、こんなことができるようになります：

• singular test SQL（「失敗行 SELECT」）の 構造 を読める
• スコア範囲・カタログ被覆・latest-state 一意性など ルールの種類 を分類できる
• _tests.yml の description と SQL を セットで 解釈できる
• 新ルール追加時に schema vs singular の 選択基準 を説明できる

singular test の基本形

-- tests/assert_skill_check_total_score_valid.sql
          select
            *
          from {{ ref('stg_supabase__skill_check_results') }}
          where total_score < 0
            or total_score > 100
          

データ品質チェック で手書きする WHERE score > 100 が 常時 CI に載ったイメージです。 singular test は「難しい SQL」ではなく、組織が守りたい断言の一覧 です。

schema test との使い分け

宣言的・列単位 → schema（YAML）
          手続き的・横断   → singular（SQL）
          

accepted_values は 離散値のリスト 向けです。 total_score < 0 OR total_score > 100 のような 連続値の範囲 は singular の方が読みやすく、description も書きやすいです。

assert_skill_check_total_score_valid

_tests.yml の説明（要約）:

スキルチェック総合スコアが 0〜100 の範囲に収まることを確認する。 日次平均スコア mart で異常値がそのまま BI に出ることを防ぐ。

4 行の SQL ですが、BI 上の平均スコアを 説明可能な域 に留めるための最小ガードです。 staging で止める理由は、汚染データが intermediate / mart へ伝播する前 に失敗させるためです。

プロジェクト内の singular test 一覧

DS Playground の analytics_mcp プロジェクトには、用途別に複数の singular test があります。

一覧を眺めると、範囲・grain・被覆・版依存 の 4 カテゴリに分類できます。 新ルール追加時は「どのカテゴリか」を先に決めると、schema に回すべきか singular に回すべきかが見えやすくなります。

版依存ルール — 軸スコアの例

assert_skill_check_axis_scores_valid は genege-v3（6 軸） と data-talent-v1（4 軸） で JSON 構造が異なります。 列単位の accepted_values ではなく SQL + 条件分岐 が必要です。

-- 概念例（簡略）。実ファイルは版ごとに CASE で検証
          select *
          from {{ ref('stg_supabase__skill_check_results') }}
          where
            (normalized_version = 'genege-v3'
              and (legacy_statistics_score < 0 or legacy_statistics_score > 100))
            or
            (normalized_version = 'data-talent-v1'
              and (data_science_score < 0 or data_science_score > 100))
          -- ... 他軸も同様
          

第2章 で学んだ normalized_version とセットです。 staging で 版の意味を明示 し、singular で 版ごとの列セット を検証する——二段構えで semantics を守ります。

grain 断言 — latest state 表

problem_attempts は 再回答で UPDATE される最新状態 です。 sources YAML の description にも latest state であることが書かれており、singular test が ドキュメントと検証を一致 させています。

同一 user × category × section × problem に複数行あると、正答率 mart が 二重計上 されます。

-- assert_problem_attempts_unique_current_state（概念）
          select
            user_id,
            category_slug,
            section_slug,
            problem_id,
            count(*) as row_count
          from {{ ref('stg_supabase__problem_attempts') }}
          group by 1, 2, 3, 4
          having count(*) > 1
          

メタデータと来歴 で学んだ「ドキュメントと検証の一致」が、ここで具体化されています。 第1章 で latest-state 表の読み方を学んだなら、この test は その grain 契約の機械的保証 です。

カタログ被覆 — seed との関係

アプリ DB だけでは「存在すべき全問題」が見えません。 seed practice_problem_catalog との整合を singular test で守ります。

-- assert_problem_attempts_catalog_covered（概念）
          select distinct
            pa.category_slug,
            pa.section_slug,
            pa.problem_id
          from {{ ref('stg_supabase__problem_attempts') }} pa
          left join {{ ref('practice_problem_catalog') }} cat
            on pa.category_slug = cat.category_slug
           and pa.section_slug = cat.section_slug
           and pa.problem_id = cat.problem_id
          where cat.problem_id is null
          

次章 で seed の設計意図を詳述します。

失敗時の読み方（運用）

singular test が CI で落ちたときの読み方は次の 3 ステップです。

1. 失敗 SQL が どの mart 意思決定を守るか を _tests.yml で読む
2. 返却行の列が 修正すべきソース / staging / seed を示す
3. 一時的に test を消さず、定義かソースか seed を直す

品質インシデント の「テストを無効化して黙らせる」アンチパターンと同型です。 singular test の失敗行は、修正すべき場所へのポインタ として読むのが現場の作法です。

体系コラム：カリキュラム上の位置づけ

まとめ

schema test が 列契約の宣言 なら、singular test は 横断ルールの断言 です。 両方そろって初めて、パイプライン全体の品質ゲートが機能します。

関連教材

• 前章: schema tests
• 次章: seeds カタログ
• 関連: 品質インシデント

確認問題

問題 1

singular test が返すべき行数とテスト結果の関係として正しいのはどれですか。

A. 1 行以上で成功
B. 0 行で成功、1 行以上で失敗
C. 常に失敗
D. 行数は関係ない

正解: B

問題 2

数値列の 0〜100 範囲チェックを singular test にした 最も適切な理由 はどれですか。

A. ref() が singular でしか使えない
B. 連続値の範囲条件は accepted_values では表しにくい
C. staging には schema test を書けない
D. YAML ファイルが 1MB 制限がある

正解: B

問題 3

fact 表の外部キーが seed マスタに存在するかを singular test で検証する 主な目的 はどれですか。

A. ユーザーの退会を検知する
B. 参照整合性の崩れ（未知 ID の混入）を CI で早期検知する
C. BI ツールのキャッシュ期限切れを防ぐ
D. dbt の profile 名の typo を直す

正解: B

用語メモ（この章）