Snowflake Architecture Study Guide — ARA-C01

Design: Architecting Enterprise-Scale Snowflake Solutions

This domain is one of the larger topic areas on the SnowPro Advanced: Architect exam (ARA-C01) under the current (carried-over, unverified) blueprint weighting. It tests your ability to translate business and technical requirements into correct Snowflake architectural choices: warehouse configuration, schema design, table types, performance optimization services, and cross-region/cross-cloud deployment patterns. Expect scenario-based questions where multiple options are plausible; the correct answer depends on knowing exact defaults, limits, and trade-offs.

Multi-Cluster Virtual Warehouses

A multi-cluster warehouse (MCW) allows Snowflake to automatically spin up additional clusters (each identical in size) to handle concurrency spikes, rather than queuing queries.

Key Configuration Parameters

Scaling policies:

• Standard: Adds a cluster immediately when a query is queued. Optimizes for performance. Removes a cluster after it has been idle for 2–3 consecutive checks (approx. 2–3 minutes).
• Economy: Adds a cluster only when the system estimates the queue will keep the cluster busy for at least 6 minutes. Optimizes for credit cost. Slower to scale out.

Auto-suspend stops the warehouse after the configured idle period. Auto-resume restarts the warehouse automatically on the next query — there is a brief cold-start latency.

Common Traps

• A single-cluster warehouse set to MAX_CLUSTER_COUNT = 1 is not a multi-cluster warehouse; it cannot scale out.
• Economy policy does not mean "cheaper always" — if workload is consistently high, it queues more queries and delivers worse SLAs. Standard is better when SLA is paramount.
• Auto-suspend timer resets on any warehouse activity, not just query execution — metadata operations count.
• Maximized mode (MIN = MAX) keeps clusters always running; useful for predictable high concurrency but burns credits even at idle.

Worked Scenario

Requirement: A BI team runs 200 concurrent dashboard queries every morning for 30 minutes, then the warehouse is idle the rest of the day. Cost must be controlled.

Reasoning: Use MCW with MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = 4, scaling policy = Economy (the 30-minute burst is long enough to justify cluster spin-up), and AUTO_SUSPEND = 60 seconds. Maximized mode would waste credits during the 23.5 idle hours.

Warehouse Sizing

Each warehouse size doubles the compute resources and doubles the credit consumption per hour relative to the previous size.

Key rules:

• Scaling up (larger size) reduces query latency for single large queries.
• Scaling out (more clusters via MCW) addresses concurrency, not single-query performance.
• A warehouse must be suspended and resumed to change size; the resize applies to the next query batch.

Common Traps

• Doubling warehouse size does not always halve query time — I/O-bound queries gain less than compute-bound queries.
• Credit consumption is per running cluster per hour, prorated to the second. An XL MCW with 3 active clusters consumes 48 credits/hour.
• Snowpark-optimized warehouses have a different credit multiplier and are intended for ML/Python workloads, not standard SQL.