dbt-audit-helperで実現する移行時の差分検証実務

移行では「同じ結果が出ているか」を定量的に示すことが重要です。dbtパッケージのdbt-audit-helperは、2つのRelationを比較し、件数・欠損・不一致行の要約を返してくれるため、移行の受け入れ基準を自動化しやすくなります。

本稿は、Analytics Engineer向けの資格対策で問われやすい概念（テストと診断の違い、再現性、CI連携）を踏まえ、実務で安全に使える差分検証パターンをまとめています。詳細仕様はdbt公式ドキュメントの安定部分を基に、バージョン差異が影響し得る箇所には注記を添えます。

移行の差分検証になぜdbt-audit-helperか

移行では、従来系テーブル（旧）と新実装のテーブル（新）の間で、主キーごとの行の有無やカラム値の差異、重複や型の差異などを素早く可視化したい場面が多くあります。dbt-audit-helperは、2つのRelationを比較して、件数・一致/不一致・重複などのサマリを返すマクロを提供します。これにより、手書きのフルアウタージョインSQLよりも短時間で再現性の高い検証を組み込めます。

試験観点では「プロダクション品質の品質保証を、宣言的に・CIで再現できるか」が頻出テーマです。dbt-audit-helperをモデル（または診断専用モデル）として組み込めば、dbtのランと共に差分が自動集計され、結果を合否基準として用いることができます。

• 行レベルの差分要約（例: 旧にのみ存在、新にのみ存在、値不一致）が得られる
• 主キーを指定した比較に強く、重複や欠損の検出を簡素化できる
• exclusion（比較から外すカラム）や正規化前処理と組み合わせて、時刻・浮動小数・トラッキング列の揺れを吸収できる

移行時の差分検証フロー（抽象図）

packages.yml（最小）

packages:
  - package: dbt-labs/dbt-audit-helper
    version: ">=0.9.0,<1.0.0"

セットアップと前提条件

基本手順は、packages.ymlにdbt-audit-helperを追加し、dbt depsで取得します。比較対象のRelation（旧/新）に対してSELECT権限があるプロファイルで実行してください。比較は大きなフルアウタージョインになる場合があるため、ウェアハウスのサイズや一時領域（中間結果）のクォータにも注意します。

dbtのベストプラクティスとして、差分のサマリを出す専用モデル（例: audit_orders）を作成し、CIでそのモデルをビルドしてゼロ件であること（もしくは許容閾値以下であること）を合否判定に使うと運用が安定します。マクロの引数名や詳細出力はパッケージのバージョンにより差異があり得るため、利用しているバージョンのドキュメントを確認してください（安定概念は「2 Relationの比較と要約」です）。

• 必要権限: 旧/新RelationへのSELECT、比較結果を格納するスキーマへのCREATE
• 大規模テーブルは日付パーティションやサンプリングで段階的に比較
• exclusion（監査列やロード時刻など）の検討を事前に合意

差分サマリモデルの雛形（audit_orders.sql）

{{ config(materialized='table') }}

-- 旧/新Relationを参照
{% set old_rel = ref('orders_legacy') %}
{% set new_rel = ref('orders') %}

-- 代表的な呼び出し例。利用可能な引数はバージョンに依存します。
-- primary_key: 差分照合に使う主キー（1列または複数列）
-- exclude_columns: 比較から除外する列（例: ロード時刻、追跡列）

{{ audit_helper.compare_relations(
    a_relation=old_rel,
    b_relation=new_rel,
    primary_key=['order_id'],
    exclude_columns=['_ingested_at', '_updated_at']
) }}

よく使う差分検証パターン

移行では完全一致をいきなり狙うより、揺れを制御しながら収束させるのが現実的です。以下のパターンを組み合わせると、安定して差分が減っていきます。

浮動小数の丸め、タイムゾーンの正規化、トリム/大文字小文字の正規化、監査列（ロード時刻・メタデータ）の除外は、どのDWHでも安定的に使える基本手筋です。

• 件数一致チェックから開始し、次に主キー有無の一致、最後にカラム値一致へと粒度を上げる
• 監査列や非決定的列はexclude_columnsで早期に除外する
• 金額や割合は小数丸め（例: 2桁）を前処理でそろえる
• タイムスタンプはタイムゾーンと精度（秒/ミリ秒/マイクロ秒）を合わせる

丸めと正規化を挟んだ比較（前処理モデルを経由）

-- 前処理（例）: 旧/新それぞれで正規化したビューを用意しておき、比較はそのビューを参照
-- models/stg_orders_legacy_norm.sql
select
  order_id,
  upper(trim(customer_name)) as customer_name,
  round(amount, 2) as amount,
  to_timestamp_ntz(order_ts) as order_ts
from {{ ref('orders_legacy') }}

-- models/stg_orders_norm.sql
select
  order_id,
  upper(trim(customer_name)) as customer_name,
  round(amount, 2) as amount,
  to_timestamp_ntz(order_ts) as order_ts
from {{ ref('orders') }}

-- models/audit_orders.sql（比較）
{{ config(materialized='table') }}
{{ audit_helper.compare_relations(
    a_relation=ref('stg_orders_legacy_norm'),
    b_relation=ref('stg_orders_norm'),
    primary_key=['order_id'],
    exclude_columns=[]
) }}

CI/CDへの組み込みと合否ゲート

差分サマリモデルをCIでビルドし、その結果に基づいてパイプラインをゲートします。ゼロ件一致を強制できない場合も、許容閾値（例: 欠損行0、値不一致<=10行）を決め、合否条件を明確化しておくと実務で揉めません。

dbtでは「単一クエリで0行を期待する」シンギュラーテストを作るだけで、任意の合否ロジックを表現できます。差分サマリモデルの結果を参照し、NG条件を返すSELECTを記述します。

• 差分サマリはmodels/audit以下にまとめ、dbt run -s models/auditをCIで実行
• tests/配下にシンギュラーテストを置き、NG条件を返すSELECTを定義
• 許容閾値はvarsや環境変数で分岐（開発と本番で切替）

シンギュラーテスト例（不一致・欠損が0件であること）

-- tests/no_diff_orders.sql
-- audit_orders（サマリ出力）に、差分があると行が出る想定
select *
from {{ ref('audit_orders') }}
where (mismatch_count > 0 or only_in_a > 0 or only_in_b > 0)
-- 上記のSELECTが1行以上返るとテスト失敗（CIでブロック）

# 参考: GitHub Actions（抜粋）
# - run: dbt deps
# - run: dbt run -s models/audit
# - run: dbt test -s tests/no_diff_orders.sql

パフォーマンスとスケール戦略

大規模テーブルの比較は、全面比較ではなく、パーティション単位やサンプリングで段階的に進めるのが現実的です。まず直近N日だけ比較してパイプラインの健全性を担保し、その後バックフィル期間を広げていきます。これによりコストと時間を抑えつつ品質を確保できます。

また、主キーにクラスタリングやソートキーがあるとフルアウタージョインのコストが下がりやすいです。比較前に統計更新やテーブルの最適化（Snowflakeのクラスタリング、DatabricksのOPTIMIZEなど）を検討してください。

• 直近N日ウィンドウの比較から開始し、Nを段階的に拡大
• where句で日付/パーティションを絞った正規化ビューを用意
• 視認のためのサンプル比較（percentile-based sampling）を初期に導入

日付ウィンドウを絞った比較（varsで可変）

{{ config(materialized='table') }}
{% set days = var('compare_days', 7) %}

with legacy as (
  select * from {{ ref('orders_legacy') }}
  where order_date >= dateadd(day, -{{ days }}, current_date)
),
newer as (
  select * from {{ ref('orders') }}
  where order_date >= dateadd(day, -{{ days }}, current_date)
)

select * from (
  {{ audit_helper.compare_relations(
      a_relation=adapter.get_relation(database=target.database, schema=this.schema, identifier='legacy'),
      b_relation=adapter.get_relation(database=target.database, schema=this.schema, identifier='newer'),
      primary_key=['order_id']
  ) }}
)

よくある落とし穴と対処法

浮動小数の丸め誤差、文字列コレーション差、タイムゾーンやサブ秒精度の差は、差分が減らない典型要因です。比較前に正規化し、どうしても揺れる列は明示的に除外します。

主キーが一意でない場合は、重複検知が先です。surrogate key（複数列のハッシュキー）を作ってから比較するのが有効です。dbt-utilsのsurrogate_keyマクロは安定して利用されています。

• 浮動小数はroundで桁数を合わせる（通貨は2桁など）
• タイムスタンプはタイムゾーンをNTZ/UTCに正規化し、精度を揃える
• 主キー未整備ならsurrogate keyを一時的に導入して比較
• 差分の再現手順（クエリ、サンプル行）をモデル内コメントに残す

サロゲートキーと丸めの前処理例

-- サロゲートキー作成（dbt-utils）と丸めの統一
with base as (
  select
    {{ dbt_utils.surrogate_key(['order_id', 'warehouse_id']) }} as sk,
    round(amount, 2) as amount,
    to_timestamp_ntz(order_ts) as order_ts,
    *
  from {{ ref('orders') }}
)
select * from base

問題で確認

よくある質問