Scalability & Performance Mastery

Snowflake · Warehouses & Performance

Theory

Snowflake scales compute in two directions. Scaling up (vertical) means resizing a warehouse to a larger size, which gives each query more CPU, memory and local disk; it helps big, complex queries finish faster and avoid spilling. Scaling out (horizontal) means a multi-cluster warehouse that adds more clusters of the same size when queries start to queue, then removes them when load drops.

Multi-cluster warehouses (Enterprise edition and above) use MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT, plus a scaling policy: Standard starts clusters quickly to avoid queuing, Economy waits to keep clusters busy and save credits. For example, a dashboard used by 200 people at 9 AM needs scale-out, while one slow monthly report needs scale-up.

Why we use it

Slow queries and many concurrent users are different problems. A bigger warehouse does not fix queuing well, and more clusters do not make a single query faster, so you need both tools.

When to use it

Scale up when a single query is slow, scans a lot of data or spills to disk. Scale out when many queries queue at the same time. If neither applies, look at pruning, clustering or the SQL itself before adding compute.

Vertical Scaling

Resizing a warehouse to a bigger T-shirt size gives more raw compute to a single query or session, which speeds up an individual heavy query but costs more per second while running.

Horizontal Scaling

Multi-cluster warehouses automatically add extra clusters of the same size to absorb concurrency spikes — many users querying at once — then scale back down once the spike passes, so read performance for hundreds of users stays consistent.

Choosing Between the Two

Use vertical scaling when a single query or job is too slow; use horizontal (multi-cluster) scaling when many people hit the same warehouse at once and start queueing.

Which One Do I Need? A Quick Flow

One query too slow → resize up (vertical). Many people queueing on the same warehouse → add clusters (horizontal): Query/job too slow? │ ┌────┴────┐ YES NO → queries queueing behind each other? │ │ ▼ YES Resize to a │ bigger T-shirt ▼ size (vertical) Raise MAX_CLUSTER_COUNT (horizontal, multi-cluster)

How it works

  1. Users report slowness
  2. Check Query Profile and warehouse load history
  3. Are queries queuing because of many concurrent users? Yes: Scale out: raise MAX_CLUSTER_COUNT. No: Look at the individual query.
  4. Is the query spilling or scanning a lot of data? Yes: Scale up: resize the warehouse. No: Tune SQL, pruning or clustering.
  5. Measure again and keep the cheapest setup that works

Example

CREATE WAREHOUSE bi_shared_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 6
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

SELECT * FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD(HOUR, -6, CURRENT_TIMESTAMP()),
  WAREHOUSE_NAME => 'BI_SHARED_WH'));

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic