Data Warehouse Experimentation

Data Warehouse Experimentation

A senior data scientist's playbook for running experiments natively out of BigQuery, Snowflake, or any modern data warehouse, with metric definitions in dbt and statistical analysis in SQL or Python.

Most companies that run experiments at scale use a dedicated platform. Statsig, Optimizely, LaunchDarkly with experimentation, PostHog, Amplitude Experiment. The platforms are good. They handle assignment, instrumentation, and analysis in one product, and the SQL-savvy data team does not have to reinvent the variance reduction wheel.

There is a different operational model that mature data teams increasingly choose: warehouse-native experimentation. Assignment happens in code or via feature flags. Exposure events fire to the warehouse like any other event. Metrics are defined as dbt models. Statistical analysis runs as SQL or in a Python notebook against warehouse data. The "experiment platform" is just your existing data stack.

This skill covers when warehouse-native is the right call, the architecture, and the specific techniques that make it work: assignment patterns, exposure logging discipline, metric definitions in dbt, t-tests and CUPED in SQL, sequential testing, and the pitfalls that take down homegrown setups.

When to use this skill: deciding between platform vs warehouse-native, building a warehouse-native experiment infrastructure, auditing an existing one, or running a specific experiment when the platform of record cannot handle a custom metric or segmentation.

---

What this skill is for

This skill spans the operational execution model for warehouse-native experimentation. It does not replace the methodology and interpretation skills; it composes with them.

• experiment-design covers methodology: hypotheses, sample size, randomization unit, primary metric. Tool-agnostic. Read it first to design the experiment correctly regardless of where it runs.
• experimentation-analytics covers interpretation: confidence intervals, p-values, effect size, decision frameworks. Tool-agnostic. Read it when results land.
• experimentation-platform-orchestrator covers the platform-vs-warehouse decision in detail. Read it to decide whether to use a platform or this skill.
• feature-flagging covers assignment infrastructure when not running through a platform. Read it for the flag-management discipline that this skill assumes.
• This skill (data-warehouse-experimentation) covers the operational execution: SQL-based assignment, exposure logging, metric definitions in dbt, statistical analysis in SQL or Python, variance reduction, sequential testing.

The distinction is between "what to do" (the methodology and interpretation skills) and "how to do it without a vendor platform" (this skill). Read this skill after you have decided warehouse-native is the right call. If you are still deciding, start with experimentation-platform-orchestrator.

---

When warehouse-native is the right call

Six factors push the decision toward warehouse-native.

1. Cost at volume. Platforms charge per MAU or per event. At 10K MAU the platform is cheap; at 1M MAU the bill becomes a real budget item. Warehouse-native runs on infrastructure you already pay for. 2. Custom metrics. If your primary metric is a complex business metric (revenue with refund-aware logic, cohort LTV, retention bracket, multi-event composites), platforms can struggle. Warehouse-native expresses any metric you can write in SQL. 3. Custom segmentation. Enterprise customers, account-tier crosscuts, complex behavioral segments. Platforms have segmentation features; the depth varies. dbt models compose without limit. 4. Trust requirements. Regulated industries (healthcare, finance, government) need full transparency into the math. Warehouse-native gives you every step of the calculation auditable in SQL. 5. Existing data team strength. If you have data engineers and data scientists, you have most of the infrastructure. Adding experimentation discipline on top costs less than adopting a new platform. 6. Iteration on metric definitions. Platforms ship metric updates on their own cadence. Warehouse-native iterates as fast as your dbt deployments.

Five factors push toward platform.

1. Frontend visual experiments. Optimizely's bread and butter. Variant code injected via a script tag, with WYSIWYG editing. 2. Sub-week iteration speed. Some platforms set up an experiment in 30 minutes; warehouse-native often takes a day or more for the first run of a new metric pattern. 3. Teams without strong data infrastructure. If you do not have a warehouse, dbt, and analysts, do not start with warehouse-native. The platform is the right call. 4. Mobile experimentation. SDK-based assignment with offline support is the platform's job, not the warehouse's. 5. Out-of-the-box sequential testing with strict guarantees. Statsig and Eppo ship mSPRT with calibrated alpha-spending. Building this in-house is real work.

Detail and a decision tree in `references/warehouse-vs-platform-decision.md`. Many mature teams use both; warehouse-native for the hard cases, platform for fast iteration on standard experiments.

---

The architecture

Four components, in order of data flow.

1. Assignment. How users get bucketed into variants. Hash function, feature flag, or randomized assignment table. 2. Exposure logging. A discrete event fired the first time a user is exposed to the experiment, written to the warehouse like any other event. 3. Metric definitions. SQL queries (or dbt models) that compute the primary and secondary metrics from warehouse events. 4. Analysis. Statistical computation in SQL or Python that joins exposure to metrics and produces effect estimates with confidence intervals.

The flow. User visits the product. Assignment determines the bucket (control or treatment). If the user is exposed to the variant (sees the treatment-specific behavior), an exposure event fires to the warehouse. The user takes actions, generating metric events to the same warehouse. At analysis time, exposure joins to metrics on the assignment unit (typically user_id); the analysis computes lift and produces a decision.

The exposure-event pattern is critical. Without it you can compute only an "intent-to-treat" analysis (everyone assigned, regardless of whether they saw the variant). With it you compute the "exposed" analysis on the population that actually experienced the variant. The latter is usually what you want, especially when the variant only affects a subset of the assigned users (e.g., users who reached a specific page).

---

Assignment patterns

Three approaches.

Deterministic hash assignment. The default for warehouse-native.

MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, 'exp_button_color_v1'))), 100) < 50

The salt ('exp_button_color_v1') ensures different experiments produce uncorrelated assignments for the same user. Reproducible (same input always produces the same bucket), no service dependency, salt isolation across experiments. The assignment can be computed inline in any SQL query.

Feature flag assignment. Rely on a feature flag service (LaunchDarkly, Statsig flags, Unleash, internal) to do bucketing; the warehouse just records the assignment that the flag service chose.

-- Read assignment from the flag service's logs
SELECT user_id, variant_id, assigned_at
FROM flag_service.assignments
WHERE flag_key = 'exp_button_color_v1'

This works when the flag service is the source of truth for assignment and the warehouse mirrors the assignment table. Useful when assignment must respect flag-service rules (e.g., percentage rollouts, targeting rules) that are inconvenient to replicate in SQL.

Randomized assignment table. Pre-randomize users into a table at experiment start.

CREATE TABLE exp_button_color_v1_assignments AS
SELECT
  user_id,
  CASE WHEN RAND() < 0.5 THEN 'control' ELSE 'treatment' END AS variant_id
FROM dim_users
WHERE eligible = true;

Less common; useful when the eligibility set is fixed at experiment start and you want assignment to be deterministic and explicit (e.g., for compliance audit). The downside: new users joining mid-experiment are not in the table; either skip them or fall back to hash assignment.

The deterministic hash approach is the default for warehouse-native because it requires no service dependency and produces stable, auditable assignments. Detail in `references/assignment-and-exposure-patterns.md`.

---

The exposure log

The single most important discipline in warehouse-native experimentation.

Required exposure event schema:

Fire exposure exactly when the user has seen the variant-specific behavior. Not at page load. Not at session start. Not at app open.

The "delayed exposure" trap. If the variant only matters at button click and you fire exposure at page load, every page-load user enters the analysis whether or not they ever saw the variant. The control group includes users who never reached the button; the treatment group does too. The analysis dilutes the real effect.

Worked example. The treatment shows a new pricing page; the control shows the old one. Fire exposure when the pricing page loads, not when the user lands on the homepage. Users who never reach the pricing page are not exposed to either variant; they should not be in the analysis.

The "always-fire" trap. Some implementations fire exposure on every variant-specific interaction. The user clicks the button five times; exposure fires five times. The exposure log is now five times larger than it should be, and analysis tools that count distinct user_ids in exposure handle this correctly while tools that count rows do not.

The discipline. Fire exactly one exposure event per user per experiment, at the moment of first variant-specific exposure. Use a deterministic flag in the client (or a server-side cache) to enforce single-fire. Detail in `references/assignment-and-exposure-patterns.md`.

---

Metric definitions in dbt models

Defining metrics as dbt models gives you four things.

1. Version control on metric definitions. Every change to a metric is a git commit. The history is queryable. 2. Testability. dbt tests on the metric output catch regressions. 3. Composability. The same fct_orders model feeds the board dashboard, the experiment analysis, and the executive report. Aligned definitions, no drift. 4. Single source of truth. When the experiment says X and the board says Y, the answer is in the dbt model, not in two unrelated SQL files.

Pattern.

-- models/experiments/exp_metrics_revenue.sql
SELECT
  user_id,
  SUM(CASE WHEN refunded THEN 0 ELSE amount_cents END) AS net_revenue_cents,
  MIN(occurred_at) AS first_purchase_at
FROM {{ ref('fct_orders') }}
WHERE occurred_at >= '{{ var("experiment_start") }}'
GROUP BY user_id

The experiment analysis joins this to the exposure log on user_id and computes group means.

The variance discipline. The same metric definition is used in board dashboards AND in experiment analysis. No "experiment-specific revenue calculation" that is slightly different. Otherwise you get the "the experiment said the revenue lifted but the board did not move" problem, which is almost always a metric-definition mismatch.

The namespace pattern. Use exp_metrics_* for experiment-shaped models that group by user_id and produce one row per user. Use fct_* for the underlying fact tables that feed both metric models and dashboards. Detail in `references/metric-definitions-in-dbt.md`.

---

Statistical analysis in SQL

The basic two-sample Welch's t-test in SQL.

WITH metric_by_variant AS (
  SELECT
    e.variant_id,
    COUNT(*) AS n,
    AVG(m.net_revenue_cents) AS mean,
    VAR_SAMP(m.net_revenue_cents) AS variance
  FROM exposures e
  LEFT JOIN exp_metrics_revenue m USING (user_id)
  WHERE e.experiment_id = 'exp_button_color_v1'
  GROUP BY e.variant_id
)
SELECT
  control.mean AS control_mean,
  treatment.mean AS treatment_mean,
  treatment.mean - control.mean AS absolute_lift,
  (treatment.mean - control.mean) / NULLIF(control.mean, 0) AS relative_lift,
  -- Welch's t-statistic
  (treatment.mean - control.mean) /
    SQRT(treatment.variance / treatment.n + control.variance / control.n)
    AS t_statistic
FROM
  (SELECT * FROM metric_by_variant WHERE variant_id = 'control') control,
  (SELECT * FROM metric_by_variant WHERE variant_id = 'treatment') treatment

Convert the t-statistic to a p-value or confidence interval using a SQL function (BigQuery: a UDF; Snowflake: native or a stored procedure) or compute in Python on the result of the SQL query.

The SQL pattern is fine for simple t-tests on continuous metrics. For proportions tests, swap variance for p * (1 - p). For non-parametric tests (Mann-Whitney), the SQL gets ugly fast; switch to Python.

Anything more complex than a simple t-test (CUPED, bootstrap, doubly robust estimation, sequential testing) is easier in Python. Use SQL for the SUM-and-AVG aggregations; ship the result to Python for the statistical math. Detail in `references/statistical-analysis-templates.md`.

---

Statistical analysis in Python

The Python pattern, typically in a Jupyter or Hex notebook.

import pandas as pd
import numpy as np
from scipy import stats

# Pull aggregated data from the warehouse
df = warehouse.query("""
  SELECT user_id, variant_id, net_revenue_cents
  FROM exp_results
  WHERE experiment_id = 'exp_button_color_v1'
""")

control = df[df.variant_id == 'control'].net_revenue_cents
treatment = df[df.variant_id == 'treatment'].net_revenue_cents

# Welch's t-test
t, p = stats.ttest_ind(treatment, control, equal_var=False)

# Confidence interval on the mean difference
diff = treatment.mean() - control.mean()
se = np.sqrt(treatment.var() / len(treatment) + control.var() / len(control))
ci_low, ci_high = diff - 1.96 * se, diff + 1.96 * se

print(f"Lift: {diff:.2f} cents (95% CI: [{ci_low:.2f}, {ci_high:.2f}])")
print(f"p-value: {p:.4f}")

Python gives you access to the full statistical ecosystem (scipy, statsmodels, numpy) for techniques SQL cannot easily express.

The notebook pattern. One notebook per experiment, parameterized by experiment_id. Version-controlled in git or as Hex projects. Each notebook produces a written-up decision document at the end, archived in a queryable repository (Notion, GitHub markdown, or a dedicated experiment-results table in the warehouse).

Detail and bootstrap templates in `references/statistical-analysis-templates.md`.

---

Variance reduction: CUPED and beyond

The most powerful variance reduction technique for warehouse-native experimentation: CUPED (Controlled-experiment Using Pre-Experiment Data). Originally from Microsoft.

The intuition. If you can predict a user's metric behavior from pre-experiment data, you can subtract out that predicted variance, leaving a smaller residual to test on.

# Pre-experiment metric for each user (e.g., last 28 days revenue)
pre = pre_period_revenue(user_id)

# Theta is the regression coefficient of the metric on the pre-period
theta = np.cov(metric, pre)[0, 1] / np.var(pre)

# Adjusted metric
adjusted_metric = metric - theta * (pre - pre.mean())

Run the t-test on the adjusted metric instead of the raw metric. The mean is preserved (CUPED does not change the point estimate) but the variance is smaller, so the confidence interval is narrower.

CUPED typically reduces variance by 30 to 50 percent on engagement metrics. That is equivalent to running an experiment 1.5x to 2x longer for the same statistical power. Worth the engineering investment for any team running 5+ experiments per quarter.

Other variance reduction techniques.

• Stratification. Slice the analysis by a pre-experiment covariate (segment, region, device) and pool the per-stratum estimates. Useful when the covariate is strongly predictive of the metric.
• Regression adjustment. Fit an OLS regression with covariates; the residual analysis has lower variance. Generalizes CUPED to multiple covariates.
• Doubly robust estimation. Combines outcome modeling and propensity-score weighting. Useful in observational and quasi-experimental settings where randomization was imperfect. Outside the scope of typical A/B tests; pointer to academic references in the variance-reduction reference file.

Detail with worked examples in `references/variance-reduction-techniques.md`.

---

Pre-experiment power analysis

Before running, compute required sample size.

from statsmodels.stats.power import tt_ind_solve_power

# Solve for sample size given desired MDE
n_per_arm = tt_ind_solve_power(
    effect_size=0.05,  # Cohen's d
    nobs1=None,
    alpha=0.05,
    power=0.8
)

# Or solve for MDE given sample size
mde = tt_ind_solve_power(
    effect_size=None,
    nobs1=8000,
    alpha=0.05,
    power=0.8
)

The "we need 10x more users than we thought" lesson. Most underpowered experiments come from optimistic effect-size assumptions. The team designs the experiment expecting a 10% lift; the actual effect is 1%, undetectable at the planned sample size; the experiment runs forever or stops with an inconclusive result.

The fix. Use the historical distribution of past experiments' observed effects to set realistic MDE expectations. If the median observed effect across the last 30 experiments is 0.5%, plan for a 0.5% MDE on new experiments. The optimism asymmetry is real; correcting it requires looking at the actual distribution of effects, not the wished-for distribution.

Detail in `references/power-analysis-calculations.md`.

---

Sequential testing patterns

The "peeking" problem. Looking at experiment results before completion inflates the false-positive rate. The naive solution is "do not peek." The practical solution is sequential testing methods that allow valid early stopping.

Three approaches.

• mSPRT (mixture Sequential Probability Ratio Test). Used by Optimizely and Statsig. Provides an always-valid p-value that survives peeking. Implementation in Python via statsmodels or custom code; not natively in SQL.
• Always-Valid Inference with confidence sequences. Howard et al. Confidence intervals that are valid at any sample size. Implementation requires careful Python; not for the data team that has not read the paper.
• Group sequential designs (O'Brien-Fleming boundaries). Pre-specified interim analysis points with calibrated alpha-spending. The classic frequentist approach.

For warehouse-native, the practical recommendation is mSPRT in Python. Document the alpha-spending function used. Train one team member on the math; do not rely on a black-box implementation.

The honest version. If you do not have someone on the team who understands sequential testing math, just do not peek. Pre-register sample size, run to completion, analyze once. Sequential testing is statistically correct only when implemented correctly; an incorrect implementation is worse than no peeking discipline at all.

Detail in `references/sequential-testing-patterns.md`.

---

Common pitfalls

Eleven patterns recur in warehouse-native experimentation. Detail in `references/common-pitfalls.md`.

• "Our exposure log fires at page load." Should fire when the variant-specific behavior is shown, not at page load. The "delayed exposure" trap dilutes the effect.
• "We see lift in control, not treatment." Assignment-hash collision or salt reuse. Audit the salt; check that different experiments produce different bucket assignments for the same user.
• "P-value is 0.04, we are shipping." Probably underpowered plus multiple comparisons plus peeking. Compute the test at the planned sample size only; correct for multiple secondary metrics.
• "Experiment shows 30% lift." Almost certainly a bug. Effects that big rarely exist; first action is to audit the exposure log and metric definitions before celebrating.
• "Treatment users are different." Sample ratio mismatch (SRM). The assignment hash is broken or the exposure log is biased. Check the SRM before computing any metric.
• "We cannot reproduce yesterday's number." Non-deterministic queries (window functions without explicit ORDER BY, sampling without a seed) or floating-point issues in aggregations. Make queries deterministic; document the random seed if any.
• "Custom metric definition disagrees with the board metric." Bad. Align them by using the same dbt model. Otherwise nobody trusts either number.
• "We never finished the experiment." Pre-register stop criteria; honor them. Experiments that drift indefinitely waste team time and produce ambiguous decisions.
• "iOS users converted 3x in treatment." Segment effect or instrumentation bug. Check if iOS instrumentation differs from the rest. Beware over-claiming on small segments.
• "It worked on phase 1, broke on phase 2." Simpson's paradox from cohort mix shift. The aggregate trend reverses when the underlying segments are weighted differently across phases.
• "Statistical significance but tiny effect." Large sample inflated power. The p-value is below 0.05 but the effect is 0.3%. Consider practical significance: is 0.3% worth shipping?

---

The framework: 12 considerations for warehouse-native experimentation

When designing or running a warehouse-native experiment, walk these 12 considerations.

1. Platform vs warehouse decision. Cost, custom metrics, segmentation, trust, team strength. Read experimentation-platform-orchestrator if undecided. 2. Assignment unit. User, account, session, device. Pick once at experiment start and stick to it. 3. Assignment salt. Unique per experiment to prevent correlation with prior experiments. Document the salt convention. 4. Exposure logging discipline. Fire when the variant matters, not at page load. One exposure event per user per experiment. 5. SRM check. Sample ratio mismatch indicates assignment bugs. Check before computing any metric. 6. Metric definition reuse. Same dbt model for board and experiment. No experiment-specific calculations that drift from canonical metrics. 7. Pre-experiment power analysis. Realistic MDE based on the historical distribution of past observed effects. 8. Variance reduction. CUPED for engagement metrics. 30 to 50 percent variance reduction is worth the engineering for any team running 5+ experiments per quarter. 9. Statistical method. Welch's t-test as default. Bootstrap for skewed distributions. Doubly robust estimation for quasi-experiments. 10. Sequential testing. mSPRT or stop-at-N. Document alpha-spending. If you cannot implement correctly, do not peek. 11. Multiple comparisons. Bonferroni or Benjamini-Hochberg correction across secondary metrics. 12. Decision documentation. Write the result up. Archive in a queryable repository. The next experiment will benefit from the institutional memory.

The output of the framework is an experiment record. Pre-registered sample size and stop criteria, the assignment salt, the exposure log specification, the dbt metric model, the analysis notebook, and a written-up decision. The record lives in version control or in a dedicated experiment-tracking system; the analysis is reproducible from the record.

---

Reference files

• `references/warehouse-vs-platform-decision.md` - When each operational model is the right call. Cost considerations at different scales. Hybrid patterns. Migration patterns.
• `references/assignment-and-exposure-patterns.md` - Hash assignment SQL templates for BigQuery and Snowflake. Salt naming conventions. Exposure event schema. SRM check SQL.
• `references/metric-definitions-in-dbt.md` - dbt model patterns for experiment metrics. Reusing fct models. The exp_metrics namespace. Versioning.
• `references/statistical-analysis-templates.md` - SQL and Python templates for Welch's t-test, proportions test, Mann-Whitney, bootstrap. Notebook structure.
• `references/variance-reduction-techniques.md` - CUPED math and Python implementation with worked example. Stratification. Regression adjustment. Doubly robust estimation primer.
• `references/power-analysis-calculations.md` - MDE math. Sample size calculations. Calibrating effect-size assumptions from historical experiments.
• `references/sequential-testing-patterns.md` - mSPRT, confidence sequences, group sequential designs. Honest framing on when to peek.
• `references/common-pitfalls.md` - Eleven failure patterns with diagnoses and fixes.

---

Closing: the build-vs-buy decision is real

Warehouse-native experimentation is powerful but expensive in engineering time. A first-year experimentation team should almost always start with a platform; the platform handles 90 percent of cases and lets you focus on hypotheses, not infrastructure. The team that graduates to warehouse-native does so because their volume, custom metric needs, or trust requirements outgrew what platforms offer.

If you are building warehouse-native because "platforms cost too much" without first running the math: you are underestimating the cost of your team's engineering time. The platform fee that looks expensive on a procurement form is often cheaper than three months of a data engineer's time spent reinventing CUPED.

If you are building it because the platform cannot handle your specific needs: you are probably right and the investment will pay back. Platforms are general; your business is specific. The custom metric the platform cannot express is often the metric that matters most for your decisions.

Honest middle ground: many mature teams use both. The platform for fast iteration on standard experiments where time-to-result matters more than custom depth. Warehouse-native for the hard cases where the platform's metric library or segmentation cannot reach. The hybrid is operationally complex; document the rule for which experiments go where, and revisit annually.

Assignment and exposure patterns

Hash assignment SQL templates. Salt naming conventions. Exposure event schema. The delayed-exposure trap. Sample ratio mismatch (SRM) check.

---

Hash assignment SQL templates

BigQuery

-- 50/50 split for experiment exp_button_color_v1
SELECT
  user_id,
  CASE
    WHEN MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, 'exp_button_color_v1'))), 100) < 50
      THEN 'control'
    ELSE 'treatment'
  END AS variant_id
FROM dim_users;

Snowflake

-- 50/50 split using SHA1 hash and modulo
SELECT
  user_id,
  CASE
    WHEN MOD(ABS(HASH(CONCAT(user_id, 'exp_button_color_v1'))), 100) < 50
      THEN 'control'
    ELSE 'treatment'
  END AS variant_id
FROM dim_users;

Postgres

-- 50/50 split using md5 hash; convert hex to integer
SELECT
  user_id,
  CASE
    WHEN ('x' || SUBSTRING(MD5(user_id || 'exp_button_color_v1'), 1, 8))::bit(32)::int % 100 < 50
      THEN 'control'
    ELSE 'treatment'
  END AS variant_id
FROM dim_users;

Three-arm split

-- 33/33/34 split (control / variant_a / variant_b)
SELECT
  user_id,
  CASE
    WHEN MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, 'exp_three_way_v1'))), 100) < 33
      THEN 'control'
    WHEN MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, 'exp_three_way_v1'))), 100) < 66
      THEN 'variant_a'
    ELSE 'variant_b'
  END AS variant_id
FROM dim_users;

Sub-sampling for opt-in experiments

-- 10% of users in the experiment, 50/50 split among them
SELECT
  user_id,
  CASE
    WHEN MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, 'exp_pricing_test_eligibility'))), 100) >= 10
      THEN 'not_in_experiment'
    WHEN MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, 'exp_pricing_test_v1'))), 100) < 50
      THEN 'control'
    ELSE 'treatment'
  END AS variant_id
FROM dim_users;

Note the two different salts: one for eligibility, one for assignment. This ensures the eligibility decision is independent of the variant decision.

---

Salt naming conventions

Three rules.

1. Unique per experiment. Reuse the same salt across experiments and you correlate assignments. A user in control of experiment A is more likely to be in control of experiment B; the experiments interfere. 2. Stable across the experiment lifecycle. Do not change the salt mid-experiment. Changing the salt re-randomizes existing users; the analysis becomes uninterpretable. 3. Versioned. When semantics change, append _v2. The old experiment data remains queryable; the new salt produces independent assignments.

Convention.

{prefix}_{descriptor}_{version}

Examples.

• exp_button_color_v1
• exp_pricing_test_v1 (eligibility salt)
• exp_pricing_test_assign_v1 (assignment salt within eligible)
• exp_recommendation_model_v2 (version 2 of an existing experiment)

Document the salt convention. Add it to the experiment record.

---

Exposure event schema

The required fields.

The minimum schema is the four required fields. Optional fields support segmentation and debugging.

Storage pattern

Either as a dedicated experiment_exposures table or as an event in the main events table with event_name = 'experiment_exposed'.

Dedicated table is cleaner for analysis. Main events table is simpler for instrumentation. Pick one and stick to it.

---

The delayed-exposure trap

The single most common warehouse-native experimentation bug.

The setup. The treatment shows a new pricing page; the control shows the old one. The team fires exposure at homepage load.

The problem. Many users land on the homepage but never click through to the pricing page. They are counted as "exposed" to the experiment but never saw the variant. The control group includes users who never reached the pricing page; the treatment group does too.

The result. The analysis dilutes the real effect. The treatment may have produced a 20 percent lift among users who actually saw the pricing page, but the analysis shows a 2 percent lift because 90 percent of "exposed" users never reached the variant.

The fix. Fire exposure exactly when the user has seen the variant-specific behavior.

// Wrong: fires at every page load
function onHomepageLoad(user) {
  fireExposure({
    experiment_id: 'exp_pricing_v1',
    variant_id: getVariant(user),
    user_id: user.id,
  });
}

// Right: fires when the pricing page renders the variant-specific UI
function onPricingPageLoad(user) {
  const variant = getVariant(user);
  if (variant === 'treatment') {
    renderNewPricingPage();
  } else {
    renderOldPricingPage();
  }
  // Fire once per user per experiment
  if (!hasExposureFired(user, 'exp_pricing_v1')) {
    fireExposure({
      experiment_id: 'exp_pricing_v1',
      variant_id: variant,
      user_id: user.id,
    });
    markExposureFired(user, 'exp_pricing_v1');
  }
}

The discipline. Fire exposure when the variant-specific UI renders or the variant-specific code path executes. Use a single-fire flag (client-side cache or server-side state) to ensure exposure fires exactly once per user per experiment.

---

Sample ratio mismatch (SRM) check

Before computing any metric, check that the assignment is balanced.

-- For a 50/50 split, expect roughly equal counts in control and treatment
SELECT
  variant_id,
  COUNT(*) AS n,
  COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS share
FROM exposures
WHERE experiment_id = 'exp_button_color_v1'
GROUP BY variant_id;

Expected output.

If the share deviates from the expected 50 percent by more than 1 percentage point at large samples, you have an SRM. The chi-squared test detects this formally.

from scipy.stats import chi2_contingency

observed = [50123, 49877]  # control, treatment
expected_ratio = [0.5, 0.5]
chi2, p, dof, _ = chi2_contingency([observed, [sum(observed) * r for r in expected_ratio]])
print(f"SRM chi-squared p-value: {p:.4f}")
# p < 0.001 indicates SRM

If you have an SRM. Do not analyze the experiment. The assignment is broken (hash collision, biased exposure logging, instrumentation that fires for one variant but not the other). Fix the bug; restart the experiment.

The honest pattern. Run an SRM check at the top of every experiment analysis notebook. If SRM is detected, abort with a clear error message; do not let the analysis proceed.

---

Common assignment and exposure mistakes

• Salt reuse across experiments. A user in control of experiment A is correlated with control of experiment B. Use unique salts per experiment.
• Changing the salt mid-experiment. Re-randomizes existing users. The analysis becomes a mix of two different assignment functions. Pre-register the salt; freeze it at experiment start.
• Firing exposure on every event. Inflates the exposure log by 10 to 100x. Use single-fire enforcement.
• Server-side fire when only client renders the variant. Server-side fires for every API call; client-side fires only when the user reaches the variant-specific UI. Match exposure to where the variant matters.
• Eligibility check in the wrong place. Eligibility (e.g., paid users only) checked client-side after assignment produces biased exposure (only paid users in the log). Check eligibility before assignment, server-side.

Common pitfalls

Eleven failure patterns that recur in warehouse-native experimentation. For each: name, symptom, root cause, fix, prevention.

---

1. Exposure log fires at page load

Symptom. The experiment shows a 1 percent lift; the team expected 10 percent. The lift is real but diluted because most exposed users never saw the variant.

Root cause. Exposure fires at page load (or session start) regardless of whether the user reaches the variant-specific code path. The control group includes users who never saw the variant; the treatment group does too.

Fix. Move exposure firing to the moment the variant-specific UI renders or the variant-specific code path executes. Re-run the experiment with corrected logging.

Prevention. Document the exposure-firing rule in the experiment record. Code review the implementation. Check the exposure-to-metric ratio (if exposure count is much higher than expected metric count, exposure is firing too broadly).

---

2. Lift in control, not treatment

Symptom. The control group has a higher mean than the treatment group. The team is confused because the variant-specific changes should have increased the metric.

Root cause. Assignment hash collision or salt reuse with a prior experiment. Users in control of the new experiment are correlated with users in treatment of an old experiment that also affected the metric.

Fix. Audit the salt. Use a unique salt per experiment. Re-run with corrected assignment.

Prevention. Salt naming convention with version suffix. Code review for new experiments. Run a chi-squared test of independence between the new experiment's assignment and recent prior experiments' assignments before launching.

---

3. P-value is 0.04, we are shipping

Symptom. The primary metric p-value is 0.04. The team declares victory and ships. Three months later the metric in production has not moved.

Root cause. Some combination of underpowered experiment, multiple comparisons without correction, and peeking. Each of these inflates the false-positive rate; together they make a 0.04 p-value uninformative.

Fix. Re-run with a pre-registered sample size, single primary metric, no peeking. If the lift is real, the second experiment will confirm it. If not, the first result was a false positive.

Prevention. Pre-register the sample size, the primary metric, and the analysis method. Do not peek. Apply Bonferroni or BH correction across secondary metrics. Treat 0.04 with much more skepticism than 0.001.

---

4. Experiment shows 30 percent lift

Symptom. The treatment shows a 30 percent lift on the primary metric. The team is excited.

Root cause. Almost certainly a bug. Effects that big rarely exist outside truly novel features. Common bugs: exposure log fires only for treatment users (control is missing); metric model has a join error that double-counts treatment events; assignment is non-random and treatment is over-represented in heavy users.

Fix. Audit the exposure log balance, the assignment ratio, and the metric model. Compare metric values to the historical baseline; if the treatment metric is higher than the all-users historical metric, something is wrong.

Prevention. Skepticism about large effects. Run an SRM check before any analysis. Compare exposed-treatment metric to the historical baseline as a sanity check.

---

5. Treatment users are different (sample ratio mismatch)

Symptom. The exposure log shows 52,000 control users and 48,000 treatment users for a planned 50/50 split. The chi-squared test rejects the null at p < 0.001.

Root cause. Assignment is not balanced. Possible causes: hash function bug, biased exposure logging, instrumentation that fires for one variant but not the other, eligibility check that runs differently across variants.

Fix. Do not analyze the experiment. Find and fix the assignment bug. Restart.

Prevention. Run the SRM check at the top of every analysis notebook. Abort with a clear error message if SRM is detected. Code review the exposure logging for both variants.

---

6. Cannot reproduce yesterday's number

Symptom. Yesterday's analysis showed 5.2 percent lift; today's shows 4.8 percent. Same query, same data, same notebook. The team cannot tell which is "the" answer.

Root cause. Non-deterministic queries. Window functions without explicit ORDER BY produce different results on different runs. Sampling without a seed produces different samples. Floating-point aggregations on large datasets produce slight numeric differences depending on the order of summation.

Fix. Make queries deterministic. Add explicit ORDER BY to window functions. Set a seed for any sampling. For floating-point precision, sum integers (cents) instead of floats; convert to dollars only at display time.

Prevention. Code review for non-determinism. Save the query plan or the result set; compare across runs to detect drift early.

---

7. Custom metric definition disagrees with board metric

Symptom. The experiment says revenue lifted 5 percent; the board's revenue dashboard shows revenue flat. The team cannot tell which is right.

Root cause. Two different SQL queries computing "revenue" with subtly different rules. The experiment query may include or exclude refunds, internal users, test orders, or specific time windows in different ways than the board.

Fix. Align via shared dbt models. The board's revenue dashboard and the experiment's revenue metric reference the same fct_orders model with the same filters.

Prevention. Schema discipline. Every metric used in an experiment is also used somewhere in the board or weekly review. Drift between the two is a code smell that should be caught at code review.

---

8. We never finished the experiment

Symptom. The experiment has been running for 8 weeks. The team has looked at the dashboard three times. Each time the result was inconclusive. The team keeps running it hoping for a clear answer.

Root cause. No pre-registered stop criteria. The experiment was launched without a clear "we will stop when X" rule. The team is implicitly peeking and refusing to call it.

Fix. Set a stop date and honor it. Analyze once on the stop date; report the result (which may be inconclusive). Document the inconclusive result and the design improvements needed for the next attempt.

Prevention. Pre-register the sample size, the stop date, and the decision rule. Honor them.

---

9. iOS users converted 3x in treatment

Symptom. The aggregate treatment lift is small (1 percent), but the iOS segment shows a 3x lift. The team wants to ship the variant for iOS specifically.

Root cause. Three possibilities. (a) Real segment effect: iOS users genuinely prefer the variant. (b) Instrumentation bug: iOS metric tracking differs from the rest. (c) Multiple comparisons: among many segments, one shows a large effect by chance.

Fix. Investigate. Compare iOS metric tracking to other platforms (is the same event firing the same way?). Check the iOS sample size (if small, the 3x is high variance). Re-run the experiment with iOS as the primary segment if the team genuinely wants iOS-specific results.

Prevention. Pre-register segments of interest. Apply multiple-comparisons correction across segments. Treat unexpected segment effects as hypotheses to confirm in a follow-up experiment, not as ship signals.

---

10. Worked on phase 1, broke on phase 2

Symptom. Phase 1 of a staged rollout showed a clear positive lift. Phase 2 (broader audience) shows a much smaller or negative lift. The team is confused.

Root cause. Simpson's paradox from cohort mix shift. Phase 1's audience is enriched in a segment where the treatment works well; phase 2 includes more of a segment where the treatment works poorly. The aggregate result reverses.

Fix. Decompose by segment. The phase 2 result is the truer estimate (broader audience), but the phase 1 result tells you which segments respond. Consider a segment-specific rollout if the segment-level economics support it.

Prevention. Run the experiment on the full target audience from the start, not on a phase-1 sub-audience. If staged rollout is operationally required, plan for the cohort mix shift in the analysis.

---

11. Statistical significance but tiny effect

Symptom. The p-value is 0.001. The lift is 0.3 percent. The experiment is statistically significant but the team is unsure whether to ship.

Root cause. Large sample inflated the test's power. The test detects effects much smaller than the team's practical significance threshold.

Fix. Apply the practical-significance check. If the team's threshold is "ship at 1 percent or larger," 0.3 percent does not meet the bar regardless of the p-value. Document the result; do not ship.

Prevention. Pre-register the practical-significance threshold (the MDE) at experiment design time. Treat statistical significance below the MDE as inconclusive, not as a ship signal.

---

The pattern across all eleven

Most warehouse-native experimentation failures share one root cause: the team did not pre-register enough discipline before the experiment started. Pre-registered sample size prevents underpowered analysis. Pre-registered exposure rule prevents the delayed-exposure trap. Pre-registered primary metric prevents multiple-comparisons fishing. Pre-registered stop criteria prevent the experiment-runs-forever pattern.

The fix at the meta level. Treat each experiment as a contract. Before launching, write down the assignment unit, the salt, the exposure rule, the primary metric, the secondary metrics with multiple-comparisons correction, the sample size, the stop criteria, the practical-significance threshold. The contract is reviewed at launch. Deviations from the contract during the experiment require explicit re-registration. The discipline is the only thing that scales as the team runs more experiments.

Metric definitions in dbt

dbt model patterns for experiment metrics. Reusing fct models. The exp_metrics namespace. Versioning. Aligning with board metrics.

The principle. Define experiment metrics as dbt models so they are version-controlled, testable, and aligned with board metrics. The same source of truth for both surfaces eliminates the "experiment said X but the board says Y" problem.

---

The namespace pattern

Three layers in a typical dbt project.

• stg_*: staging models. Light cleanup of source data. One per source table.
• fct_* and dim_*: marts models. Business-logic transformations. Joined and enriched data ready for consumption.
• exp_metrics_*: experiment-shaped models. One row per assignment unit (typically user_id) with the metrics needed for analysis.

Example structure.

models/
  staging/
    stg_orders.sql
    stg_users.sql
    stg_events.sql
  marts/
    fct_orders.sql
    fct_sessions.sql
    dim_users.sql
  experiments/
    exp_metrics_revenue.sql
    exp_metrics_engagement.sql
    exp_metrics_retention.sql

The fct_* models feed both the board dashboard and the experiment metrics. The exp_metrics_* models pivot to one row per user with the metrics joined or aggregated.

---

Pattern 1: revenue metric

-- models/experiments/exp_metrics_revenue.sql
{{ config(materialized='table') }}

WITH order_data AS (
  SELECT
    user_id,
    occurred_at,
    amount_cents,
    refunded
  FROM {{ ref('fct_orders') }}
  WHERE occurred_at >= '{{ var("experiment_start") }}'
    AND occurred_at < '{{ var("experiment_end") }}'
)
SELECT
  user_id,
  SUM(CASE WHEN refunded THEN 0 ELSE amount_cents END) AS net_revenue_cents,
  SUM(amount_cents) AS gross_revenue_cents,
  COUNT(*) AS order_count,
  MIN(occurred_at) AS first_order_at
FROM order_data
GROUP BY user_id;

Note the var() references for experiment start and end dates. The variables come from the dbt project's variables file or are passed at run time.

The same fct_orders model feeds the board's revenue dashboard. Aligned definitions.

---

Pattern 2: engagement metric

-- models/experiments/exp_metrics_engagement.sql
{{ config(materialized='table') }}

WITH event_data AS (
  SELECT
    user_id,
    occurred_at,
    event_name
  FROM {{ ref('fct_events') }}
  WHERE occurred_at >= '{{ var("experiment_start") }}'
    AND occurred_at < '{{ var("experiment_end") }}'
    AND event_name IN ('content_created', 'content_edited', 'content_shared')
)
SELECT
  user_id,
  COUNT(*) AS engagement_event_count,
  COUNT(DISTINCT DATE(occurred_at)) AS active_day_count,
  SUM(CASE WHEN event_name = 'content_shared' THEN 1 ELSE 0 END) AS shares_count
FROM event_data
GROUP BY user_id;

The metric definition specifies exactly which events count as engagement. The board's "weekly active users" dashboard uses the same event filter via the same fct_events model.

---

Pattern 3: retention metric (bracket retention)

-- models/experiments/exp_metrics_retention_w2.sql
{{ config(materialized='table') }}

WITH user_first_activity AS (
  SELECT user_id, MIN(occurred_at) AS first_active_at
  FROM {{ ref('fct_events') }}
  WHERE occurred_at >= '{{ var("experiment_start") }}'
  GROUP BY user_id
),
week2_activity AS (
  SELECT DISTINCT u.user_id
  FROM user_first_activity u
  JOIN {{ ref('fct_events') }} e ON e.user_id = u.user_id
  WHERE e.occurred_at >= u.first_active_at + INTERVAL '7 days'
    AND e.occurred_at <  u.first_active_at + INTERVAL '14 days'
)
SELECT
  u.user_id,
  CASE WHEN w2.user_id IS NOT NULL THEN 1 ELSE 0 END AS retained_w2
FROM user_first_activity u
LEFT JOIN week2_activity w2 USING (user_id);

Bracket retention (week 2 = days 7 to 13 from first activity) is more stable than N-day retention. The metric is binary: 1 if retained, 0 if not.

---

Pattern 4: ratio metric (delta method required)

Some metrics are ratios (conversion rate, click-through rate). They require the delta method for correct variance estimation.

-- models/experiments/exp_metrics_conversion.sql
{{ config(materialized='table') }}

SELECT
  user_id,
  COUNT(*) AS impressions,
  SUM(CASE WHEN converted THEN 1 ELSE 0 END) AS conversions
FROM {{ ref('fct_funnel_events') }}
WHERE occurred_at >= '{{ var("experiment_start") }}'
  AND occurred_at <  '{{ var("experiment_end") }}'
GROUP BY user_id;

The model produces numerator and denominator per user. The analysis layer (Python) computes the ratio and applies the delta method for variance.

# Per-user numerator and denominator
df = warehouse.query("SELECT * FROM exp_metrics_conversion")

# Group means
control = df[df.variant_id == 'control']
treatment = df[df.variant_id == 'treatment']

# Conversion rate per group
control_rate = control.conversions.sum() / control.impressions.sum()
treatment_rate = treatment.conversions.sum() / treatment.impressions.sum()

# Delta method variance (skipping the math here; see statistical-analysis-templates.md)
# ...

Do not compute the ratio per user and average it. That undercounts heavy users; the delta method is the correct treatment.

---

Versioning metric definitions

Same versioning pattern as elsewhere. When the metric definition changes meaningfully, append _v2.

exp_metrics_revenue.sql       <- v1, fires alongside v2 during transition
exp_metrics_revenue_v2.sql    <- new semantics

The transition is the same: ship v2 alongside v1, migrate experiments to v2 over 90 days, deprecate v1.

dbt's built-in versioning (the models: config with versions:) is also useful here; lets you serve both versions from the same model name with explicit version selection.

---

Aligning experiment metrics with board metrics

The variance discipline. The same dbt model feeds both surfaces.

If the board reports "monthly revenue" by summing amount_cents from fct_orders and excluding refunded orders, the experiment's revenue metric does the same. No special "experiment revenue" calculation that subtly differs.

The mechanism. Both the board dashboard's SQL and the exp_metrics_revenue model reference the same fct_orders table with the same filters. dbt tests verify the table contains the expected rows.

The check. When an experiment lands and the team reports "revenue lifted 5 percent in treatment," the team can pull the same numbers from the board and compare. If the board's revenue is flat or the experiment's lift is much larger than the board's revenue trend, the metric definitions disagree somewhere. Investigate.

---

Testing experiment metrics

dbt tests on the metric models catch regressions.

# models/experiments/_experiments.yml
version: 2

models:
  - name: exp_metrics_revenue
    columns:
      - name: user_id
        tests:
          - not_null
          - unique
      - name: net_revenue_cents
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

The unique test on user_id confirms the model produces one row per user (no accidental duplicates from a bad join). The >= 0 test confirms revenue is non-negative (refunds are subtracted, never additive).

---

Common metric-definition mistakes

• Drift between board and experiment. "Experiment revenue" is computed differently from "board revenue." Align via shared dbt models.
• Wrong time window. The experiment runs for 14 days but the metric model includes 30 days of data. The control and treatment numbers are similar because the variant signal is diluted.
• One row per event instead of one row per user. Joining the metric to exposure on user_id produces a row explosion. Always aggregate to one row per user before joining.
• Forgetting to handle nulls. Users with no activity should appear with metric = 0, not be missing from the model. Use LEFT JOIN from exposure or include all eligible users in the metric model.
• Non-deterministic queries. Window functions without explicit ORDER BY, sampling without a random seed. The same query produces different numbers on different days.

Power analysis calculations

Pre-experiment power analysis. MDE math. Sample size calculations. Calibrating effect-size assumptions from historical experiments.

The principle. Most underpowered experiments come from optimistic effect-size assumptions. The team designs the experiment expecting a 10 percent lift; the actual effect is 1 percent, undetectable at the planned sample size; the experiment runs forever or stops with an inconclusive result. Power analysis prevents this by forcing a concrete decision about how big the lift needs to be before the experiment starts.

---

The four parameters

Power analysis ties together four numbers. Fix any three; the fourth is determined.

1. Effect size (or MDE, minimum detectable effect). The smallest lift the experiment can reliably detect. Expressed as a relative percentage, an absolute difference, or Cohen's d (effect size in standard deviations). 2. Sample size per arm. Number of users in each variant. 3. Alpha (significance level). Default 0.05. The false-positive rate. 4. Power. Default 0.8. One minus the false-negative rate. The probability of detecting a real effect.

The standard practice. Fix alpha at 0.05, power at 0.8, decide on either the MDE (and solve for sample size) or the sample size (and solve for MDE).

---

Solving for sample size given MDE

from statsmodels.stats.power import tt_ind_solve_power

# Continuous metric, two-sample t-test
# Cohen's d = (mean_treatment - mean_control) / pooled_std

cohens_d = 0.05  # 5 percent of one standard deviation

n_per_arm = tt_ind_solve_power(
    effect_size=cohens_d,
    nobs1=None,
    alpha=0.05,
    power=0.8,
    ratio=1.0  # equal sample sizes per arm
)
print(f"Required sample size per arm: {n_per_arm:.0f}")

For a Cohen's d of 0.05 (small effect), expect roughly 6,300 users per arm. For 0.1 (still small), roughly 1,600 per arm. For 0.2 (medium), roughly 400 per arm.

Solving for proportions

from statsmodels.stats.power import zt_ind_solve_power

# Binary metric, proportions test
# baseline conversion rate = 5 percent; target lift = 5 percent relative
baseline_p = 0.05
relative_lift = 0.05
treatment_p = baseline_p * (1 + relative_lift)

# Cohen's h for proportions
import numpy as np
def cohens_h(p1, p2):
    return 2 * np.arcsin(np.sqrt(p1)) - 2 * np.arcsin(np.sqrt(p2))

h = cohens_h(treatment_p, baseline_p)

n_per_arm = zt_ind_solve_power(
    effect_size=h,
    nobs1=None,
    alpha=0.05,
    power=0.8
)
print(f"Required sample size per arm: {n_per_arm:.0f}")

For a 5 percent relative lift on a 5 percent baseline conversion rate, expect roughly 60,000 users per arm. Small relative lifts on small baselines need very large samples.

---

Solving for MDE given sample size

The reverse calculation. Given the available sample (e.g., 8,000 users per arm), what is the smallest effect the experiment can detect?

from statsmodels.stats.power import tt_ind_solve_power

mde_cohens_d = tt_ind_solve_power(
    effect_size=None,
    nobs1=8000,
    alpha=0.05,
    power=0.8,
    ratio=1.0
)
print(f"MDE in Cohen's d: {mde_cohens_d:.4f}")

# Convert to relative MDE assuming the metric's standard deviation
metric_std = 50.0  # in cents, for example
metric_mean = 200.0
mde_absolute = mde_cohens_d * metric_std
mde_relative = mde_absolute / metric_mean
print(f"MDE absolute: {mde_absolute:.2f}")
print(f"MDE relative: {mde_relative:.2%}")

If the available sample yields an MDE of 8 percent and the team expects only a 2 percent lift, the experiment is underpowered. Either secure more sample (run longer or expand the audience) or accept that the experiment will produce an inconclusive result.

---

Calibrating effect-size assumptions from historical experiments

The most common power-analysis mistake is over-optimistic effect-size estimates. Three patterns help calibrate.

Pattern 1: pull the historical distribution

For the last 30 (or however many) experiments, pull the observed lifts. Compute the median, 25th percentile, and 75th percentile.

df = warehouse.query("""
  SELECT experiment_id, observed_relative_lift
  FROM experiment_results
  WHERE shipped = true
  ORDER BY ended_at DESC
  LIMIT 30
""")

print(f"Median observed lift: {df.observed_relative_lift.median():.2%}")
print(f"P25: {df.observed_relative_lift.quantile(0.25):.2%}")
print(f"P75: {df.observed_relative_lift.quantile(0.75):.2%}")

The median observed lift is the realistic expectation for the next experiment. If past experiments show a median lift of 1 percent, planning for a 5 percent lift on the next experiment is optimistic.

Pattern 2: stratify by experiment type

Pricing experiments often produce larger lifts than UI experiments. Onboarding experiments often produce larger lifts than feature-discovery experiments. Pull the distribution by experiment type to set type-specific expectations.

Pattern 3: ask the team about prior experiments they remember

The "we usually see X" estimate from team members is anchored on memorable experiments (the wins, the disasters). Compare against the actual distribution; the memorable estimate is usually too high.

The honest conversation. "Our last 30 experiments had a median observed lift of 0.5 percent. We are planning this experiment for a 5 percent MDE. Either the experiment is going to produce an inconclusive result, or we are expecting an unusually large effect. Which is it?"

---

When the math says "we cannot run this"

Three escalation paths when the available sample is insufficient.

1. Run longer. The default. If the experiment needs 60,000 users per arm and you have 10,000 per week, plan for a 6-week run. Document the duration before starting. 2. Expand the audience. If the experiment is targeting only paid users (10 percent of the user base), consider running on all users. The result is generalizable to a broader population, but the intervention may need to work for the broader population too. 3. Accept inconclusive. Some experiments cannot be powered with the available sample. Document the MDE; design the experiment to detect a larger effect or accept that it will be inconclusive. The discipline of saying "we cannot reliably answer this with our sample" is hard but honest.

---

The underpowered-experiment cost

A team running 12 experiments per quarter, 4 of which are underpowered. The cost.

• Underpowered experiments produce inconclusive results. The team learns nothing.
• Engineering and design effort spent on the experiment is wasted.
• Stakeholders treat inconclusive as "no effect"; later experiments that would have shown the same effect at a larger sample also produce inconclusive, reinforcing the false belief that no effect exists.
• The team's experimentation discipline degrades because the visible track record looks bad.

Power analysis at the design stage prevents most of this. The cost is 30 minutes of work; the benefit is avoiding multi-week runs of experiments that cannot answer the question.

---

Common power-analysis mistakes

• Optimistic effect-size assumption. Plan for a 10 percent lift; observe a 1 percent lift. The experiment is 100x underpowered.
• Forgetting variance from the historical metric. Power calculations use Cohen's d, which depends on the metric's standard deviation. A high-variance metric requires a larger sample for the same MDE than a low-variance metric.
• Computing power on aggregate when the assignment unit is something else. If the assignment unit is user but the metric is per-event, the per-event variance includes within-user correlation. Use the user-level variance for the calculation.
• Skipping the calculation for "small" experiments. "We will just see what happens" is the path to a quarter of inconclusive experiments.
• Confusing statistical significance with practical significance. A p-value below 0.05 with a 0.3 percent effect on a 100,000-user experiment is statistically significant but may not be worth shipping. Use the MDE as the practical-significance threshold; a 0.3 percent effect is below most teams' MDE.

Sequential testing patterns

mSPRT, confidence sequences, group sequential designs. Plus an honest framing on when to peek.

The principle. Looking at experiment results before completion (peeking) inflates the false-positive rate. The naive solution is "do not peek." The practical solution is sequential testing methods that allow valid early stopping. The honest middle ground: if you do not understand the math, do not peek.

---

The peeking problem

The standard t-test assumes a fixed sample size pre-registered before the experiment runs. When the team peeks early, the test is implicitly running multiple times; some of those peeks will hit p < 0.05 by chance even when no effect exists.

A team that peeks daily on a 4-week experiment runs the test roughly 28 times. The implicit false-positive rate becomes much higher than the nominal 5 percent. Estimates suggest 30 percent or more in this case; the experiment is more likely to show a fake significant result than a real one.

The four solutions.

1. Do not peek. Pre-register the sample size; analyze once. 2. mSPRT. Always-valid p-values that survive peeking. 3. Confidence sequences. Always-valid confidence intervals via the Howard et al construction. 4. Group sequential designs. Pre-specified interim analyses with calibrated alpha-spending.

Each solution requires implementation discipline. Incorrect implementation is worse than no peeking solution at all (the team thinks the math is correct and ships based on inflated false-positives).

---

mSPRT (mixture Sequential Probability Ratio Test)

The most commonly deployed sequential testing method. Used by Optimizely, Statsig.

The intuition. Compute a Bayes-factor-like statistic that updates as data arrives. The statistic crosses a fixed threshold when the evidence is strong enough to reject the null; it never crosses by chance under the null (with the correct calibration).

Python implementation sketch

import numpy as np

def mSPRT(treatment, control, prior_var=1.0, alpha=0.05):
    """
    Mixture SPRT for two-sample mean difference.
    Returns the always-valid p-value at the current sample.
    """
    n_t = len(treatment)
    n_c = len(control)
    diff = treatment.mean() - control.mean()
    pooled_var = (treatment.var() / n_t + control.var() / n_c)
    se = np.sqrt(pooled_var)

    # Test statistic
    z = diff / se

    # Mixture prior with variance prior_var
    # Always-valid p-value via the mixture-likelihood ratio
    n_eff = (n_t * n_c) / (n_t + n_c)
    weight = np.sqrt(prior_var / (prior_var + 1.0 / n_eff))
    log_lr = 0.5 * z**2 * (1 - weight**2) - 0.5 * np.log(1.0 / weight**2)
    p_always_valid = min(1.0, np.exp(-log_lr))

    return p_always_valid

Note: the implementation above is a sketch for illustration. Production implementations should use a peer-reviewed library or expert review. Do not deploy this verbatim.

The honest version. Use a maintained library (sequential in R, custom Python adapted from peer-reviewed papers like Johari et al 2017). Have a statistician on the team review the implementation.

---

Confidence sequences (Always-Valid Inference)

The Howard et al construction. Confidence intervals that are valid at any sample size; the team can peek arbitrarily often without inflating the false-positive rate.

The math is more involved than mSPRT. Useful when the team needs not just always-valid p-values but always-valid confidence intervals (effect size with uncertainty).

References.

• Howard, Ramdas, McAuliffe, Sekhon (2021), "Time-uniform, nonparametric, nonasymptotic confidence sequences."
• Code: sequential package in R, or custom Python.

For most warehouse-native teams, mSPRT is enough. Confidence sequences are for teams with a statistician who has read the paper and validated the implementation.

---

Group sequential designs

The classical frequentist approach. Pre-specified interim analysis points (e.g., at 25 percent, 50 percent, 75 percent of the planned sample) with calibrated alpha-spending boundaries.

The most common boundary is O'Brien-Fleming. Conservative early (high effect size required to stop early); lenient late (lower effect size required to stop at the final analysis).

# Hypothetical O'Brien-Fleming boundaries for 4 interim analyses
# Z-values; if observed Z exceeds, reject the null and stop
boundaries = {
    0.25: 4.05,
    0.50: 2.86,
    0.75: 2.34,
    1.00: 2.02,
}

The discipline.

1. Pre-register the interim analysis points and the boundary values. 2. At each interim point, compute the test statistic. 3. If the statistic exceeds the boundary, stop the experiment and reject the null. 4. If the statistic does not exceed any boundary by the final analysis, fail to reject (treat as inconclusive or as "no effect detected").

Group sequential designs require commitment up front. Adding interim analyses after the experiment starts is post-hoc peeking and inflates the false-positive rate.

---

When to peek and when not to

Three scenarios.

Scenario 1: small team, no statistician, simple experiments

Do not peek. Pre-register the sample size; analyze once at completion. The discipline is simpler than implementing sequential testing correctly, and the cost (running for the full pre-registered duration) is usually small.

Scenario 2: high-volume team running many experiments, has a statistician

Implement mSPRT. The peeking is operationally useful (early stops free up the testing infrastructure for the next experiment) and the math is well-understood. Train the team on the implementation; review periodically.

Scenario 3: regulated industry, strong audit requirements

Group sequential design. Pre-registered interim analyses with calibrated boundaries. The audit trail is clean: every interim analysis was specified before the experiment started; the boundaries are reproducible from the design document.

---

Common sequential testing mistakes

• Implementing mSPRT without understanding the math. A wrong implementation produces inflated false-positives that the team trusts. Worse than no peeking.
• Adding interim analyses after the experiment starts. Group sequential designs require pre-registration. Post-hoc interim analyses are just peeking.
• Mixing methods. Compute mSPRT for some peeks and a standard t-test for others. The latter inflates false-positives; the former does not. Pick one method per experiment and stick to it.
• Using mSPRT but reporting the standard p-value. The mSPRT p-value is always-valid; the standard p-value is not. Reporting the standard p-value while peeking is the same as not using mSPRT at all.
• Stopping early on a tiny effect. mSPRT may show "significant" before the team has enough evidence on practical significance. The effect is real but small; shipping it may not be worth the engineering complexity.

---

The honest recommendation

For most warehouse-native experimentation teams, the right answer is "do not peek."

• Pre-register the sample size based on power analysis.
• Run to the pre-registered sample.
• Analyze once at completion.

The cost is some early stops the team would have liked. The benefit is no implementation risk in sequential testing math, no false-positives from mis-implemented peeking, and a clean audit trail.

Move to sequential testing when the team has the statistical expertise to implement correctly and the operational pressure to peek (high-volume infrastructure where early stops materially affect the next experiment's timeline).

Statistical analysis templates

SQL and Python templates for the most common warehouse-native analyses. t-test, proportions test, Mann-Whitney, bootstrap. Plus the recommended notebook structure.

---

Welch's t-test (continuous metrics)

The default for continuous metrics where group variances may differ.

SQL

WITH joined AS (
  SELECT e.variant_id, m.net_revenue_cents
  FROM exposures e
  LEFT JOIN exp_metrics_revenue m USING (user_id)
  WHERE e.experiment_id = 'exp_button_color_v1'
),
metric_by_variant AS (
  SELECT
    variant_id,
    COUNT(*) AS n,
    AVG(COALESCE(net_revenue_cents, 0)) AS mean,
    VAR_SAMP(COALESCE(net_revenue_cents, 0)) AS variance
  FROM joined
  GROUP BY variant_id
)
SELECT
  control.mean   AS control_mean,
  treatment.mean AS treatment_mean,
  treatment.mean - control.mean AS absolute_lift,
  (treatment.mean - control.mean) / NULLIF(control.mean, 0) AS relative_lift,
  -- Welch's t-statistic
  (treatment.mean - control.mean) /
    SQRT(treatment.variance / treatment.n + control.variance / control.n)
    AS t_statistic,
  -- Standard error of the mean difference
  SQRT(treatment.variance / treatment.n + control.variance / control.n)
    AS se,
  control.n   AS control_n,
  treatment.n AS treatment_n
FROM
  (SELECT * FROM metric_by_variant WHERE variant_id = 'control')   control,
  (SELECT * FROM metric_by_variant WHERE variant_id = 'treatment') treatment;

The query returns the t-statistic and standard error. Convert to p-value and confidence interval via a UDF or in Python.

Python

import pandas as pd
from scipy import stats
import numpy as np

df = warehouse.query("""
  SELECT e.variant_id, COALESCE(m.net_revenue_cents, 0) AS metric
  FROM exposures e
  LEFT JOIN exp_metrics_revenue m USING (user_id)
  WHERE e.experiment_id = 'exp_button_color_v1'
""")

control   = df[df.variant_id == 'control'].metric
treatment = df[df.variant_id == 'treatment'].metric

t_stat, p_value = stats.ttest_ind(treatment, control, equal_var=False)

# 95 percent confidence interval on the mean difference
diff = treatment.mean() - control.mean()
se = np.sqrt(treatment.var() / len(treatment) + control.var() / len(control))
ci_low, ci_high = diff - 1.96 * se, diff + 1.96 * se

print(f"Lift: {diff:.2f} (95% CI: [{ci_low:.2f}, {ci_high:.2f}])")
print(f"t = {t_stat:.3f}, p = {p_value:.4f}")

---

Proportions test (binary metrics)

For binary outcomes (converted vs not, retained vs not).

Python

from statsmodels.stats.proportion import proportions_ztest, proportion_confint

# Counts and totals per variant
control_conv  = 1240
control_n     = 25000
treatment_conv = 1390
treatment_n    = 25000

# Z-test
z_stat, p_value = proportions_ztest(
    [control_conv, treatment_conv],
    [control_n, treatment_n]
)

# Confidence intervals on each proportion
control_ci   = proportion_confint(control_conv, control_n, method='wilson')
treatment_ci = proportion_confint(treatment_conv, treatment_n, method='wilson')

# Lift
control_rate   = control_conv / control_n
treatment_rate = treatment_conv / treatment_n
relative_lift  = (treatment_rate - control_rate) / control_rate

print(f"Control: {control_rate:.4f}, CI: {control_ci}")
print(f"Treatment: {treatment_rate:.4f}, CI: {treatment_ci}")
print(f"Relative lift: {relative_lift:.2%}")
print(f"z = {z_stat:.3f}, p = {p_value:.4f}")

The Wilson score interval is more accurate than the normal approximation for small samples or extreme proportions.

---

Mann-Whitney U test (non-parametric)

For metrics with skewed distributions (revenue, time-on-site) where the t-test's normal-distribution assumption is violated.

from scipy.stats import mannwhitneyu

control   = df[df.variant_id == 'control'].metric
treatment = df[df.variant_id == 'treatment'].metric

u_stat, p_value = mannwhitneyu(treatment, control, alternative='two-sided')

# Mann-Whitney tests stochastic dominance, not the mean.
# Report median lift alongside.
median_lift = treatment.median() - control.median()
print(f"Median lift: {median_lift:.2f}")
print(f"U = {u_stat:.0f}, p = {p_value:.4f}")

The Mann-Whitney U test is the standard non-parametric alternative. Use when the metric distribution is skewed and the t-test's assumption fails.

---

Bootstrap confidence interval

For metrics where the analytic distribution is unclear or unusual (e.g., retention bracket, complex composite metrics).

import numpy as np

def bootstrap_ci(treatment, control, n_iterations=10000, alpha=0.05):
    diffs = []
    for _ in range(n_iterations):
        t_sample = treatment.sample(n=len(treatment), replace=True)
        c_sample = control.sample(n=len(control), replace=True)
        diffs.append(t_sample.mean() - c_sample.mean())
    diffs = np.array(diffs)
    return np.quantile(diffs, [alpha / 2, 1 - alpha / 2])

ci_low, ci_high = bootstrap_ci(treatment, control)
diff = treatment.mean() - control.mean()
print(f"Lift: {diff:.2f} (95% CI: [{ci_low:.2f}, {ci_high:.2f}])")

10,000 bootstrap iterations is the standard. More iterations produce tighter intervals at the cost of compute time.

---

Notebook structure

The recommended structure for an experiment analysis notebook.

Cell 1: parameters

EXPERIMENT_ID = 'exp_button_color_v1'
EXPERIMENT_START = '2026-04-01'
EXPERIMENT_END   = '2026-04-15'
PRIMARY_METRIC   = 'net_revenue_cents'
SECONDARY_METRICS = ['order_count', 'session_count']
ALPHA = 0.05

Parameters at the top. Parametrize the notebook so the same template runs for any experiment.

Cell 2: SRM check

df_exposures = warehouse.query(f"""
  SELECT variant_id, COUNT(*) AS n
  FROM exposures
  WHERE experiment_id = '{EXPERIMENT_ID}'
  GROUP BY variant_id
""")

# Chi-squared test against expected ratio
from scipy.stats import chi2_contingency
observed = df_exposures.n.values
expected = [observed.sum() / len(observed)] * len(observed)
chi2, p, _, _ = chi2_contingency([observed, expected])
assert p > 0.01, f"SRM detected (p = {p:.4f}). Aborting analysis."
print(f"SRM check passed (p = {p:.4f})")

Abort the notebook if SRM is detected. Do not proceed to metric analysis with broken assignment.

Cell 3: pull data

df = warehouse.query(f"""
  SELECT
    e.variant_id,
    e.user_id,
    COALESCE(m.{PRIMARY_METRIC}, 0) AS metric
  FROM exposures e
  LEFT JOIN exp_metrics_revenue m USING (user_id)
  WHERE e.experiment_id = '{EXPERIMENT_ID}'
""")

Cell 4: primary analysis

t-test, proportions test, or bootstrap depending on the metric.

Cell 5: secondary metrics

Loop over secondary metrics; apply Bonferroni correction for multiple comparisons.

Cell 6: written-up decision

A markdown cell with the conclusion. Ship, kill, or inconclusive. Include the lift, the CI, the p-value, the SRM result, and any caveats. This cell is the deliverable.

---

Common analysis mistakes

• No SRM check. Analyzing an experiment with broken assignment produces meaningless numbers.
• Equal-variance t-test on unequal-variance data. Use Welch's (equal_var=False) by default.
• t-test on heavily skewed metrics. Use bootstrap or Mann-Whitney instead.
• Per-user ratios for ratio metrics. Heavy users get under-weighted. Use the delta method.
• Multiple secondary metrics without correction. With 5 secondary metrics at alpha 0.05, expect a 25 percent chance of a false positive somewhere. Bonferroni or BH correction.
• Stop-on-significance peeking. Inflates the false-positive rate. Either pre-register the sample size or use a sequential testing method correctly.

Variance reduction techniques

CUPED is the most powerful variance reduction technique for warehouse-native experimentation. Stratification, regression adjustment, and doubly robust estimation cover specific cases.

The principle. Variance reduction makes confidence intervals narrower at the same sample size. A 30 to 50 percent variance reduction (typical with CUPED on engagement metrics) is equivalent to running the experiment 1.5 to 2x longer for the same statistical power. Worth the engineering investment for any team running 5+ experiments per quarter.

---

CUPED: the workhorse

CUPED stands for Controlled-experiment Using Pre-Experiment Data. Originally from Microsoft (Deng et al, 2013).

The intuition. If you can predict a user's metric behavior from pre-experiment data (their behavior before the experiment started), you can subtract that prediction from the metric, leaving a smaller residual to test on. The mean is preserved (CUPED does not change the point estimate of the lift), but the variance shrinks because predictable variance has been removed.

The math

For each user, compute a pre-experiment covariate (e.g., revenue in the 28 days before the experiment started). Call this pre_metric. The CUPED-adjusted metric is:

adjusted_metric = metric - theta * (pre_metric - mean(pre_metric))

where theta is the regression coefficient of metric on pre_metric:

theta = cov(metric, pre_metric) / var(pre_metric)

Run the t-test on adjusted_metric instead of metric. The mean difference between treatment and control is preserved; the variance is reduced.

Python implementation

import pandas as pd
import numpy as np

# df has columns: user_id, variant_id, metric, pre_metric
df = warehouse.query("""
  SELECT
    e.user_id,
    e.variant_id,
    COALESCE(m.net_revenue_cents, 0) AS metric,
    COALESCE(p.pre_net_revenue_cents, 0) AS pre_metric
  FROM exposures e
  LEFT JOIN exp_metrics_revenue m USING (user_id)
  LEFT JOIN pre_experiment_revenue p USING (user_id)
  WHERE e.experiment_id = 'exp_button_color_v1'
""")

# Compute theta from the entire dataset (control and treatment combined)
theta = np.cov(df.metric, df.pre_metric)[0, 1] / np.var(df.pre_metric)

# CUPED-adjusted metric
df['adjusted_metric'] = df.metric - theta * (df.pre_metric - df.pre_metric.mean())

# Run t-test on the adjusted metric
from scipy import stats
control_adj   = df[df.variant_id == 'control'].adjusted_metric
treatment_adj = df[df.variant_id == 'treatment'].adjusted_metric
t_stat, p_value = stats.ttest_ind(treatment_adj, control_adj, equal_var=False)

# Compare to unadjusted variance
unadjusted_var = df.metric.var()
adjusted_var   = df.adjusted_metric.var()
variance_reduction = 1 - adjusted_var / unadjusted_var
print(f"Variance reduction: {variance_reduction:.1%}")

When CUPED works well

• Engagement metrics with strong autocorrelation. Pre-experiment activity strongly predicts in-experiment activity.
• Revenue metrics on consumer products with repeat customers.
• Retention metrics where past retention predicts future retention.

When CUPED works poorly

• New users (no pre-experiment data). They drop out of the analysis or are imputed.
• Metrics with weak temporal autocorrelation. The pre-period does not predict the in-period.
• Metrics that are zero for most users (highly skewed binary outcomes). The t-test's normal-distribution assumption fails; CUPED on top inherits the failure.

Pre-experiment window length

A 28-day pre-experiment window is the typical default. Longer windows (60 to 90 days) capture more of the user's behavior pattern and may reduce variance more, but also reduce eligibility (users who joined within the window have no pre-period).

The discipline. Pre-register the pre-period length when designing the experiment. Do not change it after looking at results.

---

Stratification

Slice the analysis by a pre-experiment covariate (segment, region, device) and pool the per-stratum estimates. Useful when the covariate is strongly predictive of the metric.

# Stratify by device
strata = df.device_type.unique()
stratum_estimates = []
for s in strata:
    sub = df[df.device_type == s]
    diff = sub[sub.variant_id == 'treatment'].metric.mean() - sub[sub.variant_id == 'control'].metric.mean()
    stratum_estimates.append({
        'stratum': s,
        'n': len(sub),
        'diff': diff,
    })

# Pool: weighted average by stratum size
total_n = sum(s['n'] for s in stratum_estimates)
pooled_diff = sum(s['diff'] * s['n'] / total_n for s in stratum_estimates)

The variance of the pooled estimate is lower than the unpooled t-test when the strata have different baseline metrics (the across-stratum variance is removed).

The downside. Stratification by too many covariates produces small per-stratum samples, which inflates per-stratum variance. Use stratification on one or two strong predictors, not on every covariate available.

---

Regression adjustment (covariate adjustment via OLS)

A generalization of CUPED to multiple covariates.

import statsmodels.api as sm

# X: covariates (pre_metric, device_type encoded, region encoded)
X = pd.get_dummies(df[['pre_metric', 'device_type', 'region']], drop_first=True)
X['variant_treatment'] = (df.variant_id == 'treatment').astype(int)
X = sm.add_constant(X)
y = df.metric

# OLS regression
model = sm.OLS(y, X).fit()
print(model.summary())

# The coefficient on 'variant_treatment' is the adjusted effect estimate.
# Its standard error is typically smaller than the unadjusted t-test SE.

Regression adjustment is more flexible than CUPED but requires more care: collinearity among covariates, model misspecification, and overfitting on small samples are all possible failure modes.

For most warehouse-native experiments, CUPED with a single pre-period covariate is enough. Regression adjustment with multiple covariates is for edge cases.

---

Doubly robust estimation

Useful in observational and quasi-experimental settings where randomization was imperfect (geo experiments, switchback designs, natural experiments).

The intuition. Combine an outcome model (predict the metric from covariates) with a propensity-score model (predict the probability of treatment from covariates). The combined estimator is unbiased if either of the two models is correct (hence "doubly robust estimation").

For the standard A/B test where assignment is random, doubly robust estimation does not help. The benefit is in observational settings where the assignment mechanism may be confounded.

The implementation requires causalml, dowhy, or a custom implementation. Outside the scope of the typical experiment notebook; pointer to the academic literature for full treatment.

References.

• Bang and Robins (2005), "Doubly Robust Estimation in Missing Data and Causal Inference Models."
• Funk et al. (2011), "Doubly Robust Estimation of Causal Effects."

---

When to invest in variance reduction

Three signals.

1. Running 5+ experiments per quarter. The amortized engineering investment in CUPED pays back across many experiments. 2. The team is power-constrained. Experiments routinely run too long or hit inconclusive results because of insufficient sample size. CUPED extends the effective sample size by 30 to 50 percent. 3. The team has data scientists who understand the math. CUPED is straightforward but easy to implement incorrectly (theta computed on the wrong subset, pre-period window misaligned, regression diagnostics ignored). Without someone on the team who can audit the implementation, the variance reduction may not be real.

The order of investment. CUPED first; it is the highest ROI. Stratification second; useful for one or two strong predictors. Regression adjustment third; for edge cases where multiple covariates matter. Doubly robust estimation last; for genuine quasi-experiments.

---

Common variance reduction mistakes

• Computing theta on treatment data only. Theta should be computed on the combined dataset (control plus treatment) for unbiased estimation. Computing on treatment alone re-introduces the bias CUPED is trying to remove.
• Pre-period contamination. The pre-period overlaps with the experiment period. Users in the experiment have already been exposed when their "pre-period" metric is measured. The pre-metric is not actually pre.
• CUPED on heavily skewed binary metrics. CUPED reduces variance but does not change the metric's distribution. A binary metric where 99 percent of users are 0 is still skewed after CUPED; the t-test is still misspecified.
• Stratification on a post-experiment covariate. The covariate must be measured before the experiment, otherwise the stratification creates selection bias. Use only pre-experiment covariates for stratification.
• Forgetting to validate that variance actually decreased. Run both adjusted and unadjusted analyses; verify the adjusted variance is smaller. If it is not, CUPED is not helping for this metric and pre-period.

Warehouse vs platform decision

Side-by-side comparison. Cost considerations at scale. Hybrid patterns. Migration patterns. Decision tree.

The principle. Both operational models are valid. The right choice depends on team strength, volume, custom-metric needs, and how much engineering time you are willing to invest in experimentation infrastructure.

---

Side-by-side comparison

---

Cost considerations at scale

Approximate costs as of 2025-2026. Verify with vendor pricing.

10K MAU

• Platform (mid-tier): $0 to $500 per month. Free tiers from PostHog, Statsig, Amplitude cover this.
• Warehouse-native: warehouse compute already paid for. Engineering time cost: 1 to 2 weeks of a data engineer's time to set up infrastructure.

Recommendation. Platform. The engineering time on warehouse-native at this scale does not pay back.

100K MAU

• Platform (mid-tier): $1K to $10K per month. Statsig, PostHog paid tiers; Optimizely contract.
• Warehouse-native: same warehouse compute; engineering time amortized over more experiments.

Recommendation. Either. Depends on team strength and custom-metric needs. Many teams stay on a platform; some graduate to warehouse-native.

1M MAU

• Platform: $10K to $80K per month, often more for enterprise contracts. Optimizely Enterprise can hit $200K+ per year.
• Warehouse-native: same warehouse compute. The engineering investment pays back at this scale.

Recommendation. Warehouse-native is increasingly attractive. Many enterprise data teams run warehouse-native primary, with a platform for specific use cases (frontend visual experiments).

10M+ MAU

• Platform: enterprise contracts at $200K to $1M+ per year.
• Warehouse-native: dominant pattern at this scale. Custom infrastructure plus dbt plus Python notebooks.

Recommendation. Warehouse-native, often with a thin platform for fast frontend iteration.

---

Hybrid patterns

Three common hybrid patterns.

Pattern 1: platform for product, warehouse-native for analytics

The platform handles assignment and exposure for product experiments. The warehouse handles analysis for the same experiments by reading the platform's exposure data.

Why. The platform's analysis is fine for standard cases; warehouse-native analysis allows custom metrics and segmentation the platform cannot express.

Operational shape. The platform's exposure events flow to the warehouse (typically via webhook or scheduled export). dbt models compute custom metrics; the analyst runs the t-test in Python against the joined data.

Pattern 2: warehouse-native for backend, platform for frontend

Warehouse-native handles backend experiments where assignment is server-side (pricing, recommendations, ML model variants). The platform handles frontend visual experiments (button colors, copy, layout).

Why. Frontend visual experiments benefit from the platform's WYSIWYG editor and script-tag injection. Backend experiments benefit from warehouse-native's metric flexibility.

Operational shape. Two separate experiment registries. Each team picks the right tool for the experiment type.

Pattern 3: platform for fast iteration, warehouse-native for hard cases

The platform handles experiments where time-to-result matters more than custom depth. Warehouse-native handles experiments where the platform cannot express the metric.

Why. Most experiments are standard; the platform is faster. The 10 percent that are not standard need the warehouse.

Operational shape. Default to platform; escalate to warehouse-native when the platform fails to support the experiment design.

---

Migration patterns

Platform to warehouse-native

Triggered by cost (the platform bill became a budget item) or capability (the platform cannot handle a specific experiment design that matters). Typical effort: 6 to 12 engineer-weeks for the first warehouse-native experiment with full infrastructure; later experiments amortize the investment.

Steps. Build the assignment hash function. Build the exposure logging discipline. Define the first metric in dbt. Run a parallel experiment on the platform and warehouse-native; verify results match within statistical noise. Migrate experiments one at a time; retire the platform when the last in-flight experiment completes.

Warehouse-native to platform

Triggered by team contraction (the data engineer who built the infrastructure left) or by velocity needs (experiments are taking too long to set up). Typical effort: 2 to 4 engineer-weeks; the platform handles the heavy lifting.

Steps. Pick the platform. Wire up assignment and exposure. Migrate the metric library to the platform's format. Run the first experiment on the platform; verify results align with what warehouse-native would have produced.

The frequent reverse migration. Companies that move to warehouse-native and discover the engineering investment is too much. The migration back is usually faster than the migration out.

---

Decision tree

Is your team running 5+ experiments per quarter?
├── No → Platform. Warehouse-native does not pay back at low volume.
└── Yes → Continue.
   │
   Do you have a data engineer and a data scientist?
   ├── No → Platform. Warehouse-native requires both roles.
   └── Yes → Continue.
      │
      Are your experiments primarily frontend visual?
      ├── Yes → Platform (Optimizely, VWO). Warehouse-native cannot match.
      └── No → Continue.
         │
         Do you need custom metrics the platform cannot express?
         ├── Yes → Warehouse-native. The custom metric is the use case.
         └── No → Continue.
            │
            Is the platform bill exceeding 10x the cost of the engineering time?
            ├── Yes → Warehouse-native. The math justifies it.
            └── No → Platform. The simplicity is worth the cost.

The defaults. New teams: platform. Mature teams with strong data infrastructure and high volume: hybrid or warehouse-native primary. The decision is never permanent; revisit annually.

---

When the math is wrong

Two situations where teams misjudge the build-vs-buy.

Underestimating engineering cost. "We can build this in a week" rarely holds. The first experiment takes weeks; the second takes days; the long tail of pitfalls (CUPED, sequential testing, SRM checks, dashboard reconciliation) adds up to months of engineering investment. Budget realistically.

Overestimating platform limitations. "The platform cannot handle our metric" is sometimes true and sometimes a workflow problem. Verify with the vendor; many platforms have advanced features (Cortex Analyst on Snowflake, custom SQL metrics on Statsig) that solve the apparent limitation.

The honest test. Run a 30-day proof of concept on the platform with the actual experiment you say it cannot handle. If the platform truly fails, warehouse-native is justified. If the platform works (even if imperfectly), the cost-benefit shifts back toward the platform.