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

Questions 6–10

1. 6application · medium

   A data engineer is tuning a large fact table (5 TB) that is queried frequently with a filter on the `order_date` column. The table is not clustered. Query profiling shows that queries filtering on `order_date` scan far more micro-partitions than necessary. Which action would most directly improve pruning for these queries?

   • AChange the data type of `order_date` from DATE to TIMESTAMP.
   • BCreate a view that filters the table on `order_date`.
   • CDefine a clustering key on `order_date` and run a `REGROUP` to reorganize the table.
   • DIncrease the warehouse size to 2X-Large to scan the table faster.
   Reveal Solution Discussion Select an answer first
2. 7application · medium

   A company's BI team uses a Medium warehouse for interactive dashboards. During peak hours (9 AM–11 AM), 30–40 concurrent queries run, causing queuing and slow dashboard load times. Off-peak, only 2–3 queries run. The team wants to eliminate queuing during peak hours without wasting credits off-peak. Which configuration should they use?

   • ASet the warehouse to X-Large with auto-suspend set to 5 minutes.
   • BCreate a second separate Medium warehouse and manually route half the BI users to each.
   • CConfigure multi-cluster mode with min=1 and max=4 clusters, keeping the Medium size.
   • DKeep the warehouse as a single Medium cluster and increase the statement timeout in the session.
   Reveal Solution Discussion Select an answer first
4. 8foundation · easy

   Which technique can improve query pruning in Snowflake?

   • AUsing the smallest possible data types for columns.
   • BCreating indexes on frequently filtered columns.
   • CUsing the COPY command to load data in parallel.
   • DIncreasing the warehouse size to scan data faster.
   Reveal Solution Discussion Select an answer first
5. 9foundation · easy

   What is a micro-partition in Snowflake?

   • AA small, immutable storage unit that contains a subset of a table's rows.
   • BA temporary storage area used during query execution.
   • CA partition of a table that must be manually created by the user.
   • DA logical grouping of rows that can be updated independently.
   Reveal Solution Discussion Select an answer first
6. 10application · medium

   A table `events` stores 2 billion rows with a `user_id` (VARCHAR) and `event_time` (TIMESTAMP). Queries frequently filter on `user_id = 'abc123'` and `event_time > '2024-01-01'`. The table is currently clustered on `event_time`. Query profiling shows that the `user_id` filter is not pruning well. Which change would most improve pruning for these queries?

   • AConvert `user_id` to a NUMBER data type to reduce storage.
   • BChange the clustering key to `user_id, event_time`.
   • CCreate a separate table for each `user_id` to avoid filtering.
   • DKeep the clustering key on `event_time` but add a secondary index on `user_id`.
   Reveal Solution Discussion Select an answer first