Step2Study
AWS Certified Data Engineer – AssociateData Storage and ManagementHard

A data analytics team uses Amazon Redshift for their data warehousing needs. They frequently run queries that aggregate data across various tables, and some of these queries involve joining a large fact table with a relatively small dimension table (e.g., a few hundred rows). To optimize query performance and minimize data movement during these specific join operations, which distribution style should be applied to the small dimension table?

  1. ADISTSTYLE AUTO
  2. BDISTSTYLE ALL
  3. CDISTSTYLE KEY
  4. DDISTSTYLE EVEN
Show answer & explanation

Correct answer: B. DISTSTYLE ALL

For small dimension tables that are frequently joined with large fact tables, `DISTSTYLE ALL` is the most efficient choice. It copies the entire dimension table to every compute node, allowing Redshift to perform local joins without requiring data movement across the network. This significantly improves query performance for such joins.

Why the other options are wrong

  • A. DISTSTYLE AUTO lets Redshift decide, but for a known pattern like small dimension table joins, `DISTSTYLE ALL` explicitly provides the best optimization.
  • C. DISTSTYLE KEY is used for large tables that are frequently joined on a specific key to co-locate data. It's not optimal for small dimension tables where copying the entire table is more efficient.
  • D. DISTSTYLE EVEN distributes rows randomly, which would require data redistribution (broadcast or hash join) for every join operation with a fact table, leading to poor performance for small dimension tables.

Redshift DISTSTYLE ALL

A Redshift table distribution style that copies the entire table to every compute node, typically used for small dimension tables to optimize join performance.

  • Copies full table to all compute nodes
  • Eliminates data movement for joins with large fact tables
  • Best for small dimension tables (few hundred MBs/thousands of rows)
  • Increases storage footprint, so not suitable for large tables

Memory trick: For small tables, 'ALL' the nodes need 'ALL' the data for super-fast joins.

Practice this question Study as flashcard All questions
Next question

More Data Storage and Management questions