Multi-cluster Architecture · Editions & Features · Object Model · Databases & Schemas · Table & View Types
1. Multi-cluster Shared Data Architecture in Snowflake Fundamentals
Snowflake’s architecture separates storage, compute, and cloud services into three independent layers. This separation allows multiple workloads to run concurrently on the same data without contention.
The three layers:
- Storage layer: All data is stored centrally in compressed, columnar format on cloud blob storage (Amazon S3, Azure Blob, or Google Cloud Storage). It’s decoupled from compute, so it scales independently and is shared by every warehouse.
- Compute layer (Virtual Warehouses): MPP (massively parallel processing) clusters of compute resources that run queries and DML operations. Each warehouse is sized independently (X-Small to 6X-Large) and can be started, stopped, or resized without affecting the underlying data.
- Cloud services layer: Handles authentication, metadata management, query parsing/optimization, infrastructure management, and access control across the account.
Why “multi-cluster” and “shared data”:
- Multiple virtual warehouses can query the same underlying data simultaneously, each with its own compute, so heavy workloads (ETL, BI dashboards, ad-hoc analytics) don’t compete for resources — this is the “shared data” part.
- A single warehouse can also be configured as a multi-cluster warehouse: additional clusters spin up automatically when concurrent query load increases, and scale back down when demand drops. This is the “multi-cluster” part, aimed at concurrency rather than raw query speed.
- Two scaling modes for multi-cluster warehouses: Auto-scale (clusters start/stop based on load, minimizing cost) and Maximized (a fixed number of clusters always run, minimizing queuing).
Key benefits:
- Elasticity: Compute scales up/down or out/in independently of storage, with near-instant warehouse resize.
- No resource contention: Concurrent users/workloads don’t block each other since each can use separate compute.
- Pay-per-use: Warehouses can auto-suspend when idle and auto-resume on the next query, so compute cost tracks actual usage.
2. Snowflake Editions & Key Features in Snowflake Fundamentals
Snowflake offers four editions, each adding capabilities on top of the previous tier. The choice mainly comes down to compliance needs, security requirements, and advanced feature use.
| Edition | Highlights |
|---|---|
| Standard | Core data warehousing, Time Travel up to 1 day, standard support. |
| Enterprise | Everything in Standard, plus multi-cluster warehouses, extended Time Travel (up to 90 days), materialized views, and column-level security. |
| Business Critical | Everything in Enterprise, plus higher compliance support (HIPAA, PCI-DSS), customer-managed encryption keys, and database failover/failback across regions. |
| Virtual Private Snowflake (VPS) | Highest isolation tier — dedicated infrastructure separate from other Snowflake accounts, for organizations with the strictest security requirements. |
Key features available across editions:
- Zero-copy cloning: Instantly clone a database, schema, or table without duplicating the underlying storage; only changed data consumes new space.
- Time Travel: Query, clone, or restore historical data within a retention window, even after it’s been updated or deleted.
- Fail-safe: A fixed 7-day period after Time Travel expires during which Snowflake can recover data for disaster-recovery purposes (not self-service).
- Semi-structured data support: Native handling of JSON, Avro, ORC, Parquet, and XML via the VARIANT data type, without a separate ETL step.
- Automatic scaling and near-zero management: No indexes, no manual partitioning, no infrastructure tuning required.
3. Snowflake Object Model in Snowflake Fundamentals
Snowflake organizes everything in a strict hierarchy, similar to a filesystem, which determines how objects are named, referenced, and secured.
Hierarchy:
- Organization: The top-level entity that can contain one or more accounts (used for billing and account management across regions/clouds).
- Account: A top-level container tied to a specific cloud provider and region; holds all databases, warehouses, users, roles, and security policies.
- Database: A logical grouping of schemas within an account.
- Schema: A logical grouping of objects within a database (tables, views, etc.).
- Objects: The actual entities inside a schema: tables, views, stages, file formats, sequences, streams, tasks, stored procedures, and user-defined functions (UDFs).
Fully qualified naming:
Any object can be referenced with its fully qualified name: database.schema.object_name (e.g., SALES_DB.PUBLIC.ORDERS) — this avoids ambiguity when multiple schemas or databases contain similarly named objects.
Common object types:
- Tables & Views: Store and present data (covered in Topic 5).
- Stages: Named locations (internal or external) used to load/unload data files.
- File Formats: Reusable definitions of how to parse a file (CSV delimiters, JSON structure, etc.).
- Sequences: Auto-incrementing number generators, often used for surrogate keys.
- Streams & Tasks: Streams track changes (CDC) on a table; Tasks schedule SQL statements, often chained with Streams for automated incremental processing.
- Stored Procedures & UDFs: Encapsulate reusable procedural or scalar/tabular logic in SQL, JavaScript, Python, Java, or Scala.
4. Databases & Schemas in Snowflake Fundamentals
Databases and schemas are purely logical containers in Snowflake — they don’t map to physical storage allocations the way they might in traditional on-prem databases.
Databases:
- No fixed size limit; consumes only as much physical storage as the data it actually holds, since storage is decoupled from logical structure.
- Can be cloned instantly (zero-copy cloning) for creating dev/test copies without duplicating storage.
- Every database automatically includes a built-in INFORMATION_SCHEMA (metadata about objects) and a default PUBLIC schema.
Schemas:
- A schema is a namespace inside a database that groups related objects (tables, views, procedures, etc.).
- Schemas can also be marked Transient (see Topic 5 for the table-level equivalent); a transient schema’s objects skip Fail-safe, trading some recoverability for lower storage cost.
- Cross-schema and cross-database queries are supported within the same account using fully qualified names, so data doesn’t need to be copied between schemas to be joined.
Access & ownership:
- Access to databases and schemas is controlled through Snowflake’s role-based access control (RBAC) — privileges like USAGE, CREATE, and OWNERSHIP are granted to roles, which are then granted to users.
- Ownership of a database/schema (and everything inside it) can be reassigned to a different role, which is common when restructuring access as teams or projects change.
5. Table and View Types in Snowflake Fundamentals
Snowflake supports several table and view types, each trading off durability, cost, and use case differently.
Table types:
- Permanent: The default type; full Time Travel (up to the edition’s max retention) plus the standard 7-day Fail-safe period. Used for standard production data.
- Temporary: Exists only for the duration of the session that created it; automatically dropped when the session ends. No Fail-safe. Useful for intermediate/staging data within a single session or script.
- Transient: Persists across sessions like a permanent table, but has no Fail-safe period and a maximum 1-day Time Travel window. Commonly used for staging tables or ETL intermediates where long-term recovery isn’t needed, to reduce storage costs.
- External Tables: Reference data files that remain stored outside Snowflake (e.g., in an S3 bucket); Snowflake stores only the metadata/schema mapping, not the data itself, and queries read the external files directly.
View types:
- Standard View: A saved query; no data is physically stored, and the underlying query runs fresh each time the view is referenced.
- Materialized View: Stores the precomputed result set physically and refreshes it automatically as underlying data changes; speeds up repeated, expensive aggregations at the cost of extra storage and compute for refresh (Enterprise edition and above).
- Secure View: Hides the view’s underlying query definition and table structure from users who only have access to the view, not the base tables; commonly used when sharing data externally or across teams without exposing business logic.
| Need | Best fit |
|---|---|
| Production data needing full recovery options | Permanent table |
| Session-only scratch data | Temporary table |
| ETL staging data, lower storage cost, no long recovery needed | Transient table |
| Query data sitting in cloud storage without loading it | External table |
| Reusable query logic, always up to date, no extra storage | Standard view |
| Fast repeated access to an expensive aggregation | Materialized view |
| Sharing data without exposing the query/schema logic | Secure view |