We are currently running an aging on-premise SQL Server data warehouse that is hitting its storage limits. Management wants us to migrate to Snowflake to leverage cloud scalability and better performance for our BI reports. However, I’m worried about the ETL pipeline cutover. How do we ensure data consistency during the migration, and what is the best strategy to sync historical data while still processing daily delta loads without significant downtime?
3 answers
Migrating to Snowflake requires a phased approach to avoid the "Big Bang" failure. Start by setting up a parallel environment where you replicate your schema using tools like Flyway or dbt. For the data transfer, use an initial bulk load via AWS S3 or Azure Blob Storage using the COPY INTO command. To handle the "delta" or incremental loads during the transition, implement Change Data Capture (CDC) on your source SQL Server. This allows you to keep both systems in sync until you are confident enough to point your BI tools to the new Snowflake endpoint. Ensure you validate row counts and checksums at every stage to guarantee that no records were dropped during the high-volume transfer process.
Have you evaluated the cost implications of running both systems in parallel during the testing phase, especially regarding data egress fees from your local data center?
We used the "Strangler Fig" pattern for our migration. We moved one data mart at a time starting with Finance, which reduced the risk significantly.
I agree with Jessica. Moving the entire warehouse at once is a recipe for disaster. We tackled our Marketing data mart first and it helped us refine our Snowflake role-based access controls before the bigger departments moved in.
Robert, that's a valid concern. We actually calculated that the egress fees would be manageable if we compressed the data before the upload. However, I’m more worried about the network bandwidth bottleneck. If we are pushing 5TB of historical data while trying to maintain our production ETL jobs, will it kill our latency? Do you think it’s better to ship a physical Snowcone device for the initial bulk load or just try to throttle the upload during off-peak hours to save the bandwidth for our core business operations?