Size Warehouses and Use Query Pruning DEA-C02 Practice Questions (Page 3)

Questions 11–15

1. 11foundation · easy

   How does ordering data in a table improve query pruning?

   • AIt allows Snowflake to use indexes on the ordered columns.
   • BIt ensures that each micro-partition contains a narrower range of values for the ordered columns.
   • CIt compresses the data, making it faster to scan.
   • DIt reduces the number of micro-partitions in the table.
   Reveal Solution Discussion Select an answer first
2. 12foundation · easy

   What is query pruning in Snowflake?

   • AA technique to remove duplicate rows from query results.
   • BA feature that automatically rewrites queries to use more efficient join algorithms.
   • CA method to compress data to reduce storage costs.
   • DA process that reduces the number of micro-partitions scanned by using metadata to skip irrelevant ones.
   Reveal Solution Discussion Select an answer first
4. 13application · medium

   A startup runs a mix of workloads on a single Snowflake account: a nightly ETL job (1–2 hours, heavy compute), a daily reporting query (10 minutes, moderate), and occasional ad-hoc analyst queries (seconds). They want to minimize credit consumption while keeping the ETL job from slowing down the reporting query. What is the best warehouse strategy?

   • AUse one Large warehouse for all workloads, relying on auto-suspend.
   • BUse a single Medium warehouse with multi-cluster mode (min=1, max=2).
   • CRun everything on a single X-Small warehouse to minimize credits.
   • DCreate two warehouses: a Large for ETL and a Small for reporting and ad-hoc queries.
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   In Snowflake, how does increasing the warehouse size from X-Small to Small affect the compute resources and credit consumption?

   • AIt doubles the compute resources per node but keeps credit consumption per second unchanged.
   • BIt doubles the compute resources per node and doubles the credit consumption per second.
   • CIt doubles the number of compute nodes, thereby doubling the credit consumption per second.
   • DIt quadruples the number of compute nodes and the credit consumption per second.
   Reveal Solution Discussion Select an answer first
6. 15application · medium

   A table `sales` is clustered on `region` and `sale_date`. A query filters on `sale_date` only (no `region` filter). Query profiling shows poor pruning. What is the most likely cause and the best fix?

   • AThe table is too large; switch to a larger warehouse to improve pruning.
   • BThe clustering key order is suboptimal; reorder the key to `sale_date, region`.
   • CDisable automatic clustering and manually sort the table by `sale_date`.
   • DThe query is missing a `REGION` filter; add `WHERE region = 'all'` to the query.
   Reveal Solution Discussion Select an answer first