Oracle to BigQuery: a migration checklist that keeps reporting intact.

Before any table moves, list every consumer: scheduled reports, dashboards, extracts sent to partners, stored procedures that other systems call, and the spreadsheets that quietly query a view. The consumers define the acceptance criteria. A migration that reproduces the tables but not the consumers is not finished.

• Capture each consumer with its owner, refresh schedule, and the query or view it uses.
• Flag consumers that rely on Oracle-specific behavior: PL/SQL logic, sequences, NUMBER precision, date arithmetic, and NULL handling in ORDER BY.
• Decide which consumers retire, which are rebuilt, and which must be reproduced exactly.

A Raw, Staging, and Presentation layout in BigQuery keeps the migration honest. Raw receives data as extracted, with no transformation, so every downstream difference can be traced to a transformation rather than to the load. Staging applies typing, deduplication, and business rules. Presentation exposes the tables and views that reports and dashboards read.

This separation also gives the team a place to run reconciliation: Raw row counts and control totals must match the source, and Presentation figures must match the legacy reports for an agreed set of dates.

• Map Oracle NUMBER columns to NUMERIC or BIGNUMERIC deliberately; FLOAT64 introduces rounding that reconciliation will catch late.
• Decide how DATE and TIMESTAMP columns are stored and presented. Store in UTC, present in the business time zone, and document both.
• Define how empty strings, NULLs, and trailing spaces are treated during load, because Oracle and BigQuery differ and joins will silently drop rows otherwise.
• Agree on a partitioning and clustering plan for the largest tables before the first full load, not after the first slow query.

Full reloads are simple and are the right first step for reconciliation. For daily operation, an incremental strategy is required, and it must be one the operating team can describe in a sentence: a watermark column, a change table, or a log-based capture. Whichever is chosen, the pipeline needs an idempotent load so a rerun after a failure produces the same result, and a visible record of what each run loaded.

Tools such as Informatica IICS, Dataflow, or plain SQL scheduled in BigQuery can all do this. The tool matters less than the recorded design: source, watermark, dedup rule, failure behavior, and who is notified.

• Row counts per table per load date, source versus Raw.
• Control totals on the columns finance and operations already watch: amounts, quantities, balances.
• Report-level comparison for an agreed sample of dates, produced from both systems and signed off by the report owner.
• A written list of every accepted difference with its cause, so nobody rediscovers it after cutover.

Run both systems in parallel for a defined period, switch consumers one at a time starting with the least critical, and keep the Oracle path available until every consumer has been verified on BigQuery for a full reporting cycle. Document the rollback for each consumer before it moves. Decommission only when the owners of the reports agree in writing that the new path is the system of record.

Done well, the outcome is measurable: reporting latency drops from overnight batch windows to a few hours, and the people who run the reports can explain where every number comes from.

If the inventory step is already hard because nobody knows every consumer, or if past attempts stalled at reconciliation, that is the point where a scoped technical assessment pays for itself. Ascendant Systems delivers this work as a defined engagement with the checklist above as the acceptance frame.

Published 2026-09-25. Service: Data engineering. All insights.