Debugging Amazon Redshift COPY Failures: SYS_LOAD_ERROR_DETAIL, MAXERROR and Reject Handling

A failed COPY gives you a one-line error such as Load into table 'orders' failed. Check 'sys_load_error_detail' system table for details. Worse is a load that succeeds while skipping rows because MAXERROR was set. This guide covers a repeatable way to diagnose both.

Step 1: find the load

Every COPY is recorded in SYS_LOAD_HISTORY, which works on provisioned clusters and Serverless.

SELECT query_id, table_name, data_source, loaded_rows, loaded_bytes, status, start_time
FROM sys_load_history
WHERE start_time > dateadd(hour, -6, getdate())
ORDER BY start_time DESC;

Check status and loaded_rows against what you expected. A run with the status completed and fewer rows than the file contains almost always used MAXERROR.

Step 2: read the error detail

SELECT query_id, file_name, line_number, column_name, column_type, error_code, error_message, raw_line
FROM sys_load_error_detail
WHERE query_id = 12345
ORDER BY start_time DESC;

file_name and line_number point at the bad record, column_name at the field, and error_message explains the reason. For long values, raw_line shows the original text.

Common causes and fixes

Symptom	Likely cause	Fix
Delimiter not found	Wrong delimiter, or embedded delimiter in unquoted text	Use CSV with QUOTE AS, or correct DELIMITER
String length exceeds DDL length	VARCHAR is in bytes, not characters	Widen the column, or use TRUNCATECOLUMNS only if truncation is acceptable
Invalid digit / Invalid timestamp	Format mismatch or empty string for a number	Use TIMEFORMAT, EMPTYASNULL, BLANKSASNULL
Extra column(s) found	Header row or schema drift	IGNOREHEADER 1, or list the target columns explicitly
S3 access denied / 403	IAM role missing s3:GetObject or s3:ListBucket, or KMS permission	Fix the role attached to the cluster or namespace
Invalid JSON or Parquet column mismatch	Schema drift in the source files	Use FORMAT AS PARQUET with matching columns, or load into SUPER

Step 3: use MAXERROR deliberately

MAXERROR lets a load continue until the stated number of bad rows is reached. It is useful for exploratory loads, but in production it hides data loss. If you use it:

1. Keep the number small (for example MAXERROR 10), not 100000.
2. After each load, query SYS_LOAD_ERROR_DETAIL for that query_id and alert when any rows appear.
3. Compare loaded_rows with the source row count in your orchestration step.

Step 4: validate before committing with NOLOAD

COPY staging.orders
FROM 's3://my-bucket/incoming/orders/'
IAM_ROLE default
FORMAT AS CSV IGNOREHEADER 1
NOLOAD;

NOLOAD parses the files and reports errors without inserting rows, which is a cheap check on a new feed.

Step 5: a quarantine pattern

For feeds from third parties, load into a staging table with generous column types, then move good rows into the typed target.

CREATE TABLE staging.orders_raw (
  order_id VARCHAR(64), order_ts VARCHAR(64), amount VARCHAR(64), raw_extra VARCHAR(4096)
);

INSERT INTO core.orders
SELECT order_id::BIGINT, order_ts::TIMESTAMP, amount::DECIMAL(12,2)
FROM staging.orders_raw
WHERE order_id ~ '^[0-9]+$' AND amount ~ '^[0-9]+(\.[0-9]+)?$';

INSERT INTO quarantine.orders
SELECT * FROM staging.orders_raw
WHERE NOT (order_id ~ '^[0-9]+$' AND amount ~ '^[0-9]+(\.[0-9]+)?$');

In the pipeline, move the offending S3 files to a rejected/ prefix and notify the owner of the feed. Rejected rows stay queryable for the data provider rather than disappearing.

Checklist

• Never rely on the COPY return alone: check SYS_LOAD_HISTORY row counts.
• Alert on any SYS_LOAD_ERROR_DETAIL rows for the load's query_id.
• Keep MAXERROR low and documented.
• Use NOLOAD for new feeds.
• Stage untrusted data as text, then cast.

If load failures are a recurring pain in your warehouse, our team can review your ingestion design as part of a Redshift performance or administration engagement. Get in touch through the contact page.