Experimentation on Databricks: Exposures, Unity Catalog, and SQL warehouses

Databricks gives experiment programs governed data, elastic SQL compute, and a shared lakehouse—but causal validity still depends on how assignment becomes exposure.

The application or decision service assigns a user, account, device, or request to a variation. That exposure and later outcomes flow into Delta or Iceberg tables. Unity Catalog controls who can query the data, a SQL warehouse supplies compute, and an experiment-analysis layer turns unit-level facts into effects and uncertainty.

This architecture is especially useful when outcomes span product events, model inference, billing, support, and offline behavior. The result can reuse the organization's canonical data instead of duplicating it into a separate experiment event store.

Establish the experiment data contract

An exposure row needs experiment ID, variation ID, the exact randomization-unit ID, timestamp, phase or revision, environment, and source. Keep pre-treatment dimensions used for planned segments or quality checks.

Do not label only downstream converters. Exposure should be recorded when the assigned treatment can first affect the unit, at the same logical point for every variation.

GrowthBook's feature flag experiments connect deterministic assignment and tracking. The experiment design guide covers the hypothesis, population, unit, metrics, and decision rule that must be clear before the data lands.

Preserve bronze evidence and publish a canonical model

Keep raw append-only exposure events in a restricted bronze layer. Validate schema and timestamps in silver, then publish a curated experiment model in a governed catalog.

CREATE OR REPLACE VIEW product.experiment.exposures AS
SELECT
  experiment_id,
  variation_id,
  subject_id,
  exposed_at,
  phase_id,
  environment,
  source
FROM product.silver.experiment_events
WHERE environment = 'production'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY experiment_id, phase_id, subject_id
  ORDER BY exposed_at
) = 1;

Publish a companion quality model that returns subjects with more than one distinct variation. Deduplication should not conceal unstable identity or a phase collision.

Model outcomes as reusable facts

Orders, activation, sessions, latency, support contacts, model quality, and retention should have stable event keys, unit IDs, and timestamps. GrowthBook fact tables can use these governed models across multiple metrics and experiments.

Aggregate at the assigned unit

If accounts are assigned, one account is one observation. Counting its users, sessions, or inference requests as independent underestimates uncertainty.

WITH enrolled AS (
  SELECT subject_id, variation_id, exposed_at
  FROM product.experiment.exposures
  WHERE experiment_id = :experiment_id
    AND exposed_at >= :phase_start
    AND exposed_at < :phase_end
),
unit_metric AS (
  SELECT
    e.variation_id,
    e.subject_id,
    MAX(CASE
      WHEN o.completed_at >= e.exposed_at
       AND o.completed_at < e.exposed_at + INTERVAL 7 DAYS
      THEN 1 ELSE 0
    END) AS converted
  FROM enrolled e
  LEFT JOIN product.metrics.orders o
    ON o.account_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;

Bind parameters through the connector, enforce a complete observation window, and retain non-converters. Production statistics also need consistent uncertainty, ratio handling, outlier policy, repeated monitoring, and multiple-metric controls.

GrowthBook's metric framework provides reusable definitions and a statistics layer. Validate a completed experiment against independent SQL before scaling the workflow.

Design metrics before launch

Turn lakehouse facts into primary outcomes, guardrails, and diagnostic measures that support a clear product decision.

Read the KPI Playbook

Use Unity Catalog as the data boundary

Create a service principal for the experiment-analysis system. Grant only the hierarchy and objects it needs.

GRANT USE CATALOG ON CATALOG product TO `experiment-reader`;
GRANT USE SCHEMA ON SCHEMA product.experiment TO `experiment-reader`;
GRANT SELECT ON VIEW product.experiment.exposures TO `experiment-reader`;
GRANT SELECT ON TABLE product.metrics.orders TO `experiment-reader`;

Databricks' Unity Catalog privilege reference makes the hierarchy explicit: reading a table normally requires USE CATALOG, USE SCHEMA, and SELECT. Avoid granting ALL PRIVILEGES or broad catalog-level selection when curated views are sufficient.

Use dynamic views, row filters, column masks, or ABAC for sensitive attributes. Databricks' ABAC tutorial demonstrates governed tags, row filters, and column masks. Test policy behavior as the final service principal.

Separate production and development catalogs or bindings. Do not let an experiment query accidentally fall back to a developer's default catalog.

Run analysis through a governed SQL warehouse

A SQL warehouse is the compute resource used to query governed objects. Databricks' SQL warehouse documentation distinguishes warehouse access from Unity Catalog data access: a principal needs both usable compute and object privileges.

Create a dedicated warehouse or a clearly identified workload boundary for experimentation. Configure:

• service-principal CAN USE access;
• size and autoscaling based on measured concurrency;
• auto-stop appropriate to refresh cadence;
• query and cost monitoring;
• a separate path for expensive backfills;
• ownership for failures and capacity changes.

Databricks recommends serverless SQL warehouses where available, but deployment, networking, compliance, and regional requirements still need organization-specific review. Benchmark interactive refreshes, concurrent experiments, and full-history recomputation rather than relying on an idle single query.

Connect GrowthBook to Databricks

GrowthBook's warehouse-native architecture can query data in the connected source. A production Databricks setup follows this sequence:

1. Create the service principal and approved SQL warehouse access.
2. Publish exposure, fact, and dimension objects in Unity Catalog.
3. Configure the Databricks data source in GrowthBook.
4. Define the assignment query and preview its columns and time zones.
5. Define unit-aware metrics and attribution windows.
6. Inspect generated SQL under the production identity.
7. Reconcile exposure and metric counts with direct Databricks queries.
8. Run an A/A phase before a high-risk experiment.

GrowthBook's experiment analysis workflow surfaces warehouse-backed results and diagnostics. Retain query text, catalog object versions, SQL statement identifiers, and the source cutoff with important decisions.

Optimize Delta data for experiment access patterns

Experiment queries usually filter by experiment and date, then join on subject ID. Avoid scanning wide bronze payloads at dashboard time. Promote stable columns into curated Delta tables and aggregate raw events into unit-day facts where definitions are mature.

Databricks recommends liquid clustering for many current Delta layout use cases. Choose clustering keys from actual filters and joins, then validate query profiles. Do not partition on high-cardinality experiment or subject IDs.

Use materialized views or streaming tables when they provide a justified freshness and maintenance tradeoff. Display the last successful update and maximum event time; a fast stale result is still misleading.

GrowthBook's discussion of fact-table query optimization explains why repeated complex metric SQL benefits from reusable fact models and incremental work.

Validate data and experiment health

Before reading effect size, check:

1. expected versus observed variation counts;
2. subjects in multiple variations;
3. null or changing randomization IDs;
4. exposures outside the phase;
5. outcome events before exposure;
6. join and late-arrival rates by variation;
7. pre-treatment metric and invariant balance;
8. pipeline freshness and SQL warehouse completion.

GrowthBook's sample ratio mismatch checks detect suspicious allocation. An SRM is a reason to inspect assignment, exposure, filtering, and identity joins—not a statistic to suppress.

Databricks users discussing data-quality testing distinguish code tests from live data checks. Experiment systems need both: unit tests for transformations and bucketing, plus freshness, uniqueness, accepted-value, and referential checks on production models.

Include model and agent experiments carefully

For an AI treatment, log model, prompt, retrieval, policy, and configuration revisions available at decision time. Route stable randomized units and keep deterministic eligibility. Model inference tables can supply latency, token, safety, and quality guardrails, while customer outcomes remain the product-level primary evidence.

Inference tables and governed model telemetry can support the infrastructure layer, while customer-facing outcomes remain in the end-to-end product analysis. Treat model-serving comparisons and product experiments as related but distinct evidence layers.

If a model learns online during the test, or one user's treatment changes another user's experience, a standard independent-unit A/B analysis may not fit. Design around interference and policy changes before launch.

Make the final decision reproducible

Compare the primary estimate and interval with a minimum meaningful effect. Review guardrails, planned segments, metric maturity, and data-quality checks. Record the hypothesis, population, unit, variation versions, metric definitions, analysis method, Unity Catalog objects, SQL warehouse, source cutoff, and final action.

Databricks can support both an internally built platform and a warehouse-native external analysis layer; Databricks has described building its own experimentation platform. The choice should reflect engineering ownership, required statistical workflows, governance, and time to operate—not merely whether SQL can compute a conversion rate.

The durable foundation is the same: canonical exposure, one row per randomized unit, governed facts, isolated compute, and diagnostics that are read before lift.

Treat Unity Catalog objects as the public API

Expose experiment analysis through curated views or tables with documented owners, grains, timestamps, and privacy classes. Grant the analysis principal USE CATALOG, USE SCHEMA, and SELECT only on those objects. Avoid giving an external service broad access to bronze event schemas because the first POC query was easier there.

Use separate schemas for source views, temporary results, and published aggregates. If the platform materializes intermediate tables, set naming, retention, and ownership policies. Audit which objects and columns the service reads, then review changes when a metric begins using a new source.

Lineage is part of result interpretation. An experiment metric should be traceable through its fact model to source tables and transformation jobs. When a Delta table or dbt model changes, the experiment record needs the definition version and analysis cutoff that produced its result.

Operate the SQL warehouse deliberately

Use a dedicated or isolated SQL warehouse for scheduled experiment analysis when concurrency and cost justify it. Choose size and auto-stop settings from measured refresh workloads. Test concurrent metrics, historical recomputation, and self-service segments rather than benchmarking one query in an empty environment.

Tag or identify queries by application, metric, and experiment. Monitor queue time, execution time, bytes read, cache use, spill, failures, and warehouse cost. A transformation that is cheap once can dominate spend when dozens of experiments refresh it repeatedly.

Optimize recurring access patterns with bounded time filters, selective columns, compact unit-level facts, and appropriate Delta layout. Do not apply OPTIMIZE or clustering reflexively; measure pruning and file behavior for the real predicates.

Account for streaming and correction time

Bronze tables may receive exposure quickly while billing or account facts arrive hours later. Publish watermarks for every required source and derive an analysis cutoff from the slowest dependency. A result is not fresh because one input table is fresh.

Use idempotent transformations and bounded backfills for corrected or late events. Preserve event time and ingestion time. If identity mappings or model outputs change, decide whether historical experiments are frozen, versioned, or recomputed and make that policy visible.

During the POC, pause a source job, revoke one view grant, terminate the SQL warehouse, and introduce a schema change. Confirm that the analysis fails visibly, marks existing results stale, and recovers without partial variation updates. That failure rehearsal tests the lakehouse operating model as much as the happy-path SQL.

Validate AI treatments at two levels

For model or agent changes, keep service telemetry and user outcomes distinct. Token use, latency, refusal rate, tool errors, and evaluator scores describe the treatment's operation. Retention, task completion, paid conversion, and support demand describe product impact. A faster model is not necessarily a better product experience, and an offline quality score is not a causal business outcome.

Version the prompt, model, retrieval index, tool policy, and routing configuration with the exposure. If any component changes mid-run, create a new phase or variation rather than letting the label hide a moving treatment. Store evaluator versions as metric definitions because rubric and judge changes can move scores without changing the product.

When feedback or online learning updates a model during the test, ordinary fixed-treatment assumptions may fail. Freeze learning where possible, randomize policy versions deliberately, or use a design built for adaptive treatments. Unity Catalog can govern the artifacts, but the experimental design must still define what was assigned.

Include the model and data cutoff in the final decision record so a later rerun does not silently use a different treatment or evaluation corpus.

Run tests on your lakehouse data

Connect Databricks metrics to transparent experiment analysis while Unity Catalog remains the governance boundary.

Start Building Free