Design Daily Count Reconciliation Process

Context

Teams often validate ETL reliability by comparing daily record counts in source systems against warehouse tables. This is a common first-line data quality check because it quickly surfaces missing loads, duplicate loads, and date-partition issues.

Question

Explain how you would design a process to compare daily source counts against warehouse counts. Your answer should cover:

1. How you would define the counting grain and date logic
2. How you would store or calculate source and warehouse counts
3. How you would detect mismatches and classify issues
4. How you would handle late-arriving data, reruns, and acceptable thresholds
5. What SQL checks or reporting outputs you would produce

Scope Guidance

The interviewer is looking for a practical design explanation, not a full production implementation. Focus on a simple, reliable reconciliation pattern using SQL aggregations, clear assumptions, and operational considerations such as scheduling, idempotency, and alerting.