dbt における unique_key の設計: 重複排除と主キー管理

unique_key は dbt の増分モデルで更新同一性を担保する要の設定です。ソース由来の重複を安全に排除しつつ、正しく MERGE（行差分更新）するための設計を丁寧に詰める必要があります。

本稿では、試験（dbt Analytics Engineer）で問われやすい概念と、現場での落とし穴を避けるための実務的ガイドラインを、安定した公式仕様に基づいてまとめます。

unique_key の基本と dbt での意味づけ

dbt の unique_key は、主に増分モデルでターゲット表の既存行と新規行を突き合わせるための「同一性カラム（群）」を指します。データベース上の主キー制約そのものを作るわけではなく、増分ロード時の MERGE 条件として機能します。

unique_key は行の一意性を保証するために使うため、自然キー、サロゲートキー、複合キーのいずれでも指定可能です。重要なのは、同一性の定義が業務上ブレず、かつ更新時のタイブレーク（どの行を最新とみなすか）が決められていることです。

dbt の増分モデルで incremental_strategy=merge を使う場合、unique_key の適切な指定が前提になります。これにより、再取り込みや訂正データの反映が idempotent（同じ結果に収束）になります。

• unique_key は「マージキー」であり、必ずしもデータベース制約を伴わない
• 再送・訂正・CDC に強いモデルにするには、unique_key とタイブレーク列の両輪が必要
• 複合キーも指定可能。カーディナリティとヌル許容を事前に精査する

重複排除と増分マージの流れ（概念）

増分モデルで unique_key を使った安全な去重 + MERGE（方言依存度の低い書き方）

{{ config(
  materialized='incremental',
  incremental_strategy='merge',
  unique_key='order_id',            # 複合キーの場合は ['order_id','line_no'] など
  on_schema_change='append_new_columns'
) }}

with src as (
  select * from {{ source('raw', 'orders') }}
  {% if is_incremental() %}
    -- 直近のみを取り込む例。適切な境界列（例: updated_at）を使う
    where updated_at > (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
  {% endif %}
),

-- タイブレーク（最新行の決定）
ranked as (
  select
    *,
    row_number() over (partition by order_id order by updated_at desc) as rn
  from src
),

dedup as (
  select * from ranked where rn = 1
)

select
  order_id,
  customer_id,
  status,
  total_amount,
  updated_at
from dedup;

重複排除はどこで行うか：Staging と Fact の役割分担

重複排除は、一般に staging 層で行うと下流全体の安定性が増します。stg_* で「1ビジネスキー=1行」を保証しておけば、core/fact 層の増分モデルはシンプルに保てます。

ただし、CDC の遅延・再送が頻繁な系では、fact 側でもタイブレークを念押しする二重防御が有効です。stg での去重と、fact の MERGE 双方で order_id などの一意性が矛盾しないことを確認してください。

• stg での去重: 多目的に再利用されるため効果が高い
• fact 側の追加去重: 最終境界としての頑健性を確保
• タイブレークは決定的で再現性のある列（更新時刻、バージョン、is_deleted 等）を用いる

stg での去重（再利用しやすい共通パターン）

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

with base as (
  select * from {{ source('raw', 'orders') }}
), ranked as (
  select
    *,
    row_number() over (
      partition by order_id
      order by coalesce(updated_at, created_at) desc
    ) as rn
  from base
)
select
  order_id,
  customer_id,
  status,
  total_amount,
  coalesce(updated_at, created_at) as last_modified_at
from ranked
where rn = 1;

増分モデルと unique_key の相互作用（戦略別の注意点）

incremental_strategy=merge は、多くのアダプタで unique_key を必須とし、同一キーの行を更新・挿入します。訂正や遅延到着データに強いのが利点です。

パーティション単位の再作成（例: insert_overwrite）を使う戦略では、unique_key よりも partition_by の設計が要となります。ビッグテーブルで日次再作成が現実的な場合に有効です。

append 戦略は単純追記で高速ですが、重複制御や訂正反映を自力で行う必要があります。去重の SELECT を確実にしないと重複が残ります。

• merge: 訂正/再送に強い。unique_key が論理的に安定していることが前提
• insert_overwrite: パーティション設計と上流の境界抽出が肝
• append: 高速だが重複リスクが上がる。真に append-only な場合に限定

戦略別の代表例（BigQuery 例含む）

-- 1) MERGE 戦略（多くのアダプタで一般的）
{{ config(materialized='incremental', incremental_strategy='merge', unique_key=['order_id','line_no']) }}
select * from {{ ref('stg_orders_dedup') }}

-- 2) パーティション単位の再作成（BigQuery の insert_overwrite）
--   パーティションキーは日付/時刻列など安定列を指定
--   MERGE ではないため unique_key は不要
{{ config(
  materialized='incremental',
  incremental_strategy='insert_overwrite',
  partition_by={'field': 'order_date', 'data_type': 'date'}
) }}
select * from {{ ref('stg_orders_dedup') }}
where order_date >= date_sub(current_date(), interval 7 day);

主キー管理：自然キー・サロゲートキー・複合キーの使い分け

自然キーは業務上のキー（例: order_id）をそのまま使う方式で、可読性が高い一方、運用上の再利用や再採番変更に弱い場合があります。

サロゲートキーは安定した技術的キー（ハッシュや連番）で、複数列の組合せや NULL の扱いに強いのが利点です。変化に強いモデルを作りたいときに有効です。

複合キーは複数列で一意性を担保します。dbt の unique_key は配列指定で対応可能です。NULL の混入や列の将来変更に注意し、テストで堅く守るのが現実的です。

• 自然キー: そのまま使えるが、上流変更に影響を受けやすい
• サロゲートキー: 生成コストはあるが堅牢。履歴管理との相性が良い
• 複合キー: 現実的な妥協点。dbt_utils でハッシュ生成が簡便

dbt_utils を使ったサロゲートキー生成（複合キー対応）

-- packages.yml で dbt-labs/dbt_utils を追加しておく
-- モデル内での利用例
select
  {{ dbt_utils.generate_surrogate_key([
    'order_id',
    "cast(line_no as string)",
    "coalesce(customer_id, 'UNKNOWN')"
  ]) }} as sk_order_line,
  *
from {{ ref('stg_orders_dedup') }};

品質管理：dbt テストと（必要に応じた）制約の併用

dbt のスキーマテストで not_null と unique を張るのが基本です。複合一意は dbt_utils.unique_combination_of_columns を使うと堅牢です。

一部のデータウェアハウスでは、主キー・一意制約が情報的で実行時に強制されない場合があります。そのため、ビルド時の dbt テストで実効性を担保するのが安全策です。必要に応じてクエリベースの監視（異常値検出）も併用してください。

大規模テーブルでは、全表スキャンのテストが重くなる場合があります。増分テストや、日次パーティション限定の検査など、運用パターンを工夫しましょう。

• not_null + unique は最小セット。複合はユーティリティで対応
• 制約は環境依存。強制されない場合はテストで補完
• 重いテストはスケジュール分離やサンプリングで負荷分散

schema.yml の例（単一・複合一意のテスト）

version: 2
models:
  - name: fct_orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: ['order_id', 'line_no']
    columns:
      - name: order_id
        tests:
          - not_null
      - name: line_no
        tests:
          - not_null
      - name: sk_order_line
        tests:
          - unique

設計パターンと落とし穴：遅延到着・CDC・スナップショット

遅延到着や再送がある系では、去重のタイブレーク列（updated_at など）を必ず持ち、増分モデルでは MERGE を選択します。is_deleted フラグや version 列がある場合は、それらもタイブレークの材料になります。

CDC では、キーが重複しても after イメージが最後に来るとは限りません。row_number の order 基準を厳密にし、必要ならば same-key で最大 version を取るか、op_ts（操作時刻）を使います。

dbt スナップショットは、モデルの unique_key とは別に、スナップショット自身の unique_key 設計が必要です。SCD Type 2 的な履歴を作る場合、ビジネスキーで一意にしつつ、変更検出（check_cols など）を正しく指定します。

• タイブレークは決定的順序（updated_at, op_ts, version, is_deleted）を明示
• CDC は順序保証が弱い前提で設計する
• スナップショットの unique_key は履歴の軸。モデルの unique_key と混同しない

dbt スナップショットの例（SCD2）

{% snapshot dim_customer_scd %}
{{ config(
  target_schema='snapshots',
  unique_key='customer_id',
  strategy='check',
  check_cols=['name','email','status']
) }}

select * from {{ ref('stg_customers_dedup') }}

{% endsnapshot %}

問題で確認

よくある質問