Write a complex SQL query to validate the completeness and integrity of a newly ingested dataset.
Use the provided batch, account, and event data. Identify every validation failure for the most recently loaded batch.
batch_id, check_name, issue_count, and detailscheck_name| Column | Type | Description |
|---|---|---|
| batch_idPK | INT | Unique ingestion batch identifier |
| dataset_name | VARCHAR(100) | Name of the ingested dataset |
| expected_row_count | INT | Expected number of event rows |
| expected_min_event_date | DATE | Earliest permitted event date |
| expected_max_event_date | DATE | Latest permitted event date |
| loaded_at | TIMESTAMP | Timestamp when the batch was loaded |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Referenced account identifier |
| account_status | VARCHAR(20) | Current account status |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique warehouse event identifier |
| batch_id | INT | Ingestion batch identifier |
| account_id | INT | Source account identifier |
| event_date | DATE | Business date of the event |
| amount | NUMERIC(12,2) | Event amount |
| source_record_key | VARCHAR(80) | Source-system record key |
| ingested_at | TIMESTAMP | Timestamp when the event was ingested |