Snowflake

-- As an ACCOUNTADMIN or similar privileged role:

CREATE ROLE TRAFFICAL;

GRANT USAGE ON WAREHOUSE ANALYTICS_XS TO ROLE TRAFFICAL;

-- Read on the database that holds your data
GRANT USAGE ON DATABASE ANALYTICS TO ROLE TRAFFICAL;

-- Read on the source schema(s) where assignments/facts live
GRANT USAGE ON SCHEMA ANALYTICS.EXPERIMENTS TO ROLE TRAFFICAL;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.EXPERIMENTS TO ROLE TRAFFICAL;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.EXPERIMENTS TO ROLE TRAFFICAL;

-- The Traffical schema — pipeline owns this one, full DML + DDL
CREATE SCHEMA IF NOT EXISTS ANALYTICS.TRAFFICAL;
GRANT USAGE ON SCHEMA ANALYTICS.TRAFFICAL TO ROLE TRAFFICAL;
GRANT CREATE TABLE, CREATE VIEW ON SCHEMA ANALYTICS.TRAFFICAL TO ROLE TRAFFICAL;
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA ANALYTICS.TRAFFICAL TO ROLE TRAFFICAL;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ANALYTICS.TRAFFICAL TO ROLE TRAFFICAL;

CREATE USER TRAFFICAL
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...your-public-key...'
  DEFAULT_ROLE = TRAFFICAL
  DEFAULT_WAREHOUSE = ANALYTICS_XS;

GRANT ROLE TRAFFICAL TO USER TRAFFICAL;

# Private key (8192-bit RSA, unencrypted — the pipeline doesn't support passphrases)
openssl genrsa -out traffical_key.pem 2048

# Public key
openssl rsa -in traffical_key.pem -pubout -out traffical_key.pub

# View the public key — paste this into the RSA_PUBLIC_KEY of the CREATE USER above
cat traffical_key.pub

CREATE TABLE ANALYTICS.EXPERIMENT_ASSIGNMENTS (
  USER_ID          STRING NOT NULL,
  ASSIGNED_AT      TIMESTAMP_TZ NOT NULL,
  EXPERIMENT_NAME  STRING NOT NULL,
  VARIANT          STRING NOT NULL
)
CLUSTER BY (ASSIGNED_AT, EXPERIMENT_NAME);

CREATE TABLE ANALYTICS.ORDERS (
  ORDER_ID     STRING PRIMARY KEY,
  USER_ID      STRING NOT NULL,
  ORDER_TOTAL  NUMBER(18, 2) NOT NULL,
  CURRENCY     STRING,
  EVENT_TIME   TIMESTAMP_TZ NOT NULL
)
CLUSTER BY (EVENT_TIME);

SELECT
  USER_ID         AS user_id,
  ASSIGNED_AT     AS assigned_at,
  EXPERIMENT_NAME AS policy_key,
  VARIANT         AS allocation_key
FROM ANALYTICS.EXPERIMENT_ASSIGNMENTS
WHERE ASSIGNED_AT BETWEEN '{{start_date}}'::TIMESTAMP_TZ
                      AND '{{end_date}}'::TIMESTAMP_TZ

SELECT
  USER_ID    AS user_id,
  EVENT_TIME AS event_time,
  ORDER_TOTAL
FROM ANALYTICS.ORDERS
WHERE EVENT_TIME BETWEEN '{{start_date}}'::TIMESTAMP_TZ
                     AND '{{end_date}}'::TIMESTAMP_TZ