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

Questions 11–15

1. 11expert · hard

   A company has a single warehouse used for both interactive dashboards and nightly batch ETL. During the day, dashboard queries are fast, but at night the ETL jobs cause the warehouse to queue and the dashboards become slow. The company wants to ensure dashboard performance is not affected by the ETL workload. What is the best approach?

   • ASet a resource monitor on the single warehouse to limit ETL queries.
   • BCreate a separate warehouse for the ETL workload and set a resource monitor on it.
   • CEnable multi-cluster on the single warehouse with a minimum of 2 clusters.
   • DIncrease the size of the single warehouse to a 2XL.
   Reveal Solution Discussion Select an answer first
2. 12foundation · easy

   A table is frequently queried with filters on the ORDER_DATE column. To improve query performance by enabling more efficient micro-partition pruning, what should be defined on the table?

   • AA primary key on ORDER_DATE
   • BA unique constraint on ORDER_DATE
   • CA clustering key on ORDER_DATE
   • DA foreign key on ORDER_DATE
   Reveal Solution Discussion Select an answer first
4. 13foundation · easy

   Which technique can reduce the cost of repeated aggregations on the same data?

   • AUsing a larger warehouse for each query.
   • BPre-aggregating the data into a summary table or materialized view.
   • CAdding more clustering keys to the table.
   • DDisabling the query result cache.
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   What is the primary goal of join optimization in Snowflake?

   • ATo minimize the amount of data shuffled between nodes during the join.
   • BTo ensure that all joins are performed on the same node.
   • CTo use the largest warehouse possible for the join.
   • DTo avoid using indexes during the join.
   Reveal Solution Discussion Select an answer first
6. 15expert · hard

   A query joins two large tables, A and B, on a column that is not unique in either table. The EXPLAIN plan shows a hash join with a large amount of data shuffling. The tables are both clustered on the join column. Which optimization is most likely to reduce the shuffle and improve performance?

   • AIncrease the warehouse size to a 4XL.
   • BAdd a clustering key on the join column of both tables.
   • CUse a merge join hint.
   • DRewrite the query to filter more aggressively before the join.
   Reveal Solution Discussion Select an answer first