AWS Certified Data Engineer – AssociateData Storage and ManagementMedium
A data engineering team is designing a data warehouse using Amazon Redshift. They have a large fact table containing billions of records, which will be frequently joined with a smaller dimension table. To optimize query performance for these joins, the team needs to choose an appropriate distribution style for the fact table. Which distribution style is most suitable for this scenario?
- AALL distribution
- BEVEN distribution
- CKEY distribution
- DAUTO distribution
Show answer & explanationAnswer & explanation
Correct answer: C. KEY distribution
KEY distribution is ideal when a large fact table is frequently joined with a smaller dimension table on a common key. Distributing data based on the join key ensures that matching rows are co-located on the same compute nodes, minimizing data transfer during query execution.
Why the other options are wrong
- A. ALL distribution copies the entire table to all nodes, suitable for very small dimension tables, not large fact tables.
- B. EVEN distribution spreads data evenly but does not guarantee co-location for joins, leading to more data transfer.
- D. AUTO allows Redshift to choose, but explicit KEY distribution is often better for predictable join performance.
Redshift KEY Distribution
Amazon Redshift KEY distribution ensures that rows with the same value in the specified distribution column are stored on the same compute node. This is crucial for optimizing join performance between large fact tables and dimension tables.
- Distributes data based on a column's value
- Optimizes join performance by co-locating data
- Used for large fact tables joined with dimension tables
- Choose a column with high cardinality for even distribution
Memory trick: Keys Keep Joins Quick.