Snowflake Architecture · Topic 7 of 18

Data Workloads, Data Warehouse Design and ELT versus ETL

Match Snowflake workloads to compute: separate warehouses, multi-cluster, Snowpark-optimized and query acceleration, and choose ELT over ETL for pipelines.

  • 4 min
In a nutshell
  • One warehouse per workload for isolation and cost visibility; storage is shared.
  • Scale up for slow single queries, scale out (multi-cluster) for concurrency; multi-cluster needs Enterprise Edition or higher.
  • Snowpark-optimized warehouses suit memory heavy ML work; Query Acceleration Service helps outlier queries.

What it is

A workload is a type of work Snowflake runs: dashboards, batch transformations, data science, data loading, or application queries. Each has a different shape, so an architect gives each its own compute instead of one big shared warehouse.

The exam links a workload description to a compute and pipeline design. It also asks whether transformation should happen before loading (ETL) or after (ELT), and Snowflake's answer is usually ELT.

WorkloadsBI, ETL, data science, appsmapped toWarehousesone per workloadsized and scaled byRulessize, clusters, suspendcapped byResource monitors

How it works

Workloads and the compute that suits them

WorkloadCharacteristicsCompute choice
BI and dashboardsMany short, concurrent queriesMulti-cluster warehouse (Enterprise Edition or higher), modest size
Batch ELTHeavy, scheduled, predictableLarger warehouse that suspends right after the run
Data loadingMany files, parallelDedicated warehouse; serverless for Snowpipe
Data science and MLMemory hungry, long runningSnowpark-optimized warehouse, or Snowpark Container Services
Ad hoc explorationUnpredictableSmall warehouse, resource monitor, auto suspend
ApplicationsRow-level, low latency lookupsHybrid tables, or a warehouse sized for point queries

Isolation is the main reason to split warehouses: a heavy transformation does not slow down dashboards because each warehouse has its own compute. Storage is shared, so there is only one copy of the data.

Scaling decisions

Two different problems have two different answers.

ProblemFix
One query is slowScale up: a larger warehouse size
Many users queueScale out: multi-cluster warehouse with MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT
Occasional large outliers on a small warehouseQuery Acceleration Service, which offloads part of eligible queries to serverless compute
Idle credits wastedAUTO_SUSPEND and AUTO_RESUME, plus resource monitors

A multi-cluster warehouse has a scaling policy: Standard starts clusters to avoid queuing, Economy keeps clusters fully loaded to save credits, which can mean some queuing. Remember that a larger size does not fix concurrency, and extra clusters do not speed up a single query.

Exam tip

"Queued queries at 9 am" means multi-cluster. "One report takes 40 minutes" means a larger size or query tuning, not more clusters.

Data warehouse workload

A classic warehouse workload is analytic: star schemas, scheduled loads, BI tools and aggregates. In Snowflake this uses zones (raw to consumption), a warehouse per consumer group, and clustering keys, search optimization or materialized views only where measured need exists. Result caching and the warehouse cache serve repeated queries.

Use separate warehouses by function (loading, transformation, BI, data science), and per team where charge back matters, so costs are visible by warehouse. Tag warehouses and use resource monitors to enforce budgets.

ELT versus ETL

AspectETLELT
OrderTransform, then loadLoad raw, then transform in Snowflake
Where transformation runsExternal engine or serverSnowflake warehouse
Raw data keptOften notYes, so you can replay and rebuild
ScalingLimited by the ETL serverScales with warehouse size
Typical toolsTraditional integration serversSQL, streams and tasks, dynamic tables, Snowpark, dbt

Snowflake favors ELT: load fast into raw tables, then transform with SQL or Snowpark inside the platform, using elastic compute. Raw data stays available, so logic can change without reloading from the source.

ETL still has a place when data must be masked or reduced before it may leave a source network, or when an existing tool does complex work Snowflake does not need to repeat. Tools such as Informatica or Matillion can also push work down to Snowflake and behave like ELT.

Sourcefiles, CDC, streamsloadRaw tablesVARIANT or typedstreams and tasksTransformed tablesSQL or Snowparkdynamic tableMartsfor BI

Building blocks for ELT inside Snowflake: streams track changes on a table, tasks run SQL on a schedule or when a stream has data, and dynamic tables declare a query and a target lag so Snowflake keeps the result fresh. Use dynamic tables for declarative pipelines, and streams with tasks when you need custom logic or procedural steps.

Common mistakes

One warehouse for everything. Loads, BI and data science compete. Separate them.

Scaling up to fix queuing. Concurrency needs more clusters, not a bigger size.

Leaving warehouses without auto suspend. Idle credits accumulate quickly on large sizes.

Defaulting to ETL. Moving transformation outside Snowflake adds a server to manage and discards raw history.

What to remember for the exam

  • One warehouse per workload for isolation and cost visibility; storage is shared.
  • Scale up for slow single queries, scale out (multi-cluster) for concurrency; multi-cluster needs Enterprise Edition or higher.
  • Snowpark-optimized warehouses suit memory heavy ML work; Query Acceleration Service helps outlier queries.
  • ELT: load raw first, transform inside Snowflake with SQL, streams and tasks, dynamic tables or Snowpark.
  • Resource monitors and AUTO_SUSPEND control spend.

Check yourself

  • Dashboards queue at peak times on a large warehouse. What helps most?

    A multi-cluster warehouse that adds clusters under concurrency, not a larger size.

  • Why does Snowflake favor ELT?

    Raw data loads quickly and transformation uses elastic Snowflake compute, while raw history stays available for rebuilds.

  • Which warehouse type suits memory intensive machine learning training?

    A Snowpark-optimized warehouse.

Topic 7 of 18 in Snowflake Architecture

Test yourself on Snowflake Architecture

The full Snowflake ARA-C01 question bank has 687 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.