How to run A/B tests on your Redshift data

Redshift can turn randomized exposures into decision-ready experiment evidence without duplicating your canonical customer data into another analytics system.

The architecture is straightforward: applications assign variations and record exposure; Redshift stores or models exposure and outcome facts; an analysis layer runs SQL, validates the sample, and estimates treatment effects.

The difficult parts are definitions and workload design. Exposure must happen at the same logical moment in every arm. Revenue needs a precise attribution window. Repeated experiment refreshes need to coexist with ELT, dashboards, and other warehouse users. Distribution, sorting, and materialization should follow the data rather than generic tuning advice.

This workflow covers the full path: a stable data contract, unit-level SQL, least-privilege access, Redshift workload controls, GrowthBook integration, and quality checks before anyone declares a winner.

Define exposure and outcome facts

An exposure model should return one analyzable assignment per randomization unit and phase. Preserve raw records separately so conflicts can be investigated.

Required fields are:

• experiment and variation IDs;
• the exact user, account, or device ID used to randomize;
• first valid exposure timestamp;
• experiment phase or assignment revision;
• environment and source application;
• pre-treatment dimensions used for QA or planned segments.

GrowthBook's feature flag experiment documentation describes assignment through feature flags and exposure tracking. Log when the unit first has an equal opportunity to receive either experience, not after a variation-specific component finishes.

Outcomes belong in reusable facts: orders, sessions, activations, support contacts, latency, and retention states. Each fact needs a durable deduplication key, unit ID, event time, and values required by the metric.

Choose one unit and keep it consistent

If organizations receive the treatment, randomize and aggregate organizations. Joining every organization exposure to many users and treating those users as independent makes intervals artificially narrow. GrowthBook's experimental-unit guide explains how the independent assignment level anchors statistical analysis.

Write a unit-level Redshift analysis query

Build one row per randomized unit before summarizing variations.

WITH first_exposure AS (
  SELECT user_id, variation_id, exposed_at
  FROM (
    SELECT
      user_id,
      variation_id,
      exposed_at,
      ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY exposed_at
      ) AS exposure_rank
    FROM analytics.experiment_exposures
    WHERE experiment_id = :experiment_id
      AND exposed_at >= :phase_start
      AND exposed_at < :phase_end
  ) ranked
  WHERE exposure_rank = 1
),
unit_metric AS (
  SELECT
    e.variation_id,
    e.user_id,
    MAX(CASE
      WHEN o.completed_at >= e.exposed_at
       AND o.completed_at < DATEADD(day, 7, e.exposed_at)
      THEN 1 ELSE 0
    END) AS converted
  FROM first_exposure e
  LEFT JOIN commerce.orders o ON o.user_id = e.user_id
  GROUP BY e.variation_id, e.user_id
)
SELECT
  variation_id,
  COUNT(*) AS units,
  SUM(converted) AS conversions,
  AVG(converted::decimal(18, 8)) AS conversion_rate
FROM unit_metric
GROUP BY variation_id;

Bind parameters through the query client. Add a maturity cutoff so every enrolled unit has the full 7-day window, and keep units with no order in the denominator.

This query reports aggregates; a production experiment engine must also estimate uncertainty and implement ratio metrics, outlier handling, multiple testing, and any sequential decision policy consistently. GrowthBook's metric definitions and fact tables make those models reusable across experiments.

Turn warehouse facts into KPIs

Define primary outcomes, guardrails, and diagnostic metrics before your Redshift queries become rollout decisions.

Read the KPI Playbook

Design Redshift tables around measured access patterns

Redshift distributes and scans data across compute. Experiment workloads commonly filter by experiment and time, then join exposures with large outcome facts on a unit ID.

AWS's Redshift performance overview describes the roles of massively parallel processing, columnar storage, compression, distribution, sorting, and result caching.

Use these as hypotheses, then inspect plans and system views:

• sort large append-only facts so time filters can skip blocks;
• consider co-location on the unit ID for recurring large joins;
• let automatic table optimization manage changing workloads where it fits;
• analyze table statistics after material data changes;
• filter experiment and outcome windows before expensive joins;
• select only the columns needed by the metric;
• avoid repeatedly parsing broad semi-structured payloads in result queries.

Do not force a distribution key onto small dimension tables or copy a design from another workload. Data skew can be worse than redistribution.

Materialize expensive, stable transformations

If every refresh joins billions of raw events into the same unit-day facts, persist the work. Amazon Redshift materialized views store a precomputed result and can enable automatic query rewrites when eligible.

Make freshness explicit. Automatic refresh is workload-dependent, and an experiment dashboard must show the source cutoff. Schedule or manually control refreshes when decision timing matters.

Isolate and govern the workload

Experiment analysis is bursty: many queries run after ingestion, near a decision meeting, or when a dashboard refreshes. Do not allow it to crowd out business-critical ELT.

Amazon Redshift workload management can classify queries, assign priorities, and apply query-monitoring rules. Put the experimentation principal or query group in an identifiable workload class. Monitor queue time, execution time, temporary spill, scanned rows, and aborted queries.

For stronger isolation, Redshift data sharing can expose live data to another provisioned cluster or Serverless workgroup without manually copying it. A separate consumer gives experimentation its own compute and chargeback boundary while the producer remains the governed source.

Isolation does not remove cost. Set refresh expectations, cache stable facts, and prevent a dashboard from launching redundant full-history queries.

Grant least-privilege access

Create a dedicated database principal or federated identity. Grant usage on approved schemas and select on curated views, not blanket access to raw customer tables. Require encrypted connections and rotate credentials through AWS-native secret management.

A simplified pattern is:

CREATE USER experiment_reader PASSWORD DISABLE;
GRANT USAGE ON SCHEMA experiment_analytics TO experiment_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA experiment_analytics
TO experiment_reader;

Authentication details differ for IAM, provisioned clusters, and Serverless, so follow the organization's current Redshift access model. The key property is a reader that cannot modify source facts. If derived tables are required, write only to a dedicated schema with quotas and clear ownership.

AWS documents read access through data sharing at database, schema, table, view, and materialized-view levels. Test the final principal against every generated query rather than validating with an administrator account.

Connect GrowthBook to Redshift

GrowthBook's warehouse-native architecture analyzes data in connected sources. A Redshift implementation follows this sequence:

1. Create the governed reader and choose its WLM or consumer boundary.
2. Add curated exposure, fact, and dimension models.
3. Configure the Redshift data source in GrowthBook.
4. Define the assignment query and preview returned fields.
5. Define metrics with unit, window, denominator, and outlier behavior.
6. Run generated SQL under the production reader.
7. Reconcile a small sample and aggregate counts with direct queries.
8. Run an A/A test before a high-stakes treatment.

GrowthBook's experiment analysis workflow lets teams inspect results built from warehouse data. Keep query text, model versions, and data cutoffs with the experiment record so a later audit can reproduce the decision.

Validate data quality before effect size

Check these in order:

1. Expected versus observed allocation by phase.
2. Units exposed to multiple variations.
3. Null or changing randomization IDs.
4. Exposure and outcome timestamps outside valid windows.
5. Join rates and late-arrival rates by variation.
6. Pre-treatment metrics and invariant dimensions.
7. Metric maturity and materialized-view freshness.

GrowthBook's sample ratio mismatch checks identify suspicious allocation. An SRM is not a reason to adjust the p-value and continue; trace it through assignment, ingestion, filters, and joins.

Data testing should also run upstream. Community guidance on Redshift data quality emphasizes catching problems before consumers depend on them. Add uniqueness, accepted-value, freshness, and referential checks to the models that feed experiment analysis.

Make the rollout decision reproducible

Read the primary estimate, interval, minimum meaningful effect, and guardrails together. A tiny statistically detectable lift may not pay for engineering or operational cost. A positive primary metric may still be unacceptable if refunds, errors, or latency degrade.

Record the hypothesis, population, randomization unit, assignment and exposure versions, metric definitions, phase dates, analysis method, warehouse cutoff, and final action. If data quality fails, repair the pipeline and restart rather than salvaging a biased phase.

Redshift is already built for large analytical joins. A reliable experimentation workflow focuses that capability on a causal contract: trustworthy exposure, one row per randomized unit, governed metrics, isolated compute, and visible diagnostics. With those foundations, the warehouse becomes part of the product-development loop rather than a retrospective reporting system.

Design the operating model around Redshift behavior

Experiment analysis is a recurring workload, not a single successful query. Assign it to an explicit workload-management queue or Serverless workgroup so scheduled refreshes and ad hoc segments do not starve revenue reporting. Define concurrency, timeout, and priority policies with the warehouse owner, then test them with overlapping experiment jobs.

Observe both query and queue time. A fast query that waits twenty minutes produces stale decisions. Record query identifiers, service user, queue, scanned rows, spill behavior, and output freshness. Use stable query labels or comments so platform work remains attributable after SQL literals change.

Sort and distribution choices should follow measured joins. Exposure and metric facts commonly filter on time and join on a unit identifier. A distribution choice that helps one table can increase skew elsewhere, so inspect plans and system views on production-shaped data before changing table design. Revisit the decision as experiment volume and unit cardinality grow.

Late-arriving events require bounded recomputation. Store event time and ingestion time, publish an analysis cutoff, and rebuild recent fact partitions or materialized views when refunds, offline events, or identity corrections arrive. Keep the old and new metric versions distinguishable if a completed decision could change.

Finally, test resource failure. Pause or resize the workgroup, revoke one source grant, expire a credential, and introduce a schema change in a non-production environment. A healthy system marks results stale, surfaces an actionable error, and retries under controlled limits. It must not show a partially refreshed variation as comparable with a completed one.

Preserve identity and metric versions

Use effective-dated mappings when anonymous users join accounts or accounts change plans. Joining historical exposure to a current dimension table can move units between segments after the experiment. Preserve attributes at exposure or use valid-time joins that can be reproduced.

Each metric fact should declare its unit, timestamp, value, source cutoff, and grain. For revenue, define refunds, currency conversion, taxes, caps, and negative values. For ratios, keep numerator and denominator components per randomized unit. For retention, identify which cohorts have completed follow-up.

Version these definitions. Decide whether corrected source data restates completed experiments or produces a separate revised view. In either case, retain the original query, cutoff, result, and decision so the program's history remains auditable.

Use an acceptance test for platform changes

Keep one synthetic A/A dataset and several frozen completed experiments as regression fixtures. Include no-outcome units, repeated exposures, crossovers, late events, identity changes, refunds, and phase boundaries. Re-run them when shared SQL, materialized models, WLM routing, drivers, or warehouse versions change.

Compare eligible counts, exclusions, per-arm sums, means, and variances before comparing intervals. Record expected numerical tolerances and require an explanation for every material difference.

This suite catches semantic regressions that ordinary SQL syntax tests miss. It also gives the data team a safe way to optimize distribution, sort order, or materialization without asking active experiments to serve as the first production test.

Run the suite on a schedule as well as during releases. Data volume, skew, queue contention, and source lag change without a code deployment. Alert on population differences, runtime regressions, spill, stale facts, and unexplained allocation failures.

Retain the fixture versions and expected summaries with the platform runbook. When a regression appears, operators can distinguish a warehouse-performance problem from a change in experiment semantics and route it to the right owner.

Record the tested cluster or workgroup configuration so performance expectations remain tied to a reproducible execution environment rather than an abstract query.

Run transparent tests on Redshift

Connect governed warehouse facts to experiment analysis your data and product teams can inspect together.

Start Building Free