Cross-DB Data Consistency Validation | Dataford Interview Questions

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

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

Schema

qa_source_orders

Column	Type	Description
order_idPK	INT	Order identifier from the source database
customer_email	VARCHAR(255)	Customer email recorded in the source database
total_amount	DECIMAL(10,2)	Order total recorded in the source database
order_status	VARCHAR(30)	Order status recorded in the source database

qa_target_orders

Column	Type	Description
order_idPK	INT	Order identifier from the target database
customer_email	VARCHAR(255)	Customer email recorded in the target database
total_amount	DECIMAL(10,2)	Order total recorded in the target database
order_status	VARCHAR(30)	Order status recorded in the target database

Your question is Cross-DB Data Consistency Validation. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem