A data warehouse to data lake migration moves analytical data and pipelines from a traditional warehouse to low-cost cloud storage, usually organised as a lakehouse: open table formats such as Delta Lake or Apache Iceberg, layered zones for raw, cleaned and business-ready data, a catalog with access policies and lineage, and compute engines for SQL, data engineering and machine learning. Done well, it lowers storage cost, handles unstructured data and supports AI. Done badly, it creates a “data swamp” of ungoverned files. A reliable plan inventories workloads, designs governance first, migrates history and incremental changes, converts ETL, rebuilds the semantic layer, reconciles results and runs in parallel before cutover.
Key takeaways
- Migrate to a governed lakehouse, not raw files, to avoid a data swamp.
- Design catalog, access policies and data quality before moving data.
- Migrate by business domain in waves, starting with high-value, low-complexity data.
- Rebuild the semantic layer so business reports keep the same definitions.
- Reconcile numbers automatically and run in parallel before cutover.
Why move from a data warehouse to a data lake
- Lower storage cost for large and growing history.
- Support for semi-structured and unstructured data, such as logs, documents and images.
- One copy of data for BI, data science and AI, rather than separate extracts.
- Open formats that reduce dependency on one engine.
- Elastic compute that scales separately from storage.
Not every warehouse should move; for a cost and fit comparison, see data warehouse vs data lake vs lakehouse.
The target: a governed lakehouse
Open table formats such as Delta Lake and Apache Iceberg add transactions, schema enforcement and time travel to files in object storage, giving warehouse-like reliability. The layered medallion architecture keeps raw and curated data separate. Microsoft’s cloud-scale analytics guidance covers governance and operating model; platform options include Microsoft Fabric and Databricks.
Step-by-step data warehouse to data lake migration plan
| Step | Key activities |
|---|---|
| 1. Assess | Inventory tables, ETL jobs, procedures, reports, users, SLAs and data volumes |
| 2. Design | Zones, table formats, catalog structure, access model, quality rules, naming |
| 3. Migrate history | Bulk export historical data to open formats in the raw zone |
| 4. Capture changes | Incremental or change data capture loads until cutover |
| 5. Convert ETL | Rewrite stored procedures and ETL as pipelines and transformations |
| 6. Rebuild the semantic layer | Business measures and models for BI tools |
| 7. Validate and cut over | Reconcile, run in parallel, repoint reports, decommission |
Related migrations: Synapse to Fabric and Snowflake to Databricks.
Testing and reconciliation
- Row counts and totals per table and period.
- Checksums or hash comparisons on key columns.
- Key business measures, such as revenue by month, compared between old and new reports.
- Automated data quality rules on every load; see data quality frameworks.
- Performance tests for critical dashboards and queries.
Pitfalls to avoid
- Data swamp: files without catalog, ownership or quality rules.
- Lift and shift of bad logic: copying procedures without understanding them.
- Losing business definitions: reports that no longer match because measures changed.
- Security gaps: broad storage access instead of table-level policies.
- Big-bang cutover: moving everything at once instead of in waves.
Common questions
What is the difference between migrating to a data lake and a lakehouse?
A plain data lake stores files; a lakehouse adds table formats, transactions, governance and SQL performance on top, so it can replace warehouse workloads reliably.
How long does a warehouse to data lake migration take?
Typically three to twelve months, depending on the number of tables, ETL complexity and reports, delivered in waves by domain.
Will our BI reports still work?
Yes, once the semantic layer is rebuilt and reports are repointed. Reconciling key measures before cutover ensures numbers match.
How do we avoid a data swamp?
Use a catalog, clear zones, ownership, access policies and automated quality checks from the first dataset.
Should we keep the warehouse?
Sometimes for a period, or for specific workloads. Many organisations retire it after the lakehouse proves itself.
Plan a governed move from warehouse to lakehouse
Share your warehouse, ETL and reporting landscape. Our data engineering team will design the target, plan the waves and reconcile every report before cutover.