How would you approach validating a large, inconsistent dataset to ensure accuracy?
Use source_transactions as the dataset being validated and valid_accounts as the reference data. Write a query that returns every invalid source record and identifies the applicable data quality issues.
source_id ascendingsource_id, account_code, validation_status, issue_count, account_check, duplicate_recordaccount_code as missing when NULL, unmatched when absent from valid_accounts, inactive when is_active is FALSE, otherwise matched.transaction_date values as issues.amount as an issue when NULL or less than or equal to 0.status as an issue when NULL or not one of posted, pending, or void.account_code, transaction_date, amount, and status values as duplicate records, including matching NULLs.validation_status to INVALID and calculate issue_count as the number of flagged account, date, amount, status, and duplicate issues.| Column | Type | Description |
|---|---|---|
| source_idPK | INT | Unique identifier for the source record |
| account_code | VARCHAR(20) | Account identifier supplied by the source dataset |
| transaction_date | DATE | Date associated with the transaction |
| amount | NUMERIC(12,2) | Transaction amount |
| status | VARCHAR(20) | Source transaction status |
| Column | Type | Description |
|---|---|---|
| account_codePK | VARCHAR(20) | Reference account identifier |
| account_name | VARCHAR(100) | Reference account name |
| is_active | BOOLEAN | Whether the account is currently active |