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.

• ✓

  Use `EXPLAIN` to inspect the query plan and identify steps that process disproportionately large row counts.

  Why this is correct

  `EXPLAIN` provides the logical execution plan without running the query, showing operators and estimated costs. By inspecting the plan, the engineer can spot steps like Cartesian joins, unnecessary aggregations, or missing filters that cause large intermediate results. This diagnostic step guides targeted optimizations such as adding predicates or rewriting joins. It is a best practice for understanding where the optimizer is spending effort and where data volume explodes.

• ✗

  Replace all joins with `UNION ALL` to combine the tables into a single result set.

  Why it's wrong here

  `UNION ALL` concatenates result sets and does not perform relational joins. It would produce incorrect results because it does not match rows on keys. It also increases the row count rather than reducing it, which would worsen performance. This is not a valid optimization for a query that requires joining tables on common dimensions; it fundamentally changes the semantics and is inappropriate here.

• ✓

  Apply filters as early as possible in the query, ideally in a subquery or CTE, to reduce the row count before joins.

  Why this is correct

  Pushing filters down to the earliest possible point reduces the number of rows that participate in joins and aggregations. Snowflake's optimizer can sometimes do this automatically, but complex queries with CTEs or subqueries may benefit from explicit early filtering. This directly decreases the data volume flowing through the Join and Aggregate nodes, lowering both time and resource consumption. It is a fundamental optimization technique for multi-join queries.

• ✗

  Increase the warehouse size to a 4X-Large to provide more memory for the joins.

  Why it's wrong here

  Scaling up the warehouse can help with memory-intensive operations and reduce spilling, but it does not reduce the amount of data processed. The query will still read the same number of rows and perform the same joins. It may run faster due to more compute resources, but it does not address the root cause of large intermediate results. It is a costly approach that does not optimize the query itself.

• ✗

  Convert all `JOIN` clauses to `CROSS JOIN` to allow the optimizer more flexibility.

  Why it's wrong here

  `CROSS JOIN` produces a Cartesian product, multiplying row counts and dramatically increasing data volume. It does not allow the optimizer to find better join paths; instead, it removes the join predicates and forces the worst-case scenario. This would make the query far slower and return incorrect results. It is never an optimization technique for reducing data processed in a multi-table join.