About this lab
TripLedger is a ride-hailing analytics platform. Finance runs date-range reports against trip history, support agents look up one specific trip by a random reference code, and every query burns real credits on a shared warehouse.
In this lab you measure clustering depth and partition overlap on a realistically sized trips table, see exactly how much a bad physical layout costs a date-filtered query, and compare it against a well-clustered copy using Snowflake's own clustering metadata functions. You then explore the warehouse cache and the result cache side by side on the same aggregate query, watching a Query Profile collapse from a real scan to a free cache hit. Finally you build a 20-million-row lookup table where a single trip's reference code could be anywhere, prove that a plain lookup scans the entire table, turn on the Search Optimization Service, and watch the exact same query scan almost nothing.
Every comparison in this lab is measured with Snowflake's own query statistics, not just described: you will see the real partition counts before and after each fix.
Lab details
- Level: Intermediate
- Cloud: Snowflake
- Domain: Data Engineering
- Duration: 90 minutes
What you will learn
- Measure clustering depth and partition overlap with SYSTEM$CLUSTERING_INFORMATION
- Prove the cost of bad clustering, then compare it against a well-clustered copy using real partition-scan counts
- Declare a clustering key and understand that Automatic Clustering runs as a background service, not instantly
- Distinguish the warehouse cache from the result cache by reading a Query Profile for each
- Diagnose a full-table-scan lookup and fix it with the Search Optimization Service
- Read warehouse credit consumption to judge whether a warehouse is sized correctly
Concepts covered
- Micro-partitions and clustering depth
- Clustering keys and Automatic Clustering
- Warehouse cache vs result cache
- Query Profile and partition pruning
- The Search Optimization Service
- Cost optimization and credit monitoring
Technologies used
- Micro-Partition Clustering
- Query Profile
- Warehouse and Result Caching
- Search Optimization Service
- Resource Monitoring
Prerequisites
- Basic SQL (SELECT, WHERE, GROUP BY)
- A Snowflake trial account (Enterprise edition features like Search Optimization are used)
- Comfort reading a Query Profile in Snowsight is helpful but not required, this lab teaches it
Topics
- Snowflake
- Performance Tuning
- Clustering
- Query Optimization
- Cost Optimization
- Search Optimization
- SQL
