Task
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.
Schema
| table | column | type | description |
|---|
| legacy_table_summary | source_table | VARCHAR(50) | Legacy source table name |
| legacy_table_summary | extract_date | DATE | Date the legacy summary was extracted |
| legacy_table_summary | row_count | INT | Row count in the legacy extract |
| legacy_table_summary | total_amount | NUMERIC(12,2) | Total amount from the legacy extract |
| warehouse_load_audit | load_id | INT | Warehouse load identifier |
| warehouse_load_audit | source_table | VARCHAR(50) | Source table loaded into the warehouse |
| warehouse_load_audit | batch_date | DATE | Warehouse batch date |
| warehouse_load_audit | status | VARCHAR(20) | Load status |
| warehouse_transactions | txn_id | INT | Migrated transaction identifier |
| warehouse_transactions | load_id | INT | Load identifier tied to the audit table |
| warehouse_transactions | source_table | VARCHAR(50) | Source table for the migrated row |
| warehouse_transactions | amount | NUMERIC(12,2) | Migrated transaction amount |
Sample data
| source_table | extract_date | row_count | total_amount |
|---|
| orders | 2024-04-01 | 4 | 450.00 |
| payments | 2024-04-01 | 3 | 300.00 |
| refunds | 2024-04-01 | 2 | -30.00 |
| load_id | source_table | batch_date | status |
|---|
| 101 | orders | 2024-04-01 | completed |
| 102 | payments | 2024-04-01 | completed |
| 103 | refunds | 2024-04-01 | failed |
| txn_id | load_id | source_table | amount |
|---|
| 1 | 101 | orders | 100.00 |
| 2 | 101 | orders | 150.00 |
| 5 | 102 | payments | 200.00 |
Expected output
| source_table | legacy_row_count | warehouse_row_count | legacy_total_amount | warehouse_total_amount | row_count_diff | amount_diff | validation_status |
|---|
| invoices | 1 | 0 | 0.00 | 0.00 | 1 | 0.00 | FAIL |
| orders | 4 | 4 | 450.00 | 450.00 | 0 | 0.00 | PASS |
| payments | 3 | 3 | 300.00 | 300.00 | 0 | 0.00 | PASS |
| refunds | 2 | 0 | -30.00 | 0.00 | 2 | -30.00 | FAIL |
