Regression Tests For Metric Changes

Note: The quick test is available to everyone. Only logged-in learners get saved progress.

Why this matters

Analytics Engineers ship models that power dashboards, experiments, and decisions. A small code change can silently shift key metrics like revenue, conversion, or active users. Regression tests for metric changes act as guardrails that block or flag unexpected jumps or dips before stakeholders see them.

• Protect business KPIs during model refactors and dependency updates.
• Catch subtle logic changes (e.g., filters, joins) that alter denominators.
• Increase confidence in automated deployments and scheduled runs.

Who this is for

• Analytics Engineers and BI Developers integrating CI/CD for data models.
• Data Analysts responsible for metric definitions and dashboard reliability.
• Data Scientists who need stable inputs for experiments and forecasts.

Prerequisites

• Comfort writing SQL (window functions, group by, date filtering).
• Basic understanding of business metrics and dimensions.
• Familiarity with data testing concepts (e.g., data quality vs metric tests).

Concept explained simply

A regression test for metric changes compares today’s metric to a trusted baseline and fails when the difference exceeds a tolerance. Think of it as a tripwire on your key numbers. If a change is intentional, you update the baseline or widen the tolerance. If it’s accidental, the test protects you by alerting or blocking the release.

Mental model

• Metric = number you care about (e.g., daily orders).
• Baseline = what ‘normal’ looks like (e.g., rolling 28-day average).
• Tolerance = acceptable movement (e.g., ±3% or within 2 standard deviations).
• Scope = total and key segments (e.g., overall + by country).
• Action = fail/block or warn, plus owner and remediation steps.

Worked examples

-- Inputs: fact_orders with order_date, revenue
with daily as (
  select order_date, sum(revenue) as daily_revenue
  from fact_orders
  group by 1
), baseline as (
  select d1.order_date,
         avg(d2.daily_revenue) as avg_28d
  from daily d1
  join daily d2 on d2.order_date between d1.order_date - interval '28 day' and d1.order_date - interval '1 day'
  group by 1
), joined as (
  select d.order_date,
         d.daily_revenue,
         b.avg_28d,
         case when b.avg_28d = 0 then null
              else (d.daily_revenue - b.avg_28d) / b.avg_28d end as pct_diff
  from daily d
  join baseline b using(order_date)
)
select order_date,
       daily_revenue,
       avg_28d,
       pct_diff,
       case when abs(pct_diff) > 0.03 then 1 else 0 end as alert
from joined
where order_date = current_date;

with daily as (
  select order_date, count(*)::float as orders
  from fact_orders
  group by 1
), stats as (
  select d1.order_date,
         avg(d2.orders) as mean_28d,
         stddev_samp(d2.orders) as sd_28d
  from daily d1
  join daily d2 on d2.order_date between d1.order_date - interval '28 day' and d1.order_date - interval '1 day'
  group by 1
), joined as (
  select d.order_date,
         d.orders,
         s.mean_28d,
         s.sd_28d,
         s.mean_28d - 2*s.sd_28d as lower_bound,
         s.mean_28d + 2*s.sd_28d as upper_bound
  from daily d
  join stats s using(order_date)
)
select order_date,
       orders,
       lower_bound,
       upper_bound,
       case when orders < lower_bound or orders > upper_bound then 1 else 0 end as alert
from joined
where order_date = current_date;

-- Track active users overall and by country
with daily as (
  select activity_date, country, count(distinct user_id) as dau
  from fact_activity
  group by 1,2
), roll as (
  select d1.activity_date, d1.country,
         avg(d2.dau) as mean_28d
  from daily d1
  join daily d2 on d2.country = d1.country
                and d2.activity_date between d1.activity_date - interval '28 day' and d1.activity_date - interval '1 day'
  group by 1,2
)
select d.activity_date, d.country, d.dau, r.mean_28d,
       case when r.mean_28d = 0 then null
            else abs(d.dau - r.mean_28d)/r.mean_28d end as pct_diff,
       case when abs(d.dau - r.mean_28d)/nullif(r.mean_28d,0) > 0.1 then 1 else 0 end as alert
from daily d
join roll r using(activity_date, country)
where d.activity_date = current_date
order by alert desc, pct_diff desc;

How to implement (step-by-step)

Exercises

Try these hands-on tasks. They mirror the graded items below.

Exercise 1 — SQL regression test for conversion_rate

Write a SQL query that flags an alert (1/0) when today’s conversion_rate deviates from the last 28 days by more than 2 standard deviations. Assume a table fact_sessions with session_date, sessions and a table fact_signups with signup_date, signups. Compute conversion_rate as signups/sessions per day.

• Expected output: one row for current_date with columns conversion_rate, mean_28d, sd_28d, lower_bound, upper_bound, alert.

Exercise 2 — Threshold design plan

Create a short plan for guarding daily_active_users (DAU) with strong weekly seasonality. Specify baseline, tolerance, segment coverage, and actions.

• Expected output: a concise decision note with chosen window, bounds, key segments, and fail/warn logic.

Common mistakes and self-check

• Using absolute thresholds on metrics with different scales. Self-check: Would 100 be meaningful if your baseline is 1,000 vs 1,000,000?
• Including current day in the baseline. Self-check: Confirm your window uses only prior days.
• Testing only topline. Self-check: Add at least 2 critical dimensions.
• Freezing tolerances forever. Self-check: Review and tune monthly or after seasonality shifts.
• Alert without owner or action. Self-check: Who will triage? What’s the rollback path?

Practical projects

• Guardrails bundle: Implement 3 metric tests (revenue, conversion_rate, DAU) with segment-level checks and a pass/fail summary table.
• Baseline snapshotter: Build a daily job that stores baseline stats for selected metrics and enables quick “intentional change” re-basing.
• Flakiness reducer: Track test outcomes for 30 days and adjust tolerances to cut false positives by 50% while keeping true positives.

Learning path

1. Revisit metric definitions (document filters and calculations).
2. Implement a simple percent-change guardrail for one metric.
3. Add volatility-aware bounds (std dev or MAD).
4. Expand to key segments and add pass/fail summary.
5. Automate scheduling and notifications.
6. Review performance and tune thresholds.

Next steps

• Turn your best test into a reusable template for new metrics.
• Add rate-limiting or cooldowns to avoid alert fatigue.
• Pair metric guardrails with data quality tests (nulls, duplicates) for full coverage.

Mini challenge

Your A/B testing team notices conversion volatility during marketing pushes. Design a guardrail using an exponentially weighted moving average (EWMA) baseline instead of a simple rolling mean. Outline the decay factor and when to re-base after intentional launches.

Quick Test

Take the quick test below. Remember: everyone can take it; sign in to save your progress.