Warehouse-native A/B testing on ClickHouse at high event volume

ClickHouse can query immense event streams quickly, but experiment validity still begins with one trustworthy exposure and one independent analysis row per randomized unit.

High-volume products generate far more rows than an A/B test needs as statistical observations. A user may create thousands of clicks, impressions, requests, or telemetry points. Treating those rows as independent units produces false precision, even if the query completes in milliseconds.

The right architecture preserves granular events for audit and flexible metrics, then deduplicates exposure and aggregates outcomes at the unit assigned to a variation. ClickHouse's physical design and pre-aggregation features make that work efficient when they are aligned with recurring experiment queries.

Define an append-only event contract

Record exposure separately from outcomes. Required exposure fields are experiment ID, variation, randomization-unit ID, event time, phase or revision, environment, and source.

CREATE TABLE analytics.experiment_events
(
  event_date Date MATERIALIZED toDate(event_time),
  event_time DateTime64(3, 'UTC'),
  experiment_id LowCardinality(String),
  variation_id LowCardinality(String),
  subject_id String,
  phase_id String,
  environment LowCardinality(String),
  source LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (experiment_id, event_date, subject_id, event_time);

This key is an example, not a universal prescription. ClickHouse's query optimization guide emphasizes that ORDER BY selection strongly affects which data can be skipped and how efficiently queries aggregate. Benchmark the dominant experiment and retention filters with representative volume.

Do not use mutable in-place updates as the primary correction model for event history. Append corrections or rebuild bounded partitions through a controlled pipeline, and make deduplication rules explicit.

Log exposure at the decision boundary

Log when the assigned behavior can first affect the subject. If control records exposure before rendering and treatment records only after a slow component succeeds, the comparison is selected differently by variation.

GrowthBook's feature flag experiments connect randomized flag assignment with tracking. Preserve the exact ID used to randomize so ClickHouse metrics can aggregate at the same level.

Deduplicate exposure before joining outcomes

Use first valid exposure per subject and phase, while separately counting conflicting variations.

WITH exposures AS
(
  SELECT
    subject_id,
    argMin(variation_id, event_time) AS variation_id,
    min(event_time) AS exposed_at,
    uniqExact(variation_id) AS variation_count
  FROM analytics.experiment_events
  WHERE experiment_id = {experiment_id:String}
    AND environment = 'production'
    AND event_date BETWEEN {start_date:Date} AND {end_date:Date}
  GROUP BY subject_id
)
SELECT
  variation_id,
  count() AS subjects,
  countIf(variation_count > 1) AS conflicting_subjects
FROM exposures
GROUP BY variation_id;

Bind typed query parameters. A subject in multiple variations can indicate an unstable ID, phase collision, or duplicated experiment key. Do not quietly accept the first record and continue.

The experimental-unit guide explains why the independently randomized unit anchors both the metric row and variance calculation.

Pick metrics that survive scale

Define primary outcomes and guardrails before event volume turns every experiment into an expensive ad hoc query.

Read the KPI Playbook

Aggregate outcomes to one row per unit

Suppose order_events stores one completed order per event. A 7-day conversion calculation should retain non-converters and collapse repeated orders.

WITH exposures AS
(
  SELECT
    subject_id,
    argMin(variation_id, event_time) AS variation_id,
    min(event_time) AS exposed_at
  FROM analytics.experiment_events
  WHERE experiment_id = {experiment_id:String}
    AND event_date BETWEEN {start_date:Date} AND {end_date:Date}
  GROUP BY subject_id
),
unit_metric AS
(
  SELECT
    e.variation_id,
    e.subject_id,
    max(toUInt8(
      o.completed_at >= e.exposed_at
      AND o.completed_at < e.exposed_at + INTERVAL 7 DAY
    )) AS converted
  FROM exposures e
  LEFT JOIN analytics.order_events o
    ON o.subject_id = e.subject_id
  GROUP BY e.variation_id, e.subject_id
)
SELECT
  variation_id,
  count() AS units,
  sum(converted) AS conversions,
  avg(converted) AS conversion_rate
FROM unit_metric
GROUP BY variation_id;

Filter the order scan to the maximum possible outcome window in production SQL; otherwise the join may read unrelated history. Exclude recently exposed units until the full window has matured, or clearly mark their metrics preliminary.

GrowthBook fact tables and metrics provide reusable warehouse definitions. Review the generated SQL and compare a completed experiment with an independent ClickHouse query.

Design for high ingestion volume

Batch small events before insertion. Too many tiny inserts create excessive parts and merge work. Define event IDs and deduplication upstream, and monitor part counts, merge pressure, replication, disk, and ingestion lag.

Use low-cardinality types for genuinely low-cardinality dimensions, codecs based on measured data, and TTL policies tied to retention and audit needs. Never expire raw exposure before every dependent long-term metric and replay obligation has finished.

The ClickHouse case study of 100+ billion event analytics illustrates why event-centric workloads fit the engine, but your proof of concept should reproduce your joins, concurrency, retention, and correctness constraints rather than rely on another system's headline scale.

Pre-aggregate only stable work

At high cadence, repeated joins over raw events can dominate latency. Incremental materialized views can populate aggregate states as blocks arrive.

ClickHouse's guide to materialized views and aggregate states shows the *State and *Merge pattern used with AggregatingMergeTree. This can reduce query-time rows dramatically.

Good pre-aggregation candidates include:

• first exposure by experiment and subject;
• daily subject-level counts and sums;
• stable operational guardrails by experiment and variation;
• metric inputs that retain numerator, denominator, count, sum, and variance components.

Do not materialize only the final conversion rate. The statistics engine may need unit-level distribution or sufficient statistics, and metric definitions may evolve. Keep raw data and version aggregate schemas.

Projections can offer alternate ordering or pre-aggregation while clients query the base table; ClickHouse documents projection-based optimization. Compare storage, insert overhead, optimizer selection, and maintenance cost with a separate materialized model.

Manage joins and memory deliberately

Experiment queries often join a relatively narrow exposure set to a very large outcome stream. Filter and aggregate each side before the join. Select only required columns and inspect EXPLAIN plus query logs.

Test the join algorithm and memory behavior under the production version and topology. A query that succeeds alone may fail when many dashboards refresh. A recent ClickHouse proof-of-concept discussion highlights concurrency and capacity as practical evaluation criteria. Treat community reports as questions to benchmark, not universal product behavior.

Use workload settings, quotas, and separate users to protect ingestion and critical dashboards. Limit concurrent experiment backfills, cap memory, and schedule full-history rebuilds away from peak demand.

Avoid sampling shortcuts in final analysis

ClickHouse table sampling can make exploration faster, but it changes the analyzed population. Do not use SAMPLE for a final experiment result unless sampling probabilities and the estimator are part of the statistical plan.

The experiment assignment itself is already a sample from eligible traffic. Arbitrarily sampling event rows after assignment can weight high-activity users differently or omit rare conversions. Aggregate to units first; then use a principled unit sample only if the decision framework accepts the resulting precision.

Connect GrowthBook to ClickHouse

GrowthBook's warehouse-native architecture can query data where it resides. ClickHouse's own account of GrowthBook experiment analysis on ClickHouse demonstrates the integration over exposure and conversion facts.

A production setup should:

1. Create a dedicated read-only user and network path.
2. expose curated exposure, fact, and dimension models.
3. Define an assignment query with unit, variation, and time.
4. Define metrics with windows and outlier policies.
5. Inspect generated SQL and bound parameters.
6. Reconcile counts and sample records.
7. Run an A/A phase before a high-stakes test.
8. Tag and monitor query cost, latency, and failures.

Use a separate database or role boundary when raw sensitive columns are not needed. Grant write access only if a deliberate aggregate-materialization workflow requires it.

Validate data before interpreting results

Check expected allocation, multiple-variation subjects, null IDs, phase overlap, late events, outcome timing, and join rates by arm. Compare pre-treatment metrics and invariant dimensions. GrowthBook's sample ratio mismatch checks can flag allocation anomalies.

Validate materialized results against raw queries over bounded windows. Remember that materialized views process arriving blocks; backfills and source mutations may need explicit handling. Show the last complete source time on every result.

Finally, read effect size and uncertainty against a minimum meaningful threshold and guardrails. Millisecond query latency cannot compensate for an invalid unit, biased exposure, or immature outcome window.

ClickHouse earns its place in high-volume experimentation when physical design follows the causal design: event history stays auditable, exposure is canonical, outcomes collapse to the randomized unit, repeated work is pre-aggregated carefully, and concurrency is tested before the experiment program scales.

Test the design at production event volume

A query that works on one day of sample data may fail when the exposure window, outcome window, and late-arrival buffer overlap across billions of rows. Build a load test with production-shaped partitions, cardinality, skew, and concurrent ingestion. Run scheduled refreshes and analyst segments together.

Measure read rows and bytes, peak memory, elapsed time, result rows, spilled data where configured, and background merge pressure. Vary the number of concurrent experiments and metrics. The target is not the fastest isolated query; it is predictable refresh time without degrading ingestion or other analytics.

High-cardinality joins deserve special attention. Filter both sides before joining, project narrow columns, and pre-aggregate outcomes to the unit and time grain the metric needs. If dictionaries or denormalized dimensions are used, document version and effective-time behavior so current attributes do not rewrite historical segments.

Make deduplication and lateness explicit

ClickHouse engines can represent replacement, collapsing, or aggregation semantics, but those behaviors depend on merge state and query form. Preserve a stable event identifier and ingestion timestamp. Decide whether the canonical model uses argMin, argMax, a version column, or a dedicated deduplicated table, then test the choice before and after background merges.

Do not apply FINAL casually across massive source tables. It can provide convenient logical reconciliation while adding substantial work. Prefer an ingestion and table design that produces correct bounded analysis predictably, with a slower audit query available when needed.

Set a watermark for late events and show it beside experiment results. Recompute a bounded recent interval after the watermark advances. If a source backfill falls outside the normal window, run an explicit repair job and record which historical results changed.

Separate real-time monitoring from final inference

ClickHouse can update operational counters quickly, which is useful for exposure loss, errors, latency, and allocation health. Those fast aggregates are not automatically the final statistical dataset. Approximate distinct functions, sampled reads, and partially merged states can be appropriate for alerts and inappropriate for a ship decision.

Maintain two named products: a fast health view with documented approximation and a reproducible decision analysis at exact unit grain. Reconcile them during an A/A test. This preserves the advantage of high-volume monitoring without letting speed weaken the causal contract.

Keep dimensions and covariates causally safe

ClickHouse makes high-cardinality slicing feel inexpensive, which can encourage unplanned searches for a winning segment. Register decision-relevant segments before launch and derive them from attributes known before exposure. A dimension such as “completed onboarding” may be caused by treatment and cannot be used as an ordinary pre-treatment subgroup.

Store effective-dated account, device, geography, or plan attributes when historical values matter. Joining a current dimension dictionary can move old users between segments after an upgrade or account change. Preserve the value at exposure or use an as-of model whose semantics are testable.

Pre-experiment covariates for variance reduction need the same unit, metric meaning, and comparable lookback across arms. Build them in a separate bounded window, verify no post-exposure rows leak in, and retain covariance inputs for audit. Fast computation does not relax these statistical conditions.

Document the engine and table semantics beside every canonical model. A future operator should know whether correctness depends on merge completion, version columns, materialized views, dictionaries, or query modifiers. Include bounded reconciliation queries in the runbook and execute them after upgrades or ingestion changes. This is what makes a high-throughput pipeline reproducible rather than merely fast.

Retain the query version, source watermark, server settings, and result cutoff with the decision. Those details let the team explain a past result after table engines, dictionaries, or cluster topology change.

Include the assigned owner and a link to the exact rollout or feature configuration that produced the exposure population.

Analyze ClickHouse tests directly

Connect high-volume event facts to transparent warehouse-native experiment analysis without adding another raw-data silo.

Start Building Free