SOL-C01 Question #267: Real Exam Question with Answer & Explanation

Question

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 considerations and actions, when implemented directly through the Snowflake web UI and related SQL commands, will most effectively improve the performance of queries against this view?

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.

Explanation

Options A and B are the most effective. A materialized view pre-computes and stores the results, significantly improving performance for frequently accessed data, which can be created with 'CREATE MATERIALIZED VIEW' statement through UI . Ensuring proper clustering keys on the base tables is critical for efficient data retrieval, which improves query performance on any views built on them. Options C is incorrect, as using the 'WITH SECURE command encrypts the results, making it less performant. Options D, manual rewriting can be tedious and inefficient compared to allowing Snowflake's optimizer to handle the query. Option E, partitioning is not available with

No community discussion yet for this question.