Oracle Database to BigQuery: How to Move Your Data

Moving Oracle Database into BigQuery is how organisations get decades of operational data out of a licensed system and into somewhere analysts can reach it without a DBA in the loop. The usual alternative is a nightly export to flat files, which runs whether anything changed or not and tells you nothing about what was deleted.

This guide covers the managed path with Airbyte. Two decisions carry it, one at each end: which of the two Oracle connectors you use, and two BigQuery choices that cannot be changed later without rebuilding.

Oracle Database to BigQuery at a glance:

Why move data from Oracle Database to BigQuery?

Two situations account for most of these pipelines.

The first is access and cost. Oracle licensing makes it expensive to give every analyst a connection, and plenty of teams are not permitted near it at all. A BigQuery copy gives them the data without adding load or licence cost to the system of record, which is frequently the entire business case.

The second is joining. Oracle holds one part of the picture, usually the oldest and most authoritative part, and the interesting questions combine it with marketing, product or support data that lives elsewhere. A warehouse is where those meet, and that framing matters for the dataset location decision below.

What do you need before you start?

Two of these involve other people, so start them before the work you can do yourself:

A decision about deletes. The standard Oracle source replicates using a cursor and cannot observe a deletion, so a row deleted in Oracle stays in BigQuery indefinitely with nothing reporting the discrepancy. Log-based change data capture is on the Enterprise Oracle source.

DBA time, if you want CDC. Enterprise CDC needs supplemental logging plus instance-level settings, and a grant list covering flashback access, select on any table, the catalog roles, select on any transaction, log mining, and rights to create tables and sequences and lock tables. On a multitenant container database each grant needs the container clause.

An identifier length audit. LogMiner ignores tables and columns whose names exceed 30 characters, silently. Run this check before the first sync rather than after a table has quietly stopped updating.

A Google service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. BigQuery queries can only reference datasets in the same physical location and the location cannot be changed after creation, so match it to whatever you intend to join against.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the Oracle side before you begin.

How do you build an Oracle to BigQuery pipeline in Airbyte?

Step 1: Prepare Oracle and create the dataset

Create a read-only Oracle user, apply the CDC grants if you are using Enterprise, and audit identifier lengths. Then create the BigQuery dataset in the location you settled on, because that choice is permanent and getting it wrong means recreating the dataset and reloading everything.

Step 2: Configure the Oracle source

Click Sources in the left navigation, then New Source, and select the Oracle connector you decided on, following adding a source. Supply the host, port, service name or SID, schema and credentials, and pick an encryption method. Native network encryption and TLS with certificate verification are both available, and unencrypted is offered only on self-managed Airbyte.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, then pick a loading method. Batched standard inserts is the default and simplest. Cloud Storage staging gives control over where staging files live at the cost of a bucket and an HMAC key, and buckets using customer-managed encryption keys are not supported.

Step 4: Create the connection and verify a subset

Click Connections, then New connection, select tables and a sync mode per stream. With CDC, deleted rows arrive flagged rather than vanishing, so append and deduped mirrors current Oracle state while plain append preserves every version. Run against a handful of tables first and compare row counts and numeric values against Oracle before extending.

Oracle instances tend to be the system of record for figures somebody reconciles, so that verification step is worth more here than on a source nobody audits.

Why would CDC silently skip a table?

LogMiner ignores tables whose names exceed 30 characters, and columns whose names exceed 30 characters. It does not warn and it does not error. The table sits in your connection looking configured and never receives an update.

The counterintuitive part is which schemas this affects. Oracle capped identifiers at 30 characters for most of its history, so genuinely old schemas are under the limit everywhere by construction. Anything built on 12.2 or later can use longer names, and descriptive naming conventions produce them easily. A table called CUSTOMER_SUBSCRIPTION_RENEWALS is exactly 30 characters. One more word and CDC stops seeing it.

Datatypes have the same shape of problem. LogMiner does not support every Oracle type, and user-defined types such as a VARRAY cannot be replicated through CDC at all. If your schema leans on object types, check them before committing to a log-based design.

How should you query the resulting tables?

Output tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys. That is not decoration, it is the main lever you have on query cost, because BigQuery bills for the bytes a query scans.

Filtering on the partitioning column prunes partitions and reduces what a query costs. On an Oracle table that has been accumulating for a decade and now grows indefinitely in BigQuery, the difference between a query that filters on it and one that does not is substantial and recurring.

Tell your analysts this once, at the start. It is much harder to retrofit onto a set of dashboards that already work than to establish as a convention while there is one query to change. Numeric precision is worth a look at the same time: an Oracle NUMBER declared without precision holds a wider range than any single BigQuery numeric type, so check anything financial rather than assuming the inferred type is right.

Frequently asked questions

Does the standard Oracle source support CDC?

No. Log-based change data capture is on the Enterprise Oracle source. The standard connector uses a cursor and cannot capture deletes.

Why is one of my Oracle tables never updating in BigQuery?

Check the identifier length. LogMiner ignores tables and columns whose names exceed 30 characters, without raising an error.

Can I change the dataset location later?

No. It is fixed at creation, and BigQuery queries can only reference datasets in the same physical location, so match it to whatever you intend to join against.

How do I keep BigQuery query costs down?

Filter on the extraction timestamp, which is the partitioning column. Doing so prunes partitions and reduces the bytes scanned, which is what BigQuery bills for.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. The Oracle side needs a DBA to apply supplemental logging and the CDC grants if you choose the Enterprise connector, which is configuration rather than code.

Get your Oracle data into BigQuery

Decide whether you need deletes, because that chooses your connector and brings a DBA into the project. Audit identifier lengths, create the dataset in the right location since that is permanent, and establish the partition filtering convention before anyone builds a dashboard.

Airbyte's connector catalog includes 700+ pre-built connectors, so Oracle data can sit alongside marketing and product sources in one warehouse. For Oracle into a relational destination, see Oracle Database to MySQL, and for another enterprise source landing in BigQuery, IBM Db2 to BigQuery.

Start syncing now →