Migration
Oracle data warehouse to Databricks migration: a practical guide
Moving an Oracle data warehouse to Databricks: PL/SQL conversion, the Oracle behaviours that break results (empty strings, DATE, NUMBER), change data capture, parallel runs and licence decommissioning.
Hemant Walchale
· 3 min read
Oracle data warehouses move to Databricks to cut licence and support costs, to get off ageing Exadata hardware, and to put analytics and AI on one platform. The SQL converts reasonably well. The effort, and the risk, sit in PL/SQL and in a handful of Oracle behaviours that quietly change results.
This is the approach we recommend.
1. Inventory what really runs
List schemas, tables, views, materialized views, PL/SQL packages, procedures, functions and triggers, plus the jobs that run them (DBMS_SCHEDULER, cron or an external scheduler) and every consumer. Oracle’s own statistics (AWR, DBA_DEPENDENCIES, last-access information where available) show what is used. Retire the rest before migrating.
2. Know the behaviour differences
| Oracle | Databricks (Spark SQL and Delta) | What to watch |
|---|---|---|
Empty string '' is NULL |
Empty string and NULL are different |
Filters, joins and counts on text columns change. Normalize explicitly. |
DATE holds date and time |
DATE is date only; use TIMESTAMP for time |
Map Oracle DATE to TIMESTAMP unless the time is always midnight. |
NUMBER without precision |
DECIMAL(p, s) or DOUBLE |
Choose precision deliberately for money columns. |
VARCHAR2 byte vs char semantics |
STRING |
Length checks may change. |
| Sequences | Identity columns | Generated values will differ; never reconcile on surrogate keys. |
ROWNUM, CONNECT BY |
ROW_NUMBER(), LIMIT; hierarchies rewritten |
Hierarchical queries need care. |
MERGE, analytic functions |
Supported | Mostly direct. |
| Materialized views | Materialized views in Databricks SQL, or Lakeflow Declarative Pipelines | Refresh logic must be re-specified. |
| Triggers | No equivalent | Move the logic into the pipeline. |
| PL/SQL packages and cursors | Set-based SQL or Python | Rewrite; the largest effort. |
3. Keep data in sync with change data capture
Use a log-based CDC tool (Oracle GoldenGate, Lakeflow Connect where it supports your setup, or a third-party tool) to land changes in bronze Delta tables, and apply them with AUTO CDC or MERGE. This keeps Databricks current through the parallel run without bulk reloads.
4. Convert in domains and reconcile
Move one business domain at a time. Run old and new side by side, compare row counts, totals and row-level differences, and fix the causes listed above. Never compare on surrogate keys generated by sequences; compare on business keys.
5. Switch off the licence
Plan waves back from the Oracle support renewal date, and decommission each domain’s objects and jobs on the day it cuts over.
The short version
Inventory with Oracle’s own statistics, retire unused objects, handle empty strings, DATE and NUMBER deliberately, rewrite PL/SQL as set-based SQL, keep data in sync with CDC, reconcile by business key and switch off Oracle domain by domain.
Browse all migration guides, or see the Teradata to Databricks plan for the same wave-based approach. For a fixed-price plan, see the Data Platform Assessment.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Databricks. Unfamiliar terms are explained in the data engineering glossary.