Chapter 4 of 5 · Snowflake SnowPro Core COF-C03
Performance Optimization, Querying, and Transformation
Query Profile, ACCOUNT_USAGE views, workload design, acceleration features, caching and SQL transforms.
- 6topics
- 31 minfast track
- 2diagrams and exhibits
- 21%of the exam
Topics in this chapter
Take them in order, or jump to the one you need.
- 1Query Profile and Query InsightsRead the Snowflake Query Profile and Query Insights to spot bytes spilled to storage, inefficient pruning and exploding joins…Diagram6 min
- 2Query History, Attribution and Workload ManagementFind queuing, slow and costly queries in SNOWFLAKE.ACCOUNT_USAGE views, attribute credits per query, and group similar workloads…5 min
- 3Query Acceleration, Search Optimization, Clustering and Materialized ViewsChoose between the query acceleration service, search optimization, clustering keys and materialized views by query pattern…5 min
- 4Snowflake CachingLearn how the Snowflake query result cache, metadata cache and warehouse cache work, when each one is reused, and how auto…Diagram5 min
- 5Using Structured, Semi-Structured and Unstructured DataTransform structured tables, query VARIANT data with path notation, casts and FLATTEN, and work with unstructured files through…5 min
- 6Aggregate Functions, Window Functions and SQL for OptimizationUse Snowflake aggregate and window functions, QUALIFY and approximate functions, and write SQL that prunes, scans fewer columns…5 min
About this chapter
What this chapter covers
This chapter is 21% of the SnowPro Core COF-C03 exam, the second largest domain. It tests whether a Snowflake Practitioner can find out why a query is slow, pick the right fix, use the three caches well and transform structured, semi-structured and unstructured data with SQL.
Questions are short scenarios, single answer or multiple select. Most give you a symptom, such as a large spill figure or a long queue time, and ask for the feature or setting that fixes it. Knowing which symptom maps to which fix is most of the chapter.
How the topics fit together
Performance work follows one loop: measure, diagnose, fix, then measure again.
- Evaluate query performance reads the Query Profile and Query Insights for spilling, pruning, exploding joins and queuing.
- ACCOUNT_USAGE views give history and cost per query, so you can find the worst workloads.
- Workload management separates and sizes warehouses so similar queries share compute.
- Optimize query performance adds the query acceleration service, search optimization, clustering keys and materialized views.
- Caching explains the result, metadata and warehouse caches and how to keep them warm.
- Transformation covers working with each data shape, aggregate and window functions, and writing SQL that prunes well.
Match the symptom to the layer. Spilling points at warehouse size, poor pruning points at clustering or search optimization, and queuing points at concurrency, so the fix is a warehouse change, a table change or a multi cluster change.
How to use these pages
Each topic page has the same shape, so you can skim for what you need.
- What it is: the idea in plain words.
- How it works: the mechanism, with diagrams and examples.
- Common mistakes: the classic traps.
- What to remember for the exam: the facts to memorize.
- Check yourself: three questions to test your understanding.
For each optimization feature, learn the query pattern it helps and the edition it needs. Many wrong options name a real feature that simply does not help that pattern.
Ready for more practice?
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.