Amazon S3 to Amazon Redshift: How to Move Your Data
Move Amazon S3 files into Redshift with Airbyte. Why the pipeline needs two buckets, the IAM permission that fails silently, and when COPY is the better tool.
Moving Amazon S3 into Redshift turns files sitting in a bucket into typed tables anybody can query. Partner extracts, exports from another system and output from jobs all land in object storage and stay invisible to your analysts until something loads them.
This pairing has a quirk worth knowing up front. Both ends of the pipeline use S3, because the destination stages data in a bucket before loading it, so you are managing two buckets and two sets of permissions rather than one.
Amazon S3 to Amazon Redshift at a glance:
Why move data from Amazon S3 to Amazon Redshift?
Two situations account for most of these pipelines.
The first is making a file feed usable without asking every analyst to think about buckets. A loaded table is typed once, governed like everything else in your warehouse, and queried without anybody needing storage credentials or knowing which prefix this month's extract lands under.
The second is operating it reliably over time. Redshift can load from S3 itself, so the honest reason to run a pipeline is the scheduling, retries and incremental tracking around the load rather than the load itself, which the section below sets out.
What do you need before you start?
Two buckets, and a policy detail that accounts for most failed first attempts:
Read access to the source bucket, at both levels. You need read and list permissions, and the resource list must name both the bucket itself and everything under it. Object-level permissions alone are not sufficient to authenticate. The S3 source documentation includes the policy to copy.
A separate staging bucket with write access. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command. That needs put, get, delete and list, in the same region as the cluster, and it is not the same bucket your source files live in.
A glob pattern per stream. There are no predefined streams here, because the streams are whatever your bucket contains. Patterns use wcmatch syntax, are relative to the bucket root, and can be combined with a pipe, with a double asterisk matching every file.
Redshift entities created in advance. A database, a schema and a user able to write data and manage staging operations. Airbyte writes into two schemas, the one you nominate and a raw schema it manages, so tell whoever reviews your Redshift objects that the second is expected.
Note also that an SSH tunnel covers the SQL connection only. Staging traffic reaches S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.
How do you build an Amazon S3 to Redshift pipeline in Airbyte?
Step 1: Sort out both buckets first
Write the read policy for the source bucket with both resource entries, and provision the staging bucket with write access in your cluster's region. Doing both before you touch Airbyte means the connection tests tell you something useful rather than failing on permissions nobody mentioned.
Step 2: Configure the S3 source
Click Sources in the left navigation, then New Source, and select S3, following adding a source. Choose your authentication method, enter the bucket name, then define a stream for each file format with a name, a format and a glob pattern selecting only files of that type.
Step 3: Configure the Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your staging bucket and IAM key. Staging files are removed after a successful COPY by default, and can be retained if you need them for debugging.
Step 4: Create the connection and set your keys
Click Connections, then New connection, select your streams and an incremental sync mode so new files are picked up without re-reading what has already been processed. Set the frequency against how often files actually land, and set sort keys on whatever date column your queries filter by.
Check the resulting table after the first sync, particularly with CSV, since that format carries no type information and the connector has to infer what each column contains.
Why do correct credentials still fail?
Because S3 treats listing a bucket and reading an object as separate permissions, and the connector needs both. A policy granting access to everything under the bucket path looks complete and leaves the connector unable to discover which files exist, which is the step that happens first.
Airbyte's documentation flags this directly, because object-level permissions alone are not sufficient to authenticate. The resource list in your policy needs two entries: the bucket ARN on its own for listing, and the same ARN with a wildcard path for reading the objects inside it.
On this pairing the confusion doubles, because a failure could be the source bucket or the staging bucket and both involve S3 and IAM. Note which side the error came from before changing anything, and check the policies before regenerating a key, which fixes neither problem.
Why not just run COPY yourself?
This is the most reasonable version of that question in the whole series, because loading from S3 is what Redshift's COPY command exists for. A single well-formed file loaded once is a one-line statement, and reaching for a pipeline to do that would be odd.
What COPY does not give you is everything around the load. Knowing which files have already been read so tomorrow's run only takes the new ones, inferring a schema from CSV or Parquet, retrying a failure, and surfacing a problem in the same monitoring as your other pipelines rather than in a scheduled job somebody wrote two years ago.
So the dividing line is whether this is a load or a feed. A one-off migration is a COPY statement. A bucket a partner writes to every night, where somebody will eventually ask why Tuesday is missing, is a pipeline, and the fact that it uses COPY underneath is an implementation detail you no longer maintain.
Frequently asked questions
Why does this pipeline need two buckets?
One holds your source files and one is staging for the destination, since Redshift is loaded in bulk from S3 with COPY. They are separate buckets with different permissions.
My access key is correct but the connection fails. What now?
Check the source policy names both the bucket ARN and the path beneath it, since object-level permissions alone are not sufficient. Then check which side the error came from, source or staging.
Should I just use COPY instead?
For a one-off load of well-formed files, yes. A pipeline wins when files keep arriving and you need incremental tracking, schema inference, retries and shared monitoring.
Which streams does the S3 source provide?
None predefined. The streams are whatever you define from your bucket's contents, each with a format and a glob pattern selecting its files.
Can I do this without writing code?
Yes. The setup above is UI-driven apart from the IAM policies, which the documentation provides for you to paste. You may want Redshift views casting CSV columns.
Get your Amazon S3 data into Redshift
Provision both buckets before anything else, and write the source policy with both resource entries since that is where first attempts fail. Decide honestly whether this is a one-off load or a feed, define a stream per format with a precise glob pattern, and set sort keys on your filter column.
Airbyte's connector catalog includes 700+ pre-built connectors, so file feeds can reach a warehouse alongside the systems they describe. For the same source in another warehouse, see Amazon S3 to Snowflake, and for object storage on another cloud, Azure Blob Storage to BigQuery.
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.