How to run A/B tests on your BigQuery data

BigQuery already contains the behavioral, billing, and account data that can make an experiment decision credible. The work is turning it into a reliable causal comparison.

A warehouse-native A/B testing workflow separates three concerns. Your application assigns a variation and records exposure. BigQuery stores or models exposures and outcomes. An analysis layer generates or executes SQL, checks data quality, and estimates the effect on metrics.

This shape avoids building a second behavioral-data pipeline solely for experimentation. It also lets revenue, retention, support, and operational guardrails use the same governed definitions as the rest of the business.

But a warehouse does not make a test valid automatically. Identity, exposure timing, metric windows, randomization units, late events, and query cost all need explicit decisions. The following workflow takes one experiment from instrumented assignment to an auditable result.

Start with an experiment data contract

Define the row-level meaning before choosing tables or writing SQL.

Exposure records the first opportunity to receive treatment

An exposure fact should include:

• experiment_id;
• variation_id;
• the identifier used for randomization;
• exposed_at in a documented time zone;
• assignment or feature revision;
• source application and environment;
• optional dimensions available before treatment.

Log at the same logical point for every variation. If control logs when a request begins but treatment logs after a component succeeds, failed treatment sessions disappear and bias the comparison.

GrowthBook's feature flag experiments can use SDK assignment and tracking callbacks, while its data source workflow analyzes warehouse records. Your contract must link those two sides with one stable unit ID.

Keep assignment, exposure, and outcome distinct

Assignment is the random decision. Exposure is the unit's first opportunity to experience it. Outcomes are later facts such as purchase, activation, latency, or retention.

Do not infer treatment from a downstream action. If only purchasers receive a variation property, non-purchasers vanish from the denominator. Preserve an exposure table even if assignments originate in application logs or an event collector.

Align the randomization and aggregation units

If accounts are randomized, aggregate outcomes by account before estimating uncertainty. Counting every event or user as an independent observation will usually understate variance. GrowthBook's experimental-unit guide explains why the unit that can independently receive treatment must anchor the analysis.

Model BigQuery tables for repeatable analysis

A practical layout has narrow canonical models rather than one universal event table.

-- One row per experiment-unit-variation exposure.
CREATE OR REPLACE VIEW `analytics.experiment_exposures` AS
SELECT
  experiment_id,
  variation_id,
  user_id,
  MIN(event_timestamp) AS first_exposed_at,
  ANY_VALUE(platform HAVING MIN event_timestamp) AS platform
FROM `raw.product_events`
WHERE event_name = 'experiment_viewed'
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND user_id IS NOT NULL
GROUP BY experiment_id, variation_id, user_id;

The exact exposure deduplication rule should be intentional. A user seen in two variations may indicate an unstable identifier or experiment reset. Do not silently keep the first row without surfacing the conflict.

Create separate fact models for reusable outcomes:

CREATE OR REPLACE VIEW `analytics.purchase_facts` AS
SELECT
  user_id,
  order_id,
  purchased_at,
  net_revenue
FROM `finance.orders`
WHERE order_status = 'completed';

This structure makes definitions reviewable, supports multiple experiments, and avoids embedding business logic inside every result query. GrowthBook's fact tables provide a reusable model for metrics and dimensions derived from warehouse facts.

Partition and cluster around analysis filters

Experiment queries normally filter by exposure and outcome dates, then join on a unit identifier. BigQuery's partitioned-table guidance explains how a qualifying partition predicate enables pruning. Clustering can further reduce scanned blocks when filters align with clustered columns.

A common pattern is daily partitioning by event or fact date and clustering by user_id, account_id, experiment_id, or event name based on dominant access paths. Validate with actual query plans and bytes processed; do not cluster every possible dimension.

Choose metrics before you query

Use the KPI playbook to define a primary outcome, guardrails, and diagnostic metrics that map to the product decision.

Read the KPI Playbook

Grant least-privilege BigQuery access

Use a dedicated service account or workload identity for the analysis system. Separate production application credentials from analytical query credentials.

Google's current BigQuery query permissions require permission to create a query job in the execution project and read the referenced tables or authorized views. Predefined roles commonly used for these responsibilities are BigQuery Job User on the query project and BigQuery Data Viewer on approved data resources, but your security team may prefer custom roles.

Use authorized views or curated datasets to hide columns that experiment analysis does not need. Keep credentials in a secret manager, define rotation ownership, and audit query activity. If the workflow materializes fact tables or cached results, grant write access only to a dedicated derived dataset.

Test access with representative queries. Row-level and column-level controls can change what the service sees; Google's row-level security documentation notes that partition pruning still depends on an appropriate partition-column predicate.

Define metrics from warehouse facts

A metric needs more than an aggregate function. Specify:

• unit of analysis;
• numerator and denominator;
• attribution window relative to exposure;
• conversion counting rule;
• missing-value behavior;
• currency and refund treatment;
• outlier policy;
• late-arriving data policy.

For a 7-day conversion metric, only count events after first exposure and before the earlier of 7 days or the analysis cutoff. Keep users with no conversion in the denominator.

WITH enrolled AS (
  SELECT experiment_id, variation_id, user_id, first_exposed_at
  FROM `analytics.experiment_exposures`
  WHERE experiment_id = @experiment_id
    AND DATE(first_exposed_at) BETWEEN @start_date AND @end_date
),
unit_metric AS (
  SELECT
    e.variation_id,
    e.user_id,
    COUNTIF(
      o.order_id IS NOT NULL
      AND o.purchased_at >= e.first_exposed_at
      AND o.purchased_at < TIMESTAMP_ADD(e.first_exposed_at, INTERVAL 7 DAY)
    ) > 0 AS converted
  FROM enrolled e
  LEFT JOIN `analytics.purchase_facts` o USING (user_id)
  GROUP BY e.variation_id, e.user_id
)
SELECT
  variation_id,
  COUNT(*) AS units,
  COUNTIF(converted) AS conversions,
  AVG(CAST(converted AS INT64)) AS conversion_rate
FROM unit_metric
GROUP BY variation_id;

Parameterize experiment ID and dates. Do not concatenate user-supplied identifiers into SQL. Add an explicit maturity cutoff when every enrolled unit needs a full 7-day observation window.

In GrowthBook, metrics can be built on fact tables and reused across experiments. Review generated SQL before operationalizing it so data and product owners agree on the denominator and attribution.

Connect GrowthBook to BigQuery

GrowthBook's warehouse-native experimentation queries existing data rather than requiring raw behavioral data to be copied into a separate event store.

A production connection workflow is:

1. Create the dedicated BigQuery principal and execution project.
2. Grant read access to curated exposure, metric, and dimension views.
3. Add the BigQuery data source in GrowthBook with the approved credentials and location.
4. Define an experiment-assignment query that returns the expected experiment, variation, unit, and timestamp fields.
5. Add fact tables, metrics, and pre-treatment dimensions.
6. preview SQL and reconcile sample rows with BigQuery.
7. Import or create a low-risk experiment and run an A/A phase.

Use separate data sources or environments when development data must not mix with production. Record the owner of every query template and model.

Validate the result pipeline

Reconcile enrollment counts

Compare application assignment logs, exposure events, the canonical exposure view, and the analysis result. Counts will not always match exactly—bots, consent, and late ingestion can be legitimate—but differences must have an explanation that applies symmetrically.

Check for:

• units in multiple variations;
• null or changing randomization IDs;
• exposures outside the configured phase;
• environment or experiment-key collisions;
• uneven join rates by variation;
• outcomes dated before exposure.

Use GrowthBook's sample ratio mismatch checks before reading metric lift. An allocation anomaly can make every downstream number misleading.

Run invariants and an A/A test

Treatment should not affect pre-exposure revenue, account age, or historical activity. If variation labels predict those values, inspect targeting and joins.

An A/A test with identical product behavior exercises real assignment, ingestion, transformation, metric, and statistical paths. It cannot prove future treatments are implemented correctly, but it creates a baseline for platform health.

Account for late data

BigQuery tables may receive events after their event date due to mobile offline behavior, refunds, or backfills. Define when results are preliminary and when each metric is mature. Re-run historical windows after an expected lag, and monitor large post-decision changes.

Control BigQuery cost and latency

Experiment analysis can scan the same facts repeatedly as data accumulates. Make cost visible before scaling cadence.

Google's query-computation best practices recommend projecting only needed columns, filtering early, and using partitioning or materialized results where appropriate. A LIMIT on an unclustered SELECT * does not reduce bytes read.

Apply these patterns:

• require partition filters on large event tables;
• pre-aggregate events to the randomization unit;
• select explicit columns;
• restrict outcome scans to the maximum attribution window;
• reuse canonical fact tables across metrics;
• materialize stable, expensive transformations;
• separate exploratory and scheduled workloads;
• label query jobs by application and environment.

Before deploying a new query, use a BigQuery dry run to validate syntax and estimate bytes processed without consuming query slots. Monitor INFORMATION_SCHEMA.JOBS for bytes, slot time, cache behavior, errors, and repeated patterns.

Turn results into a decision

Do not reduce an experiment to “p < 0.05.” Read the estimated effect, interval, practical threshold, data-quality checks, primary metric, and guardrails together. GrowthBook supports frequentist and Bayesian analysis and makes the query path inspectable, but the decision policy still belongs to the team.

Document the hypothesis, population, unit, exposure, planned duration, minimum detectable effect, metric versions, query revisions, and launch decision. If a pipeline bug changes the analysis population, fix it and restart rather than trying to negotiate with a biased result.

BigQuery is most valuable here not because SQL can calculate an average, but because it can connect randomized exposure to the business outcomes already modeled across your organization. Preserve that connection with stable contracts, least-privilege access, efficient facts, and repeatable QA.

Roll out the analysis system in controlled stages

Begin with one exposure source and two metrics: a binary outcome and a count or revenue outcome. Reanalyze a completed experiment whose population and result the data team already understands. Compare eligible units, first exposures, crossovers, mature outcomes, sums, means, and variances before comparing intervals.

Next, run an A/A test through the real assignment and ingestion path. Exercise the same scheduled jobs, service account, metric models, and result refreshes intended for production. Introduce a safe failure in a development project—such as revoking a view grant or changing a test column—and confirm that the error is visible and does not produce a partial result that looks final.

Only then enable self-service metric selection. Give every metric an owner, grain, timestamp, allowed units, conversion window, lag expectation, and change policy. Require review for definitions that scan a new dataset or materially increase bytes processed.

Finally, establish an operating cadence. Data owners review failed and expensive jobs; experiment owners review health checks before effects; platform owners rotate credentials and audit grants; finance receives attributable query usage. This staged rollout turns a collection of working queries into an experimentation service that can scale without hiding its data and compute obligations.

Define how schema changes reach the service. Additive columns should not alter queries that select explicit fields, while renamed identifiers or type changes should fail in a test project before production. Keep small fixtures covering zero outcomes, repeated exposures, crossovers, late events, refunds, and account-level randomization. Run those fixtures whenever assignment or metric templates change.

Also decide whether completed results are frozen or recomputed. Recalculation can incorporate corrected warehouse data, but it can also rewrite the evidence behind an old decision. Preserve the data cutoff, query version, metric version, and original decision even when a corrected view is available.

Make that policy visible in the result. Label preliminary, mature, frozen, and restated analyses distinctly so a product decision is never compared with a later recalculation as if they were the same artifact.

Run tests on your BigQuery data

Connect warehouse metrics to transparent experiment analysis and start with a production-shaped A/A test.

Start Building Free