Lab 13 · Domain 4 · You are Priya
Read every Teach first section before you copy SQL. When Kai says “make the warehouse bigger,” you open Query Profile first. Today: three caches, pruning, Profile nodes, and Query Acceleration Service awareness.
LAB.ORDERS, open Snowsight Query Profile, name the three caches,
and know when QAS helps (and when a bigger WH won’t).
Episode 13 · Start here
Just make it Medium. We have a board call in ten minutes.
If we’re scanning 95% of partitions, Medium costs more for the same IO shape. Profile first: TableScan, bytes spilled, join explosion — then decide scale up, clustering, or rewrite.
Exam trap: bigger warehouse ≠ more storage and ≠ automatic pruning.
Exact same query text + unchanged data → reuse result. Often 0 warehouse credits.
SSD on WH servers holds data from recent scans. Helps warm queries on same WH.
Micro-partition stats for pruning & planning — not your full result set.
Selective filters on clustered/min-max friendly columns scan fewer partitions.
Profile graph shows operators. Bloom filters can reduce join probe work.
Heavy sorts/hashes exceed memory → spill. Remote spill hurts latency.
Serverless boost for large scans/filters. Not a cure for bad joins or tiny tables.
Before Step 1
I stared at the SQL worksheet. Where is the Profile?
After the query finishes: Query History → click the query → Profile. Look at TableScan partitions scanned vs total.
What this does: Narrow filter. On a big table this is where pruning shines; our sample is tiny — still practice the Profile habit.
USE ROLE ROLE_CORE_LAB_ADMIN;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
USE SCHEMA LAB;
SELECT ORDER_ID, CUSTOMER_ID, AMOUNT_USD, STATUS
FROM LAB.ORDERS
WHERE STATUS = 'PENDING';Before Step 2
Aggregations that touch every row won’t be “fixed” by hoping for result cache on the first run.
Right. First run builds the plan and scans. Second identical run may hit result cache. Don’t confuse cache wins with “Medium made it fast.”
What this does: Run an aggregate, then the same SELECT twice to feel result cache. RESULT_SCAN re-reads the last result without re-running the original SQL.
SELECT STATUS, COUNT(*) AS N, SUM(AMOUNT_USD) AS TOTAL_USD
FROM LAB.ORDERS
GROUP BY STATUS
ORDER BY TOTAL_USD DESC;SELECT ORDER_ID, AMOUNT_USD
FROM LAB.ORDERS
WHERE CUSTOMER_ID = 1
ORDER BY ORDER_ID;
-- Run the identical statement again, then:
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));What this does: SYSTEM$CLUSTERING_INFORMATION only helps on clustered (or naturally well-ordered) tables. Our tiny ORDERS sample usually isn’t worth clustering — know the function for the exam.
-- Only if you clustered a real table:
-- ALTER TABLE LAB.ORDERS CLUSTER BY (STATUS);
-- SELECT SYSTEM$CLUSTERING_INFORMATION('LAB.ORDERS', '(STATUS)');
-- QAS awareness (Enterprise+), don’t leave on for tiny labs:
-- ALTER WAREHOUSE WH_CORE_XS SET
-- ENABLE_QUERY_ACCELERATION = TRUE
-- QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;Say: Query a previous result set by query ID without re-executing the original SQL — handy for post-processing the last statement’s output.
Say: When the bottleneck is poor pruning (scanning almost everything), waiting on cloud services only, tiny data already memory-resident, or a bad SQL shape (exploding joins). Profile first.
Say: Operators ran out of memory and wrote intermediate data to local or remote storage. Remote spill is expensive latency-wise — scale up or reduce memory pressure.
Say: Large scans/filters that can offload to QAS serverless acceleration — especially ad-hoc analytical queries. Not a substitute for fixing cardinality explosions.
Say: Result cache (cloud services — reused results), local disk cache (warehouse SSDs — warm data), metadata cache (cloud services — partition stats for pruning/planning).