Specify query variables for a Snowflake connection (Beta)

You can add query variables to a Sigma connection to Snowflake. For each query variable, Sigma sets a corresponding session variable for every query it runs in Snowflake, and also passes it as an immutable session attribute to the system context of each Snowflake Cortex Agent run. When the variable or attribute is referenced in a row access policy in Snowflake, you can restrict the rows a user can access from Sigma, whether they access data in a table, query, or through a Cortex Agent. For an example, see Example implementations.

Query variables are useful when connection-level OAuth isn’t feasible, such as when you have a large number of business users or an embedded deployment and don’t want to provision a Snowflake account for every Sigma user. Instead, you can pass user details alongside your Sigma queries and Cortex Agent calls as query variables, then reference them in a row access policy to restrict rows at the Snowflake level without requiring a Snowflake account for each user.

Requirements

• You must be assigned the Admin account type or an account type with the Manage connections permission enabled.
• You must have an existing Snowflake connection.
• (Optional) Create a user attribute to assign values of the variable to specific users or teams.
• (Optional) To enforce a row access policy for a Cortex Agent, you must have a Snowflake Cortex Agent set up with Sigma. See Use warehouse agents with Sigma.

Considerations

• Snowflake only enforces row access policies for a Cortex Agent using immutable session attributes. A row access policy that references a query variable only through GETVARIABLE is not enforced when a user uses a Cortex Agent. To enforce the policy for both direct queries and Cortex Agent runs, reference the session attribute with SYS_CONTEXT in addition to the query variable, as shown in Example implementations.
• If you use Snowflake semantic views with your Cortex Agent, apply the row access policy to the tables used in the semantic view.

Set query variables for a Snowflake connection

To set query variables for a Snowflake connection:

1. Open the Admin Portal by selecting Administration in the user menu at the top right of your screen.

2. In the left navigation, select Connections, then select the Snowflake connection.

3. On the connection overview, click Edit.

4. In the Query variables section, click Add a query variable, then choose how to set its value:

   • Static value to set one value for all users in Sigma. Enter a Name and a Value.
   • User attribute to set a different value for different users or teams in Sigma. Enter a Name, then select a user attribute for the Value.
   • Current user email to resolve the value to each signed-in user’s email address. Sigma names the variable sigma_user_email and sets its value automatically; this option is available only until you’ve added one query variable of this type.

5. (Optional) Repeat step 4 to add other query variables.

6. Click Save to apply your changes.

Example implementations

For example, set query variables used in row access policies in Snowflake, referencing both the GETVARIABLE syntax for direct queries from Sigma and the SYS_CONTEXT syntax for a Cortex Agent.

Filter by region

Set a query variable on the Snowflake connection with the following details:

• Create a user attribute in Sigma to assign values of the variable to specific users or teams. For example, a set_region attribute with values AMER and EU.
• Click Add a query variable, select User attribute, enter the name REGION, then select the set_region attribute for the Value.

Reference the REGION session attribute and query variable in a row access policy:

CREATE OR REPLACE ROW ACCESS POLICY rap_region_filter
  AS (SALES_REGION STRING) RETURNS BOOLEAN ->
    SALES_REGION = SYS_CONTEXT('SNOWFLAKE$SESSION_ATTRIBUTES', 'REGION') -- to use the policy with a Cortex Agent
    OR SALES_REGION = GETVARIABLE('REGION'); -- to use the policy with direct queries in Sigma
ALTER TABLE secure_db.finance.profits
ADD ROW ACCESS POLICY rap_region_filter ON (SALES_REGION);

When a user assigned the set_region attribute with the AMER value queries the secure_db.finance.profits table, either directly from Sigma or through a Cortex Agent, Snowflake returns only rows where the SALES_REGION column has a value of AMER.

Filter by user email

You can apply the same pattern to restrict rows by the requesting user’s email address. In the Query variables section, click Add a query variable, then select Current user email. Sigma adds a query variable named sigma_user_email that resolves automatically to each signed-in user’s email address.

Reference the sigma_user_email session attribute and query variable in a row access policy:

CREATE OR REPLACE ROW ACCESS POLICY rap_user_filter
  AS (EMAIL STRING) RETURNS BOOLEAN ->
    EMAIL = SYS_CONTEXT('SNOWFLAKE$SESSION_ATTRIBUTES', 'sigma_user_email') -- to use the policy with a Cortex Agent
    OR EMAIL = GETVARIABLE('sigma_user_email'); -- to use the policy with direct queries in Sigma
ALTER TABLE secure_db.finance.accounts
ADD ROW ACCESS POLICY rap_user_filter ON (EMAIL);

When a user queries the secure_db.finance.accounts table, Snowflake returns only rows where the EMAIL column matches the email address of the requesting user.

For more details on using row access policies with a Cortex Agent, see Row access policies with session attributes in the Snowflake documentation.

Related resources

• Connect to Snowflake
• Use warehouse agents with Sigma