Snowflake: Architecture -- Storage/Compute Separation & Warehouses

Snowflake is a cloud-native data warehouse whose core architectural distinction is separating storage from compute -- unlike traditional warehouses tying both to the same fixed hardware, each scales (and is billed) independently.

Virtual Warehouses: Compute

-- Multiple independently-sized warehouses can query the SAME
-- underlying data simultaneously, without competing for resources
CREATE WAREHOUSE analyst_wh WITH WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300      -- pause after 5 min idle, stop billing
  AUTO_RESUME = TRUE;      -- transparently restarts on next query

CREATE WAREHOUSE etl_wh WITH WAREHOUSE_SIZE = 'X-LARGE';

-- Warehouse size scales power for ONE query; multi-cluster scaling
-- addresses CONCURRENCY -- many simultaneous queries, not query size
ALTER WAREHOUSE analyst_wh SET
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3;  -- spins up more clusters under peak load, scales back down after

Billing Reflects the Architecture

• Storage: billed on actual data volume stored -- relatively cheap cloud object storage pricing.

• Compute: billed per-second while a warehouse is actively running -- auto-suspend minimizes wasted spend during idle periods.

• A team can store a lot of historical data cheaply while only paying for compute during the actual query windows.

Automatic Micro-Partitioning

• Table data is automatically divided into small, immutable micro-partitions as it's loaded -- no manual partition key required for basic performance.

• Snowflake tracks min/max metadata per micro-partition -- queries with filter conditions can SKIP scanning partitions that can't contain matching rows.

• For a very large table whose natural load order diverges from typical query patterns, an explicit clustering key hint can improve pruning effectiveness -- an optional optimization, not required for basic functionality.

RBAC: Access Control

-- Privileges granted to ROLES (not individual users directly) --
-- roles can be granted to users or nested into other roles
CREATE ROLE analyst;
GRANT USAGE ON WAREHOUSE analyst_wh TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.reporting TO ROLE analyst;
GRANT ROLE analyst TO USER jane;

-- Updating what 'analyst' can access automatically applies to
-- everyone holding that role -- scales access management.