IntermediateSingle prompt
01
Anomaly Detection for Data Pipelines
Implement automated anomaly detection for data metrics in this pipeline. Metrics to monitor: {{metrics}} (row counts, revenue, event counts, null rates) Historical data availabl...
Prompt text
Implement automated anomaly detection for data metrics in this pipeline.
Metrics to monitor: {{metrics}} (row counts, revenue, event counts, null rates)
Historical data available: {{history}} (weeks of data)
False positive tolerance: {{tolerance}} (strict vs lenient)
1. Statistical anomaly detection approaches:
Z-score (simple, works for normally distributed metrics):
anomaly if |value - rolling_mean| / rolling_std > threshold
threshold = 3 for strict (0.3% false positive), 2 for lenient (5% false positive)
IQR-based (robust to outliers):
Q1 = 25th percentile, Q3 = 75th percentile, IQR = Q3 - Q1
anomaly if value < Q1 - 1.5 × IQR OR value > Q3 + 1.5 × IQR
Percentage deviation from rolling average:
anomaly if |value - rolling_avg_7d| / rolling_avg_7d > 0.3
-- 30% deviation from the 7-day average
Works well for business metrics with weekly seasonality
2. SQL implementation (row count anomaly detection):
WITH daily_counts AS (
SELECT DATE(created_at) AS d, COUNT(*) AS row_count
FROM orders
WHERE DATE(created_at) >= CURRENT_DATE - 30
GROUP BY 1
),
stats AS (
SELECT d, row_count,
AVG(row_count) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS avg_7d,
STDDEV(row_count) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS std_7d
FROM daily_counts
)
SELECT d, row_count, avg_7d,
ABS(row_count - avg_7d) / NULLIF(std_7d, 0) AS z_score
FROM stats
WHERE ABS(row_count - avg_7d) / NULLIF(std_7d, 0) > 3;
3. Seasonality adjustment:
- Day-of-week seasonality: compare to the same day of week in prior weeks
- Holiday effects: create a holiday flag and exclude from the baseline
- Elementary handles seasonality automatically using STL decomposition
4. Alert routing:
- Z-score 2-3: warn in Slack; no action required unless confirmed by an analyst
- Z-score > 3: alert to on-call; requires acknowledgment within 15 minutes
- Consecutive anomalies (2+ days): escalate to a data incident
Return: anomaly detection SQL, threshold calibration, seasonality handling, and alert routing rules.