Legacy Data Migration and Consistency Verification

Goal

You load a messy legacy CSV into staging and complete one full data migration cycle: profiling → cleansing → deduplication → code mapping → consistency verification → excluded-row management → writing the confirmation.

Why it matters

Verbal confirmation that "the migration went well" proves nothing. You must document count comparisons and sample verification, and among those, if you look only at counts and totals you miss the situation where one row went missing and another doubled. And if you do not keep the excluded rows as a list, when an inquiry like "the data is missing" arrives after launch, you have to investigate from scratch. If you kept it, you can answer in 30 seconds: "those 13 were excluded for unmapped codes and are scheduled for re-migration on D+3." That difference changes the quality of life during the stabilization period.

Steps

1. Source: /opt/lab/fixtures/dbo/legacy/CUST_LEGACY.csv (1,200 rows) Code mapping table: /opt/lab/fixtures/dbo/legacy/grade_map.csv
2. Create /root/db/etl.db and load the source CSV into the STG_CUST table without processing. All columns are TEXT and the row count must be 1200.
3. Create /root/db/profile.csv. The first line is column,nulls,spaces,distinct. For every column of STG_CUST, write
   • nulls: the number of rows where the value is empty or, with leading and trailing spaces removed, is the string NULL/null
   • spaces: the number of rows with leading or trailing spaces
   • distinct: the number of distinct values (based on the original string) in the table's column order.
4. Create the TGT_CUST table and load it by applying the cleansing rules. The rules are
   • Remove leading and trailing spaces from all strings
   • The string NULL/null → a real NULL
   • Unify the date (REG_DATE) to 8 digits YYYYMMDD (The source mixes three formats: YYYYMMDD, YYYY-MM-DD, and YY/MM/DD. Treat YY as 20YY.)
   • Remove commas from the amount (TOT_AMT) and convert it to an integer After loading, TGT_CUST must have 0 rows where REG_DATE is not 8 digits.
5. If there are several rows with the same CUST_ID, keep only the row with the largest UPD_DT. If UPD_DT is the same, keep the later row in source order. Save the number of removed rows to /root/db/dedup.txt as removed=<건수> (the placeholder is the count).
6. Using grade_map.csv, convert GRADE to the new code and put it in GRADE_CD. For values not in the mapping table, leave GRADE_CD as 99, and save that row's CUST_ID and original value to /root/db/unmapped.csv. The first line is cust_id,legacy_grade, in ascending cust_id order.
7. Create /root/db/recon.csv. The first line is item,source,target,diff,result. Fill in the three rows below.
   • count : source row count vs target row count
   • amount: source TOT_AMT total vs target total (compare the source after removing commas and converting to integers)
   • distinct_id: the source's number of distinct CUST_ID values vs the target row count result is OK if they match and NG if not.
8. Save the CUST_ID of the rows that were not migrated (those dropped by deduplication) to /root/db/excluded.csv. The first line is cust_id,reason, and reason is duplicate.
9. Write /root/db/etl-signoff.md. It must have five h2 headings, ## 이관 대상, ## 제외 사유, ## 검증 결과, ## 재이관 대상, ## 확인 (in order: migration target, exclusion reasons, verification results, re-migration target, confirmation), and the following four lines must be included exactly.

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

Notes

• Loading CSV: .mode csv / .import --skip 1 <파일> <테이블> (the placeholders are the file and the table)
• String cleanup: trim(), replace(), substr()
• Deduplication: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) or a GROUP BY + MAX() combination
• Common mistake 1: cleansing while loading into staging, so you can no longer compare against the original.
• Common mistake 2: interpreting YY/MM/DD as 19YY. This data is from the 2000s.
• Common mistake 3: only counting the excluded rows and not leaving them as a list.

Load the source data

Create /root/db/etl.db and load the source CSV into the STG_CUST table without processing. All columns are TEXT and the row count must be 1200.

Put the source into staging exactly as it is. If you cleanse here, you can no longer compare against the original later.

Data profiling

Create /root/db/profile.csv. The first line is column,nulls,spaces,distinct. For every column of STG_CUST, write

• nulls: the number of rows where the value is empty or, with leading and trailing spaces removed, is the string NULL/null
• spaces: the number of rows with leading or trailing spaces
• distinct: the number of distinct values (based on the original string) in the table's column order.

Cleansing rules come from the profiling results. Start by looking at how many NULLs each column has and how many kinds of values it has.

Apply cleansing rules

Create the TGT_CUST table and load it by applying the cleansing rules. The rules are

• Remove leading and trailing spaces from all strings
• The string NULL/null → a real NULL
• Unify the date (REG_DATE) to 8 digits YYYYMMDD (The source mixes three formats: YYYYMMDD, YYYY-MM-DD, and YY/MM/DD. Treat YY as 20YY.)
• Remove commas from the amount (TOT_AMT) and convert it to an integer After loading, TGT_CUST must have 0 rows where REG_DATE is not 8 digits.

Several date formats are mixed. Decide first how to detect and convert each format, then write the code.

Deduplication

If there are several rows with the same CUST_ID, keep only the row with the largest UPD_DT. If UPD_DT is the same, keep the later row in source order. Save the number of removed rows to /root/db/dedup.txt as removed=<건수> (the placeholder is the count).

What to keep is a business judgment, not a technical one. Once the rule is decided, implement exactly that rule.

Code mapping

Using grade_map.csv, convert GRADE to the new code and put it in GRADE_CD. For values not in the mapping table, leave GRADE_CD as 99, and save that row's CUST_ID and original value to /root/db/unmapped.csv. The first line is cust_id,legacy_grade, in ascending cust_id order.

What to do when a value not in the mapping table appears must be defined. Always keep those rows as a list.

Consistency verification

Create /root/db/recon.csv. The first line is item,source,target,diff,result. Fill in the three rows below.

• count : source row count vs target row count
• amount: source TOT_AMT total vs target total (compare the source after removing commas and converting to integers)
• distinct_id: the source's number of distinct CUST_ID values vs the target row count result is OK if they match and NG if not.

Counts and totals alone cannot catch a case where one missing row and one duplicated row cancel out. Use several metrics.

List of excluded rows

Save the CUST_ID of the rows that were not migrated (those dropped by deduplication) to /root/db/excluded.csv. The first line is cust_id,reason, and reason is duplicate.

"1,187 of 1,200 migrated" is the result, and you must be able to answer what the remaining 13 are.

Migration result confirmation

Write /root/db/etl-signoff.md. It must have five h2 headings, ## 이관 대상, ## 제외 사유, ## 검증 결과, ## 재이관 대상, ## 확인 (in order: migration target, exclusion reasons, verification results, re-migration target, confirmation), and the following four lines must be included exactly.

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

This is a document for answering "the data is missing" inquiries immediately after launch. Numbers and reasons must be together.