DEA-C01 Data Store Management Practice Question

A company runs an Amazon Redshift cluster with 10 RA3 nodes. The data warehouse stores 50 TB of data. The company notices that queries are slow and the cluster's storage utilization is high. The data engineer needs to improve query performance and reduce storage costs without changing the cluster's node count. Which action should the engineer take?

⚠ Common exam trap

Many candidates confuse concurrency scaling (which improves query throughput) with storage optimization, or they assume that changing distribution styles (like DISTSTYLE ALL) will always improve performance, ignoring the storage cost impact in a high-utilization scenario.

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 Redshift Spectrum to offload historical data to Amazon S3 and query it in place.

Redshift Spectrum allows you to query data directly from Amazon S3 without loading it into the cluster. By offloading historical or less-frequently accessed data to S3, you reduce the storage utilization on the RA3 nodes, which frees up managed storage and can improve query performance. This approach also lowers storage costs because S3 is cheaper than Redshift managed storage, and it does not change the node count.

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 Redshift Spectrum to offload historical data to Amazon S3 and query it in place.
Why this is correct
Redshift Spectrum queries external tables in Amazon S3 directly, so historical data leaves RA3 managed storage without altering node count. This lowers storage utilisation and cost while letting the cluster focus compute on hot data, addressing both stated constraints.
✗
Change the distribution style of large tables to DISTSTYLE ALL.
Why it's wrong here
DISTSTYLE ALL replicates the whole table to every node, multiplying storage across all 10 nodes and worsening the high utilisation. It is tempting because it eliminates redistribution broadcasts for small dimension tables, which is its actual purpose, but large tables are exactly what it must not be applied to.
✗
Migrate the cluster to Dense Compute node types.
Why it's wrong here
Dense Compute nodes use instance-attached SSD rather than managed storage, so 50 TB cannot be held without adding nodes, which the stem forbids. It is tempting because DC nodes can suit performance-focused workloads, but RA3's managed storage is what decouples capacity from compute here.
✗
Enable concurrency scaling to handle more concurrent queries.
Why it's wrong here
Concurrency scaling adds transient clusters for bursts of concurrent queries; it does not reduce storage utilisation or speed up individual slow queries. It is tempting because it is the standard remedy for query queuing, but the stem describes slow queries and high storage, not concurrency pressure.

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