dbt relationships テストで参照整合性を検証する

dbt の relationships テストは、ファクト側の外部キー列にある値が、ディメンション側の主キー列にも必ず存在することを検証します。これは DWH 上で物理的な外部キー制約を張らない設計でも、論理的な参照整合性を保つための重要な仕組みです。

本稿では、定義方法、実務での使い分け、性能・運用の勘所、そして Analytics Engineer 試験で問われやすいポイントを、公式ドキュメントの挙動に基づいて解説します。

relationships テストの基本と失敗パターン

relationships は、子テーブルの列値が親テーブルの列に存在するかを検証します。子側の NULL は既定で無視され、違反は「子に値があるが親に存在しない」ケースのみ検出されます。

dbt はテスト用のクエリを生成して差分だけを返すため、テストは read-only であり、DWH に物理的な制約を作成しません。

• 目的: 外部キー相当の参照整合性を論理テストで担保する
• 対象: 子テーブルの特定カラム → 親テーブルの主キー（または候補キー）
• NULL の扱い: 子側の NULL は違反に含めない（一般的に意図された仕様）

基本関係のイメージ

最小構成の schema.yml 例

version: 2

models:
  - name: fact_orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: id

定義パターンとフィルタリングの実務知見

テスト対象を期間や状態で絞りたい場合、generic テストの where 設定が有効です。実行環境ごとに条件を切り替えることで、開発環境の小さなサンプルにも本番相当の整合性検証を適用できます。

複合キーの参照整合性は relationships 単体では直接サポートされません。その場合は、親子双方で連結キーを作るか、単一の singular テスト(SQL)で NOT EXISTS を書くのが実務的です。

• where でテスト対象を限定（例: 直近90日、キャンセル除外）
• 複合キーは surrogate key で単一化、または singular テストで対応
• 列リネーム時は field 側の追随漏れに注意

where で子集合を絞る流れ

where を併用した relationships

version: 2

models:
  - name: fact_orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: id
              where: "order_date >= dateadd(day, -90, current_date) AND order_status <> 'CANCELLED'"

性能最適化と DWH 別の勘所

relationships は内部的に NOT EXISTS や LEFT ANTI JOIN 相当のクエリになります。大規模データでの高速化には、結合キー列の物理設計と絞り込みが有効です。

Snowflake ではクラスタリングキー、Databricks では Z-Ordering など、基盤ごとの最適化を組み合わせてテスト時間を短縮します。

• 親子の結合キー列に統計・クラスタリングを設定
• where で期間や論理削減を実施
• 親側の主キー重複は先に unique テストで検知し、誤検出を防ぐ

テスト時の論理計画イメージ（抽象化）

複合キーを singular テストで検証する SQL 例

-- tests/relationships_orders_customers.sql
-- 子: fact_orders(order_id, customer_id)
-- 親: dim_customers(customer_id, tenant_id)

select f.order_id, f.customer_id, f.tenant_id
from {{ ref('fact_orders') }} f
where f.customer_id is not null
  and not exists (
    select 1
    from {{ ref('dim_customers') }} d
    where d.customer_id = f.customer_id
      and d.tenant_id = f.tenant_id
  )

失敗の扱い・保存と運用フロー

失敗行の調査には store_failures が便利です。テスト実行時に違反レコードを専用スキーマに永続化でき、再現・共有が容易になります。

重大度は severity で制御します。開発では warn、本番パイプラインのゲートでは error とする運用が現実的です。

• store_failures: true で失敗行を永続化
• severity を環境別に切り替えて段階的なゲートを構築
• dbt build でモデルとテストを一体実行し、CI に組み込む

失敗レコードの運用フロー

環境別に重大度と失敗保存を切り替える例

# dbt_project.yml の tests デフォルト設定例
tests:
  +severity: "{{ target.name == 'prod' and 'error' or 'warn' }}"
  +store_failures: "{{ target.name == 'prod' }}"

試験対策と実務チェックリスト

Analytics Engineer 試験では、relationships の目的、NULL の扱い、unique/not_null との役割分担、そして singular テストとの使い分けが頻出です。

実務では、親のキー健全性を unique + not_null で先に担保し、その上で relationships を流す順序が安定します。

• 親: unique + not_null、子: relationships の順でテスト
• 複合キーなら surrogate key か singular SQL
• テストは制約ではなくクエリである点を明確化（DWH に外部キーは作られない）

モデル層とテストの配置イメージ

サロゲートキーで relationships を単一列に寄せる例

-- 親・子双方で同じロジックのサロゲートキーを作成
-- parent_surrogate_key = md5(coalesce(customer_id,'') || '|' || coalesce(tenant_id,''))

-- schema.yml 抜粋
models:
  - name: fact_orders
    columns:
      - name: customer_sk
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_sk

問題で確認

よくある質問