Usually the input is one huge file, or many tiny ones. Split data into multiple files of roughly equal size, compress them, keep them in the same region as the cluster, and check that the compute capacity matches the workload.

Amazon Redshift Module 3: Loading Data with COPY and Exporting with UNLOAD

Introduction

The COPY command is the fast, parallel way to load data into Redshift from Amazon S3. Row-by-row INSERT statements are far slower for any real volume. This module loads CSV and Parquet files, handles errors, and uses UNLOAD to export data back to S3.

Prerequisites

Module 2 completed
Data files in an S3 bucket in the same region (or specify the region)

Step-by-Step Guide

Step 1: Give Redshift permission to read S3

Create an IAM role that Redshift can assume with read access to your bucket, and associate it with the namespace or cluster. COPY then uses this role to read the files. Use least privilege: limit the role to the specific bucket and prefix.

-- the role ARN is used in COPY and UNLOAD commands
-- arn:aws:iam::123456789012:role/RedshiftS3AccessRole

Step 2: COPY a CSV file

Point COPY at an S3 prefix, and every file under it is loaded in parallel. Splitting large input into several similar-sized files (for example, many compressed files of 100 MB to a few hundred MB) lets all slices work at once. Tell Redshift about headers, delimiters, dates, and compression.

COPY dim_customer
FROM 's3://my-redshift-lab/input/dim_customer/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3AccessRole'
FORMAT AS CSV
IGNOREHEADER 1
DATEFORMAT 'auto'
REGION 'eu-west-1';

Step 3: COPY Parquet files

Columnar formats such as Parquet load efficiently and carry their own schema. For Parquet, you do not need delimiter options, and columns are matched by position unless you use the column mapping options.

COPY fact_sales
FROM 's3://my-redshift-lab/input/fact_sales/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3AccessRole'
FORMAT AS PARQUET;

Step 4: Find and fix load errors

When COPY fails, Redshift records the reason. Query the load error system views to see the file, line, column, and message. Common causes are wrong delimiters, values that do not fit the column type, and unexpected nulls. Use MAXERROR only for tolerating a small number of known bad rows, and always investigate them.

SELECT * FROM sys_load_error_detail ORDER BY start_time DESC LIMIT 20;

-- older equivalent on provisioned clusters
-- SELECT * FROM stl_load_errors ORDER BY starttime DESC LIMIT 20;

Step 5: Use a staging table and UNLOAD results

A common pattern loads into a staging table, validates or transforms, then merges into the target table inside a transaction. UNLOAD exports a query result to S3 in parallel, often as Parquet, for use by other tools or for archiving.

BEGIN;
  DELETE FROM fact_sales USING stage_sales s WHERE fact_sales.sale_id = s.sale_id;
  INSERT INTO fact_sales SELECT * FROM stage_sales;
COMMIT;

UNLOAD ('SELECT sale_date, sum(amount) AS revenue FROM fact_sales GROUP BY sale_date')
TO 's3://my-redshift-lab/output/daily_revenue_'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3AccessRole'
FORMAT AS PARQUET;

FAQs

Why is my COPY slow?: Usually the input is one huge file, or many tiny ones. Split data into multiple files of roughly equal size, compress them, keep them in the same region as the cluster, and check that the compute capacity matches the workload.

Summary

Use COPY with an IAM role to load S3 files in parallel, split input into similar-sized files, read the load error views when something fails, use a staging table and a transaction to merge data, and UNLOAD to export results to S3.