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`](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