About this lab
Six months after Part 1, TripLedger runs 150 million trips through Snowflake, keeps a 1.5-billion-trip archive, and serves a market dashboard that is refreshed all day. The warehouse bill has doubled and three teams say their reports are slow. Part 1 taught the individual levers: clustering, the caches and search optimization. Part 2 is about using them like an owner: find the real cause, fix it, prove the fix with numbers, and know what every feature costs before you switch it on.
You start by building a query-profile triage tool on GET_QUERY_OPERATOR_STATS that flags exploding joins, spills and pruning failures and logs every diagnosis. It finds a surge-pricing join that counted every trip 24 times, a date filter that read every partition, and a window function that spilled about 1.8 GB to disk; you fix all three and prove it. You then size warehouses with measurements: the same query on X-Small, Small and Medium, and a pandas procedure that runs out of memory on a standard Medium, succeeds on a Snowpark-optimized Medium, and needs neither once the code is pushed down. You check Query Acceleration eligibility before enabling it, measure the speed-up on the archive, and compare the result cache, a materialized view and a dynamic table for the dashboard, including where each one breaks.
Finally you add search optimization to a VARIANT field and a free-text column, measure what Automatic Clustering changed and what it cost against sorting once at load time, absorb a dashboard burst with a multi-cluster warehouse, stop a runaway query with a timeout, and build a cost dashboard that attributes the lab's spend to query tags and serverless features. Every claim is measured with Snowflake's own statistics, and the lab works on a Snowflake trial account. Part 1 is recommended but not required: Task 1 builds everything you need.
Lab details
- Level: Advanced
- Cloud: Snowflake
- Domain: Data Engineering
- Duration: 150 minutes
What you will learn
- Diagnose exploding joins, spills and pruning failures from operator statistics, then fix and prove each one
- Size warehouses from measured speed, spill and cost, and know when Snowpark-optimized memory is the answer
- Check Query Acceleration eligibility before enabling it and measure what it did
- Choose between the result cache, a materialized view and a dynamic table from tested freshness and limits
- Weigh search optimization and Automatic Clustering against their real cost before switching them on
- Govern spend with multi-cluster scaling, timeouts, a resource monitor and a cost dashboard by query tag
Concepts covered
- Query profile forensics with operator statistics
- Exploding joins, spilling and partition pruning
- Warehouse sizing and per-second billing
- Snowpark-optimized warehouses and pushdown
- Query Acceleration Service
- Result cache vs materialized views vs dynamic tables
- Search optimization on semi-structured data and text
- Automatic Clustering cost versus benefit
- Multi-cluster warehouses and concurrency
- Cost governance and attribution
Technologies used
- Query operator statistics
- Warehouse sizing
- Query Acceleration Service
- Materialized views and dynamic tables
- Search Optimization Service
- Automatic Clustering
- Multi-cluster warehouses
- Cost governance
Prerequisites
- Comfortable with SQL (joins, window functions, CTEs)
- Familiar with micro-partitions, clustering, the caches and search optimization (Part 1 of this lab covers them)
- A Snowflake account with the ACCOUNTADMIN role (tested on an Enterprise trial, AWS eu-west-1)
- About 2 credits of budget (the lab used under 1 credit when it was tested)
Topics
- Snowflake
- Part 2
- Performance Tuning
- Cost Optimization
- Query Profile
- Query Acceleration
- Materialized Views
- Dynamic Tables
- Search Optimization
- Clustering
- Snowpark
- Multi-Cluster Warehouses
- FinOps
- SQL
