What data checks and validations would you add to Zoox transactions and vendors tables, what edge cases or anomalies would you look for, and can you provide an example SQL query?
Use the provided tables to identify transaction-level data quality issues. Consider missing values, invalid amounts, unsupported statuses or currencies, vendor validity periods, missing vendor records, and duplicate transaction references.
transaction_id, vendor_id, transaction_ref, vendor_name, issue_count, and issuestransaction_id ascending| Column | Type | Description |
|---|---|---|
| vendor_idPK | INT | Unique vendor identifier |
| vendor_name | VARCHAR(120) | Registered vendor name |
| tax_id | VARCHAR(40) | Vendor tax identifier |
| active_from | DATE | Date the vendor became active |
| active_to | DATE | Date the vendor became inactive |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| vendor_id | INT | Referenced vendor identifier |
| transaction_ref | VARCHAR(50) | External transaction reference |
| transaction_date | DATE | Date the transaction occurred |
| amount | NUMERIC(12,2) | Transaction amount |
| currency | VARCHAR(3) | Transaction currency code |
| status | VARCHAR(20) | Transaction processing status |