Oracle Database to Amazon Redshift: How to Move Your Data
Move Oracle into Amazon Redshift with Airbyte. Why NUMBER precision and DATE surprise you, why S3 staging is mandatory, and how to pick sort and dist keys.
Moving Oracle into Amazon Redshift takes transactional data out of an expensive licensed engine and puts it somewhere built for scanning millions of rows. Oracle is superb at the work it does and a poor choice for the analytical queries people keep asking it to run alongside that work.
This guide covers the managed path with Airbyte. Two things shape the build: Oracle's numeric and date types do not map as neatly as they look, and Redshift rewards decisions about table design that nothing in the pipeline makes for you.
Oracle Database to Amazon Redshift at a glance:
Why move data from Oracle Database to Amazon Redshift?
Two situations account for most of these pipelines.
The first is taking analytical load off a production database. Reports that scan years of transactions compete with the application doing today's work, and the usual compromise is a read replica that costs nearly as much as the primary. Moving that reporting to a column store is cheaper and faster, and the database administrator stops receiving complaints about month-end.
The second is joining Oracle to data that was never going to live there, such as product usage or marketing activity already sitting in AWS. The pairing is less suitable when your tables change shape often or carry structures a relational warehouse handles awkwardly, since Redshift wants stable columns; Oracle Database to Databricks copes with that far more gracefully.
What do you need before you start?
Four things, and the second is an audit rather than a credential:
An Oracle user, and a decision about change capture. The standard connector is cursor-based and never observes deletes. LogMiner capture lives in the enterprise connector with supplemental logging and a long grant list behind it. The Oracle source documentation covers both routes.
A list of your NUMBER and DATE columns. Specifically the ones carrying money, quantities or anything measured precisely, and the ones your application treats as a plain date. These are where the quiet inaccuracies live, and finding them now is far cheaper than reconciling a report later.
An S3 bucket in the cluster's region. Staging is the only loading path, so the bucket is part of the architecture. It needs put, get, delete and list permissions, and cross-region staging either fails or quietly costs you in transfer.
A view on how these tables will be queried. Sort and distribution keys are what separate a fast Redshift table from a slow one, and choosing them needs to know what people will filter and join on. Ask before the data lands rather than after somebody complains.
If your database or cluster restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the relevant security groups before you begin.
How do you build an Oracle Database to Amazon Redshift pipeline in Airbyte?
Step 1: Audit your numeric and date columns
Pull the data dictionary and list every NUMBER column with its precision and scale, alongside every DATE column. Mark the ones that carry currency or measurements, and the ones your application only ever sets to midnight. This is twenty minutes of work that prevents the specific class of bug where everything loads successfully and a total is wrong by a fraction nobody can explain.
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. Where your audit found columns that need explicit handling, a view casting them deliberately is easier to reason about than hoping the default conversion suits you.
Step 3: Configure the Amazon Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster endpoint, database, credentials and bucket details. The pipeline writes your schema plus a raw schema it manages itself, so grant accordingly and tell whoever audits the cluster that the second one is not spare.
Step 4: Create the connection, then design the tables
Click Connections, then New connection, select your streams and a sync mode. Incremental needs a cursor that moves reliably, and an updated timestamp maintained by the application is the usual choice. Once tables exist, set sort and distribution keys, which is the step that turns a working pipeline into a fast warehouse.
Run VACUUM and ANALYZE on a schedule as the tables grow, since a column store fed continuously benefits from maintenance that nobody performs by accident.
What happens to Oracle's numbers and dates on the way across?
They arrive, and they may not arrive exactly. Oracle's NUMBER accepts precision beyond what many destination types can represent, which is fine for the vast majority of columns and matters enormously for the few carrying financial amounts or scientific measurements. Nothing errors when precision is lost; the value simply becomes slightly different from the one Oracle holds.
DATE is the other one, and it catches people because the name is misleading. Oracle's DATE includes a time component whether or not the application ever sets it, so a column everybody thinks of as a calendar date arrives carrying midnight, or occasionally carrying whatever time a batch job happened to run. Grouping by that column without casting produces more groups than expected.
Handle both in a view on the Oracle side, where the decision is visible and reviewable, rather than relying on whatever conversion happens in transit. Cast dates explicitly when you want a plain date, and for the handful of high-precision numeric columns decide deliberately whether to round, to carry them as a string for exactness, or to accept the loss. Write the choice down next to the table, because the next person will otherwise assume the numbers match Oracle exactly.
Why do your Redshift tables need keys you have to choose yourself?
Because Redshift distributes rows across nodes and stores them in sorted order, and both decisions are yours. The pipeline creates tables that work, using defaults that are reasonable in the absence of information about your queries. Reasonable defaults on a large table are the difference between a query returning in seconds and the same query shuffling data between nodes for minutes.
Distribution style is the first choice. A large fact table joined constantly to another large table usually wants distribution on the join key so matching rows sit on the same node. A modest dimension table is often better replicated to every node, since copying it a few times costs less than moving it during every query. Getting this wrong is the most common reason a Redshift cluster feels slower than it should.
Sort keys are the second, and for data arriving from Oracle they are usually a date. Most queries against transactional history filter by period, and a sort key on the relevant date lets Redshift skip blocks entirely rather than reading them. Choose it from how people query rather than from Oracle's primary key, which was designed for a different database solving a different problem.
Frequently asked questions
Will deleted rows disappear from Redshift?
Not with the standard cursor-based connector, which sees inserts and updates only. LogMiner change capture in the enterprise connector does, with supplemental logging and a substantial grant list as the price.
Why does a total not match Oracle exactly?
Probably NUMBER precision. Oracle permits precision beyond what many destination types hold, and the loss is silent, so check the precision and scale of the columns feeding that total.
Why does grouping by a date produce too many groups?
Oracle's DATE carries a time component, so values that look like plain dates may differ by hours. Cast explicitly in a view if the column should behave as a calendar date.
Can I load without an S3 bucket?
No. Staging through S3 and loading with COPY is the only supported path for this destination, so the bucket is a prerequisite rather than a performance option.
Can I do this without writing code?
The pipeline, yes. Casting awkward types and setting sort and distribution keys are SQL, and both are what stop this being a working pipeline that nobody enjoys querying.
Get your Oracle Database data into Amazon Redshift
Audit the numeric and date columns first, because both failure modes are silent and both produce numbers that are almost right. Arrange the staging bucket in the cluster's region before you start, since it is mandatory rather than optional. Then treat table design as part of the job: sort and distribution keys chosen from real query patterns are what make a Redshift warehouse worth having, and nothing creates them for you.
Airbyte's connector catalog includes 600+ pre-built connectors, so transactional history can be analysed without troubling the system that produced it. For the same source into another column store, see Oracle Database to ClickHouse, and for another relational source into the same destination, PostgreSQL to Amazon Redshift.
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.