Question

A data engineering team loads a large fact table into Amazon Redshift nightly from thousands of small gzip CSV files stored in an S3 prefix. The load currently uses one COPY command per file issued serially, and the nightly job is missing its SLA. The cluster has 8 slices. What is the MOST effective change to improve load throughput while keeping costs low?

Accepted Answer

Combine the many small files into a smaller number of larger files (roughly a multiple of the number of slices) and use a single COPY command pointing at the S3 prefix or manifest

Answer

Load the CSV files into the table using multi-row INSERT statements generated by a Lambda function instead of COPY

Answer

Issue each per-file COPY command in parallel from many concurrent database sessions to maximize connection usage

Answer

Enable concurrency scaling on the cluster so that additional clusters absorb the write load during the nightly job