質問とシナリオ
日次顧客スナップショットテーブルであるstaging_customerが、customer_dimへとマージされます。あるバッチ内で、同じcustomer_idが2回出現しました（1回はCRMから、もう1回は手動修正から）。ターゲットには重複キーが存在しないにもかかわらず、ステートメントはカーディナリティ違反（cardinality violation）を発生させます。PostgreSQL 18がどのように変更候補行を形成するのか、なぜ1つのターゲット行が別のソース行によって再度変更されないのか、そしてRETURNING merge_action()がどのように監査可能な結果を生成できるのかを説明してください。
面接官が見ているポイント
- ソース行の重複、ターゲットキーの重複、および広すぎる
ON条件の区別。 MERGEが各変更候補行に対して最初に一致したWHENブランチのみを実行することの説明。- 重複排除、バッチの拒否、最新の修正の維持の間で合理的な選択を行うこと。
- トランザクションログとして扱うことなく、行レベルの監査証跡として
RETURNINGを使用すること。 - 同時実行性、再実行、権限、およびデータ品質アラートの処理。
回答前の確認質問
customer_idはビジネスキーですか、それともテナントと有効日を含めた複合キーを形成する必要がありますか？- 両方のソースレコードに信頼できるバージョン、イベント時刻、または修正優先度がありますか？この回答によって重複排除の方法が決まります。
- 無効なバッチはアトミックに拒否すべきですか、それとも確定した行を先に書き込むことができますか？これはトランザクションとリプレイの設計に影響します。
- 監査には古い値、新しい値、ソースフィールド、アクションが必要ですか、それとも行数だけで十分ですか？
- ソースレコードがバッチをまたいで遅れて到着することはありますか？また、ターゲットは論理削除（ソフトデリート）を許可していますか？
30秒の回答フレームワーク
「まず、ON条件が各ターゲットキーを最大1つのソース行にマッピングしていることを確認します。PostgreSQLのMERGEは変更候補行を構築し、WHENの順序で1行につき1つのアクションを実行します。複数のソース行が1つのターゲット行に到達するとカーディナリティエラーが発生し、トランザクションは失敗します。私ならバージョンやイベント時刻によって決定論的に重複を排除し、競合を解決できない場合はバッチを拒否します。RETURNING merge_action()は挿入、更新、削除を記録し、バッチ制御テーブル、一意性制約、冪等性キーによって安全な再実行を実現します。」
ステップごとの詳細な回答
まず一致するカーディナリティを証明する
ONで使用されているキーと全く同じキーを使用してデータ品質クエリを実行し、複数のソース行を持つターゲットキーを検出します。ソースに単にDISTINCTを適用するだけでは不十分です。同じキーを持つ2つの行が矛盾するファクトを表している可能性があるためです。ビジネスキーが(tenant_id, customer_id)である場合は、MERGEおよび品質クエリの両方で両方の列を使用します。
重複排除を決定論的にする
バージョン番号を優先します。存在しない場合は、イベント時刻、信頼できるソース優先度、および安定したタイブレーカーを使用します。ウィンドウ関数を使用して勝者を1つ選択できます。
WITH ranked AS (
SELECT s.*, row_number() OVER (
PARTITION BY tenant_id, customer_id
ORDER BY version DESC, event_at DESC, source_priority DESC, ingest_id DESC
) AS rn
FROM staging_customer AS s
)
SELECT * FROM ranked WHERE rn = 1;どちらのソースも新しいと証明できない場合は、競合を隔離（quarantine）テーブルに書き込み、バッチを拒否します。データベースが任意の行を選択することに決して依存してはなりません。
WHENブランチと監査出力の設計
WHEN条件は記述順に評価され、最初に一致した（trueとなった）ブランチが実行されます。受信バージョンが高い場合にのみ更新するなど、保護条件を最初に配置し、その後にNOT MATCHEDの挿入や意図的なNOT MATCHED BY SOURCEクリーンアップを処理します。PostgreSQL 18のRETURNINGは、ソース列、古いターゲット値と新しいターゲット値、およびmerge_action()を公開できますが、これはこのステートメントによって変更された行を報告するものであり、バッチ制御テーブルを代替するものではありません。
トランザクションと同時実行性の制御
バッチのMERGE、監査の挿入、およびバッチステータスの更新を1つのトランザクション内に維持します。各入力バッチに冪等性キーを付与し、リプレイ前に成功したバッチを検出します。同時実行についてはデータベースの分離レベル規則に従い、実行時に重複を発見するのではなく、実行前にターゲット行あたり最大1つの候補ソース行を保証します。
高品質な回答例
「このエラーはターゲットキーの重複だけでは説明できません。重要な事実は、ON条件が1つのターゲット行を複数のソース行に関連付けている点です。私なら同じテナントキーと顧客キーを使用してソースのカーディナリティを確認し、バージョン、イベント時刻、ソース優先度、インジェストIDによってランク付けを行います。解決できない競合は隔離され、バッチは失敗します。MERGEは最初に一致したWHENブランチを実行するため、より高いバージョンのガードを先頭に配置します。PostgreSQL 18のRETURNING merge_action()は、挿入、更新、削除に加えて古い値と新しい値を記録します。監査行とバッチ制御行は同じトランザクション内に保持されるため、再実行、アラート、リプレイの証跡が確保されます。」
よくある間違い
- 間違い: ソースに
DISTINCTを適用する → 失敗する理由: 異なるファクトが誤ってマージされる可能性がある → 修正方法: バージョンとビジネス優先度で勝者を定義し、競合を隔離する。 - 間違い:
MERGEがソース行をランダムに選択すると想定する → 失敗する理由: 1つのターゲット行に対する複数の変更はカーディナリティエラーを引き起こす → 修正方法: 実行前に1対1の一致を証明する。 - 間違い:
RETURNINGのカウントをバッチ成功と見なす → 失敗する理由: 変更なし、失敗、およびリプレイの状態が欠落している → 修正方法: 独立したバッチ制御テーブルとトランザクション状態を使用する。 - 間違い: 1つのスレッドでのみテストする → 失敗する理由: 分離レベル、遅延データ、リプレイが検証されない → 修正方法: 同時実行性、再実行、遅延バッチをテストする。
フォローアップ質問と回答
重複したソース行の1つが削除マーカーである場合はどうしますか？
削除と更新を同じバージョン順序に配置し、最新のバージョンのみが勝つようにします。削除に比較可能なバージョンがない場合は、1つのバッチが顧客の状態をサイレントに決定することを許さず、競合を隔離します。
RETURNINGは、何にも一致しなかったソース行を記録できますか？
これはINSERT、UPDATE、またはDELETEの影響を受けた行を返します。ソース側の不一致品質レポートではありません。アンチジョイン統計を個別に実行するか、MERGEを実行する前に候補と意図したアクションを事前監査テーブルに保存してください。
なぜ直接INSERT ... ON CONFLICTを使用しないのですか？
単純な一意キーの挿入または更新であれば、ON CONFLICTの方がシンプルな場合があります。ソースの一致、存在しないソースのクリーンアップ、または複数の条件分岐が必要な場合はMERGEを選択します。それらの同時実行性および権限のセマンティクスが相互に交換可能であると仮定してはなりません。
ターゲットにトリガーがある場合、何が変わりますか？
トリガーが一致キーを変更したり、ステートメント内で同一のターゲット行を新しい候補にしたりしないことを確認してください。監査データ内でMERGEのアクションをトリガーの副作用と区別し、統合環境でロールバックをテストしてください。
参考文献
- PostgreSQL Documentation 18: MERGE
- PostgreSQL Documentation 18: Merge Support Functions
- Greg Low: SQL Interview: 35 T-SQL Merge Statement Clauses
- Simplyblock: PostgreSQL MERGE tutorial
回答のコツ
まずONにおける1対1のカーディナリティを証明し、WHENの順序とカーディナリティ違反について説明した上で、決定論的な重複排除、トランザクション制御、RETURNING merge_action()による監査、および再実行処理について述べてください。
1行のまとめ
強力なMERGEの回答は、ソースのカーディナリティ、ブランチの順序、および監査出力を1つの再現可能なデータコントラクトとして結び付けます。