Snowflake event-table optimization guide

You will learn

Learn how to apply 2 recommended Snowflake optimizations to the events table exported from Klaviyo. 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)

Before enabling Search Optimization, review the existing configuration:

DESCRIBE SEARCH OPTIMIZATION ON <EVENTS_TABLE>;

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

Before setting the clustering key, run the following command and find your events table in the output:

SHOW TABLES IN SCHEMA <EVENTS_SCHEMA>;

Review cluster_by. If it already specifies TO_DATE() on the event timestamp column, keep that key and skip the ALTER TABLE statements in this step. If it specifies a different key, review the change with your data platform team first: CLUSTER BY replaces the existing key.

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

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

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 the statements you ran succeed, or you have confirmed the required settings are already in place:

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, optimization maintenance charges, 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.

Before comparing sync performance, run SHOW TABLES again and check the row for your events table:

SHOW TABLES IN SCHEMA <EVENTS_SCHEMA>;

• If Search Optimization is enabled, confirm search_optimization is ON and search_optimization_progress has reached 100 before evaluating its performance.
• Confirm cluster_by shows the expected date expression and automatic_clustering is ON. If it is OFF, ask your Snowflake administrator to resume Automatic Clustering. The ON setting shows that maintenance is enabled, not that initial reclustering has finished. Ask your administrator to check reclustering activity before comparing results.

Step 6: Notify Klaviyo

Send Klaviyo the following details:

• The fully qualified events-table name.
• Which statements you ran and when, or which settings were already in place.
• Whether Search Optimization is enabled for id or you skipped Step 3.
• Confirmation that date-based clustering is configured on the event timestamp column.
• The Snowflake account/region and warehouse used for the sync.
• Any relevant sync ID, query ID, or error details.

Ask Klaviyo to confirm whether your event export needs a configuration change to use date-based matching with the clustered timestamp column.

Step 7: Run a controlled validation sync (optional)

Coordinate a controlled validation run with Klaviyo. Where possible, validate with an incremental or otherwise limited sync before restarting a large historical backfill.

If you captured a baseline in Step 2, compare runs with similar data volumes using the same warehouse. Review:

• Time to start processing.
• Total sync duration.
• Progress behavior in the Klaviyo UI.
• Snowflake bytes scanned.
• Snowflake credits consumed, including warehouse usage and optimization maintenance charges.
• Whether the sync completes without retries or repeated work.

Evaluate sync duration, bytes scanned, and total Snowflake costs together. Lower warehouse usage alone does not establish a cost saving if optimization maintenance or storage costs increase.

Step 8: Monitor the next syncs

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

• Stable or declining Snowflake costs for comparable data volumes, including maintenance and storage charges.
• 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 is on Enterprise Edition or higher.
• The executing role has OWNERSHIP on the table.
• The table name and id column are correct.
• The executing role has ADD SEARCH OPTIMIZATION on the schema containing the table.

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 has OWNERSHIP on the table and USAGE or OWNERSHIP on its database and schema.
• 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 (optional).
• Follow-up sync performance and compute reviewed (optional).