Ensuring Data Integrity | Dataford Interview Questions

How do you ensure data integrity in your analysis at Faire?

Use the provided Faire order, order-line, and payment data to identify orders with inconsistent or missing financial records. Return only orders requiring investigation.

Output

One row per problematic order.
Include order_id, order_total, line_total, completed_payment_total, and integrity_status.
Treat missing order lines or completed payments as integrity issues, and sort by order_id ascending.

Schema

faire_orders

Column	Type	Description
order_idPK	INT	Unique Faire order identifier
retailer_id	INT	Retailer associated with the order
order_total	DECIMAL(10,2)	Stored total amount for the order
order_status	VARCHAR(20)	Current order status
order_date	DATE	Date the order was created

order_lines

Column	Type	Description
line_idPK	INT	Unique order-line identifier
order_id	INT	Referenced order identifier
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
order_id	INT	Referenced order identifier
paid_amount	DECIMAL(10,2)	Payment amount
payment_status	VARCHAR(20)	Payment processing status

Your question is Ensuring Data Integrity. Start with the requirements and the three 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