Snowflake notes: 3

Data formats, Scaling Up-Out , Multi-cluster

Snowflake accepts both structured and semi-structured data formats for ingestion and processing.

Structured Data Formats

Snowflake supports traditional tabular data formats that follow a strict schema.

• CSV (Comma-Separated Values): The most common structured format for flat files.
• TSV (Tab-Separated Values): Delimited text files using tabs instead of commas.
• Other Delimited Files: Any plain text format using a valid single-byte delimiter.

Semi-Structured Data Formats

Snowflake natively supports several hierarchical and columnar formats, typically loading them into a VARIANT column for flexible querying.

• JSON: Commonly used for web APIs and logs.
• Avro: A binary, schema-based serialization format.
• ORC (Optimized Row Columnar): Optimized for Hive and big data environments.
• Parquet: A columnar storage format optimized for high-performance analytics.
• XML: Extensible Markup Language, supported through specialized parsing functions.

Structured Data Types

Internally, Snowflake also supports specific Structured Data Types for elements within a table column:

• ARRAY: A sequence of elements of a specific type.
• OBJECT: A collection of key-value pairs with defined keys and types.
• MAP: A collection that associates unique keys with specific values.

Difference between scaling up and scaling out virtual warehouse:

• Scaling Up = “Make one worker stronger”
• Scaling Out = “Add more workers”

Scaling Up (Vertical Scaling)

• You move from a smaller warehouse → larger one
  (e.g., X-Small → Small → Medium → Large → X-Large)
• More CPU, memory, and compute resources are allocated to a single cluster

When to use it

• Queries are complex or heavy
• You want faster execution time for individual queries

You’re dealing with:

• Large joins
• Aggregations
• Complex transformations

Example

If a query is slow, upgrading from Medium to Large can reduce runtime significantly.

Scaling Out (Horizontal Scaling):

• You enable multi-cluster warehouses
• Snowflake automatically spins up multiple clusters to handle concurrency

When to use it

• You have many users or queries running at the same time
• You want to avoid queuing delays
• Workloads are concurrent, not necessarily complex

Example

• 100 users running queries simultaneously
• Instead of upgrading size, Snowflake adds clusters to distribute the load

Scaling up or scaling out effect on storage:

In Snowflake, storage is completely decoupled from compute, so scaling operations (up or out) only impact compute resources and concurrency — not storage size or cost.

Snowflake’s architecture is built on:

• Storage layer → where your data lives (cloud object storage)
• Compute layer (virtual warehouses) → where queries run

Scaling (up or out) only touches the compute layer, not storage.

Multi-cluster snowflake environment: What scaling policies can be selected?

There are two scaling policies you can choose from:

1. Standard Scaling Policy

Balanced approach (default)

How it behaves

• Adds clusters gradually when query queues start building up
• Removes clusters slowly when load decreases

Goal -> Balance performance and cost

Get Leena Patil’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

When to use

• Mixed workloads
• Moderate concurrency
• When you want to avoid unnecessary compute cost

2. Economy Scaling Policy

Cost-optimized approach

How it behaves

• Delays adding new clusters until absolutely necessary
• Tries to reuse existing clusters as much as possible
• Scales down more aggressively when demand drops

Goal -> Minimize compute cost

Trade-off -> Queries may wait longer (more queuing)

When to use

• Cost-sensitive workloads
• Non-critical or batch queries
• When slight delays are acceptable

Components do you need to configure specifically for multi-cluster virtual warehouses:

A typical configuration looks like:

CREATE WAREHOUSE my_wh
WITH
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

There are a few specific components/settings you must configure:

1. MIN_CLUSTER_COUNT

Minimum number of clusters always running

• Ensures baseline capacity
• Even with low load, Snowflake keeps at least this many clusters active

Example

• MIN_CLUSTER_COUNT = 2 → Always 2 clusters running

When important

• Low-latency requirements
• Avoid cold-start delays

2. MAX_CLUSTER_COUNT

Maximum number of clusters Snowflake can scale to

• Defines the upper limit for concurrency scaling
• Snowflake auto-scales between min and max

Example

• MAX_CLUSTER_COUNT = 10 → Can scale up to 10 clusters

When important

• High concurrency workloads
• Prevent runaway costs

3. SCALING_POLICY

Controls how clusters are added/removed

• STANDARD → Performance-focused
• ECONOMY → Cost-focused

(You already saw this, but it’s a required config for multi-cluster behavior.)

4. AUTO_SUSPEND

Time (in seconds) before warehouse shuts down when idle

• Helps control cost
• Applies when all clusters are idle

5. AUTO_RESUME

Automatically restarts warehouse when a query arrives

• Critical for usability in multi-cluster setups

6. WAREHOUSE SIZE (Compute per cluster)

Defines the size of each cluster

• Example: MEDIUM, LARGE, etc.
• Important because:
• Scaling out = more clusters
• Scaling up = bigger cluster size