Apply Clustering and Materialized Views ARA-C01 Practice Questions (Page 3)

Questions 11–15

1. 11application · medium

   A data engineer notices that a query on a clustered table still scans many micro-partitions even though the filter column is part of the clustering key. The clustering depth is healthy. What is the most likely reason for the excessive scanning?

   • AThe warehouse is too small
   • BThe filter uses a function on the clustering column, such as DATE_TRUNC('month', order_date)
   • CThe clustering key was defined on the wrong column
   • DThe table is too large
   Reveal Solution Discussion Select an answer first
2. 12expert · hard

   A table stores web clickstream data with columns `session_id`, `event_time`, `user_id`, and `page_url`. Queries often filter on `user_id` and a time range, but also sometimes aggregate by `page_url` over a time period. The table is very large and receives continuous inserts. Which optimization strategy would provide the best overall performance?

   • ACluster on (user_id, event_time) and create a materialized view for page_url aggregations
   • BCreate a materialized view for the user_id and time range queries
   • CCluster on (event_time, user_id) and rely on automatic clustering
   • DUse a larger warehouse for all queries
   Reveal Solution Discussion Select an answer first
4. 13foundation · easy

   In which scenario is a materialized view more appropriate than clustering?

   • AWhen the table is small and rarely queried
   • BWhen queries filter on a high-cardinality column with range predicates
   • CWhen queries repeatedly perform the same expensive aggregation on a large table
   • DWhen the table is frequently updated with new rows
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   Which SQL statement is used to create a materialized view in Snowflake?

   • ACREATE MATERIALIZED VIEW
   • BCREATE SNAPSHOT
   • CCREATE TABLE AS SELECT
   • DCREATE VIEW AS MATERIALIZED
   Reveal Solution Discussion Select an answer first
6. 15application · medium

   A materialized view `mv_sales_summary` has been created on a sales table. A user runs a query that exactly matches the view's definition. How does Snowflake handle this query?

   • AThe query is automatically rewritten to use the materialized view
   • BThe query fails because the materialized view is not referenced
   • CThe query runs against the base table, and the materialized view is ignored
   • DThe user must explicitly hint to use the materialized view
   Reveal Solution Discussion Select an answer first