Green and wrong: what we learned migrating Oracle ODI to BigQuery and Airflow

A converted data pipeline ran end to end on Airflow. Fourteen steps, twenty-three tasks, every one of them green. It loaded 488 rows from 244 source records.

Nothing in the run said so. No task failed, no log carried a warning, no row count was flagged. The duplication surfaced only because somebody had written an assertion that compared input rows to output rows and named the discrepancy FANOUT rather than folding it into a generic pass/fail.

That run is the reason we now work the way we do. This is what we found migrating Oracle Data Integrator load plans to BigQuery and Airflow, and the uncomfortable conclusion at the center of it: the most expensive defects in a data migration are the ones that report success.

Automated conversion translates syntax, not intent

Most ODI-to-cloud migrations now start with a converter. Ours did. The tool takes ODI interfaces and PL/SQL packages and emits BigQuery SQL plus an Airflow DAG definition. It is genuinely useful — it does in minutes what would take a team weeks, and the bulk of what it emits is correct.

But a converter has three structural blind spots, and they are not the kind that get better with a newer version.

It only converts what it can see. ODI is not just SQL. A load plan is a graph of steps, and some of those steps are orchestration primitives — wait for data to arrive, invoke a scenario, branch on a return code. These have no BigQuery equivalent. In our bundles, a step that polled a view every 120 seconds until it returned a row was simply absent from the conversion. Not flagged, not commented, not stubbed. Absent. The converted pipeline runs that step once against a possibly-empty view and reports success.

Another step invoked an ODI scenario. The converter rendered it as a CALL to a stored procedure with a name derived from the scenario name — a procedure that has never existed in any database, anywhere.

It resolves names, not meanings. The converter emitted a real, existing dataset name for fifteen table references. Real, existing, and wrong — that dataset holds unrelated tables from a different subject area. Every reference resolved cleanly and found nothing. Had it emitted a dataset that didn't exist, the pipeline would have failed on the first dry run and cost us an hour. Because the dataset existed, it cost considerably more.

It has no notion of "this cannot be right". Oracle allows a procedure to live inside a package. BigQuery has no packages. The converter walks standalone procedures only, so a CALL to PA_SOME_PACKAGE.SOME_PROCEDURE was emitted faithfully with no body anywhere in the output. That step would have failed loudly — which is fine — but only because we had not "helpfully" stubbed it.

None of this is a criticism of automated conversion. It is a description of what conversion is. The tool's job is to produce plausible code. Deciding whether the code is true is a separate job, and it is the job nobody budgets for.

Sort defects by severity, not by frequency

We ended up classifying every defect into three buckets, and the ordering is the opposite of what intuition suggests.

Fatal defects are the good news. A reserved keyword used as a column alias, a WITH clause immediately before a MERGE (BigQuery rejects it), a CTE inside a FOR loop, a bare NULL that BigQuery infers as INT64 when the target column is a string. These announce themselves. You fix them and move on.

The silent ones are where the work is.

A missing audit predicate. Two interfaces joined a child table to its parent fact with no filter on the audit ID — only a floor on business time. Rows left behind by any earlier run therefore multiplied every new row. That is the 244-in, 488-out run. Worth noting: this was faithful to the Oracle original. The defect was migrated, not introduced. It is latent in the production system today, and anyone who replays a day gets silently duplicated records on a green run.

INSERT with no matching DELETE. Re-running appends duplicates. Trivially fixable — but flag it rather than fix it silently, because it may be behavior the business relies on.

PIVOT with a hardcoded value list. A pivot emits NULLs for listed-but-absent values and silently drops unlisted ones. No error in either direction. If the source data has grown a new category since the ODI job was written, those rows vanish.

Unresolved template placeholders. One bundle shipped twenty-five literal <XCOM_PRODUCER> markers that were never substituted. Fatal if the placeholder lands in executable SQL; silent if it lands inside a string literal or a comment.

Cursor loops converted literally. Oracle PL/SQL cursor loops become BigQuery FOR loops that call a procedure once per iteration. Correct for tens of iterations. For a multi-year window at 15-minute granularity, it does not finish.

The rewrite is set-based — derive iterations from the data rather than generating the full range, cross join, and compute with window functions what the procedure computed row by row. Four rules make that rewrite equivalent rather than merely fast:

1. Partition every window function by the iteration key, or LAG reads across boundaries.
2. Convert sequential IF mutations into one nested CASE with conditions evaluated in reverse order, because the last assignment wins.
3. Negate the preceding condition in every ELSEIF branch, or rows fire twice.
4. Watch for zero-duration events, which a WHERE start < end guard silently drops.

The rule: one step, actually run, then verified

Here is the discipline that changed our results more than anything technical.

A migration step is done only when it has actually run green on the target Airflow instance and its output has been verified against expectations. A dry run, a lint pass, and "the SQL compiles" are not done.

One step per merge. Never start step N+1 while step N has not run.

This feels slow, and people resist it. The argument against is always the same: the SQL compiles, the dry run passes, why wait? The argument for is that every one of the following was true of a step that compiled and dry-ran cleanly:

• It loaded zero rows, because it resolved a different audit ID than the one the staging data was written under, and reported SUCCESS having done nothing.
• It loaded double the correct rows.
• It ran a MERGE against a view that was empty because the sensor which should have waited for data had been dropped in conversion.
• It reported an object as PRESENT that we had created ourselves earlier in the same migration.

A dry run proves a statement parses. It proves nothing about DAG wiring, credentials, task dependencies, file paths, or whether the numbers are right. Batching four steps into one deployment and getting four green ticks tells you the pipeline ran. It tells you nothing about which of the four is correct.

Write assertions that are capable of failing

This sounds obvious. It is not, and we got it wrong twice in ways worth describing, because both failures looked exactly like success.

The first. Two provenance checks were written as SELECT ... FROM target CROSS JOIN airflow_job GROUP BY job.run_id, with a CASE producing a MISSING verdict when no job matched. The harness decides pass or fail by scanning output for failure tokens. When no Airflow job matched, the CROSS JOIN produced no rows, the GROUP BY produced no groups, and the statement returned nothing at all. Silence read as success. The MISSING branch was unreachable in precisely the situation it existed to detect.

The fix is a rule: an assertion must emit exactly one row carrying its own verdict, whatever the data does. In practice that means scalar subqueries with no top-level FROM. And the harness must report a zero-row statement explicitly rather than skipping it.

-- Wrong: returns nothing when no job matches, which reads as success.
SELECT CASE WHEN COUNT(*) = 0 THEN 'MISSING' ELSE 'OK' END AS verdict
FROM   target t
CROSS JOIN airflow_job j
GROUP BY j.run_id;
 
-- Right: always one row, always a verdict.
SELECT CASE
         WHEN (SELECT COUNT(*) FROM airflow_job) = 0 THEN 'MISSING_JOB'
         WHEN (SELECT COUNT(*) FROM target)      = 0 THEN 'MISSING_ROWS'
         WHEN (SELECT COUNT(*) FROM target) <> (SELECT COUNT(*) FROM source) THEN 'FANOUT'
         ELSE 'OK'
       END AS verdict;

The second. An assertion checked that an upstream step wrote 24 rows, "proving" it had read the target before a downstream step grew it. But the downstream step takes that shared table from 24 rows to 203, so on the next run the upstream step correctly writes 203 — and the assertion reported a failure on a perfectly good run. Scoping the count to a single run did not help. Once the MERGE has converged, "read the old target" and "read the new target" produce the same number. The check was structurally incapable of detecting the ordering violation it existed to catch.

From which the general principle:

If a check would keep passing after the property it guards is broken, it is not a check.

Two smaller lessons in the same vein. A row-count comparison is the wrong test for a slowly-changing dimension — a correct re-run writes nothing, so demanding 1:1 between staging and target reports a catastrophic-looking 5805 → 0 on a healthy load. Assert coverage instead: every source key has exactly one current row, whoever wrote it. And use distinct verdict words. FANOUT and MISMATCH send an investigator in opposite directions — duplicated rows versus missing ones — and collapsing both into FAIL throws that away.

Verification must not write

Our verification harness originally executed the SQL and then checked the results. Running it to "verify" an Airflow run therefore overwrote the batch ID stamped on every target row with the harness's own — deleting the only evidence that Airflow, rather than the harness, had produced them. It also consumed a day from the watermark table on each execution, silently advancing the business date.

Split the harness in two. --execute runs the SQL and is used before deployment, to prove the logic. --check is strictly read-only and is used after, to confirm what Airflow produced. The provenance assertion — target rows carry an Airflow run ID, not a local one — is the first thing checked, because everything downstream of it is meaningless if it fails.

Three environments, and the discipline of not crossing them

Most of the wasted time on this project came from a command running against a different environment than intended.

• The Airflow instance runs the DAG. It is the only place a step can pass the gate.
• A sandbox project holds the data and has full DDL rights. All testing happens here.
• The client's project is the real target, and is strictly read-only.

Two traps are worth stating explicitly because both are silent.

GOOGLE_APPLICATION_CREDENTIALS overrides your active cloud configuration. If that variable points at the sandbox key, Python clients authenticate as the sandbox no matter which configuration is active. Switching to the client configuration and querying the client project will quietly query the sandbox instead — and the results look entirely plausible. Command-line tools and client libraries do not share a credential source, so they can and do disagree.

BigQuery returns 403, not 404, for a dataset you lack permission on. From outside, "this does not exist" and "this exists and you cannot see it" are the same response. Never record an object as absent on the strength of a failed query. Record it as unknown and ask.

When seeding a sandbox from a client system, mirror the real control and audit rows rather than inventing them. Invented control rows produce a pipeline that works beautifully against invented control rows. Pull only what the scripts under test actually reference, ask before every pull, and never let production or personal data cross.

And when something is missing mid-test, do not stub it. Check whether the client has it. If they do, ask for it. If they don't, that is a genuine finding — report it.

Existence is not correctness

The deliverable that matters most to a deployment team is a bundle of DDL that stands the pipeline up in an empty project without anyone having to ask a question. That is the acceptance test: if it cannot build a fresh project, it is not finished.

Three genuinely distinct checks hide behind the word "verified":

1. Does the object exist? Hold the expected inventory as data, join it to INFORMATION_SCHEMA, and emit PRESENT or MISSING per object. Group known, expected gaps separately so a surprise is visually distinct from an accepted one.
2. Is its schema right? An existence check passes on a table with the right name and the wrong columns. Compare column by column — name, type, ordinal — against a reference. This is not optional, because CREATE TABLE IF NOT EXISTS silently leaves a stale definition alone. A bundle can run cleanly against a project whose tables are subtly wrong and report complete success.
3. Whose is it? PRESENT does not mean the client already had it. We once reported an object as PRESENT in the client's project that we had created there ourselves. Check creation timestamps before concluding anything about ownership.

One more, easily missed: every routine the pipeline calls must be created by the bundle. A function created by hand in the sandbox during testing works everywhere except a fresh project. Extract both lists mechanically — routines called, routines created — and diff them. A count of zero from an inventory script is a suspicious result, not a good one; ours initially reported zero calls because the pattern did not account for a backtick sitting between the routine name and its opening parenthesis.

What to demand before you write a line of code

The original ODI export. Not the converted output — the original. Several defects were visible only as a difference from the Oracle source and read as perfectly normal code in isolation. Write a baseline document describing what the original actually did, before converting anything, and audit everything else against it.

Package bodies for anything called from inside a package. Converters walk standalone procedures. Ask specifically.

The source definition of anything the converter rendered as an opaque call. If a step became a CALL to a procedure that does not exist, find out what it was in ODI. In our case it was a scenario invocation — an orchestration construct, not a procedure at all.

An honest answer about data volume in the target system. We discovered late that every one of the 29 tables in the client project had zero rows. The structures existed; nothing had ever been loaded. That single fact changes the entire test plan: all test data must be seeded, and no comparison against real client output is possible. Better to know on day one.

Say what green does not prove

The last discipline is the hardest, because it runs against every instinct about how to report progress.

If a branch of a converted step could never load a row because the data to exercise it does not exist, say so directly next to the PASS. If a step is running but one of its four sub-operations is commented out pending a package body from the client, the step is not "done" — it is partial, and calling it done costs you the one thing the whole exercise is supposed to produce.

We had a step in exactly that position. The temptation to write an empty stub for the missing procedure was real: it would have compiled, run, gone green, and let us close the ticket. It would also have made the pipeline report success while a documented part of it did nothing at all. A stub is not a placeholder. A stub is a lie that passes tests.

The commented-out call, with a note recording what is missing and what was requested from whom, is uglier and honest. Every document we hand over says the same thing about that step in the same words, so nobody reading any one of them can come away with a different impression.

In summary

Automated ODI-to-BigQuery conversion is worth using. It is not worth trusting. The gap between "the pipeline ran" and "the pipeline is correct" is where the entire risk of a migration sits, and closing it takes a specific and slightly tedious discipline:

• One step at a time, actually run on the real orchestrator, then verified.
• Assertions that emit a verdict whatever happens, with distinct words for distinct failures.
• Verification that reads and never writes, so it cannot destroy the evidence it exists to gather.
• Environments that are never crossed, and permission errors never read as absence.
• A DDL bundle that can build a fresh project unaided, checked for existence, schema and ownership separately.
• Documentation that states plainly what each green result does not prove.

If you're planning the route itself rather than auditing the conversion, the stack-level playbook is in Migrating from Oracle Data Integrator (ODI) to GCP.

None of this is clever. All of it is the difference between a migration that is finished and one that merely looks finished.