Snowflake AI Data Cloud Features & Architecture · Topic 8 of 17

Warehouse Configuration by Use Case - Ad-Hoc, Data Loading, BI

Pick warehouse type, size, Auto-suspend and cluster settings for ad-hoc queries, data loading and BI dashboards, the three use cases tested on COF-C03.

  • 5 min
In a nutshell
  • Ad-hoc: small, short Auto-suspend, AUTO_RESUME, timeouts and resource monitors; QAS for outlier scans.
  • Data loading: size to the number of files; files about 100 to 250 MB compressed; dedicated warehouse; Snowpipe is serverless.
  • BI and reporting: multi-cluster auto-scale for concurrency; longer Auto-suspend to keep cache; result cache for repeats.

What it is

There is no single best warehouse. The right type, size, cluster count and Auto-suspend value depend on the workload pattern: how many queries arrive at once, how heavy each one is, and how often the warehouse sits idle.

The exam names three use cases in the outline: ad-hoc queries, data loading, and BI and reporting. For each, you should be able to pick a sensible starting configuration and say why.

WorkloadpatterndecidesSizequery weightdecidesClustersconcurrencydecidesAuto suspendidle gaps and cache

How it works

The starting configurations

SettingAd-hoc queriesData loadingBI and reporting
TypeStandardStandard (Gen 2 helps DML and MERGE heavy pipelines)Standard
SizeStart small (XSMALL or SMALL), resize for heavy explorationMatch to number and size of files; often SMALL or MEDIUMSize for typical dashboard query; MEDIUM is common
ClustersSingle clusterSingle clusterMulti-cluster auto-scale (Enterprise)
Scaling policyNot neededNot neededStandard for responsiveness, Economy to save credits
Auto-suspendShort (for example 60 to 300 seconds)Short; suspend as soon as the batch endsLonger, to keep the cache warm between dashboard refreshes
Extra guardrailsStatement timeout, resource monitorSeparate from query warehousesRely on result cache for repeated queries

Ad-hoc queries

Ad-hoc work by analysts and data scientists is unpredictable: idle for a while, then a burst of queries of very different weight. Start with a small warehouse and short Auto-suspend, because per-second billing means a suspended warehouse costs nothing.

Add guardrails so one runaway query cannot burn credits: set STATEMENT_TIMEOUT_IN_SECONDS on the warehouse and attach a resource monitor. Keep ad-hoc users off production warehouses so exploration never slows scheduled jobs.

For occasional outlier queries that scan far more data than the rest, the Query Acceleration Service (Enterprise Edition or higher) can offload parts of the scan to serverless compute instead of permanently sizing up.

Data loading

Loading speed depends mostly on the number of files, because each file is loaded by one thread. A larger warehouse helps only when there are enough files to keep its extra threads busy.

  • Aim for compressed files of roughly 100 to 250 MB, so loads parallelize well.
  • A single huge file does not load faster on a bigger warehouse; split it.
  • A small warehouse is often enough for regular batches; scale up for large backfills with many files.
  • Use a dedicated loading warehouse so loads and analytic queries do not compete.
Many files100 to 250 MBparallel threadsLoading warehouseSMALL or MEDIUMCOPY INTOTarget tablethen Auto suspend

Continuous loading with Snowpipe does not use a user warehouse at all; it runs on serverless compute. Choose a loading warehouse for scheduled bulk COPY INTO jobs.

BI and reporting

Dashboards create many short queries from many users at the same time, often at predictable peaks such as Monday morning. The bottleneck is concurrency, so the answer is scaling out, not up.

  • Use a multi-cluster warehouse in auto-scale mode, with MIN_CLUSTER_COUNT 1 and MAX set to cover peak load.
  • Choose the Standard policy when users must not wait, or Economy if some queuing is acceptable to save credits.
  • Use a longer Auto-suspend so the warehouse local cache stays warm between refreshes.
  • Repeated identical dashboard queries can be answered from the result cache (kept for 24 hours, with the 24 hours reset each time it is reused, up to 31 days, if the data has not changed), which uses no warehouse time.
Dashboardshundreds of userspeak loadBI warehousemulti cluster auto scaleaddsClustersas queues form
Exam tip

Translate the scenario into one bottleneck. "Many users" means concurrency (scale out). "One huge query" means power (scale up). "Many small files" means parallel loading threads (size to the file count).

How the exam asks about this

  • "Dashboards slow down every morning when 300 users log in; each query is simple" -> multi-cluster auto-scale warehouse.
  • "Nightly COPY INTO of one 40 GB file takes too long even on a large warehouse" -> split the file into many 100 to 250 MB files.
  • "Analysts run occasional exploratory queries; costs must stay low" -> small warehouse, short Auto-suspend, AUTO_RESUME on, resource monitor.

Common mistakes

Sizing up for BI concurrency. A bigger single cluster runs each query faster, but queuing from hundreds of users is solved by more clusters.

Sharing one warehouse for loading and BI. Heavy loads then slow dashboards. Separate warehouses isolate workloads and make cost per team visible.

Using an XLARGE warehouse to load one file. One file is one thread. Most of the warehouse sits idle while you pay for all of it.

Setting a very short Auto-suspend on BI warehouses. Frequent suspends drop the local cache and add resume delays, so dashboards feel slower.

What to remember for the exam

  • Ad-hoc: small, short Auto-suspend, AUTO_RESUME, timeouts and resource monitors; QAS for outlier scans.
  • Data loading: size to the number of files; files about 100 to 250 MB compressed; dedicated warehouse; Snowpipe is serverless.
  • BI and reporting: multi-cluster auto-scale for concurrency; longer Auto-suspend to keep cache; result cache for repeats.
  • Scale up for complex queries, scale out for many concurrent users.
  • Gen 2 standard warehouses speed up DML-heavy pipelines such as MERGE.
Watch out

Multi-cluster warehouses require Enterprise Edition. On Standard Edition, BI concurrency is usually handled by splitting users across separate warehouses.

Check yourself

  • Why does a larger warehouse not speed up loading a single large file?

    Each file is loaded by one thread, so extra compute sits idle; split the data into many files of about 100 to 250 MB instead.

  • What configuration fits hundreds of users running simple dashboard queries at peak times?

    A multi-cluster warehouse in auto-scale mode, which adds clusters as queries queue.

  • Why give a BI warehouse a longer Auto-suspend than an ad-hoc warehouse?

    To keep its local data cache warm between refreshes and avoid repeated resume delays.

Topic 8 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.