AWS Redshift Support
Loading data into Redshift with COPY from S3.
COPY is the fastest way to load Redshift because it reads files in parallel across slices. Slow loads usually mean one big file, and failures mean bad data, permissions or format mismatches. We fix both and show you how to read the load error tables.
When You Need This
The situations where this work pays for itself.
Signs it is time
- COPY is slow, using one file or a few large ones
- COPY fails with Load into table failed or a permissions error
- Duplicate or missing rows appear after loads
- You need an automated S3 to Redshift pipeline
- Loads are blocking queries or taking longer each week
What you get
- Corrected COPY commands and file layout
- A staging and merge pattern that avoids duplicates
- Error handling and alerting for failed loads
- Automated load pipeline in infrastructure as code
How we do it
A clear, safe process you can follow along with.
1. Check permissions and access
COPY uses an IAM role attached to the cluster or Serverless namespace, with s3:GetObject and s3:ListBucket on the source and KMS access if encrypted. Access errors almost always trace to this role or the bucket policy.
2. Shape the files for parallelism
A single large file loads on one slice. We split data into many similar-sized compressed files, a multiple of the slice count where possible, and use a manifest to load exactly the intended files.
3. Pick the format
We prefer Parquet or ORC for typed, columnar data, or gzip-compressed delimited files. We set options for delimiter, quotes, null handling, date formats and header rows to match the data.
4. Read load errors
When COPY fails we query STL_LOAD_ERRORS or SYS_LOAD_ERROR_DETAIL for the file, line, column and reason, then fix the data or the options. MAXERROR is a stopgap, not a fix.
5. Load into a staging table
We COPY into a staging table, validate, then merge into the target in one transaction to avoid duplicates and partial loads.
6. Keep compression and statistics healthy
For empty tables COPY can pick compression automatically, and we make sure statistics are updated after big loads so queries plan well.
7. Automate
We set up automatic ingestion with COPY JOB (auto-copy) or a scheduled pipeline, with failure alerts and idempotent reruns.
Mistakes we help you avoid
Common ways this goes wrong, and what we do instead.
Loading one giant file
Parallelism is lost and the load runs on a single slice.
Using MAXERROR to hide bad data
Rows are silently skipped.
Loading straight into the final table
A failed or repeated load leaves partial or duplicate data.
Frequently Asked Questions
Why is my Redshift COPY so slow?
Often the data is in one or few files so it cannot load in parallel. Split into many compressed files of similar size and load through a prefix or manifest.
How do I see why a Redshift COPY failed?
Query STL_LOAD_ERRORS or the SYS_LOAD_ERROR_DETAIL view for the file name, line number, column and error reason.
Can Redshift load from S3 automatically?
Yes. Auto-copy with COPY JOB can ingest new files from an S3 prefix as they arrive, and you can also schedule COPY commands from your own pipeline.
Want Help With Loading data into Redshift with COPY from S3?
Schedule an appointment. Tell us about your environment and we will outline the safest, highest-value approach.
Schedule Appointment › No commitment required