Warehouses

Question 1

What is a virtual warehouse in Snowflake?

Answer 1

A cluster of compute resources including CPU capacity, memory, and temporary storage needed for data operations in Snowflake.

Virtual warehouses are essential for query processing in Snowflake’s architecture.

Question 2

What are the three stages of Snowflake architecture?

Answer 2

• Database storage
• Query processing
• Cloud services

Each stage serves a specific function in the overall architecture.

Question 3

What is the purpose of virtual warehouses in Snowflake?

Answer 3

To allow independent scaling of storage and compute resources.

More data can be added to storage without affecting virtual warehouses.

Question 4

What types of operations require virtual warehouses?

Answer 4

• Querying rows from tables and views
• Updating records
• Loading data into tables
• Unloading data from tables

These operations necessitate compute resources provided by virtual warehouses.

Question 5

What command is often used before running USE DATABASE in Snowflake?

Answer 5

USE WAREHOUSE

This command is crucial for specifying which warehouse to use for query processing.

Question 6

What is the default size for warehouses created with the CREATE WAREHOUSE command?

Answer 6

Extra small

This contrasts with the default size for warehouses created via the web interface, which is extra-large.

Question 7

What is the minimum billing period when starting a warehouse in Snowflake?

Answer 7

60 seconds

Billing operates on a per-second basis after this minimum period.

Question 8

What should you do to improve performance for larger and more complex queries?

Answer 8

Use larger warehouses

Larger warehouses are beneficial for handling complex queries efficiently.

Question 9

What happens when a warehouse enters suspended mode?

Answer 9

It can be resumed automatically if the auto-resume feature is enabled.

Otherwise, manual commands are required to resume or suspend the warehouse.

Question 10

What are multi-cluster warehouses used for?

Answer 10

To scale up compute resources and manage user and query concurrency needs.

They allow for better performance during peak loads.

Question 11

What are the two modes of operation for multi-cluster warehouses?

Answer 11

• Maximized mode
• Auto-scale mode

Each mode serves different concurrency and resource management needs.

Question 12

In maximized mode, how are clusters managed?

Answer 12

All clusters within the warehouse are started, providing maximum resources at all times.

This mode is suitable for consistent usage patterns.

Question 13

What is the difference between scaling up and scaling out?

Answer 13

• Scaling up: Vertical scaling, resizing a warehouse to a larger size.
• Scaling out: Horizontal scaling, adding more clusters to a multi-cluster warehouse.

Each approach addresses different performance issues.

Question 14

What is the purpose of the economy scaling policy in Snowflake?

Answer 14

To conserve credits by keeping clusters fully-loaded and avoiding starting new clusters unless necessary.

This policy may lead to some queries being queued.

Question 15

True or False: Scaling out is more effective for handling large complex queries.

Answer 15

False

Scaling out improves concurrency, while scaling up addresses performance for complex queries.

Question 16

What is the suggested best practice for managing warehouses?

Answer 16

Have two warehouses: a small one for data loading and a larger one for queries.

This approach balances cost and performance.