SQL Server to Databricks Migration | Dateonic

Summary

A large retailer with hundreds of stores and several distribution centres ran its analytics on an on-premises SQL Server estate. Nightly ETL jobs regularly overran, seasonal peaks pushed the servers to their limits, and history was trimmed to keep the databases manageable — so year-over-year analysis was patchy at best.

The company was already on Azure, which made the destination obvious. Dateonic migrated the data into Azure Data Lake Storage, rebuilt the transformation logic on Databricks with a medallion architecture, and provisioned the whole environment — three workspaces, catalogs, jobs and permissions — through Terraform. Store and warehouse data is now processed in one governed lakehouse that scales through peak season instead of buckling under it.

The challenge

• Nightly ETL that didn’t fit the night. Batch windows overran into business hours, so reports were late exactly when they mattered most.
• Fixed capacity, variable demand. Hardware sized for an average day couldn’t absorb seasonal and promotional peaks.
• History was being deleted. Storage constraints meant older data was archived or dropped, limiting trend and seasonality analysis.
• Store and warehouse data lived apart. Inventory questions that spanned both required manual reconciliation.
• Fragile transformation logic. Business rules were embedded in stored procedures with no tests and no version control.

Our approach

Discovery and dependency mapping. We inventoried every source database, job and downstream report, then built a dependency map to determine migration order and identify what could be retired rather than moved.

Land first, refactor second. Raw data was landed into ADLS as-is, giving a stable, replayable foundation before any business logic was touched. This kept migration risk contained: the ingestion change and the transformation change never happened at the same time.

Parallel running. Old and new pipelines ran side by side and outputs were reconciled until the business owners signed off. Only then was the on-prem job switched off.

The solution

Medallion architecture.

Three workspaces. Dev, test and production are separated, with production writes executed by service principals rather than named users.

Terraform for everything. Workspaces, storage, catalogs, clusters, jobs and permissions are defined as code, so environments are consistent and rebuildable.

Full history in Delta. With cheap object storage underneath, the retailer keeps complete history instead of trimming it, and Delta time travel makes point-in-time analysis straightforward.

Results

• Batch windows no longer overrun. Elastic compute means peak-day volumes are handled by scaling the job, not by rescheduling the business.
• Store and warehouse data unified. Inventory, sales and replenishment are analyzed together in the gold layer.
• Complete history retained. Year-over-year and seasonality analysis is possible for the first time.
• On-prem estate decommissioned. The SQL Server footprint supporting analytics was retired after parallel running.
• Transformation logic under version control. Business rules are reviewable code, not undocumented stored procedures.
• A foundation for AI. Clean, historical, governed retail data is the prerequisite for demand forecasting and replenishment optimization.