Snowflake

Overview

Sortment lets you pull data stored in your Snowflake warehouse and use it to create schema.

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

Snowflake credential setup

To allow Sortment access to Snowflake, it's best to create a user specifically provisioned with access to the required tables and schemas. You could also use a personal Snowflake login for your credentials, as long as it has the correct permissions.

You can use the following SQL template to create a service account with the necessary roles and permissions with some considerations:

This snippet provides an example of creating a service account; you may need to alter it depending on your Snowflake implementation details.

• If you want Sortment to access multiple databases, run the snippet multiple times, changing smt_read_db each time.

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

• The snippet includes lines for creating a new warehouse and database for Sortment to use; if you already have databases and warehouses you intend to use, omit these lines

-- Edit the following variables
set smt_username='SORTMENT_USER';
set smt_password='Sortment#1234';
set smt_first_name='Sortment';
set smt_last_name='User';
set smt_default_warehouse='COMPUTE_WH';
set smt_database='SORTMENT_DB';
set smt_default_namespace='<database.schema>'; 
set smt_default_role='SORTMENT_ROLE';
set smt_comment='Used for Sortment integrations';

-- Set role for grants
USE ROLE ACCOUNTADMIN;

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

-- Only if you want to create a new warehouse for Sortment to use
CREATE WAREHOUSE IF NOT EXISTS identifier($smt_default_warehouse);

-- Create Sortment's user
CREATE USER IF NOT EXISTS identifier($smt_username)
PASSWORD=$smt_password
FIRST_NAME=$smt_first_name
LAST_NAME=$smt_last_name
DEFAULT_WAREHOUSE=$smt_default_warehouse
DEFAULT_NAMESPACE=$smt_default_namespace
DEFAULT_ROLE=$smt_default_role
COMMENT=$smt_comment;

-- Grant 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 identifier($smt_default_role) TO USER identifier($smt_username);

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

-- Grant write access to a sortment schema where we would write audience, campaigns, audit logs, analytics
USE identifier($smt_database);
CREATE SCHEMA IF NOT EXISTS sortment;
GRANT OWNERSHIP ON SCHEMA sortment TO ROLE identifier($smt_default_role);


-- Give read access to all the database tables, this step can be run multiple times
set smt_read_db='SORTMENT_DB';
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);

Once you've created a Snowflake service account, you're ready to set up the connection in Sortment.

Connection configuration

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

Configure your source

Enter the 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.

• Database: The database that Sortment can access to write data in Snowflake.

User credentials

Enter the following fields into Sortment:

• Username: This can be your personal Snowflake login or a dedicated user for Sortment. At minimum, this user must have read and write access to your data.

• Role - This role will be used when executing queries in Snowflake. If left blank, Sortment will use the user’s default role. It is preferred that this role has read and write access to the database for uninterrupted usage.

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

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.