Migrating from Oracle Data Integrator (ODI) to GCP

Oracle Data Integrator did its job for a decade. It moved data, it ran your Knowledge Modules, it filled your Oracle warehouse on schedule. But the bill for the Oracle stack underneath it keeps climbing, the ODI agents run on servers nobody wants to patch, and every new source means another Knowledge Module that only one person on the team understands.

At some point the question stops being "how do we keep ODI running" and becomes "what does this look like on Google Cloud." We recently ran exactly this migration for a client — ODI feeding an on-prem Oracle warehouse, moved wholesale onto BigQuery — so this is the playbook, not the theory.

Why teams leave ODI

The reasons cluster tightly:

1. Licensing and infrastructure cost. ODI is licensed software running on servers you provision, sitting on top of an Oracle database you also license. On GCP, BigQuery is serverless — no agents, no warehouse to size, no patching windows.
2. ELT that nobody can read. ODI's power was pushdown ELT via Knowledge Modules. But a KM is a template written in a proprietary substitution language. When it breaks, you're debugging generated SQL you never wrote.
3. Version control is an afterthought. ODI stores mappings in a work repository — a database. Diffing two versions of a mapping means exporting XML and squinting. Git-native transformation is a different world.
4. The talent pool is shrinking. ODI specialists are getting rarer and more expensive. Anyone who writes SQL can maintain a BigQuery + dbt stack.

Map the ODI concepts to their GCP equivalents

ODI is an ELT tool, which is actually good news — its philosophy (push transformations down to the target's compute) is exactly how BigQuery and dbt want to work. The concepts translate cleanly:

The one-to-one that matters most: an ODI mapping becomes a dbt model. The proprietary pushdown becomes plain, version-controlled SQL.

The audit: inventory every mapping and scenario

Before writing a line of dbt, catalogue the ODI estate. For each mapping and scenario:

• Source and target datastores — where data comes from, where it lands.
• Which Knowledge Modules it uses — LKM/IKM/CKM tell you the extract and load pattern you need to replace.
• Transformations — joins, filters, aggregations, the expressions in each mapping.
• Schedule and dependencies — what the scenario runs after, what breaks downstream.
• Consumer — who actually reads the output.

That last column is where the savings hide. In our experience, a meaningful share of ODI scenarios are orphaned — they run nightly, consume an agent slot, and feed a report nobody opened in months. Retire those. Don't migrate dead weight to a shiny new warehouse.

Extraction: replace the LKMs with Datastream

ODI's Load Knowledge Modules handled getting data out of source systems. On GCP you have a native, purpose-built answer:

• Datastream — Google's serverless CDC service. It streams changes from Oracle, PostgreSQL, MySQL, and SQL Server straight into BigQuery, continuously. For an Oracle-sourced ODI estate, this is usually the direct replacement for your LKMs.
• Fivetran / Airbyte — for SaaS and API sources ODI reached via JDBC or bespoke connectors.
• Cloud Functions / Workflows — for the handful of bespoke pulls that don't fit either.

Raw data lands in BigQuery untouched. You register those tables as dbt sources so lineage starts clean from the first hop — something ODI never gave you for free.

Transformation: IKMs become dbt models

Each ODI mapping becomes a SQL file. Structure them in layers instead of one monolithic interface:

Staging models (stg_*.sql) — one per source table. Rename, cast Oracle types to BigQuery types, filter junk. This is where you handle the NUMBER → NUMERIC, VARCHAR2 → STRING, DATE/TIMESTAMP conversions your LKMs used to do implicitly.

-- models/staging/stg_orders.sql
SELECT
    order_id,
    CAST(order_date AS DATE)                       AS order_date,
    LOWER(TRIM(customer_email))                    AS customer_email,
    CAST(order_total AS NUMERIC)                   AS order_total
FROM {{ source('oracle_erp', 'raw_orders') }}
WHERE order_id IS NOT NULL

Mart models (mart_*.sql) — joins and business logic, the part your IKMs encoded. These are what dashboards read.

-- models/marts/mart_revenue_by_month.sql
SELECT
    DATE_TRUNC(order_date, MONTH)  AS month,
    COUNT(DISTINCT order_id)        AS orders,
    SUM(order_total)                AS revenue
FROM {{ ref('stg_orders') }}
GROUP BY 1

Resist the urge to replicate each mapping 1:1. ODI's visual expressions often paper over join logic that only works by accident. Rewriting in SQL surfaces the assumptions.

If you're weighing Dataform vs dbt for the transformation layer: both work. dbt has the larger community and portability if you ever leave BigQuery; Dataform is GCP-native and free. We default to dbt unless the client wants zero third-party tooling.

Orchestration: scenarios become a Composer DAG

ODI packages sequenced your steps. On GCP that's Cloud Composer (managed Airflow):

• Composer generates the parameter lists ODI packages looped over.
• It triggers Datastream backfills, then dbt runs, in order.
• The DAG is the schedule and the dependency graph — readable, in Git, testable.

Clean separation: Composer decides what runs and when. dbt decides how to transform. BigQuery does the compute.

Testing: the part ODI's CKMs never really enforced

ODI had Check Knowledge Modules, but in practice most teams wired them loosely or not at all. dbt makes testing the default:

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: order_total
        tests:
          - dbt_utils.accepted_range:
              min_value: 0

dbt test runs in CI. Failures block the merge. You catch more data-quality bugs in the first week than the CKMs caught in a year.

Parallel validation: the non-negotiable step

Run ODI and the GCP stack side by side for two to four weeks. No exceptions. Compare daily:

• Row counts on the critical tables.
• Aggregate values — revenue, counts, whatever the dashboards report.
• The dashboards themselves, built on both outputs.

When the numbers match for a full week, retire the ODI scenario. Not before. Teams that skip this discover tiny discrepancies in production months later — usually after the ODI agents have already been decommissioned and the fix is no longer simple.

What the stack looks like after

Timeline

For a medium ODI estate (30–80 mappings, 2–3 source systems):

Total: 7–8 weeks. Faster if the estate is clean; slower if there are packages with iteration logic or undocumented KMs.

What we saw

On the ODI-to-GCP migration we ran most recently, the pattern held: a large fraction of scenarios were orphaned and retired outright, the ELT pushdown mindset transferred almost directly to dbt, and the biggest single cost drop came from turning off the Oracle warehouse and the agent servers underneath ODI. The build teams we've done this for — like the BigQuery-based multi-tenant analytics platform for Finsights (80+ tenants, sub-second queries) — end up on the same serverless BigQuery + dbt foundation.

Migrating off ODI, or weighing GCP against Snowflake as the destination? We've run the ELT-tool exits and the warehouse cutovers both. Book a discovery call and we'll map your Knowledge Modules to a GCP stack. See also our guides on Oracle → BigQuery and the broader legacy stack migration playbook.