π§© Query Profiling in Snowflake
π What is Query Profiling?β
Query Profiling in Snowflake means analyzing the actual execution details of a query after it runs.
Itβs like an X-ray of your SQL query π©» β showing:
- How data was scanned
- How filters were applied
- How joins were executed
- Where time and compute were spent
π Profiling answers:
- Why is this query slow? β³
- Which step is the bottleneck? β‘
- Is Snowflake scanning too much data? π°
π Where to See Query Profiling?β
1οΈβ£ Snowsight (Recommended)β
- Run query β Query Details β Query Profile
- Visual DAG (Directed Acyclic Graph)
2οΈβ£ SQL (Programmatic)β
SELECT LAST_QUERY_ID();
SELECT SYSTEM$QUERY_PROFILE('<query_id>');
π Also:
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY START_TIME DESC;
π§ͺ Hands-on Example (End-to-End)
ποΈ Step 1: Create Tablesβ
CREATE OR REPLACE TABLE customers (
customer_id INT,
customer_name STRING,
city STRING
);
CREATE OR REPLACE TABLE sales (
sale_id INT,
customer_id INT,
sale_date DATE,
amount NUMBER(10,2)
);
π₯ Step 2: Insert Sample Dataβ
INSERT INTO customers VALUES
(1, 'Ravi', 'Chennai'),
(2, 'Priya', 'Bangalore'),
(3, 'Arjun', 'Hyderabad'),
(4, 'Meena', 'Coimbatore'),
(5, 'Karthik', 'Madurai');
INSERT INTO sales VALUES
(101, 1, '2024-12-15', 500),
(102, 1, '2025-01-10', 700),
(103, 2, '2025-02-05', 1200),
(104, 3, '2024-11-20', 300),
(105, 3, '2025-03-01', 900),
(106, 4, '2025-01-25', 1500),
(107, 5, '2025-02-10', 200);
π Step 3: Run Queryβ
SELECT c.customer_name, SUM(s.amount) AS total_spent
FROM sales s
JOIN customers c
ON s.customer_id = c.customer_id
WHERE s.sale_date >= '2025-01-01'
GROUP BY c.customer_name;
π§ What Youβll See in Query Profileβ
πΉ Step 1: Table Scan (SALES)β
- Reads
salestable - Applies filter:
sale_date >= '2025-01-01'
π Check:
- Bytes scanned
- Partitions pruned
πΉ Step 2: Table Scan (CUSTOMERS)β
- Full scan (small dimension table)
πΉ Step 3: Join (Hash Join)β
- Customers β build side
- Sales β probe side
π Check:
- Join type
- Rows in vs rows out
πΉ Step 4: Aggregationβ
- GROUP BY + SUM
π Check:
- Data reduction (rows decrease)
πΉ Step 5: Resultβ
- Final output returned
π Example: How to Read Query Profile
| Step | What to Check | Meaning |
|---|---|---|
| Scan | Bytes scanned | High = expensive |
| Filter | Rows reduced | Good pruning |
| Join | Skew / imbalance | Potential bottleneck |
| Aggregate | Data reduction | Efficiency |
| Result | Final output | Small |
π¨ Real Optimization Scenario
β Bad Query (No Filtering Early)β
SELECT c.customer_name, SUM(s.amount)
FROM sales s
JOIN customers c
ON s.customer_id = c.customer_id
GROUP BY c.customer_name
HAVING MAX(s.sale_date) >= '2025-01-01';
π΄ Problem:β
- Filter applied after aggregation
- Full table scan of
sales
π Query Profile shows:
- Large scan
- No pruning
- High execution time
β Optimized Query (Filter Early)β
SELECT c.customer_name, SUM(s.amount)
FROM sales s
JOIN customers c
ON s.customer_id = c.customer_id
WHERE s.sale_date >= '2025-01-01'
GROUP BY c.customer_name;
π’ Improvement:β
- Filter pushed to scan level
- Partition pruning happens
π Query Profile shows:
- Reduced bytes scanned
- Faster execution
β‘ Advanced: Simulate Large Dataset
INSERT INTO sales
SELECT
SEQ4(),
UNIFORM(1,5,RANDOM()),
DATEADD(DAY, UNIFORM(-500, 100, RANDOM()), CURRENT_DATE),
UNIFORM(100, 5000, RANDOM())
FROM TABLE(GENERATOR(ROWCOUNT => 100000));
π Now profile again and observe:
- Scan size increases
- Pruning impact becomes visible
- Join cost increases
π§ Key Metrics to Focus On
When analyzing Query Profile:
π 1. Bytes Scannedβ
π High = expensive query
π 2. Partitions Scanned vs Totalβ
π Low % = good pruning
π 3. Rows Producedβ
π Large intermediate data = inefficiency
π 4. Execution Time per Stepβ
π Identify bottleneck node
π 5. Spilling (Important)β
π If you see:
- "Spilled to local storage"
- "Spilled to remote storage"
β οΈ Indicates:
- Warehouse too small
- Or query inefficient
π― Why Query Profiling Matters
π¦ Find bottlenecks π Reduce cost π οΈ Tune SQL & schema β‘ Optimize warehouse size
β Best Practices
βοΈ Always profile slow queries first
βοΈ Ensure filters are applied early
βοΈ Check partition pruning
βοΈ Join small β large tables
βοΈ Avoid unnecessary columns (SELECT *)
βοΈ Monitor spilling
β οΈ Limitations
β Only after execution β Complex queries β complex graphs β Requires interpretation
π Advantages
β¨ Deep visibility into execution β¨ Helps reduce cost β¨ Identifies exact bottlenecks
π Disadvantages
β οΈ Can be overwhelming initially β οΈ No automatic fix suggestions β οΈ Needs SQL expertise
π Roles & Privileges
-
Snowsight: No special access needed
-
SQL access:
MONITORprivilege- or
ACCOUNTADMIN
β Quick Recap
π Query Profiling = Execution X-ray π Shows where time & compute are spent β‘ Helps optimize performance + cost π§ Essential skill for Snowflake engineers
π Combine Execution Plan + Query Profile to fully understand query behavior.