Load data into Amazon Redshift efficiently using COPY from S3

Steps

1. Stage your data files in S3 in the same AWS region as your Redshift cluster; split large files into multiple roughly equal-sized files (one per Redshift slice, typically 1–3 files per node) to maximize parallel loading
2. Prefer columnar compressed formats: Parquet or ORC for mixed workloads, or gzip/snappy-compressed CSV for simple pipelines; COPY auto-detects gzip and bz2 compression
3. Run the COPY command: COPY <table> FROM 's3://<bucket>/<prefix>' IAM_ROLE '<role-arn>' FORMAT AS PARQUET; for CSV use DELIMITER, IGNOREHEADER, and DATEFORMAT options as needed; the IAM role must have s3:GetObject and s3:ListBucket on the source bucket
4. Use a manifest file (JSON listing S3 object URLs) for COPY when loading specific files rather than an entire prefix; this prevents accidentally loading new files added to the prefix mid-load
5. After large COPY operations, run VACUUM and ANALYZE on the target table to reclaim space from deleted rows and update query planner statistics; Redshift does not auto-vacuum during active COPY loads
6. Monitor load errors in the STL_LOAD_ERRORS system table and load progress in STV_LOAD_STATE; errors are not surfaced in the COPY return value — always check STL_LOAD_ERRORS after a load