Design Transaction Data Quality Checks
Company: Zoox
Role: Data Engineer
Category: Software Engineering Fundamentals
Difficulty: medium
Interview Round: Technical Screen
You own data quality for the same credit-card transaction dataset.
`transactions`
| column | type | description |
|---|---|---|
| `transaction_id` | integer | Unique ID for a transaction. |
| `user_id` | integer | Unique ID for a customer. |
| `vendor_id` | integer | Unique ID for a vendor. |
| `transaction_time` | timestamp | Time when the transaction was recorded. |
| `transaction_dollars` | numeric | Dollar amount of the transaction. |
| `transaction_type` | text | Transaction type, such as `PURCHASE` or `REFUND`. |
| `refund_transaction_id` | integer | For a `REFUND`, the `transaction_id` of the original `PURCHASE`. |
`vendors`
| column | type | description |
|---|---|---|
| `vendor_id` | integer | Unique ID for a vendor. |
| `city` | text | City where the vendor is located. |
| `state_province` | text | State or province where the vendor is located. |
| `country` | text | Two-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.
Overview: 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.