Your dashboard is green and the number is wrong: the SQL checks I schedule next to every metric

Broken dashboards often appear functional, but subtle issues can arise. Pipeline monitoring rarely detects these problems, as a job may run smoothly yet generate incorrect numbers. A set of simple SQL queries can serve as a reliable safeguard. Here's what to monitor:

1. Freshness: Verify that data arrives within an acceptable timeframe. If not, the dashboard should alert you. Use a time window matching the actual load schedule, such as 3 hours for nightly batches.

2. Volume comparison by weekday: Instead of checking the count against a fixed threshold, compare the current day's volume to the same weekday in the past few weeks. This helps account for weekend and holiday variations.

3. Grain consistency: Ensure the data model remains consistent. If a single row should represent an order, but due to a schema change, it now represents an order line, the check will reveal the discrepancy before any summing occurs.

4. Mapping table integrity: Watch for new plan, country, or campaign entries that don't match upstream. This can cause issues when breaking down data by category. Check the percentage of unmapped entries.

5. Reconciliation with a second source: Cross-reference critical data, such as orders against payments or app signups against the CRM. Compare the same time period from multiple data sources to ensure consistency.

Each check is designed to return rows only when an issue exists. Zero rows indicate everything is functioning correctly. This approach simplifies alerting, as a single row signifies a problem, providing both the affected day or key and a detailed alert message.