Snowflake Explained: The Cloud Data Warehouse

Snowflake is a cloud data warehouse sold as a service: you load data and run SQL, and the platform handles servers, storage, tuning and upgrades. It runs on AWS, Azure and Google Cloud. What made it influential was an architecture that separates storage from compute, so the two scale, and are paid for, independently. Understanding that architecture explains both how to use Snowflake well and where the bills come from.

Three layers

Snowflake describes its architecture as a hybrid of shared-disk and shared-nothing designs, in three layers (Snowflake docs: key concepts):

• Database storage. Loaded data is reorganised into Snowflake's own compressed, columnar format in cloud object storage. You do not manage files; you see tables.
• Compute. Queries run in virtual warehouses, clusters of compute. Each virtual warehouse is independent and shares no compute with the others, though all read the same storage.
• Cloud services. A coordination layer handles authentication, access control, metadata, query parsing and optimisation.

The practical consequence is workload isolation. The nightly load, the BI dashboards and a data scientist's heavy query can each have their own warehouse, so one never slows another, while all see the same data.

Virtual warehouses and credits

A warehouse has a size, from X-Small to 6X-Large, and each step up doubles the compute and the cost: an X-Small uses 1 credit per hour, an X-Large 16. Billing is per second, with a 60-second minimum each time a warehouse starts, and warehouses can suspend automatically when idle and resume when a query arrives (Snowflake docs: warehouses).

CREATE WAREHOUSE reporting_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60          -- seconds of inactivity before suspending
  AUTO_RESUME = TRUE;

A worked example makes the model concrete. A report runs for 5 minutes on that X-Small warehouse, and the warehouse suspends after 60 idle seconds: 6 minutes of running time, one tenth of an hour, 0.1 credit. Run the same report on a Large warehouse, 8 credits an hour, and if it finishes in 1 minute plus the 60-second idle period, the cost is 2 minutes at 8 credits an hour, about 0.27 credits. Bigger is not always dearer per query, and not always faster in proportion; the only way to know is to measure. The price of a credit depends on edition, cloud and region, so check your own contract.

For many simultaneous users, a multi-cluster warehouse adds clusters of the same size to handle concurrency, instead of making one cluster bigger.

Micro-partitions and pruning

Snowflake has no indexes to create and no partitions to declare. Tables are automatically divided into micro-partitions, each holding 50 MB to 500 MB of uncompressed data, stored column by column, with metadata recording the range of values in every column (Snowflake docs: micro-partitions). When a query filters on a column, Snowflake uses those ranges to skip micro-partitions that cannot match, known as pruning.

Pruning works best when data is naturally ordered by the columns you filter on. Data loaded in date order prunes well on date filters. A table whose rows arrive in random order with respect to a common filter prunes badly, and every query scans most of it; for very large tables, defining a clustering key tells Snowflake to keep the data organised by those columns.

Time Travel and cloning

Snowflake keeps previous versions of data for a retention period, by default one day and up to 90 days on Enterprise Edition, and lets you query, clone and restore from them (Snowflake docs: Time Travel):

-- the table as it was five minutes ago
SELECT * FROM orders AT(OFFSET => -60*5);

-- a copy of the table as it was before a bad statement ran
CREATE TABLE orders_restored CLONE orders
  BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

-- recover a table dropped by mistake
UNDROP TABLE orders;

These features change everyday practice. A wrong UPDATE is repaired from history instead of from backups, and a clone gives a developer or a test run a logical copy of production data to work on, without touching the original. Longer retention stores more historical data, so it costs more.

Habits that keep Snowflake fast and affordable

• Set auto-suspend on every warehouse, short for interactive ones. An idle warehouse left running is the classic waste.
• Size by measurement. Start small and increase only if queries are too slow; watch queueing before adding clusters.
• Separate warehouses by workload, so costs are attributable and loads do not block dashboards.
• Select only the columns you need and filter on well-clustered columns, so pruning can work.
• Load in batches of reasonable size instead of row by row, and use staged files for bulk loads.
• Monitor credits by warehouse and by query, and set resource monitors or budgets to cap surprises.

Loading data

The usual pattern is ELT. Files land in a stage, a named location in cloud storage; a COPY command bulk-loads them into raw tables; and SQL transformations build cleaned and modelled tables inside Snowflake, often managed with dbt. Because loading and transformation run in warehouses you choose, a small warehouse can trickle in data all day while a larger one runs the nightly transformations.

Where Snowflake fits

Snowflake is one of several cloud warehouses with separate storage and compute; BigQuery vs Snowflake vs Redshift compares the three, and BigQuery explained covers Google's serverless model. Whichever platform you use, the modelling principles in data warehouses explained and the tuning habits in SQL optimisation for data engineers carry over.

Cloud data warehousing with Amazon Redshift, BigQuery and Azure Synapse Analytics, and data warehouse concepts, are part of the Data Engineering course.

Sources

• Key concepts and architecture (Snowflake documentation) — Snowflake
• Overview of warehouses (Snowflake documentation) — Snowflake
• Micro-partitions and data clustering (Snowflake documentation) — Snowflake
• Understanding and using Time Travel (Snowflake documentation) — Snowflake