DEA-C02 Performance Optimization 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.

• ✓

  Ensure the join condition uses the correct columns and is not accidentally omitted.

  Why this is correct

  A Cartesian join warning indicates that the join is producing a Cartesian product, typically because the join condition is missing, always true, or uses incorrect columns. The engineer should verify that the join predicate correctly matches the date_key in the fact table to the primary key in the date dimension. Fixing the join condition eliminates the Cartesian product, reducing the result set to the intended matches and dramatically improving performance. This is the direct solution to the warning.

• ✗

  Add a WHERE clause to filter the date dimension to the same range as the fact table.

  Why it's wrong here

  Filtering the date dimension to the same range might reduce the number of rows in the dimension, but it does not address the Cartesian join. A Cartesian join occurs when the join condition is missing or always true. Filtering does not fix the join condition. While it might reduce the size of the intermediate result, the Cartesian product would still be generated for the remaining rows, leading to incorrect results and poor performance. The root cause is the join predicate, not the data volume.

• ✗

  Add a clustering key on the date dimension's date key.

  Why it's wrong here

  Clustering the date dimension might improve join performance if the dimension were large, but it is already small. More importantly, clustering does not resolve a Cartesian join. The warning indicates a missing or incorrect join condition, which clustering cannot fix. The query would still produce a Cartesian product, leading to incorrect results and excessive resource consumption. The engineer should focus on correcting the join predicate instead.

• ✗

  Increase the warehouse size to handle the larger result set.

  Why it's wrong here

  Increasing warehouse size provides more compute resources, but it does not fix the underlying issue of a Cartesian join. A Cartesian product can produce an enormous number of rows, potentially overwhelming any warehouse size and leading to incorrect results. Scaling up might temporarily handle the load, but it is not a correct solution because the query logic is flawed. The engineer must first correct the join condition to avoid the Cartesian product.