Cloud-Native ETL for Oracle
OCI Data Integration is a fully managed ETL service that extracts data from sources, transforms it using a visual designer, and loads it into target systems. For Oracle database developers, it replaces on-premise ETL tools like Oracle Data Integrator (ODI) for cloud workloads, with native connectivity to Oracle databases, Object Storage, Autonomous Database, and Fusion applications.
Key Concepts
A Workspace is your development environment. Data Assets define connections to source and target systems. Data Flows are the visual ETL jobs that define extract, transform, and load steps. Pipelines orchestrate multiple data flows with conditional logic and error handling. Tasks are the executable units that you schedule or trigger.
Creating a Simple Data Flow
To load data from an EBS database into Autonomous Data Warehouse:
Create data assets for the source (EBS Oracle DB) and target (Autonomous DW). In the data flow designer, add a Source operator pointing to the EBS table or view. Add a Filter operator to select only changed records (incremental load). Add an Expression operator for transformations like data type conversion, concatenation, or lookup. Add a Target operator pointing to the ADW table with the merge (upsert) strategy.
Incremental Loading
For efficient incremental loads, use a high-water mark pattern. Store the last loaded timestamp in a parameter table. Each run extracts only records modified since the last timestamp. After a successful load, update the high-water mark:
Source SQL: SELECT * FROM orders WHERE last_update_date > :last_loaded_timestamp. After successful load, update the parameter table with the current timestamp.
Scheduling and Monitoring
Schedule data flows to run at specific intervals using the built-in scheduler. OCI Data Integration provides execution logs showing row counts, elapsed time, and errors for each run. Set up OCI Alarms on the pipeline execution metrics to get notified when a pipeline fails or when processing time exceeds expected thresholds.
When to Use Data Integration vs OIC vs Custom PL/SQL
Use OCI Data Integration for batch ETL workloads, large data volumes, and data warehouse loading. Use OIC for event-driven integrations, application-to-application orchestration, and when you need pre-built SaaS adapters. Use custom PL/SQL for in-database transformations where the data is already in Oracle and does not need to leave the database.