Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performancePractice Questions: Part 2

Practice Questions: Part 2

Practice Questions · Part 2

Thirty questions in the exam's format: single choice and multiple select, no topic hints. Answer each one, then read the explanation even when you got it right.

Practice questions

  1. A resource monitor on BI_WH has ON 100 PERCENT DO SUSPEND. When the quota is reached, a 20-minute aggregation is halfway through on BI_WH. What happens to it?

    1. It keeps running, since SUSPEND only sends a notification to admins
    2. It's cancelled at once, and the user must submit it again later
    3. It finishes, and then the warehouse suspends and refuses new queries
    4. It's paused and resumes automatically when the monthly quota resets
  2. An Enterprise edition account runs dashboards on REPORTING_WH, a Medium warehouse. Every weekday from 09:00 to 10:00, hundreds of short queries queue, although each one runs fast once it starts. Which change fixes this at the lowest cost?

    1. Enable the Query Acceleration Service on REPORTING_WH
    2. Resize REPORTING_WH to 2X-Large and keep it that size all day
    3. Make REPORTING_WH a multi-cluster warehouse in Auto-scale mode
    4. Define clustering keys on all the tables that the dashboards read
  3. Which two queries are typical candidates for the Query Acceleration Service? (Choose two.)

    1. An INSERT … SELECT that scans a large table and writes millions of rows
    2. A point lookup that reads 3 rows from a small dimension table by its key
    3. A large scan whose filter depends on RANDOM()
    4. A query joining two tables of a few thousand rows each
    5. A month-end rollup that scans 2 TB and aggregates it into 200 rows
  4. A data engineer runs ALTER TABLE shipments ADD SEARCH OPTIMIZATION ON EQUALITY(tracking_no, status) on a 3 TB SHIPMENTS table. About 80% of shipments have the status 'DELIVERED'. Which query does this speed up most directly?

    1. SELECT * FROM shipments WHERE tracking_no = '1Z9X42';
    2. SELECT carrier, COUNT(*) FROM shipments GROUP BY carrier;
    3. SELECT * FROM shipments WHERE ship_date BETWEEN '2026-01-01' AND '2026-03-31';
    4. SELECT * FROM shipments WHERE status = 'DELIVERED';
  5. Which table is the best candidate for a clustering key?

    1. A 6 TB SHIPMENTS table, appended daily, where most queries filter on a carrier code and a range of ship dates
    2. A 6 TB SHIPMENTS table where most queries look up a single tracking number
    3. A 2 GB lookup table that a job rebuilds every night
    4. A 6 TB ORDERS table that MERGE statements update all day on random order IDs
  6. A Query Profile shows a Join operator receiving 2,000 rows on one side and 2 million on the other, and producing 4 billion rows. What is the most likely cause?

    1. The join condition is missing or wrong
    2. The query uses UNION instead of UNION ALL
    3. The warehouse is too small and spilled to remote storage
    4. The SELECT list includes too many columns
  7. The Query Profile of a slow aggregation shows 40 GB spilled to local storage and 12 GB spilled to remote storage. Which change most directly addresses this?

    1. Enable search optimization on the source table
    2. Raise the warehouse's maximum cluster count
    3. Raise MAX_CONCURRENCY_LEVEL on the warehouse
    4. Run the query on a larger warehouse size
  8. Which three can't be used in the definition of a materialized view? (Choose three.)

    1. A join between two tables
    2. A user-defined function
    3. A WHERE filter on one column
    4. The CURRENT_TIMESTAMP() function
    5. A GROUP BY with SUM()
  9. A Medium multi-cluster warehouse (4 credits per hour per cluster) runs 3 clusters from 09:00 to 10:00, then 1 cluster from 10:00 to 12:00. How many credits does it consume over those three hours?

    1. 8
    2. 20
    3. 16
    4. 12
  10. A team wants extra clusters to start only when Snowflake estimates enough queued work to keep a new cluster busy for at least six minutes, accepting some queuing to save credits. Which configuration matches?

    1. Auto-scale mode with the Economy scaling policy
    2. Maximized mode with the Standard scaling policy
    3. Maximized mode with the Economy scaling policy
    4. Auto-scale mode with the Standard scaling policy
  11. Which two statements about multi-cluster warehouses are correct? (Choose two.)

    1. Multi-cluster warehouses are available in Standard edition
    2. Equal minimum and maximum cluster counts above 1 put it in Maximized mode
    3. Resizing the warehouse changes the size of all of its clusters
    4. Each cluster can have a different size to match its share of the load
    5. In Maximized mode, idle clusters shut down until queries queue again
  12. An administrator tries to set the maximum cluster count of an X-Small multi-cluster warehouse to 40 in Snowsight, and the form won't accept more than 10. What is true?

    1. More than 10 clusters needs Business Critical edition or a support case
    2. Snowsight stops at 10, but ALTER WAREHOUSE accepts up to 300 for X-Small
    3. Only warehouses sized 4X-Large or larger are allowed more than 10 clusters
    4. Multi-cluster warehouses are capped at 10 clusters, whatever their size is
  13. A Snowpark Python stored procedure that trains a model on a Medium standard warehouse fails with out-of-memory errors, while CPU use never goes above 30%. Which fix targets the bottleneck at the lowest cost?

    1. Convert the warehouse to a multi-cluster warehouse
    2. Resize the warehouse to 2X-Large on the standard type
    3. Enable the Query Acceleration Service on it
    4. Use a Snowpark-optimized warehouse of the same size (Medium)
  14. A FinOps lead must be alerted when the combined monthly spend of a Snowpipe pipe, Automatic Clustering on one table and two warehouses is projected to exceed 500 credits. Which feature fits?

    1. A daily scheduled query on WAREHOUSE_METERING_HISTORY
    2. A custom budget on those objects with a 500-credit spending limit
    3. A resource monitor with SUSPEND_IMMEDIATE at 100 percent
    4. A resource monitor on the two warehouses with a 500-credit NOTIFY trigger
  15. A suspended warehouse with AUTO_SUSPEND = 300 is resumed and runs a 25-second query. Ten seconds after it finishes, a 70-second query runs, and an administrator suspends the warehouse as soon as that query ends. How much compute time is billed for this start?

    1. 105 seconds
    2. 60 seconds
    3. 95 seconds
    4. 120 seconds
  16. In an Enterprise edition account, a custom role must run ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id). Which two privileges does the role need for this statement? (Choose two.)

    1. UPDATE on the EVENTS table
    2. ADD SEARCH OPTIMIZATION on the schema that contains EVENTS
    3. OWNERSHIP on the EVENTS table
    4. USAGE on a warehouse to build the search access path
    5. MODIFY on the database that contains EVENTS
  17. A team wants BI_WH suspended automatically when the custom budget that covers it projects an overspend. What should they do?

    1. Enable the budget's built-in option that suspends BI_WH when it fires
    2. Nothing, because a budget can only send email notifications to users
    3. Add a custom action that calls a procedure that suspends BI_WH
    4. Move BI_WH out of the budget, since budgets can't watch warehouse spend
  18. A data engineer needs credit consumption per warehouse for the last 11 months for a chargeback report. Which source should be queried?

    1. The Query Profile of the most expensive queries
    2. The output of SHOW WAREHOUSES
    3. The SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY view
    4. The INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY table function
  19. ETL_WH is resized from Medium to X-Large while three statements are running on it. What happens to those three statements?

    1. They finish on their current resources; new statements use the new size
    2. They move to the new nodes while running and finish sooner
    3. They pause until the resize is complete, then continue on the larger size
    4. They're cancelled, and the user has to submit them again on the new size
  20. Which statement about Gen2 standard warehouses is correct?

    1. Gen2 is billed per second with a 60-second minimum, like Gen1
    2. Gen2 is offered at every size from X-Small to 6X-Large
    3. Gen2 stores tables in a different micro-partition format
    4. Gen2 uses the same credit rate as Gen1 at every size
  21. Which two costs come from Snowflake-managed background services rather than from a warehouse you run? (Choose two.)

    1. Automatic Clustering of a table with a clustering key
    2. Maintenance of a materialized view
    3. A SELECT that joins two large tables
    4. A task defined with WAREHOUSE = etl_wh
    5. A COPY INTO statement run from a worksheet
  22. A FinOps team wants to know which warehouses in the account would benefit most from the Query Acceleration Service, based on past workload. Where should it look first?

    1. SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
    2. SYSTEM$CLUSTERING_INFORMATION on the largest tables
    3. SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    4. The Query Profile of each slow query, opened one at a time
  23. A data engineer is writing the CREATE WAREHOUSE statement for a warehouse that will run a few short queries a day. Which two settings reduce the credits it consumes while no queries are running? (Choose two.)

    1. SCALING_POLICY = 'ECONOMY'
    2. MAX_CONCURRENCY_LEVEL = 4
    3. STATEMENT_TIMEOUT_IN_SECONDS = 600
    4. AUTO_SUSPEND = 120
    5. INITIALLY_SUSPENDED = TRUE
  24. In the Query Profile, the statistic "Percentage scanned from cache" refers to data served from which cache?

    1. The remote storage layer's object cache
    2. The query result cache in cloud services
    3. The metadata cache used for COUNT(*)
    4. The warehouse's local disk cache
  25. SYSTEM$CLUSTERING_DEPTH on a SHIPMENTS table clustered by SHIP_DATE returned 1.4 in March and 31 in September. What does this mean?

    1. Automatic Clustering was switched off in September
    2. The table now holds 31 micro-partitions in total
    3. The table is more deeply clustered, so pruning improved
    4. Partitions now overlap heavily on SHIP_DATE, so pruning got worse
  26. A data engineer needs to stop Automatic Clustering on SHIPMENTS temporarily without dropping its clustering key. Which statement does this?

    1. ALTER TABLE shipments SET DATA_RETENTION_TIME_IN_DAYS = 0;
    2. ALTER WAREHOUSE load_wh SUSPEND;
    3. ALTER TABLE shipments DROP CLUSTERING KEY;
    4. ALTER TABLE shipments SUSPEND RECLUSTER;
  27. A role named FINOPS must query SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY without using ACCOUNTADMIN. Which two grants give it access? (Choose two.)

    1. GRANT DATABASE ROLE snowflake.governance_viewer TO ROLE finops;
    2. GRANT SELECT ON VIEW snowflake.account_usage.warehouse_metering_history TO ROLE finops;
    3. GRANT DATABASE ROLE snowflake.usage_viewer TO ROLE finops;
    4. GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE finops;
    5. GRANT MONITOR ON WAREHOUSE etl_wh TO ROLE finops;
  28. SHOW MATERIALIZED VIEWS shows that MV_STORE_TOTALS is behind its base table. What should a data engineer do so that queries on the view return current results?

    1. Suspend and resume the view to force a full rebuild
    2. Nothing, because queries already return current results
    3. Recreate the view with CREATE OR REPLACE
    4. Run ALTER MATERIALIZED VIEW mv_store_totals REFRESH
  29. A warehouse has ENABLE_QUERY_ACCELERATION = TRUE and QUERY_ACCELERATION_MAX_SCALE_FACTOR = 0. What does the 0 mean?

    1. Query acceleration is turned off for the warehouse
    2. The default scale factor of 8 applies to this warehouse
    3. QAS may lease at most as much compute as the warehouse itself
    4. There's no upper limit on the compute QAS may lease
  30. Which two choices follow Snowflake's guidance for defining a clustering key? (Choose two.)

    1. Order the key's columns from lowest to highest cardinality
    2. Wrap a raw timestamp column in an expression such as TO_DATE()
    3. Add a key to every table larger than 1 GB to speed up all scans
    4. Include 8 to 10 columns so the key covers every common filter
    5. Put a unique ID column first so each partition holds one value

Finish this set to count it as done.