Snowflake optimization helps Data Engineers improve query speed, reduce warehouse costs, and build efficient data pipelines.
1. Optimize SQL Queries
Select only the columns you need instead of using "SELECT *".
SELECT ORDER_ID, ORDER_AMOUNT
FROM SALES
WHERE CUSTOMER_ID = 1001;
Benefit: Reduces unnecessary data processing.
2. Use Micro-Partition Pruning
Filter data using suitable date or numeric conditions.
SELECT *
FROM SALES
WHERE ORDER_DATE >= '2026-01-01'
AND ORDER_DATE < '2026-02-01';
Benefit: Snowflake may skip irrelevant micro-partitions.
3. Choose the Right Warehouse Size
Use a warehouse size that matches your workload.
ALTER WAREHOUSE COMPUTE_WH
SET WAREHOUSE_SIZE = 'SMALL';
Benefit: Balances performance and credit consumption.
4. Use Caching
Snowflake can reuse eligible query results and cached warehouse data to reduce repeated work.
Tip: Measure both cached and uncached query performance when testing optimization changes.
5. Optimize Joins
Join tables using appropriate keys and avoid unnecessary joins.
SELECT
C.REGION,
SUM(S.ORDER_AMOUNT) AS TOTAL_SALES
FROM SALES S
JOIN CUSTOMERS C
ON S.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.REGION;
Benefit: Helps avoid unnecessary processing and unexpected row multiplication.
6. Monitor Warehouse Costs
Track warehouse credit consumption over the last seven days.
SELECT
WAREHOUSE_NAME,
ROUND(SUM(CREDITS_USED), 2) AS TOTAL_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
GROUP BY WAREHOUSE_NAME
ORDER BY TOTAL_CREDITS DESC;
Benefit: Identifies warehouses with high credit consumption.
7. Find Slow Queries
Use query history to identify queries with long execution times.
SELECT
QUERY_ID,
TOTAL_ELAPSED_TIME / 1000.0 AS SECONDS,
BYTES_SCANNED,
QUERY_TEXT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
ORDER BY TOTAL_ELAPSED_TIME DESC
LIMIT 10;
Benefit: Helps identify queries that need further investigation.
Conclusion
Effective Snowflake optimization combines efficient SQL, suitable warehouse sizing, caching, and cost monitoring. Always measure performance before and after making changes.
