​
Overview

Assignment can be set or retrieved directly in SQL. This is useful when doing analysis (querying directly from experimentation.assignments or if wanting to implement an experiment directly in SQL.

​
Roles

A user must be assigned of the following application roles to execute any of the examples described on this page:
  • admin
  • editor
  • scientist

​
Get assignments

To get assignments for a given subject, perform a select against the assignments table: 
SELECT variant_id
  FROM experimentation.assignments
  WHERE subject_id = '<subject_id>' AND experiment_id = '<experiment_id>';

​
Get or make assignments

Gets an assignment or makes one if it doesn’t exist: 
SELECT experimentation.create_assignment('<subject_id>', '<experiment_id>');
Returns the new or existing variant assigned.
There’s currently a limitation where create_assignment can only be called for a single row at a time. That is, you couldn’t run something like: SELECT create_assignment(user, 'my-experiment') FROM mytable

This will be resolved in a soon-to-come release :-)

​
Examples

​
Manually get or create an assignment

This example gets an assignment for user with ID user_0001 in experiment home-cta. 
SELECT experimentation.create_assignment('user_0001', 'home-cta');

​
Split test different Cortex models

This example demonstrates how you can run a SQL query that produces an output from Cortex’s COMPLETE method, testing different model versions for various customers. Winning Variant is used to determine which model should be used per customer ID. Later, we would tie business KPIs (i.e., revenue, churn, returns, etc) back to each variant to see which model should be deployed. We have the following declared for this example:
  • A 50/50 test between control (llama3.2-1b model) and treatment (claude-3-5-sonnet)
  • The Winning Variant experiment ID is cortex-test
  • The ID of your user in this example is abc123
-- Get an assignment from Winning Variant for the experiment
WITH assignment AS (
    SELECT create_assignment('abc123', 'cortex-test') AS variant
)

-- If the assigned variant for this user is 'TREATMENT',
-- use 'claude-3-5-sonnet', otherwise use 'llama3.2-1b' (CONTROL)
SELECT
  CASE
    WHEN variant = 'TREATMENT'
    THEN SNOWFLAKE.CORTEX.COMPLETE('claude-3-5-sonnet', 'Is a hot dog a sandwich?')
    ELSE SNOWFLAKE.CORTEX.COMPLETE('llama3.2-1b', 'Is a hot dog a sandwich?')
  END AS result
FROM assignment;