Audit Imported Records Against References

How do you ensure accuracy when managing large, complex data sets in Excel?

Write a PostgreSQL query that audits imported records against reference records and returns every discrepancy, including records present in only one source. Treat two NULL values as matching.

Output

1. One row per discrepancy, with record_key, imported and expected amounts, imported and expected statuses, and discrepancy_type.
2. Exclude records whose values and statuses match.
3. Order by record_key ascending.

Output details

• Return columns in this exact order: record_key, imported_amount, expected_amount, imported_status, expected_status, discrepancy_type.
• Label source-only records as missing_import or missing_reference, respectively.
• Label discrepancies as amount_mismatch, status_mismatch, or amount_and_status_mismatch.
• Treat one NULL and one non-NULL amount or status as mismatching.