Greetings @rahult1407 , I did some digging and would like to offer some helpful hints to guide you through your conversion.
Databricks Lakebridge (formerly Lakehouse Bridge) is a powerful migration tool, but Oracle to SparkSQL conversion—especially for views and materialized views—presents several challenges that require both automated assistance and manual intervention.
Common Conversion Challenges
Automated conversion tools like Lakebridge typically achieve around 75% conversion rates, with the remaining 25% requiring manual rework. Oracle and Databricks SQL procedure/view conversion is notoriously difficult due to fundamental differences in SQL dialects, built-in functions, data types, and architectural approaches.
Key Function and Syntax Differences
Here are critical Oracle to SparkSQL mappings you'll need to address:
- NVL/ISNULL → coalesce()
- SYSDATE → current_timestamp()
- ROWNUM → row_number() window function
- INSTR (4-parameter version) → substring_index() or modified locate() functions
- TO_DATE → unix_timestamp() or to_timestamp() with format strings
- Oracle NUMBER type → DECIMAL with max precision of 38, or DOUBLE for higher precision
Handling Views and Materialized Views
For regular views, the conversion process involves translating the view definition SQL from Oracle syntax to SparkSQL syntax, then recreating them in Databricks. For materialized views, Databricks supports them natively through Lakeflow pipelines that automatically manage refresh operations.
Recommended Approach
Rather than relying solely on automated conversion, adopt this hybrid strategy:
1. Categorize your views - ETL-focused views should convert to Delta Live Tables, while reporting views can become parameterized queries or dashboards
2. Manual conversion for complex logic - Most successful migrations involve significant manual work, especially for procedural logic
3. Test with representative data - Validate both syntax conversion and semantic equivalence
At the end of the day these migrations are involved and nuanced. Lakebridge is a great tool that will get you through 75% of what needs to be converted, the rest will involved manual intervention to address the edge cases.
Hope this helps, Louis.
