Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 38 ยท Architecture

Cloud Services and Metadata

Explore the Snowflake cloud services layer: authentication, metadata, the query optimizer, transactions and ACID, free metadata queries and cloud services billing.

The cloud services layer is the part of Snowflake you never launch, resize or pause, yet it does a great deal of work. It is the reason SELECT COUNT(*) FROM huge_table returns instantly and the reason two sessions can update the same table safely. This lesson opens that box.

What cloud services does

  • Authentication and access control: logins, MFA, SSO, roles and privileges.
  • Metadata management: names, columns, micro-partition statistics, clustering information, query history.
  • Query parsing and optimization: turning SQL into an efficient plan.
  • Transaction management: ACID guarantees across concurrent sessions.
  • Infrastructure management: provisioning warehouses, replicating data, handling failures.
  • Result caching and sharing coordination.

Because this layer is shared and always on, some operations run there and need no warehouse at all.

Metadata-only operations

Snowflake keeps exact row counts, min and max per column and size for each micro-partition. So some queries can be answered entirely from metadata, without scanning data and without a running warehouse:

SQL
-- Snowflake SQL (read-only)
SELECT COUNT(*)           FROM shop.raw.orders;          -- from metadata
SELECT MIN(order_date), MAX(order_date) FROM shop.raw.orders;   -- from metadata
SHOW TABLES;                                             -- metadata
DESCRIBE TABLE shop.raw.orders;                          -- metadata

If you see 0 bytes scanned in the query profile and a result in milliseconds, this is why. Not every aggregate qualifies: SUM and AVG need real data and use the warehouse. Filters also usually require scanning.

You can imitate the idea. The stand-in keeps a tiny metadata table with row counts and min/max next to the data and answers questions from it instead of scanning:

SQL Stand-in: answer from metadata, not data ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The optimizer

When you submit SQL, the optimizer:

  1. parses and checks it against metadata and your privileges,
  2. rewrites it (pushes filters down, removes unneeded columns and joins),
  3. estimates costs using partition statistics,
  4. chooses join order and methods and decides which partitions to prune,
  5. produces a plan that a warehouse executes in parallel.

You can inspect plans with EXPLAIN:

SQL
-- Snowflake SQL (read-only)
EXPLAIN
SELECT c.region, SUM(o.total)
FROM shop.raw.orders o JOIN shop.raw.customers c ON c.id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.region;

The output lists operations (TableScan, Join, Aggregate) with partition counts (partitionsAssigned and partitionsTotal). For richer detail use the Query Profile in Snowsight, which draws the operator tree with time per operator.

Snowflake's EXPLAIN differs from other databases in shape, but SQLite has a version you can run, showing the same idea of a plan:

SQL Stand-in: reading a query plan ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Transactions and concurrency

Snowflake provides ACID transactions. The default is autocommit: each statement is its own transaction. You can group statements explicitly:

SQL
-- Snowflake SQL (read-only)
BEGIN;
UPDATE shop.raw.accounts SET balance = balance - 100 WHERE id = 1;
UPDATE shop.raw.accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Isolation level is READ COMMITTED: a statement sees data committed before it started. Because micro-partitions are immutable, readers never block writers. An update creates new partitions and the swap is a metadata change at commit. Two writers modifying the same table at the same time serialize: the second waits for the lock on that table (a statement timeout applies), which is why very frequent single-row updates are a poor fit.

Try the transaction logic with a runnable model, including a rollback that undoes the change:

SQL Stand-in: a transaction with rollback

Press Run (or Ctrl + Enter) to see the result here.

Query history and the information schema

All queries are recorded. In Snowsight you see them under Query History. In SQL:

SQL
-- Snowflake SQL (read-only)
SELECT query_id, query_text, warehouse_name, total_elapsed_time, bytes_scanned
FROM TABLE(information_schema.query_history(RESULT_LIMIT => 20))
ORDER BY start_time DESC;

For longer history (up to 365 days) and account-wide views use the SNOWFLAKE.ACCOUNT_USAGE schema; it lags real time by up to a few hours but contains everything: queries, logins, storage, credits, copy history. Access needs a role granted the SNOWFLAKE database imported privileges.

Cloud services billing

Cloud services consumption is billed in credits too, but with an allowance: daily cloud services usage up to 10 percent of your daily warehouse credits is free. For most accounts, cloud services cost nothing extra. Heavy use of very many tiny queries, thousands of SHOW commands or huge numbers of metadata operations can exceed it. You can see it in ACCOUNT_USAGE.METERING_HISTORY.

Operations that run in cloud services, such as SHOW and DESCRIBE, count as cloud services credits, not warehouse credits. A script polling SHOW TABLES a thousand times a minute is a classic way to get a surprise charge.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which query can usually be answered from metadata without a running warehouse?

    Row counts and min/max per column are kept in metadata. Averages and filtered scans need the data.
  2. 2. What isolation level does Snowflake use for transactions?

    Statements see data committed before they began.
  3. 3. Why do readers not block writers in Snowflake?

    Immutability means a reader keeps using the partitions that were current when it started.
  4. 4. Where can you find up to a year of query history across the account?

    ACCOUNT_USAGE keeps long history with some latency. INFORMATION_SCHEMA functions give recent, real-time results.

Summary

  • Cloud services handles security, metadata, optimization, transactions and infrastructure.
  • Row counts and min/max are in metadata, so some queries return instantly with no warehouse.
  • The optimizer uses partition statistics to build plans and prune partitions; use EXPLAIN and the Query Profile.
  • Transactions are ACID with READ COMMITTED isolation and autocommit by default.
  • ACCOUNT_USAGE holds long-term history; cloud services usage is free up to 10 percent of daily compute credits.

Next: Caching Layers