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 uses MERGE statements to apply profile changes to KLAVIYO_PROFILE. Hybrid tables are built for low-latency, index-based point reads and concurrent writes. They can suit individual profile lookups from an application, an API, or a UI. Standard tables generally perform better for large analytical scans and aggregations. Before migrating, compare export performance, downstream queries, and total cost using your own data and workload.

Before you begin

Work through all 4 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, and replace PUBLIC if you use a different schema. Apply these substitutions throughout the SQL examples. Keep the profile table name KLAVIYO_PROFILE: the setup script marks it as fixed, and Klaviyo's export writes to that name.

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 a statement fails, stop and read the Snowflake error before continuing. An unsupported account or region, missing privileges, an unavailable warehouse, or a quota limit can prevent creation. Resolve the reported cause and rerun the check successfully.

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. SIZE_GB describes the existing standard table; it is not a reliable estimate of hybrid storage. Measure the test hybrid table's storage and leave room for indexes and growth under your database's hybrid storage quota.

3. Check for duplicate or null IDs

Klaviyo's setup script declares primary key (ID) and ID NOT NULL on the standard table. Snowflake does not enforce primary keys on standard tables, but it does enforce NOT NULL. Hybrid tables enforce both, so duplicate IDs will block the load. Check for null IDs as well in case the existing table's schema differs from the setup script. 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_DATABASE = 'KLAVIYO_DATABASE'
  AND REFERENCED_SCHEMA = 'PUBLIC'
  AND REFERENCED_OBJECT_NAME = 'KLAVIYO_PROFILE';

This view can lag by up to 3 hours and does not include data movement such as CTAS, INSERT, or MERGE. Also check scheduled tasks, external applications, and other consumers of the table. If anything downstream relies on an unsupported feature, rebuild it before you migrate. Plan to update and validate dependent objects after the rename in step 5.

Step 1: Pause your export sync

1. In Klaviyo, navigate to Advanced KDP > Data management > Syncing.
2. Select your Snowflake destination and open the Periodic tab.
3. In the Profile Export row, select Pause.

If a profile export is still running in the Historical tab, pause that task too. Keep all profile exports paused until step 7.

Confirm the tasks you paused show Paused, then confirm in Snowflake query history that any export queries already running have finished. The paused status alone does not confirm that an in-progress write has stopped. Keep other writers to KLAVIYO_PROFILE stopped throughout the copy, validation, and rename. Migrating while the table is changing can leave the copy incomplete.

Step 2: Create the hybrid table and load it

Choose 1 loading method: run the single CREATE HYBRID TABLE ... AS SELECT (CTAS) statement below, or use the batch-loading alternative in the next subsection. Do not run both: each example replaces KLAVIYO_PROFILE_HYBRID. CTAS requires you to declare the full column schema explicitly; it cannot be inferred from the SELECT. The examples match Klaviyo's setup script. Compare your existing schema first and preserve its column names and data types.

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, use this alternative to create the hybrid table empty and load it in date ranges. The initial INSERT INTO ... SELECT into an empty table uses the optimized bulk-load path. For later batches, use of that path depends on the size and number of rows being loaded. Check the query profile to confirm whether it was used.

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)
);

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 overhead to writes. The statements below are optional examples: use EMAIL or EXTERNAL_ID for lookups on those columns, and UPDATED for queries filtered by recency. Run only the statements you need, 1 at a time, and wait for each index to finish building before starting another.

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);

After each CREATE INDEX, check its status with SHOW INDEXES. Wait for ACTIVE before creating the next index. If the status is BUILD FAILURE, inspect status_info, resolve the reported cause, then drop and recreate the index:

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 replace someone@example.com with an email present in your data. Run the same point-read query against the standard table and compare the returned profile values. Evaluate performance using representative queries from your actual application as well:

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

Keep all profile exports and other writers stopped. Rename the standard table out of the way rather than dropping it, so you have a backup for rollback. Confirm that KLAVIYO_PROFILE_STANDARD_BACKUP is not already in use before running these statements. After the rename, update and test the downstream dependencies you identified earlier so they use the hybrid table.

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

1. In Klaviyo, return to Advanced KDP > Data management > Syncing.
2. Select your Snowflake destination and open the Periodic tab.
3. In the Profile Export row, select Resume. Resume any historical profile export that you paused in step 1 as well.

Periodic exports follow the cadence configured for your destination. After the resumed export processes profile changes, check that it runs without errors and that those changes appear in Snowflake. A periodic task can return to Scheduled between runs; it does not need to show Completed. Use the query below to check the row count and latest profile update timestamp, and spot-check a profile you know changed. These values alone do not prove that every change was exported:

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. Once exports and downstream checks pass, restart any other writers you stopped.

Rolling back

If the sync fails against the hybrid table and you need to revert, pause any active profile exports and confirm that in-progress Snowflake writes have finished, as in step 1. Before renaming the tables, determine whether any inserts, updates, or deletes reached the hybrid table after the backup was created. The backup does not receive those changes.

If changes reached the hybrid table, reconcile those inserts, updates, and deletes into the standard backup before you restore it. If you cannot reconcile the data, keep exports stopped and contact Klaviyo Support for help recovering the missing changes. Resume preserves the export's progress and does not automatically replay changes already processed. Once the backup is current, use the commands below and substitute your Klaviyo service role name.

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;

Verify the Klaviyo role's grants and restore any downstream access and dependencies on the standard table. Then resume the profile exports and repeat the checks in step 7. For a task that shows Failed, select Re-enable instead of Resume. Keep the hybrid table until you have confirmed that the restored table is current and exports are working. Restart any other writers once validation passes.

After you migrate

Keep the standard backup until you have validated resumed exports and downstream consumers and no longer need it for rollback. Once those checks pass, 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: The default quota is 2 TB of active hybrid table data in the row store per Snowflake database. Above quota, writes that add data to any hybrid table in that database are blocked, which can fail your export. Remove data or tables to reduce usage, or contact Snowflake Support about a quota increase.
• 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: Snowflake charges for hybrid table storage and virtual warehouse compute. Row-store storage is more expensive than standard storage, while the warehouse consumption rate is the same. Review Snowflake's cost documentation and measure your workload 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