Snowflake Bills Storage and Compute Apart, and That Is the Whole Design

How separating storage from compute changes what a warehouse costs and how it scales, with the Python and SQL to drive it. As of spring 2025.
Data Engineering
Author

Ravi Kalia

Published

April 2, 2025

Snowflake

In a traditional data warehouse the disks and the CPUs come in one box, so a team that needs to keep ten years of history pays for the compute to match, and a team that needs a fast query on Monday morning pays for it all week. Snowflake’s one structural idea is to keep the two apart: data lives in cloud object storage, priced by the terabyte, and queries run on “virtual warehouses”, clusters that start, resize and stop on demand and are billed by the second. Almost every feature on its list follows from that split. This post traces the consequences and shows the Python and SQL needed to use it, written as of spring 2025.

Everything on the feature list is the split, seen from a different side

Once storage and compute are separate services, the rest is arithmetic:

  • Independent scaling. Storage grows with the data; compute grows with the load, and only while the load is there. A warehouse that is suspended costs nothing.
  • Isolation. Two teams run two warehouses on the same tables and never slow each other down, because they are not sharing a box.
  • Zero-copy cloning. A clone of a database is a new set of pointers to the same storage, so a test copy of a ten-terabyte table is instant and costs nothing until it diverges.
  • Time travel. Storage is immutable and versioned, so a query can read a table as it was up to ninety days ago, and a dropped table is recoverable.
  • Semi-structured data. JSON, Avro and Parquet are stored as-is in a VARIANT column and queried with a path syntax, because the storage layer does not care what shape the bytes have.

The operational features (no indexes to build, automatic clustering, result caching, multi-cloud deployment on AWS, Azure and Google) are the managed service doing the tuning a DBA used to do; they are real but not distinctive.

Driving it from Python is a cursor and SQL

The connector is a DB-API cursor, so anyone who has used sqlite3 or psycopg2 knows the shape. Connect, then run SQL:

import snowflake.connector

conn = snowflake.connector.connect(user="...", password="...", account="xyz123.us-east-1")
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS demo")
cur.execute("USE DATABASE demo")
cur.execute("CREATE SCHEMA IF NOT EXISTS analytics")
cur.execute("USE SCHEMA analytics")
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTOINCREMENT PRIMARY KEY,
        name STRING,
        age INT
    )
""")
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)")
conn.commit()
cur.execute("SELECT * FROM users")
print(cur.fetchall())        # [(1, 'Alice', 30), (2, 'Bob', 25)]

Two rows are a demonstration of the API, not of Snowflake; the two-row table is synthetic and stands in for a fact table of billions. A result lands in pandas through the cursor’s description:

import pandas as pd

cur.execute("SELECT * FROM users")
df = pd.DataFrame(cur.fetchall(), columns=[c[0] for c in cur.description])

Loading real volumes goes through a stage, a named location in storage: files are uploaded to it and then copied into a table in bulk, which is orders of magnitude faster than row inserts:

cur.execute("CREATE OR REPLACE STAGE my_stage")
cur.execute("PUT file://users.csv @my_stage AUTO_COMPRESS=TRUE")
cur.execute("COPY INTO users FROM @my_stage FILE_FORMAT=(TYPE=CSV)")

The stage is the split again: the data goes to storage first, and the compute that parses it is a warehouse you size for the job and suspend afterwards.

What it costs, and when the split does not pay

The billing model is the design’s honest face. Storage is cheap and flat; compute is metered per second per warehouse size, so cost tracks how much querying happens rather than how much data exists. That is a bargain for a bursty analytics team and a surprise for a workload that runs continuously, where a permanently running warehouse costs more than a box would. Small data is also a poor fit: for a few gigabytes that one machine handles, the split buys nothing and the per-query latency of a cloud warehouse is the price. And the more of a team’s logic lives in Snowflake SQL, stages and clones, the harder the move to anything else, which is true of every managed platform and worth saying anyway.

Compared with the other shape of platform, Spark-based ones such as Databricks, the difference is what the compute is for: a warehouse is optimised for SQL over tables, and a Spark cluster for code over anything. Teams whose work is SQL-shaped pick the warehouse.

Storage. Splits. From. Compute. Scaling. Becomes. Billing. Warehouses. Stop. Blocking. Each. Other.

References