A junior data engineer is using the Snowflake web UI to create a view based on a complex query against a large dataset. They want to optimize the view for performance. Which of the following considera

Question

Options

• ACreate the view as a materialized view instead of a regular view, using the 'CREATE
• BEnsure that the underlying tables used by the view have appropriate clustering keys defined and
• CUse the `WITH SECURE option when creating the view to prevent unauthorized access, as this
• DManually rewrite the query within the view definition to utilize specific table aliases and join orders
• EPartition the underlying tables based on a relevant date column before creating the view.

• A
  79% (22)
• C
  14% (4)
• D
  4% (1)
• E
  4% (1)

Explanation

Option A is the most impactful optimization: a materialized view pre-computes and physically stores the results of the complex query. Subsequent queries against the materialized view read pre-aggregated, pre-joined data rather than re-executing the expensive transformation each time, dramatically reducing query time. Option B is also valid: if the underlying tables have clustering keys defined on frequently filtered columns, Snowflake's micro-partition pruning skips large portions of data during scans, which benefits both the view's definition query and queries on the view. Option C (WITH SECURE) is a security feature that hides the view's definition from non-privileged users; it has no effect on query performance. Option D is incorrect-Snowflake's query optimizer handles join ordering automatically; manual hints in the view definition do not reliably improve performance. Option E is incorrect-Snowflake uses automatic micro-partitioning, not traditional user-defined table partitioning.

No community discussion yet for this question.