Migrating from Oracle to BigQuery

If your organization is already on Google Cloud — or heading there — moving an Oracle warehouse to BigQuery is often the cleanest destination choice. BigQuery is serverless in a way even Snowflake isn't: there are no warehouses to size or resume, you query and Google allocates the compute. For an Oracle team tired of provisioning capacity, that's the whole pitch.

But BigQuery is also the most different from Oracle of the modern targets. Getting the mental model right matters more here than anywhere. This is the playbook.

Why teams move Oracle to BigQuery

• Genuinely serverless. No warehouse to size, resume, or right-size. You query; Google runs it. Billing is per-byte-scanned (on-demand) or per-slot (capacity).
• Native to the GCP ecosystem. If you're using Datastream, Looker, Vertex AI, or Cloud Composer, BigQuery is the gravitational center.
• Separation of storage and compute, taken further. Storage is cheap and columnar; compute is elastic and ephemeral.
• A clean transformation story with dbt or Dataform running SQL directly in the warehouse.

The mental-model shift: no indexes, no primary keys

This is the part Oracle DBAs stumble on, so start here. BigQuery has no indexes and no enforced primary keys. Performance comes from two things instead:

• Partitioning — usually by a date/timestamp column, or by ingestion time. This is what replaces most of your Oracle range partitions and date-column indexes.
• Clustering — up to four columns that BigQuery uses to co-locate and prune data. This replaces most of your secondary indexes.

You don't tune BigQuery by adding indexes. You tune it by partitioning on the column you filter by most and clustering on the columns you filter and join on next. Redesigning your hottest tables around partition + cluster keys is a real migration task, not an afterthought.

Primary and unique keys can be declared (as unenforced metadata, which the optimizer can use) but BigQuery won't enforce them. Uniqueness becomes a dbt test, not a constraint.

The data-type mapping

Same headline trap as every Oracle migration: Oracle DATE carries time. Map it to DATETIME (or TIMESTAMP), never DATE, or your daily totals will quietly shift.

PL/SQL: rebuild, mostly in SQL

BigQuery has scripting and stored procedures, but no PL/SQL. The translation:

• Set-based transformation logic → dbt models. This is most of it, and it's the right home. A PL/SQL package that builds a summary table is a dbt model waiting to happen.
• Procedural control flow → BigQuery scripting (BEGIN … END, LOOP, variables) or stored procedures. Reserve for genuinely procedural steps.
• MERGE is supported and close to Oracle's — validate the branches.
• Sequences → GENERATE_UUID() or a max+row_number pattern. BigQuery has no sequence objects; surrogate keys are generated in SQL.

Getting the data across: Datastream

GCP has a first-party answer for Oracle → BigQuery replication: Datastream, Google's serverless CDC service. It reads Oracle redo logs and streams changes into BigQuery continuously, with a managed Oracle → BigQuery template. For the parallel-running phase this is usually the path of least resistance — no ETL server, no agent.

For the one-time historical backfill, export to Parquet/Avro on Cloud Storage and load with bq load or an external table, then let Datastream keep it current.

Alternatives — Fivetran or Airbyte — make sense if you also have SaaS sources to consolidate in the same project.

Transformation in dbt

Raw tables land, register as dbt sources, and you layer models on top:

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

Configure partitioning and clustering right in the model config — this is where the index redesign lands:

{{ config(
    materialized='table',
    partition_by={'field': 'order_ts', 'data_type': 'datetime', 'granularity': 'day'},
    cluster_by=['customer_email']
) }}

Uniqueness that used to be a constraint becomes a test:

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]

Parallel validation: the non-negotiable step

Two to four weeks running Oracle and BigQuery side by side. Compare daily:

• Row counts on the tables that feed reports.
• Aggregate values to the cent — this is where the DATE/DATETIME and NUMERIC/INT64 traps surface.
• The same dashboards on both.

Match for a full week, then cut over.

What the stack looks like after

Timeline

Proof it works

BigQuery is home turf for us. We built Finsights — multi-tenant embedded analytics for 80+ tenants — on a BigQuery + dbt stack with sub-second query response at p95 and 90% of manual reporting eliminated. Boldspace runs on the same foundation, with client onboarding cut from six weeks to one. The Oracle-to-BigQuery move lands teams on exactly this kind of stack.

Deciding between BigQuery and Snowflake as the Oracle destination? The data-type traps are the same; the tuning model and ecosystem fit differ. Book a discovery call and we'll help you pick. Related: Oracle → Snowflake, Postgres → BigQuery, and the legacy stack migration playbook.