The short answer
Attempting to extract large datasets from Redshift using a client-side SQL tool (such as SELECT * in Python via psycopg2) routes gigabytes through the single leader node and client memory, causing connection timeouts and memory crashes.
The proper, scalable way to export data from Redshift to Amazon S3 is the UNLOAD command. UNLOAD executes the query in parallel across all compute slices, streaming files directly to an S3 bucket.
UNLOAD ('SELECT customer_id, order_date, amount FROM sales WHERE year = 2024')
TO 's3://analytics-export-bucket/sales_2024/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftExportRole'
FORMAT AS PARQUET
PARTITION BY (order_date)
MANIFEST;Key parameters and best practices
- FORMAT AS PARQUET: Older pipelines exported uncompressed CSV or GZIP text. Exporting as Parquet automatically preserves schema datatypes, compresses data with Snappy, and makes files immediately queryable by downstream Spark or Athena engines.
- PARTITION BY: Redshift can automatically output files into Hive-style directory structures (
order_date=2024-01-01/) in S3 based on column values. - PARALLEL ON: Enabled by default. Each slice writes its own files concurrently, producing multiple part files. If downstream consumers strictly require a single unified file, set
PARALLEL OFF, but note that all rows will be funneled through the leader node, slowing the export. - MANIFEST: Writing a manifest file outputs a small file listing every generated data part file, preventing downstream consumers from reading partial files during ongoing exports.