DEA-C01 Data Store Management Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Create a materialized view for each complex query.

  Why it's wrong here

  Materialized views can improve performance for repetitive queries by precomputing results, but they are not a substitute for proper distribution and sort key design. They add storage and maintenance overhead, and may not be suitable for ad-hoc or varying queries. The question focuses on optimizing the underlying table design, not creating precomputed views, so this is not one of the two best actions.

• ✓

  Set the distribution style of small dimension tables to ALL.

  Why this is correct

  ALL distribution replicates small dimension tables to every node, eliminating the need to redistribute data during joins. This is effective for small tables that are frequently joined with large fact tables. It reduces network overhead and improves join performance, but should only be used for tables that are small enough to fit comfortably in node memory and storage.

• ✗

  Use EVEN distribution for all tables to ensure uniform data distribution.

  Why it's wrong here

  EVEN distribution spreads data evenly across nodes, which is useful for tables not involved in joins. However, for join-heavy workloads, EVEN distribution causes data redistribution during joins, increasing network traffic and slowing queries. It is not optimal for large fact tables that join on specific columns, as it does not collocate matching rows, leading to inefficient join execution.

• ✗

  Apply a sort key on the column used in the WHERE clause of frequent queries.

  Why it's wrong here

  While sort keys can improve query performance by enabling efficient range scans, this option is not one of the two best actions for optimizing joins and aggregations. Sort keys are beneficial for filtering and range queries, but distribution styles are more critical for join performance. Additionally, the question asks for actions to improve performance for complex queries with multiple joins; sort keys alone may not address data movement during joins.

• ✓

  Set the distribution style of large fact tables to KEY on the join column.

  Why this is correct

  Using a KEY distribution on the join column for large fact tables collocates matching rows on the same node, minimizing data movement during joins. This reduces network traffic and speeds up join operations, especially for frequently joined large tables. It is a best practice for optimizing join performance in Redshift when the join column has high cardinality and is used in many queries.

Go deeper

Related to this question