DEA-C01 Data Store Management Practice Question

A company uses Amazon Redshift for analytics. The data engineer notices that queries are slow due to many small inserts. Which technique would improve write performance?

⚠ Common exam trap

It's easy for candidates to confuse performance tuning for reads (DISTKEY/SORTKEY) or general scaling (adding nodes) with the specific write performance bottleneck caused by many small inserts, overlooking the COPY command as the primary solution for bulk data loading.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Use the COPY command to load data from Amazon S3.

The COPY command is the recommended way to load data into Amazon Redshift because it performs bulk inserts in parallel across all nodes, leveraging the cluster's distributed architecture. Small individual INSERT statements cause high overhead due to transaction logging and commit processing, leading to slow write performance. By loading data from Amazon S3 using COPY, you bypass these per-row overheads and achieve optimal throughput.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✓
Use the COPY command to load data from Amazon S3.
Why this is correct
Row-by-row inserts force Redshift to commit many small transactions, which is inefficient. COPY loads large batches in parallel from Amazon S3 directly into the cluster, dramatically improving write throughput and reducing the overhead of many small inserts.
✗
Define DISTKEY and SORTKEY on the table.
Why it's wrong here
DISTKEY and SORTKEY govern data distribution and sort order for read queries, not insert batching; small inserts still incur per-statement overhead. They are tempting because they optimise scan and join performance, and would be correct when queries are slow from poor pruning or redistribution rather than write volume.
✗
Increase the number of nodes in the cluster.
Why it's wrong here
Adding nodes increases storage and compute capacity for reads, but each small insert still commits individually, so per-statement overhead remains unchanged. It is tempting because scaling is the usual remedy for resource-bound workloads, and would be correct if the cluster were genuinely CPU- or memory-saturated during query execution.
✗
Configure workload management (WLM) queues.
Why it's wrong here
WLM queues allocate memory and concurrency across query types; they govern read-side resource contention and do nothing for row-by-row insert overhead. The fix is batching small inserts into larger COPY or multi-row transactions. WLM would be right when concurrent query workloads starve each other.

Quick reference

AWS S3 Storage Class Comparison

Storage Class	Min Duration	Retrieval	Use Case
S3 Standard	None	Immediate	Frequently accessed data
S3 Standard-IA	30 days	Immediate	Infrequent access, rapid retrieval
S3 One Zone-IA	30 days	Immediate	Non-critical infrequent data
S3 Intelligent-Tiering	None	Immediate–hours	Unknown or changing access patterns
S3 Glacier Instant	90 days	Milliseconds	Archive with instant retrieval
S3 Glacier Flexible	90 days	Minutes–hours	Archive, flexible retrieval
S3 Glacier Deep Archive	180 days	Hours	Long-term compliance archive

Go deeper

DEA-C01 Data Store Management Practice Question

Why each option matters

Option-by-option breakdown

AWS S3 Storage Class Comparison

Related to this question