Katkat
Back

Design Transaction Data Quality Checks

ZooxData Engineer · Technical Screen
MediumData EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

This question evaluates the ability to design and implement data quality checks for relational transaction and vendor datasets, focusing on integrity constraints, referential integrity, business-rule validation (refund logic), missing/invalid values, and anomaly detection.

You own data quality for the same credit-card transaction dataset.

transactions

columntypedescription
transaction_idintegerUnique ID for a transaction.
user_idintegerUnique ID for a customer.
vendor_idintegerUnique ID for a vendor.
transaction_timetimestampTime when the transaction was recorded.
transaction_dollarsnumericDollar amount of the transaction.
transaction_typetextTransaction type, such as PURCHASE or REFUND.
refund_transaction_idintegerFor a REFUND, the transaction_id of the original PURCHASE.

vendors

columntypedescription
vendor_idintegerUnique ID for a vendor.
citytextCity where the vendor is located.
state_provincetextState or province where the vendor is located.
countrytextTwo-letter country code.

Propose at least five data quality checks or validations for these tables. Consider integrity constraints, referential integrity, refund logic, invalid values, missing data, and anomaly detection. For at least two checks, provide SQL that returns the failing rows or failing keys.