Data Quality: Alert When Missing Data
New feature: How does 'Insert 0s for missing values' work? provides an updated way to alert on missing data.
There are two popular scenarios for alerting with there is missing data or a data source fails to update. In both cases, we start by pulling in your source data. Then we create a metric to implement some logic and set an alert rule.
Values are missing on 8-21 and 8-27. If we create a metric on this data then nothing will be charted for those days. I want to be alerted when I'm missing data like this. If my data source returned a 0 then it would chart (and I could alert on it) so I'll have to insert the missing values in Metric Insights. The first step is to create a report and pull the source data into the Metric Insights Database.
Create a Report that pulls in your data. Use the data source for your source system and the appropriate data collection trigger (schedule).
Use the Dashboard Database so that we can query both the system's 'calendar_date' table and your report's table. This query only pulls data from 2015 and newer (the calendar table goes back to 1980).
SELECT c.calendar_date,coalesce(x.Value,0) as 'Value'
FROM calendar_day c
LEFT JOIN (
SELECT your_date, your_value as Value
FROM fred_report_data_segment_0.your_data_report) x
ON c.calendar_date = x.your_date
WHERE c.calendar_date >= '2015-01-01'
ORDER BY c.calendar_date
I have a Tableau view and I want to be alerted when it doesn't update. The strategy here is to create a report and pull in data from the data source (Tableau in this case but could be anywhere data is expected to load consistently).
Note: Using version 3.3's Single Existing Report Data Source (New). You can use the 'Existing Report' or 'Dashboard DB' to query the same data like this:
SELECT measurement_time, count(*)
GROUP BY 1
'measurement_time' is the snapshot date Metric Insights applies when saving the report as a MySQL table. The count represents the numbers of records Metric Insights pulls into the report. If the count is zero than no data has been loaded. If you have false alerts check the timing of data collection.
Remember the measurement_time column only has dates on a go-forward basis. When you create the metric you will only have one data point to start with. This chart tells is the report pulled in 89 records. That is good. All we care about is that there are records at all.