Snowflake

This guide walks you through connecting Snowflake to Conversion and setting up your first sync.

Before You Begin

Make sure you have the following before starting:

ACCOUNTADMIN access in Snowflake: You’ll need to create a warehouse, role, and user, and grant privileges. The ACCOUNTADMIN role (or an equivalent role with these permissions) is required.
OpenSSL installed locally: Used to generate the key pair Conversion uses to authenticate. Available on macOS and Linux out of the box, and on Windows via WSL or Git Bash.
The database(s) you want to sync identified: Know which database, and optionally which schemas and tables, Conversion should be able to read from.
Your Snowflake account identifier: The <org>-<account> portion of your Snowflake URL (e.g. fntyhkd-zxb06236). You’ll need it when connecting in Step 2.

Step 1: Set Up Snowflake Access

Conversion connects to Snowflake using key-pair authentication. In this step you’ll create a dedicated warehouse, role, and user, configure network access, and generate a key pair. Run the following commands in a Snowflake SQL file. Use the ACCOUNTADMIN role.

USE ROLE ACCOUNTADMIN

Create a Virtual Warehouse

Conversion needs a virtual warehouse to run queries. We recommend creating a dedicated warehouse for Conversion so its compute usage is easy to monitor in isolation, but you can reuse an existing one if you prefer.

-- Create a dedicated warehouse (optional - you can reuse an existing one)
CREATE WAREHOUSE CONVERSION_SYNC
  WITH WAREHOUSE_SIZE = 'XSMALL'
  WAREHOUSE_TYPE = 'STANDARD'
  AUTO_SUSPEND = 300  -- 5 minutes
  AUTO_RESUME = TRUE;

Create a Role and Grant Access

Create a role for Conversion with read access to the tables you want to sync. We recommend granting access at the database level so that Conversion automatically sees new schemas and tables as you add them. No manual SQL changes required as the warehouse evolves.

-- Create the role
CREATE ROLE CONVERSION_SYNC_ROLE;

-- Allow the role to use the warehouse from Step 1
GRANT USAGE ON WAREHOUSE CONVERSION_SYNC TO ROLE CONVERSION_SYNC_ROLE;

-- Allow the role to read everything (current + future) inside the database
-- Replace MY_DATABASE with the database you want Conversion to sync from
GRANT USAGE  ON DATABASE MY_DATABASE                     TO ROLE CONVERSION_SYNC_ROLE;
GRANT USAGE  ON ALL    SCHEMAS IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;
GRANT USAGE  ON FUTURE SCHEMAS IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON ALL    TABLES  IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON FUTURE TABLES  IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;

Run the grants above as ACCOUNTADMIN (or the database’s owner role).

Granting Access table-by-table

If your database contains data Conversion shouldn’t see, grant access to specific tables instead of the whole database:

-- Create the role and grant warehouse access (same as above)
CREATE ROLE CONVERSION_SYNC_ROLE;

GRANT USAGE ON WAREHOUSE CONVERSION_SYNC TO ROLE CONVERSION_SYNC_ROLE;

-- Replace with your database, schema, and table names
GRANT USAGE  ON DATABASE MY_DATABASE                          TO ROLE CONVERSION_SYNC_ROLE;
GRANT USAGE  ON SCHEMA   MY_DATABASE.my_schema                TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON TABLE    MY_DATABASE.my_schema.users          TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON TABLE    MY_DATABASE.my_schema.products       TO ROLE CONVERSION_SYNC_ROLE;
-- Add more tables as needed

With table-by-table grants, Conversion only sees the tables you explicitly include. Any new tables you create later or existing tables you forgot to grant on cannot be accessed by Conversion.

Verify the grants worked

Before moving on, confirm CONVERSION_SYNC_ROLE can actually see your database:

USE ROLE CONVERSION_SYNC_ROLE;
SHOW DATABASES LIKE 'MY_DATABASE';   -- expect 1 row
USE DATABASE MY_DATABASE;            -- expect success

Create a User

Create a user for Conversion and assign the role you created.

-- Create user
CREATE USER CONVERSION_SYNC_USER
  DEFAULT_ROLE = CONVERSION_SYNC_ROLE
  DEFAULT_WAREHOUSE = CONVERSION_SYNC;

-- Assign role to user
GRANT ROLE CONVERSION_SYNC_ROLE TO USER CONVERSION_SYNC_USER;

Configure Network Policies

If your Snowflake account uses network policies, allow connections from Conversion’s IP addresses:

Region	IP Addresses
US	35.239.90.161, 35.188.167.166, 34.56.101.43, 34.122.97.230, 34.29.176.66, 35.226.154.44

Here’s how to add these IP addresses to your existing policy:

-- 1. Find your existing policy
SHOW NETWORK POLICIES;
-- 2. See what IPs it currently allows; copy these, you'll need them in the next step
DESC NETWORK POLICY <EXISTING_NETWORK_POLICY>;
-- 3. Re-set the list with your existing IPs plus Conversion's IPs
ALTER NETWORK POLICY <EXISTING_NETWORK_POLICY> SET ALLOWED_IP_LIST = (
  '<existing_ip_1>',
  '<existing_ip_2>',
  -- ... all of your existing entries
  '35.239.90.161',
  '35.188.167.166',
  '34.56.101.43',
  '34.122.97.230',
  '34.29.176.66',
  '35.226.154.44'
);

ALTER NETWORK POLICY ... SET ALLOWED_IP_LIST replaces the list; it doesn’t append. Always run DESC NETWORK POLICY first and copy the existing entries into the new list.

Generate a Private Key

Conversion authenticates using a private key in PKCS#8 PEM format. Generate one using OpenSSL:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

This creates an unencrypted private key file called rsa_key.p8. Then generate the matching public key:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Print the key body (without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines) so you can copy it:

sed '1d;$d' rsa_key.pub

Assign the copied value to your Snowflake user:

ALTER USER CONVERSION_SYNC_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Keep your private key secure. You’ll paste it into Conversion when setting up the connection.

Step 2: Connect Snowflake to Conversion

In Conversion, go to Settings → CRM & Syncing → Connections.
Click Add Snowflake connection.

Fill in the connection details using the values from the earlier steps:

Field	What to enter	Where it came from
Name	A friendly label, e.g. `Production Snowflake`	Free-text, just for your reference in Conversion
Account	Your account identifier, e.g. `fntyhkd-zxb06236`	The `<org>-<account>` portion of your Snowflake URL (`https://app.snowflake.com/<org>/<account>`)
Warehouse	`CONVERSION_SYNC`	Created in Step 1
Database	`MY_DATABASE` (replace with your actual database name)	The database you granted access to in Step 1
User	`CONVERSION_SYNC_USER`	Created in Step 1
Role	`CONVERSION_SYNC_ROLE`	Created in Step 1
Private Key	The full contents of `rsa_key.p8`	Generated in Step 1

Click Connect

Conversion will verify the connection. If successful, you’re ready to create syncs.

Step 3: Create a Sync

After connecting, open your new Snowflake connection and go to the Syncs tab to create a sync. Read more about Setting Up a Sync.

Snowflake SQL Reference

Converting Timestamps

Conversion expects Unix timestamps for date/time fields. Use DATE_PART(EPOCH_SECOND, column) to convert Snowflake TIMESTAMP values:

SELECT 
  EMAIL AS "email",
  DATE_PART(EPOCH_SECOND, CREATED_AT) AS "created_at",
  DATE_PART(EPOCH_SECOND, LAST_LOGIN) AS "last_login"
FROM users
WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})

Using last_sync_time

{{last_sync_time}} is a Unix timestamp in seconds (an integer). Conversion substitutes the integer value directly into your query at run time; do not wrap it in quotes. Use it in a WHERE clause to limit each run to rows that have changed since the last successful sync. On the first sync the value is 0, so every row matches. To compare it against a Snowflake TIMESTAMP column, convert the integer to a timestamp with TO_TIMESTAMP():

WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})

Or convert your column to Unix seconds and compare integers directly:

WHERE DATE_PART(EPOCH_SECOND, UPDATED_AT) >= {{last_sync_time}}

To add a buffer that catches rows whose UPDATED_AT might be slightly stale, subtract seconds from the integer before converting:

WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}} - 300)  -- 5 minute buffer

Building Nested Objects with OBJECT_CONSTRUCT

Use OBJECT_CONSTRUCT to create nested JSON objects like relationshipFields:

OBJECT_CONSTRUCT(
  'role', ROLE,
  'quantity', QUANTITY,
  'started_at', DATE_PART(EPOCH_SECOND, STARTED_AT)
) AS "relationshipFields"

Converting Booleans

Snowflake stores booleans as binary (0/1). Use IFF to convert to string values if needed:

SELECT 
  EMAIL AS "email",
  IFF(IS_ACTIVE, 'true', 'false') AS "is_active",
  IFF(IS_ADMIN, 'true', 'false') AS "is_admin"
FROM users
WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})

Handling NULLs

Use COALESCE or NVL to provide default values:

SELECT 
  EMAIL AS "email",
  COALESCE(FIRST_NAME, '') AS "first_name",
  NVL(PHONE, '') AS "phone"
FROM users

Casting Types

Use CAST or :: to convert between types:

SELECT 
  CAST(USER_ID AS VARCHAR) AS "id",
  SCORE::INTEGER AS "lead_score"
FROM users

Troubleshooting

Column names are still uppercase

If column names remain uppercase despite using AS "lowercase", check the QUOTED_IDENTIFIERS_IGNORE_CASE setting. Set it to FALSE for your user or role:

ALTER USER CONVERSION_SYNC_USER
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

Or for the role:

ALTER ROLE CONVERSION_SYNC_ROLE
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

“Permission denied” errors

Ensure your role has the required grants:

USAGE on the warehouse
USAGE on the database and schema
SELECT on each table you want to query

”Could not connect” errors

Verify that:

Your account identifier is correct (e.g., xy12345.us-east-1)
The private key is in PKCS#8 PEM format (starts with -----BEGIN PRIVATE KEY-----)
The public key is assigned to your Snowflake user
Conversion’s IP addresses are allowed if you use network policies

Sync taking too long

Ensure you’re filtering by last_sync_time to reduce rows
Select only the columns you need
Consider using a larger warehouse size
Reduce sync frequency for large datasets

Frequently Asked Questions

What Snowflake permissions does Conversion need?

The role needs USAGE on the warehouse, database, and schema, plus SELECT on the specific tables you want to sync. Conversion only reads data; it never writes to your Snowflake account.

Can I use an encrypted private key?

No. Conversion requires an unencrypted private key in PKCS#8 PEM format. If your key is encrypted, decrypt it first:

openssl pkcs8 -in encrypted_key.pem -out decrypted_key.pem -nocrypt

How do I sync from multiple schemas?

Use fully-qualified table names in your query:

SELECT u.EMAIL AS "email", o.ORDER_ID AS "order_id"
FROM my_database.users_schema.users u
JOIN my_database.orders_schema.orders o ON u.ID = o.USER_ID

What if a contact doesn't exist yet?

If you sync a custom object with a userId or email that doesn’t exist in Conversion, we create the contact automatically with just that identifier. You can enrich that contact’s profile through a separate contacts sync.

Records

Emails

Workflows

Audiences

Forms

Content

Campaigns

Data Syncing

Unified Query Engine

Folders

Liquid

Tracking

Workspace Settings

GDPR

Before You Begin

Step 1: Set Up Snowflake Access

Create a Virtual Warehouse

Create a Role and Grant Access

Granting Access table-by-table

Verify the grants worked

Create a User

Configure Network Policies

Generate a Private Key

Step 2: Connect Snowflake to Conversion

Step 3: Create a Sync

Snowflake SQL Reference

Converting Timestamps

Using last_sync_time

Building Nested Objects with OBJECT_CONSTRUCT

Converting Booleans

Handling NULLs

Casting Types

Troubleshooting

Column names are still uppercase

“Permission denied” errors

”Could not connect” errors

Sync taking too long

Frequently Asked Questions

​Before You Begin

​Step 1: Set Up Snowflake Access

​Create a Virtual Warehouse

​Create a Role and Grant Access

​Granting Access table-by-table

​Verify the grants worked

​Create a User

​Configure Network Policies

​Generate a Private Key

​Step 2: Connect Snowflake to Conversion

​Step 3: Create a Sync

​Snowflake SQL Reference

​Converting Timestamps

​Using last_sync_time

​Building Nested Objects with OBJECT_CONSTRUCT

​Converting Booleans

​Handling NULLs

​Casting Types

​Troubleshooting

​Column names are still uppercase

​“Permission denied” errors

​”Could not connect” errors

​Sync taking too long

​Frequently Asked Questions

Before You Begin

Step 1: Set Up Snowflake Access

Create a Virtual Warehouse

Create a Role and Grant Access

Granting Access table-by-table

Verify the grants worked

Create a User

Configure Network Policies

Generate a Private Key

Step 2: Connect Snowflake to Conversion

Step 3: Create a Sync

Snowflake SQL Reference

Converting Timestamps

Using last_sync_time

Building Nested Objects with OBJECT_CONSTRUCT

Converting Booleans

Handling NULLs

Casting Types

Troubleshooting

Column names are still uppercase

“Permission denied” errors

”Could not connect” errors

Sync taking too long

Frequently Asked Questions