Chapter 2 of 5 · Snowflake DEA-C02
Performance Optimization
Query troubleshooting, warehouses, clustering, caching, cost control and pipeline monitoring.
- 7topics
- 35 minfast track
- 4diagrams and exhibits
- 19%of the exam
Topics in this chapter
Take them in order, or jump to the one you need.
- 1Troubleshooting Underperforming Queries and the Query ProfileFind slow Snowflake queries in Query History and ACCOUNT_USAGE, read the Query Profile, spot spilling, poor pruning and exploding…Diagram5 min
- 2Warehouse Sizing, Multi-cluster and Snowpark-optimized WarehousesChoose between scaling up and scaling out in Snowflake, set virtual warehouse properties, and know when a Snowpark-optimized…Diagram5 min
- 3Query Complexity, Micro-partitions and ClusteringHow query shape affects Snowflake performance, how micro-partition pruning works, and when a clustering key and Automatic…5 min
- 4Materialized Views, Search Optimization, Query Acceleration and CachingChoose between materialized views, search optimization, query acceleration and Snowflake caches to speed up queries and keep…Diagram5 min
- 5Monitoring Usage, Resource Monitors and Credit ControlUse ACCOUNT_USAGE and warehouse load metrics, set resource monitors and timeouts, trim storage cost and balance Snowflake speed…Diagram5 min
- 6Monitoring Streams, Tasks, Pipes and Snowpipe StreamingKnow where to look when a Snowflake pipeline stalls: Snowsight task history, TASK_HISTORY, stale streams, pipe status and…5 min
- 7Dynamic Table Monitoring, Alerts, Notifications and Data QualityMonitor dynamic table refreshes, build Snowflake alerts, send email and webhook notifications, and track data quality with data…5 min
About this chapter
What this chapter covers
This chapter covers the Performance Optimization domain of the SnowPro Advanced: Data Engineer DEA-C02 exam. It tests how a data engineer finds a slow or costly workload, works out why it is slow, and picks the feature that fixes it at an acceptable credit cost. It also covers how to monitor the pipelines you build, so problems surface before users notice.
Questions are scenarios that start with "A Data Engineer needs to ...". They give you symptoms, such as remote spilling, a scan of nearly every partition, queued queries or a stale stream, and ask for the best action. Many hinge on matching one symptom to one feature.
How the topics fit together
The work follows one loop: measure, diagnose, fix, control cost, then keep watching.
- Query troubleshooting gives you the evidence: history, profile statistics and telemetry.
- Warehouse sizing answers scale up versus scale out and which warehouse type to use.
- Data layout and query shape decide how much data a query must read.
- Acceleration features and caching speed up specific query patterns, each with its own cost.
- Usage monitoring and credit control keep spend visible and capped.
- Pipeline monitoring covers tasks, streams, pipes, dynamic tables, alerts, notifications and data quality checks.
Match the symptom to the fix. Spilling means more memory, queuing means more clusters, poor pruning means better data layout or search optimization, and repeated identical queries mean the result cache.
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, SQL and comparison tables.
- Common mistakes: the classic traps.
- What to remember for the exam: the facts to memorize.
- Check yourself: three questions to test your understanding.
Read the pages in order the first time. The later pages on monitoring assume you know the warehouse and query concepts from the first four.
Ready for more practice?
The full Snowflake DEA-C02 question bank has 709 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.