Balancing Redshift Compute Node Load with Distribution Keys

Automate data processing by using AWS services.

Answer Correct answer: B — Change the distribution key to the table column that has the largest dimension to ensure even data distribution across nodes.

A company uses an Amazon Redshift provisioned cluster as its database. The Redshift cluster has five reserved ra3.4xlarge nodes and uses key distribution. A data engineer notices that one of the nodes frequently has a CPU load over 90%. SQL Queries that run on the node are queued. The other four nodes usually have a CPU load under 15% during daily operations. The data engineer wants to maintain the current number of compute nodes. The data engineer also wants to balance the load more evenly across all five compute nodes. Which solution will meet these requirements?

Change the sort key to be the data column that is most often used in a WHERE clause of the SQL SELECT statement.
Change the distribution key to the table column that has the largest dimension. Correct Answer
Upgrade the reserved node from ra3.4xlarge to ra3.16xlarge.
Change the primary key to be the data column that is most often used in a WHERE clause of the SQL SELECT statement.

Community Votes

100%

100% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The exam tests knowledge of Redshift data distribution styles, specifically the trap of confusing sort keys (filtering) with distribution keys (parallelism).

This question addresses how to resolve CPU skew in Amazon Redshift by selecting an optimal distribution key. It establishes that choosing a column with high cardinality ensures even data spread across nodes.

Many candidates select Option A, believing that optimizing the sort key for WHERE clauses will balance CPU load, but sort keys only affect scan efficiency, not data placement.

Community Discussion (5 comments)

rralucard_ 👍 7 Selected: B

https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html Option B, changing the distribution key, is the most effective solution to balance the load more evenly across all five compute nodes. Selecting an appropriate distribution key that aligns with the query patterns and data characteristics can result in a more uniform distribution of data and workloads, thus reducing the likelihood of one node being overutilized while others are underutilized.

pypelyncar 👍 2 Selected: B

In a Redshift cluster with key distribution, data is distributed across compute nodes based on the values of the distribution key. An uneven distribution can lead to skewed workloads on specific nodes. By choosing the table column with the largest dimension (most distinct values) as the distribution key, you ensure a more even spread of data across all nodes. This balances the processing load on each node when queries access that column.

khchan123 👍 2 Selected: B

The correct solution is B. Change the distribution key to the table column that has the largest dimension. This will help to distribute the data more evenly across the nodes, reducing the load on the heavily utilized node.

Christina666 👍 1 Selected: A

Gemini result: Understanding the Problem: The scenario describes a Redshift cluster with uneven load distribution. This indicates potential issues with either the distribution style or the sort key. Key Distribution: The problem states that the cluster uses key distribution, meaning a specific column is designated as the distribution key. Data rows with matching distribution key values are placed on the same node. Sort Key: A sort key determines the order in which data is physically stored within a table's blocks on a node. A well-chosen sort key can significantly optimize query performance, especially when queries often filter by that column.

damaldon 👍 2

B. With "Key distribution". The rows are distributed according to the values in one column. The leader node places matching values on the same node slice. If you distribute a pair of tables on the joining keys, the leader node collocates the rows on the slices according to the values in the joining columns. This way, matching values from the common columns are physically stored together. https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Amazon Redshift distributes rows across compute nodes based on the hash of the distribution key. When one node is overloaded while others are idle, it indicates data skew caused by a poor distribution key choice (e.g., low cardinality). By changing the distribution key to a column with the largest dimension (highest cardinality/most distinct values), you maximize the randomness and uniformity of data placement, thereby balancing the workload across all five ra3.4xlarge nodes without adding hardware.

Why the Other Options Are Wrong

Option A suggests changing the sort key. Sort keys determine the physical order of data on disk to optimize range scans and filters (WHERE clauses), but they do not influence which node stores which row. Option C involves upgrading node size, which violates the requirement to maintain the current number of compute nodes and does not fix the underlying skew issue. Option D mentions a primary key; while Primary Key constraints exist in Redshift Spectrum or external tables, they do not dictate internal data distribution in provisioned clusters.

Community Comment Notes

The community consensus strongly supports Option B, with users noting that "Selecting an appropriate distribution key... can result in a more uniform distribution of data." One user highlighted that "rows are distributed according to the values in one column," reinforcing that the distribution key is the lever for load balancing. Another comment clarified that matching values are placed on the same slice, so high-cardinality columns prevent clustering on specific nodes.

Official Reference

https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html

Exam Strategy

When asked to balance load across Redshift nodes, always look for 'distribution key' solutions first. Remember: Distribution keys handle parallelism/load balancing, while Sort keys handle query filtering performance.

Frequently Asked Questions

Why doesn't changing the sort key balance CPU load?

Sort keys optimize data retrieval speed for filtered queries but do not determine which compute node stores the data rows.

What defines the 'largest dimension' in this context?

It refers to the column with the highest cardinality (most unique/distinct values), which minimizes data skew when hashed.

More DEA-C01 FAQ →

Related Analysis

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide