dbt incremental の merge 戦略で実現する堅牢な upsert パターン

incremental + merge は、DWH 上での標準的な upsert（更新＋挿入）パターンを実現するための dbt の中核機能です。

本稿では、unique_key 設計、重複排除、ウォーターマークの安全な取り方、パフォーマンスとスキーマ変更対応まで、試験で問われやすい論点と実務の勘所をまとめます。

merge 戦略の全体像と適用場面

dbt の incremental モデルで incremental_strategy=merge を使うと、ターゲット表に対し「キー一致時は更新、未一致時は挿入」という upsert が実行されます。これによりフルリロードを避け、到着データのみを効率的に反映できます。

merge を成立させる前提は一意なキー（unique_key）の適切な指定と、増分対象の正しい抽出です。試験観点では、unique_key が未設定または重複時のリスク（不定更新・MERGE エラー）を説明できることが重要です。

• unique_key は論理的に一意であること。重複は前段で必ず排除する。
• is_incremental() を用いて取り込み対象を絞るのが基本。ただし遅延到着に備えたルックバックが実務では有効。
• full_refresh 時はテーブル再作成。インデックス/クラスタ設定はモデル側の config に明示。

UPSERT フロー（incremental + merge）

最小の merge インクリメンタル設定

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id'
) }}

with src as (
  select * from {{ source('app', 'orders') }}
  {% if is_incremental() %}
    where updated_at >= (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
  {% endif %}
)

select * from src

unique_key 設計と重複排除の実務

merge 成功のカギは unique_key の健全性です。ビジネスキーが揺れる場合はサロゲートキーの導入を検討し、ソースに重複が届く前提で前段での重複排除を徹底します。

試験では「unique_key が重複したら？」に対して、事前のウィンドウ関数による行選択（最新優先など）で 1 レコードに確定させる回答が期待されます。

• ビジネスキーが変化しうる場合は安定キー（サロゲート）を導入する。
• 同一キーの複数候補は、更新時刻やバージョンで決定的に 1 行に絞る。
• null キーは unique とみなせないため、not_null テストを必ず付与する。

重複排除の流れ

ウィンドウ関数で重複を 1 行に確定

with staged as (
  select *,
         row_number() over (
           partition by id
           order by updated_at desc
         ) as rn
  from {{ source('app', 'orders') }}
), dedup as (
  select * from staged where rn = 1
)
select * from dedup

実践的 upsert: ルックバック付きウォーターマーク

遅延到着や時刻の揺れに備えるため、単純な max(updated_at) ではなく、少し巻き戻した閾値で取り込むルックバック方式が安全です。ターゲット側の最大更新時刻から一定期間戻し、その範囲のソースを再評価します。

この方式では一部の重複取込が発生しうるため、前段の重複排除と unique_key の merge が前提になります。

• ルックバック期間はデータ到着 SLA に合わせて数時間〜数日で調整。
• ターゲットの最大時刻が null の初回実行を必ず考慮。
• 時間列は更新駆動（updated_at）を優先。作成時刻のみは避ける。

ルックバックの概念

ルックバック付き incremental + merge の完全例

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id'
) }}

{% set lookback_hours = 24 %}

with base as (
  select * from {{ source('app', 'orders') }}
), window as (
  select coalesce(
           dateadd('hour', -{{ lookback_hours }}, max(updated_at)),
           cast('1970-01-01' as timestamp)
         ) as watermark
  from {{ this }}
), filtered as (
  select b.*
  from base b
  {% if is_incremental() %}
  where b.updated_at >= (select watermark from window)
  {% endif %}
), dedup as (
  select *, row_number() over (partition by id order by updated_at desc) as rn
  from filtered
)
select * from dedup where rn = 1

パーティション/クラスタと増分プルーニング

大規模テーブルでは、物理設計（パーティション/クラスタ）と増分時の絞り込みがクエリコストに直結します。dbt の config でアダプタに応じた設定を行い、スキャン範囲を最小化します。

incremental_predicates は、ターゲット/ソース双方に適用される追加述語の指定に対応したアダプタで有効です。サポート状況は利用アダプタのドキュメントを確認してください。

• 時間列での partition_by（サポートする DWH のみ）を活用する。
• クラスタ/ソートキーでマージの一致探索を高速化。
• 増分述語は過度に複雑にせず、統計に効く列を選ぶ。

プルーニングのイメージ

最適化の例（アダプタ依存の一例）

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id',
    partition_by={'field': 'order_date'},          -- e.g. BigQuery/Spark family
    cluster_by=['id'],                              -- e.g. BigQuery/Snowflake clustering equivalents
    incremental_predicates=["order_date >= dateadd('day', -7, current_date)"]
) }}

select * from {{ ref('stg_orders_clean') }}
{% if is_incremental() %}
where order_date >= dateadd('day', -7, current_date)
{% endif %}

スキーマ変更と削除の扱い（on_schema_change とソフトデリート）

増分モデルではスキーマ変更時の挙動を on_schema_change で制御します。append_new_columns は新規列を追加、fail は不整合時に失敗、sync_all_columns は対応アダプタで列差分を同期します。

実データの削除対応は、merge の標準挙動だけでは網羅できない場合があります。実務では is_deleted フラグでのソフトデリートが安全で、履歴の整合性も保ちやすいです。ハードデリートを行う場合は、影響範囲の正確な同定とテストを必須にしてください。

• on_schema_change は adapter のサポートに依存。試験では意味と使い所を説明できること。
• 削除はソフトデリート優先。ハードデリートは監査要件と整合させる。
• スキーマ変更後は full_refresh のオプションも検討。

ソフトデリートの流れ

ソフトデリートを含む upsert ロジック（フラグ反映）

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id',
    on_schema_change='append_new_columns'
) }}

with src as (
  select id,
         updated_at,
         status,
         case when status = 'deleted' then true else false end as is_deleted
  from {{ ref('stg_orders_clean') }}
  {% if is_incremental() %}
    where updated_at >= (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
  {% endif %}
)

select * from src

資格対策チェックリスト（Analytics Engineer 向け）

試験では、incremental の前提（unique_key、is_incremental の役割、full_refresh の意味）と、merge を選ぶべき状況の判断が頻出です。on_schema_change の選択肢、重複排除の書き方、ウォーターマークの安全策まで説明できるように準備しましょう。

テスト（not_null/unique・関数テスト）をモデルに付与し、データ品質を担保するのも評価対象になりやすいポイントです。

• unique_key は必須。重複排除はモデル内で確定的に行う。
• is_incremental() で取り込み範囲を制御、遅延到着にはルックバック。
• on_schema_change の各モードと使い分けを言語化できること。

試験イメージ

品質テストの最小例（schema.yml 抜粋）

version: 2
models:
  - name: dim_orders
    columns:
      - name: id
        tests:
          - not_null
          - unique
      - name: updated_at
        tests:
          - not_null

問題で確認

よくある質問