Definition
- Data profiling – Analyze legacy data for nulls, duplicates, encoding issues, and dirty formats before designing the transform.
- Staging zone – Cloud object storage (S3/GCS) as a durable, replayable landing area so failures need not re-read legacy.
- ETL pipeline – Extract from legacy, transform (clean, validate, map old codes to new), load into the cloud database.
- Idempotent load – Upserts keyed on a stable legacy ID so re-running updates instead of creating duplicates.
- Reconciliation – Compare record counts and balance totals between source and target to prove correctness.
- Parallel run – Serve reads from the new system while validating against the old before cutting over writes.
National Bank of India is moving from an old paper-and-mainframe records room to a new digital vault in the cloud. You photograph each record (extract), correct smudges and reformat to the new filing standard (transform), file neatly in the new vault (load), and keep the old room open to compare both for a while (parallel run) until the new vault is verified perfect.
Understanding the Concepts
Why start with data profiling?
Legacy systems accumulate decades of dirty data. You cannot design a correct transform until you know the mess you are moving.
package com.bank.migration;
public class DataProfiling {
public static void main(String[] args) {
System.out.println("Legacy data problems found during profiling:");
System.out.println(" - Dates stored as VARCHAR in 5 different formats");
System.out.println(" - Phone numbers: some with country code, some without");
System.out.println(" - Duplicate customer records (same PAN, different IDs)");
System.out.println(" - Balance stored as comma-separated string: '9,50,000.00'");
System.out.println(" - Status codes: 'A', 'ACT', 'ACTIVE' all mean active");
System.out.println();
System.out.println("Profile BEFORE designing the transform");
}
}
Expected output:
Legacy data problems found during profiling:
- Dates as VARCHAR in 5 formats
- Duplicate customers (same PAN, different IDs)
- Balance as comma-separated string
- Status codes: 'A', 'ACT', 'ACTIVE' all mean active
Profile BEFORE designing the transform
How does the staging zone help?
Export to cloud storage first. If the load fails, re-run from staging without touching the fragile legacy system.
package com.bank.migration;
public class StagingZone {
public static void main(String[] args) {
System.out.println("Staging in S3/GCS:");
System.out.println(" 1. Export legacy DB to CSV/Parquet -> upload to S3");
System.out.println(" 2. S3 is durable, cheap, and replayable");
System.out.println(" 3. If load fails: re-run from S3 (not legacy)");
System.out.println(" 4. Legacy system never touched again after export");
}
}
Expected output:
Staging in S3/GCS:
1. Export legacy -> S3
2. Durable, replayable
3. Failures re-run from S3
4. Legacy untouched after export
How does the ETL transform work?
Clean, validate, map old codes, parse amounts into BigDecimal, and reject bad records.
package com.bank.migration;
import java.math.BigDecimal;
public class TransformStep {
public static BigDecimal parseAmount(String legacyAmount) {
// Legacy: "9,50,000.00" (Indian format with commas)
String cleaned = legacyAmount.replace(",", "");
return new BigDecimal(cleaned);
}
public static String mapStatus(String legacyStatus) {
return switch (legacyStatus.toUpperCase()) {
case "A", "ACT", "ACTIVE" -> "ACTIVE";
case "C", "CLS", "CLOSED" -> "CLOSED";
case "D", "DOR", "DORMANT" -> "DORMANT";
default -> throw new IllegalArgumentException("Unknown status: " + legacyStatus);
};
}
public static void main(String[] args) {
System.out.println("Transform: " + parseAmount("9,50,000.00") + " = " + new BigDecimal("950000.00"));
System.out.println("Map status: 'ACT' -> " + mapStatus("ACT"));
}
}
Expected output:
Transform: 950000.00 = 950000.00
Map status: 'ACT' -> ACTIVE
Why must the load be idempotent?
Migrations get interrupted. An idempotent load via upserts keyed on legacy ID means re-running updates existing rows instead of creating duplicates.
package com.bank.migration;
public class IdempotentLoad {
public static void main(String[] args) {
System.out.println("Idempotent load:");
System.out.println(" INSERT INTO customers (legacy_id, name, balance)");
System.out.println(" VALUES ('LEG-001', 'Rajesh Kumar', 950000.00)");
System.out.println(" ON CONFLICT (legacy_id) DO UPDATE SET");
System.out.println(" name = EXCLUDED.name, balance = EXCLUDED.balance;");
System.out.println();
System.out.println(" Run 1: INSERT Rajesh -> new row");
System.out.println(" Run 2: CONFLICT -> UPDATE Rajesh -> same row, no duplicate");
System.out.println();
System.out.println(" Checkpoint progress so restart resumes from last batch");
}
}
Expected output:
Idempotent load:
Run 1: INSERT Rajesh -> new row
Run 2: CONFLICT -> UPDATE Rajesh -> same row, no duplicate
Checkpoint progress so restart resumes from last batch
How does reconciliation prove correctness?
Compare counts and balance totals between source and target. Run both systems in parallel before cutover.
package com.bank.migration;
import java.math.BigDecimal;
public class Reconciliation {
public static void main(String[] args) {
long legacyCount = 1_500_000;
BigDecimal legacyTotalBalance = new BigDecimal("45000000000.00");
long cloudCount = 1_500_000;
BigDecimal cloudTotalBalance = new BigDecimal("45000000000.00");
boolean countMatch = legacyCount == cloudCount;
boolean balanceMatch = legacyTotalBalance.compareTo(cloudTotalBalance) == 0;
System.out.println("Reconciliation:");
System.out.println(" Customers: legacy=" + legacyCount + ", cloud=" + cloudCount
+ " -> " + (countMatch ? "MATCH" : "MISMATCH"));
System.out.println(" Total balance: legacy=" + legacyTotalBalance + ", cloud=" + cloudTotalBalance
+ " -> " + (balanceMatch ? "MATCH" : "MISMATCH"));
System.out.println();
System.out.println(" Only cut over writes when reconciliation is 100% clean");
}
}
Expected output:
Reconciliation:
Customers: legacy=1500000, cloud=1500000 -> MATCH
Total balance: legacy=45000000000.00, cloud=45000000000.00 -> MATCH
Only cut over writes when reconciliation is 100% clean
How do you handle security for banking data?
TLS in transit, KMS at rest, least-privilege IAM, PII masking in non-production.
package com.bank.migration;
public class MigrationSecurity {
public static void main(String[] args) {
System.out.println("Security for banking master data:");
System.out.println(" In transit: TLS between legacy and S3, S3 and cloud DB");
System.out.println(" At rest: KMS encryption on S3 buckets and database");
System.out.println(" IAM: least-privilege roles for migration service");
System.out.println(" PII: mask Aadhaar, PAN, phone in non-production");
System.out.println(" Audit: log every access to staging and production data");
}
}
Expected output:
Security for banking master data:
In transit: TLS
At rest: KMS encryption
IAM: least-privilege
PII: masked in non-production
Audit: every access logged
How do you expose the migrated data?
Clean versioned REST APIs with an anti-corruption facade during transition.
package com.bank.migration;
public class ApiDesign {
public static void main(String[] args) {
System.out.println("API design:");
System.out.println(" GET /api/v1/customers/{id}");
System.out.println(" GET /api/v1/accounts/{id}");
System.out.println(" Versioned from day one");
System.out.println();
System.out.println(" During transition: anti-corruption facade");
System.out.println(" Old consumers call familiar endpoints");
System.out.println(" Facade routes to new cloud store");
System.out.println(" Old consumers migrate at their own pace");
}
}
Expected output:
API design:
GET /api/v1/customers/{id}
GET /api/v1/accounts/{id}
Versioned from day one
Anti-corruption facade during transition
Why must the migration be in CI/CD?
The migration code, schema, and infrastructure are tested and automated, not a hand-run script.
package com.bank.migration;
public class MigrationCiCd {
public static void main(String[] args) {
System.out.println("CI/CD for migration:");
System.out.println(" 1. Unit tests on transform logic (parse amounts, map statuses)");
System.out.println(" 2. Schema migrations via Flyway (versioned, reproducible)");
System.out.println(" 3. Dry run against sample data in CI");
System.out.println(" 4. Deploy migration tooling as a tested artifact");
System.out.println(" 5. NOT a hand-run script on someone's laptop");
}
}
Expected output:
CI/CD for migration:
1. Unit tests on transform logic
2. Schema migrations via Flyway
3. Dry run in CI
4. Deploy as tested artifact
5. NOT a hand-run script
What about monitoring and rejected records?
Track per-record progress. Alert on rejection spikes. Capture rejects with reasons.
package com.bank.migration;
public class MigrationMonitoring {
public static void main(String[] args) {
System.out.println("Migration observability:");
System.out.println(" Metrics: extracted=1500000, transformed=1499950, loaded=1499950, rejected=50");
System.out.println(" Rejected records table: legacy_id, reason, raw_data");
System.out.println(" Alert: rejection rate > 0.1% -> investigate");
System.out.println(" Dashboard: progress bar, records/sec, ETA");
}
}
Expected output:
Migration observability:
extracted=1500000, transformed=1499950, loaded=1499950, rejected=50
Rejected records: legacy_id, reason, raw_data
Alert: rejection rate > 0.1%
What should a senior engineer know?
Always keep a rollback plan. Profile before designing. Reconcile before cutting over. Automate everything.
Senior migration checklist:
[x] Profile legacy data before designing transform
[x] Stage in S3/GCS (durable, replayable)
[x] ETL: clean, validate, map, reject bad records
[x] Idempotent load: upserts + checkpoints
[x] Reconciliation: counts + balance totals must match
[x] Parallel run: serve from new, validate against old
[x] Security: TLS, KMS, IAM, PII masking, audit
[x] CI/CD: migration code is tested and automated
[x] Rollback plan: always available
Visual Overview
Master Data Migration Pipeline:
┌──────────────┐ export ┌──────────────┐
│ Legacy DB │─────────▶│ S3 Staging │
│ (mainframe) │ CSV/ │ Zone │
│ │ Parquet │ (durable, │
└──────────────┘ │ replayable) │
└──────┬───────┘
│ read
┌──────▼───────┐
│ Transform │
│ - Clean │
│ - Validate │
│ - Map codes │
└──────┬───────┘
│ upsert
┌──────▼───────┐
│ Cloud DB │
│ (Aurora/ │
│ Cloud SQL) │
└──────┬───────┘
│
┌──────────────────┼──────────────────┐
│ │ │
┌──────▼──────┐ ┌──────▼──────┐ ┌──────▼──────┐
│ Reconcile │ │ APIs │ │ Parallel │
│ counts + │ │ /customers │ │ Run │
│ balances │ │ /accounts │ │ Old vs New │
└─────────────┘ └─────────────┘ └─────────────┘
Key Takeaway
Migrating master data from a legacy system to the cloud is a controlled ETL pipeline with reconciliation, not a one-shot copy. Start with data profiling to understand the legacy mess: duplicate customers, inconsistent date formats, balances as comma-separated strings. Stage the export in cloud object storage as a durable, replayable landing zone so failures never require re-reading the fragile legacy system.
The transform cleans, validates, and maps old codes to new ones. The load is idempotent via upserts keyed on legacy ID, so an interrupted migration re-runs safely without creating duplicates. Reconciliation compares record counts and balance totals between source and target, and you run both systems in parallel before cutting over writes, always with a rollback plan.
At National Bank of India, Pankaj migrated 1.5 million customer records. An earlier attempt without idempotent loads created duplicate Rajesh rows on restart. After adding upserts, checkpoints, and nightly reconciliation comparing total balances, the migration proved clean at every step. Security wrapped everything: TLS in transit, KMS at rest, PII masked in non-production, and the entire migration was tested and deployed through CI/CD.
Remember one sentence: master data migration is an idempotent, reconciled ETL pipeline that stages in cloud storage, cleans and validates on the way in, proves itself by matching counts and balance totals, and runs the old and new systems in parallel before cutover.
---