【SQL】マスタデータの論理削除の仕組みを作る

はじめに
今回の検証内容
使用データと処理後の期待値
想定されるマスタデータ更新とデータ追加のパターン
処理フロー
クエリ
解説
最後に

はじめに

・Snowflakeの環境を使います。
・使用データのNAME列の名前は生成AIで作成しており、実在する人物とは関係ありません。
・データベース名は「TEST」としています。
・ID,名前(NAME),部署名(DEPARTMENT)をユニークキーとします。

今回の検証内容

マスタデータを管理する際に以下2点の処理を実装する。
　・古いマスタデータを論理削除
　・新しいマスタデータを追加

使用データと処理後の期待値

・使用するデータ（スキーマ名.テーブル名）
　　RAWスキーマのテーブル：最新のマスタデータ
　　MSTスキーマのテーブル：既存のマスタデータ

・処理後の期待値（スキーマ名.テーブル名）

想定されるマスタデータ更新とデータ追加のパターン

処理フロー

以下のような流れで処理をしていきます。データ転送の部分は今回は割愛します。

クエリ

まず最初にクエリの完成形を記載して、その後に処理の内容を記載していきます！

完成版クエリ

-- Step1: MST.MASTERテーブルにある古いマスタデータのEND_DATEを更新
MERGE INTO TEST.MST.MASTER AS target_master
USING (
  SELECT 
    inactive_records.ID,
    inactive_records.NAME,
    inactive_records.DEPARTMENT,
    new_data.IMPORT_DATE
  FROM (
    SELECT
      active_master.ID,
      active_master.NAME,
      active_master.DEPARTMENT
    FROM (SELECT * FROM TEST.MST.MASTER WHERE END_DATE IS NULL) AS active_master
    LEFT JOIN TEST.RAW.MASTER AS current_raw
      ON active_master.ID = current_raw.ID
      AND active_master.NAME = current_raw.NAME
      AND active_master.DEPARTMENT = current_raw.DEPARTMENT
    WHERE current_raw.ID IS NULL
  ) AS inactive_records
  LEFT JOIN TEST.RAW.MASTER new_data
  ON inactive_records.ID = new_data.ID
) AS update_source
ON target_master.ID = update_source.ID
   AND target_master.NAME = update_source.NAME
   AND target_master.DEPARTMENT = update_source.DEPARTMENT
WHEN MATCHED THEN
  UPDATE SET END_DATE = DATEADD(day, -1, update_source.IMPORT_DATE);


-- Step2: MST.MASTERテーブルに新しいマスタデータを追加
MERGE INTO TEST.MST.MASTER AS target_master
USING TEST.RAW.MASTER AS source_master
ON target_master.ID = source_master.ID
AND target_master.NAME = source_master.NAME
AND target_master.DEPARTMENT = source_master.DEPARTMENT
WHEN NOT MATCHED THEN
  INSERT (ID, NAME, DEPARTMENT, START_DATE, END_DATE)
  VALUES (source_master.ID, source_master.NAME, source_master.DEPARTMENT, source_master.IMPORT_DATE, NULL);