Skip to main content

πŸš€ Query Profiling for Cost Optimization in Snowflake


What is Query Profiling?​

Query profiling involves analyzing a query’s execution characteristics to understand:

  • Query plan details (operators, steps)
  • Resource usage (CPU, memory, I/O)
  • Bottlenecks and inefficiencies

It’s accessible through the Query Profile tab in the Snowflake UI or via the QUERY_HISTORY and QUERY_PLAN functions.


Why is it required?​

  • βœ… Identify expensive queries
  • βœ… Eliminate unnecessary data scans
  • βœ… Optimize compute usage β†’ reduce credit burn
  • βœ… Align performance with cost goals

Associated SQL & Access​

You can gather historical query profiling data using:

SELECT
QUERY_ID,
QUERY_TEXT,
EXECUTION_STATUS,
TOTAL_ELAPSED_TIME,
EXECUTION_TIME,
BYTES_SCANNED,
PARTITIONS_SCANNED,
CREDITS_USED_CLOUD_SERVICES
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -1, CURRENT_TIMESTAMP())
ORDER BY TOTAL_ELAPSED_TIME DESC;

For detailed query plan:

SELECT
SYSTEM$EXPLAIN_JSON('<query_text>');

or directly via UI: Query History β†’ Profile.


Use Case​

πŸ› οΈ Example: A data engineering team sees that nightly ETL jobs consume 1,000 credits/month. Using query profiling, they find:

  • Some queries do full table scans
  • Joins aren’t using appropriate clustering keys
  • Redundant subqueries

They rewrite queries to reduce scans and leverage CLUSTER BY on large tables, cutting costs by 30%.


Best Practices​

  • βœ… Always check scan size: Large table scans β†’ consider pruning with filters, partitioning, or clustering.

  • βœ… Watch for repeated data scans: E.g., nested subqueries, repeated joins.

  • βœ… Look for high execution time steps: Profile shows which step took longest – optimize those.

  • βœ… Use appropriate warehouse sizes: Right-size the warehouse based on the workload’s complexity.

  • βœ… Leverage caching: Result reuse, data pruning, and micro-partition metadata caching reduce costs.

  • βœ… Regularly review high-cost queries: Use QUERY_HISTORY and highlight top credit-consuming queries.


Limitations​

⚠️ Profiling doesn’t auto-optimize. It provides visibility; human intervention is needed. ⚠️ May not pinpoint all performance issues (some might be schema-level, not query-level).


Advantages​

  • βœ… Prevents credit waste by making queries efficient
  • βœ… Improves user experience (faster queries)
  • βœ… Helps right-size compute (warehouse scaling)

Disadvantages​

❌ Takes time and expertise to interpret profiles ❌ Complex queries might still need multiple rewrites


Required Roles​

  • MONITOR: To see historical queries
  • ACCOUNTADMIN or SECURITYADMIN: For full access
  • Or delegated roles with access to SNOWFLAKE.ACCOUNT_USAGE views

Impact on Performance Tuning​

πŸ’‘ Query profiling directly supports cost optimization by:

  • Minimizing unnecessary scans
  • Revealing join inefficiencies
  • Guiding warehouse size decisions
  • Improving caching effectiveness

Example: A full table scan costs 10 credits vs. a targeted scan costing 1 credit β†’ rewriting the query to leverage micro-partition pruning can save 9 credits each time.


βœ… Query Profiling Review Checklist

This checklist is designed to guide intermediate IT teams and Snowflake admins through consistent, periodic reviews to optimize queries for performance and cost.


πŸ“… Frequency​

  • Daily (for high-volume systems)
  • Weekly (for moderate usage)
  • Monthly (for stable, low-volume usage)

πŸ”Ž Query History Review​

  • Identify top 10 slowest queries
  • Identify top 10 highest credit-consuming queries
  • Identify queries with frequent execution (run repeatedly)

SQL Reference:

SELECT
QUERY_ID,
QUERY_TEXT,
TOTAL_ELAPSED_TIME,
CREDITS_USED_CLOUD_SERVICES
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY TOTAL_ELAPSED_TIME DESC;

πŸ“Š Query Profiling Analysis​

For each target query:

  • Open the Query Profile tab
  • Check for full table scans
  • Identify expensive join steps
  • Review data scanned vs. result size

βš™οΈ Warehouse Optimization​

  • Evaluate if the warehouse size is right-sized for the query
  • Check if auto-suspend and auto-resume are configured
  • See if queries can be optimized to run on a smaller warehouse

πŸ’‘ Query Rewrite Opportunities​

  • Add filtering to avoid scanning entire tables
  • Replace suboptimal joins with better join keys
  • Use clustering for large tables with frequent pruning
  • Simplify query logic (CTEs, subqueries)

πŸ› οΈ Schema & Indexing Improvements​

  • Check if clustering keys are defined and helpful
  • Validate that partition pruning is working
  • Review materialized views for reuse opportunities

πŸ”„ Result Caching & Reuse​

  • Confirm queries can benefit from result reuse
  • Encourage teams to reuse query results instead of repeated execution

πŸ“ Documentation & Action Items​

  • Document any identified issues (scans, joins, bottlenecks)
  • Create tickets for query rewrites or schema changes
  • Track before & after costs (credits) to validate savings

πŸš€ Follow-Up & Automation​

  • Schedule next review
  • Share findings with dev/analytics teams
  • Explore automated cost alerts using Resource Monitors

πŸ”” Note: Adjust this checklist to your environment’s scale and complexity!

πŸ“Œ Summary​

πŸ”Ž Query profiling is essential for both performance and cost efficiency in Snowflake:

  • βœ… Helps identify and resolve expensive operations
  • βœ… Directly supports performance tuning β†’ smaller warehouses, faster queries
  • βœ… Key tool in your Snowflake cost control toolkit!