How to Stop Redshift Broadcasting a Store Location Table?

DEA-C01 By islever Editorial Last updated Oct 20, 2026

Design data models and schema evolution.

Short answer

A Set the Redshift store location table to ALL distribution so it is replicated to every compute node and joins no longer require broadcasting.

Question

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?

Change the distribution style of the store location table from EVEN distribution to ALL distribution. Correct Answer
Change the distribution style of the store location table to KEY distribution based on the column that has the highest dimension.
Add a join column named store_id into the sort key for all the tables.
Upgrade the Redshift reserved node to a larger instance size in the same instance family.

Key Takeaways

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.

Common mistake: 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.

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

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

This link opens the official vendor documentation in a new tab.

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.

More DEA-C01 FAQ →

Community Votes

100%

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

Discussion 4 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 4

PGGuy 👍 4 Answered A

Changing the distribution style of the store location table to ALL distribution (A) is the most cost-effective solution. It directly addresses the issue of broadcasting by ensuring the entire table is available on each node, significantly improving join performance without incurring substantial additional costs.
bakarys 👍 2 Answered A

The most cost-effective solution to speed up the query performance by minimizing the broadcasting of the store location table would be: A. Change the distribution style of the store location table from EVEN distribution to ALL distribution. In Amazon Redshift, the ALL distribution style replicates the entire table to all nodes in the cluster, which eliminates the need to redistribute the data when executing a query. This can significantly improve query performance. Given that the store location table is updated only once or twice every few years, the overhead of maintaining the replicated data would be minimal. This makes it a cost-effective solution for improving the query performance.
tgv 👍 2 Answered A

Using ALL distribution means the table is replicated to all nodes, eliminating the need for broadcasting during queries. Since the store location table is updated infrequently, this will significantly speed up queries without incurring frequent update costs.
andrologin 👍 2 Answered A

ALL distribution is optimal for slowly changing dimension tables and generally small in size to allow for optimal joins.

Reader comments & corrections

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

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