Situation
A transportation, logistics and fleet-operations company ran its operations and finance reporting on SQL Server: shipment operations from its transportation management system, inventory from its warehouse management system, fleet and vehicle telemetry, and accounts receivable and payable. Years of business rules lived inside stored procedures, views and ETL jobs. Upstream systems changed their schemas without notice, duplicates crept into reporting, and there was no unified semantic layer for business metrics.
Leadership wanted AI on that data: agents, retrieval over company knowledge, and natural-language questions answered from governed tables. None of that was safe on an estate where no one could say with confidence how a number was produced.
Source platform and size
| Before | After |
|---|---|
| SQL Server: about 200 tables and about 5 TB across shipments, warehousing, fleet telemetry, receivables, payables and operations | Governed bronze, silver and gold Delta Lake architecture under Unity Catalog |
| Business logic hidden in stored procedures, views and ETL jobs | Metadata-driven pipelines in Python, PySpark and Delta Live Tables (now Lakeflow Spark Declarative Pipelines), with the logic made explicit |
| Schema drift and duplicates from upstream systems | Incremental ingestion with Delta MERGE and automated watermarks; deterministic deduplication in silver; source deletions handled in bronze and silver |
| No unified semantic layer, no AI-ready data | Conformed fleet and asset dimensions, a governed metrics layer in Unity Catalog, and Databricks Genie for natural-language questions |
| No systematic lineage or reconciliation | Pipeline logging, audit timestamps, execution status and lineage on every run; reconciliation across bronze, silver and gold |
Approach
We delivered the migration in about six months, in waves, each closed by a reconciliation cycle.
- Assess and reverse-engineer. We read the stored procedures, views and ETL to recover the business rules they encoded, including the ones nobody had written down, and designed the entity-relationship model for the new platform.
- Design. A bronze, silver and gold lakehouse governed by Unity Catalog: group-based access, row-filter policies on financial and operational datasets, and column masks on personal data.
- Rebuild. Metadata-driven, incremental pipelines: Delta MERGE with automated watermark management, deduplication with deterministic window functions in silver, and type 1 slowly changing dimensions with surrogate keys and source-aware natural keys. Ingestion-control columns never reach gold.
- Validate. Automated data-quality checks on every load (natural-key uniqueness, null keys), and every migrated table reconciled against the SQL Server output. When a table did not match, we traced the cause and settled it with the business: a code defect gets fixed, an undocumented rule gets written down.
- Operate and hand over. Git-based version control with Azure DevOps pull requests and automated deployment. Performance tuned with Liquid Clustering, Photon and adaptive query execution. Then documentation and handover to the client's engineering and analytics teams.
Outcome
| Measure | Result |
|---|---|
| Tables migrated | About 200, across shipments, warehousing, fleet, receivables, payables and operations |
| Reconciliation | 95% of tables matched on the first reconciliation run; the remaining 5% were resolved by clarifying business rules |
| Cutover | No downstream analytics broke |
| Now running on the new platform | AI agents, retrieval-augmented generation (RAG), natural-language questions to SQL through Databricks Genie on governed metrics, and secure self-service analytics for engineering, finance and operations |
| Data volume | About 5 TB |
| Reporting freshness | Daily before, hourly after |
| Manual reconciliation | Many hours of manual reconciliation work saved, now replaced by automated data-quality checks and reconciliation |
| Delivery | About 6 months, in waves aligned to reconciliation cycles |
What the client said
"Prasad modernized our SQL Server environment into Databricks with remarkable accuracy. The new Delta Lake pipelines are cleaner, faster, and far easier for our teams to work with."Data and analytics leader, transportation and logistics company
Why it matters for your migration
The hard part was never moving 200 tables. It was finding the rules hidden in stored procedures and proving that the new numbers match the old ones. That is why every Sunsprinkle wave reconciles every table before anything goes live, and why the AI work starts on data that can be traced to its source.
See the method on a public sample. Our reference migration runs the same five steps on Microsoft's WideWorldImporters warehouse, with every number reproducible.