Snowflake AI Data Cloud Features & Architecture · Topic 10 of 17
Warehouse Workloads - Different Teams, High Concurrency, Complex Queries
Isolate teams on separate warehouses, handle high concurrency with multi-cluster scaling and tune complex queries with sizing, Gen 2 and QAS for COF-C03.
- 5 min
- Separate warehouses per team or workload: no interference, right sizing, cost visibility, access control.
- Resource monitors set credit quotas per warehouse or account, with notify and suspend actions.
- High concurrency -> multi-cluster auto-scale (Enterprise); Standard policy for speed, Economy for savings.
What it is
A workload is a group of queries with a similar pattern and owner, such as finance reporting, data engineering pipelines or data science exploration. Because Snowflake separates compute from storage, every workload can get its own warehouse while reading the same data.
The exam tests three workload situations: several teams sharing an account, high concurrency from many users, and complex queries that need more power.
How it works
Workload isolation principles
- Run similar queries together. A warehouse serving one kind of workload is easier to size and monitor.
- Give different workloads separate warehouses, so a heavy job never slows an unrelated team.
- Control spend per warehouse with resource monitors, timeouts and Auto-suspend.
- Grant USAGE on each warehouse only to the roles that should use it.
Different teams
When several teams share one Snowflake account, give each team (or each team and workload) its own warehouse:
| Benefit | How separate warehouses provide it |
|---|---|
| No interference | A large data science job cannot slow finance dashboards |
| Right sizing | Each warehouse is sized and configured for its own pattern |
| Cost visibility | Credits are reported per warehouse, so chargeback per team is simple |
| Cost control | A resource monitor per warehouse can notify or suspend when a quota is reached |
| Access control | Only the team's roles get USAGE on its warehouse |
Credit usage per warehouse is visible in Snowsight and in the WAREHOUSE_METERING_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE. A QUERY_TAG set in the session adds a label to queries for finer attribution inside one warehouse.
When a scenario says "costs must be tracked per department" or "one team's jobs slow another team", the answer is separate warehouses per team, often with a resource monitor on each.
High concurrency
High concurrency means many queries arriving at once, typically BI dashboards or applications with many users. Symptoms are queued queries while each query on its own is fast.
- Use a multi-cluster warehouse (Enterprise Edition or higher) in auto-scale mode. Snowflake adds clusters of the same size as queues form and removes them as load drops.
- Pick the Standard scaling policy to minimize waiting, or Economy to save credits when short waits are acceptable.
- Use Maximized mode (MIN equals MAX) only when the peak is constant and predictable.
MAX_CONCURRENCY_LEVEL(default 8) limits how many queries one cluster runs at once; Snowflake adds clusters, or queues queries, beyond that.STATEMENT_QUEUED_TIMEOUT_IN_SECONDScan cancel queries that wait too long in a queue.
The result cache helps too: identical repeated queries on unchanged data return without using warehouse time.
Complex queries
Complex queries are large joins, heavy aggregations or scans across big tables. A complex query runs on one cluster, so more clusters do not help it.
| Signal in the Query Profile | Response |
|---|---|
| Bytes spilled to local storage | Consider sizing up |
| Bytes spilled to remote storage | Size up; memory is far too small |
| Most time in table scan, few partitions pruned | Improve pruning (filters, clustering), covered in the performance chapter |
| Occasional outlier queries scanning much more data | Query Acceleration Service (Enterprise Edition or higher) |
- Size up the warehouse for consistently heavy queries. More compute and memory per cluster reduce spilling and run time.
- Consider Gen 2 standard warehouses, which speed up table scans and DML such as DELETE, UPDATE and MERGE.
- Use the Query Acceleration Service when a few outlier queries need extra scan power; it offloads parts of the work to serverless compute instead of making the whole warehouse bigger.
- Keep complex batch work on its own warehouse, separate from interactive users.
How the exam asks about this
- "Marketing and engineering share a warehouse, and engineering jobs slow marketing dashboards" -> create a separate warehouse for each team.
- "During the morning peak, queries queue but each finishes in seconds" -> multi-cluster warehouse in auto-scale mode.
- "A monthly report with large joins spills to remote storage" -> size up its warehouse.
Common mistakes
One big warehouse for everyone. It is hard to size, impossible to charge back cleanly, and one heavy job slows all users.
Adding clusters for a complex query. Multi-cluster adds concurrency only. A single heavy query needs a larger size.
Raising MAX_CONCURRENCY_LEVEL to cure queuing. More queries per cluster means fewer resources for each query. Scaling out is the usual fix.
Ignoring the Query Profile. Spilling, pruning and time per operator show whether a query needs a bigger warehouse or a better design.
What to remember for the exam
- Separate warehouses per team or workload: no interference, right sizing, cost visibility, access control.
- Resource monitors set credit quotas per warehouse or account, with notify and suspend actions.
- High concurrency -> multi-cluster auto-scale (Enterprise); Standard policy for speed, Economy for savings.
- MAX_CONCURRENCY_LEVEL defaults to 8 queries per cluster.
- Complex queries -> size up; spilling to remote storage is the clearest signal.
- Query Acceleration Service handles outlier scan-heavy queries; Gen 2 speeds scans and DML.
Separate warehouses do not mean separate data. All warehouses read the same tables, so isolating compute never requires copying data.
Check yourself
Why give each team its own warehouse?
It stops one team's workload from slowing another, allows separate sizing, and makes credit usage and chargeback clear per team.
Queries queue at peak but each runs fast. What is the best fix?
A multi-cluster warehouse in auto-scale mode, which adds clusters while the queue lasts.
A query spills to remote storage. What should the Practitioner do first?
Size up the warehouse, giving each cluster more memory and local storage so the query stops spilling.
Topic 10 of 17 in Snowflake AI Data Cloud Features & Architecture
Test yourself on Snowflake AI Data Cloud Features & Architecture
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.