Your question is Validate Migration Reconciliation Metrics. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are validating a migration from a legacy transactional system into an Accenture-managed cloud warehouse. Write a PostgreSQL query that returns one row per source table with reconciliation metrics comparing legacy row counts and amount totals against the migrated warehouse data for the migration batch dated 2024-04-01.
Your result should include the source table name, legacy row count, warehouse row count, legacy amount total, warehouse amount total, count difference, amount difference, and a status flag that marks each table as PASS only when both row count and amount total match after treating NULL amounts as zero. Include legacy tables even if no warehouse rows were loaded for that table in the batch.
| Column | Type | Description |
|---|---|---|
| source_table | VARCHAR(50) | Legacy source table name |
| extract_date | DATE | Date the legacy summary was extracted |
| row_count | INT | Row count captured from the legacy source |
| total_amount | NUMERIC(12,2) | Total amount captured from the legacy source |
| Column | Type | Description |
|---|---|---|
| load_idPK | INT | Warehouse load identifier |
| source_table | VARCHAR(50) | Source table loaded into the warehouse |
| batch_date | DATE | Warehouse batch date |
| status | VARCHAR(20) | Load execution status |
| Column | Type | Description |
|---|---|---|
| txn_idPK | INT | Migrated transaction identifier |
| load_id | INT | Load identifier tied to the audit table |
| source_table | VARCHAR(50) | Source table for the migrated transaction |
| amount | NUMERIC(12,2) | Migrated transaction amount |