It's 7:43 AM on a Tuesday. Your stakeholders are starting their day, pulling up dashboards, and getting ready for the weekly revenue review. Somewhere in the night, a dbt model silently failed — not loudly, not with a crash, but with a subtle schema drift that let bad data flow downstream. The orders table is missing three days of transactions. The marketing attribution numbers are wrong. Nobody knew until someone started asking questions in Slack.
This scenario isn't hypothetical. It's the default state of most dbt pipelines without deliberate observability. dbt is excellent at transforming data, but by itself it tells you almost nothing about whether your data is trustworthy after it runs. A green checkmark on a dbt run means the SQL executed without errors — it says nothing about whether the results make sense. That gap between "the job ran" and "the data is correct" is where data quality incidents live.
By the end of this lesson, you'll know how to close that gap in a production environment. We'll instrument a realistic dbt project with Elementary for execution monitoring, anomaly detection, and alerting, and we'll layer in re_data for statistical drift detection. You'll walk away with a working alerting system, structured logging you can query, and a systematic approach to root cause analysis when something goes wrong.
What you'll learn:
You should be comfortable with:
not_null, unique, relationships)You don't need prior experience with Elementary or re_data.
Before we install anything, let's be precise about what you're actually monitoring when you monitor a dbt pipeline. There are three distinct failure modes, and they require different tools:
Execution failures — A model fails to compile or run. dbt surfaces these natively. The run exits non-zero, CI catches it, and you get an error in your logs. These are the easy failures.
Data quality failures — A model runs successfully but produces wrong results. Null values where there shouldn't be any. Row counts that drop 40% with no corresponding business event. Duplicate order IDs. dbt tests can catch these, but only if you've written tests that cover the failure mode, and only if you're doing something with the test results.
Silent drift failures — The data looks right but has slowly changed shape. A new upstream data source started sending dates in a different format. A column that used to have cardinality of 50 now has cardinality of 12,000 because someone changed an enum to a free-text field. These failures evade static tests because they represent change relative to a historical baseline, not violation of a fixed rule.
Elementary primarily handles the first two categories and does it exceptionally well. re_data is purpose-built for the third. Together, they give you comprehensive production observability.
Elementary works as a dbt package that runs as part of your project, combined with a Python CLI that reads the results and sends alerts. Start by adding it to your packages.yml:
# packages.yml
packages:
- package: elementary-data/elementary
version: 0.14.1
Run dbt deps to install it.
Next, Elementary needs its own profile target in your profiles.yml. It writes its observability tables to a dedicated schema, separate from your regular dbt output:
# profiles.yml
jaffle_shop:
target: dev
outputs:
dev:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
role: TRANSFORMER
database: ANALYTICS
warehouse: TRANSFORMING
schema: DBT_PROD
threads: 4
elementary:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
role: TRANSFORMER
database: ANALYTICS
warehouse: TRANSFORMING
schema: ELEMENTARY
threads: 4
The elementary target is where Elementary stores its run results, test results, and anomaly metrics. Keep it in its own schema so it doesn't pollute your production models.
Add an elementary block to your dbt_project.yml. This tells the package which profile target to use and enables the on-run-end hooks that capture run results:
# dbt_project.yml
name: jaffle_shop
version: '1.0.0'
config-version: 2
profile: jaffle_shop
models:
jaffle_shop:
staging:
+materialized: view
+tags: ['staging']
marts:
+materialized: table
+tags: ['mart']
on-run-end:
- "{{ elementary.on_run_end(elementary_target='elementary') }}"
That on-run-end hook is the linchpin. After every dbt run, Elementary collects metadata about every model execution, every test result, and every row count. Without it, the Python CLI has nothing to read.
Run your project once to initialize Elementary's tables:
dbt run --target dev
dbt test --target dev
You should now see a set of tables in your ELEMENTARY schema — dbt_run_results, dbt_test_results, dbt_models, and several others. These are the raw material for your monitoring dashboard and alerts.
The Elementary Python CLI is what reads those tables and sends alerts:
pip install elementary-data[snowflake]
# Or for BigQuery: pip install elementary-data[bigquery]
# Or for Redshift: pip install elementary-data[redshift]
Test the connection:
edr monitor --profiles-dir ~/.dbt --profile jaffle_shop --target elementary
If Elementary can connect and read its tables, you'll see a summary of your last run in the terminal. If you see authentication errors, double-check that your elementary target in profiles.yml uses credentials with read access to the ELEMENTARY schema.
Elementary's anomaly detection is only as good as your test coverage. Before we get to alerting, let's build a test suite that actually covers the failure modes you care about.
Most dbt projects have tests. Most of those tests are not_null and unique sprinkled on primary keys. That's necessary but not sufficient. Here's a more realistic test configuration for an orders mart:
# models/marts/orders/schema.yml
version: 2
models:
- name: fct_orders
description: "One row per order, with payment and fulfillment status"
columns:
- name: order_id
tests:
- not_null
- unique
- name: customer_id
tests:
- not_null
- relationships:
to: ref('dim_customers')
field: customer_id
- name: order_status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
- name: amount
tests:
- not_null
- dbt_utils.expression_is_true:
expression: ">= 0"
- name: ordered_at
tests:
- not_null
tests:
- dbt_utils.recency:
datepart: hour
field: ordered_at
interval: 25
That last test — recency — is often overlooked. It asserts that at least one row was inserted in the last 25 hours. If your orders pipeline stops feeding data, this test will catch it. Without it, a completely empty load is indistinguishable from a successful one.
This is where Elementary earns its place. These tests don't assert against a fixed value — they observe historical behavior and alert when something deviates significantly from the baseline.
# models/marts/orders/schema.yml (continued)
models:
- name: fct_orders
tests:
- elementary.volume_anomalies:
timestamp_column: ordered_at
sensitivity: 3
- elementary.freshness_anomalies:
timestamp_column: ordered_at
max_time_added_seconds: 86400
columns:
- name: amount
tests:
- elementary.column_anomalies:
column_anomalies:
- null_count
- zero_count
- average
- standard_deviation
timestamp_column: ordered_at
sensitivity: 3
- name: order_status
tests:
- elementary.column_anomalies:
column_anomalies:
- null_count
- null_percent
- distinct_count
timestamp_column: ordered_at
Let's unpack what sensitivity: 3 means. Elementary uses a Z-score-based approach — it calculates the mean and standard deviation of the metric over a training window (default: 14 days), then flags any value more than sensitivity standard deviations from the mean. A sensitivity of 3 is a good starting point for production: it's conservative enough to avoid constant noise, but catches genuine anomalies.
For volume_anomalies, you're saying: "Alert me if today's row count is more than 3 standard deviations away from the average row count over the last two weeks." This catches both sudden drops (upstream pipeline failure) and sudden spikes (double-loading, CDC fan-out bugs).
Production tip: Set
sensitivitylower (2.0–2.5) for business-critical tables likefct_ordersand higher (3.5–4.0) for noisier tables like event streams. You're calibrating the signal-to-noise ratio. If a table gets adjusted per seasonality or promotions, consider using a longer training window (training_period: {days: 30}) to smooth out those cycles.
Monitoring is only useful if the right people see failures immediately. Elementary's CLI handles alerting via a configuration file called config.yml, which you typically store in your dbt project root or in a dedicated monitoring config directory.
# elementary_config.yml
slack:
notification_webhook: "{{ env_var('ELEMENTARY_SLACK_WEBHOOK') }}"
channel_name: "#data-alerts"
alerts:
dbt_test_alerts: true
dbt_model_alerts: true
dbt_snapshot_alerts: true
# Control which severities trigger alerts
slack_alert_types:
- model_errors
- test_failures
- test_warnings
- schema_changes
# Suppress known acceptable issues during business hours
suppression_interval: 0
# Send different alert types to different channels
slack_alert_rules:
- alert_field: tags
alert_field_value: mart
slack_channel: "#data-alerts-critical"
- alert_field: tags
alert_field_value: staging
slack_channel: "#data-alerts-staging"
The slack_alert_rules section is powerful in production. You almost certainly don't want your on-call data engineer to get paged at 2 AM because a staging view had a test failure. Route mart-layer failures (which affect business users) to a critical channel, and staging failures to a lower-priority channel.
In production, you run the Elementary CLI after every dbt job completes:
edr monitor \
--profiles-dir /app/.dbt \
--profile jaffle_shop \
--target elementary \
--config-dir /app/elementary_config.yml \
--slack-webhook "$ELEMENTARY_SLACK_WEBHOOK" \
--slack-channel-name "#data-alerts"
If you're using GitHub Actions or a similar CI/CD system, add this as a step after your dbt run step:
# .github/workflows/dbt_production.yml (relevant section)
- name: Run dbt
run: |
dbt run --target prod --profiles-dir .
dbt test --target prod --profiles-dir .
env:
SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
- name: Send Elementary Alerts
if: always() # Run even if dbt steps failed
run: |
edr monitor \
--profiles-dir . \
--profile jaffle_shop \
--target elementary \
--slack-webhook "${{ secrets.ELEMENTARY_SLACK_WEBHOOK }}" \
--slack-channel-name "#data-alerts"
env:
SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
ELEMENTARY_SLACK_WEBHOOK: ${{ secrets.ELEMENTARY_SLACK_WEBHOOK }}
The if: always() condition is critical. If dbt run fails, GitHub Actions will skip subsequent steps by default. You need alerts to fire precisely when things go wrong.
Alerts tell you that something failed. Logs tell you why. Most teams treat dbt logs as an afterthought — something you dig through manually after the fact. You can do much better.
After every dbt run, dbt writes a run_results.json file to your target/ directory. This is machine-readable and contains execution times, row counts, and error messages for every node in the DAG. Elementary reads this file as part of its on-run-end hook, but you can also parse it directly for custom logging and alerting.
Here's a Python script you can run as part of your CI/CD pipeline to extract structured failure information:
# scripts/parse_run_results.py
import json
import sys
from pathlib import Path
from datetime import datetime
def parse_run_results(results_path: str = "target/run_results.json") -> dict:
"""
Parse dbt run_results.json and return a structured summary
suitable for logging or alerting.
"""
with open(results_path) as f:
results = json.load(f)
summary = {
"generated_at": results.get("metadata", {}).get("generated_at"),
"elapsed_time": results.get("elapsed_time"),
"failures": [],
"warnings": [],
"success_count": 0,
"failure_count": 0,
}
for result in results.get("results", []):
node_id = result.get("unique_id", "")
status = result.get("status")
execution_time = result.get("execution_time", 0)
# Extract model name from unique_id (e.g., "model.jaffle_shop.fct_orders")
node_parts = node_id.split(".")
node_type = node_parts[0] if node_parts else "unknown"
node_name = node_parts[-1] if len(node_parts) >= 3 else node_id
if status in ("error", "fail"):
failure_info = {
"node_id": node_id,
"node_type": node_type,
"node_name": node_name,
"status": status,
"execution_time_seconds": round(execution_time, 2),
"message": result.get("message", "No message available"),
"failures": result.get("failures"),
"timestamp": datetime.utcnow().isoformat(),
}
# Include compiled SQL for model errors (not test failures)
if node_type == "model":
compiled_path = Path("target/compiled") / Path(
result.get("compiled_code", "")[:500]
)
failure_info["adapter_response"] = result.get("adapter_response", {})
summary["failures"].append(failure_info)
summary["failure_count"] += 1
elif status == "warn":
summary["warnings"].append({
"node_id": node_id,
"node_name": node_name,
"message": result.get("message", ""),
})
else:
summary["success_count"] += 1
return summary
if __name__ == "__main__":
results_path = sys.argv[1] if len(sys.argv) > 1 else "target/run_results.json"
summary = parse_run_results(results_path)
print(json.dumps(summary, indent=2))
if summary["failure_count"] > 0:
print(f"\n❌ {summary['failure_count']} failures detected", file=sys.stderr)
sys.exit(1)
else:
print(f"\n✅ All {summary['success_count']} nodes succeeded")
Run this after dbt run or dbt test in your CI/CD pipeline. The JSON output is easy to pipe into a log aggregator (Datadog, Splunk, CloudWatch) or a custom Slack message with full failure context.
Elementary stores rich run history in your warehouse. Once you're in a production incident, the fastest path to root cause is often a direct SQL query against these tables.
This query gives you the last 7 days of test failures for your mart layer, which is usually the right starting point when a stakeholder reports bad data:
-- Query Elementary's test results for recent mart failures
SELECT
t.model_unique_id,
t.test_unique_id,
t.test_name,
t.status,
t.failures,
t.detected_at,
t.test_results_description,
t.test_params,
-- Calculate how long this test has been failing
DATEDIFF(
'hour',
MIN(t.detected_at) OVER (
PARTITION BY t.test_unique_id,
-- Group consecutive failures
t.status
),
t.detected_at
) AS hours_failing
FROM ELEMENTARY.dbt_test_results t
WHERE
t.detected_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND t.status IN ('fail', 'error')
AND t.model_unique_id LIKE 'model.jaffle_shop.fct_%' -- mart layer only
ORDER BY t.detected_at DESC;
This query is more useful than it looks. The hours_failing calculation tells you whether you're looking at a new failure or one that's been silently running for days. That distinction dramatically changes your root cause investigation — a brand-new failure suggests a recent deployment or upstream change, while a week-old failure suggests the alert system itself is broken.
Elementary is excellent at anomaly detection within a single run cycle. re_data fills a different gap: it maintains detailed statistical profiles of your tables over time and makes it easy to detect gradual changes that wouldn't trigger an anomaly test on any single day.
Add re_data to your packages.yml:
packages:
- package: elementary-data/elementary
version: 0.14.1
- package: re-data/re_data
version: 0.9.1
Run dbt deps again.
re_data needs a small configuration block in dbt_project.yml:
# dbt_project.yml (additions for re_data)
vars:
re_data:monitored: true
re_data:schemas:
- DBT_PROD # The schema where your production models live
re_data:time_window:
base_date: '{{ run_started_at }}'
days_back: 1
re_data monitors are configured in your model YAML files using the re_data_monitored meta property:
# models/marts/orders/schema.yml (re_data additions)
models:
- name: fct_orders
meta:
re_data_monitored: true
re_data_time_filter: ordered_at
columns:
- name: amount
meta:
re_data_monitored: true
- name: order_status
meta:
re_data_monitored: true
- name: customer_id
meta:
re_data_monitored: true
With re_data_time_filter set to ordered_at, re_data will profile each day's worth of orders independently, giving you a time-series view of your data's statistical properties rather than a single snapshot.
Add re_data's monitoring models to your production run. In your CI/CD pipeline:
# Run your dbt project as normal
dbt run --target prod --models tag:mart
# Run re_data's monitoring models
dbt run --target prod --models package:re_data
# Run dbt tests (including Elementary tests)
dbt test --target prod --models tag:mart
re_data populates a set of re_data_* tables in your warehouse — re_data_table_metrics, re_data_column_metrics, re_data_alerts, and others. These tables are designed to be queried by the re_data UI or directly via SQL.
Here's where re_data shines. This query identifies columns where the null rate has changed significantly over the last 30 days — a classic silent drift pattern that indicates upstream schema changes:
-- Detect null rate drift over the last 30 days
WITH daily_null_rates AS (
SELECT
table_name,
column_name,
time_window_start::DATE AS metric_date,
nulls_percent,
AVG(nulls_percent) OVER (
PARTITION BY table_name, column_name
ORDER BY time_window_start
ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
) AS rolling_14d_avg_null_rate,
STDDEV(nulls_percent) OVER (
PARTITION BY table_name, column_name
ORDER BY time_window_start
ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
) AS rolling_14d_stddev
FROM re_data.re_data_column_metrics
WHERE
table_name IN ('fct_orders', 'fct_customer_ltv', 'dim_customers')
AND time_window_start >= DATEADD('day', -30, CURRENT_DATE())
AND nulls_percent IS NOT NULL
),
drift_candidates AS (
SELECT
*,
ABS(nulls_percent - rolling_14d_avg_null_rate) /
NULLIF(rolling_14d_stddev, 0) AS z_score
FROM daily_null_rates
WHERE rolling_14d_stddev > 0 -- Exclude perfectly stable columns
)
SELECT
table_name,
column_name,
metric_date,
ROUND(nulls_percent, 2) AS todays_null_pct,
ROUND(rolling_14d_avg_null_rate, 2) AS baseline_null_pct,
ROUND(z_score, 2) AS z_score
FROM drift_candidates
WHERE
z_score > 3
AND metric_date = CURRENT_DATE() - 1
ORDER BY z_score DESC;
If this query returns rows, you have columns whose null rates have deviated more than 3 standard deviations from their 14-day baseline. That's the kind of signal that often precedes a full data quality incident by 1–2 days — catching it here gives you time to investigate before stakeholders are affected.
You've got an alert. Something failed. Here's how to work through it efficiently.
Your first question is always: what kind of failure is this?
dbt_run_results for the error message. These usually have explicit SQL errors that point directly to the problem.dbt_test_results for which test failed and what the failure count was. A not_null failure on order_id is very different from a volume_anomalies failure.dbt's lineage graph is your most powerful RCA tool. When fct_orders fails, you need to know whether the failure originated in fct_orders itself or in one of its 12 upstream dependencies.
# Check only the failed model and its direct parents
dbt test --models "+fct_orders" --target prod
# Compile the failed model to inspect the SQL
dbt compile --models fct_orders --target prod
cat target/compiled/jaffle_shop/models/marts/orders/fct_orders.sql
If the upstream models pass their tests but fct_orders fails, the logic in fct_orders itself is likely the culprit. If upstream models are also failing, you've found the root source.
The most underused RCA technique is baseline comparison. Elementary stores historical run results, so you can directly compare today's broken run against yesterday's successful one:
-- Compare today's row counts against yesterday for all mart models
SELECT
today.node_name,
yesterday.rows_affected AS yesterday_rows,
today.rows_affected AS today_rows,
ROUND(
(today.rows_affected - yesterday.rows_affected)::FLOAT /
NULLIF(yesterday.rows_affected, 0) * 100,
1
) AS pct_change
FROM (
SELECT
node_name,
rows_affected,
generated_at::DATE AS run_date
FROM ELEMENTARY.dbt_run_results
WHERE
generated_at::DATE = CURRENT_DATE()
AND node_type = 'model'
AND status = 'success'
) today
JOIN (
SELECT
node_name,
rows_affected,
generated_at::DATE AS run_date
FROM ELEMENTARY.dbt_run_results
WHERE
generated_at::DATE = CURRENT_DATE() - 1
AND node_type = 'model'
AND status = 'success'
) yesterday ON today.node_name = yesterday.node_name
WHERE ABS(pct_change) > 10 -- Flag models with >10% row count change
ORDER BY ABS(pct_change) DESC;
A 60% drop in fct_orders rows combined with a 0% change in stg_orders is diagnostic: the staging data is fine but something in the transformation logic is filtering out rows. A 60% drop that propagates from stg_orders all the way through the DAG means the source data didn't load.
Many failures originate in the source systems, not in your dbt models. dbt's source freshness command checks whether your raw tables have been updated recently:
dbt source freshness --target prod
If sources are stale, you've found your root cause. The fix is upstream (in the ingestion layer, not in dbt), and your stakeholders need to know that timeline.
Important: Add
source freshnessas a step beforedbt runin your production pipeline, and configure it to fail fast if sources are stale. Running a full dbt transformation on stale source data is a waste of compute and produces confidently wrong results.
You now have the conceptual framework. Let's build it. This exercise assumes you have a working dbt project targeting Snowflake (adjust credentials for your warehouse).
Objective: Instrument an existing fct_orders model with Elementary anomaly detection, configure Slack alerting, and run a simulated root cause analysis.
packages.yml and run dbt deps.on-run-end hook to dbt_project.yml.elementary target to your profiles.yml pointing to a dedicated ELEMENTARY schema.dbt run --target prod and verify that Elementary tables appear in your ELEMENTARY schema.Add the following tests to your fct_orders schema YAML. After adding them, run dbt test --target prod and note the baseline period message (Elementary will tell you it's building its training window):
tests:
- elementary.volume_anomalies:
timestamp_column: ordered_at
sensitivity: 3
training_period:
days: 14
- elementary.freshness_anomalies:
timestamp_column: ordered_at
Add a filter to your fct_orders model that excludes the last 2 days of data, run dbt run, then run dbt test. You should see the volume_anomalies test fail (assuming your training window has data to compare against). Now run edr monitor and verify the Slack alert arrives.
Remove the artificial filter, re-run, and verify the test passes again.
After the simulated failure, run the baseline comparison query from the RCA section against your Elementary tables. Verify that you can identify fct_orders as the model with the anomalous row count change, and trace it to the source by checking whether stg_orders showed a similar change (it shouldn't, since you only modified fct_orders).
"Elementary tables exist but are always empty"
The on-run-end hook is probably not firing. Check that your dbt_project.yml uses the exact macro syntax: {{ elementary.on_run_end(elementary_target='elementary') }}. A common mistake is omitting the elementary_target argument, which causes the hook to write to your default target schema instead of the Elementary schema.
"Anomaly tests always fail, even for healthy data"
Your training window is too short. Elementary needs at least 7 days of run history to establish a meaningful baseline. If you've just added anomaly tests, expect false positives for the first week. Set sensitivity: 4 temporarily while the training window fills up, then reduce it once you have 14+ days of history.
"I'm getting alerts but the failure already resolved itself"
You're running edr monitor too long after the dbt run completed. Run edr monitor immediately after dbt test in your pipeline. If you're using an orchestrator like Airflow or Prefect, make it a dependent task that runs as soon as the test task completes.
"re_data metrics tables are empty after dbt run"
re_data's monitoring models need to be explicitly selected in your run command. Run dbt run --models package:re_data separately after your main project run, or add re_data models to your production run selector.
"Volume anomaly triggered but the business says data is fine"
You've hit a seasonality problem. A promotion, holiday, or product launch created a genuine volume spike that's outside your 14-day training window's normal range. This is expected behavior — add a comment to the alert investigation, and consider increasing sensitivity for this specific model or adding a longer training_period. You can also add exclude_final_results: true to suppress the alert for a specific run if you know the cause in advance.
"dbt test fails but Elementary doesn't send an alert"
Check that your edr monitor command runs after both dbt run and dbt test complete. Elementary reads test results from the database (populated by the on-run-end hook), not from the local target/ directory. If tests fail before the run completes, the hook may not have captured all results. Separate your dbt run and dbt test steps explicitly rather than running them as a single command.
You now have a production-grade monitoring stack for dbt pipelines. Here's what you've built:
run_results.json parsing and direct SQL queries against Elementary's tablesThe single highest-leverage thing most teams skip is the dbt source freshness check before the main run. Add that first — it prevents a whole class of confidently-wrong-result incidents that are impossible to catch after the fact.
Where to go from here:
--slack-webhook flag is the easiest path, but the underlying tables are yours. Build PagerDuty integrations, JIRA ticket creation, or incident dashboards in Metabase using the same Elementary and re_data tables you've been querying.alert_description config.Learning Path: Modern Data Stack