Snowflake

Overview

Sortment lets you access data stored in your Snowflake and use it to create audiences.

📘 Connecting Sortment to Snowflake requires setup in both platforms. It's recommended to set up a specific account with the correct permissions in Snowflake before configuring the connection in Sortment.

Connection configuration

To get started, select Snowflake on Data connections page and and follow the steps below.

Snowflake configuration

Following required fields into Sortment:

• Account identifier: This is always found at the beginning of your Snowflake URL which might look like this: https://ACCOUNT_IDENTIFIER.snowflakecomputing.com.

• ℹ️ Account identifier format may differ based on Snowflake account age.

  For example, older Snowflake accounts often have identifiers that look like ACCOUNT_LOCATOR.CLOUD_REGION_ID.CLOUD, whereas newer Snowflake accounts have identifiers that look like ORGNAME-ACCOUNT_NAME.

  For more details, visit Snowflake's account identifier docs.

• Warehouse: The warehouse that will be used when Sortment executes queries in Snowflake. Follow the Snowflake query reference to share with Sortment access to a warehouse.

• Database: The database that Sortment can access to write data in Snowflake. Follow the Snowflake query reference to share with Sortment access to a database with write access to respective schema.

User credentials

• Username: This should be a dedicated account for Sortment. At minimum, this user must have read access to your data relevant for Sortment. This user should have read/write access to a dedicated schema where Sortment can write campaigns and analytics data. Follow the Snowflake query reference to create a user with relevant access.

• Role: This role will be used when executing queries in Snowflake. If left blank, Sortment will use the user’s default role. Follow the Snowflake query reference to create a role with relevant access.

• Password: This is the password for the user specified above.

Snowflake query reference

Sortment recommends creating a new:

• Role for Sortment user with the required permissions for better access control on data and actions available to Sortment.

• Warehouse to avoid warehouse sharing between workloads, and easy access to all billing and credit usage information on Snowflake

• Schema for Sortment to write data into Snowflake to follow best data management practices.

Prerequisites

To complete all steps on Snowflake, the user should have at least ACCOUNTADMIN role.

Also, execute the steps in same worksheet as subsequent steps reference variables set in Step 1.

Step 1: Create a new Snowflake User and Sortment Role

You need to create a user account and assign the created Sortment role to the new user.

Snowflake is enforcing MFA for user login credentials (see reference).

We will soon add support for key-pair authentication to comply with Snowflake deadline for Nov '25, with enough time for existing users to migrate to new authentication method.

-- Set role for grants
USE ROLE ACCOUNTADMIN;

set smt_username='SORTMENT_USER45';
set smt_password='Sortment_Password';
set smt_default_warehouse='<warehouse>';
set smt_default_namespace='<database.schema>'; 
set smt_default_role='SORTMENT_ROLE';
set smt_comment='Used for Sortment integrations';

-- Run this only if you want to create a new warehouse for Sortment
-- We recommend keeping a MEDIUM warehouse. You may tweak this basis your usage estimate
CREATE WAREHOUSE IF NOT EXISTS identifier($smt_default_warehouse)
  WAREHOUSE_SIZE = 'MEDIUM';

-- Create a role for Sortment
CREATE ROLE IF NOT EXISTS identifier($smt_default_role)
COMMENT = $smt_comment;

-- Create Sortment's user
CREATE USER IF NOT EXISTS identifier($smt_username)
PASSWORD=$smt_password
DEFAULT_WAREHOUSE=$smt_default_warehouse
DEFAULT_NAMESPACE=$smt_default_namespace
DEFAULT_ROLE=$smt_default_role
MUST_CHANGE_PASSWORD = FALSE    -- Prevent forced password reset
TYPE = 'LEGACY_SERVICE'   -- Assign a dedicated role
COMMENT=$smt_comment;

-- Grant usage permissions to the role
GRANT ROLE identifier($smt_default_role) TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE identifier($smt_default_warehouse) TO ROLE identifier($smt_default_role);

-- Grant role to the Sortment user
GRANT ROLE identifier($smt_default_role) TO USER identifier($smt_username);

Once you've created a Snowflake user account, you're ready to add read and write access to the Sortment User.

Step 2: Assign write access to a dedicated schema to Sortment role

Sortment needs write access to write back Audiences and Campaigns data back to your Snowflake account.

The snippet includes creation of a new database for Sortment to use. If you already have database you intend to use, omit the creation steps. These steps grant access to Sortment role to a write schema and permission to access the tabels created by Sortment in the database.

set smt_write_db='SORTMENT_WRITE_DB';

-- Create a new database for Sortment to use
CREATE DATABASE IF NOT EXISTS identifier($smt_write_db);

-- Grant write access to a sortment schema where we would write audience, campaigns, audit logs, analytics
USE identifier($smt_write_db);
CREATE SCHEMA IF NOT EXISTS sortment;

-- Grant access to Sortment role to access the schema
GRANT OWNERSHIP ON SCHEMA sortment TO ROLE identifier($smt_default_role);

-- Grant access permissions on write database created for Sortment
GRANT USAGE ON DATABASE identifier($smt_write_db) TO ROLE identifier($smt_default_role);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($smt_write_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON ALL TABLES IN DATABASE identifier($smt_write_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($smt_write_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($smt_write_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($smt_write_db) TO ROLE identifier($smt_default_role);

Step 3: Assign read access to relevant tables to Sortment role

If you want Sortment to access multiple databases, run this step multiple times, changing smt_read_db each time.

-- Follow the following steps to give Sortment read access to your data. 
-- These tables will be used to setup schema and run queries for marketing use cases.
-- This step can be run multiple times, if you have tables distributed across different databases.
set smt_read_db='<read_database>';

GRANT USAGE ON DATABASE identifier($smt_read_db) TO ROLE identifier($smt_default_role);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($smt_read_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON ALL TABLES IN DATABASE identifier($smt_read_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($smt_read_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($smt_read_db) TO ROLE identifier($smt_default_role);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($smt_read_db) TO ROLE identifier($smt_default_role);

Test connection

When setting up a source for the first time, Sortment validates the following:

• Network connectivity

• Snowflake credentials

• Permission to list schemas and tables

• Permission to write to the schema

All configurations must pass them for uninterrupted access to Sortment. Some sources may initially fail connection tests due to timeouts. Once a connection is established, subsequent API requests should happen more quickly, so it's best to retry tests if they first fail. You can do this by clicking Continue again.

If you've retried the tests and verified your credentials are correct but it is still failing, reach out to [e-mail]

Tips and troubleshooting

If you encounter an error or question not listed below and need assistance, don't hesitate to reach out . We're here to help.

Connection timeout

When initially testing your connection, you may receive a connection timeout error. Once a connection is established, subsequent API requests should happen more quickly, so it's best to retry the tests if they first fail. You can do this by clicking Continue again.

Network error: Could not reach Snowflake

You may receive this error if the input for Account identifier is invalid. Instead of using the complete Snowflake URL, for example, https://ACCOUNT_IDENTIFIER.snowflakecomputing.com ensure that you're only using the ACCOUNT_IDENTIFIER part of the URL, for example, companyname-abc123 or companyname-abc123.us-east-1.