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
In a nutshell
  • 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.

Shared storageone copy of dataread byFinance warehousereportingShared storageone copy of dataread byETL warehousepipelinesShared storageone copy of dataread byData science warehouseSnowpark

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:

BenefitHow separate warehouses provide it
No interferenceA large data science job cannot slow finance dashboards
Right sizingEach warehouse is sized and configured for its own pattern
Cost visibilityCredits are reported per warehouse, so chargeback per team is simple
Cost controlA resource monitor per warehouse can notify or suspend when a quota is reached
Access controlOnly 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.

Resource monitorcredit quotanotify at 80 percentAdminsalertsuspend at 100 percentTeam warehousestopped
Exam tip

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_SECONDS can 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 ProfileResponse
Bytes spilled to local storageConsider sizing up
Bytes spilled to remote storageSize up; memory is far too small
Most time in table scan, few partitions prunedImprove pruning (filters, clustering), covered in the performance chapter
Occasional outlier queries scanning much more dataQuery 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.
Complex querylarge joinspills to remoteQuery Profileshows spillingfixSize upmore memory per cluster

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.
Watch out

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.