snowflake-migration-deep-dive

name	snowflake-migration-deep-dive
description	Execute migration to Snowflake from Redshift, BigQuery, or on-prem databases with data transfer, schema conversion, and validation strategies. Use when migrating to Snowflake from another platform, planning data transfers, or re-platforming existing data warehouses to Snowflake. Trigger with phrases like "migrate to snowflake", "snowflake migration", "redshift to snowflake", "bigquery to snowflake", "snowflake replatform".
allowed-tools	Read, Write, Edit, Bash(npm:*), Bash(python3:*)
version	1.5.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","data-warehouse","analytics","snowflake"]
compatibility	Designed for Claude Code

Snowflake Migration Deep Dive

Overview

Comprehensive guide for migrating to Snowflake from Redshift, BigQuery, on-prem databases, or other data warehouses.

Migration Types

Source	Complexity	Duration	Key Challenge
Amazon Redshift	Medium	2-6 weeks	SQL dialect differences
Google BigQuery	Medium	2-6 weeks	Nested/repeated fields
On-prem (Oracle/SQL Server)	High	1-3 months	Data transfer bandwidth
Another Snowflake account	Low	Days	Replication or data sharing

Instructions

Step 1: Schema Conversion

-- Common SQL differences from Redshift/BigQuery

-- Redshift DISTKEY/SORTKEY → Snowflake clustering (optional, for large tables)
-- Redshift: CREATE TABLE orders (id INT) DISTSTYLE KEY DISTKEY(customer_id) SORTKEY(order_date);
-- Snowflake:
CREATE TABLE orders (
  id INTEGER AUTOINCREMENT,
  customer_id INTEGER,
  order_date TIMESTAMP_NTZ
);
ALTER TABLE orders CLUSTER BY (order_date);  -- Only for tables > 1TB

-- Redshift IDENTITY → Snowflake AUTOINCREMENT
-- Redshift: id INT IDENTITY(1,1)
-- Snowflake: id INTEGER AUTOINCREMENT START 1 INCREMENT 1

-- BigQuery STRUCT/ARRAY → Snowflake VARIANT/ARRAY
-- BigQuery: address STRUCT<street STRING, city STRING>
-- Snowflake:
CREATE TABLE customers (
  id INTEGER,
  address VARIANT  -- Store as JSON: {"street": "...", "city": "..."}
);
-- Access: SELECT address:street::VARCHAR FROM customers

-- BigQuery REPEATED fields → Snowflake ARRAY
-- BigQuery: tags ARRAY<STRING>