How do you validate data integrity and handle anomalies when designing automated reporting pipelines for Intuit?
Write a PostgreSQL query that compares source transactions with published report entries and identifies records requiring investigation. Include duplicate records, missing records, invalid source values, and amount discrepancies.
run_id, transaction_id, anomaly_type, source and reported amounts, and record counts.run_id, transaction_id, and anomaly type.| Column | Type | Description |
|---|---|---|
| run_idPK | INT | Unique automated reporting run identifier |
| pipeline_name | VARCHAR(40) | Name of the reporting pipeline |
| run_date | DATE | Business date processed by the run |
| Column | Type | Description |
|---|---|---|
| source_record_idPK | INT | Unique source record identifier |
| run_id | INT | Reporting run that ingested the source record |
| transaction_id | VARCHAR(20) | Business transaction identifier |
| amount | NUMERIC(12,2) | Source transaction amount |
| status | VARCHAR(20) | Source transaction status |
| Column | Type | Description |
|---|---|---|
| report_entry_idPK | INT | Unique published report entry identifier |
| run_id | INT | Reporting run that published the entry |
| transaction_id | VARCHAR(20) | Business transaction identifier |
| amount | NUMERIC(12,2) | Amount published to the report |