Databricks

-- Catalog access (read)
GRANT USE CATALOG ON CATALOG analytics TO `traffical`;

-- Source schema — read only
GRANT USE SCHEMA ON SCHEMA analytics.experiments TO `traffical`;
GRANT SELECT ON SCHEMA analytics.experiments TO `traffical`;

-- Traffical working schema — pre-create and grant DDL + DML
CREATE SCHEMA IF NOT EXISTS analytics.traffical;
GRANT USE SCHEMA           ON SCHEMA analytics.traffical TO `traffical`;
GRANT CREATE TABLE         ON SCHEMA analytics.traffical TO `traffical`;
GRANT SELECT, MODIFY       ON SCHEMA analytics.traffical TO `traffical`;

-- Compute access — replace with your warehouse ID
GRANT CAN USE ON WAREHOUSE `abc123def456` TO `traffical`;

CREATE TABLE analytics.experiments.assignments (
  user_id          STRING NOT NULL,
  assigned_at      TIMESTAMP NOT NULL,
  experiment_name  STRING NOT NULL,
  variant          STRING NOT NULL
)
USING DELTA
PARTITIONED BY (DATE(assigned_at));

OPTIMIZE analytics.experiments.assignments
ZORDER BY (user_id, experiment_name);

CREATE TABLE analytics.experiments.orders (
  order_id     STRING,
  user_id      STRING NOT NULL,
  order_total  DECIMAL(18, 2) NOT NULL,
  currency     STRING,
  event_time   TIMESTAMP NOT NULL
)
USING DELTA
PARTITIONED BY (DATE(event_time));

OPTIMIZE analytics.experiments.orders ZORDER BY (user_id);

SELECT
  user_id,
  assigned_at,
  experiment_name AS policy_key,
  variant         AS allocation_key
FROM analytics.experiments.assignments
WHERE assigned_at BETWEEN TIMESTAMP('{{start_date}}')
                      AND TIMESTAMP('{{end_date}}')

SELECT
  user_id,
  event_time,
  order_total
FROM analytics.experiments.orders
WHERE event_time BETWEEN TIMESTAMP('{{start_date}}')
                     AND TIMESTAMP('{{end_date}}')