Payments flow from the source database (src_payments) into the warehouse (wh_payments); both have payment_id, paid_at (a UTC TIMESTAMP) and amount, which can be NULL. The warehouse is partitioned by the date of paid_at. Reconcile the two per partition date:
- •for each side, count its rows and total its
amount (a NULL amount counts as 0); - •a date found on only one side is a break;
- •so is a date whose row counts differ, or whose totals differ by more than 0.01 — smaller differences are rounding drift, not breaks.
Report every date that breaks, with one issue — the first that applies: 'missing_in_warehouse' (no warehouse rows that day), 'missing_in_source' (no source rows), 'count_mismatch', 'amount_mismatch'.
Columns: partition_date, issue, src_rows, wh_rows, src_amount, wh_amount (totals rounded to 2 decimal places; a side with no rows that day shows 0 rows and an amount of 0). Sort by partition_date.