A property-management company loads about 2 TB of daily lease and payment data into Amazon Redshift with a COPY command. The upstream system writes the entire day into one very large gzip-compressed file in S3, and the COPY takes almost five hours, which is close to the team's loading window.
The team wants the load to finish well inside the window, with the same cluster size and the same target table.
Which change meets these requirements?
Choose one answer.
AA. Keep the single gzip file, and add COMPUPDATE OFF and STATUPDATE OFF to the COPY command so that it skips its analysis steps.
BB. Replace the COPY command with a series of single-row INSERT statements that run from a script on a schedule.
CC. Split the output into many similar-size compressed files, a multiple of the slice count, and load them with one COPY.
DD. Add more nodes to the cluster, and keep the single gzip file, so the COPY has more compute to use across all the nodes.
Show the answer and explanation
Answer: C
A. Wrong: Skipping compression analysis and statistics updates can save a little time, but the single gzip file is still read by one slice. The load stays mostly serial, so the option does not fix the parallelism problem.
B. Wrong: Single-row inserts are far slower than COPY for bulk data, and they add load to the cluster. The option works against the loading window.
C. Right: Correct. COPY loads files in parallel across the slices, so many similar-size files let every slice work at once. A count that is a multiple of the slices keeps the work even, and one COPY from the prefix keeps the command simple. Learn more: Amazon Redshift best practices for loading data (opens in a new tab)
D. Wrong: Extra nodes add slices, but one compressed file still cannot be divided among them. The option adds cost without removing the serial read.
Explanation
COPY loads in parallel across the cluster's slices, but it cannot split one gzip-compressed file, so a single large file serializes the load. Splitting the output into many similar-size compressed files, with a count that is a multiple of the slices, spreads the work evenly. Skipping analysis steps, row-by-row inserts and extra nodes do not change that serial read.
Want more on Loading and unloading Redshift? The 5 full-length practice sets have 10 more Loading and unloading Redshift questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →