How would you find missing records in a new system, find mismatched records (amount, date, etc.), and perform row count comparison by day?

To validate data after a migration, I would use a multi‑step approach:

1. Row count comparison – Group both source and target tables by the relevant date column and compare the counts. Any discrepancy flags a potential issue.
2. Missing records – Perform a LEFT JOIN from the source to the target on the primary key (or composite key). Rows that return NULL in the target side are missing.
3. Mismatched records – Use a FULL OUTER JOIN (or a UNION of two LEFT JOINs) on the key and compare each field of interest (amount, date, status, etc.). Rows where any field differs are flagged.
4. Checksum or hash comparison – For large tables, compute a hash (e.g., MD5) of each row or of aggregated groups to quickly spot differences without row‑by‑row comparison.
5. Automated reporting – Store the results in a validation table and generate alerts for any anomalies.

Complexity is O(n) for each pass, where n is the number of rows. Indexes on the join keys keep the joins efficient. This process ensures that both quantity and quality of data are preserved during migration.