How to Stop Redshift Broadcasting a Store Location Table?
A retail company stores transactions, store locations, and customer information tables in four reserved ra3.4xlarge Amazon Redshift cluster nodes. All three tables use even table distribution. The company updates the store location table only once or twice every few years. A data engineer notices that Redshift queues are slowing down because the whole store location table is constantly being broadcast to all four compute nodes for most queries. The data engineer wants to speed up the query performance by minimizing the broadcasting of the store location table. Which solution will meet these requirements in the MOST cost-effective way?
Community Votes
100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
The question tests whether you can match Redshift distribution styles to table size and update frequency, and the trap is assuming KEY distribution or a bigger node will always fix broadcast joins.
This DEA-C01 scenario tests how Amazon Redshift distribution styles affect join performance for a small, rarely updated dimension table. The page establishes that changing the store location table to ALL distribution is the correct, cost-effective answer because it eliminates repeated broadcasting without adding nodes.
Many candidates choose KEY distribution (B) because they associate distribution keys with join performance, but the option uses the highest-cardinality column rather than the actual join key, and ALL distribution is better for a tiny, static dimension.
Community Discussion (4 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
The store location table is a classic small, slowly changing dimension: it is updated only once or twice every few years, yet it is joined often enough that EVEN distribution forces Redshift to broadcast it to all four compute nodes. Switching it to ALL distribution stores a complete copy on every node, so those joins can execute locally and the repeated network broadcast disappears. Because the table is tiny and almost static, the extra storage and refresh cost is negligible compared with the query-latency gain, and no larger reserved nodes are required. This makes ALL distribution the most cost-effective fix rather than an infrastructure upgrade.Why the Other Options Are Wrong
Changing to KEY distribution can help only when the distribution column matches the common join key; option B instead picks the column with the highest dimension, which is not guaranteed to be the join column and may still leave some joins needing redistribution. Adding store_id to the sort key (C) can improve range-restricted scans and merge joins, but it does not change how rows are distributed across nodes, so it does not eliminate broadcast. Upgrading to a larger reserved node (D) adds ongoing cost and leaves the inefficient EVEN distribution in place, so it treats the symptom rather than the cause.Community Comment Notes
PGGuy focuses on the fact that ALL distribution directly addresses the issue of broadcasting without substantial added cost, which aligns with the cost-effective requirement. andrologin describes ALL distribution as optimal for slowly changing dimension tables that are small enough to replicate. bakarys and tgv make the same point: replication removes the need to redistribute or broadcast the store location table during joins, and the infrequent updates make the approach cheap.Official Reference
Exam Strategy
When a question mentions repeated broadcast of a small, slowly changing dimension table in Redshift, map that symptom to ALL distribution; reserve KEY for large tables whose join key is known and EVEN for tables without a clear distribution advantage. Remember that cost-effective means avoid node upgrades unless the current hardware is the actual bottleneck.
Frequently Asked Questions
Why is KEY distribution not better for the store location table?
KEY distribution only helps when the chosen column is the frequent join key; the option selects the highest-cardinality column, and a small dimension can still need redistribution.
Does ALL distribution increase storage cost in Redshift?
Yes, it stores a full copy on every node, but for a small table updated once or twice every few years the write and storage overhead is negligible compared with repeated broadcasts.
Related Analysis
Practice All DEA-C01 Questions
Access 100 questions with complete answers and detailed explanations.
View Full DEA-C01 Practice Test →