> ## Documentation Index
> Fetch the complete documentation index at: https://docs.conversion.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake

> Connect Snowflake to sync contacts, custom objects, and events into Conversion.

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.

```sql theme={null}
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.

```sql theme={null}
-- 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.

```sql theme={null}
-- 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;
```

<Note>
  Run the grants above as `ACCOUNTADMIN` (or the database's owner role).
</Note>

#### Granting Access table-by-table

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

```sql theme={null}
-- 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
```

<Warning>
  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.
</Warning>

#### Verify the grants worked

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

```sql theme={null}
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.

```sql theme={null}
-- 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:

```sql theme={null}
-- 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'
);
```

<Warning>
  `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.
</Warning>

### Generate a Private Key

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

```bash theme={null}
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:

```bash theme={null}
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:

```bash theme={null}
sed '1d;$d' rsa_key.pub
```

Assign the copied value to your Snowflake user:

```sql theme={null}
ALTER USER CONVERSION_SYNC_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
```

<Info>
  Keep your private key secure. You'll paste it into Conversion when setting up the connection.
</Info>

## Step 2: Connect Snowflake to Conversion

1. In Conversion, go to **Settings → CRM & Syncing → Connections**.
2. Click **Add Snowflake connection**.
3. 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                                                                               |
4. 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](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview#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:

```sql theme={null}
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()`:

```sql theme={null}
WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})
```

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

```sql theme={null}
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:

```sql theme={null}
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`:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
ALTER USER CONVERSION_SYNC_USER
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;
```

Or for the role:

```sql theme={null}
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

<AccordionGroup>
  <Accordion title="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.
  </Accordion>

  <Accordion title="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:

    ```bash theme={null}
    openssl pkcs8 -in encrypted_key.pem -out decrypted_key.pem -nocrypt
    ```
  </Accordion>

  <Accordion title="How do I sync from multiple schemas?">
    Use fully-qualified table names in your query:

    ```sql theme={null}
    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
    ```
  </Accordion>

  <Accordion title="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.
  </Accordion>
</AccordionGroup>