Warehouse sizes and scaling in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a warehouse with the smallest size.

Snowflake

CREATE WAREHOUSE my_wh WITH WAREHOUSE_SIZE = '[1]';

Drag options to blanks, or click blank then click option'

AX-SMALL

BLARGE

CMEDIUM

DX-LARGE

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a larger size than needed wastes resources.

Using incorrect size names causes errors.

The smallest warehouse size in Snowflake is X-SMALL. It uses the least compute resources.

2fill in blank

medium

Complete the code to set the warehouse to auto-scale between 1 and 3 clusters.

Snowflake

ALTER WAREHOUSE my_wh SET MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = [1];

Drag options to blanks, or click blank then click option'

A2

B10

C5

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Setting max cluster count less than min cluster count.

Using values outside allowed range.

Setting MAX_CLUSTER_COUNT to 3 allows the warehouse to scale up to 3 clusters automatically.

3fill in blank

hard

Fix the error in the code to enable multi-cluster scaling.

Snowflake

ALTER WAREHOUSE my_wh SET SCALING_POLICY = '[1]';

Drag options to blanks, or click blank then click option'

ADYNAMIC

BSTANDARD

CMANUAL

DAUTOMATIC

Attempts:

3 left

💡 Hint

Common Mistakes

Using unsupported scaling policy names.

Confusing manual and automatic scaling options.

The correct value to enable multi-cluster scaling is STANDARD. Other values are invalid.

4fill in blank

hard

Fill both blanks to create a warehouse that auto-suspends after 5 minutes and resumes automatically.

Snowflake

CREATE WAREHOUSE my_wh WITH AUTO_SUSPEND = [1], AUTO_RESUME = [2];

Drag options to blanks, or click blank then click option'

A300

BTRUE

CFALSE

D600

Attempts:

3 left

💡 Hint

Common Mistakes

Using auto-suspend time in minutes instead of seconds.

Setting auto-resume to FALSE disables automatic resume.

Auto-suspend time is set in seconds, so 300 means 5 minutes. Auto-resume must be TRUE to resume automatically.

5fill in blank

hard

Fill all three blanks to create a warehouse with size LARGE, max 4 clusters, and auto-suspend after 10 minutes.

Snowflake

CREATE WAREHOUSE my_wh WITH WAREHOUSE_SIZE = '[1]', MAX_CLUSTER_COUNT = [2], AUTO_SUSPEND = [3];

Drag options to blanks, or click blank then click option'

AMEDIUM

B4

C600

DLARGE

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing warehouse sizes.

Using auto-suspend time in minutes instead of seconds.

The warehouse size is LARGE, max clusters set to 4, and auto-suspend time is 600 seconds (10 minutes).

Practice

1. What does the size of a Snowflake warehouse primarily determine?

easy

A. The storage capacity for data

B. The amount of computing power available for queries

C. The number of users who can access the database

D. The network bandwidth for data transfer

Solution

1. Step 1: Understand warehouse size meaning

   Warehouse size in Snowflake controls the compute resources allocated.

2. Step 2: Differentiate from storage and network

   Storage and network bandwidth are managed separately, not by warehouse size.

3. Final Answer:

   The amount of computing power available for queries -> Option B

4. Quick Check:

   Warehouse size = compute power [OK]

Hint: Warehouse size means compute power, not storage or users [OK]

Common Mistakes:

• Confusing warehouse size with storage capacity
• Thinking warehouse size limits user access
• Assuming network bandwidth depends on warehouse size

2. Which Snowflake warehouse size is correctly written in a SQL command to create a warehouse?

easy

A. CREATE WAREHOUSE mywh WAREHOUSE_SIZE = 'LARGE';

B. CREATE WAREHOUSE mywh WAREHOUSE_SIZE = 'medium';

C. CREATE WAREHOUSE mywh WITH WAREHOUSE_SIZE = 'XSMALL';

D. CREATE WAREHOUSE mywh SET SIZE = 'SMALL';

Solution

1. Step 1: Check correct syntax for warehouse size

   The correct syntax uses WAREHOUSE_SIZE = 'SIZE' with uppercase size names.

2. Step 2: Validate option correctness

   CREATE WAREHOUSE mywh WAREHOUSE_SIZE = 'LARGE'; uses correct syntax and valid size 'LARGE'. Others have syntax or size errors.

3. Final Answer:

   CREATE WAREHOUSE mywh WAREHOUSE_SIZE = 'LARGE'; -> Option A

4. Quick Check:

   Correct syntax uses WAREHOUSE_SIZE = 'SIZE' [OK]

Hint: Use WAREHOUSE_SIZE = 'SIZE' with uppercase size [OK]

Common Mistakes:

• Using lowercase size names
• Using WITH keyword
• Using SET SIZE instead of WAREHOUSE_SIZE

3. Given a Snowflake warehouse set to size 'MEDIUM' with auto-suspend enabled at 5 minutes, what happens if no queries run for 6 minutes?

medium

A. The warehouse suspends automatically after 5 minutes of inactivity

B. The warehouse continues running indefinitely

C. The warehouse scales up to a larger size automatically

D. The warehouse deletes all cached data

Solution

1. Step 1: Understand auto-suspend behavior

   Auto-suspend pauses the warehouse after the specified inactivity time to save costs.

2. Step 2: Apply to given time

   Since no queries run for 6 minutes, which is more than 5 minutes, the warehouse suspends.

3. Final Answer:

   The warehouse suspends automatically after 5 minutes of inactivity -> Option A

4. Quick Check:

   Auto-suspend triggers after inactivity time [OK]

Hint: Auto-suspend pauses warehouse after set idle time [OK]

Common Mistakes:

• Thinking warehouse keeps running without queries
• Assuming warehouse scales automatically without manual setting
• Confusing auto-suspend with cache clearing

4. You configured a multi-cluster warehouse with MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=3, but queries queue up during peak times. What is the likely cause?

medium

A. The warehouse is suspended manually

B. Warehouse size is too large causing delays

C. Auto-suspend is set to zero minutes

D. Auto-scaling is not enabled or configured incorrectly

Solution

1. Step 1: Understand multi-cluster warehouse scaling

   Multi-cluster warehouses scale clusters between min and max counts automatically if auto-scaling is enabled.

2. Step 2: Identify cause of query queuing

   If queries queue despite max clusters > 1, auto-scaling might be off or misconfigured, preventing cluster addition.

3. Final Answer:

   Auto-scaling is not enabled or configured incorrectly -> Option D

4. Quick Check:

   Multi-cluster needs auto-scaling enabled to add clusters [OK]

Hint: Check auto-scaling enabled for multi-cluster warehouses [OK]

Common Mistakes:

• Assuming large warehouse size fixes queuing
• Confusing auto-suspend with scaling
• Not verifying if warehouse is suspended

5. A company wants to handle unpredictable spikes in query load without manual intervention. Which Snowflake warehouse configuration best supports this?

hard

A. Set warehouse size to X-LARGE and disable auto-suspend

B. Create multiple single-cluster warehouses and switch between them manually

C. Use a multi-cluster warehouse with auto-scaling enabled and set MIN_CLUSTER_COUNT=1, MAX_CLUSTER_COUNT=5

D. Set warehouse size to SMALL and rely on auto-resume only

Solution

1. Step 1: Identify need for handling spikes automatically

   The company wants automatic scaling to handle unpredictable query spikes without manual steps.

2. Step 2: Evaluate options for auto-scaling

   Multi-cluster warehouses with auto-scaling and a range of cluster counts allow dynamic scaling up and down.

3. Step 3: Compare other options

   Set warehouse size to X-LARGE and disable auto-suspend wastes resources by disabling suspend; B requires manual switching; D is too small and lacks scaling.

4. Final Answer:

   Use a multi-cluster warehouse with auto-scaling enabled and set MIN_CLUSTER_COUNT=1, MAX_CLUSTER_COUNT=5 -> Option C

5. Quick Check:

   Multi-cluster auto-scaling handles spikes best [OK]

Hint: Multi-cluster with auto-scaling handles spikes automatically [OK]

Common Mistakes:

• Choosing fixed large size wastes cost
• Relying on manual warehouse switching
• Ignoring cluster count settings