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
- 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.
How it works
Workloads and the compute that suits them
| Workload | Characteristics | Compute choice |
|---|---|---|
| BI and dashboards | Many short, concurrent queries | Multi-cluster warehouse (Enterprise Edition or higher), modest size |
| Batch ELT | Heavy, scheduled, predictable | Larger warehouse that suspends right after the run |
| Data loading | Many files, parallel | Dedicated warehouse; serverless for Snowpipe |
| Data science and ML | Memory hungry, long running | Snowpark-optimized warehouse, or Snowpark Container Services |
| Ad hoc exploration | Unpredictable | Small warehouse, resource monitor, auto suspend |
| Applications | Row-level, low latency lookups | Hybrid 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.
| Problem | Fix |
|---|---|
| One query is slow | Scale up: a larger warehouse size |
| Many users queue | Scale out: multi-cluster warehouse with MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT |
| Occasional large outliers on a small warehouse | Query Acceleration Service, which offloads part of eligible queries to serverless compute |
| Idle credits wasted | AUTO_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.
"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
| Aspect | ETL | ELT |
|---|---|---|
| Order | Transform, then load | Load raw, then transform in Snowflake |
| Where transformation runs | External engine or server | Snowflake warehouse |
| Raw data kept | Often not | Yes, so you can replay and rebuild |
| Scaling | Limited by the ETL server | Scales with warehouse size |
| Typical tools | Traditional integration servers | SQL, 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.
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_SUSPENDcontrol 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.