Optimize Warehouse and Query Performance ARA-C01 Practice Questions (Page 5)

Questions 21–25

1. 21application · medium

   A query joins a large fact table with a small dimension table and runs slowly. The EXPLAIN plan shows that the query is performing a hash join and scanning the entire fact table. The dimension table is only 1,000 rows. What is the most likely optimization to improve performance?

   • AAdd a clustering key on the fact table's join column.
   • BIncrease the warehouse size to a 2XL.
   • CRewrite the query to use a broadcast join hint.
   • DCreate a materialized view on the fact table.
   Reveal Solution Discussion Select an answer first
2. 22expert · hard

   A table stores IoT sensor readings with columns: SENSOR_ID, TIMESTAMP, and VALUE. The table is loaded with new data every 5 minutes, and queries often filter on TIMESTAMP for the last hour. The table currently has no clustering key, and queries scan many micro-partitions. The team wants to improve pruning without adding significant maintenance overhead. What is the best approach?

   • ADefine a clustering key on TIMESTAMP and let Snowflake auto-cluster the table.
   • BUse the SEARCH OPTIMIZATION SERVICE on the TIMESTAMP column.
   • CCreate a materialized view that aggregates data by minute.
   • DManually re-cluster the table every hour using ALTER TABLE ... CLUSTER BY.
   Reveal Solution Discussion Select an answer first
4. 23foundation · easy

   Which Snowflake caching layer stores metadata about tables, such as the number of rows and the range of values in micro-partitions, to help queries avoid scanning unnecessary data?

   • AMetadata cache
   • BIndex cache
   • CQuery result cache
   • DData cache
   Reveal Solution Discussion Select an answer first
5. 24expert · hard

   A table stores web clickstream data with a high insert rate. Queries often filter on a USER_ID column that has high cardinality. The table currently has no clustering key, and queries scan many micro-partitions. The team wants to improve pruning for USER_ID queries without incurring high auto-clustering costs. What is the best approach?

   • AManually re-cluster the table daily.
   • BCreate a materialized view on USER_ID.
   • CDefine a clustering key on USER_ID and enable auto-clustering.
   • DEnable the SEARCH OPTIMIZATION SERVICE on USER_ID.
   Reveal Solution Discussion Select an answer first
6. 25foundation · easy

   In a query that joins a large fact table with a small dimension table, which join strategy is most likely to be used by Snowflake to avoid shuffling the large table?

   • AHash join
   • BMerge join
   • CNested loop join
   • DBroadcast join
   Reveal Solution Discussion Select an answer first