COF-C02 Performance and Cost Optimization Concepts

Performance and Cost Optimization Concepts Detailed Explanation

Performance and cost in Snowflake are directly related because:

Compute = Warehouses = Billing
Storage is inexpensive, but compute costs scale with warehouse usage.

Understanding how performance choices influence cost (and vice versa) is essential both for the exam and real-world usage.

1. Warehouse Sizing & Configuration

Warehouse sizing determines how fast queries run and how much you pay.
The goal is to choose the right warehouse size and behavior for your workload.

1.1 Sizing

1.1.1 Choosing the Right Warehouse Size

Warehouse sizes range from X-SMALL to 4X-LARGE, each doubling compute resources.

When choosing the size, consider:

• Query complexity
  Heavy joins, large aggregations, and complex transformations benefit from larger sizes.

• Query volume
  More simultaneous work means more compute needed.

• Concurrency
  Many users working at the same time may require scale-out or multi-cluster.

1.1.2 Larger Warehouse: Pros & Cons

• Pros:

  • Faster execution for complex queries

  • Better performance for large datasets

• Cons:

  • Higher cost per second

A larger warehouse does not fix everything—sometimes query tuning or clustering is more effective.

1.2 Multi-Warehouse Strategy

Instead of using one huge warehouse, use separate warehouses for different workloads.

1.2.1 Recommended Patterns

Use different warehouses for:

• ETL/ELT
  Typically scheduled jobs or heavy transformations.

• BI/Reporting
  High concurrency, mostly read-heavy queries.

• Data science / ad hoc
  Spiky workloads, unpredictable query patterns.

1.2.2 Why This Helps

• Prevents workload interference

• Makes tuning easier (each warehouse isolated)

• Allows separate auto-suspend rules

• Improves concurrency

• Reduces total cost by suspending non-active warehouses

1.2.3 What to Avoid

Do not use one giant warehouse for everything.

It creates contention, wastes money, and limits flexibility.

1.3 Auto-Suspend & Auto-Resume

1.3.1 Auto-Suspend

Auto-suspend turns off warehouses after N seconds of inactivity (common values: 60–300 seconds).

Benefits:

• Avoids paying for idle compute

• Warehouses stop billing immediately when suspended

1.3.2 Auto-Resume

When a new query arrives:

• The warehouse automatically resumes

• Resume is near-instant

• Queries do not fail because of suspension

1.3.3 Exam Tip

Snowflake automatically resumes a suspended warehouse when needed.
Suspended warehouses do not block queries.

2. Concurrency and Multi-Cluster Warehouses

Snowflake warehouses can run into queueing when concurrency is high.

2.1 Single-Warehouse Concurrency Limits

A single warehouse:

• Has limited compute threads

• Can only process a certain number of queries at once

• Will queue queries when overloaded

This leads to slow dashboards or delayed job execution.

2.2 Multi-Cluster Warehouses

Multi-cluster warehouses solve concurrency problems by adding more clusters behind a single warehouse.

2.2.1 How It Works

You configure:

• Minimum clusters

• Maximum clusters

• A scaling policy:

  • STANDARD (faster scaling)

  • ECONOMY (slower scaling, cheaper)

When Snowflake detects queueing:

• It launches an additional cluster

• Queries are distributed across clusters

• When concurrency decreases, extra clusters automatically shut down

2.2.2 Ideal Use Cases

• BI dashboards with many users

• Heavy analyst workloads

• Teams sharing the same logical warehouse

2.2.3 Trade-off

More clusters = higher cost.

Therefore, you should tune min/max cluster counts carefully.

3. Micro-Partitions & Clustering

This section affects both performance and cost, because better pruning = fewer scanned partitions = lower compute usage.

3.1 Micro-Partitions

3.1.1 What They Are

Micro-partitions are:

• Internal storage units (~16 MB compressed)

• Immutable

• Automatically created by Snowflake

Each partition stores metadata such as:

• Column min/max

• Distinct value counts

• Null counts

This metadata is used for pruning.

3.1.2 Why Micro-Partitions Matter

Pruning allows Snowflake to:

• Skip scanning irrelevant partitions

• Reduce compute needed

• Improve query speed

Example:
If a query filters WHERE date = '2024-01-01', only micro-partitions overlapping that date are scanned.

3.2 Clustering

Clustering determines how evenly and efficiently micro-partitions are organized.

3.2.1 Effects of Clustering

• Good clustering:
  High pruning → fewer partitions scanned → faster queries → lower cost

• Poor clustering:
  Many partitions scanned → slower queries → higher cost

3.2.2 Natural Clustering

Snowflake naturally sorts data during ingestion.
For many tables, natural clustering is sufficient.

3.2.3 Clustering Keys

When natural clustering is insufficient, you define a clustering key on frequently filtered columns.

Typical keys:

• date

• customer_id

• region

Snowflake will automatically recluster as data grows.

3.2.4 Search Optimization

A special feature enabling much faster:

• Point lookups

• Highly selective queries

• Contains / IN / LIKE queries

It is useful when normal clustering is not effective.

3.2.5 Exam Expectation

You must understand when to cluster and when to scale the warehouse:

• Performance issue on single heavy queries → scale up

• Too many scanned partitions → clustering

• Too many queued queries → multi-cluster warehouse

4. Query Optimization Practices

This section focuses on SQL techniques and Snowflake tools that improve query performance.

4.1 Use Query Profile

Query Profile shows:

• Amount of data scanned

• Micro-partition pruning efficiency

• Distribution of work

• JOIN behavior

• Bottlenecks

Learning to read Query Profile is essential for tuning.

4.2 SQL Best Practices

4.2.1 Filter Early

Push filters as early as possible:

SELECT ...
FROM table
WHERE date > '2024-01-01';

This reduces the amount of data scanned.

4.2.2 Project Only Needed Columns

Avoid SELECT *, especially in wide tables.

4.2.3 Avoid Unnecessary DISTINCT or ORDER BY

These operations force Snowflake to perform extra work:

• Sorting

• Deduplication

• Large data movement

Only use them when necessary.

4.2.4 Use Appropriate Data Types

Correct data types:

• Improve storage

• Improve micro-partition metadata

• Improve pruning

• Reduce query cost

4.3 Data Loading Optimization

For loading:

• Avoid extremely large files

• Use many smaller files for parallelism

Example:

• Instead of one 5GB file → use 50 files of 100MB each

This allows Snowflake to parallelize loading across multiple threads.

5. Cost Optimization Levers

Snowflake provides many ways to control and reduce cost.

5.1 Right-Size Warehouses & Auto-Suspend

Use:

• Smallest warehouse that meets performance needs

• Aggressive auto-suspend policies

5.2 Separate Warehouses for Workloads

• ETL at night

• BI during the day

• Data science workloads bursty

Each warehouse suspends independently → reduces wasted compute.

5.3 Use Caches & Materialized Views

• Result cache reduces repeated query cost

• Materialized views pre-compute expensive logic

5.4 Tune Data Loading

• Compress files

• Use proper file sizes

• Use COPY options effectively

Better ingestion = lower cost.

5.5 Monitor Usage Carefully

Important monitoring tools:

• Account Usage views (SNOWFLAKE.ACCOUNT_USAGE)

• Warehouse utilization metrics

• Query history

These help identify:

• Inefficient queries

• Underutilized warehouses

• Expensive transformations

Performance and Cost Optimization Concepts (Additional Content)

1. Three Types of Caching (Result Cache, Metadata Cache, Data Cache)

Caching is a major contributor to Snowflake performance and cost efficiency. Each cache type operates at a different layer of Snowflake’s architecture and behaves differently.

1.1 Result Cache

The result cache stores the results of previously executed queries.

Key characteristics:

• Stored in the Cloud Services layer

• Shared across all warehouses in the same account

• Returned instantly if the query text, role, session settings, and underlying data have not changed

• Zero compute cost because no warehouse needs to run

This cache provides performance acceleration for repeated BI dashboards or recurring analytical queries.

1.2 Metadata Cache

The metadata cache stores statistics and metadata about micro-partitions, including:

• Minimum and maximum values

• Null counts

• Distinct values

• Partition location

• Other structural statistics

Metadata cache lives in the Cloud Services layer. It enables partition pruning, which reduces the number of micro-partitions scanned. Pruning greatly reduces compute cost.

1.3 Data Cache

The data cache lives on the local SSD of each warehouse cluster.
Key behaviors:

• Warehouse-specific (not shared between warehouses)

• Lost when the warehouse suspends

• Helps accelerate subsequent scans of the same micro-partitions

Although less durable, the data cache improves performance for repeated scanning workloads such as iterative analytics.

2. Query Profile Pruning Metrics

Query Profile is Snowflake’s main tool for diagnosing performance and compute cost.

2.1 What Query Profile Shows

Important pruning metrics include:

• Micro-partitions scanned

• Micro-partitions pruned

• Percentage of total partitions pruned

These metrics indicate how efficiently Snowflake avoids unnecessary reads.

2.2 Interpreting Pruning

• High pruning percentage indicates that clustering and partition organization are efficient

• Low pruning percentage often means:

  • Poor clustering

  • High data fragmentation

  • Queries filtering on unclustered columns

  • Need for different clustering keys or search optimization

Efficient pruning directly reduces cost because fewer micro-partitions are scanned.

3. Scale-Up vs Scale-Out (Warehouse Tuning)

Snowflake offers two scaling strategies that serve different purposes.

3.1 Scale-Up (Vertical Scaling)

Scale-up means selecting a larger warehouse size, such as moving from M to L.

Effects:

• More compute resources per cluster

• Faster performance for single large queries

• No improvement in concurrency

• Higher cost per second of execution

Use scale-up when a small number of heavy queries require more computational power.

3.2 Scale-Out (Horizontal Scaling, Multi-Cluster)

Scale-out means adding additional clusters within a multi-cluster warehouse.

Effects:

• Increased concurrency

• No improvement in single-query performance

• More cost only when additional clusters are activated

Scale-out is useful for BI dashboards, team-wide workloads, and spiky concurrency scenarios.

4. Materialized Views — Performance and Cost Behavior

Materialized Views (MVs) are stored query results maintained incrementally.

4.1 Benefits

• Excellent for repeated expensive aggregations

• Reduce compute by serving precomputed results

• Provide significant performance gains for dashboards and repetitive workloads

4.2 Maintenance Behavior

Snowflake updates MVs incrementally whenever underlying micro-partitions change.
This maintenance process consumes compute resources.

4.3 Cost Considerations

Materialized Views introduce:

• Additional storage for the materialized data

• Additional compute cost for maintaining freshness

MVs are best for stable, well-defined workloads with predictable patterns.

5. Dynamic Tables (Performance–Cost Model)

Dynamic Tables support declarative data pipelines by automatically refreshing based on a defined freshness target.

5.1 Freshness Parameter

Each Dynamic Table specifies how fresh its data must be.

Examples:

• 1 minute

• 5 minutes

• 1 hour

5.2 Cost Implications

• Lower freshness values cause more frequent refresh operations, increasing compute cost

• Higher freshness values reduce refresh frequency and compute cost

Dynamic Tables enable cost-tuned ELT pipelines where freshness and compute cost must be balanced carefully.

6. Query Acceleration Service (QAS)

Query Acceleration Service supplements warehouse compute with additional temporary resources.

6.1 Purpose

QAS is designed for:

• Irregularly heavy operations

• Highly complex queries

• Queries that do not justify scaling the warehouse permanently

6.2 Characteristics

• Automatically activated for qualifying queries when enabled

• Billed separately from warehouse compute

• Does not require administrators to change warehouse size

QAS is best for unpredictable workloads where sizing a warehouse to peak demand would be costly.

7. Automatic Clustering — Cost Considerations

Automatic Clustering continuously reorganizes micro-partitions to maintain efficient clustering.

7.1 Benefits

• Improves pruning efficiency

• Reduces query scan cost

• Maintains clustering depth as data grows or becomes disordered

7.2 Costs

Automatic Clustering uses compute under the hood. This cost is billed to the user’s account.

7.3 When to Use

Recommended for:

• Very large tables

• Tables where queries frequently filter on specific clustering keys

• Tables whose clustering depth significantly deteriorates over time

Admins must evaluate cost versus performance gains.

8. COPY INTO File Size Best Practices

File size impacts parallelism and throughput of Snowflake’s bulk loading process.

8.1 Optimal File Size

For compressed files:

• 100–250 MB per file is optimal

This size balances parallelism and efficiency.

8.2 What to Avoid

Files smaller than roughly 10 MB:

• Cause too many small tasks

• Reduce load parallelism

• Increase overhead

Files that are too large:

• Decrease throughput

• Increase load latency

• Reduce the effectiveness of warehouse parallelism

8.3 Best Practice

Use many evenly sized files to maximize the number of parallel threads available in the warehouse.