What we learned consolidating data from several platforms onto a single governed Databricks Lakehouse for a global leisure client.
After 20 years of growth through organic expansion and acquisition, a global leisure and attractions business had its data spread across Microsoft Fabric, Azure-hosted databases, Google BigQuery and a growing Databricks estate. Reports drew on several platforms at once, core metrics existed in competing versions, and duplicated processing pushed up storage and compute costs. Our team at Kubrick recommended consolidating analytics on Databricks, with Power BI kept for reporting.
We made a deliberate decision early on: this would not be a like-for-like migration.
Copying each workload across would have been the quickest route, but it would also have carried every one of those problems onto the new platform. Instead, we treated the migration as an opportunity to improve the estate: simplifying the architecture, removing duplicated logic and processing, and building in governance as each workload moved.
The evidence backed that decision. When we traced the business’s priority reports back to source, more than half needed architectural cleanup before they were ready to migrate anyway.
1. Trace lineage from the reports back to source
An inventory of platforms and tables shows what exists, but not what matters. We started from the other end: the Power BI reports the business relies on most. Our AI-assisted lineage scanner analyzed each platform in scope and traced every attribute in those reports through each transformation and platform hop to its system of record.
The trace exposed four problems:
- Calculations and business rules were embedded in Power BI reports and semantic models, so the same metric could be calculated differently in different places.
- Around a fifth of the lineage on Databricks traced back to static reference files in SharePoint or Azure Data Lake Storage, rather than to a system of record.
- Critical reporting data passed through several platforms before reaching a report, with processing repeated at each step.
- Common dimensions were rebuilt separately by different functions.
Each finding pointed to a specific improvement. Together, they turned a general wish to simplify the estate into a prioritized backlog.
2. Measure how closely the Lakehouse follows the medallion pattern
The business already had a substantial Databricks estate, so we also checked how well it followed the medallion pattern. Classifying every table-to-table relationship by the layers it connects gave a clear picture:
- About 72% were ordered transitions: Bronze to Silver, or Silver to Gold.
- About 11% needed review: Bronze tables consumed directly, paths that skip Silver, and Gold tables built on other Gold tables. One example was a reference mapping table read straight from Bronze to enrich downstream data.
- About 16% had no captured upstream. These were generally legitimate: backfills, forecast training tables, point-in-time KPI snapshots and tables built incrementally from their own previous state.
You can run a similar check on your own workspace. Unity Catalog records lineage in the system.access.table_lineage system table, so if your layer names appear in catalog or schema names, one query classifies every relationship:
Gotcha: Unity Catalog lineage only sees what runs on Databricks. Logic in Power BI, spreadsheets and other platforms need separate tooling, which is why the cross-platform trace in practice 1 mattered. The system table keeps a rolling year of history, so choose a window that covers your least frequent schedules.
3. Decide what to fix, what to move and what to retire
A common warning is not to fix technical debt during a migration, because scope grows. The risk is real. Here, though, the cleanup was a prerequisite for migrating, not extra scope: reports flagged by the trace weren’t ready to move until it was done.
Scope was controlled in three ways:
- Classify every asset. Each one was marked strategic, transitional or retire, and assets marked retire weren’t migrated at all.
- Fit the treatment to the workload. The default was a native rebuild. Where only high-value parts such as ingestion needed modernizing, the workload got a hybrid approach. Lift-and-shift was used where speed and low risk mattered most, such as preserving historical data before a platform was retired.
- Prioritize data products. They were ranked by value, effort, benefit across the business, and how much they would let the business decommission.
4. Rebuild to a standard, then prove parity before retiring anything
Rebuilt workloads followed one standard, so every move also fixed a known problem:
| Before | After | Why it matters |
| Business logic inside Power BI reports and semantic models | Calculations and rules in governed Gold data products | One definition per metric, reused by every report |
| Reference data in spreadsheets and SharePoint files | Governed reference datasets with named owners | Quality checks, auditability and clear ownership |
| Reporting data hopping across several platforms | A single governed copy on Databricks | Fewer pipelines to run, monitor and pay for |
| The same dimensions and semantic models rebuilt by each function | Conformed dimensions, with shared semantic models on the Gold layer | Consistent answers across functions |
| Bronze read directly, Silver skipped, Gold built on Gold | Every path runs Bronze → Silver → Gold → reporting | Quality rules apply once, and lineage stays readable |
| Legacy, black-box ingestion | Standard Databricks-native ingestion, including change data capture | Supportable, incremental loads |
| Temporary and test assets in production paths | Naming standards, testing and CI/CD across all assets | Production holds only what is meant to be there |
Two standards go beyond the usual medallion guidance. Gold uses materialized views to pre-aggregate the measures reports actually use, which speeds up reporting and cuts compute cost. And personal data is protected with Unity Catalog column masks from the moment it lands.
Every rebuilt workload is validated at each layer before it is promoted: completeness and integrity checks first, then parity checks that compare business metrics such as tickets, visits and spend with the legacy platform. Where logic has been deliberately corrected, exact parity is the wrong target. The difference should be explained, documented and signed off by the data owner, or the corrected numbers will be mistaken for a migration defect.
A legacy asset is retired only once parity is proven, in an order set by cost and dependency so that savings arrive early. For automating reconciliation at scale, the Databricks Community post Speed up data warehouse migration validation covers the mechanics in depth.
Results and lessons
The most valuable improvements depend on ownership as much as engineering. Moving logic out of reports and spreadsheets only sticks when each data product and each source has a named business owner. If we ran the discovery again, we would put those ownership questions to executive sponsors in the first weeks, because they shape the roadmap as much as the architecture does.
If you have used lineage to plan a migration of your own, we would be glad to hear what worked in the comments.
