Optimising Query Performance in Snowflake with clustering keys

Snowflake is a powerful cloud-based data warehouse designed for scalability and performance. However, as datasets grow, even Snowflake can face challenges with query performance. One effective way to tackle this issue is by optimising how data is organised using clustering keys. In this guide, we’ll explore clustering keys, how they work, and how they can significantly enhance query performance, especially for large datasets.

What Causes Query Performance Issues in Snowflake?

Snowflake stores data in a columnar format across micro-partitions. While this architecture is excellent for fast analytics, performance bottlenecks can arise when:

1. Large Datasets: Queries scan more data than necessary.
2. Inefficient Data Layout: Data is not organized to align with query patterns.
3. Frequent Full Table Scans: Indexing is absent in Snowflake, making clustering critical for optimization.

What Are Clustering Keys?

A clustering key is a Snowflake feature that helps optimise how data is stored and accessed. It determines the order in which data is physically organised within Snowflake’s micro-partitions.

Clustering keys improve query performance by:

• Reducing Scan Size: Limiting data scanned by queries.
• Improving Cache Efficiency: Organized data is faster to retrieve.
• Optimizing Range Queries: Queries filtering by ranges (e.g., date ranges) see the most benefit.

When Should You Use Clustering Keys?

Clustering keys are beneficial when:

• Your table has large datasets.
• Queries often use range filters (e.g., BETWEEN, >=, <=).
• Query performance slows as data volume grows.

Real-World Scenario

Imagine you’re analyzing sales data for an e-commerce platform. Your dataset contains millions of rows with fields like OrderDate, CustomerId, OrderAmount, etc. Most queries filter by date ranges, such as:

SELECT SUM(OrderAmount)  
FROM Sales  
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-03-31';

Without clustering, Snowflake scans all micro-partitions, leading to slower performance.

Step-by-Step Guide to Using Clustering Keys in Snowflake

1. Create a Sample Table

Start by creating a sample table with millions of rows to simulate a large dataset.

CREATE TABLE Sales (
    OrderId INT,
    OrderDate DATE,
    CustomerId INT,
    OrderAmount DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO Sales 
SELECT SEQ4(), 
       DATEADD(DAY, UNIFORM(1, 365, RANDOM()), '2024-01-01'), 
       UNIFORM(1, 1000, RANDOM()), 
       UNIFORM(10, 1000, RANDOM())
FROM TABLE(GENERATOR(ROWCOUNT => 1000000)); -- 1 million rows

2. Query Performance Without Clustering

Run a query and analyze its performance using the Query Profile in Snowflake.

SELECT SUM(OrderAmount) 
FROM Sales 
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-06-30';

In the Query Profile, note the scanned rows. Without clustering, Snowflake scans all micro-partitions, even those outside the date range.

3. Add a Clustering Key

Now, define a clustering key to optimize query performance.

ALTER TABLE Sales 
CLUSTER BY (OrderDate);

This reorganizes the data in micro-partitions based on OrderDate, aligning storage with query patterns.

4. Query Performance with Clustering

Re-run the same query and analyze its performance again.

SELECT SUM(OrderAmount) 
FROM Sales 
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-06-30';

In the Query Profile, observe the reduction in scanned rows. Queries now scan fewer micro-partitions, improving performance.

Advanced Tips for Clustering Keys

1. Multi-Column Clustering

If your queries filter on multiple columns, use a composite clustering key.

ALTER TABLE Sales 
CLUSTER BY (OrderDate, CustomerId);

This is useful for queries like:

SELECT SUM(OrderAmount) 
FROM Sales 
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-06-30' 
  AND CustomerId = 123;

2. Periodic Re-Clustering

Over time, as data grows, clustering can become less effective. Use Snowflake’s RECLUSTER functionality or set up automatic clustering:

ALTER TABLE Sales 
SET AUTO_CLUSTERING = TRUE;

This ensures data remains optimally clustered without manual intervention.

3. Use Clustering Depth

Monitor clustering depth using the SYSTEM$CLUSTERING_INFORMATION function.

SELECT SYSTEM$CLUSTERING_INFORMATION('Sales');

A high depth indicates that re-clustering is needed.

Code Example: Comparing Query Times

Here’s a simple comparison to highlight the benefits of clustering keys.

-- Query time without clustering
SELECT SUM(OrderAmount) 
FROM Sales 
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-06-30';

-- Add clustering key
ALTER TABLE Sales 
CLUSTER BY (OrderDate);

-- Query time with clustering
SELECT SUM(OrderAmount) 
FROM Sales 
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-06-30';

Record the execution times for both queries to see the performance boost.

Common Pitfalls to Avoid

1. Over-Clustering
   Avoid using too many columns in the clustering key, as this can lead to unnecessary overhead. Focus on columns that align with your most frequent query patterns.
2. Ignoring Data Growth
   Clustering can become less effective over time. Use automatic clustering for large, frequently updated datasets.
3. Assuming Instant Results
   Clustering takes time to reorganize data. Monitor progress using the TASK_HISTORY or INFORMATION_SCHEMA.TABLES views.

Real-World Impact

By optimising query performance with clustering keys, businesses can save time and costs. For instance:

• Retail Analytics: Analyse seasonal trends faster.
• IoT Data: Query billions of sensor logs efficiently.
• Customer Segmentation: Perform real-time analytics on user behaviour.

Conclusion

Clustering keys are a powerful yet often underutilised feature in Snowflake that can dramatically improve query performance for large datasets. By organising data to align with query patterns, you can reduce scan sizes, save computation time, and optimise costs.

If you’re a beginner looking to make the most of Snowflake’s capabilities, mastering clustering keys is an excellent first step. Try applying clustering to your datasets today and experience the performance boost firsthand!