Step2Study
AWS Certified Data Engineer – AssociateData Storage and ManagementMedium

A data engineering team is migrating an on-premises data warehouse to Amazon Redshift. The source system has a customer table with a `customer_id` column that is frequently used in JOIN operations with other large fact tables. To optimize query performance in Redshift, particularly for queries involving this `customer_id`, which distribution style should be applied to the customer table?

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

Correct answer: C. DISTSTYLE KEY

When a table is frequently joined with other large tables on a specific column, using DISTSTYLE KEY on that column (the join key) ensures that rows with matching join key values are stored on the same compute node. This minimizes data movement across the network during joins, significantly improving query performance.

Why the other options are wrong

  • A. DISTSTYLE EVEN distributes rows round-robin across all compute nodes. This is suitable for tables that are not frequently joined or where no clear join key exists, but it doesn't optimize for specific join operations.
  • B. DISTSTYLE ALL copies the entire table to all compute nodes. While it can improve join performance for small dimension tables, it's inefficient for large fact or dimension tables due to increased storage and load times.
  • D. DISTSTYLE AUTO lets Redshift decide the distribution style. While often good, for critical performance optimizations with known join patterns, explicitly defining KEY distribution is usually better.

Redshift DISTSTYLE KEY

A Redshift table distribution style that distributes rows based on the hash of values in a specified column, commonly used to co-locate data for efficient joins.

  • Optimizes join performance
  • Co-locates matching join keys on the same compute node
  • Reduces data movement across the network
  • Best for large fact tables joined with large dimension tables

Memory trick: Key is the 'Key' to fast joins; Even is 'Even' distribution; All is 'All' over.

Practice this question Study as flashcard All questions
Next question

More Data Storage and Management questions