Skip to main content

🧩 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?​

  • 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 sales table
  • 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

StepWhat to CheckMeaning
ScanBytes scannedHigh = expensive
FilterRows reducedGood pruning
JoinSkew / imbalancePotential bottleneck
AggregateData reductionEfficiency
ResultFinal outputSmall

🚨 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:

    • MONITOR privilege
    • 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.