Recently, our team successfully completed a large-scale migration from Redshift to Snowflake for a leading global luxury beauty brand. This was no small feat. It involved migrating dbt models, ensuring minimal disruption for Looker users, and optimizing for better performance and cost efficiency.
Migrations are notoriously complex: 75% go over budget, and 38% fall behind schedule, according to McKinsey (source). We weren’t about to be part of that statistic.
If you’re a data leader considering a similar transition, I hope our experience offers a framework for planning and managing a smooth migration.
Alignment First, Migration Second
We had three months. That wasn’t just a target, it was a commitment to focused execution and minimal disruption.
Before starting the migration, we conducted a series of alignment discussions. The aim was simple: get everyone on the same page regarding our goals, architecture, and impacted workflows to achieve success.
We covered:
- Defining Migration Goals: We outlined our primary targets: optimizing performance, minimal to no disruption to stakeholders leveraging Looker, and a clear understanding of what items would be migrated and how.
- Identifying the Architecture: We identified all of the architecture that would be involved in the migration. In our case: Fivetran, dbt, and Looker.
- Identifying Stakeholders and Workflows: We mapped out key board meetings, product launches, and other mission-critical events where minimizing disruption was non-negotiable. This helped us pinpoint which teams, workflows, and recurring meetings would be affected, ensuring we had a clear communication plan in place to keep everything running smoothly.
- Outlining Migration Items: Every element in our tech stack was broken down. For each item, we considered:
- Priority and Level of Effort
- Migration Type [No Change, Fix (minor SQL updates), Refactor]
- Migration Group (Product, Customer Behavior, etc.)
- Dependencies and Additional Notes
By structuring our discussions this way, we set a solid foundation for the rest of the project.
The Tech Stack in Action
Our migration plan revolved around carefully chosen tools for assistance:
- Fivetran & AWS S3: While Fivetran was our primary tool for syncing data, we faced a timing challenge with Shopify’s historical sync. To avoid impacting timelines, we leveraged AWS S3 as an intermediary option, preserving production data for the final migration steps.
- Redshift to Snowflake: Transitioning between these two platforms required a clear understanding of both environments, ensuring that performance and cost were managed effectively.
- dbt: We leveraged dbt Core to switch between the two warehouse environments. We also set up a new dbt project to streamline the migration and ensure our SQL code was compatible with Snowflake.
- Looker: To minimize disruption for stakeholders, we created a new Looker project that could be easily integrated into the existing setup. This approach helped us sidestep major change management challenges that had already taken place a few months prior with the introduction of Looker.
- Cursor and SnowConvert: SnowConvert is one of the latest tools released by Snowflake that we leveraged to help accelerate the Redshift to Snowflake conversion process. Given the novelty of the tool, our team also leveraged Cursor to verify that our code met Snowflake SQL standards. This ensured a smooth translation from Redshift.
A migration is only as good as its testing strategy
We defined multiple testing points throughout the project (ingestion, each dbt layer, and Looker), focusing on:
- Data Integrity Checks: Row counts, null values, primary keys, time zones, and aggregated data were all carefully compared between warehouses. This was particularly important considering that warehouses handle aggregation precision, timezones, etc. differently.
- Variance Tolerance: We set an acceptable variance threshold of less than 3% between Snowflake and Redshift to account for any minor discrepancies.
One key aspect of the testing strategy was automation to minimize impact on timelines. Our team modified open source software to fit our needs for this migration. This allowed us to validate and easily compare data tables between Redshift and Snowflake to identify where discrepancies existed and specifically which records had variances.
Lastly, given the timing demands of the QA process, our team had decided to allocate one engineer to handle the SQL migration items and two engineers to run QA and validation.
Avoiding Migration Landmines
Even the best-planned migrations come with surprises. Anticipating potential "gotchas" early helped us stay ahead of challenges and keep the project on track.
- Budget Awareness: Running Fivetran and Redshift in parallel temporarily increased costs, something we planned for by leveraging AWS S3 as an intermediary. This ensured we could preserve Fivetran production data without unnecessary spend, keeping the transition smooth and cost-effective.
- Risk-Proofing the Migration: We built a contingency plan for common pitfalls, unexpected access issues, performance tuning needs, and ad-hoc stakeholder requests. By mapping out potential blockers in advance, we avoided last-minute fire drills and kept the migration running smoothly.
Lessons from the Trenches
In the end, as the result of migrating away from Redshift, our team was able to reduce load times for Looker dashboards from 50 seconds to 15 seconds! Every migration is a lesson in trade-offs, planning, and adaptability. No matter how much you prepare, challenges will come up, but a clear strategy, strong alignment, and the ability to adjust make all the difference.
For anyone tackling a similar migration, here’s my advice: Plan aggressively, communicate often, and expect surprises. The goal isn’t perfection, it’s making sure your team can handle the unexpected without losing momentum or veering off track.
