π 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_HISTORYand 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 queriesACCOUNTADMINorSECURITYADMIN: For full access- Or delegated roles with access to
SNOWFLAKE.ACCOUNT_USAGEviews
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!