Amazon Redshift AUTO Distribution Style for Growing Tables

DEA-C01 By islever Editorial Last updated

Choose a data store.

Short answer

C Use AUTO distribution to let Amazon Redshift dynamically assign the optimal distribution style based on table size and growth, ensuring least maintenance overhead.

Question

A company uses Amazon Redshift as its data warehouse service. A data engineer needs to design a physical data model. The data engineer encounters a de-normalized table that is growing in size. The table does not have a suitable column to use as the distribution key. Which distribution style should the data engineer use to meet these requirements with the LEAST maintenance overhead?
  1. ALL distribution
  2. EVEN distribution
  3. AUTO distribution Correct Answer
  4. KEY distribution

Key Takeaways

The question tests knowledge of Redshift distribution styles, specifically the trap of assuming EVEN or KEY is required when no good key exists, ignoring the dynamic nature of AUTO.

This question addresses how to handle a de-normalized table in Amazon Redshift that lacks a suitable distribution key and is growing in size. The correct approach utilizes the AUTO distribution style to minimize maintenance overhead.

Common mistake: Many candidates choose EVEN distribution because there is no suitable column for KEY distribution, but they overlook that AUTO can dynamically adjust from ALL to KEY/EVEN as data grows, offering less manual intervention.

Expert Analysis

Why the Answer Is Correct

The correct answer is C (AUTO distribution). The AUTO distribution style allows Amazon Redshift to automatically assign an optimal distribution style based on the size of the table data. When a table is small, it may use ALL distribution; as it grows, it may switch to KEY or EVEN. This meets the requirement of 'LEAST maintenance overhead' because the system handles the re-distribution decisions rather than the engineer.

Why the Other Options Are Wrong

EVEN distribution (Option B) spreads rows evenly across slices but does not adapt to growth patterns or join performance needs dynamically. It requires explicit configuration and doesn't offer the automated optimization that AUTO provides. KEY distribution (Option D) requires a specific column to act as the distribution key, which the question states is unavailable. ALL distribution (Option A) replicates the entire table on every slice, leading to excessive storage usage and is only suitable for very small tables, making it unsuitable for a 'growing' table without significant maintenance/reconfiguration.

Community Comment Notes

One user noted that AUTO assigns an optimal style based on size, initially using ALL for small tables and potentially changing to KEY or EVEN as data grows. Another user pointed out that if no suitable column exists, some might assume EVEN, but missed the dynamic capability of AUTO. A third comment highlighted that AUTO changes the distribution style as the table grows, aligning with the 'least maintenance' requirement.

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 asked about 'least maintenance' or automatic scaling in AWS services, look for options that imply automation or dynamic adjustment rather than static configurations. In Redshift, AUTO is designed precisely for scenarios where manual tuning of distribution keys is difficult or impractical due to changing data characteristics.

Frequently Asked Questions

Why isn't EVEN distribution the best choice here?

EVEN distributes rows evenly but doesn't adapt to join patterns or growth dynamically like AUTO. AUTO minimizes maintenance by handling style changes automatically.

Can AUTO distribution change from ALL to KEY?

Yes, AUTO can start with ALL for small tables and switch to KEY or EVEN as data grows, optimizing performance without manual intervention.

More DEA-C01 FAQ →

Community Votes

C
67%
B
33%

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

Discussion 3 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 3

  1. idenrai 👍 2 Answered C
    the LEAST maintenance overhead = C With AUTO distribution, Amazon Redshift assigns an optimal distribution style based on the size of the table data. For example, if AUTO distribution style is specified, Amazon Redshift initially assigns the ALL distribution style to a small table. When the table grows larger, Amazon Redshift might change the distribution style to KEY, choosing the primary key (or a column of the composite primary key) as the distribution key. If the table grows larger and none of the columns are suitable to be the distribution key, Amazon Redshift changes the distribution style to EVEN. The change in distribution style occurs in the background with minimal impact to user queries.
  2. jacob_nz 👍 2 Answered B
    If the table grows larger and none of the columns are suitable to be the distribution key, Amazon Redshift changes the distribution style to EVEN.
  3. emupsx1 👍 2 Answered C
    https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html

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