How to Load Data into Amazon Redshift

The short answer for beginners

If you are new to Redshift and just need to get data in, here is the default path:

1. Upload your data files to an S3 bucket
2. Create an IAM role that allows Redshift to read from that bucket
3. Attach the role to your Redshift cluster or namespace
4. Run a COPY command that points at the S3 path

That is it. Everything else on this page — Parquet vs CSV, manifest files, Glue, Firehose — builds on that foundation. Start there if you are not sure where to begin.

How Redshift loading works

Almost every Redshift load follows the same path, regardless of whether the source is a database, an application, or a flat file export:

1. Source system: data originates from a database (RDS, DynamoDB), an application log, or an export from another service
2. S3 staging: the data lands in Amazon S3 as one or more files (CSV, JSON, Parquet, ORC, or Avro)
3. IAM role: your Redshift cluster has an IAM role attached that grants read access to the S3 bucket
4. COPY command: you run COPY in Redshift, which reads the S3 files in parallel across all compute nodes
5. Validate and troubleshoot: if the load succeeds, confirm the row count; if it fails, check STL_LOAD_ERRORS

S3 sits in the middle because Redshift’s COPY command is built to read from it at high throughput. The cluster’s MPP architecture means each node reads a different subset of your files simultaneously, which is why COPY scales with both data volume and cluster size.

When to load data into Redshift

Good use cases

• Bulk loads from S3: one-time or recurring exports from another system, uploaded to S3 and loaded with COPY
• Batch pipelines: nightly or hourly ETL jobs that move data from operational databases into the warehouse for reporting
• Streaming ingestion: events delivered via Amazon Data Firehose, which stages in S3 and issues COPY automatically
• Transformed loads: data read from RDS, reshaped with AWS Glue or another ETL tool, and written to Redshift

When Redshift is the wrong tool

Redshift is not an OLTP database. If your application needs to write individual rows in real time (recording user events, updating order status, serving API responses), use RDS or DynamoDB for that. Redshift is designed for analytical queries on pre-collected data, not live application writes.

If you are deciding whether Redshift or a data lake is the right landing zone, the data warehouses vs data lakes guide covers the decision in detail.

Setting up the IAM role for COPY

Before you can use COPY, your Redshift cluster needs an IAM role attached to it. The cluster uses this role to authenticate to S3. You cannot pass your own access keys to COPY.

Step 1: Create the IAM role

aws iam create-role \
  --role-name RedshiftS3LoadRole \
  --assume-role-policy-document '{
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Principal": { "Service": "redshift.amazonaws.com" },
      "Action": "sts:AssumeRole"
    }]
  }'

aws iam attach-role-policy \
  --role-name RedshiftS3LoadRole \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess

In production, replace the managed policy with an inline policy that restricts access to your specific bucket rather than all of S3.

Step 2: Attach the role to the cluster

# Provisioned cluster
aws redshift modify-cluster-iam-roles \
  --cluster-identifier my-cluster \
  --add-iam-roles arn:aws:iam::123456789012:role/RedshiftS3LoadRole

# Redshift Serverless: attach to the namespace instead
aws redshift-serverless update-namespace \
  --namespace-name my-namespace \
  --iam-roles arn:aws:iam::123456789012:role/RedshiftS3LoadRole

COPY from CSV

CSV is the most common format for simple loads. Point COPY at an S3 prefix and it loads all files under that path:

COPY orders (order_id, customer_id, order_date, region, product_id, quantity, unit_price, total_amount)
FROM 's3://my-data-bucket/orders/2025/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS CSV
IGNOREHEADER 1
DATEFORMAT 'YYYY-MM-DD'
TIMEFORMAT 'auto'
MAXERROR 10
REGION 'us-east-1';

Key options:

• IGNOREHEADER 1 skips the header row in each file
• MAXERROR 10 allows up to 10 rows to fail before COPY aborts. Useful during initial data exploration, but remove it once your data is clean.
• REGION is required when the S3 bucket and the cluster are in different AWS regions

COPY from compressed CSV

Redshift automatically decompresses gzip, bzip2, lzo, and zstd files. Just declare the compression type:

COPY orders
FROM 's3://my-data-bucket/orders/2025/data.csv.gz'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS CSV
IGNOREHEADER 1
GZIP;

Compressed files transfer faster from S3 and cost less to store. For recurring loads, compressing CSV files with gzip or zstd is worth the extra step at the source.

COPY from Parquet

Parquet is a columnar file format. It stores data in a compressed, typed format that Redshift can read more efficiently than CSV because column types are already encoded in the file.

COPY orders
FROM 's3://my-data-bucket/orders/parquet/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS PARQUET;

No IGNOREHEADER, DATEFORMAT, or delimiter options are needed. Parquet files embed schema information, so Redshift does significantly less parsing work. Parquet also supports predicate pushdown in Redshift Spectrum queries, letting you query data directly in S3 without loading it into a table.

COPY from JSON

JSON loads use either auto-detection or a jsonpaths file that maps JSON keys to table columns. Auto works when your JSON keys exactly match your column names:

COPY orders
FROM 's3://my-data-bucket/orders/json/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS JSON 'auto';

If JSON keys do not match column names, create a jsonpaths file in S3 and reference it:

{
  "jsonpaths": [
    "$.orderId",
    "$.customerId",
    "$.orderDate",
    "$.totalAmt"
  ]
}

COPY orders (order_id, customer_id, order_date, total_amount)
FROM 's3://my-data-bucket/orders/json/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS JSON 's3://my-data-bucket/config/orders_jsonpaths.json';

Using manifest files

A manifest file is a JSON document that explicitly lists which S3 files to load. Use it when you want to load a specific set of files rather than everything under a prefix. This is useful for loading only the files produced by a specific pipeline run.

{
  "entries": [
    {"url": "s3://my-data-bucket/orders/2025/q1/part-001.csv", "mandatory": true},
    {"url": "s3://my-data-bucket/orders/2025/q1/part-002.csv", "mandatory": true},
    {"url": "s3://my-data-bucket/orders/2025/q1/part-003.csv", "mandatory": true}
  ]
}

COPY orders
FROM 's3://my-data-bucket/manifests/orders_q1_2025.manifest'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS CSV
IGNOREHEADER 1
MANIFEST;

Setting “mandatory”: true causes COPY to fail if any listed file is missing. Without this, a missing file is silently skipped, which is a common source of subtle data quality bugs in production pipelines.

Loading with AWS Glue

When your data needs transformation before it reaches Redshift (reading from RDS, joining tables, renaming columns, filtering records), AWS Glue is the right tool. Glue is a managed Spark environment for running ETL jobs without managing infrastructure.

A Glue job reads from the source, applies transformations, writes the result to an S3 staging location, and issues the COPY command to Redshift automatically. You do not write the COPY command yourself. If you are deciding whether to transform before or after loading, the ETL vs ELT guide covers the trade-offs.

Loading with Amazon Data Firehose

Amazon Data Firehose (formerly Kinesis Data Firehose) is the lowest-effort way to get streaming data into Redshift. You configure a delivery stream with Redshift as the destination, and Amazon Data Firehose handles everything in between.

Under the hood, Amazon Data Firehose buffers incoming records in S3, then automatically issues a COPY command at a configurable interval (60 seconds to 15 minutes). You configure:

• The Redshift cluster endpoint and database
• The target table and column mapping
• The S3 staging bucket where records are buffered before loading
• The COPY options to use
• The IAM role for Firehose to access both S3 and Redshift

The trade-off is latency. Data is not available in Redshift the moment it arrives. If you need sub-minute latency or more control over processing logic, look at Kinesis Data Streams with a Lambda consumer instead.

How it works in practice: the common load path

Here is a complete end-to-end walkthrough of the standard Redshift load. See Running Your First Redshift Query for a hands-on version that also covers creating the table.

1. Data lands in S3

Upload your data files to an S3 bucket under a consistent prefix, for example s3://my-bucket/orders/2025/05/. For large datasets, split into multiple files so Redshift can load them in parallel.

2. Confirm the IAM role is attached

In the AWS console, open the Redshift cluster or serverless namespace and check the IAM roles tab. The role must have s3:GetObject and s3:ListBucket on the target bucket.

3. Run COPY

COPY orders
FROM 's3://my-data-bucket/orders/2025/05/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3LoadRole'
FORMAT AS PARQUET;

4. Verify the result

SELECT COUNT(*) FROM orders;

SELECT order_date, COUNT(*) AS row_count
FROM orders
GROUP BY order_date
ORDER BY order_date DESC
LIMIT 5;

5. Inspect load errors if needed

SELECT filename, line_number, colname, raw_field_value, err_reason
FROM STL_LOAD_ERRORS
ORDER BY starttime DESC
LIMIT 20;

COPY vs INSERT

For any significant data volume, COPY is the right choice. INSERT is fine for adding a handful of rows manually, but it does not scale.

Choosing a file format: CSV, Parquet, and JSON

AWS Glue vs Amazon Data Firehose

The choice comes down to whether you need transformation logic. If you are just moving data from a source to Redshift with no reshaping, Firehose is simpler. If you need to join, filter, or reformat data before it lands in the warehouse, Glue gives you the flexibility to do that. The Designing Data Pipelines guide covers end-to-end pipeline architecture with S3, Glue, and Redshift.

Performance and reliability best practices

Split large files for parallel loading

Redshift loads files in parallel: each slice in the cluster reads a different file simultaneously.

Avoid loading many tiny files

The parallel loading benefit disappears when files are too small. Hundreds of kilobyte-sized files create overhead without meaningful parallelism. Combine tiny files in S3 before loading, or use a manifest file to load them all in a single COPY command.

Use compression

Compressed files transfer faster from S3 and cost less to store. Gzip and zstd are good choices for CSV files. Parquet applies column-level compression automatically.

Prefer Parquet for recurring pipelines

Parquet reduces COPY parsing overhead, supports predicate pushdown for Spectrum queries, and is generally more compact than compressed CSV for columnar data. For ad-hoc or one-off loads where you cannot control the source format, CSV is fine.

Run ANALYZE after large loads

Redshift’s query planner uses statistics about each table to build efficient query plans. After loading a large batch of new data, run:

ANALYZE tablename;

Stale statistics cause the planner to choose poor execution strategies, such as a full table scan when a more selective plan would be much faster. See Redshift Performance Optimisation for a full guide to VACUUM, ANALYZE, and workload management.

Troubleshooting load failures

When a COPY command fails or only partially loads, Redshift stores the details in system tables. Start with STL_LOAD_ERRORS:

SELECT
  filename,
  line_number,
  colname,
  type,
  col_length,
  raw_field_value,
  err_reason
FROM STL_LOAD_ERRORS
ORDER BY starttime DESC
LIMIT 20;

For character-level detail on encoding issues, also check STL_LOADERROR_DETAIL:

SELECT
  d.bytenum,
  d.raw_field_value,
  e.err_reason
FROM STL_LOADERROR_DETAIL d
JOIN STL_LOAD_ERRORS e ON d.query = e.query
ORDER BY d.bytenum;

Common COPY errors and what they usually mean

• “Invalid digit, Value ‘N’, Pos 0, Type: Integer” — a column Redshift expects to be numeric contains a text value, such as the string NULL or an empty field. Check for stray text or NULLs in numeric columns.

• “Extra column(s) found” — the file has more columns than the table definition. Either list the expected columns explicitly in the COPY statement, or update the table schema to match the file.

• “Date out of range” — the date format in the file does not match what COPY expects. Try DATEFORMAT ‘auto’ to let Redshift infer the format.

• “Delimiter not found” — the file uses a different delimiter than COPY assumed (the default is a pipe |, not a comma). Set DELIMITER ’,’ explicitly for comma-separated files.

• “S3ServiceException: Access Denied” — the IAM role attached to the cluster does not have read access to the S3 bucket or the specific prefix. Check the role’s policy and confirm the bucket name and path are correct.