Column order mismatch of an S3 csv file affecting redshift load job

Question

I have an S3 bucket that is populated by csv files from the output of an API run. The problem is that before and after a particular date, my column order is different which is causing the load job to run into error because of invalid data types. Is there any way to fix this issue? 
I first created a temporary table with column names and there is a order in that temporary table, and the older files were in that order, but then after that date, the order changed and it is causing error while running COPY 'table name' FROM 'S3 bucket'

Daniel Buaon · Answer

Redshift loads CSVs by position, not by column name. If the file’s column order changed, COPY will mis-cast values unless you tell Redshift the order.

Fixes:
Run separate COPY commands with an explicit column list for each file version (or use prefixes/manifests):
-- old files
COPY target_table (col1, col2, col3)
FROM 's3://bucket/prefix/old/' CSV IGNOREHEADER 1;

-- new files (order changed)
COPY target_table (col1, col3, col2)
FROM 's3://bucket/prefix/new/' CSV IGNOREHEADER 1;

Stage everything as VARCHAR, then cast/reorder on insert:

CREATE TEMP TABLE stg (a varchar, b varchar, c varchar);
COPY stg FROM 's3://bucket/prefix/all/' CSV IGNOREHEADER 1;
INSERT INTO target_table (col1, col2, col3)
SELECT a::int, c::date, b::varchar FROM stg;  -- reorder + cast

Better long-term: convert to Parquet (or JSON with JSONPaths). Parquet in Redshift maps by column name, so order changes don’t break loads.

There’s no built-in way for COPY to auto-detect/remap CSV columns—use column lists, staging, or a columnar format.

Column order mismatch of an S3 csv file affecting redshift load job

Add your answer

Relevant content