How would you audit data anomalies in a dataset?
Write a PostgreSQL query using dataset_records and record_line_items to identify missing values, duplicate external identifiers, invalid quantities or prices, missing detail rows, negative totals, and total mismatches.
record_id, anomaly_type, anomaly_detail, and severity.record_id, then anomaly_type.| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique dataset record identifier |
| external_record_id | VARCHAR(30) | Identifier received from an external source |
| customer_id | VARCHAR(20) | Source customer identifier |
| recorded_at | DATE | Date the record was captured |
| declared_total | DECIMAL(10,2) | Total supplied with the record |
| Column | Type | Description |
|---|---|---|
| line_idPK | INT | Unique line item identifier |
| record_id | INT | Parent dataset record identifier |
| item_code | VARCHAR(20) | Item identifier |
| quantity | INT | Item quantity |
| unit_price | DECIMAL(10,2) | Price per item |