Pain points migration from oracle to bigquery


1. 

SQL Syntax Differences



  • Oracle uses PL/SQL, while BigQuery uses standard SQL.
  • Key differences in functions, procedures, windowing functions, and error handling often require manual rewriting.






2. 

Stored Procedures & Packages



  • Oracle’s stored procedures, packages, and triggers are deeply embedded in workflows.
  • BigQuery has limited support for procedural logic and doesn’t support packages like Oracle.






3. 

ETL/ELT Reengineering



  • ETL processes built with Oracle-specific tools (e.g., Oracle Data Integrator) must be re-architected using tools compatible with BigQuery (e.g., Dataflow, Cloud Composer, dbt).






4. 

Data Type Mismatches



  • Differences in data types (e.g., NUMBER, DATE, CLOB) between Oracle and BigQuery can cause data fidelity issues during migration.






5. 

Performance Tuning Shift



  • Oracle relies on indexes, partitions, and hints, while BigQuery is serverless and relies on query optimization and table partitioning strategies.
  • Performance tuning paradigms must be relearned.






6. 

Security and Access Control



  • Oracle uses role-based access control and fine-grained access policies, which may not map 1:1 to BigQuery’s IAM model.
  • Requires redesign of data governance policies.






7. 

Migration of Historical Data



  • Large volumes of data, especially if not well-partitioned, can be costly and time-consuming to move.
  • May require staging in Google Cloud Storage and batch load jobs.






8. 

Change Management & User Training



  • Analysts and developers accustomed to Oracle tools need training in BigQuery and associated cloud-native tools.
  • Business users may also need to adapt to new reporting workflows (e.g., from Oracle BI to Looker).






9. 

Integration with Legacy Systems



  • Oracle often integrates tightly with legacy ERP systems and custom apps.
  • BigQuery may require middleware or APIs to maintain similar integrations.






10. 

Cost Management



  • BigQuery uses pay-per-query pricing, which is very different from Oracle’s licensing model.
  • Lack of understanding of cost controls (e.g., materialized views, partitions, byte limits) can lead to unexpected costs.



From Blogger iPhone client