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.
Output
- One row per validation failure, with
batch_id, check_name, issue_count, and details
- Include only failed checks and sort by validation priority, then
check_name
Output details
- Select the batch with the greatest
loaded_at, breaking ties by greatest batch_id.
- Sort by ascending validation priority, then ascending
check_name.
- Check exact
check_name values: row_count_mismatch, duplicate_source_record_key, null_account_id, unknown_account_id, invalid_event_date, and negative_amount.
- Count duplicate
source_record_key occurrences beyond the first, excluding NULL keys, and report only checks with positive issue_count.
- Treat event dates equal to the expected minimum or maximum as valid; treat
NULL dates as invalid and count only amounts less than 0.
- Use the exact details text templates:
Expected N rows, found M; N duplicate source record(s); and corresponding N event(s) ... messages.