A ride-sharing company keeps 4 billion trip rows in an Amazon Redshift fact table that uses city_code as its distribution key. Most trips come from five large cities. Queries that join trips to a riders table on rider_id run slowly, and one node runs at high CPU while the others are mostly idle.
The team must confirm the cause, then fix it without recreating the table or adding nodes.
Which TWO actions meet these requirements? (Choose two.)
Choose two answers.
AA. Resize the cluster to add two nodes, so that the new slices take over part of the work from the busy node.
BB. Query the SVV_TABLE_INFO system view for the trips table, and check the skew_rows ratio between its fullest and emptiest slices.
CC. Run ALTER TABLE with ALTER DISTKEY rider_id on the trips table, and distribute the riders table on rider_id as well.
DD. Change the trips table to DISTSTYLE ALL, so that every node holds a full copy and the join never moves rows.
EE. Run VACUUM SORT ONLY on the trips table, so that its rows are sorted again and the busy slice reads fewer blocks.
Show the answer and explanation
Answers: B, C
A. Wrong: The team ruled out adding nodes. Rows are still placed by the hash of city_code, so the five large cities keep landing on a few slices and the skew remains.
B. Right: Correct. The skew_rows column is the ratio of rows in the slice with the most rows to the slice with the fewest. A high value confirms that the distribution key concentrates the table on a few slices. Learn more: SVV_TABLE_INFO (opens in a new tab)
C. Right: Correct. ALTER DISTKEY changes the distribution key of an existing table without recreating it. Rider IDs have many values, so rows spread evenly, and matching keys on both tables keep the join local to each slice. Learn more: ALTER TABLE (opens in a new tab)
D. Wrong: ALL distribution suits small dimension tables. Copying 4 billion rows to every node multiplies storage and slows every load, which is far worse than the current skew.
E. Wrong: Sorting changes the order of rows within each slice, not which slice holds them. The busy slice would still hold most of the rows.
Explanation
One busy node and idle others point to distribution skew. SVV_TABLE_INFO shows it directly through skew_rows, and ALTER DISTKEY moves the table to a high-cardinality key that also matches the join, without recreating the table. Adding nodes, copying the fact table everywhere or re-sorting does not change where the rows live.
Want more on Redshift table design? The 5 full-length practice sets have 9 more Redshift table design questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →