Amazon Redshift ALL Distribution Style for Small Tables

DEA-C01 By islever Editorial Last updated

Automate data processing by using AWS services.

Short answer

C Use the ALL distribution style for rarely updated small tables to eliminate data redistribution during joins.

Question

A company's data engineer needs to optimize the performance of table SQL queries. The company stores data in an Amazon Redshift cluster. The data engineer cannot increase the size of the cluster because of budget constraints. The company stores the data in multiple tables and loads the data by using the EVEN distribution style. Some tables are hundreds of gigabytes in size. Other tables are less than 10 MB in size. Which solution will meet these requirements?
  1. Keep using the EVEN distribution style for all tables. Specify primary and foreign keys for all tables.
  2. Use the ALL distribution style for large tables. Specify primary and foreign keys for all tables.
  3. Use the ALL distribution style for rarely updated small tables. Specify primary and foreign keys for all tables. Correct Answer
  4. Specify a combination of distribution, sort, and partition keys for all tables.

Key Takeaways

The exam tests knowledge of Redshift table design, specifically that the ALL distribution style is optimal for small, rarely updated tables to avoid data redistribution during joins.

This question addresses optimizing Amazon Redshift query performance without scaling cluster size by selecting the correct distribution style for tables of varying sizes.

Common mistake: Learners often select Option D because it sounds comprehensive, but specifying partition keys is not a standard Redshift optimization technique in this context, and primary/foreign keys do not drive distribution.

Expert Analysis

Why the Answer Is Correct

Option C is the correct solution because the ALL distribution style replicates the entire table on every node in the cluster. For small tables (less than 10 MB), this overhead is negligible, but it eliminates the need to redistribute data during join operations, significantly boosting performance. The constraint 'rarely updated' is critical because updating an ALL-distributed table requires modifying copies on all nodes, which is expensive.

Why the Other Options Are Wrong

Option A is incorrect because EVEN distribution spreads data randomly, causing massive data movement (shuffle) during joins between large and small tables. Option B is inefficient because storing full copies of large tables on every node wastes storage and increases maintenance costs unnecessarily. Option D is invalid because Amazon Redshift does not support partition keys; it uses sort keys for ordering and distribution keys for placement.

Community Comment Notes

Community consensus strongly supports C, with users noting that 'ALL distribution is appropriate only for relatively slow moving tables'. One commenter highlighted that this approach 'eliminates the need for data redistribution or shuffling', while another asked 'why not D?', pointing out that some learners mistakenly believe partitioning exists in Redshift.

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

Always check table size and update frequency when choosing distribution styles. Use ALL for small lookup tables (<10MB) and EVEN or KEY for large fact tables.

Frequently Asked Questions

Why can't we use partition keys in Redshift?

Amazon Redshift does not support partitioning like other databases; it uses distribution keys for data placement and sort keys for I/O optimization.

When should I use KEY distribution instead of EVEN?

Use KEY distribution when joining large tables on common columns to keep related data together on the same node, reducing shuffle overhead.

More DEA-C01 FAQ →

Community Votes

C
100%

100% of anonymous learners picked answer C. 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

  1. rralucard_ 👍 8 Answered C
    Use the ALL Distribution Style for Rarely Updated Small Tables. This approach optimizes the performance of joins involving these smaller tables and is a common best practice in Redshift data warehousing. For the larger tables, maintaining the EVEN distribution style or considering a KEY-based distribution (if there are common join columns) could be more appropriate.
  2. pypelyncar 👍 3 Answered C
    For small tables (less than 10 MB in size) that are rarely updated, using the ALL distribution style can provide better query performance. With the ALL distribution style, each compute node stores a copy of the entire table, eliminating the need for data redistribution or shuffling during certain queries. This can significantly improve query performance, especially for joins and aggregations involving small tables.
  3. DevoteamAnalytix 👍 2 Answered C
    "ALL distribution is appropriate only for relatively slow moving tables; that is, tables that are not updated frequently or extensively." (https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html)
  4. jk15997 👍 3
    why not D?

Reader comments & corrections

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

Log in to comment, report an error, or add a note about this question.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

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