Redshift to Snowflake for a Small Team: Which Sort Keys Become Clustering Keys

Last updated: October 2026

Teams moving from Redshift often arrive with years of tuning in their table definitions: distribution keys chosen for joins, sort keys chosen for filters, and workload management queues adjusted week by week. The instinct is to carry all of it across. Most of it is not needed.

This article draws on the migration we delivered for a direct-to-consumer kitchenware brand with about $28 million in annual revenue across Shopify and Amazon, covered in our e-commerce Redshift migration case study. A two-person data team was spending about 40% of its time managing a four-node dc2.large Redshift cluster. Project details come from that engagement. The code is a simplified illustration written for this article.

Why the cluster needed so much attention

The team ran VACUUM every night, and it often had not finished by 7am, when the morning analytics queries started. While the two competed for the same cluster, dashboard load times went from about 8 seconds to more than 2 minutes, and the marketing team took to screenshotting dashboards every evening. Weekend traffic, the brand's busiest period, made things worse, and the team adjusted workload management queues every Friday.

Some of that maintenance is now automated on Redshift itself. Redshift has run VACUUM DELETE automatically in the background since December 2018 and automatic table sort since November 2019, and AWS says you rarely need a manual DELETE ONLY vacuum. AWS still advises a manual VACUUM when data must be fully sorted, for example after a large load, and on a small cluster that work competes with queries. The team's 40% figure covered more than vacuuming: tuning WLM, resizing nodes and troubleshooting sort and distribution keys.

Moving 3.2 TB through S3

We moved 3.2 TB by unloading from Redshift to S3 as Parquet and loading the same files into Snowflake with COPY INTO, running eight schemas in parallel. The transfer took about 14 hours. We considered third-party migration tools, but UNLOAD and COPY INTO are well documented and predictable, and they do not add another vendor. Our tiering article covers the UNLOAD options to watch, such as partition columns and time zones.

COPY INTO raw.order_items
  FROM @redshift_export/order_items/
  FILE_FORMAT = (TYPE = PARQUET)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

What happens to DISTKEY, SORTKEY and DISTSTYLE

In Redshift, DISTSTYLE decides how a table's rows are spread across nodes (AUTO, EVEN, KEY or ALL), DISTKEY names the column that collocates matching join values on the same slice, and SORTKEY stores rows in key order so range filters can skip blocks. Snowflake has no equivalent of the first two, because storage and compute are separate and data is organised into micro-partitions automatically. Converting the 67 Redshift views was mostly a matter of removing those clauses.

Sort keys deserve more thought, because Snowflake clustering keys play a similar role. We evaluated every table behind the 67 views and found that only three benefited from an explicit clustering key:

Everything else performed well without one because the tables were not large enough for it to matter, and adding clustering keys to a few smaller tables made them slower in testing. That matches Snowflake's guidance, which says clustering keys suit tables of multiple terabytes that are queried often, changed rarely and filtered selectively, with at most three or four columns and date columns as a common choice for fact tables.

-- How well does the table cluster on the candidate column today?
SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.order_items', '(order_date)');

-- Only if the figures and the query profile justify it:
ALTER TABLE analytics.order_items CLUSTER BY (order_date);

Reclustering is automatic and has a cost. Since 1 September 2026, newly clustered tables use Optima Clustering, billed on the uncompressed volume ingested multiplied by an overlap factor, which stays low for data that arrives in date order and is clustered by date.

Handling weekend peaks without WLM

In the project, the analytics warehouse ran at X-Small during the week and at Medium for weekend peaks, which replaced provisioning Redshift for the peak and paying for it all week. Weekend performance problems stopped, and the team no longer adjusts anything on Fridays.

It is worth being precise about how this works, because a standard Snowflake warehouse does not change size on its own. There are three ways to absorb a predictable peak:

• Scheduled resizing. A task runs ALTER WAREHOUSE on a schedule. A resize does not affect running statements; queued and new statements use the new size once it is provisioned.
• Multi-cluster scale-out. On Enterprise Edition, a multi-cluster warehouse adds clusters of the same size when queries queue, which suits many concurrent dashboard users.
• Adaptive warehouses. Generally available on Enterprise Edition in selected regions, they replace the fixed size setting and handle scaling automatically.

CREATE OR REPLACE TASK ops.weekend_scale_up
  WAREHOUSE = ops_wh
  SCHEDULE = 'USING CRON 0 18 * * FRI America/New_York'
AS
  ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'MEDIUM';

CREATE OR REPLACE TASK ops.weekday_scale_down
  WAREHOUSE = ops_wh
  SCHEDULE = 'USING CRON 0 6 * * MON America/New_York'
AS
  ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'XSMALL';

ALTER TASK ops.weekend_scale_up RESUME;
ALTER TASK ops.weekday_scale_down RESUME;

Fivetran for ingestion, dbt for the models

Custom Python scripts on an EC2 instance had been pulling data from Shopify, Amazon Seller Central, Google Analytics and Facebook Ads. Fivetran replaced them. For Shopify, Fivetran recommends its GraphQL schema, since Shopify deprecated its REST Admin API in October 2024, and its Amazon Selling Partner connector covers Seller Central data through the SP-API.

The chain of 67 SQL views became 42 dbt models in staging, intermediate and mart layers, with tests that run after every transformation. Within the first week, a test caught a Fivetran sync issue that was double-counting Amazon order refunds. The old Redshift views had the same bug; nobody had noticed because nothing was testing for it.

models:
  - name: stg_amazon__refunds
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns: ['order_id', 'refund_id']

Looker replaced the Mode instance that had been connected to Redshift. The migration took seven weeks including testing, the team's time on infrastructure fell from 40% to about 5%, and the company saved about $8,000 a month, comparing Redshift reserved-instance pricing with Snowflake on-demand pricing.