【PL/SQL】マスタ同期・履歴管理｜SCD Type 2設計 | コーディングライフスタイル

商品マスタ、顧客マスタ、店舗マスタ、部門マスタのような基準データは、業務システムのあちこちで参照されます。そのため、外部システムからマスタを取り込むときに、単純なUPDATEだけで上書きしてしまうと、過去の注文や集計結果を後から説明できなくなることがあります。マスタ同期では、最新値を反映するだけでなく、いつからいつまで有効だったかを残す設計が重要です。

この記事では、PL/SQLでマスタ同期と履歴管理を設計する方法を整理します。差分取込、ステージング、有効開始日・有効終了日、SCD Type 2、再実行、廃止マスタ、検算ログまでを扱います。差分抽出の考え方は PL/SQLの差分抽出・増分処理、取込前の検証はステージングテーブル設計と組み合わせると安全です。

この記事で扱うこと

マスタ同期で起きる典型的な事故
上書き型と履歴管理型の違い
有効期間を持つマスタテーブル設計
ステージングへの差分取込
ハッシュ値による差分判定
SCD Type 2の更新手順
再実行・廃止マスタ・検算ログの設計

マスタ同期で起きる問題

マスタ同期の失敗は、単に値が古いという問題だけではありません。過去データと現在マスタの意味がずれる、同じコードなのに名称が変わる、廃止されたコードが突然消える、再実行で履歴が二重に作られる、といった運用上の問題が起きます。

過去実績を説明できない商品名や部門名を上書きすると、過去の帳票が当時の名称で再現できなくなります。

削除で参照エラーが起きる外部マスタから消えたコードを物理削除すると、既存データが参照できなくなります。

再実行で履歴が増える同じ取込を再実行しただけで、有効期間履歴が重複することがあります。

差分判定が曖昧何が変わったら履歴を切るのかを決めていないと、不要な履歴が増えます。

マスタ同期は、外部から来たデータをそのまま反映する処理ではありません。業務上の意味を壊さないように、現行値、過去履歴、廃止状態、取込証跡を分けて設計します。

上書き型と履歴管理型を使い分ける

すべてのマスタに履歴管理が必要なわけではありません。たとえば、郵便番号辞書のように過去値をあまり参照しないものは上書き型で十分なことがあります。一方、商品名、取引先名称、部署、担当者、料金区分のように過去実績と紐づくマスタは、履歴管理型にした方が安全です。

上書き型現在値だけが必要なマスタに向いています。実装は簡単ですが、過去値は残りません。

履歴管理型有効期間を持ち、過去の時点でのマスタ値を参照できます。実装は少し複雑です。

ハイブリッド現行マスタと履歴マスタを分け、画面は現行、帳票や監査は履歴を見る方式です。

この記事では、実務で事故を防ぎやすい履歴管理型、つまりSCD Type 2に近い設計を中心に扱います。SCD Type 2では、値が変わったときに古い行を閉じ、新しい行を追加します。

有効期間を持つマスタテーブルを設計する

履歴管理型のマスタでは、業務キーと有効期間を持たせます。商品マスタならitem_codeが業務キー、valid_fromとvalid_toが有効期間です。現行行だけをすばやく探せるように、is_currentも持たせます。

item-master-history-ddl.sql

CREATE TABLE item_master_hist (
  item_hist_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  item_code         VARCHAR2(50) NOT NULL,
  item_name         VARCHAR2(200) NOT NULL,
  category_code     VARCHAR2(50),
  sales_status      VARCHAR2(20) NOT NULL,
  valid_from        DATE NOT NULL,
  valid_to          DATE NOT NULL,
  is_current        CHAR(1) DEFAULT 'Y' NOT NULL,
  source_updated_at TIMESTAMP,
  row_hash          VARCHAR2(64) NOT NULL,
  batch_id          NUMBER NOT NULL,
  created_at        TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT ck_item_hist_current CHECK (is_current IN ('Y','N')),
  CONSTRAINT ck_item_hist_period CHECK (valid_from < valid_to)
);

CREATE UNIQUE INDEX uq_item_master_current
  ON item_master_hist(
    CASE WHEN is_current = 'Y' THEN item_code END
  );

Oracleでは部分インデックスのような構文はありませんが、関数ベース索引で現行行だけの一意性に近い制約を作れます。これにより、同じitem_codeに対して現行行が2つ存在する事故を防ぎやすくなります。

有効終了日は最大日付で表す

現行行のvalid_toには、業務上使わない最大日付を入れることが多いです。たとえばDATE '9999-12-31'です。NULLを使う設計もありますが、期間検索ではBETWEENや範囲条件が複雑になります。

current-master-query.sql

SELECT
  item_code,
  item_name,
  category_code,
  sales_status
FROM item_master_hist
WHERE item_code = :item_code
  AND is_current = 'Y';

過去時点のマスタを参照する場合は、取引日や計上日を有効期間に当てます。この検索ができることが、履歴管理型マスタの価値です。

point-in-time-master-query.sql

SELECT
  item_code,
  item_name,
  category_code,
  sales_status
FROM item_master_hist
WHERE item_code = :item_code
  AND :order_date >= valid_from
  AND :order_date <  valid_to;

ステージングに取込データを固定する

外部マスタを直接履歴テーブルへ反映すると、検証や再実行が難しくなります。まずステージングテーブルに取込データを固定し、バッチID単位で検証してから履歴テーブルへ反映します。ステージングは、重複コード、必須項目、日付形式、廃止フラグなどを検証する場所です。

stg-item-master-ddl.sql

CREATE TABLE stg_item_master (
  batch_id          NUMBER NOT NULL,
  item_code         VARCHAR2(50) NOT NULL,
  item_name         VARCHAR2(200),
  category_code     VARCHAR2(50),
  sales_status      VARCHAR2(20),
  delete_flag       CHAR(1) DEFAULT 'N' NOT NULL,
  source_updated_at TIMESTAMP,
  row_hash          VARCHAR2(64),
  validate_status   VARCHAR2(20) DEFAULT 'NEW' NOT NULL,
  error_message     VARCHAR2(2000),
  loaded_at         TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT pk_stg_item_master PRIMARY KEY (batch_id, item_code)
);

ステージングにbatch_idを持たせることで、同じ取込単位を後から追跡できます。CSV取込やバリデーションの考え方はステージングテーブル設計と共通です。

ハッシュ値で差分を判定する

履歴を切るべきかどうかは、業務上意味のある列が変わったかで判断します。更新日時や取込日時だけが変わっても、商品名やカテゴリが同じなら新しい履歴を作る必要はありません。そのため、履歴対象列を正規化してハッシュ化しておくと、差分判定が簡単になります。

staging-row-hash.sql

UPDATE stg_item_master
SET row_hash = STANDARD_HASH(
      NVL(item_name, '#NULL#') || '|'
      || NVL(category_code, '#NULL#') || '|'
      || NVL(sales_status, '#NULL#'),
      'SHA256'
    )
WHERE batch_id = :batch_id;

ハッシュ対象に含める列は、履歴を切りたい業務列だけにします。たとえばsource_updated_atを含めると、値が同じでも更新日時が変わるたびに履歴が増えます。delete_flagのような制御列は、値変更とは別の廃止処理で扱う方が分かりやすくなります。NULLや数値、日付を含む場合は、NVLやTO_CHARで表現を固定します。

取込前にステージングを検証する

SCD Type 2の更新処理に入る前に、ステージングの品質を確認します。同一バッチ内で商品コードが重複していないか、必須列がNULLでないか、ステータス値が許可範囲かを確認します。ここでエラー行を隔離しておくと、履歴テーブルを壊さずに済みます。

validate-staging.sql

UPDATE stg_item_master s
SET
  validate_status = 'ERROR',
  error_message = '必須項目が不足しています。'
WHERE batch_id = :batch_id
  AND (
    item_code IS NULL
    OR item_name IS NULL
    OR sales_status IS NULL
  );

UPDATE stg_item_master s
SET
  validate_status = 'ERROR',
  error_message = 'sales_statusが不正です。'
WHERE batch_id = :batch_id
  AND validate_status = 'NEW'
  AND sales_status NOT IN ('ACTIVE', 'STOPPED', 'DISCONTINUED');

検証でエラーが出た場合、全体を止めるか、正常行だけ反映するかは業務要件で決めます。基準データであるマスタは影響範囲が広いため、基本は全体停止の方が安全です。エラーコードを体系化するなら PL/SQLの業務エラーコード設計も参考になります。

新規マスタを追加する

ステージングに存在し、現行マスタに存在しないコードは新規マスタです。この場合は、valid_fromを同期基準日、valid_toを最大日付として追加します。

insert-new-master.sql

INSERT INTO item_master_hist (
  item_code,
  item_name,
  category_code,
  sales_status,
  valid_from,
  valid_to,
  is_current,
  source_updated_at,
  row_hash,
  batch_id
)
SELECT
  s.item_code,
  s.item_name,
  s.category_code,
  s.sales_status,
  :effective_date,
  DATE '9999-12-31',
  'Y',
  s.source_updated_at,
  s.row_hash,
  s.batch_id
FROM stg_item_master s
WHERE s.batch_id = :batch_id
  AND s.validate_status = 'NEW'
  AND s.delete_flag = 'N'
  AND NOT EXISTS (
    SELECT 1
    FROM item_master_hist h
    WHERE h.item_code = s.item_code
      AND h.is_current = 'Y'
  );

新規追加は比較的単純ですが、同じコードの現行行が2つあると壊れます。そのため、現行行の一意性は制約や索引で守っておく必要があります。

変更がある現行行を閉じる

既存マスタとステージングのハッシュ値が違う場合、現行行を閉じます。ここでvalid_toを同期基準日に更新し、is_currentをNにします。同じデータを再実行しても閉じ直さないよう、ハッシュ差分がある行だけを対象にします。

close-changed-current-master.sql

UPDATE item_master_hist h
SET
  h.valid_to = :effective_date,
  h.is_current = 'N'
WHERE h.is_current = 'Y'
  AND EXISTS (
    SELECT 1
    FROM stg_item_master s
    WHERE s.batch_id = :batch_id
      AND s.validate_status = 'NEW'
      AND s.delete_flag = 'N'
      AND s.item_code = h.item_code
      AND s.row_hash IS NOT NULL
      AND NVL(s.row_hash, '#NULL#') <> NVL(h.row_hash, '#NULL#')
  );

effective_dateは履歴を切り替える業務日です。取込日時ではなく、マスタ変更がいつから有効かを表す日付にします。ここを曖昧にすると、過去時点参照が正しくできません。

変更候補を明示してから追加する

変更後の新しい現行行を追加する前に、どのコードが変更対象なのかを一時集合として明示しておくと安全です。新規コードと変更コードを同じNOT EXISTS current条件だけで扱うと、処理の意図が分かりにくくなります。実務では、変更候補を作ってから現行行を閉じ、新しい行を追加する流れにするとレビューしやすくなります。

changed-master-candidates.sql

CREATE GLOBAL TEMPORARY TABLE tmp_changed_item_master (
  item_code VARCHAR2(50) NOT NULL,
  row_hash  VARCHAR2(64) NOT NULL
) ON COMMIT DELETE ROWS;

INSERT INTO tmp_changed_item_master(item_code, row_hash)
SELECT
  s.item_code,
  s.row_hash
FROM stg_item_master s
JOIN item_master_hist h
  ON h.item_code = s.item_code
 AND h.is_current = 'Y'
WHERE s.batch_id = :batch_id
  AND s.validate_status = 'NEW'
  AND s.delete_flag = 'N'
  AND s.row_hash IS NOT NULL
  AND NVL(s.row_hash, '#NULL#') <> NVL(h.row_hash, '#NULL#');

この一時表は、同一トランザクション内で使い切る前提です。ON COMMIT DELETE ROWSなので、途中でCOMMITすると候補が消えます。処理を複数トランザクションに分ける場合は、batch_id付きの通常テーブルに変更候補を保存します。

変更後の新しい現行行を追加する

古い現行行を閉じたら、変更後の値で新しい現行行を追加します。ここでは、変更候補に含まれるコードだけを対象にします。再実行時に同じ現行行を重複挿入しないよう、現行行が存在しないコードであることも確認します。

insert-changed-current-master.sql

INSERT INTO item_master_hist (
  item_code,
  item_name,
  category_code,
  sales_status,
  valid_from,
  valid_to,
  is_current,
  source_updated_at,
  row_hash,
  batch_id
)
SELECT
  s.item_code,
  s.item_name,
  s.category_code,
  s.sales_status,
  :effective_date,
  DATE '9999-12-31',
  'Y',
  s.source_updated_at,
  s.row_hash,
  s.batch_id
FROM stg_item_master s
JOIN tmp_changed_item_master c
  ON c.item_code = s.item_code
WHERE s.batch_id = :batch_id
  AND s.validate_status = 'NEW'
  AND s.delete_flag = 'N'
  AND NOT EXISTS (
    SELECT 1
    FROM item_master_hist h
    WHERE h.item_code = s.item_code
      AND h.is_current = 'Y'
  );

新規追加は現行行が存在しないステージング行、変更追加はtmp_changed_item_masterに含まれる行として分けると、処理の意図が明確になります。先に差分がある現行行を閉じておくことで、変更されたコードは現行行なしの状態になり、新しい行が追加されます。トランザクション全体は、閉じる処理と追加処理を同じコミット境界に置くのが基本です。コミット境界は PL/SQLのトランザクション設計と合わせて考えます。

同じ有効開始日の変更をどう扱うか

SCD Type 2では、同じeffective_dateに同じコードの変更が複数回来ると扱いが難しくなります。日単位の有効期間では、同じ日に古い行を閉じて新しい行を複数作ると、valid_from < valid_toを満たせなかったり、期間が重複したりします。そのため、同一有効日の複数変更を許すか、同じ日の最後の変更だけを採用するかを先に決めます。

日単位で管理する同一日の複数変更は最後の値だけを採用し、1日に1履歴とします。

時刻単位で管理するvalid_from/valid_toをTIMESTAMPにし、同じ日でも時刻で履歴を分けます。

承認制にする締め後や同日再変更はステージングで止め、手動承認後に反映します。

廃止マスタをどう扱うか

外部マスタから消えたコードや、廃止フラグが立ったコードをどう扱うかは重要です。物理削除すると、過去データから参照できなくなります。履歴管理型では、現行行を閉じるか、販売停止や廃止ステータスの新履歴を作るのが基本です。

close-deleted-master.sql

UPDATE item_master_hist h
SET
  h.valid_to = :effective_date,
  h.is_current = 'N'
WHERE h.is_current = 'Y'
  AND EXISTS (
    SELECT 1
    FROM stg_item_master s
    WHERE s.batch_id = :batch_id
      AND s.validate_status = 'NEW'
      AND s.delete_flag = 'Y'
      AND s.item_code = h.item_code
  );

外部ファイルに存在しないコードを廃止扱いにする場合は注意が必要です。部分連携なのか全件連携なのかで意味が変わるためです。全件連携であることが保証されている場合だけ、ステージングに存在しない現行コードを廃止候補にできます。

再実行しても壊れない順序にする

マスタ同期は再実行されることを前提にします。途中で失敗した場合、同じbatch_idを再実行しても履歴が二重にならず、現行行が2つにならないようにします。そのためには、更新順序と条件が重要です。

ステージングを検証する
ハッシュ値を作る
差分がある現行行だけ閉じる
現行行が存在しないコードだけ追加する
廃止対象を閉じる
現行行の重複と期間重複を検算する
問題なければコミットする

この順序なら、同じステージングをもう一度流しても、ハッシュが同じ現行行は閉じられず、現行行があるコードは追加されません。MERGEを使いたくなる場面ですが、SCD Type 2では「古い行を閉じる」と「新しい行を追加する」が別操作になるため、分けて書いた方が読みやすいことが多いです。単純なUPSERTは PL/SQLのMERGE文とUPSERT と使い分けます。

履歴期間の重複を検算する

履歴管理型マスタで必ず確認したいのが、同じコードで有効期間が重なっていないかです。期間が重なると、ある日付でマスタを引いたときに複数行が返り、帳票や集計が壊れます。

check-period-overlap.sql

SELECT
  a.item_code,
  a.item_hist_id AS hist_id_1,
  b.item_hist_id AS hist_id_2,
  a.valid_from AS from_1,
  a.valid_to AS to_1,
  b.valid_from AS from_2,
  b.valid_to AS to_2
FROM item_master_hist a
JOIN item_master_hist b
  ON b.item_code = a.item_code
 AND b.item_hist_id > a.item_hist_id
 AND a.valid_from < b.valid_to
 AND b.valid_from < a.valid_to;

このSQLが1件でも返る場合は、履歴期間が重複しています。同期処理の最後に検算し、重複があればコミットしない設計にします。

現行行が1つだけか確認する

現行行の重複も致命的です。関数ベース索引で防げる場合でも、バッチの最後に検算SQLを実行しておくと、運用上の説明がしやすくなります。

check-current-duplicate.sql

SELECT
  item_code,
  COUNT(*) AS current_count
FROM item_master_hist
WHERE is_current = 'Y'
GROUP BY item_code
HAVING COUNT(*) <> 1;

この検算では、現行行が0件のコードも別途確認します。廃止済みコードは現行行0件でよいのか、廃止ステータスの現行行を残すのかを業務要件で決めます。

check-current-missing.sql

SELECT x.item_code
FROM (
  SELECT DISTINCT item_code
  FROM item_master_hist
) x
WHERE NOT EXISTS (
  SELECT 1
  FROM item_master_hist h
  WHERE h.item_code = x.item_code
    AND h.is_current = 'Y'
)
AND NOT EXISTS (
  SELECT 1
  FROM item_master_hist h
  WHERE h.item_code = x.item_code
    AND h.sales_status = 'DISCONTINUED'
    AND h.valid_to < DATE '9999-12-31'
);

この例では、廃止済みとして閉じられたコードを除外し、それ以外で現行行がないコードを検出しています。廃止ステータスの現行行を残す設計にする場合は、除外条件を変えます。大切なのは、現行行0件が正常なのか異常なのかをマスタ種別ごとに決めておくことです。

同期ログに件数を残す

マスタ同期では、取込件数、新規件数、変更件数、廃止件数、エラー件数を残します。単に成功・失敗だけでは、マスタがなぜ変わったのか後から追えません。ジョブ履歴の基本設計はジョブ実行履歴テーブル設計と同じです。

master-sync-log-ddl.sql

CREATE TABLE master_sync_log (
  batch_id          NUMBER PRIMARY KEY,
  master_name       VARCHAR2(100) NOT NULL,
  effective_date    DATE NOT NULL,
  status            VARCHAR2(20) NOT NULL,
  staged_rows       NUMBER DEFAULT 0 NOT NULL,
  inserted_rows     NUMBER DEFAULT 0 NOT NULL,
  changed_rows      NUMBER DEFAULT 0 NOT NULL,
  closed_rows       NUMBER DEFAULT 0 NOT NULL,
  error_rows        NUMBER DEFAULT 0 NOT NULL,
  started_at        TIMESTAMP NOT NULL,
  finished_at       TIMESTAMP,
  error_message     VARCHAR2(2000)
);

件数が急に増えたり減ったりした場合は、正常終了でも確認対象です。たとえば商品マスタが通常100件変更なのに、突然2万件変更になった場合、外部ファイルの仕様変更やハッシュ対象列のミスを疑います。

サマリーや実績との関係を考える

マスタ同期は、サマリーテーブルや実績集計にも影響します。商品カテゴリが変わった場合、過去実績を旧カテゴリで見たいのか、新カテゴリで見たいのかによって集計の作り方が変わります。履歴マスタを使えば、取引日時点のカテゴリで集計できます。

join-fact-with-master-history.sql

SELECT
  TRUNC(o.order_date) AS summary_date,
  mh.category_code,
  SUM(d.amount) AS sales_amount
FROM orders o
JOIN order_details d
  ON d.order_id = o.order_id
JOIN item_master_hist mh
  ON mh.item_code = d.item_code
 AND o.order_date >= mh.valid_from
 AND o.order_date <  mh.valid_to
GROUP BY
  TRUNC(o.order_date),
  mh.category_code;

サマリーテーブル側でどの時点のマスタを使うかは、最初に決めておきます。集計バッチの設計はサマリーテーブル・集計バッチ設計と合わせて考えると一貫性が出ます。