AWS Certified Data Engineer – AssociateData Storage and ManagementMedium
A data analytics team uses Amazon Redshift for their data warehousing needs. They frequently run complex analytical queries that involve large joins and aggregations on tables with billions of rows. The current query performance is slow, and the team suspects that data distribution is a bottleneck. Which Redshift table design strategy should the data engineer explore to improve query performance significantly?
- AIncrease the number of `VACUUM` operations to reorganize data.
- BImplement `SORTKEY` on columns used in `WHERE` clauses for filtering.
- CSet the `DISTSTYLE` to `ALL` for all large fact tables.
- DUse `DISTSTYLE KEY` on columns frequently used in join predicates between large tables.
Show answer & explanationAnswer & explanation
Correct answer: D. Use `DISTSTYLE KEY` on columns frequently used in join predicates between large tables.
Using `DISTSTYLE KEY` on join columns ensures that matching rows from large tables are co-located on the same compute nodes, minimizing data movement across the network during joins, which is a major performance bottleneck for Redshift.
Why the other options are wrong
- A. `VACUUM` reclaims space and reorganizes data after deletions/updates, which can help with general performance but doesn't directly address distribution for join optimization.
- B. `SORTKEY` improves performance for filtering and range scans, but `DISTSTYLE` directly addresses the data distribution for joins, which is identified as the primary bottleneck.
- C. `DISTSTYLE ALL` copies the entire table to all nodes, which is suitable only for small dimension tables, not large fact tables due to storage and load overhead.
Redshift DISTSTYLE KEY
Amazon Redshift `DISTSTYLE KEY` distributes data rows across compute nodes based on the values in a specified column, aiming to co-locate related data for efficient joins.
- Distributes data based on a column's hash value
- Optimizes join performance by co-locating data
- Reduces data movement across network during joins
- Crucial for large fact tables involved in frequent joins
Memory trick: Distribute by Key for joins to make queries fly.