AWS Certified Data Engineer – AssociateData Storage and ManagementMedium
A data engineering team is migrating an on-premises data warehouse to Amazon Redshift. The on-premises warehouse uses a traditional ETL process that loads data nightly into a dimensional model. The new Redshift cluster will continue this nightly batch load. To ensure optimal query performance for analytical queries on large fact tables (billions of rows) with frequently joined dimension tables, what Redshift distribution style should be applied to the largest fact table?
- ADISTSTYLE KEY
- BDISTSTYLE EVEN
- CDISTSTYLE ALL
- DDISTSTYLE AUTO
Show answer & explanationAnswer & explanation
Correct answer: A. DISTSTYLE KEY
For large fact tables in a star schema, `DISTSTYLE KEY` on a column that is frequently used in joins with dimension tables (like a foreign key) is typically the most efficient. This ensures that matching rows from the fact and dimension tables are co-located on the same compute nodes, minimizing data movement (shuffling) during joins and significantly improving query performance.
Why the other options are wrong
- B. DISTSTYLE EVEN distributes data round-robin across all nodes, which is good for tables without frequent joins or when join keys are unknown, but it will lead to data shuffling for joined tables.
- C. DISTSTYLE ALL replicates the entire table to all nodes, which is good for small dimension tables but highly inefficient for large fact tables (billions of rows) due to storage duplication and increased load times.
- D. DISTSTYLE AUTO lets Redshift decide, which might not always be optimal for complex star schemas with known join patterns.
Redshift DISTSTYLE KEY
Amazon Redshift's DISTSTYLE KEY distributes rows of a table across compute nodes based on the hash value of a chosen column. This is crucial for optimizing join performance in data warehouses.
- Co-locates data for efficient joins.
- Choose a column frequently used in join predicates (e.g., foreign key).
- Minimizes data movement (shuffling) during query execution.
- Ideal for large fact tables in star schemas.
- Requires careful selection of the distribution key to avoid data skew.
Memory trick: Key for joins, all for small, even for none, auto for all.