The SQL behind an A/B test: Writing experiment queries in Snowflake

A Snowflake A/B test query is only trustworthy when its rows preserve the experiment's random assignment.

Calculating the average outcome for control and treatment is easy. Building the correct denominator is harder. A plausible result can still include outcomes before exposure, count events instead of randomized users, mix staging with production, drop non-converters, or compare a mature control window with an immature treatment window.

This guide builds the SQL in layers: first exposure, exposure-quality checks, post-exposure outcomes, one value per randomization unit, variation summaries, and operational QA. It also explains which work belongs in Snowflake and which work is safer in a tested statistical engine.

The examples assume user-level randomization and completed-order revenue. Replace database, schema, table, timestamp, environment, and business-status values before running them. Use a development role and bounded dates first.

Define the analytical contract

Assume these tables.

ANALYTICS.EXPERIMENT_EXPOSURES contains:

• EXPERIMENT_ID VARCHAR
• USER_ID VARCHAR
• VARIATION_ID VARCHAR
• EXPOSED_AT TIMESTAMP_TZ
• ENVIRONMENT VARCHAR

ANALYTICS.ORDERS contains:

• ORDER_ID VARCHAR
• USER_ID VARCHAR
• ORDER_AT TIMESTAMP_TZ
• NET_REVENUE NUMBER(18,2)
• ORDER_STATUS VARCHAR

An exposure means the user had a real opportunity to experience the assigned variation. A background flag refresh or an eligibility lookup is not necessarily exposure. Write this semantic rule beside the schema.

The analysis unit must match assignment. If accounts are randomized, use ACCOUNT_ID and aggregate all user events to one account value. Foreign-key joins do not make user rows statistically independent inside an assigned account.

Use half-open intervals: >= start and < end. They compose without overlap when a scheduled job advances from one analysis window to the next.

Select the first exposure and identify crossovers

This query keeps repeated exposure rows for diagnostics, counts distinct variations per user, selects the earliest qualifying exposure, and excludes users observed in both groups.

WITH raw_exposures AS (
  SELECT
    experiment_id,
    user_id,
    variation_id,
    exposed_at
  FROM YOUR_DATABASE.ANALYTICS.EXPERIMENT_EXPOSURES
  WHERE exposed_at >= '2026-08-01 00:00:00 +00:00'::TIMESTAMP_TZ
    AND exposed_at <  '2026-08-22 00:00:00 +00:00'::TIMESTAMP_TZ
    AND experiment_id = 'checkout-copy-v3'
    AND environment = 'production'
    AND user_id IS NOT NULL
    AND variation_id IN ('control', 'treatment')
),
exposure_quality AS (
  SELECT
    user_id,
    COUNT(DISTINCT variation_id) AS variations_seen,
    COUNT(*) AS exposure_rows
  FROM raw_exposures
  GROUP BY user_id
),
first_exposure AS (
  SELECT
    experiment_id,
    user_id,
    variation_id,
    exposed_at AS first_exposed_at
  FROM raw_exposures
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY experiment_id, user_id
    ORDER BY exposed_at, variation_id
  ) = 1
),
eligible_exposures AS (
  SELECT f.*
  FROM first_exposure AS f
  JOIN exposure_quality AS q USING (user_id)
  WHERE q.variations_seen = 1
)
SELECT *
FROM eligible_exposures;

Snowflake evaluates QUALIFY after window functions, so the query can filter ROW_NUMBER() without another nested select. The variation key breaks identical-timestamp ties deterministically; identical cross-variation timestamps should still trigger investigation.

Do not discard the crossover measure after filtering. It is an operational signal for unstable identity, non-sticky assignment, delayed configuration, environment overlap, or duplicated pipelines.

Create one post-exposure value per user

Extend the same CTEs with the following unit-value and variation-summary steps. The broad order bounds improve pruning; user-specific predicates enforce the fourteen-day conversion window.

WITH raw_exposures AS (
  SELECT experiment_id, user_id, variation_id, exposed_at
  FROM YOUR_DATABASE.ANALYTICS.EXPERIMENT_EXPOSURES
  WHERE exposed_at >= '2026-08-01 00:00:00 +00:00'::TIMESTAMP_TZ
    AND exposed_at <  '2026-08-22 00:00:00 +00:00'::TIMESTAMP_TZ
    AND experiment_id = 'checkout-copy-v3'
    AND environment = 'production'
    AND user_id IS NOT NULL
    AND variation_id IN ('control', 'treatment')
),
exposure_quality AS (
  SELECT user_id, COUNT(DISTINCT variation_id) AS variations_seen
  FROM raw_exposures
  GROUP BY user_id
),
first_exposure AS (
  SELECT
    experiment_id,
    user_id,
    variation_id,
    exposed_at AS first_exposed_at
  FROM raw_exposures
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY experiment_id, user_id
    ORDER BY exposed_at, variation_id
  ) = 1
),
eligible_exposures AS (
  SELECT f.*
  FROM first_exposure AS f
  JOIN exposure_quality AS q USING (user_id)
  WHERE q.variations_seen = 1
),
unit_values AS (
  SELECT
    e.variation_id,
    e.user_id,
    COUNT(DISTINCT o.order_id) > 0 AS converted,
    COALESCE(SUM(o.net_revenue), 0) AS revenue
  FROM eligible_exposures AS e
  LEFT JOIN YOUR_DATABASE.ANALYTICS.ORDERS AS o
    ON o.user_id = e.user_id
   AND o.order_at >= '2026-08-01 00:00:00 +00:00'::TIMESTAMP_TZ
   AND o.order_at <  '2026-09-05 00:00:00 +00:00'::TIMESTAMP_TZ
   AND o.order_at >= e.first_exposed_at
   AND o.order_at < DATEADD('day', 14, e.first_exposed_at)
   AND o.order_status = 'completed'
  GROUP BY e.variation_id, e.user_id
)
SELECT
  variation_id,
  COUNT(*) AS units,
  COUNT_IF(converted) AS converted_units,
  COUNT_IF(converted) / NULLIF(COUNT(*), 0) AS conversion_rate,
  AVG(revenue) AS mean_revenue_per_unit,
  VAR_SAMP(revenue) AS sample_variance_revenue,
  SUM(revenue) AS total_revenue
FROM unit_values
GROUP BY variation_id
ORDER BY variation_id;

The LEFT JOIN retains users with zero completed orders. Keep order filters inside the join. A final WHERE o.order_status = 'completed' would remove null matches, turn the analysis into a converter-only comparison, and inflate the metric.

Aggregating to unit_values before the variation summary protects the experimental sample size. Revenue events are not independently randomized; users are. VAR_SAMP returns the dispersion of user-level revenue that a statistical engine needs.

The query uses Snowflake's 0 to express the outcome window relative to each user's first exposure. Keep that per-user rule even when a broad literal predicate is added for pruning.

The summary is not a complete significance test. SQL is well suited to population construction and sufficient statistics. A tested statistical layer should handle confidence intervals or Bayesian posteriors, sequential monitoring, variance reduction, and multiple comparisons. A public discussion about warehouse-native A/B test analysis illustrates both the transparency of this approach and the platform work needed around the SQL.

Put Snowflake metrics to work

Connect governed exposures and outcomes to transparent experiment analysis without rebuilding the statistical workflow for every test.

Start Building Free

Calculate descriptive lift for reconciliation

Use a pivot only after the variation summaries are correct. This helps compare an experimentation UI with analyst-owned SQL.

WITH variation_summary AS (
  -- Replace this comment with the complete query above through unit_values.
  SELECT
    variation_id,
    COUNT(*) AS units,
    COUNT_IF(converted) / NULLIF(COUNT(*), 0) AS conversion_rate,
    AVG(revenue) AS revenue_per_unit
  FROM unit_values
  GROUP BY variation_id
),
pivoted AS (
  SELECT
    MAX(IFF(variation_id = 'control', conversion_rate, NULL)) AS control_cvr,
    MAX(IFF(variation_id = 'treatment', conversion_rate, NULL)) AS treatment_cvr,
    MAX(IFF(variation_id = 'control', revenue_per_unit, NULL)) AS control_rpu,
    MAX(IFF(variation_id = 'treatment', revenue_per_unit, NULL)) AS treatment_rpu
  FROM variation_summary
)
SELECT
  control_cvr,
  treatment_cvr,
  treatment_cvr - control_cvr AS cvr_absolute_change,
  (treatment_cvr - control_cvr) / NULLIF(control_cvr, 0) AS cvr_relative_lift,
  control_rpu,
  treatment_rpu,
  treatment_rpu - control_rpu AS rpu_absolute_change,
  (treatment_rpu - control_rpu) / NULLIF(control_rpu, 0) AS rpu_relative_lift
FROM pivoted;

Return NULL when the control mean is zero instead of manufacturing a relative percentage. Always preserve absolute differences in the original unit: percentage points for conversion and currency per randomized unit for revenue.

Observed lift alone does not answer whether to ship. Define the smallest practically useful effect before launch, then interpret uncertainty and guardrails against that threshold.

Run quality checks before interpreting effects

Sample ratio mismatch

For a nominal 50/50 allocation, calculate the Pearson chi-square statistic from eligible counts. Use a statistics library or experimentation platform for the p-value and alert policy.

WITH counts AS (
  SELECT variation_id, COUNT(*) AS observed
  FROM eligible_exposures
  GROUP BY variation_id
),
totals AS (
  SELECT SUM(observed) AS total_units FROM counts
)
SELECT
  SUM(
    POWER(observed - total_units * 0.5, 2)
    / NULLIF(total_units * 0.5, 0)
  ) AS chi_square_statistic
FROM counts
CROSS JOIN totals;

A failed sample ratio mismatch check means the observed variation counts do not match allocation closely enough for the configured threshold. It does not identify the cause. Check targeting, assignment, exposure emission, warehouse ingestion, filters, joins, and missing IDs.

Crossover rate

SELECT
  COUNT(*) AS exposed_units,
  COUNT_IF(variations_seen > 1) AS crossover_units,
  COUNT_IF(variations_seen > 1) / NULLIF(COUNT(*), 0) AS crossover_rate,
  MAX(exposure_rows) AS max_exposure_rows_for_one_unit
FROM exposure_quality;

Repeated evaluation in one variation can be normal. A unit seen in two variations has ambiguous treatment. Report and investigate it even when the main query excludes it.

Fact-table grain

If the order fact promises one row per order, test the promise.

SELECT order_id, COUNT(*) AS rows_per_order
FROM YOUR_DATABASE.ANALYTICS.ORDERS
WHERE order_at >= '2026-08-01 00:00:00 +00:00'::TIMESTAMP_TZ
  AND order_at <  '2026-09-05 00:00:00 +00:00'::TIMESTAMP_TZ
GROUP BY order_id
HAVING COUNT(*) > 1;

An empty result passes. If the source stores order versions, create a model that selects the current valid row using explicit effective-time logic. Do not add DISTINCT to the experiment query and hide uncertainty about grain.

Pre-exposure outcome leakage

SELECT
  COUNT(*) AS pre_exposure_order_rows,
  COUNT(DISTINCT e.user_id) AS affected_users
FROM eligible_exposures AS e
JOIN YOUR_DATABASE.ANALYTICS.ORDERS AS o
  ON o.user_id = e.user_id
WHERE o.order_at >= '2026-07-18 00:00:00 +00:00'::TIMESTAMP_TZ
  AND o.order_at < e.first_exposed_at;

Prior orders are valid inputs for pre-experiment covariates or eligibility. They are not post-treatment revenue. Separating these windows is essential when applying CUPED.

Handle metric maturity and late-arriving facts

A user exposed yesterday has not completed a fourteen-day outcome window. Either include only mature users or use a cumulative method that compares equal follow-up across variations.

For a mature-cohort analysis, add:

WHERE DATEADD('day', 14, first_exposed_at)
      <= '2026-09-05 00:00:00 +00:00'::TIMESTAMP_TZ

Use an as_of time that reflects source completeness, not merely CURRENT_TIMESTAMP(). Subscription renewals, refunds, offline events, and batch ingestion can update old periods. Publish a metric-lag policy and re-run historical windows when late data is expected.

Time zones need equal care. Store instant timestamps consistently, then derive business dates in an explicit zone. A revenue day based on an account locale may not align with an exposure day in UTC. Implicit session time zones make results difficult to reproduce.

Identity models must be effective-dated. Joining historical exposures to the current anonymous-to-authenticated identity map can rewrite past unit membership. Freeze or reconstruct the mapping as it was known for the analysis contract.

Make Snowflake experiment queries efficient

Snowflake automatically stores table data in micro-partitions and can prune them when predicates align with useful metadata. The micro-partition and clustering documentation explains why bounded time filters and natural clustering matter on large event tables.

Apply these practices:

• select only necessary columns;
• use literal or clearly bound time ranges around every large fact;
• aggregate raw events to reusable unit-level facts;
• avoid repeatedly scanning the same exposure and identity transformations;
• use a dedicated, auto-suspending analysis warehouse;
• size up only when reduced runtime offsets higher credit consumption;
• schedule broad refreshes away from interactive workloads;
• set a query tag for attribution.

Set the tag before an analysis session or in the service connection:

ALTER SESSION SET QUERY_TAG =
  '{"application":"experimentation","metric":"net_revenue"}';

Snowflake Query History can filter by user, warehouse, query tag, duration, and query hash. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY provides longer-lived metadata such as bytes scanned, queue time, errors, warehouse size, and query tag.

Use a dedicated warehouse and attach a resource monitor with notifications and suspension thresholds. Resource monitors cover user-managed warehouses, not every serverless service, so pair them with broader budgets where necessary.

Connect the query model to GrowthBook

SQL alone can produce an audit result. An experimentation program also needs reusable metrics, diagnostics, permissions, statistical methods, result history, and decision workflows.

GrowthBook's warehouse-native architecture queries Snowflake data and exposes generated SQL. Configure:

1. a dedicated Snowflake user, role, and analysis warehouse;
2. an experiment-assignment query equivalent to the first-exposure population;
3. a reusable fact table with unit, timestamp, and value columns;
4. metric definitions for conversion and revenue;
5. conversion windows, caps, covariates, guardrails, and statistical settings;
6. an A/A test and a completed A/B reconciliation.

Preview the generated SQL. Compare eligible units, crossovers, mature units, sums, means, and variances with the reference. If they differ, resolve the data contract before comparing p-values or credible intervals.

GrowthBook can then reuse those governed metrics across experiment analysis and warehouse-native product analytics, reducing drift between dashboards and decisions.

Production checklist

Before a Snowflake result informs a release decision, confirm:

• exposure represents an opportunity to receive treatment;
• the randomization unit matches the metric grain;
• first exposure is deterministic;
• crossovers are measured and handled consistently;
• environment and eligibility filters are explicit;
• primary outcomes occur after exposure;
• non-converters remain in the denominator;
• follow-up windows are mature or comparable;
• joins cannot multiply units;
• allocation, duplicates, null IDs, and data lag are monitored;
• every large table has a bounded predicate;
• query tags, warehouse usage, and credits are visible;
• statistical inference uses a tested implementation;
• metric changes are owned, reviewed, and versioned.

Snowflake SQL is the executable expression of an experiment's population and metric rules. Treat it like production code: make assumptions explicit, test the grain, preserve zeroes, bound time, inspect cost, and reconcile against a known result. Then use a shared analysis layer to apply consistent statistics and retain the decision.

Scale beyond Snowflake SQL

Reuse governed warehouse metrics, inspect every generated query, and give teams a consistent path from exposure to decision.

Build with GrowthBook