Loading and unloading Redshift: DEA-C01 study notes and practice questions
What to know about Loading and unloading Redshift for the DEA-C01 exam, from the Data Store Management domain (26% of the exam guide), then 2 free questions to test it. Part of the DEA-C01 study guide.
Loading questions ask for the fastest, safest way to get data into or out of Redshift.
COPY
Loads in parallel across slices. Split input into multiple files, ideally a multiple of the number of slices and of similar size; a single large gzip file can’t be split and loads on one slice.
Sources: S3, DynamoDB, EMR, remote hosts over SSH.
Formats: CSV, JSON, Avro, Parquet, ORC and others.
Authorize with an IAM role attached to the cluster (IAM_ROLE), not access keys in SQL.
A manifest file lists exactly which files to load.
COMPUPDATE and STATUPDATE set compression and statistics.
Errors appear in STL_LOAD_ERRORS (or SYS_LOAD_ERROR_DETAIL); MAXERROR allows a few bad rows.
Auto-copy (COPY JOB) loads new files from an S3 prefix automatically as they arrive.
Upserts (merge)
COPY new data into a staging table.
MERGE into the target (or DELETE matching rows then INSERT) in one transaction.
Drop or truncate the staging table.
Other loading options
INSERT row by row is slow; use COPY or INSERT INTO ... SELECT.
Streaming ingestion from Kinesis Data Streams or MSK into a materialized view, with low latency.
Firehose delivers to Redshift by staging files in S3 and running COPY.
Zero-ETL integrations from Aurora, RDS and DynamoDB.
UNLOAD
Exports query results to S3 in parallel, as Parquet or text, with PARTITION BY, encryption with KMS, and a manifest. Use it to feed the data lake or share extracts.
Common traps
One huge compressed file loaded slowly.
Single-row INSERTs in a loop.
Access keys in COPY statements. Use an attached IAM role.
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.
A logistics broker runs a daily pipeline that moves data between Amazon S3 and Amazon Redshift. The pipeline loads carrier files into a staging table, and it exports a shipment summary back to S3 for a data lake that Athena queries by month.
The team wants the loads to read only the files it intends to load, to avoid stored credentials in SQL, and to write the export in a columnar format partitioned by month.
Which THREE options meet these requirements? (Choose three.)
Choose three answers.
AA. Use COPY with an IAM role attached to the cluster, so the SQL statement does not contain access keys.
BB. Run an INSERT statement for each row in a file, so that each load is limited to the rows the team intends to load.
CC. Use COPY with a manifest that lists the exact objects to load, so stray or partial files in the prefix are not loaded.
DD. Use UNLOAD with plain text output and no partitions, then rename the files in S3 by month with a script after each run.
EE. Use UNLOAD with PARQUET format and PARTITION BY on the month column, so the export writes columnar month prefixes.
FF. Store the access key and secret key in a table in the cluster, and read them into each COPY statement at run time.
D. Wrong: Plain text is row-based, and renaming files by script adds a fragile step. The option does not produce the columnar, partitioned layout the team asked for.
E. Right: Correct. UNLOAD can write Parquet files and partition the output by a column, which creates month prefixes that Athena can prune. The layout matches the data lake's query pattern. Learn more: UNLOAD (opens in a new tab)
F. Wrong: Storing keys in a table keeps long-term credentials in the database, which the team wants to avoid. The option fails the credentials requirement.
Explanation
Three practices meet the stated needs. An IAM role attached to the cluster avoids access keys in SQL, a manifest limits COPY to the files the team intends to load, and UNLOAD with Parquet and PARTITION BY writes a columnar layout partitioned by month. Row-by-row inserts, plain text exports and stored keys each miss one requirement.
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 →