COF-C03 Practice Question: Performance Optimization, Querying, and Transformation

Answer analysis

Option-by-option breakdown

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

• ✗

  Increase the size of the virtual warehouse to provide more memory for the join operation.

  Why it's wrong here

  Warehouse size affects compute and memory available for spilling, but it cannot correct a semantically wrong join. A Cartesian join is a logical plan issue, not a resource shortage. A larger warehouse will simply process the incorrect cross-product faster, still returning an enormous and wrong result set. Scaling up addresses performance symptoms, not the root cause of missing join keys.

• ✗

  Rewrite the join to use `NATURAL JOIN` so Snowflake automatically infers the join key from matching column names.

  Why it's wrong here

  `NATURAL JOIN` infers equality on all columns with the same name. If the fact table has `dim_id` and the dimension table has `id`, there is no common column name, so the natural join would still produce a Cartesian product. Even if names matched, relying on implicit inference is fragile. The explicit predicate is already correct in the stem; the issue is elsewhere, and this rewrite does not address it.

• ✗

  Add a `CLUSTER BY` clause on the fact table's `dim_id` column to improve join locality.

  Why it's wrong here

  Clustering improves scan pruning for range predicates, but it does not change join semantics. A Cartesian join arises from a missing or ineffective join predicate, not from poor data layout. Adding clustering will not remove the cross-product; the query will still return every fact row paired with every dimension row. Clustering is a performance aid for large-table filtering, not a correctness fix for join logic.

• ✓

  Ensure the join predicate is actually included in the query and that the dimension table's `id` column is not wrapped in a function or cast that prevents hash-join matching.

  Why this is correct

  A Cartesian join in the Query Profile typically means the optimizer could not derive an equi-join condition. This happens when the predicate is missing, commented out, or when one side is wrapped in a non-sargable expression such as `CAST(dim.id AS VARCHAR)`. Verifying the predicate is present and that both sides are directly comparable allows Snowflake to choose a hash join and eliminate the cross-product.