dbtのaccepted_valuesテストでカテゴリ値を確実に検証する

カテゴリ列の不正値は、ダッシュボードの指標崩れや下流ETLの例外を招きます。dbtのaccepted_valuesテストは、あらかじめ許容する値の集合を宣言し、それ以外を検出するための汎用テストです。

この記事では、公式ドキュメントに基づく安定的な使い方、落ちやすい罠、他手法との使い分け、CIへの組み込みまでを一気通貫で解説します。試験対策の観点からも、問われやすい比較ポイントを押さえます。

accepted_valuesの基本とユースケース

accepted_valuesテストは、列の値が指定したリストに含まれているかを検証します。典型例は、注文ステータス、顧客区分、国コード、言語コードなどの有限集合をとるカテゴリ列です。列の意味が変わる前に逸脱を検知できるため、スキーマドリフトの早期発見に有効です。

スキーマYAMLに許容集合を宣言するだけで機能し、軽量で高速です。データベース制約が実運用で有効化されていないDWH（例: 外部キーが非強制）でも、dbtテストとしてアプリケーション側で担保できます。

• 対象: 有限集合をとるディメンション的な列（status、plan、channelなど）
• 効果: 逸脱値の早期検出、ステージングでの正規化の漏れ検知
• 運用: 変更時はPRでリストの更新を伴うため、レビューで差分が見える

データ整備とaccepted_valuesの配置

最小構成のaccepted_valuesテスト例（schema.yml）

version: 2
models:
  - name: dim_customer
    description: 顧客ディメンション
    columns:
      - name: status
        description: 顧客の現在ステータス
        tests:
          - accepted_values:
              values: ['active', 'inactive', 'prospect']
              quote: true

schema.ymlでの定義パターンとオプション

列配下でaccepted_valuesを指定すると、その列に対してリスト外の値が失敗として検出されます。数値キーなどはquoteをfalseにしてSQLリテラルとして扱います。欠損を無視したい場合はwhereで条件を絞ります。

テストの重大度はconfigで調整できます。severityをwarnにするとテストは失敗せず警告になります。重大度や一部の細かな閾値設定はdbtのバージョン差分があります。必要に応じてdbt公式ドキュメントを確認してください。

• values: 許容する値の配列（必須）
• quote: 値を引用符でくくるか。数値はfalse、文字列はtrueが無難
• where: 対象行の条件（例: 削除済みを除外、NULLを除外）
• config.severity: warn または error（典型的な使い方）

整数ID、NULL除外、警告運用の例

version: 2
models:
  - name: fct_orders
    columns:
      - name: status_id
        tests:
          - accepted_values:
              values: [1, 2, 3, 4]
              quote: false
              where: "status_id is not null"
              config:
                severity: warn

落ちやすいポイントと回避策

よくある失敗は、前段での整形不足（大文字小文字・空白・別名）により、同義語が別値として扱われるケースです。stagingで正規化（trim、lower）した列に対してaccepted_valuesを当てると安定します。

NULLを許可するかどうかは設計判断です。NULLも許容したい場合はwhereで除外するか、別途not_nullテストを併用して方針を明確にします。

• 文字列の正規化: stagingでlower/trimしてからテスト
• NULLの扱い: 許容ならwhereで除外、非許容ならnot_nullを併用
• 値の追加: PRでvaluesへ追加し、変更意図をレビュー可能にする
• 大量値: 長大なリストはseedやディメンション表を使い、relationshipsに切替

stagingでの正規化とテストの組み合わせ例

-- models/stg_orders.sql
with src as (
  select
    order_id,
    lower(trim(status)) as status_norm,
    *
  from {{ ref('raw_orders') }}
)
select * from src;

# models/stg_orders.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: status_norm
        tests:
          - accepted_values:
              values: ['pending','shipped','canceled']
              quote: true

accepted_valuesと他手法の使い分け

静的な有限集合ならaccepted_valuesが最短距離です。一方、集合が業務マスタで管理される場合は、マスタ表との整合をrelationshipsテストで担保する方が保守性に優れます。DWHのネイティブ制約（CHECK/FOREIGN KEY）は製品により非強制または最適化無視されることがあるため、dbtテストでの担保が現実的です。

試験では、accepted_valuesとrelationshipsの使いどころ、変更時の運用（PRでリスト更新 vs マスタ更新）が問われやすいです。

• 静的・少数のカテゴリ: accepted_values
• 業務マスタ準拠・変更頻度高: relationshipsで参照整合性
• DWH制約: 実環境での強制度とメンテコストを評価

マスタ表と整合を取るrelationshipsテスト例

version: 2
models:
  - name: fct_orders
    columns:
      - name: status
        tests:
          - relationships:
              to: {{ ref('dim_status') }}
              field: status
              to_field: status_code

運用: 失敗行の保管、重大度、CI組み込み

失敗行を後から確認したい場合はstore_failuresを有効化します。専用スキーマに失敗行テーブルが作成され、原因分析が容易になります。重大度は環境ごとに調整し、開発はwarn、本番はerrorとする運用が現実的です。

CIでは、変更に関連するモデルだけをテスト対象に絞ると高速です。タグや状態選択子を組み合わせ、PRでの差分テストを実現します。

• store_failures: trueで失敗行を永続化
• severityは環境に応じて切替（開発warn/本番error）
• 選択子: -s でモデルやタグ、state:modified で差分テスト

dbt_project.ymlでのデフォルトとCLI例

# dbt_project.yml（抜粋）
tests:
  +store_failures: true
  +schema: dbt_test__audit
  +severity: error

# CLI例（CI向け）
# 変更のあったモデルとその下流のみをテスト
$ dbt test -m state:modified+
# 重要度の高いテストだけ
$ dbt test -s tag:critical

試験対策の着眼点とミニ演習

Analytics Engineer試験では、accepted_valuesとrelationshipsの適材適所、whereやquoteの使いどころ、NULLの扱い方が頻出です。問題文中のヒント（マスタ表があるか、列が整数か、NULLを許容するか）を見落とさないことが肝心です。

YAML上の記述位置にも注意します。列配下に書く場合はcolumn_nameは不要です。数値リストはquote: false、文字列はquote: trueが基本線です。

• マスタがある→relationships、ない→accepted_values
• NULL許容→whereで除外、非許容→not_nullと併用
• 整数ID→quote: false、文字列→quote: true

ミニ演習用の正答例

version: 2
models:
  - name: dim_subscription
    columns:
      - name: plan
        tests:
          - accepted_values:
              values: ['free','standard','pro']
              quote: true
              where: "plan is not null"

問題で確認

よくある質問