SnowPro Advanced Data Engineer

By Snowflake · Question Mode

QuestionQ77

Design and Manage Snowflake Resources and Performance

A Data Engineer is optimizing end-user query performance for automobile shipment data in a portal web application that produces dashboards for different clients.

An internal team queries this information with order_date as a WHERE condition; ingestion and natural clustering occur on that column. However, the application retrieves data using client_id, warehouse, product_id, and destination_city. The Engineer must create materialized views with different cluster keys and needs to select two columns to combine with client_id for the first cluster key.

Which statements provide all the information required to select the MOST effective column combination for clustering?

Choose two
Explanation

SYSTEM$CLUSTERING_DEPTH can calculate the average clustering depth for any explicitly specified table columns, independent of the table’s defined clustering key; a lower depth indicates more effective clustering for those columns. Evaluating (client_id, warehouse, product_id) and (client_id, destination_city, product_id) supplies the comparable measurements for the two offered candidate keys that include client_id and exactly two additional application query columns. SYSTEM$CLUSTERING_INFORMATION('shipments', 3) uses 3 as the requested number of Automatic Clustering errors, not as a proposed clustering-key definition.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!