Cross-DB Data Consistency Validation

How do you validate data consistency across multiple databases in an automated test?

Assume synchronized extracts from two databases are available in qa_source_orders and qa_target_orders. Write a SQL query that identifies records missing from either extract or containing different field values.

Output

1. One row per inconsistent order
2. Columns: order_id, source and target values, and consistency_status
3. Include missing records and value mismatches, exclude consistent records, and order by order_id ascending

Output details

• Return columns in this order: order_id, source_customer_email, target_customer_email, source_total_amount, target_total_amount, source_order_status, target_order_status, consistency_status.
• Treat two NULL field values as equal, but treat NULL and 0 as different values.
• Label records with statuses MISSING_IN_SOURCE, MISSING_IN_TARGET, and VALUE_MISMATCH according to the inconsistent condition.