Connecting Klaviyo and Snowflake

Learn how to prepare Snowflake for imports, create a scheduled profile sync in Klaviyo, and troubleshoot import performance. For an overview of supported data and use cases, see Understanding data warehouse import in Klaviyo.

For the purposes of this article we use the term “table”, but views, materialized views, and tables are all valid Snowflake objects that can be imported. As long as Klaviyo can run SELECT col1 FROM table_name on the object, you are free to use whatever you prefer.

The key words "MUST", "MUST NOT", "REQUIRED", "SHALL", "SHALL NOT", "SHOULD", "SHOULD NOT", "RECOMMENDED", "MAY", and "OPTIONAL" in this document are to be interpreted as described in RFC 2119.

Snowflake admin setup

Before you begin, confirm that you have the securityadmin and sysadmin roles in Snowflake. Run SHOW GRANTS TO USER <your_username> to review your roles, and contact your Snowflake administrator if you need access. Then follow these steps to allow Klaviyo to import your data.

1. Generate a private key by running the following command in your local terminal:

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

1. Generate a public key that references the private key by running the following command in your terminal:

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

1. Open rsa_key.pub in a text editor and copy the public key without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines or line breaks. In the SQL setup script, replace GENERATE_PUBLIC_KEY in rsa_public_key with this value, keeping the surrounding single quotes. On a Mac, the command below copies the value to your clipboard.

# Mac terminal command to copy the public key without PEM delimiters or line breaks
sed '/-----BEGIN PUBLIC KEY-----/d; /-----END PUBLIC KEY-----/d' rsa_key.pub | tr -d '\r\n' | pbcopy

1. Run the following SQL script in your Snowflake environment to create a service user for Klaviyo.
   1. Customize the user, role, warehouse, database, and network names at the beginning of the script as needed. Keep names uppercase, and do not change the schema names in the DO NOT CHANGE section.
   2. In summary, you’ll:
      1. Choose an existing warehouse or create a new one
      2. Choose an existing database or create a new one to hold the new schemas
      3. Create 2 new schemas, KLAVIYO_TMP and KLAVIYO_IMPORT_FROM_DWH
      4. Create a new network policy and allow list Klaviyo IPs
      5. Create a user and role for Klaviyo
   3. The script uses IF NOT EXISTS to preserve existing objects with the same names. If you reuse an existing service user, confirm that its configured public key matches the private key you will enter in Klaviyo. The script does not replace an existing user’s public key.

BEGIN;

-- create variables for user / password / role / warehouse / database. 
-- Change these to whatever you prefer.
SET role_name = 'KLAVIYO_DATA_TRANSFER_ROLE'; -- all letters must be uppercase, ex. 'KLAVIYO_DATA_TRANSFER_ROLE'
SET user_name = 'KLAVIYO_DATA_TRANSFER_USER'; -- all letters must be uppercase, ex. 'KLAVIYO_DATA_TRANSFER_USER'
SET warehouse_name = 'KLAVIYO_DATA_TRANSFER_WAREHOUSE'; -- all letters must be uppercase, ex. 'KLAVIYO_DATA_TRANSFER_WAREHOUSE'
SET database_name = 'KLAVIYO_DATABASE'; -- all letters must be uppercase, ex. 'KLAVIYO_DATABASE'. If this database doesn't exist, a new one will be created.
SET network_policy = 'KLAVIYO_DATA_TRANSFER_NETWORK_POLICY'; -- all letters must be uppercase, ex. 'KLAVIYO_NETWORK_POLICY'
SET network_rule = 'KLAVIYO_DATA_TRANSFER_NETWORK_RULE'; -- all letters must be uppercase, ex. 'KLAVIYO_NETWORK_RULE'
/* replace GENERATE_PUBLIC_KEY below with generated public key */

-- DO NOT CHANGE
SET schema_name_tmp = $database_name || '.KLAVIYO_TMP';  -- DO NOT CHANGE
SET schema_name_import = $database_name || '.KLAVIYO_IMPORT_FROM_DWH';  -- DO NOT CHANGE
SET full_network_rule_tmp = $schema_name_tmp || '.' || $network_rule; -- DO NOT CHANGE
SET full_network_rule_import = $schema_name_import || '.' || $network_rule; -- DO NOT CHANGE


-- change role to sysadmin for warehouse / database steps
USE ROLE sysadmin;

-- create a warehouse for data transfer service
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($warehouse_name)
    warehouse_size = xsmall
    warehouse_type = standard
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

-- create database for data transfer service
CREATE DATABASE IF NOT EXISTS IDENTIFIER($database_name);

-- create schemas for data transfer service
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($schema_name_tmp);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($schema_name_import);

-- change role to securityadmin for user / role steps
USE ROLE securityadmin;

-- create network rule and policy for database
GRANT USAGE ON DATABASE IDENTIFIER($database_name) TO ROLE securityadmin;
GRANT USAGE, CREATE NETWORK RULE ON SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE securityadmin;
GRANT USAGE, CREATE NETWORK RULE ON SCHEMA IDENTIFIER($schema_name_import) TO ROLE securityadmin;

-- whitelist klaviyo ip ranges, for KLAVIYO_TMP schema 
CREATE NETWORK RULE IF NOT EXISTS IDENTIFIER($full_network_rule_tmp)
    type = IPV4
    value_list = (
        '184.72.183.187/32', '52.206.71.52/32', '3.227.146.32/32', '44.198.39.11/32', '35.172.58.121/32', '3.228.37.244/32', '54.88.219.8/32', '3.214.211.176/32'
        )
    comment = 'Klaviyo IP Ranges as of April 2025';
CREATE NETWORK POLICY IF NOT EXISTS IDENTIFIER($network_policy)
    allowed_network_rule_list = ($full_network_rule_tmp);

-- whitelist klaviyo ip ranges, for KLAVIYO_IMPORT_FROM_DWH schema 
CREATE NETWORK RULE IF NOT EXISTS IDENTIFIER($full_network_rule_import)
    type = IPV4
    value_list = (
        '184.72.183.187/32', '52.206.71.52/32', '3.227.146.32/32', '44.198.39.11/32', '35.172.58.121/32', '3.228.37.244/32', '54.88.219.8/32', '3.214.211.176/32'
        )
    comment = 'Klaviyo IP Ranges as of April 2025';
CREATE NETWORK POLICY IF NOT EXISTS IDENTIFIER($network_policy)
allowed_network_rule_list = ($full_network_rule_import);


-- create role for data transfer service
CREATE ROLE IF NOT EXISTS IDENTIFIER($role_name);
GRANT ROLE IDENTIFIER($role_name) TO ROLE sysadmin;

-- create a user for data transfer service
CREATE USER IF NOT EXISTS IDENTIFIER($user_name)
    type = SERVICE
    network_policy = $network_policy
    default_role = $role_name
    default_warehouse = $warehouse_name
    rsa_public_key = 'GENERATE_PUBLIC_KEY';
GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);
ALTER USER IDENTIFIER($user_name) SET NETWORK_POLICY = $network_policy;

-- grant service role access to warehouse
GRANT USAGE
    ON WAREHOUSE IDENTIFIER($warehouse_name)
    TO ROLE IDENTIFIER($role_name);

-- grant service access to database
GRANT MONITOR, USAGE
    ON DATABASE IDENTIFIER($database_name)
    TO ROLE IDENTIFIER($role_name);

-- Grant privileges for KLAVIYO_TMP 
GRANT USAGE ON SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);
GRANT MONITOR, USAGE, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE FUNCTION, CREATE PROCEDURE
    ON SCHEMA IDENTIFIER($schema_name_tmp)
    TO ROLE IDENTIFIER($role_name);
GRANT ALL ON ALL TABLES IN SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);
GRANT ALL ON FUTURE TABLES IN SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);
GRANT ALL ON ALL VIEWS IN SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);
GRANT ALL ON FUTURE VIEWS IN SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);

-- Grant privileges for KLAVIYO_IMPORT_FROM_DWH
GRANT USAGE ON SCHEMA IDENTIFIER($schema_name_import) TO ROLE IDENTIFIER($role_name);
GRANT SELECT ON ALL TABLES IN SCHEMA IDENTIFIER($schema_name_import) TO ROLE IDENTIFIER($role_name);
GRANT SELECT ON FUTURE TABLES IN SCHEMA IDENTIFIER($schema_name_import) TO ROLE IDENTIFIER($role_name);
GRANT SELECT ON ALL VIEWS IN SCHEMA IDENTIFIER($schema_name_import) TO ROLE IDENTIFIER($role_name);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA IDENTIFIER($schema_name_import) TO ROLE IDENTIFIER($role_name);

COMMIT;

Snowflake data setup

The setup script creates 2 schemas:

• KLAVIYO_TMP will be used exclusively by Klaviyo. You MUST NOT modify any tables created in this schema. Klaviyo will delete these tables when they are no longer needed.
• KLAVIYO_IMPORT_FROM_DWH is where you should store your final tables for Klaviyo to import. When you go through the sync creation process, all the tables in this schema will be listed for you to choose from. Hence you SHOULD only store the final tables you want to import to avoid confusion during setup.

All tables you plan to import to Klaviyo must meet the following criteria.

Timestamp requirements

1. Each table MUST contain a timestamp field that indicates when a row was created or last updated, such as inserted_at or updated_at. Use a Snowflake timestamp data type so the field is available in New Row Detection during sync creation.
   1. The timestamp field MUST be monotonically increasing (i.e. it must always be getting larger or staying the same, never getting smaller).
   2. After sync creation, you MUST NOT set a row’s timestamp value to a time in the past or Klaviyo may not pick up that row.
   3. Your timestamps MUST be in UTC, or include timezone information. If timezone information is missing, Klaviyo will assume UTC. For custom properties, these timestamps remain in string format, allowing you to interpret them in your preferred timezone.
2. The change-detection timestamp MUST reflect when a row is inserted or updated in the import table. Historical and manual imports start from the oldest timestamp and scan forward in 1-hour windows. A single old timestamp, such as a 2023 record among recent rows, can make these imports slow because Klaviyo scans the intervening empty windows. Subsequent scheduled runs continue from their saved progress; they do not restart from the oldest row.
3. Consider row density within each 1-hour timestamp window. Millions of records in the same window can slow a sync. About 100,000 rows per window is a performance guideline, not a fixed limit; row size also affects performance.
4. Klaviyo recommends you set the timestamp field with CURRENT_TIMESTAMP() or an equivalent function whenever you add rows to the table we will sync from. Multiple rows can have the same timestamp. See example below.

INSERT INTO table_name (email, inserted_at)
SELECT email, CURRENT_TIMESTAMP()
FROM source_table;

Table structure

1. Tables SHOULD be treated as append-only (aka insert-only)
   1. If you prefer to update the rows in place instead, you MUST update the timestamp field so Klaviyo can identify the change.
2. Tables SHOULD be ordered on your timestamp column. Snowflake will handle clustering and partitioning based on your insert order. This will help optimize Klaviyo’s import queries, keeping compute costs in Snowflake down

Profile uniqueness and consistency

1. Each non-identifier profile property MUST be imported from only 1 table. Klaviyo rejects duplicate property mappings across tables when you save a sync. Profile identifiers, such as email, phone number, and external ID, can be mapped from multiple tables.
2. You SHOULD use the same profile identifier(s) (email, phone number, external ID, etc) in all your import tables, to minimize the risk of duplicate profiles being created.
   1. Klaviyo will create new profiles if the profile identifier you provide does not match an existing profile within Klaviyo.
   2. Example: Table1 (Email, fav_color) + Table2 (Phone, birthday)
      1. These tables can create 2 profiles for the same person if the email and phone number do not resolve to the same existing profile. To update 1 existing profile from both tables, use identifiers that already belong to that profile or include a shared identifier in both tables.
   3. One way to avoid this problem is to use only a single import table for all of your profiles.

Circular import-export loop prevention

1. You SHOULD carefully manage scenarios where both import and export features are used to prevent circular import-export loops. Ensure your export process does not feed data back into a table that is upstream of your import table, as Klaviyo does not currently detect this scenario.
   1. This would look something like:
      1. Klaviyo exports new or updated profiles to your warehouse.
      2. Your transformations write those profiles into an import table and advance their change-detection timestamps.
      3. Klaviyo imports those rows again, updating profiles that can then be exported again.
   2. Examples that avoid this loop:
      1. Use exported data only to filter which source rows are imported, without rewriting those rows or their change-detection timestamps on each export.
      2. Keep exported data out of transformations that add or update rows in your import table.
   3. What are the consequences of a circular import-export loop?
      1. This will result in unnecessary compute costs for both you and Klaviyo.

Create a scheduled profile import in Klaviyo

After preparing your tables, create a sync for each table you want to import. Have your Snowflake account identifier, warehouse and database names, service username, and the full contents of rsa_key.p8 ready. You also need permission to create data warehouse syncs in Klaviyo. Use the private key here, including its PEM header and footer; the public key belongs in the Snowflake setup script.

1. Open Data syncing in Klaviyo. If you already have a connection, click Create sync.
2. Select Import data from your data warehouse, select Snowflake, then click Connect to Snowflake.
3. Enter your Account, Warehouse, Database, Username, and Private Key. Use the warehouse, database, and user names from your setup script. Click Next.
4. Click Test. After the connection succeeds and Klaviyo finds your import tables, click Next.
5. Enter a name for the sync, select your table, and select Profiles as the data object type. Under Cadence, select Schedule run and choose the frequency, start date, start time, and timezone. Click Next.
6. Under Change detection, select your timestamp field in New Row Detection. Map your table’s identifier column to the corresponding Klaviyo profile identifier, such as email, phone number, or external ID, and map the profile properties you want to import.
7. Click Done to save the connection and sync. After a successful save, Klaviyo returns you to Data syncing.

The scheduled sync imports existing rows and picks up new or updated rows using the timestamp field you selected. Keep updating that field whenever an import row changes.

Troubleshooting

Sync appears stuck

If an import is running but data is not appearing in Klaviyo, check its timestamp values and the number of rows in each timestamp window:

• For an initial historical import or a manual import, check for timestamps much older than the rest of the data, such as 1 row from 2023 when the others are from the past week. Klaviyo scans the intervening 1-hour windows before reaching recent rows. For an ongoing scheduled sync, check that new or updated rows have current change-detection timestamps; backdated changes can fall before its saved progress and be missed.
• Before enabling an import, correct erroneous change-detection timestamps. For a backfill, use a recent timestamp for the import table’s change-detection field while keeping any dates that describe the original activity in separate columns. For large backfills, aim for batches of about 100,000 rows, load them at least 61 minutes apart, and stamp each batch with CURRENT_TIMESTAMP() when it is loaded. This spreads rows across the 1-hour windows instead of placing the entire backfill in 1 window.

Recommended Snowflake clustering key setup

Clustering a Snowflake table on the timestamp column can help import queries skip unnecessary micro-partitions. Review Snowflake’s Automatic Clustering costs before adding a clustering key; lower query costs do not guarantee lower total costs. To add a key, replace the database, table, and column names in this example and run it in Snowflake:

ALTER TABLE your_database.KLAVIYO_IMPORT_FROM_DWH.your_table CLUSTER BY (your_timestamp_column);