Optimization Techniques: Clustering, Search Optimization & Materialized Views

Architecture Diagram 1: Clustering Key Architecture

Architecture Diagram 2: Search Optimization Service

Architecture Diagram 3: Clustering Key Impact

Architecture Diagram 4: Materialized View Performance

Architecture Diagram 5: Materialized Views Architecture

Detailed Explanation

What are Clustering Keys?

Clustering keys define how data is organized within micro-partitions, optimizing query performance by improving data locality.

Clustering Key Selection

• Choose columns frequently used in WHERE, JOIN, GROUP BY
• Low-to-medium cardinality works best (date, region, status)
• High-cardinality (UUID, microsecond timestamps) less effective
• Track via CLUSTERING_INFORMATION() — target depth < 1.5

Automatic Clustering

• Background service continuously optimizes data placement
• Runs asynchronously — no user intervention
• Cost-based approach: re-clusters only when benefits outweigh costs
• Moves rows between micro-partitions to reduce overlap

Search Optimization Service

• Not recommended for low-cardinality or range query columns

Materialized Views

• Pre-compute and store query results on disk
• Standard views re-execute each time; MVs return cached results
• Automatic refresh — incremental updates on base table changes
• Optimizer transparently rewrites queries to use MVs when possible

Performance Monitoring and Tuning

1. Use QUERY_HISTORY for execution statistics
2. Use PROFILE for detailed execution plans
3. Monitor partition pruning effectiveness, clustering depth, MV freshness
4. Identify slow queries and optimization opportunities

Key Takeaway: Not all tables need clustering — consider query patterns and cost. Use search optimization only for high-cardinality equality lookups.

Key Concepts Table

Code Examples

-- Example 1: Create table with clustering key
CREATE TABLE sales_data (
    id INTEGER,
    transaction_date DATE,
    region VARCHAR(10),
    product_id VARCHAR(20),
    amount NUMBER(10,2)
)
CLUSTER BY (transaction_date, region);

-- Example 2: Alter table to add clustering key
ALTER TABLE sales_data CLUSTER BY (transaction_date, region);

-- Example 3: Check clustering information
SELECT *
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_INFORMATION(
    TABLE_NAME => 'sales_data',
    SCHEMA_NAME => 'PUBLIC'
));

-- Example 4: Create materialized view
CREATE MATERIALIZED VIEW sales_summary_mv
AS
SELECT 
    transaction_date,
    region,
    COUNT(*) as transaction_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount
FROM sales_data
GROUP BY 1, 2;

-- Example 5: Enable search optimization
ALTER TABLE sales_data ADD SEARCH OPTIMIZATION ON EQUALITY (product_id);

-- Example 6: Refresh materialized view
ALTER MATERIALIZED VIEW sales_summary_mv REFRESH;

-- Example 7: Check materialized view freshness
SELECT 
    name,
    refresh_group,
    last_refresh_time,
    seconds_since_refresh
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE name = 'SALES_SUMMARY_MV';

-- Example 8: Analyze clustering effectiveness
SELECT 
    table_name,
    clustering_key,
    total_partition_count,
    total_constant_partition_count,
    average_partition_depth,
    average_overlap_bytes,
    average_partition_depth * average_overlap_bytes as clustering_score
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_INFORMATION(
    TABLE_NAME => 'sales_data'
));

-- Example 9: Check search optimization status
SHOW SEARCH OPTIMIZATION IN TABLE sales_data;

-- Example 10: Compare query performance
-- Without materialized view
EXPLAIN SELECT 
    transaction_date,
    region,
    SUM(amount)
FROM sales_data
WHERE transaction_date >= '2024-01-01'
GROUP BY 1, 2;

-- With materialized view (optimizer should rewrite)
EXPLAIN SELECT 
    transaction_date,
    region,
    SUM(amount)
FROM sales_summary_mv
WHERE transaction_date >= '2024-01-01'
GROUP BY 1, 2;

Performance Metrics

Best Practices

1. Choose clustering keys wisely: Select columns frequently used in WHERE, JOIN, and GROUP BY clauses. Prefer low-to-medium cardinality columns for better pruning.

2. Monitor clustering depth: Regularly check clustering information to identify tables with poor clustering. Consider re-clustering for tables with depth > 2.0.

3. Use search optimization selectively: Enable search optimization only for high-cardinality columns used in equality searches. Avoid for low-cardinality or range query columns.

4. Create materialized views for aggregations: Materialize complex aggregations that are queried frequently. Consider refresh frequency requirements and storage costs.

5. Monitor materialized view freshness: Ensure materialized views are refreshed within SLA requirements. Use manual refresh for time-critical scenarios.

6. Leverage result cache: Ensure identical queries can hit the cache by avoiding non-deterministic functions. Use QUERY_TAG to group similar queries.

7. Analyze query patterns: Use QUERY_HISTORY to identify slow queries and optimize accordingly. Focus on queries with poor partition pruning.

8. Consider composite clustering keys: Use multiple columns for clustering when queries frequently filter on multiple columns. Order columns by selectivity.

9. Balance optimization overhead: Consider storage and compute costs when implementing optimization features. Not all tables need clustering or search optimization.

10. Regular performance reviews: Conduct weekly optimization reviews to identify performance trends and adjust strategies accordingly.

See Also

• Snowflake Architecture — Micro-partition architecture details
• Warehouse Management — Warehouse sizing for optimization
• Semi-Structured Data — VARIANT optimization
• Snowpipe Ingestion — Ingestion performance
• PySpark Iceberg Tables — Iceberg table optimization
• Data Warehouse Concepts — Data warehouse design principles