Skip to main content

How to Migrate From Oracle to Databricks SQL

Summary

  • Oracle to Databricks migration: This video outlines proven strategies for migrating legacy Oracle data warehouses to the Databricks Data and AI platform, including data type and code conversion best practices.
  • Technical methodology and tooling: Learn methodologies for handling Oracle-specific challenges, choosing between CSV and PySpark approaches, and leveraging automation tools to reduce migration complexity and risk.
  • Real-world cost and performance gains: Discover customer case studies showing the benefits of migration including improved scalability, reduced costs and enhanced query performance on the Databricks Data and AI platform.

How to Migrate From Oracle to Databricks SQL

Watch: How to Migrate From Oracle to Databricks SQL
Talk by Laurent Léturgez, Sr. Specialist Solutions Architect, Databricks
Migrating your legacy Oracle data warehouse to the Databricks Data and AI platform can accelerate your data modernization journey. In this video, learn the top strategies for completing this data migration. We will cover data type conversion, basic to complex code conversions, validation and reconciliation best practices. Discover the pros and cons of using CSV files to PySpark or using pipelines to Databricks tables. See before-and-after architectures of customers who have migrated, and learn about the benefits they realized.
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

FAQs

What are the main challenges when migrating from Oracle to Databricks?

This video covers data type conversion, basic to complex code conversions, and validation strategies specific to Oracle. You'll learn how the Databricks Data and AI platform simplifies these challenges compared to Oracle's traditional RDBMS architecture.

Should I use CSV files or PySpark for migration?

This video discusses the pros and cons of both approaches and shows how to leverage pipelines to Databricks tables for optimal performance and governance on the Databricks Data and AI platform.

How do I build a business case for migrating off a legacy data warehouse to a lakehouse?

Building a business case for migrating from legacy data warehouses to a lakehouse requires demonstrating cost savings from reduced infrastructure, faster analytics, and expanded AI capabilities. This video shows how organizations can quantify ROI by comparing total cost of ownership, analyzing query performance improvements, and measuring time-to-insight gains when transitioning to the Databricks Data and AI platform. The migration also unlocks new use cases for AI and machine learning that legacy warehouses cannot support.

migrating from legacy data warehouse to modern platform

Migrating from legacy data warehouses to modern platforms like Databricks requires careful planning to ensure data consistency, minimize downtime, and preserve institutional knowledge about existing data structures. This video provides practical guidance on assessing compatibility, planning schema migrations, managing historical data, and validating results before cutover. The migration path from Oracle to Databricks SQL maintains familiar SQL syntax while providing lakehouse scalability and AI capabilities.

What is a data warehouse?

A data warehouse is a centralized repository of structured data organized to support business intelligence and analytics queries through optimized storage and fast retrieval. This video explains how traditional data warehouses like Oracle have been designed for SQL analytics but often lack the flexibility and scalability needed for modern AI, machine learning, and real-time analytics workloads. Modern lakehouse platforms like Databricks extend data warehouse capabilities with support for unstructured data and advanced analytics.

Full transcript

0:00 Introduction and motivation

Hello everyone. Uh welcome to this session on how to migrate from Oracle to data brick SQL. Um I will be your host for 40 minutes. Um before we start um quick forwardlooking statement uh to mention that everything that you will hear see uh is subject to change. uh you will be able also to complete this survey uh at the end of the of this presentation. Uh so first of all who am I? Uh I work at data bricks uh as a specialist solutions architect and I'm a specialized in all of these questions around data warehouse migrations.
Um I and prior to that I worked uh more than 15 years uh with Oracle database solutions uh in various uh roles uh DBA consultant architect uh and I also work in various industries. If you want to connect this is a QR code to my LinkedIn profile. So in this presentation uh we will be focused on three main uh aspects and um the major party of this uh of this presentation will be focused on part one and part two. Um first of all uh we will describe the importance of building a strategy and plan your migration and we will see how we can do that uh by using multiple approaches um and techniques.
Um then we will be focused on all of these questions and concerned about uh Oracle migrations and the specifics uh whether it's on data movements uh and uh conversion uh whether it's data types or code uh and finally uh we will wrap up this um presentation on showing the benefits of migrating from Oracle to data bricks. So first of all uh when you uh migrate your data warehouse from Oracle to data bricks uh Oracle is a legacy data warehouse.

2:06 Why migrate to Databricks

Um it's uh an expensive solution. Um and we at data bricks we are seeing more and more customers who want to to migrate and to move their workloads out of this uh Oracle and move them to data bricks. So when we are uh speaking about Oracle, we have uh two main types of solutions. We have this one that is based uh out of the and relying on the RDBMS solutions. So you have installed Oracle or Oracle clusters on standard clusters and you can also um have Oracle installed on Exadata uh appliances.
But for both of these solution, the first one as it is a solution based on an RDBMS uh scaling is some thing that is usually complex to do. Um especially if you have more and more data and more and more workloads to address. Uh and on the right hand side of this um uh slide um this Oracle Exodata is a solution that based on an appliance which is really expensive uh in terms of um material. So when we are uh speaking about uh moving uh from uh Oracle to uh data bricks, we are usually seeing this uh in the scope of moving data to scale up the yeah the capacity of your um data sets to to the cloud.
That's a good point. Uh and also to scale your ETL because of the previous uh elements that I presented just before. But it's also a new opportunity for you to do way more. The first of all the first of all of this uh opportunity is to enhance improve your business by adding new usage, new use cases. It's also an opportunity for you uh to um change uh your data architecture from uh legacy data models, Kimble, the data vault to uh the lakecast.
But even if we it's an opportunity to change these data models. You have also the you can keep these models uh within data bricks. No problem on that. Um it's also an opportunity to enrich um your uh data by adding new modern uh data sources. For example, if you want to add some uh Salesforce data coming from Salesforce for example uh or other data source which is quite a bit challenging uh for with Oracle. Um it will also help you to it's it will be also an opportunity for you u to enable new use cases based on predictive uh and ML use cases but also prescriptive analytics and prescriptive analytics will be um able to be done to be done by using genai capacity of data bricks and last but not least it's also an opportunity for you to remove all this technical depth that you acquired you you accumulated on this Oracle database.
Um and uh this one is very important when you uh think about migrating uh out of Oracle and more broadly uh data warehouse solutions. So, but before doing that um you will have to build a plan and you will have to build a strategy to migrate uh out of Oracle because yeah it's it's not something simple to do. Um and in terms of uh uh strate approaches uh at data bricks uh we are usually seeing three of them. The first one is the lift and shift approach.

5:59 Strategy and planning

What does it mean? Lift and shift means that you will lift and shift your use case from a solution to another one. You will not add new use cases, new usage. The architecture will remain the same and the ETL logic will remain the same with also will also remain the same. So it's a lift and shift. You move your solution, your use case, your architecture as it is to data bricks. At the opposite uh you have this uh modernization approach.
What does it mean modernizing to data bricks? Um it can mean that you will uh restart from scratch all um the the ingestion of your data the ETL process by and the architecture by uh adopting into the lakehouse and using spark and so on to to transform uh your uh logic. Uh but it can be also adding some new use case for example if you want to uh keep your data coming from Oracle and add some new usages. For example, if you want to uh implement ML or if you want to implement real-time decision making uh use case, it's the path to uh to choose.
And in between uh you can also lift and shift and then modernize two-step approach. Um and this one can be uh useful for example if you have a strict deadline in terms of license renewal. So you want to go fast to lift and shift and then you will have some new use case and it's also a path that you can consider depending on your needs depending on the your your timelines uh and what you want to achieve. So now we have this in mind.
Um we uh we we have to choose um a strategy to build the plan and usually uh every data warehouse they are hosting um uh descriptive analytics use cases and all of these use cases and because it's a data warehouse it will support decision making. Uh so what we uh usually recommend is to make first an inventory of all the business cases that lives into your data warehouse first of all and then the this business case will act as a the atomic unit of migration of the whole migration.
Um and it will also help you to define dependencies between them and then build the yeah to prioriti you will sorry you will be able to prioritize the the migration process by uh migrating the first use case and then the second one and so on and so forth. But for each uh business case that you will uh inventory um the other things that you have to do is to uh make another inventory for each business case. You will um yeah you you will make the inventory of the all the ETL process and the code logic that are associated with this business case as well as all the the data the Oracle database artifacts that you use for each of these business case.
It could be table, index, index types, materials, you and all the objects that you know, but also data types, whether they are built-in data types or userdefined data types. Um, stored procedures if you have them. Uh, and also very important external procedure. Um, we all know that something this is something that can exist in Oracle database. And uh making this inventory will help us to build the yeah the strategy first of all with the business case and then apply the migration strategy on top of this um objects.
So how to do that uh with Oracle? So at the top level uh we have the business case because it's our automic unit and this business case can live into a database or a pluggable database depending on your architecture and you will find all of this information in this table views uh in in the Oracle dictionary and for each of these business case you will probably have your data within a schema um that will uh And in this schema you will find different objects whether it's table indexes and so on.

10:24 Inventory and assessment

So all of this is um available in these tables uh or views and for each objects you will have some uh data types uh whether they are um builtin and for the built-in uh data types you will find them into the DBA tab columns and data types for userdefined data types. So this is this is for the Oracle artifacts. Then uh the ETL so ETL process can live directly into the within the database. Uh so it will take the form of store procedure functions or packages.
All the code is available in DBA source view or if you use an ex a third party ETL it will be in the uh in this solution it could be Informatica data stage ODI or else and to assess the complexity of all of these um uh artifacts at datab bricks uh you can use the profilers. So the profilers will help you to estimate the run rate once the workload will be migrated to datab bricks and datab bricks leg bridge um and a specific part of the leg bridge uh solution that is the analyzer that will helps you to estimate the cut complexity uh that lives uh either in um in Oracle uh installed procedure or uh externally if it's uh used in let's say data stage ODI then so remember we have this list of business case we've established the priority of all of these business case and we will not u decommission the the Oracle data warehouse as a single operation we will do that step by step and the step will be represented by the business cases that you will move from Oracle to data bricks And because you will have established this list of business case, you will be able to prioritize them and build priority list P 0, P1, P2.

12:36 Phased decommissioning

In the case of a lift and shift, you will take the existing uh business cases and you will migrate them one by one uh according this uh priority list. But if you have new use case you want to uh add during the migration process that will make a modernization process you will be able to uh add this new business case into your priority list depending on various uh parameters. Uh for example in this uh example the new business case one can be dependent on this uh uh first uh business case one.
So by doing that you will be able to maximize the ARI by defining the priority list. Okay. So you will deliver the highest value for your business as soon as possible. You will minimize risk because you will repeat the stuff um in a yeah in kind of industry process. You will migrate everything the same way. Um and you will also minimize the time to migrate because you will apply on top of this uh each of these uh business case will be migrated to the to data bricks following the same uh migration strategy.
We recommend to use you to use the same um migration methodology because it will make this migration process something repeatable, some kind of industry process that will give you confidence on the way to to migrate your uh assets from Oracle to data bricks. So in terms of migration strategy, I did at data bricks we have two main uh migration strategies. We named them uh injection and ETL first and BI first. So what does it mean?

15:06 Migration methodologies

So first of all the injection in ETL first. So this one can be recommended for example if you want to modernize from scratch you will you will recreate all the injection the DTL process and so on. Or if you want to use something that is natural to migrate because you will open up a new branch here from data sources, you will uh add them into the lakehouse. Then you will import the integration layer that lives within your data model for example as an initial load uh in in the bronze area.
Then you will recreate you will migrate your ETL process to create this new integration layer directly into the silver layer and at the end you will create the serving layer the data mount that you will expose to your end user. you will build the the UAT, you will test it from uh a business perspective as well as technical uh validation and this technical validation is usually usually what we name the data reconciliation process and it's done at the end but also during the creation of this uh layer.
And what does it mean data reconciliation? We will we will check that the data created is the same as uh on the original uh on the source platform. We will validate some indicators like aggregates uh uh averages and so on to validate that we have the same consistency and of course at the end we will validate that for a business perspective we have also the same consistency and all when all of these tests will be uh done we will be able to decommission the uh the legacy platform and use data bricks as it is.
So this one as I mentioned is perfect if you want to be focused on the most complex part of your migration which is which is usually the ETL part. But on the other hand you have also the BI first uh methodology. The BI first methodology is roughly the same but just a little bit different because we will migrate first the data mounts to datab bricks as a source. We will copy this data source as it is from the bronze directly to the gold layer and then we will be able to present this data to our business lines and say for example if you want to implement Genie or if you want to implement data products on top of your uh uh data mods um to to and this could be part of the use case you will provide this value directly to the final users at the beginning of the migration process.
Okay, because the previous one is something that could take some time to bring the value directly to your customer while you will uh spend time on migrating the ETL. Here it's not the case. But once the data mod has been migrated to data bricks, we will uh uh do the same as previously. We will create the data source. We will make the initial load, create the integration layer, build the data mods, and once we will be at this stage, we will compare our data mods.
Are they the same? Yes. So, if they are the same, that's a good point. Are they delivering the same um business logic? Yes. So, perfect. We will validate everything from a technical consistency as well as the business consistency. And once it will be validated on both side we will be able to decommission the platform and then migrating and this methodologies BI first and ETL first will be applied for every business process uh business case sorry that you've identified during the initial inventory.
This will make the migration process something kind of bit automatic. Okay. So next session uh on the challenges which are specific to Oracle. Oracle as mentioned it this morning is a closed system. You may know that like me uh you have these data files, you have this table table space, you have this table of materials view and it's really hard to move this data out of Oracle to something open like data bricks but you have some u capabilities to do that and in this slide I uh split it into two parts the first one is the archive data or non-living data because we know that in every data warehouse we have archive data which are no not used very often and we have what we I named living data uh which are recent data ingested and transform and the way you will migrate them out of Oracle to data bricks could be different uh and using different uh methods.

19:27 Technical challenges

So the first one for archive data uh if you are using uh an Oracle on prem instance uh 19 19. 9 plus uh you and on cloud deployment as well uh you will be able to uh use the DBMS cloud package and the export data function that will help you to export data from table to uh various formats could be CSV, JSON or park Um you can also export data using uh SQL developer. So SQL developer is a Oracle um uh client tool to query your database and there is an offload function on uh SQL developer that will help you to u uh yeah offload the data to CSV and then uh import them into data bricks.
You can also uh write your own script. So you will spool uh the data into a CSV file. Uh and once this data uh is exported, you can transfer them depending on the volume to the cloud. Because when you migrate, for example, an on-prem database, you have also to consider the data volume that you bring from your uh servers to the cloud. And when it's a concern of hundreds of terabytes, it's a real concern. uh and for that uh you can rely on data transfer solution for example AWS noble or uh Azure data box uh to transfer uh your data from your servers to to cloud data centers um and once it's done you can load the data by using various functions uh features of datab bricks it could be autoloader it could be the load from from file option available in the uh the UI of datab bricks Um and last uh solution is to use data sharing especially if you are using Oracle in their autonomous uh database option.
Um because um Oracle decided to uh to use data sharing as a sharing protocol. That means that between Oracle autonomous database and datab bricks you can use the delta sharing protocol for the living data. Uh you can use uh lakehouse federation in a way uh because it will not um uh um lakehouse federation is a is a good fit if you want to transfer data for example for your data mass because there will the data volume won't be very important.
Um you can also use spice spark and JDBC connection directly to your Oracle database and uh execute some merge statement that will um on a daily basis uh uh uh ingest the delta between uh what has been imported previously and the daily jobs. You can also use Asia data factory, Amazon DMS uh to connect to your Oracle instances and move the data to data bricks. And finally, you can also use various solution coming from our partner ecosystem uh click taland and fiverran uh especially for example if you want to use CDC based uh connection uh fiverr has a tool named hbr that do that very very well.
Okay. So this first of all the data migration you have lots of tools that you can use and depending on the case depending on lots of parameters you can use one or another. Then in terms of uh challenges you have also the data types challenge. Um, Oracle data types are not the same uh as in data bricks. Uh, especially for example numbers to decimal. Uh, number uh rely on precision and scale uh which are not the same. And so depending on the precision depending on the scale, it could it could be a challenge to migrate this.
Um and so at uh beyond the the limit of uh the scale and the and the scale of decimal in data bricks you will have to convert it to double precision uh floating number. Uh character set conversion can be also a concern because in Oracle you have multiple ways to use uh various character sets. At data rigs we use only UTF8. Um time stamp and date and dates can be also a challenge. Uh you have also some data types that are not they are not existing on data bricks B5 for example or row ID.
Um so if you use that it will be a a very complex uh things to address especially for ID which is not a recommended uh uh use uh for Oracle and also uh for uh semiructured data types uh JSON and XML type can be converted into variant on data bricks user defined types. Yes, this is also a concern because um you will uh create kind of objects and these objects can use data types but uh also uh code um functions and procedures that you will have to convert.
But we have a solution for that and this solution is named blade brbridge formally blade brbridge. Um and blade bridge is made of um various solution and the first the the one that will be very interesting to convert your code will be the code converter and this converter um will support the code conversion from Oracle to datab bricks and it will support DDL conversion as well as PL/SQL to uh uh to to datab bricks and the target is to use datab brick SQL For uh PL/SQL, the target will be to convert PL/SQL to SQL scripting.

25:59 Code conversion

You have here a basic example of what could be converted from Oracle to data bricks. It's not the same uh language, but it's uh totally doable. And Legbridge will be able to support this code conversion. and you have some um topics that will requires more effort to be converted. Especially for example, you have lots of store procedure function PL/SQL packages that use intensively all of these supplied PL/SQL packages.
They are usually named DBMS something, UTL something, CTX something. And for all of this we don't have parity because there are packages supplied by Oracle. So if you have this in your code that means that you will have to retro engineered the code and write something that will be compatible with uh data bricks and the final one is the external libraries. So uh the thing I I mentioned at the beginning of this presentation um in Oracle you can write your routines using C or C++ language and put them into external libraries.
So on Windows they will live into DLL files or on Linux and SO files and so on. And because these are compiled code, you will if you want to convert this to data bricks, you will have to get the code, read the code, and convert the code. And because it's usually based off on C or C++ routines, leg bridge doesn't support C or C++ conversion. So you will have to do it manually. Okay. So in the next uh part of this presentation I have recorded a quick uh demo of what leg bridge look like.

28:40 Migration demo

So currently this is the former version of legbridgeidge named blood bridge. It will be changed uh in in days I would say. I hope tomorrow you will attend the keynote. So in in this uh first part I will extract the code from uh uh my Oracle database and I will be in the case of I want to implement a BI first. That means that I will target my data MS. I will export the code okay from Oracle. I will convert the code and this generated converted code will be pasted into data bricks to create the data mod structure.
This will not this demo will will not show the data transfer but it's just uh for the sake of the demo because we don't have a lot of time. So I connect to my Oracle database. Then there is three data mods. There is three simple data mods. So in the next calls we'll format the output. Okay. So we we will use here in Oracle a package named DBMS metadata to extract the DDL and then um we will uh run this DBMS metadata get DDL for every tables that will extract the code of all of these tables and we will paste the generated code to files and these files will be converted by um blade bridge.
So I use the script here. So in this uh example I have uh four files. Okay. And you can see the contents. This is the same that has been generated previously by the DBMS metadata. Same for this modu. sql. And then I've added another table to show you the difference between the numbers that we can convert depending on the scale on Oracle uh two decimals. And here for example column C and column D. This is not possible on data bricks because here we have a scale uh that is greater than uh 38 and here we have uh a negative scale that means that this number will be rounded at the hundreds of uh its value.
Then we will run um bladbridge. So blade brbridge here is a command line uh uh tool um and we provide the input directory, the output directory and a configuration file. Oops, sorry. that will uh generate um the code converted and at the end I as you can see here my data m has been converted into uh a syntax that is um compatible with um with data bricks uh numbers has been converted to decimal var 2 have been converted to strings and so on and if I look at my test number tables table um We can see that the column A and B has been converted into decimal uh following the the precision and scale which are compatible with data bricks.
And for the the one with uh the scale beyond than 38, it has been automatically converted into double. And the one that has been uh with a negative precision, it has been converted to kind of integer because this is the way it will be converted um to negative uh precision and so I think the rest so as you can see here I will copy the code generated and then I paste it into data bricks, run the code and it will create the table structure and once converted I will just have to uh transfer the data from my Oracle to datab bricks by using for example a JDBC connection or uh a lakehouse federation u um connection.

33:46 Benefits and wrap-up

Okay. So what are the benefits of migrating uh Oracle to data bricks? Um there are multiple. The first one is the TCO rediction. We all know that uh Oracle solutions are really expensive in terms of hardware. If for example you use Exad data as well as license um so you will eliminate for example hardware cost because you will move to the cloud. Um you will reduce software cost because you will pay for what you use and not pay for license plus uh opt enterprise options plus packs plus all the the options available on Oracle. you will also lower administrative cost.
Um what does it means? Uh you I have been DBA in my uh previous life. Uh and DBA for example if you want to tune a query you don't have any uh AI for example you don't have this productive optimization that we have into data bricks. um we don't have indexes and if you know Oracle you have these B3 indexes function indexes reverse key indexes bit mapap indexes lots of indexes and uh each one of them can be used in various use case you will not need that into data bricks you will lose you will uh use liquid clustering and that will be fine um and I have lots of example on that Um you will improve scalability and uh and performance uh because you will be able to scale up at levels of hundreds of thousands of nodes which is not possible for example with Exad data um and uh you will be able to scale up and scale down the performance and pay for the level of uh scalability that you will have chosen.
Um you will be able also to enhance data science and uh modern analytics capabilities. Uh for example if you want to implement um uh uh classification ML uh if you want to implement genai for example you will be able to do that with data bricks. With Oracle they will tell you oh move to OCI and use this OML package within OCI. But OML package means Oracle ML that means that you will be you will continue to be uh software locked.
Um your governance will be simplified especially when you if you decide to use ML and JI because you will be able to govern your model the same way you govern your tables, your columns, your rules and so on. Um and you will be able to implement uh faster time to insight and decision- making because for example implementing uh real time ingestion and real time at the end real time decision making process will be a real concern in Oracle because you will have to ingest data coming from Kafka cues to temporary tables and trigger ETL processing and so on and it will take more time if we compare it to data bricks because of delta because of our native connectivity to uh that kind of cues.
So this uh is one of the last slide that I have for you today. Uh and I have another one. Uh so if you want to uh know more and more details about the strategy that I've presented, you can scan this QR code. it will uh redirect you to a form uh to download this uh ebook on how to transform and to migrate your legacy data warehouse to data bricks. Oracle can be uh uh is addressed in this document but not only Terod data as well SQL server and all these legacy technologies and we recently uh released a couple of resources uh you will find them into databicks. comresources and these guides are technology specifics and in that one you have this Oracle to databicks migration guide uh that will give you all the details and some examples on how to convert um objects uh to data bricks uh and you have the same for uh terod data Microsoft SQL server and nitza so we are at the end of the presentation one minute left on time so um sorry I know that it's a lot of QR code to scan but um this one will redirect you to a set of resources if you want to continue your learning journey uh by using data bricks and last uh slide on the survey um to yeah to give some uh insight of what you have thinking about this presentation I thank you all if you have any question I'm sorry we are short in time 30 seconds so if you have questions I can take them uh after this uh presentation.
Thank you.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.