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.

• ✗

  Change the distribution style to ALL for all large tables.

  Why it's wrong here

  Using ALL distribution copies the entire table to every node, which increases storage and load time. For large tables, this is inefficient and can degrade performance due to higher data movement during loads. The engineer already set KEY distribution on join columns, which is appropriate for large tables; switching to ALL would be counterproductive.

• ✗

  Enable concurrency scaling to handle concurrent queries.

  Why it's wrong here

  Concurrency scaling adds transient clusters to handle bursts of concurrent queries, improving throughput. However, it does not optimize the performance of a single complex join query. The requirement is to improve join performance, not to handle more concurrent users.

• ✓

  Set the sort key on the columns used in the join and in the WHERE clause.

  Why this is correct

  Sort keys enable efficient range scans and merge joins by co-locating data on disk in sorted order. When joining large tables, having the join columns as sort keys can allow Redshift to use merge joins instead of hash joins, reducing data movement and improving performance. This is a best practice for join optimization.

• ✗

  Increase the number of nodes in the Redshift cluster to add more compute resources.

  Why it's wrong here

  Adding nodes increases compute and storage capacity, but it does not optimize the join itself. Without proper sort keys, the join may still require significant data movement across nodes. Scaling is a costly solution that does not address the underlying query execution efficiency for joins.

Go deeper

Related to this question