dbt-anomaly-detector

A lightweight dbt package that uses Z-score statistics to detect anomalies in your data warehouse metrics over time.

Open-source data observability that runs entirely inside your warehouse — no external SaaS required.

How It Works

capture_metrics (macro)          detect_anomalies (SQL/Python)    active_alerts (SQL)
────────────────────────         ────────────────────────────     ───────────────────
Computes row counts,       →     Reads metric history,       →    Filters to warning
null rates, uniqueness,          applies Z-score anomaly           & critical alerts
numeric stats per model          detection per metric               for investigation
                                                              →
Appends to                       Classifies severity:              anomaly_summary (SQL)
metric_snapshots table           info / warning / critical         Aggregated dashboard view

Features

• Dual detection engine — pure SQL (default, all warehouses) or Python (Snowflake/Databricks/BigQuery)
• 7 metrics out of the box — row count, null rate, uniqueness, mean, min, max, stddev
• Zero dependencies — no dbt-utils or external services required
• 5-minute setup — configure vars, capture metrics, detect anomalies

Supported Warehouses

Warehouse	SQL Engine	Python Engine
PostgreSQL	Yes	—
Snowflake	Yes	Yes (Snowpark)
Databricks	Yes	Yes (PySpark)
BigQuery	Yes	Yes (Dataproc)
Redshift	Yes	—
DuckDB	Yes	—

Requires dbt >= 1.6.0.

Installation

Add to your packages.yml:

packages:
  - git: "https://github.com/tripleaceme/dbt-anomaly-detector.git"
    revision: master

Then run:

dbt deps

Quick Start

1. Configure Which Models to Monitor

In your dbt_project.yml:

vars:
  dbt_anomaly_detector_monitors:
    - stg_customers
    - fct_orders
    - dim_products

2. Capture Metrics (Run on a Schedule)

# Using the var config above
dbt run-operation capture_metrics

# Or pass models directly
dbt run-operation capture_metrics --args '{models: ["stg_customers", "fct_orders"]}'

Run this daily (or on every dbt run) to build up metric history.

Minimum 5 snapshots required before anomaly detection activates.

3. Run Anomaly Detection

dbt run -s dbt_anomaly_detector

4. Query Results

-- Active alerts (warning + critical only)
SELECT * FROM active_alerts ORDER BY abs(z_score) DESC;

-- Full summary by model and metric
SELECT * FROM anomaly_summary;

-- All detected anomalies (including info)
SELECT * FROM detect_anomalies;

Detection Engines

SQL Engine (Default)

Works on all dbt-supported warehouses. Uses window functions and aggregate statistics — no Python runtime required.

This is the default engine and requires no additional configuration.

Python Engine (Optional)

Uses pandas and NumPy for anomaly detection. Requires a warehouse with Python model support (Snowflake, Databricks, or BigQuery).

To enable, add both the var and the model config in your dbt_project.yml:

# In your dbt_project.yml
vars:
  dbt_anomaly_detector_use_python: true

models:
  dbt_anomaly_detector:
    detect_anomalies_python:
      +enabled: true
    detect_anomalies:
      +enabled: false

Both engines use the same Z-score detection algorithm and produce identical output schemas. Choose Python if you plan to extend the detection logic with custom statistical methods.

Metrics Captured

Metric	Level	Description
row_count	Table	Total row count
null_rate	Column	Fraction of null values (0.0 – 1.0)
uniqueness	Column	Distinct value ratio (0.0 – 1.0)
column_mean	Column	Average value (numeric columns only)
column_min	Column	Minimum value (numeric columns only)
column_max	Column	Maximum value (numeric columns only)
column_stddev	Column	Standard deviation (numeric columns only)

Detection Method

Z-score analysis — the latest metric value is compared against the historical distribution:

• Info: |Z| >= 0.7 × sensitivity (default: |Z| >= 2.1)
• Warning: |Z| >= 1.0 × sensitivity (default: |Z| >= 3.0)
• Critical: |Z| >= 1.5 × sensitivity (default: |Z| >= 4.5)

Configuration

All configuration is via dbt vars in your dbt_project.yml:

Variable	Default	Description
dbt_anomaly_detector_monitors	[]	List of model names to monitor
dbt_anomaly_detector_database	target.database	Database for metric_snapshots table
dbt_anomaly_detector_schema	target.schema	Schema for metric_snapshots table
dbt_anomaly_detector_use_python	false	Use Python engine instead of SQL
anomaly_sensitivity	3.0	Z-score threshold (lower = more sensitive)
anomaly_min_history	5	Minimum snapshots before detection activates
anomaly_lookback_days	30	How far back to look for metric history

Output Models

Model	Description
detect_anomalies	Core detection — one row per flagged anomaly with Z-score and severity
anomaly_summary	Aggregated by model, metric, and severity — for dashboards
active_alerts	Warning + critical only, with deviation direction — for alerting

Output Columns (detect_anomalies)

Column	Description
detected_at	Timestamp of the anomalous metric snapshot
model_name	Name of the monitored dbt model
metric_name	Type of metric (row_count, null_rate, etc.)
column_name	Column name (null for table-level metrics)
metric_value	The flagged metric value
mean_value	Historical mean (excluding the latest value)
stddev_value	Historical standard deviation
z_score	Z-score (positive = above average, negative = below)
severity	info, warning, or critical
method	Detection method used (z_score)

Example: Automated Monitoring

Add to your dbt orchestration (Airflow, GitHub Actions, dbt Cloud):

# 1. Run your models
dbt run

# 2. Capture metrics for monitored models
dbt run-operation capture_metrics

# 3. Run anomaly detection
dbt run -s dbt_anomaly_detector

# 4. Check for critical alerts
# (query active_alerts and send to Slack/email/PagerDuty)

How It Compares

Feature	dbt-anomaly-detector	Elementary	Monte Carlo
Cost	Free	Free	$$$$
Setup	5 minutes	30 minutes	Days
Models	500+	100+	N/A (SaaS)
Detection	Z-score (SQL + Python)	Threshold (SQL)	ML (proprietary)
Dependencies	None	dbt-utils	External service
Warehouse support	All	Snowflake-first	N/A
Warehouse compute	Minimal	Moderate	N/A

License

MIT