Snowflake event-table optimization guide

You will learn

This guide walks through two recommended Snowflake optimizations for the Klaviyo events table. These changes are intended to reduce broad table scans and help Snowflake focus on the relevant event records and time ranges during synchronization.

• Enable Search Optimization for equality lookups on id. This requires Snowflake Enterprise Edition or higher.
• Cluster the events table by the event date. This works on every Snowflake edition and should be done regardless of whether you can also complete the Search Optimization step.

Before you begin

Step 1: Identify and validate the events table

1. Open a Snowflake worksheet using a role with the required permissions.
2. Confirm the exact table identifier used by your Klaviyo integration.
3. Verify that the table contains the expected lookup and timestamp columns.

For example:

DESCRIBE TABLE <EVENTS_TABLE>;

Confirm that:

• id is the event identifier used for equality lookups.
• DATETIME is the event timestamp, or identify the equivalent timestamp column to use in the clustering expression.

Step 2: Capture a baseline (optional)

Before applying the changes, record enough information to compare results afterward:

• Current sync status and duration.
• Whether the sync is historical or incremental.
• Snowflake warehouse used by the integration.
• Approximate credits or compute used by recent sync activity.
• Any relevant query IDs, error messages, or timestamps from Snowflake Query History.

Step 3: Enable Search Optimization on the event ID (optional)

Run the following statement after replacing the placeholder with your actual table name:

ALTER TABLE <EVENTS_TABLE>
ADD SEARCH OPTIMIZATION ON EQUALITY(id);

For example, if the table is named klaviyo_events:

ALTER TABLE klaviyo_events
ADD SEARCH OPTIMIZATION ON EQUALITY(id);

This creates an optimized access path for equality predicates on id, which is intended to reduce the amount of the events table Snowflake must scan for targeted lookups.

Step 4: Add date-based clustering to the events table

Apply a clustering key based on the date portion of the event timestamp:

ALTER TABLE <EVENTS_TABLE>
CLUSTER BY (TO_DATE(DATETIME));

For example:

ALTER TABLE KLAVIYO_EVENT
CLUSTER BY (TO_DATE(DATETIME));

Use the timestamp column confirmed in Step 1. If your column has a different name, substitute that column in the expression.

Date-based clustering is intended to help Snowflake prune unrelated time ranges instead of scanning the full events table.

Step 5: Allow Snowflake maintenance to complete

After both statements succeed:

1. Allow Snowflake time to build or maintain the search and clustering structures.
2. Avoid judging the result from the first query immediately after the DDL statements.
3. Continue monitoring warehouse usage and query performance while the table is being maintained.

The amount of time required depends on the table size, existing data layout, and Snowflake workload.

Step 6: Notify Klaviyo

Send Klaviyo the following details:

• The fully qualified events-table name.
• The timestamp when each statement was applied.
• Confirmation that Search Optimization was enabled for id.
• Confirmation that date-based clustering was applied.
• The Snowflake account/region and warehouse used for the sync.
• Any relevant sync ID, query ID, or error details.

Klaviyo will verify whether any export-side configuration is required to take full advantage of the event-table optimization.

Step 7: Run a controlled validation sync (optional)

Where possible, validate with an incremental or otherwise limited sync before restarting a large historical backfill.

Compare the new run with the baseline from Step 2:

• Time to start processing.
• Total sync duration.
• Progress behavior in the Klaviyo UI.
• Snowflake bytes scanned.
• Warehouse credits consumed.
• Whether the sync completes without retries or repeated work.

The strongest signal is an improvement across both sync duration and Snowflake compute, rather than one metric in isolation.

Step 8: Monitor the next syncs

Track several subsequent syncs rather than relying on a single run. Look for:

• Stable or declining compute as the events table grows.
• Fewer broad scans of historical data.
• More predictable sync duration.
• No increase in failed or repeated queries.

Share the results with Klaviyo, including before-and-after query IDs when available.

Troubleshooting

The Search Optimization statement fails

Check that:

• The Snowflake account supports the feature.
• The executing role has permission to alter the table.
• The table name and id column are correct.
• Search Optimization is not already configured for the requested column.

The clustering statement fails

Check that:

• The table name is correct.
• The timestamp column exists and is a compatible timestamp or date value.
• The executing role can alter the table.
• The clustering expression matches the actual event timestamp field.

The sync is still slow or appears stuck

Send Klaviyo:

• The exact table definitions and statements used.
• The time each change was applied.
• The sync start time and current status.
• Snowflake query IDs and warehouse usage.
• Any evidence that the UI status is not progressing.

Completion checklist

• Correct events table identified.
• id and event timestamp columns confirmed.
• Baseline sync and compute metrics recorded (optional).
• Search Optimization enabled for equality lookups on id (optional).
• Events table clustered by event date.
• Klaviyo notified of the changes.
• Controlled validation sync completed.
• Follow-up sync performance and compute reviewed (optional).