レガシーデータの移行と整合性検証

目標

散らかったレガシーCSVをステージングにロードし、プロファイリング → クレンジング → 重複排除 → コードマッピング → 整合性検証 → 除外件の管理 → 確認書の作成まで、データ移行の1サイクルを最後までやり遂げます。

なぜ重要なのか

「データ移行はうまくいきました」という口頭での確認は、何も証明しません。件数の比較とサンプル検証を文書化する必要があり、そのうち件数と合計だけを見ると、1件が抜けて別の1件が2倍になった状況を見逃します。そして、除外された件を一覧として残さないと、サービスイン後に「データがないのですが」という問い合わせが来たときに、最初から調べ直すことになります。残しておけば、「その13件はコード未マッピングで除外され、D+3に再移行の予定です」と30秒で答えられます。その違いが、安定化期間の生活の質を変えます。

ステップ

ソース: /opt/lab/fixtures/dbo/legacy/CUST_LEGACY.csv(1,200行) コードマッピング表: /opt/lab/fixtures/dbo/legacy/grade_map.csv
/root/db/etl.dbを作成し、ソースのCSVを加工せずにSTG_CUSTテーブルにロードしてください。すべてのカラムはTEXTで、行数は1200である必要があります。
/root/db/profile.csvを作成してください。1行目はcolumn,nulls,spaces,distinctです。 STG_CUSTのすべてのカラムについて、
- nulls: 値が空、または前後の空白を取り除いた値が文字列NULL/nullである件数
- spaces: 前後に空白が付いている件数
- distinct: 異なる値の個数(元の文字列基準) をテーブルのカラム順に書いてください。
TGT_CUSTテーブルを作成し、クレンジングルールを適用してロードしてください。ルールは次のとおりです。
- すべての文字列の前後の空白を除去します
- 文字列NULL/nullを本物のNULLにします
- 日付(REG_DATE)をYYYYMMDDの8桁に統一します (ソースにはYYYYMMDD、YYYY-MM-DD、YY/MM/DDの3つの形式が混在しています。YYは20YYとして扱います。)
- 金額(TOT_AMT)のカンマを除去して整数にしますロード後、TGT_CUSTにREG_DATEが8桁でない行が0件である必要があります。
同じCUST_IDが複数件ある場合は、UPD_DTが最も大きい行1つだけを残してください。UPD_DTが同じなら、ソースの順序で後ろの行を残してください。削除した件数を、/root/db/dedup.txtにremoved=<건수>(プレースホルダーは件数です)として保存してください。
grade_map.csvでGRADEを新しいコードに変換し、GRADE_CDに入れてください。マッピング表にない値はGRADE_CDを99にし、その行のCUST_IDと元の値を/root/db/unmapped.csvに保存してください。1行目はcust_id,legacy_gradeで、cust_idの昇順です。
/root/db/recon.csvを作成してください。1行目はitem,source,target,diff,resultです。次の3行を埋めてください。
- count: ソースの行数と対象の行数の比較
- amount: ソースのTOT_AMTの合計と対象の合計の比較 (ソースはカンマを除去して整数に換算して比較します)
- distinct_id: ソースのユニークなCUST_IDの数と対象の行数の比較 resultは、一致すればOK、異なればNGです。
移行されなかった件(重複排除で抜けた件)のCUST_IDを、/root/db/excluded.csvに保存してください。1行目はcust_id,reasonで、reasonはduplicateです。
/root/db/etl-signoff.mdを作成してください。## 이관 대상、## 제외 사유、## 검증 결과、## 재이관 대상、## 확인(韓国語の見出しで、順に移行対象、除外理由、検証結果、再移行対象、確認を意味します)の5つのh2見出しが必要で、次の4行が正確に含まれている必要があります。
```
source_rows=<원천 행 수>
target_rows=<대상 행 수>
excluded=<제외 건수>
unmapped=<코드 미매핑 건수>
```
(山括弧の中の韓国語はプレースホルダーで、順にソースの行数、対象の行数、除外件数、コード未マッピング件数です。)

参考

CSVのロード: .mode csv / .import --skip 1 <파일> <테이블>(プレースホルダーはファイル名とテーブル名です)
文字列の整理: trim()、replace()、substr()
重複排除: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)、またはGROUP BY + MAX()の組み合わせ
よくあるミス1: ステージングにロードしながら同時にクレンジングして、元のデータと突き合わせられなくなることです。
よくあるミス2: YY/MM/DDを19YYと解釈すること。このデータは2000年代です。
よくあるミス3: 除外した件を数えるだけで、一覧として残さないことです。

ソースデータのロード

/root/db/etl.dbを作成し、ソースのCSVを加工せずにSTG_CUSTテーブルにロードしてください。すべてのカラムはTEXTで、行数は1200である必要があります。

ソースはそのままの形でステージングに入れます。ここでクレンジングすると、あとで元のデータと突き合わせられなくなります。

データプロファイリング

/root/db/profile.csvを作成してください。1行目はcolumn,nulls,spaces,distinctです。 STG_CUSTのすべてのカラムについて、

nulls: 値が空、または前後の空白を取り除いた値が文字列NULL/nullである件数
spaces: 前後に空白が付いている件数
distinct: 異なる値の個数(元の文字列基準) をテーブルのカラム順に書いてください。

クレンジングのルールはプロファイリングの結果から出てきます。カラムごとにNULLが何件あるか、値の種類がいくつあるかから見ます。

クレンジングルールの適用

TGT_CUSTテーブルを作成し、クレンジングルールを適用してロードしてください。ルールは次のとおりです。

すべての文字列の前後の空白を除去します
文字列NULL/nullを本物のNULLにします
日付(REG_DATE)をYYYYMMDDの8桁に統一します (ソースにはYYYYMMDD、YYYY-MM-DD、YY/MM/DDの3つの形式が混在しています。YYは20YYとして扱います。)
金額(TOT_AMT)のカンマを除去して整数にしますロード後、TGT_CUSTにREG_DATEが8桁でない行が0件である必要があります。

日付の形式が複数混在しています。各形式をどう判別して変換するかを先に決めてから、コードを書いてください。

重複排除

同じCUST_IDが複数件ある場合は、UPD_DTが最も大きい行1つだけを残してください。 UPD_DTが同じなら、ソースの順序で後ろの行を残してください。削除した件数を、/root/db/dedup.txtにremoved=<건수>(プレースホルダーは件数です)として保存してください。

何を残すかは技術的な判断ではなく、業務上の判断です。ルールが決まったなら、そのルールどおりに正確に実装してください。

コードマッピング

grade_map.csvでGRADEを新しいコードに変換し、GRADE_CDに入れてください。マッピング表にない値はGRADE_CDを99にし、その行のCUST_IDと元の値を/root/db/unmapped.csvに保存してください。 1行目はcust_id,legacy_gradeで、cust_idの昇順です。

マッピング表にない値が出てきたときにどうするかが、定義されている必要があります。その対象は必ず一覧として残してください。

整合性検証

/root/db/recon.csvを作成してください。1行目はitem,source,target,diff,resultです。次の3行を埋めてください。

count: ソースの行数と対象の行数の比較
amount: ソースのTOT_AMTの合計と対象の合計の比較 (ソースはカンマを除去して整数に換算して比較します)
distinct_id: ソースのユニークなCUST_IDの数と対象の行数の比較 resultは、一致すればOK、異なればNGです。

件数と合計だけでは、1件の欠落と1件の重複が相殺される場合を検出できません。指標を複数置いてください。

除外件の一覧

移行されなかった件(重複排除で抜けた件)のCUST_IDを、 /root/db/excluded.csvに保存してください。 1行目はcust_id,reasonで、reasonはduplicateです。

「1,200件中1,187件を移行」までが結果であり、残りの13件が何なのかを答えられなければなりません。

移行結果確認書

/root/db/etl-signoff.mdを作成してください。 ## 이관 대상、## 제외 사유、## 검증 결과、## 재이관 대상、## 확인(韓国語の見出しで、順に移行対象、除外理由、検証結果、再移行対象、確認を意味します)の5つのh2見出しが必要で、次の4行が正確に含まれている必要があります。

source_rows=<원천 행 수>
target_rows=<대상 행 수>
excluded=<제외 건수>
unmapped=<코드 미매핑 건수>

(山括弧の中の韓国語はプレースホルダーで、順にソースの行数、対象の行数、除外件数、コード未マッピング件数です。)

サービスイン後の「データがないのですが」という問い合わせにすぐ答えるための文書です。数値と理由が一緒に書かれている必要があります。