Migration Guide: From Redshift, BigQuery & Legacy Warehouses

Migrating to Snowflake from Amazon Redshift, Google BigQuery, or a legacy data warehouse is a strategic move for many organizations seeking better scalability, performance, and cost management. Snowflake's unique architecture—separating storage and compute, automatic scaling, and near-zero maintenance—makes it an attractive target. However, migration is not trivial. Differences in SQL syntax, data types, partitioning, and performance optimization require careful planning. This guide provides a practical, step-by-step approach to migrating your data warehouse to Snowflake. We'll cover schema conversion, data transfer, query adaptation, and common pitfalls. Whether you're moving from a traditional on-premise warehouse or a cloud-based solution, you'll find actionable advice and real-world examples. By the end, you'll be equipped to execute a smooth migration with minimal downtime.

1. Assessing Your Source Warehouse

Before migrating, thoroughly assess your current data warehouse. Identify all databases, schemas, tables, views, stored procedures, and user-defined functions. Document data types, indexes, partitioning schemes, and any proprietary features. For Redshift, note sort keys and distribution styles. For BigQuery, note clustering and partitioning. For legacy warehouses, capture any custom SQL dialects. This assessment will guide your conversion strategy. Create a migration plan that includes a timeline, resource allocation, and rollback procedures. Use Snowflake's Migration Assessment Tool if available.

-- Redshift: Get table DDL
SELECT pg_catalog.pg_get_viewdef('schema.table_name', true);
-- BigQuery: Get table info
SELECT table_name, table_type, ddl FROM `project.dataset.INFORMATION_SCHEMA.TABLES`;
-- Legacy: Use SHOW CREATE TABLE or equivalent

2. Converting DDL and Data Types

Snowflake uses a different SQL dialect and data type system. Map source types to Snowflake equivalents: Redshift's BIGINT to NUMBER(38,0), TIMESTAMP to TIMESTAMP_NTZ, VARCHAR to VARCHAR. BigQuery's ARRAY<STRUCT> to VARIANT, GEOGRAPHY to GEOGRAPHY. Legacy types like MONEY to NUMBER(19,4). Use Snowflake's CONVERT function or manual CAST. For DDL, replace Redshift's DISTKEY and SORTKEY with Snowflake's clustering keys. BigQuery's partitioning becomes Snowflake's automatic clustering. Legacy indexes are not needed; Snowflake uses automatic micro-partitioning. Example conversion: Redshift CREATE TABLE with DISTKEY and SORTKEY becomes Snowflake CREATE TABLE with CLUSTER BY.

-- Redshift original
CREATE TABLE orders (
  order_id BIGINT,
  order_date TIMESTAMP,
  customer_id BIGINT,
  amount DECIMAL(10,2)
) DISTKEY(customer_id) SORTKEY(order_date);

-- Snowflake converted
CREATE OR REPLACE TABLE orders (
  order_id NUMBER(38,0),
  order_date TIMESTAMP_NTZ,
  customer_id NUMBER(38,0),
  amount NUMBER(10,2)
) CLUSTER BY (order_date);

3. Data Transfer Strategies

Choose a data transfer method based on volume and latency requirements. Options: 1) COPY INTO from cloud storage (S3, GCS, Azure Blob) using external stages. 2) Snowpipe for continuous ingestion. 3) Third-party ETL tools (Fivetran, Matillion). 4) Custom scripts using Snowflake's Python connector. For large datasets, use COPY INTO with compression (gzip, snappy). Use VALIDATION_MODE to catch errors before loading. Example: unload from Redshift to S3 as CSV, then COPY into Snowflake. For BigQuery, export to GCS as Parquet. For legacy, use ODBC/JDBC bulk export.

-- Create external stage pointing to S3
CREATE OR REPLACE STAGE my_stage
  URL='s3://my-bucket/data/'
  CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');

-- COPY into Snowflake with error handling
COPY INTO orders
  FROM @my_stage/orders/
  FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY='"')
  VALIDATION_MODE = 'RETURN_ERRORS';

4. Query Adaptation and Optimization

Rewrite queries to leverage Snowflake's features. Replace Redshift's window functions with Snowflake's (similar but check syntax). BigQuery's UNNEST becomes LATERAL FLATTEN. Legacy SQL might need CTEs or subqueries. Optimize by using clustering keys on frequently filtered columns, materialized views for aggregations, and result caching. Avoid anti-patterns like SELECT * in production. Use query profiles to identify bottlenecks. Example: convert a Redshift query with DISTKEY join to Snowflake without explicit distribution.

-- Redshift: Join on distribution key
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Snowflake: No distribution needed; just join
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- BigQuery UNNEST to Snowflake FLATTEN
-- BigQuery: SELECT * FROM table, UNNEST(arr) AS element
-- Snowflake: SELECT * FROM table, LATERAL FLATTEN(input=>arr) AS element

5. Handling Stored Procedures and UDFs

Migrate stored procedures and UDFs to Snowflake's JavaScript or SQL-based procedures. Redshift's PL/pgSQL can be converted to Snowflake's JavaScript stored procedures. BigQuery's UDFs in SQL or JavaScript can be ported directly. Legacy procedures may need significant rewriting. Use Snowflake's CREATE PROCEDURE with LANGUAGE JAVASCRIPT. For simple logic, use SQL UDFs. Test each procedure with sample data. Example: convert a Redshift procedure to Snowflake.

-- Redshift procedure (PL/pgSQL)
CREATE OR REPLACE PROCEDURE update_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders SET status = 'processed' WHERE order_date < CURRENT_DATE;
END;
$$;

-- Snowflake procedure (JavaScript)
CREATE OR REPLACE PROCEDURE update_orders()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
  var sql = "UPDATE orders SET status = 'processed' WHERE order_date < CURRENT_DATE";
  snowflake.execute({sqlText: sql});
  return 'Updated';
$$;

6. Testing and Validation

After migration, validate data integrity and performance. Compare row counts, checksums, and sample records between source and target. Use MINUS to find differences. Run a subset of production queries and compare results. Test with concurrent users to assess warehouse scaling. Monitor Snowflake's query history and account usage. Create a rollback plan if issues arise. Example: validate using MINUS.

-- Compare row counts
SELECT COUNT(*) FROM source_table;
SELECT COUNT(*) FROM target_table;

-- Find differences using MINUS
SELECT * FROM source_table
MINUS
SELECT * FROM target_table;

-- Check for extra rows
SELECT * FROM target_table
MINUS
SELECT * FROM source_table;

7. Post-Migration Optimization

After migration, optimize Snowflake for cost and performance. Set up resource monitors to control costs. Enable auto-suspend for warehouses. Use automatic clustering and materialized views. Implement time travel retention as needed. Monitor with Snowflake's built-in tools. Consider using Snowflake's search optimization service for point lookup queries. Example: create a resource monitor.

-- Create resource monitor
CREATE OR REPLACE RESOURCE MONITOR my_monitor
  WITH CREDIT_QUOTA = 1000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = '2025-01-01 00:00:00'
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

-- Assign to warehouse
ALTER WAREHOUSE my_wh SET RESOURCE_MONITOR = my_monitor;