Oracle DB to Snowflake: How to Move Your Data
Move Oracle into Snowflake with Airbyte. Choosing between cursor and log-based replication, the 30 character LogMiner limit, and handling DATE and NUMBER.
Moving Oracle into Snowflake takes the database running your business and puts a copy where analysts can work without consuming licensed capacity. Oracle is excellent at transactions and expensive to use as a reporting engine, which is the whole argument for this pipeline.
The decision that shapes everything is which connector you use. The standard one reads with a cursor and never sees deletions, while the enterprise one offers log-based change capture with a long list of prerequisites and one limit that silently drops columns.
Oracle to Snowflake at a glance:
Why move data from Oracle to Snowflake?
Two situations account for most of these pipelines.
The first is cost and capacity. Oracle licensing makes analytical workloads an expensive way to use a database, and moving reporting to a warehouse frees capacity on a system where capacity is a budget line rather than a technical detail.
The second is joining. Your Oracle database holds one part of the picture and your marketing, support and product data lives elsewhere, so a warehouse is where those meet and where the definitions get settled once rather than per team.
What do you need before you start?
The connector choice comes first because everything else follows from it:
A decision about deletions. If rows are deleted in the tables you need and that matters analytically, you want log-based capture and the enterprise connector. If records are only ever inserted and updated, the standard connector is simpler and sufficient. The Oracle source documentation covers both.
A DBA, if you chose log-based capture. LogMiner needs supplemental logging enabled and a substantial list of grants, none of which you can arrange yourself. Book that time before planning a date for the pipeline.
A check on your identifier lengths. LogMiner silently ignores table and column names longer than 30 characters, so a schema using modern long names needs checking before you rely on CDC covering everything.
Snowflake objects and a role. A warehouse, database, schema and a role able to create tables. Size the warehouse generously for the initial snapshot and scale back afterwards, since the first sync is by far the heaviest.
If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on both sides before you begin.
How do you build an Oracle to Snowflake pipeline in Airbyte?
Step 1: Choose your connector and audit the schema
Settle the deletion question, then go through the tables you need checking identifier lengths, NUMBER precision and which columns are Oracle DATE. That audit takes an hour and determines whether the resulting dataset is correct or merely plausible.
Step 2: Configure the Oracle source
Click Sources in the left navigation, then New Source, and select Oracle, following adding a source. Supply the host, port, service name or SID, and credentials, along with your connection encryption settings and schema selection.
Step 3: Configure the Snowflake destination
Click Destinations, then New Destination, and select Snowflake, following adding a destination. Supply the account identifier, warehouse, database, schema and role. Airbyte creates one table per stream with its own metadata columns alongside your data.
Step 4: Choose sync modes and schedule off-peak
Click Connections, then New connection, select your tables and a sync mode each, nominating cursor fields if you are using the standard connector. Schedule the initial load when the database is quiet, because a full read of a production Oracle instance is genuine load.
Then build a view handling the type conversions below, so every consumer gets the same answer rather than each making their own assumption.
What does LogMiner quietly leave out?
Identifiers longer than 30 characters, which it ignores without raising anything. A table or column with a longer name is simply not captured, so your CDC pipeline runs successfully while a table you believed was replicating never updates.
That catches schemas built after Oracle raised its own identifier limit, because the names are perfectly legal in the database and merely invisible to log mining. Check the lengths rather than assuming, since nothing in the pipeline will tell you.
User-defined types are the other gap worth knowing, since structures such as VARRAY cannot replicate through CDC at all. Where either limit applies, the honest options are renaming on the Oracle side, falling back to cursor-based replication for those tables, or accepting they are out of scope.
Which Oracle types need attention?
DATE first, because Oracle's DATE includes a time component and most other systems' does not. Treating it as a plain date silently discards the time, which is fine until somebody asks why two events on the same day cannot be ordered, or why a daily aggregate counts things twice.
NUMBER is the second. Oracle's precision range exceeds what many destination types hold comfortably, so a column declared generously can carry values that will not survive conversion exactly. Check the values actually in use rather than the declaration, because most columns are far narrower in practice than on paper.
Handle both in a modelled view over the landed tables, keeping the raw underneath. Snowflake's try cast is useful here, returning null rather than failing, though on financial columns the loud version is the right choice because a silently rounded figure is worse than a broken build.
Frequently asked questions
Does the standard Oracle connector capture deletions?
No. It is cursor-based, so a deleted row simply stops being returned and stays in your warehouse. Log-based capture through the enterprise connector is what sees deletions.
A table is not replicating under CDC. Why?
Check the identifier length. LogMiner ignores names longer than 30 characters without warning, so the pipeline succeeds while that table never updates.
Why did my timestamps lose their time?
Oracle's DATE carries a time component that a plain date type elsewhere discards. Map it to a timestamp type in your modelled view.
Can I replicate user-defined types?
Not through CDC. Structures such as VARRAY cannot replicate that way, so those tables need cursor-based replication or a view flattening them on the Oracle side.
Can I do this without writing code?
The Airbyte setup is UI-driven and the Oracle side is DBA configuration. You will want Snowflake views handling the type conversions, which is short SQL.
Get your Oracle data into Snowflake
Settle the deletion question first, because it decides which connector you use and how much DBA time you need. Audit identifier lengths before trusting CDC, handle Oracle DATE and NUMBER in a modelled view, and schedule the initial load against a quiet period.
Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the commercial sources that explain it. For the same source in a lakehouse, see Oracle to Databricks, and for another relational source in the same warehouse, Microsoft SQL Server to Snowflake.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.