Which Snowflake operations complete without a running virtual warehouse, and why?

middleshould knowfreq 50%

answer

not every statement needs compute at all
the catalog and statistics live above the clusters
two identical runs need not both do work
some aggregates are already recorded per file
adding a WHERE clause changes everything

basics

~20 s

DDL, SHOW and DESCRIBE commands, queries served from the account-wide result cache, and simple aggregates answerable from file metadata such as COUNT(*) or MIN/MAX all run in Snowflake's cloud services layer, which holds the catalog, statistics and cached results, so no compute cluster has to start.

solid answer

~50 s

Snowflake's cloud services layer owns the catalog, per-file column statistics, the query result cache, and the compiler — none of which live on a virtual warehouse. So anything answerable from that layer alone needs no compute: DDL (CREATE, ALTER, DROP, GRANT), SHOW / DESCRIBE, a result-cache hit where the same query text runs against unchanged data with the same role and settings, and metadata-answerable aggregates like SELECT COUNT(*) FROM t or SELECT MAX(order_date) FROM t, which Snowflake reads from the statistics it keeps per stored file. The moment a statement needs actual column values — a WHERE filter, a join, an expression over rows — a warehouse must be running or auto-resume. This matters in practice: cheap monitoring and freshness checks can be written to stay metadata-only, and a suspended warehouse is not a reason a SHOW TABLES fails.

Snowflake's cloud services layer owns the catalog, per-file column statistics, the query result cache, and the compiler — none of which live on a virtual warehouse. So anything answerable from that layer alone needs no compute: **DDL** (`CREATE`, `ALTER`, `DROP`, `GRANT`), **`SHOW` / `DESCRIBE`**, a **result-cache hit** where the same query text runs against unchanged data with the same role and settings, and **metadata-answerable aggregates** like `SELECT COUNT(*) FROM t` or `SELECT MAX(order_date) FROM t`, which Snowflake reads from the statistics it keeps per stored file. The moment a statement needs actual column values — a `WHERE` filter, a join, an expression over rows — a warehouse must be running or auto-resume. This matters in practice: cheap monitoring and freshness checks can be written to stay metadata-only, and a suspended warehouse is not a reason a `SHOW TABLES` fails.

code

sql · 8 lines

sql

-- No warehouse required: served from catalog / file statistics
SHOW TABLES IN SCHEMA analytics.public;
SELECT COUNT(*) FROM analytics.public.sales;
SELECT MAX(order_ts) FROM analytics.public.sales;
CREATE TABLE sales_clone CLONE analytics.public.sales;

-- Requires a running (or auto-resuming) warehouse
SELECT COUNT(*) FROM analytics.public.sales WHERE region = 'EMEA';

go deeper

for a junior

Remember that catalog commands and simple whole-table counts can return with no compute running, and that a filtered query cannot. Do not be surprised when a query succeeds against a suspended warehouse.

for a middle

Explain the mechanism: catalog, per-file statistics and the result cache all live in cloud services, so anything answerable there skips compute entirely. Be ready to say precisely where the boundary falls.

for a senior

Use it operationally — write freshness and volume checks so they stay metadata-only, and recognize a result-cache hit as the reason a supposedly heavy query returned instantly with zero credits during a benchmark.

for a principal

Own the cost consequence across an estate: constantly-polling monitoring written the wrong way resumes warehouses around the clock. Set the convention, and know that the cloud-services allowance scales with warehouse spend, not independently.

Where the answer comes from

Snowflake's three layers split responsibility so cleanly that some work never touches compute. The cloud services layer holds the catalog (databases, schemas, tables, columns, grants), the per-file column metadata Snowflake maintains for every stored file (row counts, min/max values, distinct-value information), the query result cache, and the SQL compiler itself. If a statement can be satisfied out of those structures, there is nothing for a virtual warehouse to do, and Snowflake will not start one.

What runs with no warehouse

DDL and access control. CREATE TABLE, ALTER TABLE, DROP, CREATE VIEW, GRANT / REVOKE, CREATE WAREHOUSE itself — all of these edit catalog state. Note the important corollary: CREATE TABLE ... CLONE is also metadata-only, which is why cloning a huge table is instant.

SHOW and DESCRIBE. SHOW TABLES, SHOW WAREHOUSES, DESC TABLE t read the catalog directly.

Result-cache hits. The result cache lives in cloud services, not in a warehouse, so a repeat of an identical query can be served with every warehouse suspended. The conditions are strict: the query text must match, the underlying data must not have changed, the role must have the same privileges on the referenced objects, and the query must not use non-deterministic functions or certain runtime-dependent constructs.

Metadata-answerable aggregates. SELECT COUNT(*) FROM sales and SELECT MIN(order_ts), MAX(order_ts) FROM sales can be assembled by summing and combining the statistics Snowflake already keeps per stored file. No file is opened. In the Query Profile these show up as a metadata-based result rather than a table scan.

What always needs a warehouse

Anything requiring the actual column values of individual rows:

sql

-- metadata only: no warehouse needed
SELECT COUNT(*) FROM sales;

-- needs compute: the predicate must be evaluated per row
SELECT COUNT(*) FROM sales WHERE region = 'EMEA';

The second query cannot be answered from range statistics because the count of matching rows inside a file is not recorded. Likewise COUNT(DISTINCT ...), joins, GROUP BY, expressions, sorting of real rows, COPY INTO loads and unloads, and anything reading the result of a UDF all require a running (or auto-resuming) warehouse.

A subtlety worth knowing: SELECT COUNT(col) (non-*) counts non-NULL values, which Snowflake can often still satisfy from per-file NULL counts, but do not assert that in an interview as a guarantee — the safe framing is "simple aggregates over whole tables can be metadata-served; predicates cannot."

Why interviewers ask this

Three reasons, all practical.

It tests whether you actually understand the layer split. If you believe the optimizer and metadata live on the warehouse, you cannot explain how any of this works.
Cost. Monitoring queries, freshness checks and dashboard heartbeat queries run constantly. Written as SELECT MAX(loaded_at) FROM t they are free; written as SELECT MAX(loaded_at) FROM t WHERE source = 'crm' they resume a warehouse every minute and burn credits around the clock.
Operational confusion. Teams see a suspended warehouse and assume nothing works; then they see a mysterious query that returned instantly with zero credits and assume a bug. Both are explained by the same fact.

Billing note

Cloud services usage is metered in credits, but Snowflake charges only the part of daily cloud-services credits that exceeds 10% of that day's warehouse credit consumption. For a normal workload that means metadata-only work is effectively free — but an account that runs almost no warehouse compute while hammering metadata operations can see a cloud-services line item, because the 10% allowance is proportional to warehouse usage that is not happening.

Practical takeaway

When you want a cheap freshness or volume check, write it so it stays inside metadata: no WHERE, no join, no expression. When you want to be sure a query is really exercising compute (a benchmark, for example), remember that a result-cache hit will silently short-circuit it — vary the query text or disable result reuse for the session.

## Where the answer comes from Snowflake's three layers split responsibility so cleanly that some work never touches compute. The **cloud services** layer holds the catalog (databases, schemas, tables, columns, grants), the per-file column metadata Snowflake maintains for every stored file (row counts, min/max values, distinct-value information), the **query result cache**, and the SQL compiler itself. If a statement can be satisfied out of those structures, there is nothing for a virtual warehouse to do, and Snowflake will not start one. ## What runs with no warehouse **DDL and access control.** `CREATE TABLE`, `ALTER TABLE`, `DROP`, `CREATE VIEW`, `GRANT` / `REVOKE`, `CREATE WAREHOUSE` itself — all of these edit catalog state. Note the important corollary: `CREATE TABLE ... CLONE` is also metadata-only, which is why cloning a huge table is instant. **`SHOW` and `DESCRIBE`.** `SHOW TABLES`, `SHOW WAREHOUSES`, `DESC TABLE t` read the catalog directly. **Result-cache hits.** The result cache lives in cloud services, not in a warehouse, so a repeat of an identical query can be served with every warehouse suspended. The conditions are strict: the query text must match, the underlying data must not have changed, the role must have the same privileges on the referenced objects, and the query must not use non-deterministic functions or certain runtime-dependent constructs. **Metadata-answerable aggregates.** `SELECT COUNT(*) FROM sales` and `SELECT MIN(order_ts), MAX(order_ts) FROM sales` can be assembled by summing and combining the statistics Snowflake already keeps per stored file. No file is opened. In the Query Profile these show up as a metadata-based result rather than a table scan. ## What always needs a warehouse Anything requiring the actual column values of individual rows: ```sql -- metadata only: no warehouse needed SELECT COUNT(*) FROM sales; -- needs compute: the predicate must be evaluated per row SELECT COUNT(*) FROM sales WHERE region = 'EMEA'; ``` The second query cannot be answered from range statistics because the count of matching rows inside a file is not recorded. Likewise `COUNT(DISTINCT ...)`, joins, `GROUP BY`, expressions, sorting of real rows, `COPY INTO` loads and unloads, and anything reading the result of a UDF all require a running (or auto-resuming) warehouse. A subtlety worth knowing: `SELECT COUNT(col)` (non-`*`) counts non-NULL values, which Snowflake can often still satisfy from per-file NULL counts, but do not assert that in an interview as a guarantee — the safe framing is "simple aggregates over whole tables can be metadata-served; predicates cannot." ## Why interviewers ask this Three reasons, all practical. 1. **It tests whether you actually understand the layer split.** If you believe the optimizer and metadata live on the warehouse, you cannot explain how any of this works. 2. **Cost.** Monitoring queries, freshness checks and dashboard heartbeat queries run constantly. Written as `SELECT MAX(loaded_at) FROM t` they are free; written as `SELECT MAX(loaded_at) FROM t WHERE source = 'crm'` they resume a warehouse every minute and burn credits around the clock. 3. **Operational confusion.** Teams see a suspended warehouse and assume nothing works; then they see a mysterious query that returned instantly with zero credits and assume a bug. Both are explained by the same fact. ## Billing note Cloud services usage is metered in credits, but Snowflake charges only the part of daily cloud-services credits that exceeds 10% of that day's warehouse credit consumption. For a normal workload that means metadata-only work is effectively free — but an account that runs almost no warehouse compute while hammering metadata operations can see a cloud-services line item, because the 10% allowance is proportional to warehouse usage that is not happening. ## Practical takeaway When you want a cheap freshness or volume check, write it so it stays inside metadata: no `WHERE`, no join, no expression. When you want to be sure a query is really exercising compute (a benchmark, for example), remember that a result-cache hit will silently short-circuit it — vary the query text or disable result reuse for the session.

Why does adding a WHERE clause to SELECT COUNT(*) force a warehouse to start?
Because the per-file statistics record value ranges, not how many rows inside a file satisfy an arbitrary predicate. Ranges can rule a whole file out, but any file that might contain matches must be opened and its rows evaluated — and only a warehouse can do that.
How would you make sure a benchmark query is really executing rather than hitting the result cache?
Either change the query text so it is not a cache hit, or turn off result reuse for the session before timing it. Otherwise a repeat run returns in milliseconds with no compute and you measure the cache, not the engine. Also remember the cache is invalidated when the underlying data changes.
Does a metadata-only query cost anything?
It consumes cloud-services credits, but Snowflake bills only the daily cloud-services credits above 10% of that day's warehouse credits, so alongside normal compute it is effectively free. An account with almost no warehouse usage but heavy metadata traffic can see a real charge, because the allowance scales with warehouse consumption.

saying these in an interview costs you the question

Believes every SQL statement requires a running warehouse
Thinks the result cache lives inside the virtual warehouse
Claims COUNT(*) with any WHERE clause is still metadata-only
Says SHOW TABLES fails when all warehouses are suspended
Assumes a cloned table is physically copied by compute

Which Snowflake operations complete without a running virtual warehouse, and why?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Where the answer comes from

What runs with no warehouse

What always needs a warehouse

Why interviewers ask this

Billing note

Practical takeaway

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics3 topics

Where the answer comes from

What runs with no warehouse

What always needs a warehouse

Why interviewers ask this

Billing note

Practical takeaway

answer

go deeper

context