In this post we are going to cover Snowflake architecture and virtual warehouses: what makes Snowflake different from a traditional database, the three layers, how warehouses keep workloads apart, when sizing up helps, how auto-suspend and billing work, and what changes between the editions. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where you set up warehouses for loading, transformation, BI dashboards and data science.
Snowflake is a cloud data platform where the data sits in one place and the compute that reads it comes and goes. Interviewers start here because everything else depends on it. If you don't understand storage, compute and warehouses, you can't explain why a query is slow, why two teams fight, or why the bill keeps running at night.
Part 1 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Question 1: Your manager asks what makes Snowflake different from a traditional database like SQL Server or Oracle. What do you say?
- Question 2: A new engineer asks what the storage, compute and cloud services layers do. How do you explain the three layers?
- Question 3: Two teams run heavy queries at the same time and complain about each other. How do virtual warehouses keep workloads apart?
- Question 4: A query runs slowly on an X-Small warehouse. When do you size up the warehouse, and when does that not help?
- Question 5: Finance asks why the warehouse bill keeps running at night. How do auto-suspend, auto-resume and per-second billing work?
- Question 6: The company is choosing a Snowflake edition. What changes between Standard, Enterprise and Business Critical?
- Scenario 7: You are asked to set up warehouses for loading, transformation, BI dashboards and data science. What do you propose?
The short answer
Snowflake separates storage from compute: one copy of the data in central storage, read by independent virtual warehouses, with cloud services coordinating on top. Size a warehouse up for big queries, not for queuing, set auto-suspend by workload, and pick the edition by the features you need.
Keep that in mind. Every question below is one piece of it.
Question 1: Your manager asks what makes Snowflake different from a traditional database like SQL Server or Oracle. What do you say?
The short answer: Snowflake separates storage from compute.
Let's start with the traditional database. In SQL Server or Oracle, the data and the engine live on the same server. So one heavy job slows everyone down, and when you need to grow, you buy a bigger server.
Now Snowflake. The data sits once in central storage, in cloud object storage. Compute comes from virtual warehouses: independent clusters that you start, stop and resize. Every warehouse reads the same data, so a load warehouse, a transform warehouse and a BI warehouse each get their own cluster without copying anything.
Snowflake calls this a hybrid of shared disk and shared nothing. Shared disk, because every warehouse reads the same central storage. Shared nothing, because each warehouse is a massively parallel cluster that keeps part of the data locally.
On top sits the cloud services layer. It runs security, metadata, query optimization and infrastructure for you.
And billing follows that design. Compute is billed per second while a warehouse runs, with a 60-second minimum each time it starts.
Don't just say "it's in the cloud". Say storage and compute scale separately, so loading and reporting never fight over one server.
Question 2: A new engineer asks what the storage, compute and cloud services layers do. How do you explain the three layers?
I draw three layers. Let's go from the bottom up.
First, database storage. When you load data, Snowflake reorganizes it into its own compressed, columnar format in cloud object storage, split into micro-partitions. You can't touch those files directly. You reach them through SQL.
Then, in the middle, compute. These are the virtual warehouses: clusters of compute resources that run your queries and loads. The docs used to call this layer query processing, and now they call it compute. Both names are fine in an interview.
Then, on top, cloud services. This is the brain: authentication and access control, metadata, query parsing and optimization, the Horizon Catalog, and infrastructure management. The result cache lives here too. Results are kept for 24 hours, and the clock is renewed each time a result is reused.
Billing follows the layers:
| Layer | Billed for |
|---|---|
| STORAGE | data you keep |
| COMPUTE | credits while running |
| CLOUD SERVICES | only above 10% of daily warehouse use |
So cloud services are charged only when their daily use is more than 10 percent of your daily warehouse use.
Snowflake keeps statistics on every micro-partition, so it skips partitions that can't match your filter, and some operations are metadata-only.
Question 3: Two teams run heavy queries at the same time and complain about each other. How do virtual warehouses keep workloads apart?
Give each team, or each workload, its own warehouse. Every warehouse is an independent compute cluster, so a heavy job on one warehouse doesn't slow down queries on another.
Say finance runs dashboards and data science runs heavy feature queries. Let's give them a warehouse each and grant usage to the right role:
CREATE WAREHOUSE finance_wh WITH WAREHOUSE_SIZE = 'SMALL'
AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE WAREHOUSE ds_wh WITH WAREHOUSE_SIZE = 'LARGE'
AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
GRANT USAGE ON WAREHOUSE finance_wh TO ROLE finance_role;
GRANT USAGE ON WAREHOUSE ds_wh TO ROLE ds_role;Notice what we did not do: we didn't copy any data. Both warehouses read the same tables in central storage, so finance and data science work from one version of the truth.
Now inside one warehouse, queries share resources. By default a cluster runs up to eight queries at once, and the rest wait in a queue. Queued queries use no credits, but users feel the wait.
If one team has many users at the same time, the next step is a multi-cluster warehouse. That is an Enterprise feature that adds clusters when queries queue, and we cover it in the next part.
Grant each team's role usage on its own warehouse only. You also get clean cost: each warehouse has its own line in the warehouse metering history.
Question 4: A query runs slowly on an X-Small warehouse. When do you size up the warehouse, and when does that not help?
Each size up doubles the compute and the credits per hour. On a standard Gen1 warehouse it looks like this (Gen2 has its own rate):
| Size | Credits per hour |
|---|---|
| X-SMALL | 1 |
| SMALL | 2 |
| MEDIUM | 4 |
| LARGE | 8 |
| X-LARGE | 16 |
Size up when one big query needs more power or memory. A bigger size brings more memory and more local disk, so it helps with:
- Large scans, big joins and sorts
- Spilling to local or remote storage, which you see in the Query Profile
It does not help with these:
- Queuing. That is concurrency, so use more clusters or another warehouse.
- Poor pruning, where the query reads far more data than it needs.
- An exploding join, where the join itself is wrong.
- An already small query. There is nothing left to speed up.
Two details interviewers like. Resizing a running warehouse doesn't speed up queries that are already running. Only new and queued queries get the new size. And shrinking a warehouse drops the cache on the removed nodes.
Test it, one size up. If the run time halves, the credits stay about the same, so take the speed. If it barely changes, fix the query, not the warehouse.
Question 5: Finance asks why the warehouse bill keeps running at night. How do auto-suspend, auto-resume and per-second billing work?
A warehouse only bills credits while it runs. Auto-suspend stops it after a set number of idle seconds, and auto-resume starts it again on the next query.
| Setting | What it does | Default in SQL |
|---|---|---|
| AUTO_SUSPEND | suspends after this many idle seconds | 600 seconds (10 minutes) |
| AUTO_RESUME | starts it again on the next query | TRUE |
| BILLING | per second while it runs | 60-second minimum per start or resume |
When you create a warehouse in SQL, both are on by default, and auto-suspend is 600 seconds, which is 10 minutes. Billing is per second, with a 60-second minimum each time it starts or resumes. So a two-second query on a sleeping warehouse pays for a full minute.
So why does it run all night? Usually one of three things:
- AUTO_SUSPEND is 0 or NULL, which means it never suspends.
- Something keeps waking it, like a scheduled job, a BI refresh or monitoring.
- A long job really is running.
To find out, let's look at the hourly credits in the metering history for the night hours:
SELECT warehouse_name, start_time, credits_used
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE HOUR(start_time) BETWEEN 0 AND 5
ORDER BY credits_used DESC;Then set auto-suspend by workload. BI gets at least 10 minutes, because the warehouse cache is lost when it suspends:
| Workload | Auto-suspend |
|---|---|
| TASKS, LOADS | almost immediately |
| DATA SCIENCE | about 5 minutes |
| BI DASHBOARDS | 10 minutes or more |
Don't go too low on bursty work. Each resume bills at least a minute, so constant suspend and resume can cost more than staying up a little longer.
Question 6: The company is choosing a Snowflake edition. What changes between Standard, Enterprise and Business Critical?
Each edition includes everything in the one below it. Let's go up the ladder.
| Edition | Adds |
|---|---|
| STANDARD | Time Travel up to 1 day, automatic clustering, database and share replication |
| ENTERPRISE | Time Travel up to 90 days, multi-cluster warehouses, QAS, search optimization, materialized views, masking and row access policies, classification, access history, adaptive warehouses |
| BUSINESS CRITICAL | Tri-Secret Secure, private connectivity, HIPAA and PCI DSS, failover and Client Redirect |
| VPS | a completely separate Snowflake environment, no shared hardware |
Standard has the core platform: Time Travel up to one day, automatic clustering, and database and share replication.
Enterprise adds what most data teams need at scale. Time Travel goes up to 90 days, and you get multi-cluster warehouses, the query acceleration service, search optimization and materialized views. You also get governance: masking policies, row access policies, classification and access history. Adaptive warehouses also need Enterprise.
Business Critical is for regulated or sensitive data. It adds Tri-Secret Secure with a customer-managed key, private connectivity like AWS PrivateLink and Azure Private Link, support for HIPAA and PCI DSS data, and failover with Client Redirect. It used to be called Enterprise for Sensitive Data.
Above that is Virtual Private Snowflake, a completely separate Snowflake environment that shares no hardware with other accounts.
On a real project, Enterprise is the usual answer, because multi-cluster and masking come up early. Pick Business Critical when security asks for private connectivity, or the business needs failover.
Scenario 7: You are asked to set up warehouses for loading, transformation, BI dashboards and data science. What do you propose?
I propose one warehouse per workload: load, transform, BI and data science. That way each one is sized, suspended and costed on its own.
Let's go through them in order.
- LOAD_WH: Small, with auto-suspend at 60 seconds. Load parallelism is capped by the number of files, so a bigger size doesn't help one big file.
- TRANSFORM_WH: starts at Medium, suspends after 60 seconds, and is sized by testing the heaviest job.
- BI_WH: a multi-cluster warehouse on Enterprise, one to three clusters, standard policy, and 10 minutes of auto-suspend to keep the cache warm.
- DS_WH: a Snowpark-optimized warehouse for model training in Snowpark, with 16 times the memory per node.
Here are the BI and data science warehouses in SQL:
CREATE WAREHOUSE bi_wh WITH
WAREHOUSE_SIZE = 'SMALL'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 3
SCALING_POLICY = STANDARD
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE;
CREATE WAREHOUSE ds_wh WITH
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
WAREHOUSE_SIZE = 'MEDIUM';Then the guardrails:
- Resource monitor on each warehouse.
- Statement timeout, so no query runs for days.
- USAGE grants only to the right roles.
- Few admins who can create and resize warehouses.
Also mention adaptive warehouses, a newer option where Snowflake sizes the compute for you. They are generally available on some clouds and regions, on Enterprise and higher.
These sizes are a starting point. Test, track the metering history for two weeks, then resize.
Key points to remember
- Storage and compute are separate; cloud services coordinate on top
- Each warehouse is an independent cluster reading the same data
- Size up for big queries and spilling, not for queuing
- Auto-suspend by workload; per-second billing, 60-second minimum
- Enterprise for multi-cluster and masking; Business Critical for private links
Part 2: Scaling, Concurrency and Cost Control
Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.