RDS to Snowflake: Practical Data Migration for Low-Latency Analytics

Direct lift-and-shift from Amazon RDS (MySQL/PostgreSQL/SQL Server) to Snowflake rarely meets the requirements for high-performance analytics. The naïve approach—dump and reload—ignores schema, process, and query differences, creating technical debt and brittle pipelines. Treat RDS-to-Snowflake migration as an inflection point to modernize both your architecture and ingestion strategy.

Evaluating RDS Environment: Schema, Workloads, and Gaps

Blind migration can lock in old inefficiencies. Establish:

• Schema mapping: Map normalized OLTP schemas to Snowflake's columnar storage. Typical anti-patterns: highly normalized joins and over-indexed tables.
• Volume and activity: Analyze actual data sizes (not just allocated storage), plus daily change rates. Example: A 600 GB RDS instance with ~1 million new rows/day yields different pipeline sizing from a static archive.
• Workload profile: Identify reference tables, lookup dimensions, and any hot-path queries. For instance, dashboards updating every 2 minutes require change tracking, not just batch loads.

Known issue: RDS date/timestamp precision and time zones rarely match Snowflake defaults. Normalize during export to avoid downstream headaches.

Toolchain Selection: Speed, Reliability, Cost

Multiple approaches, varying trade-offs:

Sample DMS config for ongoing sync:

{
  "cdcStartTime": "2024-06-01T00:00:00Z",
  "targetTablePrepMode": "DO_NOTHING",
  "controlTablesSchema": "cdc_meta",
  "includeOpForFullLoad": false
}

AWS DMS minimum version recommended: 3.5.1. For Parquet, enable Snappy compression to reduce S3 egress time.

Efficient Bulk Load: File Formats and Partition Strategy

Max throughput and minimize ingest cost:

• Use Parquet (columnar, compressed) for large tables. Typical reduction vs CSV: ~5-8x in size.
• Partition outputs by event date, created_at, or similar. E.g.:

  s3://your-bucket/exported/orders/date=2024-06-11/*.parquet
  

• For large tables, split files to 100-250 MB chunks. Snowflake's COPY INTO achieves peak performance in this zone.

Sample load command:

COPY INTO raw.orders
FROM @external_stage/orders/
FILE_FORMAT = (type = 'PARQUET')
PATTERN = '.*date=2024-06-.*.parquet'
ON_ERROR = 'SKIP_FILE';

Note: Unpartitioned or oversized files are common ingestion bottlenecks. Monitor load history for skew.

Schema Optimization in Snowflake

OLTP designs rarely fit Snowflake's architecture:

• Denormalization: Merge lookup/reference tables where possible; avoid excessive joins in analytical queries.
• Semi-structured columns: Use VARIANT for columns storing JSON/BLOB payloads—reduces load/transformation steps, and simplifies evolving schemas.
• Clustering keys: Place on high-cardinality, commonly-filtered columns (e.g. order_date) to improve pruning. Not indexing—clusters only help scan efficiency.

Example for merging CDC and full loads:

MERGE INTO analytics.orders AS tgt
USING staging.orders_cdc AS src
  ON tgt.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...);

Orchestrating Real-Time and Near-Real-Time Sync

Low-latency pipeline:

1. Enable CDC on RDS (binlog_format=ROW for MySQL, rds.logical_replication=1 for PostgreSQL).
2. Stream CDC events: Use AWS DMS or Debezium → S3.
3. Snowpipe autotriggers: S3 PUT creates event, pipes to Snowflake staging table within ~1 minute.
4. Transformations via TASKS: Automate MERGE/UPDATE; for heavy loads, stagger with multi-task tree.
5. BI access: Connect Looker/PowerBI/Tableau, point at analytics schema.

Gotcha: Network hiccups between RDS/S3/Snowflake can trigger lag >5m. Downstream dashboards should display data currency or completeness markers.

Realistic Example: E-Commerce Orders Pipeline

• Tables: orders, customers in RDS MySQL 8.0.32.
• Bulk load (nightly):

  mysqldump --tab=/tmp/export --fields-terminated-by=',' orders
  compress and upload to S3 → COPY INTO snowflake.raw_orders
  

• Incremental sync (live):
  • DMS task filtering only INSERT/UPDATE/DELETE on orders/customers.
  • CDC data lands in S3 as Parquet every 2 minutes.
  • Snowpipe auto-loads to staging.orders_cdc.
  • Scheduled TASK runs every minute:

    MERGE INTO analytics.orders USING staging.orders_cdc ON ...
    

• Edge case: Foreign keys dropped during migration, replaced by periodic referential checks via SQL scripts or dbt tests; Snowflake does not enforce FK constraints.

Monitoring and Robustness

• Monitor with CloudWatch (DMS errors/latency) and Snowflake's LOAD_HISTORY/TASK_HISTORY.
• Expected error example:

  COPY INTO failed: File skipped due to record parse error at line 123. Value too large for column.
  

  Usually caused by mismatched VARCHAR lengths; fix via explicit casting in export.
• Plan for iterative batching tweaks—network throughput and S3/AWS throttling can vary by region and time.

Notes and Pro Tips

• Pre-load small reference/dimension tables fully; don't CDC them unless critical to business logic.
• Use snowflake.connector for Python-based task orchestration; sometimes easier than managed Airflow for lightweight setups.
• For multi-TB pipelines, throttle DMS or Snowpipe workers to avoid surprise costs.
• Remember: Snowflake credits burn fast if accidentally re-processing large files—archive moved files immediately after ingest.

Migrating from RDS to Snowflake, when executed with the right schema and pipeline choices, routinely reduces dashboard latency from hours to minutes. Yet live sync and real-time dashboards come at the price of deeper monitoring and process rigor. The most robust migrations are opinionated—drop OLTP baggage, favor denormalization, and test performance under load before transitioning stakeholders.

Questions for edge cases or unusual source schemas? Always prototype on representative data—Snowflake's cost model will penalize avoidable mistakes.