For years the standard way to load files into Redshift was a scheduled COPY: a cron job, an Airflow task, or a Lambda triggered by S3 events that ran COPY and then recorded which files it had loaded so the next run would not load them again. Every team wrote that bookkeeping slightly differently and every team eventually double-loaded a file.
Redshift's auto-copy feature (COPY JOB) makes the bookkeeping the database's problem. You define a COPY once, mark it as a job, and Redshift loads every new object that appears under the S3 prefix, exactly once, tracking the files it has seen. This tutorial covers how to lay out the files, define the job and monitor it.
Legacy scheduled COPY vs COPY JOB
| Scheduled COPY | COPY JOB | |
|---|---|---|
| Trigger | cron / orchestrator / Lambda | S3 object creation (via event notifications Redshift sets up) |
| Dedup of loaded files | your manifest logic | built in |
| Latency | schedule interval | seconds to a few minutes after the file lands |
| Failure handling | your retry logic | job keeps running; errors in SYS_LOAD_ERROR_DETAIL |
| Works on | provisioned + Serverless | provisioned + Serverless |
Keep scheduled COPY when you need a transaction that spans several tables (COPY JOB loads one table), when files are rewritten in place (a job loads an object once; an overwritten key is a new object and loads again), or when the load must wait for a downstream condition.
File layout that loads fast
COPY parallelizes across files, so the shape of what you put in S3 matters more than the COPY options.
- Many files, not one. Aim for file counts that are a multiple of the slice count on a provisioned cluster, or simply hundreds of files on Serverless. A single 20 GB file loads on one slice.
- Size files between roughly 100 MB and 1 GB after compression. Thousands of 1 KB files spend the load in S3 list and open calls.
- Compress. Gzip for text, or use Parquet, which is columnar and compressed by default. Parquet also lets COPY skip columns you do not load.
- One prefix per target table, with date-partitioned subfolders:
s3://my-lake/orders/2026/08/21/part-0001.parquet. The job watches the prefix recursively. - Write files atomically. Upload to a staging prefix and copy to the watched prefix, or use multipart upload so a partial object is never visible.
Define the job
First, a normal COPY that you have tested by hand:
CREATE TABLE orders (
order_id BIGINT,
customer_id BIGINT,
order_ts TIMESTAMP,
status VARCHAR(16),
amount DECIMAL(12,2)
);
COPY orders
FROM 's3://my-lake/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET;
Then make it a job by appending JOB CREATE:
COPY orders
FROM 's3://my-lake/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET
JOB CREATE orders_autocopy
AUTO ON;
AUTO ON means the job runs automatically on new objects. AUTO OFF creates a job you trigger yourself with COPY JOB RUN orders_autocopy, which is useful for backfills: point the job at the prefix, run it once, and switch to AUTO ON afterwards.
The IAM role needs s3:GetObject and s3:ListBucket on the prefix, and Redshift needs to be able to receive S3 event notifications for the bucket. The documentation lists the bucket policy statement; it is a one-time setup per bucket.
Existing objects under the prefix at job creation time are not loaded automatically, which is almost always what you want for a table that already has history. For a fresh table, run COPY JOB RUN once for the backfill.
Operate the job
-- What jobs exist and whether they are active
SELECT job_name, job_id, job_state, data_source, copy_query
FROM SYS_COPY_JOB;
-- Pause / resume
COPY JOB ALTER orders_autocopy AUTO OFF;
COPY JOB ALTER orders_autocopy AUTO ON;
-- Remove (does not delete loaded data)
COPY JOB DROP orders_autocopy;
Monitor with SYS_LOAD_HISTORY
Every load the job performs shows up in SYS_LOAD_HISTORY, with the job's copy_job_id set, so you can separate automatic loads from manual ones.
SELECT start_time, table_name, status,
file_count, lines_scanned, data_size / 1048576.0 AS mb,
duration / 1000000.0 AS seconds
FROM SYS_LOAD_HISTORY
WHERE copy_job_id IS NOT NULL
ORDER BY start_time DESC
LIMIT 20;
Rejected rows and files that failed to parse land in SYS_LOAD_ERROR_DETAIL:
SELECT start_time, file_name, line_number, column_name, error_message
FROM SYS_LOAD_ERROR_DETAIL
WHERE start_time > DATEADD(day, -1, GETDATE())
ORDER BY start_time DESC;
For alerting, schedule that query in Query Editor v2 or run it from Lambda and publish a CloudWatch metric when the count is non-zero. A job that has silently stopped loading is the failure mode to watch for: alarm on no successful loads in N hours for tables that should be busy.
Downstream: from raw to modeled
Auto-copy gets data into a landing table. Deduplication across late files, type coercion and merging into a modeled table are still yours, and a good pattern is a MERGE scheduled in Query Editor v2 or run by dbt:
MERGE INTO orders_curated USING orders_landing src
ON orders_curated.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET status = src.status, amount = src.amount
WHEN NOT MATCHED THEN INSERT VALUES (src.order_id, src.customer_id, src.order_ts, src.status, src.amount);
Truncate or age out the landing table on a schedule so it does not become a second copy of history.
When to use something else
If the source is an Aurora, RDS or DynamoDB database rather than files, a zero-ETL integration removes the files entirely. If the source is a stream, streaming ingestion skips S3 as well. Auto-copy is the right tool when files are the contract: vendor drops, exports from SaaS tools, and lake zones written by Spark.
Need help untangling a pile of legacy COPY scripts? That is a standard part of our Data Integration & Migration service.