Comprehensive Data Warehouse Migrations to Databricks SQL

Summary

• Accelerated Migration Process: Databricks provides a comprehensive migration solution achieving 50% faster data warehouse migrations than traditional methods.
• Four-Stage Framework: The migration approach includes Discovery, Assessment, Conversion, and Reconciliation stages to systematically transform legacy systems.
• Automated Code Transpilation: Advanced code conversion tools streamline the transformation of legacy queries and schemas into Databricks SQL.

Comprehensive Data Warehouse Migrations to Databricks SQL

Talk by Simon Eligulashvili, Lead Specialist Solutions Architect, Databricks ; Sundar Shankar, Sr Specialist Solutions Architect, Databricks
Databricks has a free, comprehensive solution for migrating legacy data warehouses from a wide range of source systems. See how we accelerate migrations from legacy data warehouses to Databricks SQL, achieving 50% faster migration than traditional methods. We'll cover the tool’s automated migration process: Discovery: Source system profiling Assessment: Legacy code analysis Conversion: Advanced code transpilation Reconciliation: Data validation This comprehensive approach increases the predictability of migration projects, allowing businesses to plan and execute migrations with greater confidence.
Here's more to explore:
The best data warehouse is a lakehouse: https://www.databricks.com/product/databricks-sql
How to migrate legacy data warehouses: https://www.databricks.com/resources/ebook/migrate-your-legacy-data-warehouse-databricks
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

• 0:00 Introduction to speakers
• 1:26 Why migrate to Databricks
• 2:42 Challenges of migration
• 4:54 Introducing Lakebridge
• 5:52 The profiler tool
• 9:01 The analyzer tool
• 17:39 The converter tool
• 24:04 Converter demonstrations
• 28:24 The reconciliation tool
• 32:38 Roadmap and conclusions

FAQs

What are the four stages of Databricks' migration framework?

The framework consists of: Discovery (profiling the source system), Assessment (analyzing legacy code), Conversion (using advanced transpilation to transform code), and Reconciliation (validating data integrity). This systematic approach increases predictability and reduces migration risk.

How much faster is migration to Databricks compared to traditional methods?

Databricks accelerates legacy data warehouse migrations by 50% compared to traditional approaches. This faster timeline helps businesses reduce disruption and reach production more quickly while maintaining data quality and reliability throughout the migration journey.

How can organizations migrate from legacy data warehouses?

Use Databricks' four-stage migration framework: discover your source schema and queries, assess optimization opportunities and dependencies, implement automated code transpilation to convert SQL and ETL logic, then validate data quality. This structured approach on the Databricks Data and AI platform reduces migration risk and accelerates time-to-value.

How do I build a business case for migrating to a modern lakehouse?

Calculate total cost of ownership including compute, storage, and maintenance on the legacy system versus the Databricks Data and AI platform. Factor in accelerated analytics capabilities, AI/ML integration, and reduced time-to-insight. Databricks' track record shows successful migrations deliver both cost savings and performance improvements within 6-12 months.

Which platforms provide migration accelerators?

The Databricks Data and AI platform offers comprehensive migration acceleration through Lakebridge, pre-built connectors for major data warehouses, automated schema conversion, and proven migration patterns. Databricks has successfully migrated thousands of organizations from legacy systems.

Full transcript

0:00 Introduction to speakers

Okay. All right. Uh thank you all for uh joining us today. So uh today you're going to hear us hear about how to migrate to data bricks from your existing uh data warehouse systems. I'm myself sundur have close to 15 years of experience in the data and a space. I've been uh having privilege of working in various roles within data bricks. I've been a brickser since 2020 and uh somebody one you know if comes up and ask me what do I do daily I say I passionately solve uh the migration challenges to data bricks.
Hi everyone, I'm, a lead specialist solutions architect at Data Bricks. Uh I joined Data Bricks in January 2025 through acquisition of Blade uh where I was a co-founder and uh architect responsible for the majority of the accelerators that we've built. Um my experience spans for about 30 years. uh but in the last 15 years I've been focusing mainly on building accelerators for data warehousing and uh code migrations. Thank you.
Um why migrate your EDW to data bricks right um if you on when you're working on our on premise systems uh generally those are when they're logged in you don't get the elastic uh capabilities which which the cloud uh warehouses offer um it's difficult to onboard new use cases as well so data bricks is an open platform uh it is easy to operate, lowers the cost. We have uh the wonderful governance tool called Unity catalog.

1:26 Why migrate to Databricks

Uh we have over thou 10,000 plus enterprises already adopting uh to data brick SQL and there have been performance benchmarks over the years which which shows how good data brick SQL is uh when compared to other uh cloud warehouses. And today's session will mostly fall under the fourth pillar. How data bricks is today's talk especially is going to talk stress on risk assurance through tools which can help you migrate to data bricks in a predictable fashion.
So um I was actually when I was putting this slide together I was uh trying to explain how why migrations are difficult uh why migrating any or shutting down when I say migrating not not just coexisting but shutting down your legacy systems are difficult. So Gartner actually put up a very interesting observation that uh all most of the migrations are like an heart transplant. Uh I was actually had a very good joke because I'm from India.

2:42 Challenges of migration

Uh uh but I don't see anybody probably will not be able to follow. But if if you ever be to come to Bangalore um there's a famous uh bridge um in Koramangla which is the party hub of Bangalore uh called IPura flyover they are uh we we jokingly say that it's like stone edge it doesn't go anywhere so most of the migrations actually end up being like a stone edge right there not progressing so uh anyways that was uh that was a joke uh actually but uh uh The other uh other interesting uh challenge uh we see uh when you are migrating from uh your existing system is your legacy code and uh is there any archaeologist in the room?
Okay. So uh migration migrating like legacy code is like archaeology right? So you don't know uh who has written it, when it was written and uh what is the business logic around it. It's very difficult to give business users uh confidence that oh you migrated but does it really produce the same results? Does it really um uh ensure your business operations are not going to be impacted after moving to your platform? There is no guarantee or yeah predictable pathway to it and here we are going to talk about fast and predictable uh migrations to datab bricks.

4:54 Introducing Lakebridge

So introducing the datab bricks integrated solution. What what are they? They are Remorph which was our inhouse tool which started we started working on a year and a half ago and then six months ago and his team joined us uh through the acquisition of Blade bridge and today we have integrated the solution into called Lakebridge. Now after using leg brbridge automation tooling um hopefully there are no doctors uh here I'm not going to offend any doctors uh unlike any you know medical procedures the risk of migration reduces by utilizing these accelerators now before we dive more deep into uh the actual tool and how it behaves I would like to touch upon the pillars of any migration and why it is important.
Right? So the first uh concept uh is on the profiler. This is where you understand your existing uh ecosystem of your like your legacy application. What what is it about? You get deep insights on it. And then the analyzer portion which talks about your code analysis, your complexity planning your migration project and then finally the tip of the iceberg which is your code converters uh which helps you convert your code to SQL SQL scripting uh your workflows as well as uh running and SQL validator.

5:52 The profiler tool

And then finally uh this is where most of the projects derail especially with uh longer parallel runs to en you know ensure confidence to business. So we have a tool called reconciliation which will help you uh in solving that. So all these four pillars is what you get when you work with lick bridge. It's an CLA based tool currently. It's open source. Uh it's platform agnostic and it's easily extensible. I will touch on the extensibility bit more when we talk about converters.
Why I say that is extensible. Now let's deep dive into the pillars. So what is the first one is what is the profiler right? I think everybody is uh u aware of this famous quote, right? If you have six hours to cut down a tree, you spend the first four hours sharpening the axe. Everybody knows that. But in order to sharpen the axe, you need to have the right tools. What are the right tools? Profiling is the right tool for you.
What does profiler tell you? It actually categorizes your workloads uh based on your query history. It understands your utilization patterns. uh uh it it provides you insights and access patterns. Why are these important? These ensure to unear your ungoverned assets. For example, there might be shadow IT running. As a platform owner, you might not even know that there is certain components around which which are not we are not aware of.
So these uh tools will help you in understanding those uh or unearthing those hidden workloads. Now what do you what do you get like okay I have unearthed it what is what is hidden for me. So uh it's important to uh also understand why data bricks what will be the cost of operations when I move to data bricks. Yeah, typically this is an example I come up with. It uh talks about your current spend, current breakground.
What is uh if you want to do a BI only migration or you want to do complete ETL migration, how do I look forward? What will be the potential cost of migrating over to data bricks? Now I'll hand it over to talk more about analyzer. Thank you. All right, analyzer. So what is analyzer? Um, analyzer is one of the uh critical uh tools that you'll have at your disposal during code migration project. Um, analyzer as the name implies analyzes u actually code the code that you're migrating.

9:01 The analyzer tool

Uh, that includes the store procedures, ETL pipelines, uh, table DDLs, view DDLs, uh, and so on. So basically it looks at the actual code or metadata whether it's ETL tool you're migrating or SQL code. Um just to draw a distinction between what the analyzer does and what the profiler does. Um profiler looks at the runtime statistics of your current uh legacy of your legacy environment whereas analyzer looks specifically at the code at the artifacts that you need to migrate.
Sorry. All right. Um now why do we need to run the analyzer? Analyzer is not necessarily a requirement to be executed during your migration project but it will help you a lot when you when you run it. So analyzer will produce the um the estimates for it will help you estimate your code migration effort. So it will look at the code, it will uncover the details that perhaps you've forgotten because these projects have been developed for many years, sometimes even decades by many teams.
So it's it's it's very usual, very common not to know exactly what is in your code. So these are some of the things that the analyzer will will help you uncover. Um better analysis, better understanding of your code will lead to more precise estimates. you'll be able to more precisely estimate your cost and timelines, be able to staff your teams correctly and at the right time. Um, one of the other things that analyzer will bring uh to the table is the ability to u determine interdependencies.
Uh, your systems do not work in an isolation. They work in an ecosystem and uh of your organization. They're all interconnected. So analyzer will tell you uh what the dependence are on the code uh that you are migrating, what types of other systems your code is connecting to. For example, your TL pipelines might be connecting to web services or making remote calls and so on or connecting to 10 different uh SQL platforms.
So these are some of the things that the analyst will tell you. And again the uh the goal of the analyzer to get you perhaps more familiar familiarize or perhaps to remind you what your code does exactly. Um analyzer will also uncover some of the patterns um that your code may represent. So for example, if you're if you're converting NTL codebase, analyzer will tell you the distribution of the types of components in your codebase and that will actually help you with um stage out a large project.
Uh uh break it out into into phases basically use a divide and conquer approach perhaps. Um and so ultimately using the analy will will lead you to better planning to better execution and um to much greater risk mitigation as opposed to going into a project without fully understanding what lies in your code. Um these are some of the statistics that the analyzer will will help um bring uh bring up to the surface. So when you run the analyzer whe whether it's being executed on your ETL uh code base or on your SQL code base or perhaps on some scripting tools analyzer will first and for foremost we'll will take a broader picture uh broader look at your code and we'll estimate um how many complex how many medium complex how many low complexity jobs or pipelines you have that will give you an idea of you know how complex your your project is and uh analyze uses specific algorithms to identify the complexity which we publish um and not and by the way these metrics are taken from um from a real life informatica project.
So next graph there is um breakdown uh of artifacts by the by the types for example mappings workflows maplets workless if you worked with informatic you probably are familiar with this uh the same will be done for other ETL tools for SQL code bases um the analyze will um will give the breakdown of the types of SQL statements uh that it finds whether you're doing a lot of updates or or merges or perhaps you are using materialized tables and so um these are important metrics because they will drive the strategy for your um for your code migration efforts.
So for example, if the number of mappings is almost the same as the number of workflows, then the question arises why why do I need workflows? Why do I need to migrate workflows? And then perhaps the scope of your migration project will drop. um the analyze will give you a distribution of the types of components that are or types of SQL statements that are used within the code and that will actually help you stage your migration project um smarter.
So for example, if if if uh only a small part of your um code uses uh complex or u exotic components uh in your ETL tooling, perhaps you want to leave it for later and concentrate uh initially on on simpler jobs just to get the project started and get the teams rolling. Uh so these are all the statistics that um actually not all but uh most important statistics that the analyze will will produce. there are a lot a lot more uh drill downs that the analyzer will will give you.
So basically analyze will will give you all a lot of ammunition if not all to help you navigate through a very complex conversion project. Okay, so these are some of the sources that the analyzer can penetrate. Um one thing that's worth mentioning is that uh the analyzer will never touch your production system. it export it expects the code to be exported out into a folder accessible to it. The analyzer can run on on Windows, Linux and.
Um so once you export the code the anal the analyzer traverses the code. It takes u usually a few minutes to traverse the code. So in a matter of minutes you can get something that manual would have taken uh days sometimes weeks to analyze and to execute. Um the analyzer will sorry the so the analyzer supports about um 30 plus different types of technologies whether it's SQL ser sorry SQL based platforms or ETL based platforms um along with some other scripting languages like pig for example um it's important to make sure that you analyze everything that you are planning to convert because again your uh informatic environment or date stage environment uh does not live in isolation.
There are probably some other touch points that you want to analyze as well. So it's important just to feed everything to the analyzer to get the uh overall picture of uh how complex your implementation will be. All right. And these are just um was supposed to be an animation but didn't work. Um so these are the source systems that the analyzer can penetrate. The way uh you work the analyzer at the command line you execute the command um datab bricks labs lakebridge analyze and the analyzer will prompt you for uh one of the 33 options as far as your source technologies are concerned.
You choose one. Then you provide your source file or source folder and your report um file name and the analyzer will take a few minutes. Occasionally on large codebase it may take a few hours and it will produce a report that then you can use and um and uh and start planning out your conversion. So again, analyzer is a very important step in your migration journey and it's a I would say that it's it's a prerequisite uh to making a conversion project a success.
So now let's move on to the converter. So converter as the name implies is is a tool responsible for uh translating the legacy source code into datab bricks native format. Um and once in a while we do get asked the question whether this step is worth automating and the answer is always yes it is because you're usually dealing with a huge code base usually um you're dealing with thousands hundreds but usually thousands of scripts thousands of ETL jobs sometimes different um technologies in your stack that you need to uh convert um if this were a person converting each script at the time.

17:39 The converter tool

It would take uh a few hours probably or maybe a few minutes or a few hours to convert each script. With the autom with the automation, you get the benefit of getting code converted each artifact, you know, converted within within within seconds. Uh typically the converter as well as the analyzer uh they look for patterns. So um the results that the converter produces is um is is predictable. uh uh is consistent because it's a machine implementing the conversion over and over again.
Um the converter will also give you the benefit of scaling out the large uh conversion project. So you you can um break out your large implementation into multiple phases into you can have multiple teams running conversions in parallel and uh again the converter will will consistently output similar patterns that are data bricks native. Um besides the speed and consistency uh the converter can also be uh adjusted or configured to match your organizational or or project uh requirements.
So uh converter internally runs on a set of configuration files which uh from which you can inherit and you can basically specify your your own um requirements um in terms of uh naming conventions in terms of code styling and so on. So this is something that um some of our some of our users uh have been using. Um obviously with uh as with any automation uh uh it's it's the it's the repetitive t uh tasks that you uh will um automate as far as the conversions is concerned.
So you can expect the same result from the converter over and over again uh as opposed to having a team of let's say 50 or 100 people converting code manually where it becomes very hard to manage uh as far as the output patterns are concerned and uh very importantly the automation will free up the valuable engineering time uh so the engineers can concentrate on other uh important tasks such as uh data validation, testing and optimization.
So um I remember I had mentioned uh Lakebridge uh is extensible. Um what do I mean? Um I was told this was a beginner talk not to get into details but uh I couldn't uh uh stop myself. So sorry if I'm going to be nerding out here a little bit. Um how many of you use IDs day-to-day and can you show an answer? Oh that's a good good amount of audience. Perfect. So when you use ids you might have seen autocomp completion refactoring when you use your ID right so those are implemented uh by something called language server protocol where your ids are the client and your uh language server is the protocol uh it uses JSON RPC to do the refactoring of uh uh code based on the input you type it so we use something similar to uh uh in lebridge is the LSP client and we have have a specified spec uh through which you can build your own converters uh and we can plug that in to Lakebridge and once you plug that into Lakebridge you can actually automate your uh last mile or you have a better converter because converters honestly it's not a it's not a magic band right it cannot get you say 100% of conversion every time there will always be an acceleration to your program but not 100% converted.
So uh the your converter will you can plug in your converter uh and what you will get is the validator. So right now we have the SQL validator which takes the output of the converter and validates this uh the SQL query which generates. Now in future you will also get get it for other sources like for workflows for u uh a assisted uh validation so that you can get your code to a much higher degree of automation. So that's a little bit of uh geeking out about Lakebridge LSP server.
Uh and I'll hand it. Thank you. All right, back to the converter. Um so converter supports u migrations from multiple types of technologies uh ETL and SQL alike. Uh as far as um SQL platforms are concerned, we uh we support the usual suspects Terod data, Oracle, Snowflake, SQL Server, uh we support multiple Informatica um platforms such as Infocloud and Power Center date stage and there's more coming. Uh as far as the conversion process is concerned and the conversion output is concerned, um the converter will produce uh data bricks native code. uh when it converts um code from ETL technologies, it it usually outputs um notebooks uh that are native to data bricks with either PISpark or Spark SQL dialect and when it converts uh SQL code, it will it will convert that to either SQL scripting or SQL scripting procedures.
It can also handle table DDLs, view DDLs and so on. When it comes to orchestration or orchestrational components such as Informaticus workflows or uh data stages uh sequences, the converter will convert those to native data bricks workflows. And now I'd like to move on to the demo of the converter. Uh so this is a sample SQL store procedure uh with a uh with a header with a with a with a procedure signature a few declarations um and a few uh DML variables sorry a few DML statements with a with a while loop and so on.

24:04 Converter demonstrations

So the way you execute the converter is you run the command hope everyone can see it clearly you run the command data bricks labs uh lakebridgeidge install transpile. So the install transpile directive will either uh install the converter from the scratch in case it has not been installed or it will take you through a set of prompts. So the first prompt will be what type of technology you want to uh convert and you get you get a set of uh nine uh choices.
So in this case uh I've chosen u option number three which is SQL server. And uh after I do that there there a couple of other prompts that the converter will will take you through which normally I I just hit hit enter for default. And then at the end uh we execute the command data bricks labs uh leg transpile and we provide either a source file or source folder as the input and the output folder um as a second parameter.
Once we do that, the converter will take a few seconds and it will um output the statistics and create the output file and the output file. So now there are two files um aligned side to side. Um on on the left hand side you have the original file and on the right hand side you have the converted data brick SQL uh scripting file. So u what the converter will first detect is whether we're converting store procedures or uh perhaps table DDLs or view of view definitions.
In case of store procedures it will alter the um the signature the syntax of the signature of the store procedure it will make it data bricks compatible. It will also go through all the statements making them data brick compatible. So in case there are statements that need to be completely altered, for example, instead of declaring a variable and assigning a default, the converter may choose to first declare the variable and then later on assign uh the initial value to the um uh to that variable.
It it will also alter the syntax of control flow statements like while loops, for loops, etc. just to adhere to data bricks uh SQL scripting specifications. Now one other important thing that the converter well one of the other important things that the SQL converter will do it will also touch on every function call uh that is not compatible with data bricks and it will alter the function name and potentially the function signature of um of the calls that uh it finds it in code.
So for example in this uh stop procedure we have char index uh being called with argument one and argument two. That's the that's the green highlight. And then uh the converter chose to convert it to in uh call where it swapped the arguments and just because that's how we adhere to data bricks u SQL convention. And now really quick um uh the demo of Informatica to data bricks notebook. So this is a sample Informatica um Informatica mapping. um it consists of several types of components such as source qualifier um filter expressions aggregator and targets.
So when we run the converter again the same idea we run install transpile command we get the choice of technologies that we want to convert from. uh in this case I choose um option number two informatica power center and the same process kicks in a similar command except that I'm providing the XML file as my input as opposed to SQL file and then the converter will actually generate the file that's going to be importable into data bricks as a notebook it will separate all the commands into separate cells so it's easier to debug uh it's easier to manage this script Okay, back to you sir.

28:24 The reconciliation tool

Thank you. So now that you have seen sharpen your axe uh by running profiler analyzer and then you actually run the converter but your migration project is not done until you decommission your existing system. Uh I'm that is basically plugging out your system from your network. So happy to be part of that. Uh I love to do that. So if you if you have if you are using this and plugging out any system please call me. I'll fly in and uh do the unplugging myself.
Thank you. Uh let me talk about reconcile. So reconcile parts forms the important pillar to do that. The business needs are very clear. Your users need to have confidence that data and the code converted and the uh output it's producing is accurate. It also uh reduces your engineering hours like your your people can work on more innovation or how do we value bring out value out of the data you have moved on. That's why you actually move to the new platform.
So you can actually work on real use cases post uh migration and technically how do we achieve it? Uh we you need to ensure the schema the row column are matching. uh it has to be fast and accurate so that you can repeat it over the time understand and explain it easier and also to be able to operate on the initial large set of load so that it has to be performant and then also on the incremental refresh which is going to be on the subset of data.
So okay how does it work right? If we categorize this into three buckets, right? Okay, you want to do say I'm a c I want to do a validation. So I want to first ensure all the data types are matching. Then I will probably ensure none of the keys uh are missing. Right? That's my row. And then once I validate my keys are missing, I would ensure the exact column value is matching. That's your column. Now you can do that in three ways.
You can the column column validation. You can actually do an exact match think that hey I had ABC I need to have ABC especially on the numeric values right uh you want to do exact match or sometimes you might choose hey I'm okay to have certain differences because of timing issues or uh explainable uh changes you can do it at aggregates you can set up uh thresholds as well so okay this is my acceptable ranges I want to re you know allow the reconcile to fail you can do both percentages as well as absolute values.
Uh then how do you do it? Filters. Uh you can have transformation which is like you have a business rule which is changed or you added a new data stewardship on your data which mapped your column value to a different value. You can supply those uh using transformations. You renamed the column. You can use column mapping. Then you'll have uh audit control uh columns which is your insert date, update date, time stamp which which can be part of your include or exclude list.
And then you can also aggregate by key. So you don't you don't need to just run the aggregation. You can also aggregate by a key particular key so that you can get the reports matched across two systems. Now um how do you run it? Data bricks labs bridge configure reconcile. It'll ask you a bunch of questions and prompts. You just configure them. This is the out how the output looks like and then this is the dashboard you get.
So you can actually say what what is the behavior over a days what is my source type what is my uh the actual details will be presented to you as a pivot table. So you can actually go and the developers can actually go and see what is the exact record which is mismatching. So this is what you provide. So and then so this is not built in isolation. Uh we have uh our wonderful customers who've been using uh this tool last year in this very uh uh data summit.
Uh doash actually presented a talk about their internal tool called transaxle which uses link bridge uh which is what we are calling it now to build their interoperable uh system. Then what is coming up uh sneak peek into uh what is coming up next which is an UI through which you'll be able to curate your migration in a much more uh effective way. uh you might you'll be able to convert your code move your data reconcile also the AI assistant which will help you you do verify and uh validate your data and then last but not the least uh I think uh there is an uh term called what is my conversion accuracy.

32:38 Roadmap and conclusions

So we are working on something uh to help you decide okay how what what will be the right um way to measure your conversion accuracy and your program uh acceleration rather. So announcement, tune into the DBSQL keynote on uh Thursday. You'll have some exciting announcements coming up and then where to find more information. Uh this is coming through. So please scan the QR code. The more information is getting into the docs as we speak.
So uh after this week, you'll probably have a stable uh doc and the page with all the necessary information on how to use this tool. Please uh uh save this and uh go back and try this out uh from next week. Um and we have five more minutes. I'm happy to take any questions.

Learn more about the Databricks Data and AI platform.