Migrating your Snowflake profile export table to a hybrid table

You will learn

Learn how to convert the KLAVIYO_PROFILE table that Klaviyo's data warehouse export writes to in Snowflake from a standard table into a Snowflake hybrid table, with the exact commands to run at each step.

Klaviyo's export sync writes to KLAVIYO_PROFILE with a MERGE (upsert) on every periodic run. Hybrid tables are a Snowflake table type built for low-latency, index-based point reads and writes, so converting reduces the time and cost of those MERGE operations — this holds regardless of how you read the table afterward, so it's worth doing even if you only run large analytical scans and aggregations downstream. If you also use KLAVIYO_PROFILE to serve individual profile lookups to an application, an API, or a UI, a hybrid table returns those lookups faster too.

Before you begin

Work through all four checks below before you change anything.

1. Confirm your account supports hybrid tables

Replace KLAVIYO_DATABASE and KLAVIYO_DATA_TRANSFER_WAREHOUSE with the names you set in your Snowflake setup script. The script creates the profile table from a $profile_table variable, so substitute whatever value you assigned there anywhere this article refers to KLAVIYO_PROFILE.

USE ROLE SYSADMIN;
USE WAREHOUSE KLAVIYO_DATA_TRANSFER_WAREHOUSE;
USE DATABASE KLAVIYO_DATABASE;
USE SCHEMA PUBLIC;

CREATE OR REPLACE HYBRID TABLE HYBRID_SMOKE_TEST (ID VARCHAR(32) PRIMARY KEY);
DROP TABLE HYBRID_SMOKE_TEST;

If either statement fails, hybrid tables are not enabled for your account or region and you should stop here.

2. Size your profile table

SELECT
    ROW_COUNT,
    BYTES / POWER(1024, 3) AS SIZE_GB
FROM KLAVIYO_DATABASE.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME = 'KLAVIYO_PROFILE';

Hybrid tables use row-based primary storage, so the same data typically occupies more space than it does in a columnar standard table. Treat the SIZE_GB figure as a floor, not an estimate, and leave room under the 2 TB per-database quota.

3. Check for duplicate or null IDs

Klaviyo's setup script already declares primary key (ID) on the standard table, but Snowflake does not enforce primary keys on standard tables. Hybrid tables do enforce them, so duplicate or null IDs that were tolerated before will now block the load. Both queries must return 0.

SELECT COUNT(*) AS DUPLICATE_IDS
FROM (
    SELECT ID
    FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE
    GROUP BY ID
    HAVING COUNT(*) > 1
);

SELECT COUNT(*) AS NULL_IDS
FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE
WHERE ID IS NULL;

If either returns a non-zero value, resolve the data quality issue in the standard table before you continue. The load in step 2 will fail otherwise.

4. Check for downstream dependencies

Hybrid tables do not support clustering keys, data sharing, dynamic tables, Fail-safe, materialized views, Query Acceleration Service, replication, Search Optimization Service, Snowpipe, Snowpipe Streaming, streams, or UNDROP. Time Travel is supported with limitations.

SELECT
    REFERENCING_DATABASE,
    REFERENCING_SCHEMA,
    REFERENCING_OBJECT_NAME,
    REFERENCING_OBJECT_DOMAIN
FROM SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES
WHERE REFERENCED_OBJECT_NAME = 'KLAVIYO_PROFILE';

If anything downstream relies on an unsupported feature, rebuild it before you migrate.

Step 1: Pause your export sync

In Klaviyo, navigate to Advanced KDP > Data management > Syncing, click into your Snowflake destination, open the Periodic tab, and select Pause.

Wait until any in-progress sync shows a status of Completed or Paused before continuing. Migrating while a sync is writing to the table can drop rows.

Step 2: Create the hybrid table and load it

CREATE HYBRID TABLE ... AS SELECT requires you to declare the full column schema explicitly; it cannot be inferred from the SELECT. Use the same column names and data types as the existing table so Klaviyo's sync continues to write to it without changes.

USE ROLE SYSADMIN;
USE WAREHOUSE KLAVIYO_DATA_TRANSFER_WAREHOUSE;
USE DATABASE KLAVIYO_DATABASE;
USE SCHEMA PUBLIC;

CREATE OR REPLACE HYBRID TABLE KLAVIYO_PROFILE_HYBRID (
    ID VARCHAR(32) NOT NULL,
    EXTERNAL_ID VARCHAR(255),
    EMAIL VARCHAR(255),
    PHONE_NUMBER VARCHAR(255),
    FIRST_NAME VARCHAR(255),
    LAST_NAME VARCHAR(255),
    TITLE VARCHAR(255),
    ORGANIZATION VARCHAR(255),
    PROPERTIES OBJECT,
    IMAGE VARCHAR(255),
    CREATED TIMESTAMP_NTZ(9),
    UPDATED TIMESTAMP_NTZ(9),
    LOCATION_ADDRESS1 VARCHAR(255),
    LOCATION_ADDRESS2 VARCHAR(255),
    LOCATION_CITY VARCHAR(255),
    LOCATION_COUNTRY VARCHAR(255),
    LOCATION_LATITUDE VARCHAR(255),
    LOCATION_LONGITUDE VARCHAR(255),
    LOCATION_REGION VARCHAR(255),
    LOCATION_ZIP VARCHAR(255),
    PRIMARY KEY (ID)
)
AS
SELECT
    ID,
    EXTERNAL_ID,
    EMAIL,
    PHONE_NUMBER,
    FIRST_NAME,
    LAST_NAME,
    TITLE,
    ORGANIZATION,
    PROPERTIES,
    IMAGE,
    CREATED,
    UPDATED,
    LOCATION_ADDRESS1,
    LOCATION_ADDRESS2,
    LOCATION_CITY,
    LOCATION_COUNTRY,
    LOCATION_LATITUDE,
    LOCATION_LONGITUDE,
    LOCATION_REGION,
    LOCATION_ZIP
FROM KLAVIYO_PROFILE;

Loading a very large table in batches

If a single CTAS is too large to run comfortably, create the hybrid table empty and load it in date ranges instead. INSERT INTO ... SELECT also uses the optimized bulk-load path.

CREATE OR REPLACE HYBRID TABLE KLAVIYO_PROFILE_HYBRID (
    ID VARCHAR(32) NOT NULL,
    EXTERNAL_ID VARCHAR(255),
    EMAIL VARCHAR(255),
    PHONE_NUMBER VARCHAR(255),
    FIRST_NAME VARCHAR(255),
    LAST_NAME VARCHAR(255),
    TITLE VARCHAR(255),
    ORGANIZATION VARCHAR(255),
    PROPERTIES OBJECT,
    IMAGE VARCHAR(255),
    CREATED TIMESTAMP_NTZ(9),
    UPDATED TIMESTAMP_NTZ(9),
    LOCATION_ADDRESS1 VARCHAR(255),
    LOCATION_ADDRESS2 VARCHAR(255),
    LOCATION_CITY VARCHAR(255),
    LOCATION_COUNTRY VARCHAR(255),
    LOCATION_LATITUDE VARCHAR(255),
    LOCATION_LONGITUDE VARCHAR(255),
    LOCATION_REGION VARCHAR(255),
    LOCATION_ZIP VARCHAR(255),
    PRIMARY KEY (ID)
);

INSERT INTO KLAVIYO_PROFILE_HYBRID
SELECT * FROM KLAVIYO_PROFILE
WHERE UPDATED < '2024-01-01';

INSERT INTO KLAVIYO_PROFILE_HYBRID
SELECT * FROM KLAVIYO_PROFILE
WHERE UPDATED >= '2024-01-01' AND UPDATED < '2025-01-01';

INSERT INTO KLAVIYO_PROFILE_HYBRID
SELECT * FROM KLAVIYO_PROFILE
WHERE UPDATED >= '2025-01-01' OR UPDATED IS NULL;

Adjust the boundaries to suit your data, and make sure the ranges neither overlap nor leave gaps.

Step 3: Add secondary indexes

The primary key on ID is indexed automatically. Add secondary indexes only for the columns you actually filter on, because each index consumes storage and adds cost to every write. If you look up profiles by EMAIL or EXTERNAL_ID, index those columns; if you also filter or sort by recency (for example, fetching profiles updated since a given time), index UPDATED as well.

CREATE INDEX IDX_KLAVIYO_PROFILE_EMAIL
    ON KLAVIYO_PROFILE_HYBRID (EMAIL);

CREATE INDEX IDX_KLAVIYO_PROFILE_EXTERNAL_ID
    ON KLAVIYO_PROFILE_HYBRID (EXTERNAL_ID);

CREATE INDEX IDX_KLAVIYO_PROFILE_UPDATED
    ON KLAVIYO_PROFILE_HYBRID (UPDATED);

To see the indexes on the table:

SHOW INDEXES IN TABLE KLAVIYO_PROFILE_HYBRID;

Step 4: Validate the copy

Row counts must match and the difference query must return 0.

SELECT
    (SELECT COUNT(*) FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE) AS STANDARD_ROWS,
    (SELECT COUNT(*) FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE_HYBRID) AS HYBRID_ROWS;

SELECT COUNT(*) AS MISSING_IDS
FROM (
    SELECT ID FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE
    MINUS
    SELECT ID FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE_HYBRID
);

Confirm the new table really is hybrid, then spot-check a point read against the same query on the standard table:

SHOW HYBRID TABLES LIKE 'KLAVIYO_PROFILE_HYBRID';

SELECT * FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE_HYBRID
WHERE EMAIL = 'someone@example.com';

Step 5: Swap the hybrid table into place

Rename the standard table out of the way rather than dropping it, so you have a rollback path.

USE ROLE SYSADMIN;
USE DATABASE KLAVIYO_DATABASE;
USE SCHEMA PUBLIC;

ALTER TABLE KLAVIYO_PROFILE RENAME TO KLAVIYO_PROFILE_STANDARD_BACKUP;
ALTER TABLE KLAVIYO_PROFILE_HYBRID RENAME TO KLAVIYO_PROFILE;

Step 6: Re-grant privileges to the Klaviyo role

Substitute the role name you set as role_name in your Snowflake setup script.

USE ROLE SECURITYADMIN;

GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE
    TO ROLE KLAVIYO_DATA_TRANSFER_ROLE;

SHOW GRANTS ON TABLE KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE;

Step 7: Resume the sync and confirm

In Klaviyo, return to Advanced KDP > Data management > Syncing, click into your Snowflake destination, open the Periodic tab, and select Resume.

Periodic syncs run hourly. After the next cycle completes, confirm the status is Completed and check that data is landing:

SELECT
    COUNT(*) AS ROW_COUNT,
    MAX(UPDATED) AS LAST_UPDATED
FROM KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE;

If the sync errors, open the failed export in the Periodic tab to read the error summary, code, and the message returned by Snowflake. See the View error logs section of Understand data warehouse syncing in Klaviyo.

Rolling back

If the sync fails against the hybrid table and you need to revert, pause the sync and swap the tables back.

USE ROLE SYSADMIN;
USE DATABASE KLAVIYO_DATABASE;
USE SCHEMA PUBLIC;

ALTER TABLE KLAVIYO_PROFILE RENAME TO KLAVIYO_PROFILE_HYBRID;
ALTER TABLE KLAVIYO_PROFILE_STANDARD_BACKUP RENAME TO KLAVIYO_PROFILE;

USE ROLE SECURITYADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE
    TO ROLE KLAVIYO_DATA_TRANSFER_ROLE;

Then resume the sync. Klaviyo's next periodic sync backfills anything created or updated while the table was swapped out.

After you migrate

Once you are confident in the new table, drop the backup so it stops accruing storage costs.

USE ROLE SYSADMIN;
DROP TABLE KLAVIYO_DATABASE.PUBLIC.KLAVIYO_PROFILE_STANDARD_BACKUP;

Keep the following in mind on an ongoing basis:

• Storage quota: You are limited to 2 TB of hybrid table data per Snowflake database. If you exceed it, writes to every hybrid table in that database are blocked until you bring usage back under quota, which will fail your Klaviyo sync.
• Request quota: Roughly 16,000 operations per second per database for a balanced 80% read / 20% write workload.
• No Fail-safe, limited Time Travel: UNDROP is not supported for hybrid tables. Maintain your own backup process if you need one.
• No results cache: Queries against hybrid tables do not use Snowflake's persisted query results cache.
• Cost: Hybrid table storage and requests are metered separately from standard table storage and compute. Review Snowflake's cost documentation before migrating a large table.

Additional resources

• Understand data warehouse syncing in Klaviyo
• Connecting Klaviyo and Snowflake
• Snowflake: Hybrid tables
• Snowflake: Create hybrid tables
• Snowflake: Limitations and unsupported features for hybrid tables
• Snowflake: Evaluate cost for hybrid tables
• Snowflake: Converting standard Snowflake tables to hybrid tables