Quick answer: Validate row counts, business-key uniqueness, required fields, schema expectations and source-to-target reconciliation at defined pipeline boundaries.
1. Completeness
Verify that expected input files or partitions arrived, and compare landed row counts with source totals when the source can provide them reliably.
2. Uniqueness
SELECT business_key, COUNT(*) AS copies
FROM staging.daily_batch
GROUP BY business_key
HAVING COUNT(*) > 1;3. Null and domain checks
Check required columns for nulls and ensure amounts, dates and status codes meet known business rules. Distinguish legitimately missing values from technical defects.
4. Schema validation
Detect unexpected new, missing or type-changed fields before they break downstream transformations. Decide whether to reject, quarantine or evolve a schema based on a documented contract.
5. Source-to-target reconciliation
Compare accepted, rejected and transformed rows, and capture a run ID with each result. Alerts need context: which source, which rule, observed value and intended threshold.
Operational principle
A dashboard is not a substitute for a clear response process. Give each failed check an owner, severity, mitigation procedure and documented threshold.