98 Snowflake Data Warehouse Interview Questions and Answers (2026)

Snowflake uses a hybrid architecture with three physically decoupled layers: a shared storage layer, an elastic compute layer of virtual warehouses, and a cloud services layer that coordinates everything. Each scales independently, which is the source of Snowflake's flexibility.

• Database storage layer:

  • Holds all data as immutable, compressed, columnar micro-partitions in cloud object storage (S3, Blob, GCS).

  • Snowflake manages organization, compression, statistics, and file layout: it is not directly accessible, only via SQL.

• Compute layer (virtual warehouses):

  • Each virtual warehouse is an independent MPP cluster of compute nodes that executes queries.

  • Multiple warehouses read the same storage concurrently without contending, and can be sized/started/stopped independently.

• Cloud services layer:

  • The "brain": authentication, metadata management, query parsing and optimization, transaction management, and access control.

  • Also powers features like the result cache, pruning via micro-partition metadata, and security policies.

Snowflake is a fully managed cloud data platform (a cloud data warehouse) delivered as SaaS: you run SQL and never manage infrastructure, servers, or software installs.

• It is SaaS, not PaaS:

  • There is nothing to install, patch, or tune at the OS level: Snowflake handles provisioning, upgrades, and maintenance transparently.

  • You consume it through SQL, a web UI, or connectors, paying per usage.

• Runs on public clouds: Deployed on AWS, Azure, or GCP, but abstracts them away behind one consistent interface.

• Relational and ANSI-SQL: It's a columnar, ACID-compliant relational database that also natively handles semi-structured data (JSON, Avro, Parquet) via the VARIANT type.

The storage layer is foundational: it is the single source of truth all compute reads from, and how Snowflake stores data (micro-partitions) directly drives query performance. Its design enables pruning, cloning, and Time Travel.

• Central persistent store: All tables live here in cloud object storage; every virtual warehouse queries this shared copy.

• Micro-partitions:

  • Data is auto-divided into compressed columnar micro-partitions (~50-500MB uncompressed each) with per-partition metadata (min/max, counts).

  • That metadata enables partition pruning: skipping partitions that can't match a filter.

• Enables key platform features: Because storage is immutable and versioned, Snowflake supports Time Travel, Fail-safe, and zero-copy cloning.

• Fully managed: Compression, encryption, and layout are automatic: you never manage files directly, only through SQL.

Snowflake's differentiators come from its decoupled architecture and fully managed SaaS delivery: features that are hard or impossible in traditional MPP warehouses where compute and storage are fused.

• Separation of storage and compute: Independent scaling and per-workload isolation via virtual warehouses.

• Elastic, per-second compute: Instant resize, auto-suspend/resume, and multi-cluster warehouses that auto-scale for concurrency.

• Zero-copy cloning: Instantly clone databases/tables by copying metadata, not data: ideal for dev/test.

• Time Travel and Fail-safe: Query or restore historical data (up to 90 days) and recover from accidental changes.

• Native semi-structured data: The VARIANT type handles JSON/Parquet alongside relational data.

• Secure Data Sharing: Share live data with other accounts without copying, via the Marketplace or direct shares.

• Near-zero administration: No indexes, partitions, or vacuuming to manage: automatic optimization and maintenance.

A virtual warehouse is a named cluster of compute resources (CPU, memory, and local SSD cache) that executes queries and DML in Snowflake. It is independent from storage, so you can start, stop, resize, or run multiple warehouses without affecting the data.

• What it does:

  • Provides compute for SELECTs, INSERT/UPDATE/DELETE, COPY, and other query operations.

  • Reads data from the shared storage layer and processes it in memory, spilling to local/remote disk if it doesn't fit.

• Sizing: T-shirt sizes (XS, S, M, L, ...) where each step adds compute nodes and roughly doubles credits per hour.

• Local cache: Each warehouse caches recently read micro-partitions on local SSD, speeding repeated queries until it suspends.

• Isolation: Separate warehouses per workload (ETL, BI, ad hoc) prevent resource contention while sharing the same data.

Auto-suspend stops a warehouse after a set period of inactivity so you stop paying for idle compute, and auto-resume restarts it automatically when a new query arrives. Together they ensure you only pay (per-second, minimum 60s) for compute you actually use.

• Auto-suspend:

  • Configured via AUTO_SUSPEND (seconds of inactivity before suspending).

  • Suspended warehouses consume no credits.

• Auto-resume: Set with AUTO_RESUME = TRUE; the warehouse spins back up transparently on the next query.

• Cost/performance trade-off:

  • Short auto-suspend saves credits but discards the local SSD cache, so subsequent queries lose warm-cache benefits.

  • Common practice: keep it short (e.g. 60s) for sporadic workloads, longer for BI dashboards that benefit from cache reuse.

A virtual warehouse moves between running, suspended, and resizing states, which determine whether it consumes credits and can execute queries.

• Started / Running: Provisioned and actively able to run queries; consumes credits while in this state.

• Suspended: Shut down (via AUTO_SUSPEND or manually); consumes no credits, and its local SSD cache is cleared.

• Resizing: Changing size (scaling up/down); running queries finish on the old size while new ones use the new size.

• Resuming (transitional): Spinning back up from suspended; takes a moment to provision compute before queries run.

Snowflake bills warehouse compute in credits by the second, but with a 60-second minimum each time a warehouse starts or resumes: you always pay for at least the first minute, then per second afterward.

• Credit rate scales with size: XS burns 1 credit/hour, S = 2, M = 4, doubling each size; multi-cluster multiplies by active cluster count.

• 60-second minimum on start/resume: A warehouse that resumes and runs a 5-second query is still billed for 60 seconds.

• Per-second after the first minute: Beyond 60 seconds, billing is by the second with no further rounding.

• Practical implication: Frequent suspend/resume cycles re-trigger the 60-second minimum each time, so overly aggressive auto-suspend can cost more, not less.

A micro-partition is a compressed, immutable, columnar storage unit (~50-500MB uncompressed) that Snowflake creates automatically as data is ingested. With each one, Snowflake maintains metadata in the cloud services layer that it uses to optimize and prune queries.

• Metadata stored per micro-partition:

  • The range of values (min and max) for each column.

  • The number of distinct values per column.

  • Count of NULLs and other statistics.

  • Which columns exist and their offsets, enabling column-level access.

• Why it matters:

  • This metadata drives pruning and cost estimation without scanning the data itself.

  • It is stored and managed centrally, separate from the compute that reads the data.

Within each micro-partition, Snowflake stores data column by column rather than row by row. This columnar layout lets queries read only the columns they need and compress data far more effectively, which dramatically reduces I/O for analytical workloads.

• Selective column access: A query touching 3 of 50 columns reads only those 3, not entire rows.

• Better compression:

  • Values within a column share a data type and often similar values, so compression ratios are high.

  • Snowflake picks the compression scheme automatically per column.

• Analytical efficiency: Aggregations and scans over few columns are the norm in analytics and map perfectly to columnar reads.

• Combined with metadata: columnar layout plus min/max stats maximizes both pruning and scan efficiency.

No, Snowflake does not use traditional user-defined indexes. Instead it relies on automatic micro-partition metadata and pruning to achieve fast lookups, which removes the need for indexes and the maintenance they demand.

• Why indexes aren't needed:

  • Each micro-partition carries min/max and other stats, so Snowflake prunes irrelevant partitions automatically.

  • Columnar storage limits reads to the needed columns, further reducing scan cost.

• Why avoiding indexes is desirable:

  • Indexes require building, storage, and ongoing tuning: contrary to Snowflake's no-maintenance philosophy.

  • They can become stale or bloated under heavy DML.

• What you tune instead:

  • Define a clustering key on large tables to co-locate related data and improve pruning.

  • Newer features like search optimization service help point lookups on large tables.

Time Travel lets you query, restore, or clone data as it existed at a point in the past, within a configurable retention window. Snowflake keeps historical versions of data automatically whenever rows change, tables are dropped, or objects are altered.

• How you access it:

  • Query past data with AT or BEFORE clauses using TIMESTAMP, OFFSET (seconds ago), or a STATEMENT query id.

  • Restore dropped objects with UNDROP, or recover from a bad update by cloning/copying an earlier state.

• Retention period:

  • Controlled by DATA_RETENTION_TIME_IN_DAYS, set at account, database, schema, or table level.

  • Default is 1 day; Standard edition allows 0-1 day, Enterprise+ allows 0-90 days for permanent objects.

  • Transient and temporary objects max out at 1 day.

• Common use cases:

  • Recovering from accidental or erroneous DML (a wrong DELETE or UPDATE).

  • Restoring dropped tables/schemas/databases.

  • Historical analysis: comparing current data against a prior snapshot for auditing or trend checks.

The main use case is spinning up full-size, isolated copies of production data instantly and cheaply for development, testing, and analytics, without impacting or duplicating the source.

• Dev/test environments:

  • Clone production to a dev database so engineers test against realistic data with no storage penalty until they modify it.

  • Refresh nightly by dropping and re-cloning, cheaper than physical copies.

• Safe experimentation and QA: Test schema migrations, new pipelines, or destructive changes on a clone before applying to production.

• Ad-hoc analytics and sandboxes: Give analysts a private clone to run heavy or exploratory queries without touching live tables.

• Backup and recovery checkpoints: Clone before a risky bulk load to keep an instant rollback point.

• Scope: You can clone tables, schemas, or entire databases with CREATE ... CLONE.

UNDROP instantly restores a dropped table, schema, or database by relying on Time Travel: the object's data still exists in the retention window, so Snowflake simply reactivates the metadata pointers rather than recovering from backup.

• How it works:

  • Dropping an object doesn't immediately delete its partitions; they're kept for the Time Travel period.

  • UNDROP restores the most recent version of the object, near-instantly and with no data copy.

• Dependency on Time Travel:

  • Only works while within the retention window; once Time Travel expires the object moves to Fail-safe and UNDROP fails.

  • If an object with the same name already exists, you must rename it first.

• Syntax: Works at three levels: UNDROP TABLE, UNDROP SCHEMA, UNDROP DATABASE.

A Snowflake stage is a named location that holds data files for loading into tables (or unloading out of them). It's the intermediary between raw files and structured tables.

• Two categories:

  • Internal: storage managed by Snowflake (user, table, or named stages).

  • External: a pointer to your own cloud bucket (S3, Azure, GCS).

• Role in data loading:

  • You place files in the stage (via PUT for internal, or they already exist for external).

  • COPY INTO reads from the stage into a table; Snowpipe watches a stage for new files.

  • You can inspect files with LIST @stage and query them directly before loading.

• Also used for unloading data out of Snowflake back to files.

COPY INTO is Snowflake's bulk data movement command: it loads data from staged files into a table (COPY INTO <table>) or unloads table data out to files in a stage (COPY INTO <location>).

• Loading (ingest):

  • Reads files from an internal or external stage (S3, Azure Blob, GCS) into a target table.

  • Uses a file format (CSV, JSON, Parquet, etc.) and can apply column transformations during load.

• Unloading (export): Writes query or table results to files in a stage for downstream use.

• Load metadata tracking: Snowflake records which files were loaded and skips already-loaded files by default, preventing duplicates.

• Runs on a virtual warehouse: Batch loading uses warehouse compute; Snowpipe wraps COPY INTO for continuous serverless loading.

Snowflake bills on a consumption model denominated in credits: you pay for compute (credits burned by running warehouses), storage (flat rate per TB per month), and cloud services (credits, but largely free). Credits translate to dollars based on your edition and cloud region.

• Compute:

  • Virtual warehouses consume credits per second (minimum 60s) while running; cost scales with size (each T-shirt size doubles credits/hour) and count of clusters.

  • Also includes serverless features (auto-clustering, Snowpipe, materialized view maintenance).

• Storage:

  • Billed on average compressed bytes per month at a flat per-TB rate (on-demand or lower capacity pricing).

  • Includes Time Travel and Fail-safe copies, so retention settings affect the bill.

• Cloud services: Metadata, authentication, query optimization, result cache; consumes credits but only charged for usage exceeding 10% of daily compute credits.

• Credit-to-dollar: A credit's price depends on edition (Standard, Enterprise, Business Critical) and cloud/region, so higher editions cost more per credit but add features.

Snowflake's RBAC grants privileges to roles, and roles to users (or to other roles); users act with the privileges of whatever role is active. Access is never granted directly to users, which makes permissions manageable and auditable.

• Core objects:

  • Privileges: fine-grained rights on securable objects (e.g. SELECT on a table, USAGE on a schema).

  • Roles: named collections of privileges granted to users.

  • Users: authenticate and are assigned one or more roles.

• Role hierarchy:

  • Roles can be granted to other roles, so a parent role inherits all child privileges; a user switches active role with USE ROLE.

  • System roles: ACCOUNTADMIN (top), SECURITYADMIN, USERADMIN, SYSADMIN, and PUBLIC (granted to everyone).

• Ownership and future grants: The owning role can manage an object; GRANT ... ON FUTURE auto-applies privileges to objects created later.

• Best practice: Build functional roles (e.g. analyst, loader) granted to users, and align a role hierarchy under SYSADMIN for object ownership; least privilege throughout.

The Snowflake Marketplace is a public catalog where providers publish data products and consumers discover and access them: it is built directly on secure data sharing, so acquiring a dataset mounts it as a live, read-only shared database rather than downloading files.

• A discovery layer over data sharing: Listings are backed by shares; getting a free/public dataset gives you an instantly queryable database with no ETL.

• Listing types: Free/public data, personalized listings (request-based), and paid listings monetized through Snowflake.

• Benefits:

  • No copying, always-current data, and consumers query it with their own warehouse.

  • Cross-region/cloud listings are handled via replication behind the scenes.

• Relation to private sharing: Direct shares are one-to-one/private; the Marketplace is the public, discoverable extension of the same underlying mechanism (with Data Exchange as a private-hub option).

Traditional architectures couple compute and storage, forcing a trade-off; Snowflake decouples them so each scales on its own. This avoids the data-movement bottleneck of shared-nothing and the concurrency bottleneck of shared-disk.

• Shared-disk: All nodes share one storage but compete for it, so a single disk/IO subsystem becomes the bottleneck under concurrency.

• Shared-nothing:

  • Data is partitioned across nodes that own their local disk, so scaling compute means physically redistributing (reshuffling) data.

  • Storage and compute grow together even if you only need one.

• Snowflake's decoupled model: Central storage plus independent compute clusters: resize a warehouse without moving any data.

• Benefits:

  • Independent scaling: pay for storage and compute separately.

  • Workload isolation: ETL, BI, and data science each get their own warehouse over the same data, no contention.

  • Elasticity: scale up for a heavy query, then down, and auto-suspend when idle.

Snowflake is primarily a data warehouse, but it can serve as a data lake or, more accurately, support a lakehouse pattern: it stores structured and semi-structured data natively and can query external files in place without loading them.

• Native semi-structured support: The VARIANT type ingests JSON, Avro, ORC, and Parquet, queryable with SQL (dot/bracket notation), schema-on-read style.

• External tables and stages:

  • External tables let you query raw files in S3/Blob/GCS in place without ingesting: the classic data-lake access pattern.

  • Iceberg tables allow open-format lake data managed with Snowflake performance.

• Caveat: A pure data lake stores raw, cheap, open-format files for any engine; Snowflake adds governance and compute on top, so many teams pair a lake with Snowflake rather than replace it.

Snowflake organizes objects in a strict containment hierarchy: Organization > Account > Database > Schema > objects (tables, views, stages). This structure drives how you isolate environments, apply security, and manage governance.

• Namespacing and access control:

  • Privileges are granted per object and inherited/managed through the RBAC model, so hierarchy shapes your grant strategy.

  • Fully-qualified names (db.schema.table) avoid ambiguity across environments.

• Environment isolation: Separate databases or schemas for dev/test/prod, or separate accounts for hard isolation.

• Compute is decoupled from the hierarchy: Warehouses are account-level and not tied to a database, so any warehouse can query any database it has rights to.

• Sharing and cloning boundaries: Cloning and secure data shares operate at database/schema/table levels, so hierarchy design affects what can be shared or cloned cleanly.

Snowflake offers tiered editions where higher tiers add security, compliance, and availability features on top of the previous tier: Standard, Enterprise, Business Critical, and Virtual Private Snowflake (VPS).

• Standard: Core data warehouse: full SQL, secure data sharing, Time Travel up to 1 day, always-on encryption.

• Enterprise: Adds multi-cluster warehouses, Time Travel up to 90 days, materialized views, search optimization, column/row-level security, and dynamic data masking.

• Business Critical: Adds enhanced security/compliance (HIPAA, PCI), customer-managed keys (Tri-Secret Secure), private connectivity (PrivateLink), and database failover/failback for DR.

• Virtual Private Snowflake (VPS): Highest isolation: a dedicated, isolated Snowflake environment for the most sensitive workloads (e.g. financial, government).

Scaling up changes a single warehouse's size (more compute per query) to handle heavy or complex queries faster; scaling out adds more clusters of the same size to serve more concurrent queries. You pick based on whether the bottleneck is query complexity or concurrency.

• Scaling up (resize: XS -> S -> M -> L ...):

  • Each size step roughly doubles compute (and cost per hour).

  • Use for slow, large, complex queries or heavy joins/aggregations, and to reduce spilling to disk.

• Scaling out (multi-cluster warehouse):

  • Adds/removes clusters automatically between MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT based on queued load.

  • Use for many concurrent users/queries where requests are queuing, not for making one query faster.

• Rule of thumb: Slow individual query, scale up; queries waiting in queue, scale out. Often you combine both.

For multi-cluster warehouses, the scaling policy controls how aggressively Snowflake starts and shuts down clusters. Standard favors performance (start clusters quickly, minimize queuing), while Economy favors cost (keep clusters busy before adding more, tolerate some queuing).

• Standard policy:

  • Starts a new cluster immediately when queries begin queuing.

  • Shuts a cluster down after ~2 consecutive minutes of low load.

  • Prioritizes low latency and concurrency over cost.

• Economy policy:

  • Only starts a new cluster if the system estimates enough queued work to keep it busy at least ~6 minutes.

  • Waits longer (~5-6 min of low load) before shutting a cluster down.

  • Maximizes credit efficiency at the cost of possible queuing/latency.

• Choosing: Standard for user-facing/interactive workloads; Economy for batch or cost-sensitive workloads that tolerate wait.

Query queuing means requests are waiting because the warehouse has no free resources to run them concurrently, a classic sign it is undersized or under-provisioned for its concurrency demand. You address it by scaling out, scaling up, or better isolating workloads.

• How to detect it: Check QUEUED_OVERLOAD_TIME and QUEUED_PROVISIONING_TIME in QUERY_HISTORY, plus the warehouse load chart.

• What it indicates:

  • Overload queuing: too many concurrent queries for the current compute.

  • Also watch disk spilling, which instead signals per-query resource shortage (needs a bigger size).

• Actions:

  • Scale out: enable a multi-cluster warehouse (raise MAX_CLUSTER_COUNT) to add clusters under load.

  • Scale up: increase warehouse size if individual queries are heavy or spilling.

  • Segregate workloads onto separate warehouses so ETL doesn't queue behind BI.

  • Tune MAX_CONCURRENCY_LEVEL or optimize expensive queries.

You isolate workloads by giving each one its own dedicated virtual warehouse, since warehouses have independent compute and never share resources: ETL heavy loads won't slow down interactive BI queries.

• Separate warehouses per workload: Create e.g. ETL_WH, BI_WH, LOAD_WH: each has its own compute so contention is impossible across them.

• Size to the workload: ETL/batch: larger warehouse for heavy transforms; BI: smaller with multi-cluster for many concurrent users.

• Multi-cluster for concurrency-heavy BI: Auto-scale out clusters when dashboard users spike, so queries queue less.

• Cost control per workload: Independent warehouses let you set AUTO_SUSPEND, resource monitors, and track credit usage per team/workload.

• Because storage is shared, all warehouses query the same single copy of data, no duplication.

Warehouse size sets how much compute you pay for per unit time, while auto-suspend stops billing during idle periods: together they balance speed against cost.

• Sizing (XS to 6XL):

  • Each size up doubles compute and credit cost per hour (XS = 1, S = 2, M = 4...).

  • Bigger isn't always more expensive: a larger warehouse may finish a heavy query in a fraction of the time, so total credits can be similar while performance improves.

  • Oversizing small queries wastes credits with no speedup.

• Auto-suspend:

  • Suspends after an idle interval so you stop paying when nobody queries.

  • Trade-off: very short suspend saves money but clears the local cache, so the next query is slower (cold start).

  • Tune it: seconds for spiky ad-hoc, longer for BI where warm cache matters.

• Auto-resume pairs with it to spin up transparently on the next query.

A multi-cluster warehouse can run several identical compute clusters behind one warehouse name, automatically adding clusters to handle concurrent query load and removing them when demand falls.

• Scaling out, not up: Addresses concurrency (many users/queries at once), unlike resizing which addresses a single query's complexity.

• Configured with min/max clusters: Snowflake adds clusters when queries start queuing and drops them when load subsides.

• Benefits:

  • Prevents query queuing during peak BI/dashboard usage.

  • Elastic cost: you pay for extra clusters only while they run.

  • Transparent to users: same warehouse name, Snowflake routes queries.

• Available on Enterprise edition and above.

A virtual warehouse is a cluster of compute resources (CPU, memory, local SSD) that executes queries and data loads; storage is separate, so warehouses only provide processing power. You size it to the workload's complexity and concurrency.

• What it does: Runs SELECTs, DML, and loads; can be started, suspended, and resized independently without affecting stored data.

• Sizes are T-shirt sized: XS, S, M, L up to 6XL; each step doubles compute and cost.

• Sizing by workload:

  • Small ad-hoc / dev: XS or S.

  • Complex transforms or large scans: scale up (L/XL) to finish faster.

  • High concurrency (many BI users): keep size modest but enable multi-cluster to scale out.

• Approach: start small, monitor spillage to disk and query duration, then adjust rather than guessing.

Auto-scaling (multi-cluster scale-out plus auto-suspend/resume) matches compute to actual demand, so you get performance when load is high and stop paying when it isn't.

• Scale-out for concurrency: Extra clusters spin up when queries queue and shut down when demand drops, so users avoid waits during peaks.

• Cost management:

  • You pay only for clusters actually running; idle capacity releases automatically.

  • Auto-suspend stops all billing when the warehouse is idle; auto-resume restarts on the next query.

• Scaling policy tuning: Standard favors performance (adds clusters eagerly); Economy favors savings (waits, packs queries).

• Net effect: elastic performance under load without paying for a permanently large warehouse.

Multi-cluster warehouses run in one of two modes set by min and max cluster counts: maximized (fixed number always running) or auto-scale (dynamic between a min and max). Both handle concurrency, but differently on cost.

• Maximized mode:

  • Set when min = max (both greater than 1): all clusters start immediately and stay running.

  • Guarantees steady capacity for predictable, sustained high concurrency; costs more since all clusters run regardless of load.

• Auto-scale mode:

  • Set when min < max: Snowflake adds/removes clusters between the bounds based on query queuing.

  • Cost-efficient for variable/spiky concurrency: pay for extra clusters only during peaks.

• Concurrency effect:

  • Both prevent queuing by spreading concurrent queries across clusters; maximized is proactive, auto-scale is reactive within limits.

  • Scaling policy (Standard vs Economy) governs how eagerly auto-scale adds clusters.

Micro-partitions are the fundamental storage units in Snowflake: contiguous units of ~50-500MB of uncompressed data (compressed on disk) into which table data is automatically divided. They power query performance because Snowflake stores rich metadata per micro-partition and uses it to skip reading partitions that can't match a query.

• What they are:

  • Data is split into many small immutable files as it is loaded, automatically, with no user management.

  • Each holds columns stored independently and compressed.

• How they boost performance:

  • Pruning: metadata (min/max per column) lets Snowflake skip whole micro-partitions that fall outside a filter range.

  • Columnar reads: only the columns referenced by the query are scanned.

  • Fine granularity: small size means pruning is precise, so less I/O per query.

• Automatic and transparent: no DBA-managed partition schemes required.

Snowflake uses per-column min/max metadata to skip (prune) micro-partitions whose value ranges cannot satisfy a query's filters, so it reads only the partitions that might contain matching rows. Because this pruning is automatic and effective, Snowflake has no need for traditional user-defined indexes.

• How pruning works:

  • For a predicate like WHERE date = '2024-01-01', Snowflake checks each partition's min/max for that column.

  • Partitions whose range excludes the value are skipped entirely, cutting I/O.

  • It also prunes within a partition at the column level via columnar storage.

• Why no user-defined indexes:

  • Metadata-based pruning already narrows scans without extra structures to build or maintain.

  • Indexes require tuning, storage, and maintenance overhead that conflict with Snowflake's zero-management goal.

  • Clustering (natural or via a clustering key) improves pruning effectiveness instead.

Metadata in Snowflake lives in the cloud services layer, a centralized, always-on tier separate from storage and compute. This layer maintains all the information needed to locate data, optimize queries, and enforce security, and it is fully managed by Snowflake.

• What the cloud services layer holds:

  • Micro-partition statistics (min/max, distinct counts, NULLs) used for pruning.

  • Table and object definitions, roles, grants, and access control.

  • Query parsing, optimization plans, and the result cache.

  • Transaction and versioning info powering Time Travel and cloning.

• How it's managed:

  • Runs as a shared, multi-tenant, automatically scaled service: no user provisioning.

  • Coordinates the other layers: it decides which micro-partitions the compute (virtual warehouses) must read.

• Benefit: because metadata is decoupled from data, some queries (e.g. COUNT(*), MIN/MAX) can be answered from metadata alone.

Snowflake handles partitioning automatically: as data loads, it is divided into micro-partitions with no user-defined partition keys or manual distribution. Data is stored centrally in cloud object storage and accessed by any virtual warehouse, so distribution is a matter of which micro-partitions compute reads, not physically sharding data across nodes you manage.

• Automatic partitioning:

  • Rows are grouped into micro-partitions in load order, each with its own metadata.

  • No CREATE INDEX or manual partition schemes; it's transparent.

• Centralized storage, decoupled compute:

  • Data lives once in the storage layer (cloud object store); virtual warehouses read from it independently.

  • Multiple warehouses can query the same data concurrently without copying it.

• Clustering for organization:

  • Natural clustering follows load order; a defined clustering key re-organizes data to keep related values together.

  • Better clustering means better pruning, which is Snowflake's substitute for physical distribution tuning.

Time Travel is a user-accessible recovery window you control; Fail-safe is a non-configurable 7-day period after Time Travel expires that only Snowflake support can use for disaster recovery. Together they form a layered data protection lifecycle.

• Time Travel:

  • Duration is configurable (0-90 days depending on edition).

  • Self-service: you run SELECT ... AT, UNDROP, or clone historical data yourself.

• Fail-safe:

  • Fixed 7 days, only for permanent objects, and cannot be disabled or changed.

  • Not queryable by you: recovery is a best-effort process handled only by Snowflake support.

  • Intended as a last resort for catastrophic loss, not routine recovery.

• Order of the lifecycle: Active data → Time Travel (N days) → Fail-safe (7 days) → permanently purged.

• Both consume storage: You pay for historical/Fail-safe storage; transient and temporary tables have no Fail-safe to reduce cost.

They combine powerfully: zero-copy cloning can target a historical point via Time Travel, letting you create an instant, independent copy of a table as it existed in the past without duplicating storage. This is ideal for point-in-time recovery and safe experimentation.

• How they connect:

  • A clone references existing micro-partitions by metadata; adding an AT/BEFORE clause makes it reference the partitions from that past moment.

  • The result is a new, writable object that starts as a snapshot of the old state and diverges independently.

• Real-world scenario:

  • A faulty ETL job corrupts the sales table at 2am. Instead of blocking production, you clone it as of just before the job ran.

  • You validate the cloned data, then swap it in or reconcile, all without extra storage for unchanged partitions and without downtime.

Time Travel increases storage costs because Snowflake must retain the historical versions of data (the micro-partitions that changed or were deleted) for the entire retention window. The longer the retention and the more churn, the more you pay.

• What drives the cost:

  • When rows are updated or deleted, old partitions can't be freed until Time Travel (and then Fail-safe) expires.

  • High-churn tables with long retention accumulate large historical footprints.

• Levers to control it:

  • Tune DATA_RETENTION_TIME_IN_DAYS down for volatile or non-critical tables.

  • Use transient tables (max 1 day, no Fail-safe) for staging/intermediate data.

  • Set retention to 0 where recovery isn't needed to avoid holding history at all.

• Note: Time Travel storage is billed on top of active storage, and Fail-safe adds another 7 days for permanent tables.

The three table types differ in how durably Snowflake protects their data and how long they persist, which directly affects Time Travel, Fail-safe, and storage cost. Permanent tables are fully protected, temporary tables are session-scoped, and transient tables sit in between with minimal protection.

• Permanent (default):

  • Time Travel: 0-90 days (Enterprise+), default 1 day.

  • Fail-safe: yes, 7 days.

  • Highest storage cost due to history plus Fail-safe; use for critical production data.

• Transient:

  • Persists across sessions until explicitly dropped.

  • Time Travel: 0-1 day only.

  • Fail-safe: none, so lower storage cost; good for staging/ETL intermediate data that can be regenerated.

• Temporary:

  • Exists only for the session and is dropped when the session ends.

  • Time Travel: 0-1 day (within session lifetime).

  • Fail-safe: none; cheapest, used for scratch/session-scoped work.

• Rule of thumb: Use TRANSIENT or TEMPORARY to cut storage cost when data is reproducible and doesn't need Fail-safe protection.

A materialized view is a pre-computed, persisted result of a query that Snowflake stores physically and keeps automatically in sync with its base table, so expensive aggregations or filtering run fast without recomputing each time.

• What it is: Persisted query result maintained by a background service; Snowflake transparently rewrites queries to use it when beneficial (even if you query the base table).

• Benefits:

  • Accelerates repeated complex scans/aggregations over large, slow-changing tables.

  • Always current: the maintenance service refreshes it as base data changes, so results aren't stale.

• Key restrictions:

  • Single table only: no joins, no UNION, no self-joins, no subqueries.

  • Limited aggregation support and no HAVING, ORDER BY, LIMIT, or window functions.

  • Enterprise Edition feature.

• Maintenance considerations:

  • Incurs serverless compute + storage costs for automatic maintenance; frequently changing base tables make it expensive.

  • Best for tables that are queried far more often than they change.

An external table lets Snowflake query data files sitting in external cloud storage (S3, Azure Blob, GCS) as if they were a table, without loading the data into Snowflake. Metadata points to the files; the data stays external.

• How it works:

  • Defined over an external stage; Snowflake reads files (Parquet, JSON, CSV, etc.) at query time.

  • Exposes a virtual VALUE column of raw rows plus user-defined virtual columns derived via expressions.

  • Uses partition columns (often from file paths) plus metadata refresh to prune files scanned.

• Keeping metadata current: Run ALTER EXTERNAL TABLE ... REFRESH or enable auto-refresh via cloud event notifications so new files are recognized.

• Use cases:

  • Query a data lake in place without an ingestion/ETL step.

  • Occasional or exploratory access to large archival data where loading isn't worth the cost.

  • Lakehouse patterns: build materialized views on top for performance.

• Trade-offs:

  • Read-only, and typically slower than native tables (no micro-partition optimization on raw files).

  • Iceberg tables now offer a richer alternative for lakehouse workloads.

Snowflake stores semi-structured data using three native types: VARIANT (any type, including nested JSON/XML/Avro), OBJECT (key-value maps), and ARRAY (ordered lists). You load documents as-is and query them with SQL, without defining a rigid schema up front.

• The three types:

  • VARIANT: a universal container; a whole JSON/Avro/XML document fits in one column.

  • OBJECT: nested key-value structure (like a JSON object).

  • ARRAY: ordered collection accessed by index.

• Querying with path notation:

  • Access nested fields with col:path.to.field and array elements with [n]; cast with ::.

  • Snowflake stores VARIANT in an optimized columnar form internally, so queries stay fast.

• vs. traditional relational databases:

  • Schema-on-read: no need to predefine columns or normalize; the structure can vary row to row.

  • Legacy RDBMS require rigid schema-on-write or store JSON as opaque text/blobs with limited native querying.

A Dynamic Table is a table whose contents are defined by a query and refreshed automatically by Snowflake to meet a target freshness (lag). You declare the desired end state, and Snowflake incrementally maintains it, turning multi-step transformations into a declarative pipeline.

• Declarative model: You write CREATE DYNAMIC TABLE ... AS SELECT ... and set TARGET_LAG; you don't write refresh logic or schedules.

• Automated, incremental refresh:

  • Snowflake computes only the changes since the last refresh when possible, keeping results within the specified lag.

  • A managed refresh service handles orchestration behind the scenes.

• Automatic dependency chaining: Dynamic tables built on other dynamic tables form a DAG; Snowflake refreshes them in the correct order.

• Benefit: Replaces hand-built Stream + Task pipelines with simpler, self-maintaining definitions.

Dynamic Tables are declaratively defined, automatically refreshed tables that let Snowflake manage incremental transformation to a target freshness. Streams and Tasks are the older, imperative building blocks where you manually capture changes and schedule the SQL to process them.

• Dynamic Tables (declarative):

  • You define the target query and TARGET_LAG; Snowflake decides how and when to refresh incrementally.

  • Dependencies auto-resolve into a DAG, so multi-stage pipelines need no manual orchestration.

• Streams + Tasks (imperative):

  • A Stream tracks change data (inserts/updates/deletes) on a table via an offset.

  • A Task runs SQL on a schedule (or on a trigger) to consume the stream and apply MERGE/insert logic you write.

  • You own scheduling, ordering, and error handling.

• When to choose which:

  • Dynamic Tables: standard transformation pipelines where you want simplicity and self-maintenance.

  • Streams + Tasks: custom logic, side effects, calling procedures, or fine-grained control the declarative model can't express.

You load semi-structured data into a VARIANT column and query nested fields with path/cast notation. When a field is an array (or object) that you need as rows, FLATTEN explodes it into one row per element, turning nesting into a relational shape you can join and aggregate.

• Load and access: Store JSON/Avro/XML in VARIANT; navigate with col:field.subfield and cast using ::.

• Why FLATTEN matters:

  • A table function that unnests arrays/objects: each element becomes a row, exposing VALUE, INDEX, and KEY.

  • Used with LATERAL to correlate each parent row with its child elements.

  • Enables SQL joins/aggregations over data that started as nested documents.

• Related helpers: PARSE_JSON, OBJECT_KEYS, and ARRAY_SIZE support inspection and transformation.

All three expose a query as a queryable object, but they differ in what they hide and whether they store data: a standard view is a plain stored query, a secure view adds security/privacy guarantees, and a materialized view physically stores pre-computed results for speed.

• Standard view:

  • Just a saved SELECT; computed at query time, stores no data.

  • Definition is visible, and the optimizer may expose underlying details through query internals.

• Secure view:

  • Hides the view definition from unauthorized users and prevents optimizer pushdowns that could leak base-table data.

  • Used for data sharing and row/column-level privacy; slightly slower due to fewer optimizations.

• Materialized view:

  • Physically persists results and is auto-maintained as base data changes.

  • Speeds up expensive queries but is limited (single table, no joins) and costs storage + maintenance compute (Enterprise Edition).

• Quick rule: Standard for logic reuse, secure for protection/sharing, materialized for performance on heavy repeated queries.

Snowpipe is Snowflake's serverless, continuous ingestion service that loads files from a stage automatically as soon as they arrive, giving near-real-time availability instead of waiting for scheduled batch jobs.

• Event-driven loading:

  • Cloud storage notifications (e.g. S3 event, Azure Event Grid) trigger Snowpipe when new files land.

  • Alternatively, files are submitted via the Snowpipe REST API.

• Serverless and elastic:

  • It uses Snowflake-managed compute, not your virtual warehouse, so you don't size or run a warehouse.

  • Billed per-second by compute consumed plus a small per-file overhead.

• Advantages over batch loading:

  • Latency drops from hours to typically a minute or so, data is queryable soon after arrival.

  • No dedicated warehouse to keep running or schedule.

  • Automatically scales with incoming file volume.

• Defined with a pipe wrapping a COPY INTO statement.

Snowflake recommends compressing files to roughly 100-250 MB each so that loads parallelize efficiently across warehouse threads: files that are too large limit parallelism, and files that are too small waste per-file overhead.

• Target ~100-250 MB compressed per file: A warehouse loads files in parallel across its threads (8 per node); many appropriately sized files keep all threads busy.

• Avoid very large files: A single huge file can't be split across threads, so one thread does all the work and a failure means reprocessing the whole file.

• Avoid too many tiny files: Per-file management overhead dominates, and with Snowpipe the per-file charge makes tiny files expensive.

• Why it matters:

  • Performance: right-sized files maximize parallelism and throughput.

  • Cost: fewer wasted compute seconds and lower per-file overhead.

A stage is a location where data files sit before loading or after unloading. Snowflake has internal stages (managed by Snowflake) in three flavors: user, table, and named; plus external stages that point to your own cloud bucket, accessed securely via a storage integration.

• Internal stages:

  1. User stage (@~): auto-provisioned per user, good for files only one user loads.

  2. Table stage (@%table): tied to one table, for files loaded into that table.

  3. Named stage (@my_stage): a database object you create and share, most flexible and reusable.

• External stage: References a path in S3, Azure Blob, or GCS where the files already live.

• Storage integration:

  • A named object holding a cloud IAM identity (e.g. an AWS role) so credentials aren't embedded in SQL.

  • You grant that identity access to the bucket, then reference it via STORAGE_INTEGRATION when creating the external stage.

  • Centralizes and secures credential management, avoiding hardcoded keys.

COPY INTO <table> is Snowflake's bulk loader that reads files from a stage into a table using a virtual warehouse. It is idempotent by default: it tracks which files it has already loaded and skips them, so re-running the same command won't duplicate data.

• Basic operation:

  • Reads staged files, applies a FILE_FORMAT, and inserts rows into the target table.

  • Runs on your warehouse, so you control compute size and parallelism.

• Load metadata and idempotency:

  • Snowflake records each loaded file's name and ETag in the table's load history (retained ~64 days).

  • On re-run it skips files already loaded (unless the file changed), preventing duplicates.

  • Use FORCE = TRUE to reload files regardless, or PURGE to delete files after load.

• Error handling:

  • ON_ERROR controls behavior (ABORT_STATEMENT, CONTINUE, SKIP_FILE).

  • VALIDATION_MODE lets you dry-run to catch errors without loading.

Both load staged files into tables using the same underlying logic, but COPY INTO is a manual/scheduled batch command running on your warehouse, while Snowpipe is an automated, serverless, event-driven service for continuous loading.

• Compute:

  • COPY INTO uses your virtual warehouse (you pay for it while running).

  • Snowpipe uses Snowflake-managed serverless compute, billed per-second plus per-file.

• Triggering:

  • COPY INTO is run explicitly or on a schedule (task).

  • Snowpipe auto-triggers on cloud storage events or via REST API.

• Latency and pattern:

  • COPY INTO suits large scheduled batches.

  • Snowpipe suits continuous, near-real-time micro-batches.

• Both track loaded files to avoid duplicate loads.

Serverless features are billed by Snowflake-managed compute that you don't provision: you pay per-second for the exact resources the background service uses, at a credit rate that differs from your own warehouses, rather than paying for a warehouse you start and stop.

• Virtual warehouse compute:

  • You size and control the warehouse; billed per-second (60s minimum) while it runs, whether or not it's fully utilized.

  • 1 credit/hour for XS, doubling per size.

• Serverless compute:

  • Snowflake provisions and scales the compute automatically; you never start/stop it.

  • Billed only for actual work done, often at a different (frequently higher) credit multiplier than warehouse credits.

• Examples:

  • Snowpipe: per-file ingest cost plus compute for the load.

  • Automatic clustering: charged for the re-clustering (reshuffling) work as it maintains sort order.

  • Search optimization: charged for building and maintaining the search access path.

• Practical implication: Serverless costs appear separately and accrue in the background, so monitor them via ACCOUNT_USAGE metering views rather than warehouse credit reports.

Clustering is how Snowflake co-locates related data within micro-partitions so pruning can skip irrelevant partitions. A clustering key is an explicitly defined set of columns Snowflake maintains sort order on, useful for very large tables whose natural load order doesn't match query filters.

• Micro-partitions and natural clustering:

  • Data is stored in immutable ~16MB compressed micro-partitions, each with min/max metadata per column.

  • Queries prune partitions whose min/max ranges can't match the filter; data is naturally clustered by load order.

• When to define a clustering key:

  • Multi-terabyte tables where query predicates don't align with load order, causing poor pruning.

  • Columns frequently used in WHERE, JOIN, or range filters.

• Choosing the key:

  • Order columns from lowest to highest cardinality; avoid extremely high-cardinality keys that fragment partitions.

  • Check effectiveness with SYSTEM$CLUSTERING_INFORMATION.

• Trade-off: Automatic clustering runs in the background and incurs serverless cost; only worth it when improved pruning outweighs maintenance cost.

Snowflake uses three cache layers that each cut work at a different stage: the metadata cache avoids scanning for statistics, the warehouse (local disk) cache reuses data already read from storage, and the result cache returns a whole prior result without any compute.

• Result cache (in the cloud services layer):

  • Stores the full result set of a query for 24 hours (extended up to 31 days on reuse).

  • Returns instantly with no warehouse needed, hence zero compute cost, if the query and data are unchanged.

• Local disk / warehouse cache:

  • Each warehouse caches micro-partition data on its SSDs as it reads from remote storage.

  • Subsequent queries touching the same data avoid re-fetching from storage; cleared when the warehouse suspends.

• Metadata cache (in cloud services):

  • Holds table statistics: row counts, min/max per column, partition counts.

  • Lets queries like COUNT(*) or MIN/MAX resolve from metadata alone, without a warehouse.

• Cost angle: Result and metadata caches use no warehouse credits; the warehouse cache reduces I/O time, so keeping a warehouse warm can improve repeated-query performance.

Result caching stores the complete output of every query in the cloud services layer, so an identical query re-run against unchanged data returns the stored result immediately with no warehouse compute and no credits consumed.

• How reuse is decided:

  • The new query text must match syntactically (after normalization) and reference the same objects.

  • Underlying table data must not have changed since the result was cached.

  • The querying role must have the same privileges on the referenced objects.

• Lifetime: Persists 24 hours; each reuse resets the clock, up to a maximum of 31 days.

• Performance and cost effect:

  • Served from cloud services, so no warehouse needs to be running and no credits are billed.

  • Ideal for dashboards and repeated reporting queries.

• Control: Set USE_CACHED_RESULT = FALSE to force fresh execution (e.g. for benchmarking).

Automatic clustering is a serverless background service that continuously maintains the sort order of a table defined with a clustering key: Snowflake monitors how well-clustered the data is and re-writes micro-partitions as needed, with no manual reclustering and no dedicated warehouse.

• How it works:

  • Once you set CLUSTER BY, Snowflake tracks clustering depth and identifies poorly-clustered partitions.

  • It reorganizes those micro-partitions in the background as DML changes the table, so pruning stays effective over time.

  • It works incrementally, only touching partitions that need it, rather than rewriting the whole table.

• Benefits: Sustained query pruning on large, frequently-updated tables without manual maintenance windows.

• Cost implications:

  • Billed as serverless compute for the reclustering work, which can be significant on tables with high churn.

  • Tables with heavy random DML re-cluster constantly, so weigh maintenance cost against query savings; can be suspended with ALTER TABLE ... SUSPEND RECLUSTER.

The result cache is bypassed whenever Snowflake can't guarantee the stored result is still valid: any change to the query text, the underlying data, the environment, or the requesting role's privileges forces a fresh execution.

• Query text changes: Any difference after normalization (whitespace-insensitive but semantically distinct) misses the cache.

• Underlying data changed: Any DML (INSERT, UPDATE, DELETE), or reclustering/DDL on referenced tables invalidates it.

• Non-deterministic functions: Queries using functions like CURRENT_TIMESTAMP() or RANDOM() are not cached.

• Privilege or role differences: The running role must have sufficient (matching) access to all referenced objects.

• Expiry and settings: Result older than 24 hours (max 31 days), or USE_CACHED_RESULT = FALSE.

• External / dynamic data: Queries on external tables or that use external functions generally don't reuse results.

Resource Monitors track credit consumption against a defined quota over an interval and can notify, suspend, or forcibly kill warehouses when thresholds are crossed. They are the primary guardrail against runaway compute spend.

• What they track:

  • A credit quota over a schedule (DAILY, WEEKLY, MONTHLY, etc.) with optional reset frequency.

  • Scope: assigned to the whole account or to specific warehouses.

• Threshold actions (triggers):

  • NOTIFY: send an alert only, no interruption.

  • SUSPEND: suspend the warehouse after running queries finish.

  • SUSPEND_IMMEDIATE: cancel running queries and suspend at once.

• Important limits:

  • They govern virtual warehouse credits only, not storage or most cloud-services/serverless costs.

  • Notifications require enabling them per user in preferences.

The cloud services layer is Snowflake's "brain": it coordinates the whole system, handling everything that isn't raw compute or storage. It consumes credits, but Snowflake gives a daily allowance of 10% of that day's compute credits free, so most accounts pay little or nothing for it.

• What it handles:

  • Authentication and access control; query parsing and optimization; metadata management.

  • Infrastructure management, transaction coordination, and the result cache.

• How billing works:

  • Cloud services usage is charged only for the portion exceeding 10% of the day's total compute (warehouse) credits.

  • Example: burn 100 compute credits and 8 cloud-services credits, you pay 0 for cloud services; burn 15, you pay only for 5.

• Practical note: Metadata-heavy patterns (huge numbers of tiny queries, frequent SHOW/INFORMATION_SCHEMA calls, or many small DDLs) can push you past the 10% allowance and incur real charges.

They are two column- and row-level security features that filter or obscure data at query time based on the querying role, without duplicating data: masking policies control what values in a column a user sees, while row access policies control which rows a user can see.

• Masking policies (column-level):

  • A schema-level object attached to a column that returns either the real value or a masked value depending on context.

  • Logic typically checks CURRENT_ROLE() so privileged roles see plaintext and others see redacted/hashed data.

  • Supports dynamic data masking and conditional masking (masking based on another column's value).

• Row access policies (row-level):

  • Attached to a table/view; returns a boolean per row deciding visibility, often via a mapping table of role-to-region/entitlement.

  • One policy can be applied consistently across many tables sharing the same access column.

• Why they help with sensitive data:

  • Policy-based, centralized, and reusable: security logic lives in one place instead of scattered per-view.

  • Applied at query runtime, so the same table serves different audiences (PII vs. non-PII) without copies.

Snowflake ships with system-defined roles that form a hierarchy for separating administration duties; ACCOUNTADMIN sits at the top with full control, so it should be reserved for rare, high-privilege tasks rather than daily work.

• ACCOUNTADMIN: top-level superuser: Encapsulates SYSADMIN and SECURITYADMIN; can view billing, manage account parameters, everything.

• SECURITYADMIN: manages security globally: Can create/manage roles and grants account-wide; inherits USERADMIN.

• USERADMIN: manages users and roles: Creates users and roles (via CREATE USER/CREATE ROLE privileges) but not broad grants.

• SYSADMIN: manages objects: Creates and owns databases, warehouses, schemas; custom roles should be granted up to it so it can manage all objects.

• PUBLIC: default role every user has: Grants here are visible to everyone; use sparingly.

• Why not work daily as ACCOUNTADMIN:

  • Least privilege: minimizes blast radius of mistakes and compromised credentials.

  • Objects created by it are owned by it, complicating access for other roles.

  • Best practice: enforce MFA on it, grant to few people, and do routine work under SYSADMIN or custom roles.

Snowflake supports several authentication methods (password, key-pair, federated SSO, and MFA) and complements them with network policies that restrict which IP addresses can connect at all.

• Key-pair authentication:

  • Uses an RSA public/private key pair instead of a password; ideal for service accounts and automated/programmatic access.

  • Public key is registered on the user; supports rotation with two keys.

• SSO (federated / SAML, OAuth): Delegates login to an IdP (Okta, Azure AD) via SAML 2.0; OAuth supports token-based access for apps and BI tools.

• MFA: Multi-factor via Duo, layered on top of password login; strongly recommended for ACCOUNTADMIN and human users.

• Network policies:

  • Allow/block lists of IP ranges (ALLOWED_IP_LIST, BLOCKED_IP_LIST) applied at account or user level.

  • Enforce that connections only come from trusted networks (e.g., corporate VPN), independent of authentication method.

Streams and Tasks combine to build incremental, automated pipelines: a Stream captures the changes (CDC) made to a source table since it was last read, and a Task runs SQL on a schedule (or trigger) to consume those changes and push them downstream.

• Streams provide the CDC delta:

  • A stream on a table exposes only rows that changed, so you process deltas instead of full scans.

  • Ideal for incrementally loading a target: merge only new/changed rows.

• Tasks provide orchestration:

  • Run a statement on a CRON/interval schedule or when triggered; can chain into DAGs with AFTER dependencies.

  • Use WHEN SYSTEM$STREAM_HAS_DATA() so the task only spends compute when the stream actually has changes.

• Typical pattern:

  1. Data lands in a raw/staging table.

  2. A stream tracks its changes.

  3. A scheduled task runs a MERGE from the stream into the curated table; reading the stream in a committed statement advances its offset.

A Stream is a change-tracking object that records a table's DML changes as an offset (a bookmark) rather than storing the data itself; when queried it returns the net rows changed since the offset, along with metadata columns describing what happened. Consuming the stream in a committed DML statement advances the offset.

• Metadata columns it exposes:

  • METADATA$ACTION: INSERT or DELETE.

  • METADATA$ISUPDATE: true when a row change is part of an UPDATE, represented as a paired DELETE + INSERT.

  • METADATA$ROW_ID: a unique row identifier for tracking.

• Stream types:

  • Standard (delta): tracks inserts, updates, and deletes and returns the net change between two offsets.

  • Append-only: tracks only inserts (ignores updates/deletes); cheaper and useful for insert-only ingestion.

  • Insert-only: a variant for external tables/directory tables that only surfaces new rows.

• Consumption contract:

  • The offset advances only when the stream is read inside a successfully committed DML statement (e.g., an INSERT ... SELECT FROM stream or MERGE).

  • A plain SELECT does not consume it, so you can preview changes safely.

  • Streams have a staleness limit tied to the source table's retention; if not consumed in time they can go stale and must be recreated.

  • Multiple independent streams can track the same table, each with its own offset.

Tasks are Snowflake's built-in scheduler for running SQL (or calls to stored procedures) on a schedule or in response to a stream, letting you build automated pipelines entirely inside Snowflake.

• What a Task is:

  • A schedulable object that executes one SQL statement or a CALL to a procedure, triggered by a SCHEDULE (cron or interval) or by a predecessor.

  • Often paired with a stream via WHEN SYSTEM$STREAM_HAS_DATA() so it only runs when new data exists.

• Orchestrating trees/DAGs:

  • Use AFTER <predecessor> to chain tasks; one root task runs on a schedule and children run when parents finish.

  • Modern accounts support finalizer tasks and multiple predecessors, forming true DAGs, not just linear chains.

  • Resume tasks with ALTER TASK ... RESUME (they start suspended); resume children before the root.

• Serverless vs warehouse-backed:

  • Warehouse-backed: you name a virtual warehouse; you pay for that warehouse's uptime and control its size.

  • Serverless: omit the warehouse; Snowflake provisions and auto-sizes compute, billed per-second for actual usage.

  • Serverless suits short, frequent, or unpredictable tasks (no idle warehouse cost); dedicated warehouses suit heavy or highly customized workloads.

A Stream tracks what changed (change data capture), while a Task decides when to run logic: they are complementary, not alternatives. Streams answer "which rows are new/updated/deleted?"; tasks answer "when and what SQL runs?"

• Stream = change tracking:

  • A logical object over a table that records DML changes since the last time it was consumed, exposing METADATA$ACTION and METADATA$ISUPDATE.

  • Its offset advances only when read inside a DML transaction that commits.

• Task = scheduled execution: Runs SQL on a schedule or after another task; has no knowledge of change data by itself.

• They combine for pipelines: A task periodically consumes a stream to process only changed rows, e.g. MERGE into a target using WHEN SYSTEM$STREAM_HAS_DATA() as its condition.

Both expose metadata, but INFORMATION_SCHEMA gives real-time, short-window views per database, while ACCOUNT_USAGE gives account-wide historical data with some latency and long retention, making it the go-to for auditing.

• Purpose:

  • INFORMATION_SCHEMA: current object metadata and recent activity, scoped to a single database (plus table functions like QUERY_HISTORY()).

  • ACCOUNT_USAGE (in the SNOWFLAKE shared DB): account-wide historical usage, cost, security, and audit data.

• Latency:

  • INFORMATION_SCHEMA: essentially real-time / no lag.

  • ACCOUNT_USAGE: latency of minutes to a few hours depending on the view.

• Retention:

  • INFORMATION_SCHEMA: short window (typically 7 days to 6 months depending on the function/view).

  • ACCOUNT_USAGE: long history, generally up to 365 days.

• Rule of thumb: Need it now and locally: INFORMATION_SCHEMA. Need long-term account audit/cost analysis: ACCOUNT_USAGE (accepting lag) and note it also shows dropped objects.

Spilling means an operation needed more memory than the warehouse had, so it wrote intermediate data to disk: local storage (fast SSD on the warehouse nodes) first, then remote storage (object storage, much slower) when even local disk is exhausted. It's a strong signal the warehouse is undersized for the workload.

• What each level means:

  • Local spilling: memory exceeded, data spilled to node SSD; a mild-to-moderate slowdown.

  • Remote spilling: local disk also exhausted, spilling to remote storage; severe performance hit and the clearest sign of undersizing.

• Common causes: Large sorts, aggregations, or hash joins over big or exploded datasets on a small warehouse.

• How to resolve:

  • Size up the warehouse: more memory per operation is the most direct fix.

  • Reduce data processed: filter earlier, select fewer columns, pre-aggregate, and fix exploding joins.

  • Improve pruning so less data is read into memory in the first place.

  • Break the query into smaller steps if a single operator is disproportionately large.

Secure data sharing lets a provider grant another account live, read-only access to selected objects without copying or moving data: consumers query the shared data directly through Snowflake's Share object, and the underlying storage is never duplicated.

• It uses metadata, not data copies:

  • A provider creates a SHARE and grants privileges on databases, schemas, tables, and secure views to it; consumers mount it as a read-only database.

  • No storage is duplicated: consumers read the provider's data, so there is only one copy.

• Always live and current: Changes on the provider side are instantly visible to consumers with no ETL, pipelines, or refresh lag.

• Consumer pays for their own compute: Queries run on the consumer's own virtual warehouse, so the provider isn't billed for consumer queries.

• Fine-grained and governed: Use secure views (SECURE VIEW) and row/column policies to expose only what should be shared.

• Main advantages:

  1. No data movement or duplication, so lower cost and no stale copies.

  2. Instant access and real-time freshness.

  3. Secure, revocable, and auditable access controlled by the provider.

• Caveat: within the same region/cloud sharing is instant; cross-region or cross-cloud sharing requires replication first.

A reader account is a special, lightweight Snowflake account that a provider creates and owns on behalf of a consumer who has no Snowflake account of their own: the provider manages and pays for it, and the consumer uses it purely to query shared data.

• Created and owned by the provider: Created via CREATE MANAGED ACCOUNT; it belongs to and is administered by the provider account.

• Consumer accesses shared data only: The reader can query the shares the provider grants but cannot load its own data, do DML, or share with others.

• Provider bears the cost: Compute (warehouses) in the reader account is billed to the provider, so watch usage with resource monitors.

• Use case:

  • Ideal for sharing with partners or customers who aren't Snowflake customers, without them signing up.

  • If the consumer already has a Snowflake account, share directly instead: it's simpler and they pay their own compute.

Choose based on what you need: stored procedures for procedural, multi-statement logic with side effects; UDFs for reusable computed values inside queries; external functions when the logic must live outside Snowflake in an external service or API.

• Stored procedures:

  • For orchestration and administrative/DML workflows: loops, branching, transactions, and running multiple SQL statements.

  • Can perform side effects (INSERT, UPDATE, DDL); called with CALL, not embedded in a query's SELECT.

  • Written in SQL (Snowflake Scripting), JavaScript, Python, Java, or Scala.

• UDFs (user-defined functions):

  • For computing and returning a value (scalar) or a set of rows (table/UDTF) used directly inside a query.

  • Deterministic, no side effects, and can be inlined per row: ideal for reusable business calculations.

• External functions:

  • For calling remote logic via an API gateway: ML model scoring, third-party enrichment, or code that can't run in Snowflake.

  • Higher latency and require external network/API configuration, so use only when the capability isn't available natively.

• Rule of thumb: Need a value in a query, use a UDF; need a process/side effects, use a procedure; need outside code, use an external function.