enterprise data warehouse modernisation
Enterprise Data Warehouse Modernisation: A Practical Guide to Snowflake and BigQuery Migration
A technical guide for data engineering leaders modernising legacy data warehouses to Snowflake or BigQuery — covering migration sequencing, data modelling changes, cost governance, and the common mistakes that extend timelines.
TL;DR — Key Points
- 1Complete the inventory before scoping — table count alone underestimates migration effort by 2–3x in most enterprise programs
- 2Run the SQL compatibility assessment before committing to a timeline — the manual-rewrite category is the critical path item
- 3Redesign the data model for columnar execution at migration time, not after go-live
- 4Implement Snowflake resource monitors and BigQuery cost controls before business users run queries on the new platform
Enterprise Data Warehouse Modernisation: A Practical Guide to Snowflake and BigQuery Migration
Enterprise data warehouse modernisation projects have a reputation for running over time and over budget. The cause is rarely the technology. Snowflake and BigQuery are genuinely better platforms than the Teradata or Oracle warehouses they replace.
The cause is that the migration plan underestimates three things: the data quality debt accumulated in the source system, the SQL dialect incompatibility between legacy and cloud-native warehouses, and the business tolerance for a BI tool cutover that happens at the same time as the data migration.
This guide covers the sequencing decisions and technical patterns that consistently reduce migration timelines for enterprise data warehouse modernisation programs.
What makes enterprise DWH migrations harder than they look
Legacy data warehouses — Teradata, Oracle Exadata, on-premises SQL Server — accumulate two decades of stored procedures, views, and transformation logic that no single person fully understands. The migration scope always grows once the actual inventory is complete. What starts as "migrate 50 tables" becomes "migrate 50 tables, 200 views, 80 stored procedures, and 30 SSIS packages" — half of which are not documented.
The second underestimated challenge is that analytical SQL written for Teradata or Oracle does not run on Snowflake or BigQuery without modification. QUALIFY, PIVOT, date arithmetic functions, window function syntax, and proprietary JOIN hints all differ. Automated migration tools convert 60–80% of SQL correctly; the remaining 20–40% requires manual review and rewrite, and that is where timeline estimates consistently fail.
A Sequenced Approach to Data Warehouse Modernisation
The following framework reduces migration timeline overruns by addressing the three root causes — data quality debt, SQL incompatibility, and BI cutover risk — before they become blocking issues in the final migration phases.
Complete a full inventory before scoping the migration
Before committing to a timeline, produce a complete inventory of: all source tables and row counts, all views and their dependency chains, all stored procedures and scheduled jobs, all ETL pipelines and their source/target mappings, and all BI reports and dashboards that consume warehouse data. Data catalog tools (Alation, Collibra, or open-source alternatives like OpenMetadata) can accelerate this. The inventory is the foundation of the migration scope; teams that skip it consistently underestimate the work by 2–3x.
Run a SQL compatibility assessment and flag manual rewrites early
Use the target platform's migration assessment tools — Snowflake's SQL migration guide, BigQuery Migration Service assessment — to categorise SQL by complexity: auto-convertible, needs minor modification, needs significant rewrite. Focus engineering time on the "needs significant rewrite" category first. These are the blocking items that compress the timeline if left to the end. For Teradata migrations specifically, BTEQ scripts and FastLoad/MultiLoad utilities have no direct equivalents and require application-layer re-engineering.
Redesign the data model for the cloud warehouse's execution model
Legacy warehouses optimised for row-based storage often have heavily normalised schemas with many small dimension tables joined at query time. Cloud-native columnar warehouses (Snowflake, BigQuery) perform better with wider, denormalised schemas — fewer joins, more pre-computed aggregations. Rebuilding the star schema with dbt at the transformation layer (staging → intermediate → mart) is the right architectural approach. Migrating the old schema as-is and optimising later is slower in aggregate than redesigning at migration time.
Implement FinOps controls before the first production workload moves
Snowflake and BigQuery pricing models are fundamentally different from on-premises or Redshift. Snowflake charges per virtual warehouse compute-second; BigQuery charges per bytes scanned. Without query governance, a poorly written analytical query can cost hundreds of dollars. Implement: Snowflake resource monitors and warehouse auto-suspend (60-second timeout), BigQuery authorised views to prevent full table scans in user-facing tools, per-project and per-user cost attribution labels, and budget alerts at 80% and 100% of monthly allocation before business users start running queries.
Decouple the BI tool cutover from the data migration
The most common source of migration delays is coupling the warehouse migration with a BI tool migration (Tableau to Looker, Business Objects to PowerBI). These are separate programs with separate risk profiles. Run the new warehouse in parallel with the legacy system during a validation period, during which both systems produce results for the same business queries. Only migrate BI connections after the validation period confirms result parity. This extends the migration duration by 4–8 weeks but prevents the most expensive failure mode: a BI tool cutover that fails and requires rollback to the legacy system.
Inventory completeness, SQL assessment before scoping, data model redesign for columnar execution, FinOps controls before go-live, and decoupled BI cutover — these five sequencing decisions consistently reduce the time-to-production and cost-overrun risk for enterprise data warehouse modernisation programs.
Applying this to a Teradata-to-Snowflake migration
A financial services client engaged Wolk Inc to assess a planned Teradata-to-Snowflake migration that had been scoped at 6 months. The initial scope was based on a table count (120 tables), not an inventory of transformation logic.
Wolk Inc conducted a full inventory: 120 tables, 340 views, 95 stored procedures (many unused), 12 BTEQ scripts, and 28 Tableau workbooks consuming the warehouse. The SQL compatibility assessment flagged 34 stored procedures as requiring significant rewrites due to Teradata-specific syntax. The BI workbooks had 15 Teradata-specific SQL clauses that would fail silently on Snowflake.
We restructured the migration plan to address the 34 rewrite-required stored procedures in weeks 2–8 (before any data migration began), implement Snowflake resource monitors and auto-suspend policies in week 3, rebuild the data model in dbt with a star schema redesigned for columnar performance, and decouple the Tableau cutover into a separate 8-week workstream after warehouse go-live. The migration completed in 9 months — longer than the original 6-month estimate, but shorter than the 14-month outcome the original plan would likely have produced.
Actionable takeaways
- Complete the inventory before scoping — table count alone underestimates migration effort by 2–3x in most enterprise programs
- Run the SQL compatibility assessment before committing to a timeline — the manual-rewrite category is the critical path item
- Redesign the data model for columnar execution at migration time, not after go-live
- Implement Snowflake resource monitors and BigQuery cost controls before business users run queries on the new platform
- Decouple the BI tool cutover from the data migration — they are separate programs with separate risk profiles
Priya Nair
AI/ML Engineering Lead · Wolk Inc
Leads AI and ML delivery at Wolk Inc, focused on moving models from prototype to production for FinTech, healthcare, and analytics SaaS clients.
Planning a data warehouse modernisation?
Wolk Inc designs and executes data warehouse migrations to Snowflake, BigQuery, and Redshift — including SQL assessment, dbt transformation layer design, FinOps governance, and BI tool cutover planning. Book a 30-minute call to assess your migration scope before committing to a timeline.
Wolk Inc is a 2021-founded senior-engineer-only DevOps, Cloud, AI and Cybersecurity consulting firm serving US and Canadian enterprises.