Snowflake Architecture: How It Works and How to Design for It

The quick answer

Snowflake's architecture separates compute (virtual warehouses) from storage (Snowflake's managed columnar store) at the core layer. Every design decision in Snowflake — schema design, query patterns, data loading, pipeline architecture — is influenced by this separation and by Snowflake's specific storage format (micro-partitions). Understanding these mechanisms lets you design schemas and queries that work with the architecture rather than against it.

The most common Snowflake design mistakes: treating Snowflake like a traditional on-premise data warehouse (full table scans, normalised OLTP schemas, heavy use of UPDATE statements), not using clustering keys for large filtered tables, and not understanding how micro-partition pruning works. This guide covers the architectural fundamentals and the design principles that follow from them.

The multi-cluster shared data architecture

Snowflake separates three layers that traditional data warehouses couple together:

**Storage**: Snowflake stores all data in its own managed object storage (built on S3, Azure Blob, or GCS depending on the cloud platform), in Snowflake's proprietary compressed columnar format. Storage is shared — multiple virtual warehouses can access the same storage simultaneously without locking. Data is compressed automatically (typically 3–5× compression depending on data type). You pay per TB of compressed data stored.

**Compute** (virtual warehouses): compute clusters that execute queries against storage. Each virtual warehouse is an independent cluster of EC2/VM instances. Multiple warehouses can operate against the same storage simultaneously — a reporting warehouse and a transformation warehouse run concurrently without resource contention because they are independent compute clusters. Warehouses do not share memory; each warehouse has its own result and data cache. When a warehouse suspends, its cache is lost.

**Cloud services layer**: the services that coordinate queries, manage transactions, maintain metadata, and enforce security — authentication, query parsing, query optimization, transaction management, metadata management. The cloud services layer runs continuously and is shared across all warehouses.

The implication: you can run a 4X-Large warehouse for a one-hour transformation job and switch back to an X-Small warehouse for interactive analytics without copying data. Compute and storage scale independently.

Micro-partitions and pruning

Snowflake's storage format divides every table into micro-partitions — contiguous units of 50–500MB of uncompressed data, stored in columnar format. Micro-partitions are created automatically; you do not control their boundaries explicitly.

Each micro-partition stores metadata about its content: the MIN and MAX value of each column in that partition. When a query includes a filter predicate (WHERE Date >= '2026-01-01'), Snowflake's query planner reads the micro-partition metadata and skips any partition whose MAX Date value is less than 2026-01-01. This is **micro-partition pruning** — the primary mechanism that makes queries on large tables fast.

Pruning is effective when data is **naturally ordered** by the filter column. If a sales table was loaded chronologically, each micro-partition contains data from a narrow date range, and date-based filter queries prune the vast majority of partitions. If data was loaded in random order (interleaved dates, shuffled IDs), micro-partitions contain values from across the full range, and pruning is ineffective.

**Clustering keys** address the case where natural data ordering is not sufficient for your query patterns. A clustering key specifies one or more columns on which Snowflake automatically re-clusters the table to improve partition-level ordering for that column. If your most frequent filter is Region despite data being loaded chronologically (so Date is naturally ordered but Region is not), adding Region as a clustering key enables pruning on Region queries.

Clustering keys incur background maintenance credits as Snowflake re-clusters the table. Use clustering keys only for large tables (100GB+) where queries regularly filter on a column with poor natural clustering. The Snowflake SYSTEM$CLUSTERING_INFORMATION function returns the clustering depth for a table — low depth indicates good clustering (few overlapping partitions); high depth indicates poor clustering that would benefit from a clustering key.

Virtual warehouse sizing and design

Virtual warehouse sizing follows T-shirt sizes: X-Small (1 node), Small (2 nodes), Medium (4 nodes), up to 4X-Large (128 nodes). Each size doubles compute and credits consumed per hour.

**Single large query vs concurrent small queries**: larger warehouses execute a single query faster (more parallel compute nodes). For high-concurrency analytics (many small queries from many users simultaneously), multi-cluster warehouses scale out (add cluster instances) rather than up (add nodes per cluster). Multi-cluster handles concurrency; single-cluster handles large query scale.

**Cache effectiveness**: Snowflake has two local caches per warehouse: a metadata cache (query results cached at the cloud services level for identical queries) and a local disk cache (data read from Snowflake storage cached in the warehouse's local disk). The local disk cache is wiped when the warehouse suspends. For interactive analytics workloads where users run many queries against the same data, keeping the warehouse warm (extended auto-suspend) improves cache hit rates and reduces query latency for repeated access patterns.

**Separate warehouses per workload**: as covered in Snowflake pricing guide, separating dbt transformation jobs from interactive analytics from data loading allows independent sizing, independent auto-suspend policies, and independent cost governance per workload.

Schema design for Snowflake

### Dimensional modelling

Snowflake's columnar storage and query optimizer are well-suited to **dimensional models** (star schema fact and dimension tables). The optimizer joins star schemas efficiently; the columnar storage reads only the columns that queries reference; the MPP architecture handles large fact table scans efficiently.

For the dimensional modelling methodology, see Kimball vs Inmon.

### Denormalisation

In traditional normalised databases, denormalisation is avoided to reduce storage redundancy. In Snowflake, storage is cheap (compressed columnar is efficient) and join overhead is real. For frequently joined tables that are queried together in most analytical use cases, **pre-joining** (creating a denormalised table) reduces query complexity and improves performance.

This does not mean all schemas should be fully denormalised — large dimension tables with many columns that are rarely all queried simultaneously may be better kept separate. The decision is empirical: if a join is in the critical path of most queries and the joined result is large, materialise it as a table.

### Table clustering

Apply clustering keys to tables that: are large (100GB+), are frequently filtered on a specific column, and have poor natural clustering on that column (data was not loaded in order of the filter column).

Common clustering candidates: large transaction tables filtered by date range when data was not loaded chronologically, large event tables filtered by customer_id when events arrived in timestamp order, large order tables filtered by region when orders were loaded in random sequence.

Do not cluster small tables — the maintenance credit overhead exceeds the query benefit for tables under 100GB.

### Data types

Use the most specific data type for each column. VARCHAR over-specification (VARCHAR(1000) for a column that contains 2-character country codes) does not increase storage in Snowflake (storage is compressed), but accurate data types enforce data quality at the database level and communicate schema intent.

NUMBER over-specification: Snowflake's NUMBER type is exact numeric. For most numeric analytics columns (revenue, counts, quantities), NUMBER(18, 2) is appropriate. For floating-point scientific data, FLOAT. Do not use FLOAT for monetary values — floating-point representation causes rounding errors in financial calculations.

TIMESTAMP_NTZ vs TIMESTAMP_TZ: TIMESTAMP_NTZ stores a datetime with no timezone information. TIMESTAMP_TZ stores with timezone. For most analytics use cases, storing timestamps in UTC as TIMESTAMP_NTZ is the simplest approach. Timestamp_TZ adds complexity for applications that need to preserve original timezone information.

VARIANT for semi-structured data: Snowflake stores JSON, Avro, and Parquet natively in the VARIANT data type and queries it with dot notation (record:field_name). For source data that arrives as JSON (SaaS API responses, event streams), loading as VARIANT and extracting columns via SELECT record:field_name AS column_name is more flexible than defining a rigid schema upfront.

Data loading patterns

### COPY INTO for bulk loading

COPY INTO is Snowflake's bulk data loading mechanism. It reads files from a stage (Snowflake internal stage, S3, ADLS Gen2, or GCS) and loads them into a table. Parallelism is determined by the number and size of files — many files load faster than one large file of the same total size. Optimal file size for parallel COPY INTO: 100–250MB compressed.

COPY INTO is idempotent: if you COPY INTO a table from a stage and the same files have already been loaded (tracked by Snowflake's file ingestion metadata), they will not be loaded again. This makes COPY INTO safe to run multiple times without deduplication concerns.

### Snowpipe for streaming ingestion

Snowpipe is Snowflake's continuous data ingestion service — it loads files from a cloud storage stage as they arrive, without requiring a scheduled COPY INTO job. Files staged in S3, ADLS Gen2, or GCS trigger a Snowpipe load automatically via an event notification (S3 bucket notification, Azure Event Grid notification).

Snowpipe uses serverless compute (not your virtual warehouses) and is priced per credit consumed. It is appropriate for near-real-time data loading (minutes after data lands in storage) without managing a scheduled job. For very high-volume streaming, Kafka Connector for Snowflake or Snowflake Streaming Ingest API provide lower-latency ingestion.

### MERGE for upsert patterns

CDC (Change Data Capture) pipelines often need to apply inserts, updates, and deletes from a source system to a Snowflake table. MERGE handles this: match records by primary key, update matching records with new values, insert new records without a match, optionally delete records flagged for deletion.

MERGE in Snowflake is more expensive than INSERT for new-record-only workloads. For tables that are append-only (new records, never updated), use INSERT or COPY INTO rather than MERGE.

Performance troubleshooting

**Query Profile**: Snowflake's Query Profile (accessible in the Snowflake web interface for each query or via QUERY_HISTORY) shows the execution plan, node timing, rows processed, and bytes scanned for each query step. The key metrics to check: Bytes Scanned vs. Bytes Scanned from Cache (high cache ratio = efficient; low cache ratio = many micro-partitions read from storage), Rows Deleted by Partition Pruning vs. Rows Inserted into Result Sets (high pruning ratio = effective clustering), and time spent in the most expensive operator steps.

**Spillage**: if a query uses more memory than the warehouse has available, Snowflake spills to local disk (slower) and then to remote storage (much slower). Spillage is visible in Query Profile as "Bytes Spilled to Local Storage" and "Bytes Spilled to Remote Storage." High spillage indicates the warehouse is too small for the query or the query is reading too much data. Fix by increasing warehouse size, reducing data volume in the query (earlier filter, or smaller aggregation set), or breaking the query into smaller steps.

**JOIN types**: Snowflake uses broadcast joins (small table distributed to all nodes), hash joins (both tables repartitioned), or merge joins (pre-sorted inputs). The query optimizer selects the join type automatically. If hash join performance is poor (large JOIN result sets), check whether the join key has a usable clustering key or whether the joined tables can be filtered before joining.

For the cost governance layer that sits above architecture — monitoring credits, attributing costs, setting resource limits — see Snowflake pricing guide. For dbt-based transformation patterns that work with Snowflake's architecture, see what is dbt.

Our cloud engineering practice designs Snowflake data architectures for mid-market and enterprise organisations — from initial warehouse setup and schema design to performance tuning and cost optimisation of existing deployments. If you are designing a new Snowflake environment or optimising an existing one, book a free 30-minute audit for a direct assessment.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.