Snowflake Study Guide

A cloud data platform delivered as a fully managed service on AWS, Azure and Google Cloud. It started as a SQL data warehouse and now also covers data lakes (Iceberg tables), data engineering, data sharing, applications and AI. There is no hardware or software to install, tune or patch.

Database storage (data stored in compressed, columnar micro-partitions in cloud object storage), query processing (virtual warehouses: independent compute clusters), and cloud services (authentication, metadata, query optimization, access control, transactions and infrastructure management).

Data is stored once in central storage, and any number of virtual warehouses can read it independently. You scale and pay for compute and storage separately, and workloads (loading, BI, data science) don't compete for the same resources.

Like shared-disk, all compute nodes see one central copy of the data. Like shared-nothing (MPP), each warehouse processes its part of the data on its own nodes with local caching, which gives scalable performance.

A named cluster of compute resources that executes queries and DML and loads data. It has a size, can be suspended and resumed, and consumes credits only while running.

It coordinates everything: authentication and access control, the metadata store (tables, micro-partition statistics, history), query parsing and optimization, transaction management, result caching and security. It is billed only if its usage exceeds 10% of the daily warehouse compute.

An account is one Snowflake deployment in one cloud region, with its own users, databases and warehouses, reached through a URL. An organization groups several accounts of the same customer for billing, replication and account management (by ORGADMIN).

Organization → account → database → schema → objects (tables, views, stages, file formats, sequences, streams, tasks, pipes, functions, procedures). Warehouses, users, roles, resource monitors and integrations are account-level objects.

Standard; Enterprise (adds multi-cluster warehouses, Time Travel up to 90 days, materialized views, masking and row access policies, search optimization); Business Critical (adds stronger security and compliance such as HIPAA and PCI support, Tri-Secret Secure, private connectivity, failover); and Virtual Private Snowflake (a fully isolated environment).

The unit for billing compute. A warehouse consumes credits per second (with a 60-second minimum each time it resumes) according to its size. Serverless features (Snowpipe, automatic clustering, search optimization, serverless tasks) also consume credits. Storage is billed separately per TB per month.

Each size step doubles the compute and the credit rate: X-Small uses 1 credit per hour, Small 2, Medium 4, Large 8, and so on up to 6X-Large. A query that parallelizes well runs about twice as fast on the next size, so the cost can stay similar while latency drops.

AWS, Azure and Google Cloud, in many regions. An account lives in one region of one cloud; replication and data sharing across regions and clouds connect accounts.

Through Snowsight (the web UI), the Snowflake CLI and SnowSQL, drivers (JDBC, ODBC, Python connector, Go, Node.js, .NET, Spark and Kafka connectors), the SQL REST API, and partner tools (BI, ETL, dbt).

Snowflake's web interface for writing SQL and Python worksheets, notebooks, dashboards, exploring data, monitoring queries and costs, and administering the account.

Snowflake's built-in governance and catalog capabilities: access control, classification of sensitive data, masking and row access policies, tags, lineage, data quality monitoring, auditing and discovery, applied across tables, Iceberg tables and shared data.

Traditional MPP warehouses couple storage and compute on the same nodes, so scaling means redistributing data, and all workloads share one cluster. Snowflake keeps storage central and lets you start independent warehouses in seconds, with no indexes, distribution keys or vacuuming to manage.

The account identifier (organization name plus account name, or the legacy locator with region) forms the URL that clients connect to, for example myorg-myaccount.snowflakecomputing.com.

The current role, warehouse, database and schema of a session (USE ROLE, USE WAREHOUSE, USE DATABASE, USE SCHEMA). Queries run with the current role's privileges on the current warehouse, and unqualified names resolve against the current database and schema.

The physical storage unit of a Snowflake table: an immutable file of 50 to 500 MB of uncompressed data (much smaller compressed), stored in columnar format. Snowflake creates them automatically as data is loaded, in the order it arrives.

The range of values (min and max) of each column, the number of distinct values, null counts and other statistics. The optimizer uses them to skip micro-partitions that can't contain matching rows.

Skipping micro-partitions during a scan because their metadata shows they can't match the filter. Good pruning is the main reason queries on huge tables can be fast. It appears in the query profile as "partitions scanned" versus "partitions total".

Immutability makes concurrency, Time Travel and cloning simple. An UPDATE or DELETE writes new micro-partitions containing the changed rows and marks the old ones as no longer current; the old ones are kept for Time Travel and Fail-safe.

How well rows with similar values of a column are co-located in the same micro-partitions. Data loaded in date order is naturally well clustered by date. Clustering degrades when DML scatters values over many partitions.

One or more columns or expressions declared on a large table (CLUSTER BY (event_date, customer_id)) so that the Automatic Clustering service reorganizes micro-partitions in the background to keep similar values together, improving pruning for filters on those columns.

On very large tables (multi-terabyte) where queries filter on columns that are not naturally clustered and the query profile shows poor pruning. Clustering costs credits for reclustering, so it isn't worth it for small or frequently fully rewritten tables.

With SYSTEM$CLUSTERING_INFORMATION('table', '(col)'), which reports the average depth and overlap of micro-partitions. Lower depth means better clustering.

Automatically, per column, with the best encoding for the data. You don't choose compression; storage is billed on the compressed size.

Permanent tables (Time Travel and 7-day Fail-safe), transient tables (Time Travel of 0 or 1 day, no Fail-safe, cheaper for data that can be rebuilt), temporary tables (exist only for the session), external tables (read files in a stage), Iceberg tables, hybrid tables (row-oriented, for transactional workloads) and dynamic tables.

Tables stored in Apache Iceberg format in your own cloud storage (an external volume). With Snowflake as the catalog they behave almost like native tables; with an external catalog (AWS Glue, Snowflake Open Catalog/Apache Polaris and others) Snowflake can read and, in some cases, write them. Other engines (Spark, Trino) can work on the same data.

Tables optimized for low-latency, high-concurrency single-row reads and writes, with enforced primary keys and indexes, that can be joined with analytic tables in the same query. They target light transactional workloads (Unistore).

A read-only table whose data stays in files in an external stage (S3, GCS, Azure). Snowflake stores file-level metadata, and you query the files with SQL. It's slower than native tables; Iceberg tables are usually preferred now.

On the average compressed bytes per month across tables, Time Travel and Fail-safe data, and staged files, at a rate per TB depending on capacity or on-demand pricing and region.

It pauses automatic clustering on a table, stopping reclustering costs, for example during a large backfill; RESUME RECLUSTER turns it back on.

Natural clustering comes from the order in which data was loaded and costs nothing; a clustering key asks Snowflake to maintain an order through background reclustering, which costs credits but keeps pruning good despite DML.

In ACCOUNT_USAGE.TABLE_STORAGE_METRICS, which reports active bytes, Time Travel bytes, Fail-safe bytes and bytes retained for clones per table.

It stops consuming credits and its local cache is dropped. With auto-resume, the next query starts it again in a second or two.

Settings that suspend a warehouse after a number of seconds of inactivity and resume it automatically when a query arrives. A short auto-suspend (for example 60 seconds) saves credits on intermittent workloads; a longer one keeps the cache warm for BI.

An Enterprise feature where a warehouse can run several clusters of the same size, between a minimum and maximum, to absorb concurrency. Extra clusters start when queries queue and stop when load drops.

Scaling up (a bigger warehouse) makes individual complex queries faster and reduces spilling. Scaling out (more clusters in a multi-cluster warehouse) serves more concurrent users without queuing; it doesn't make a single query faster.

Standard (starts clusters quickly to avoid queuing, favoring performance) and Economy (starts a cluster only if there's enough load to keep it busy for several minutes, favoring cost).

With different warehouses per workload (ingestion, transformation, BI, data science, ad hoc), each sized and suspended appropriately, so they never compete for compute and their costs are visible separately.

Queries wait when a warehouse is at its concurrency limit (MAX_CONCURRENCY_LEVEL, 8 by default). Fixes: multi-cluster warehouses, separate warehouses for different workloads, faster queries, or a larger warehouse.

A warehouse with much more memory per node, for memory-heavy Snowpark workloads such as ML training or large UDF processing.

A serverless feature that offloads parts of large scans and filters in eligible queries to shared compute, so outlier queries finish faster without resizing the whole warehouse. It's enabled per warehouse with a scale factor limit.

A newer generation of standard warehouses with faster hardware and engine improvements, especially for DML and large scans, billed at a somewhat higher credit rate. They can be chosen per warehouse.

STATEMENT_TIMEOUT_IN_SECONDS, set on a warehouse, user or session, cancels queries running longer than the limit. Setting it prevents runaway queries from burning credits for hours.

Resizing affects new queries; running queries finish on the existing resources. Scaling up provisions new resources in the background, so queued and new queries benefit once they're ready.

The number of concurrent SQL statements a warehouse cluster runs before queuing more. Lowering it gives each query more resources; raising it allows more parallel queries at the risk of slowing each one.

Start small (X-Small or Small) for development and simple loads, test representative queries, and size up only when queries spill, run too long, or don't meet the target time. Measure cost per run, not just duration.

The result cache (in the cloud services layer: query results reused for 24 hours if the query and data haven't changed), the warehouse (local disk) cache (micro-partition data cached on the warehouse's SSD while it runs), and the metadata cache (statistics that answer some queries like COUNT(*) or MIN without a warehouse).

When the same query text runs again, the underlying data hasn't changed, the user has the required privileges, and the query doesn't use non-deterministic functions such as CURRENT_TIMESTAMP(). Each reuse extends retention, up to 31 days. It costs no compute.

Suspending drops the local cache, so the next queries read from remote storage and are slower. For dashboards that query the same data repeatedly, a longer auto-suspend can be faster and not much more expensive.

ALTER SESSION SET USE_CACHED_RESULT = FALSE; so benchmark queries actually run.

Yes, if the other user's role has the required privileges on the underlying objects and the query text is identical; results are not tied to the original user.

The data changed, the query text differs (even by whitespace or case in some cases), it uses non-deterministic functions, it queries external functions or some table functions, the result is older than its retention, or caching is disabled for the session.

Bulk loading with COPY INTO from stages, continuous loading with Snowpipe (files) and Snowpipe Streaming (rows), the Kafka connector, Openflow and partner ETL/ELT tools, INSERT statements, external and Iceberg tables for querying in place, and data sharing for data from other accounts.

A location for files to load or unload. Internal stages are inside Snowflake: user stages (@~), table stages (@%table) and named stages. External stages point to S3, GCS or Azure containers, usually through a storage integration.

An account object that holds a cloud identity (for AWS, an IAM role that Snowflake assumes) and the allowed storage locations, so external stages access buckets without storing credentials in SQL.

CREATE STORAGE INTEGRATION s3_lake
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-lake'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-lake/raw/');

CREATE STAGE raw_stage URL = 's3://my-lake/raw/' STORAGE_INTEGRATION = s3_lake;

A named definition of how to parse files: type (CSV, JSON, Avro, ORC, Parquet, XML), delimiters, header handling, null strings, compression, date formats. Stages and COPY statements reference it.

It loads files from a stage into a table in parallel, using a file format, with options for error handling and transformations in a SELECT. It keeps 64 days of load metadata per table, so files already loaded are skipped by default.

COPY INTO raw.orders
FROM @raw_stage/orders/
FILE_FORMAT = (TYPE = 'PARQUET')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
ON_ERROR = 'ABORT_STATEMENT';

ABORT_STATEMENT (default for bulk loads), CONTINUE (skip bad rows), SKIP_FILE, SKIP_FILE_<n> or SKIP_FILE_<n>% (skip a file with too many errors). Use VALIDATION_MODE = RETURN_ERRORS to test a load without loading, and the VALIDATE function to see errors from a previous load.

Roughly 100–250 MB compressed per file. Many tiny files add overhead; a single huge file can't be loaded in parallel. Split large files and batch small ones.

A serverless service that loads files continuously as they arrive in a stage, using a COPY statement defined in a pipe. With auto-ingest, cloud event notifications (S3 events via SQS, Event Grid, Pub/Sub) trigger it; otherwise you call its REST API. Latency is typically around a minute.

CREATE PIPE raw.orders_pipe AUTO_INGEST = TRUE AS
COPY INTO raw.orders FROM @raw_stage/orders/ FILE_FORMAT = (TYPE = 'JSON');

On serverless compute plus a small per-file overhead (in recent pricing, a fixed amount per GB ingested). Very small files make the overhead dominate, so producers should batch data into files of reasonable size, ideally sent every minute or so rather than per event.

An API (through the Java or Python SDK, the Kafka connector or Openflow) that writes rows directly into tables with seconds of latency, without staging files. It suits event streams; its high-performance architecture bills by data volume.

With COPY INTO @stage/path/ FROM (SELECT ...), writing CSV, JSON or Parquet files to an internal or external stage, with options for partitioning (PARTITION BY), file size and headers. Then GET downloads files from internal stages.

Client commands (SnowSQL, CLI, drivers) to upload local files to an internal stage (PUT, which compresses and encrypts them) and download files from it (GET).

A function that reads staged Parquet, Avro, ORC, JSON or CSV files and returns column names and types; combined with CREATE TABLE … USING TEMPLATE, it creates a table from files' structure. Tables can also enable schema evolution so new columns are added during loads.

FORCE = TRUE loads files again even if the load metadata says they were already loaded, which can create duplicates; it's used deliberately for reloads after truncating a table.

Load metadata expires after 64 days, so for older files Snowflake may not know whether they were loaded. LOAD_UNCERTAIN_FILES = TRUE loads them anyway; to avoid ambiguity, move or delete files once loaded, or use Snowpipe, which keeps 14 days of history per pipe.

With a SELECT over the staged files in the COPY statement: reorder or omit columns, cast types, use $1, $2 positions or $1:field for semi-structured data, and add METADATA$FILENAME or METADATA$FILE_ROW_NUMBER for lineage.

SELECT $1, $2, METADATA$FILENAME FROM @stage/path (FILE_FORMAT => 'my_csv'), which is useful to inspect files before loading.

Snowflake's managed data integration service, based on Apache NiFi, with connectors for databases, SaaS apps and streams, deployed in Snowflake or in your own cloud (bring your own cloud), to ingest structured and unstructured data.

With a CDC tool (Openflow connectors, Debezium with Kafka and the Snowflake Kafka connector, Fivetran, AWS DMS writing to S3 plus Snowpipe) landing change events in raw tables, then a MERGE from a stream or a dynamic table to apply them to the current-state table.

A type that can hold any JSON-like value (object, array, string, number, boolean, null) up to a size limit. Snowflake stores frequently present keys in columnar form internally, so queries on them can still prune and run fast.

With path notation and casts:

SELECT payload:customer.id::NUMBER       AS customer_id,
       payload:items[0]:sku::STRING       AS first_sku
FROM raw.events
WHERE payload:type::STRING = 'purchase';

A table function that expands an array or object into one row per element, used with LATERAL to unnest nested data.

SELECT e.payload:order_id::STRING AS order_id, i.value:sku::STRING AS sku
FROM raw.events e, LATERAL FLATTEN(input => e.payload:items) i;

Semi-structured types for key-value objects and arrays. Functions like OBJECT_CONSTRUCT, ARRAY_AGG, ARRAY_CONTAINS and PARSE_JSON build and inspect them. Structured versions (OBJECT(name STRING, age NUMBER), ARRAY(NUMBER)) enforce element types.

Keep raw JSON in VARIANT in the landing layer for flexibility and auditability, then extract the fields that are queried often into typed columns in clean layers: typed columns are easier to use, document and govern, and they prune predictably.

Cast defensively with TRY_CAST or TRY_TO_NUMBER, which return NULL instead of failing, and check the type with TYPEOF() to quarantine unexpected values.

With OBJECT_CONSTRUCT(*) or OBJECT_CONSTRUCT('id', id, 'name', name) for each row, and ARRAY_AGG to group rows into arrays, useful for exporting nested documents or feeding APIs.

A missing key returns SQL NULL; a JSON null value is a VARIANT null, which is not the same as SQL NULL (IS_NULL_VALUE() tests it). Casting with ::STRING turns JSON null into SQL NULL.

With MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE in COPY, which maps file columns to table columns by name, or by selecting $1:column_name::type explicitly.

A clause that filters on window function results, like HAVING for aggregates. It's the idiomatic way to deduplicate:

SELECT *
FROM raw.customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

It inserts, updates or deletes rows in a target table based on a join with a source, in one atomic statement, typically used for upserts and CDC.

MERGE INTO dw.customers t
USING staging.customers s ON t.customer_id = s.customer_id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.email = s.email, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, email, updated_at)
  VALUES (s.customer_id, s.email, s.updated_at);

On standard tables, only NOT NULL is enforced. Primary, unique and foreign key constraints are informational: they document the model and can help BI tools and the optimizer (with RELY), but they don't prevent duplicates. Hybrid tables do enforce them.

Sequences generate unique numbers (seq.NEXTVAL), not necessarily gap-free. AUTOINCREMENT/IDENTITY columns use a sequence internally. Many warehouse designs prefer hash-based surrogate keys, which are deterministic across loads.

Reading a random fraction of a table (SELECT * FROM t SAMPLE (1) for 1% of rows, or SAMPLE BLOCK for micro-partitions), which is useful for quick exploration and testing on big tables.

Scalar or table functions written in SQL, JavaScript, Python, Java or Scala. Python UDFs run in a secure sandbox with packages from Anaconda or uploaded files. Vectorized Python UDFs process batches with pandas for better performance.

Procedures written in Snowflake Scripting (SQL), JavaScript, Python, Java or Scala, used for procedural logic and administration. They run with owner's rights (default) or caller's rights.

A procedural SQL extension with variables, IF, loops, cursors, exception handling and RETURN, usable in stored procedures and anonymous blocks.

ACID with the read committed isolation level. Each statement autocommits unless inside an explicit BEGIN … COMMIT. DDL commits implicitly. Concurrent DML on the same table can be blocked by locks until the other transaction finishes.

It restores a recently dropped table, schema or database from Time Travel, as long as it is within the retention period.

ALTER TABLE a SWAP WITH b exchanges two tables' names atomically, a common way to publish a rebuilt table without downtime for readers.

A statement that creates an object if it doesn't exist or alters it to match the definition if it does, keeping data and grants, useful for declarative deployments of tables and other objects.

Rotating rows into columns (for example months into columns) and columns back into rows. PIVOT supports dynamic value lists with ANY in recent versions.

Pattern matching over ordered rows (for example a sequence of events in a session, such as view → cart → purchase), returning matches with measures, without writing complex self-joins.

Access to historical data that was changed or deleted, for a retention period: 1 day by default, configurable up to 90 days on Enterprise for permanent tables. You can query, clone or restore data as of a time, an offset or a statement.

SELECT * FROM orders AT (OFFSET => -3600);
SELECT * FROM orders BEFORE (STATEMENT => '01b2c3d4-...');
CREATE TABLE orders_restored CLONE orders AT (TIMESTAMP => '2026-10-15 08:00'::TIMESTAMP_LTZ);

A 7-day period after Time Travel during which Snowflake can recover permanent table data for disaster recovery. Only Snowflake support can access it; you can't query it. It costs storage, which is why transient tables (no Fail-safe) are cheaper.

With DATA_RETENTION_TIME_IN_DAYS at the account, database, schema or table level. Long retention on tables with frequent updates increases storage costs considerably.

Creating a copy of a table, schema or database instantly with CLONE, without copying data: the clone shares the original's micro-partitions and only new changes consume storage. It's used for dev and test environments, backups before risky changes, and point-in-time snapshots.

The data and structure, and for databases and schemas the child objects. Privileges on the source object itself are not copied unless you use COPY GRANTS; privileges on child objects are cloned. Pipes referencing internal stages, and some other objects, are not cloned.

Clone the production database into a dev database (instantly, with no extra storage until changes are made), apply masking policies or a secure view layer if the data is sensitive, and give the developer a role on the clone.

The new table is separate; the dropped one stays in Time Travel and can be restored with UNDROP only after renaming the new table, since names must be unique.

Yes: CREATE TABLE t_clone CLONE t AT (TIMESTAMP => ...) or BEFORE (STATEMENT => ...), as long as it's within the retention period. It is a common way to recover from a bad change while keeping the current table for comparison.

An object that records the changes (inserts, updates, deletes) made to a table, view or external table since the stream was last consumed, exposing them with the columns METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID. Consuming it in a DML transaction advances its offset.

Standard (all changes, net of each other), append-only (only inserts, cheaper, for tables with insert-only loads), and insert-only for external and Iceberg tables.

A scheduled or triggered unit that runs a SQL statement, a stored procedure call or Snowflake Scripting, using a warehouse or serverless compute. Tasks can be chained into DAGs with AFTER and can run only when a stream has data with WHEN SYSTEM$STREAM_HAS_DATA(...).

CREATE TASK load_orders
  WAREHOUSE = transform_wh
  SCHEDULE = '15 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_stream')
AS
  INSERT INTO dw.orders SELECT * FROM raw.orders_stream WHERE METADATA$ACTION = 'INSERT';

ALTER TASK load_orders RESUME;

A task with no warehouse specified, where Snowflake manages compute size based on previous runs. It suits small, frequent tasks, because there is no warehouse minimum billing.

A task with no schedule that runs when its stream has new data, reacting quickly to changes without polling on a fixed interval.

A table defined by a query and a target lag (TARGET_LAG = '10 minutes'), which Snowflake keeps refreshed automatically, incrementally when possible. It replaces many stream-and-task pipelines with declarative SQL.

CREATE DYNAMIC TABLE dw.daily_sales
  TARGET_LAG = '30 minutes'
  WAREHOUSE = transform_wh
AS
SELECT order_date, SUM(amount) AS sales FROM dw.orders GROUP BY order_date;

Dynamic tables for declarative pipelines with joins and aggregations at a chosen freshness. Materialized views for speeding up repeated queries on a single table (no joins), always current. Streams and tasks for procedural logic, custom MERGE handling or side effects.

Incremental refresh processes only changes in sources since the last refresh; full refresh recomputes the whole query. Snowflake chooses incremental when the query supports it; some constructs (certain non-deterministic functions, some window patterns) force full refreshes, which cost more.

A precomputed result of a query on one table, maintained automatically and transparently by a background service (Enterprise). The optimizer can use it even when you query the base table. Maintenance costs credits, so use it for expensive, frequent queries on slowly changing tables.

It can become stale when its offset falls outside the source table's data retention period (Snowflake extends retention up to 14 days to prevent this for tables with streams). A stale stream must be recreated, and changes may need a full reload.

With TASK_HISTORY (information schema table function and account usage view), the task graph view in Snowsight, error notifications through a notification integration, and SYSTEM$TASK_DEPENDENTS_ENABLE to resume whole graphs.

A task attached to the root of a task graph that runs after all other tasks finish, whether they succeeded or failed, for cleanup or notifications.

Yes. A dynamic table can read other dynamic tables, forming a pipeline; TARGET_LAG = DOWNSTREAM means a table refreshes only as needed by the tables that depend on it.

Through role-based access control (RBAC) combined with discretionary access control: privileges are granted to roles, roles are granted to users and other roles, and each object has an owner role that controls it.

ORGADMIN (organization), ACCOUNTADMIN (top account role; use sparingly), SECURITYADMIN (manages grants and can manage users and roles), USERADMIN (creates users and roles), SYSADMIN (creates warehouses and databases; custom roles should roll up to it) and PUBLIC (granted to everyone).

Access roles holding privileges on objects (for example SALES_READ, SALES_WRITE), functional roles representing jobs or teams (ANALYST, DATA_ENGINEER) that are granted access roles, and functional roles rolled up to SYSADMIN so administrators can manage everything. Service accounts get their own functional roles.

Grants that apply automatically to objects created later in a schema or database: GRANT SELECT ON FUTURE TABLES IN SCHEMA sales.marts TO ROLE sales_read;. They avoid missing grants on new tables.

Roles scoped to a database, used to manage access inside it and especially to share fine-grained access through data sharing.

Schemas where only the schema owner (or roles with MANAGE GRANTS) can grant privileges on objects, not the object owners, which centralizes access control.

A schema-level object that rewrites a column's value at query time depending on the context, typically the role: analysts see ***@*** while a privileged role sees the real email. It's attached to columns directly or via tags (Enterprise).

CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE WHEN IS_ROLE_IN_SESSION('PII_READER') THEN val ELSE '***MASKED***' END;

ALTER TABLE crm.customers MODIFY COLUMN email SET MASKING POLICY mask_email;

A policy that returns whether a row is visible for the current context, attached to a table or view, often using a mapping table of roles to allowed regions. It filters rows transparently for every query.

Attaching a masking policy to a tag, so every column with that tag (for example pii = 'email') is masked automatically, including new columns as soon as they are tagged.

A feature that analyzes columns to detect sensitive data (emails, names, phone numbers, national IDs) and suggests or applies system tags, which can then drive masking policies.

With passwords plus MFA (which Snowflake is making mandatory for password sign-ins), SSO through SAML 2.0 identity providers (Okta, Entra ID), OAuth (Snowflake or external), key-pair authentication for services, workload identity federation, and programmatic access tokens. Service users should use key pairs or OAuth, not passwords.

Rules that allow or block client IP addresses (and network rules for private endpoints) at account or user level, so only corporate networks or specific services can connect.

Always at rest (AES-256) and in transit (TLS), with a hierarchical key model and automatic key rotation. Business Critical adds Tri-Secret Secure, combining a customer-managed key with Snowflake's key.

Views and functions whose definition is hidden from non-owners and that disable some optimizations that could leak data through query behavior. They are required for objects in data shares.

SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY records which columns each query read and wrote, which helps auditing, lineage and finding unused data.

The role owning an object has full control, including granting privileges on it and dropping it. Other privileges (SELECT, INSERT, USAGE, MONITOR, OPERATE) allow specific actions. Ownership can be transferred with GRANT OWNERSHIP.

It has unrestricted power, so mistakes or compromised sessions are catastrophic. Grant it to a few administrators with MFA, never use it for daily work or service accounts, and create objects with SYSADMIN or custom roles.

USE SECONDARY ROLES ALL lets a session use the privileges of all granted roles for queries (the primary role still owns created objects), which simplifies work for users with several roles.

Objects that allow UDFs and procedures to call external network endpoints, controlled by network rules listing allowed hosts and secrets objects for credentials.

Sharing live, read-only data from one account with another without copying it: the provider creates a share containing databases, schemas, tables and secure views, and the consumer creates a database from it. The consumer pays for the compute it uses; the provider pays for storage.

Yes, through a reader account that the provider creates and pays for, with limited capabilities.

Data must be replicated to an account in the consumer's region, or shared through listings with cross-cloud auto-fulfillment, which replicates the data automatically.

A catalog of listings where providers publish free or paid datasets, data services and Native Apps that consumers can get into their accounts directly.

Environments where several parties combine data for analysis (for example audience overlap) under policies that allow only approved queries and aggregated outputs, without exposing raw records to each other.

An application packaged with code and data that a provider distributes through the Marketplace and that runs inside the consumer's account, so the consumer's data doesn't leave it.

You can't modify it, clone it or re-share it (except through specific provider settings), and Time Travel isn't available on shared objects. You can query it, join it with your data and create views over it.

A packaged offering of a share or Native App with metadata, terms and usage analytics, published privately to chosen accounts or publicly on the Marketplace.

A set of libraries and runtimes for processing data in Snowflake with Python, Java or Scala. DataFrame operations are translated to SQL and executed in a warehouse, so data doesn't leave Snowflake.

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_

session = Session.builder.configs(connection_params).create()
daily = (session.table("dw.orders")
         .filter(col("status") == "paid")
         .group_by("order_date")
         .agg(sum_("amount").alias("sales")))
daily.write.save_as_table("dw.daily_sales", mode="overwrite")

A pandas-compatible API (Snowpark pandas, through Modin) that runs pandas code on Snowflake compute, so familiar pandas syntax scales to large tables.

Notebooks in Snowsight that mix SQL, Python and Markdown cells and run on a warehouse or on container compute, for exploration, data engineering and ML.

A managed container runtime in Snowflake for running any containerized app, service or ML model (including GPUs) next to the data, governed by Snowflake's security.

Snowflake's AI layer: LLM functions callable from SQL (AI_COMPLETE, AI_CLASSIFY, AI_EXTRACT, AI_SENTIMENT, summarization, translation), Cortex Search for retrieval, Cortex Analyst for natural-language questions over semantic models, and agents.

A way to build and host Streamlit data apps directly in Snowflake, running with the account's governance and sharing them through roles.

Snowflake ML includes a feature store, model registry, distributed training and inference on warehouses or containers, and ML observability, so models are built and served where the data is.

For logic that's easier in Python (complex string or business rules, ML feature engineering, reuse of Python libraries), for unit-testable transformation code, or for teams more comfortable with DataFrames. For straightforward transformations, SQL is often simpler and as fast.

From the Snowflake Anaconda channel (listed in PACKAGES), from wheels or zip files uploaded to a stage (IMPORTS), or through artifact repositories that pull from PyPI.

Open the Query Profile: look at the most expensive operators, partitions scanned versus total (pruning), bytes spilled to local and remote storage, exploding joins (output rows much greater than input), and queuing time. GET_QUERY_OPERATOR_STATS gives the same data in SQL.

When a warehouse doesn't have enough memory for an operation, it spills to local disk and then to remote storage, which is much slower. Fix it with a larger warehouse, by processing less data (better filters, pre-aggregation), or by splitting the query.

A join on a non-unique key, or missing join conditions, so each row matches many rows and the output multiplies. Check join keys for uniqueness and that every join has the full condition.

Filter on columns that correlate with load order (dates), add a clustering key on large tables filtered by other columns, avoid wrapping filter columns in functions that prevent pruning, and filter as early as possible.

An Enterprise feature that builds a persistent search access path for a table so point lookups and selective filters (equality, IN, substring and regex searches, VARIANT fields, geo) can skip most micro-partitions. It costs storage and maintenance credits.

Storage is columnar, so reading only needed columns scans far less data. SELECT * on wide tables reads every column and slows queries and BI extracts.

Don't: Snowflake is optimized for set-based operations. Batch rows into files and use COPY, use Snowpipe Streaming for continuous rows, or insert in large multi-row statements.

Yes, on initial loads of big tables: sorting by the main filter columns produces well-clustered micro-partitions from the start, improving pruning without paying for automatic clustering.

Single-row DML in loops, SELECT *, functions on filter columns, joins on mismatched types, huge IN lists, too-small warehouses that spill, too-large warehouses for trivial queries (cost), and complex views stacked many levels deep.

The logical plan of a query without running it: operators, join order and partitions to be scanned (from metadata), useful to check pruning before running an expensive query.

Check if deduplication is really needed (often a join fanned out), deduplicate earlier on fewer columns, use APPROX_COUNT_DISTINCT (HyperLogLog) when an estimate is enough, and use a larger warehouse if it spills.

APPROX_COUNT_DISTINCT, APPROX_PERCENTILE and APPROX_TOP_K give estimates with small error using far less memory and time, which suits dashboards and exploration on very large tables.

Warehouse compute (by far the largest for most customers), serverless features (Snowpipe, clustering, materialized views, search optimization, tasks, AI functions), storage (including Time Travel and Fail-safe), cloud services above 10% of compute, and data transfer out of the region.

An object that tracks credit usage for warehouses or the account over a period and can notify, suspend after running queries finish, or suspend immediately when thresholds are reached.

CREATE RESOURCE MONITOR monthly_bi WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = monthly_bi;

With SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY, QUERY_HISTORY (with QUERY_ATTRIBUTION_HISTORY for per-query cost), METERING_DAILY_HISTORY for serverless features, the cost management pages in Snowsight, budgets, and object tags or query tags for attribution.

A session parameter (ALTER SESSION SET QUERY_TAG = 'dbt:daily_sales') stored with each query in the history, used to attribute costs and find queries by job, tool or team.

Spending limits for the account or for groups of objects, with notifications when projected spend exceeds the limit, helping teams keep track of serverless and warehouse costs.

Right-size warehouses per workload, use short auto-suspend, avoid always-on warehouses, consolidate small jobs onto shared warehouses, use multi-cluster with the Economy policy where queuing is acceptable, set statement timeouts, optimize the most expensive queries, and use resource monitors.

Use transient tables for staging and rebuildable data, shorten Time Travel on high-churn tables, drop unused tables and clones that diverged, clean old stage files, and avoid keeping many full copies instead of clones.

Not necessarily. If a query parallelizes well, doubling the size halves the runtime at double the rate, so the cost is similar and the result arrives sooner. It's more expensive when queries are small or don't parallelize.

Each resume bills at least 60 seconds, and auto-suspend keeps it running for the configured idle time after each query. A query every minute can keep the warehouse running permanently. Batch such queries, use serverless tasks, or use a single small shared warehouse.

Cloud services usage is free up to 10% of the day's warehouse compute; only the excess is billed. Heavy metadata operations (many tiny queries, SHOW commands, frequent clones) can exceed it.

SNOWFLAKE.ACCOUNT_USAGE views cover the whole account, keep one year of history and include dropped objects, but have latency (minutes to a few hours). INFORMATION_SCHEMA views and table functions are per database, real-time and keep shorter history (7–14 days for query history).

Copying databases (and with replication groups, other objects such as users, roles, warehouses and integrations) to accounts in other regions or clouds, refreshed on a schedule, for disaster recovery, data sharing or migration.

Failover groups (Business Critical) let a secondary account be promoted to primary for replicated objects during an outage. Client redirect uses a connection URL that can be switched to the new primary, so applications reconnect without config changes.

With Terraform (the Snowflake provider) for account objects such as warehouses, roles, grants, databases and integrations; dbt or schemachange for schema and transformation changes; and the Snowflake CLI or EXECUTE IMMEDIATE FROM with Git repositories in Snowflake for deploying SQL files.

An integration that clones a Git repository into Snowflake so you can run SQL and Python files from it, reference them in notebooks and procedures, and deploy from a branch or tag.

With alerts (scheduled conditions that run an action such as sending an email or a webhook notification), data metric functions (Horizon data quality monitoring: null counts, duplicates, freshness, custom metrics), and failure notifications on tasks.

Functions (system ones like NULL_COUNT, DUPLICATE_COUNT, FRESHNESS, or custom ones) attached to tables and run on a schedule, with results stored for monitoring data quality over time.

A special table that collects logs, traces and metrics emitted by UDFs, procedures, Native Apps and Snowpark Container Services, so application code inside Snowflake can be monitored.

Time Travel and Fail-safe cover recent mistakes and disasters; for longer protection use zero-copy clones on a schedule, backups (immutable point-in-time backup sets, where available), replication to another region, or unloading to cloud storage.

Version SQL in Git, deploy through CI with a service user (key pair or OAuth), use CREATE OR ALTER or migration tools (schemachange, dbt, Terraform), test on a cloned database first, and keep rollback scripts or rely on Time Travel and clones.

Yes. Snowflake handles joins well and storage is cheap, but star schemas still make analytics simpler, consistent and fast for BI tools. Many teams use a layered approach: raw, staging, intermediate and marts with facts and dimensions, often built with dbt.

Normalize where data integrity and reuse matter (core entities), denormalize for consumption (wide marts, one big table for specific dashboards). Columnar storage makes wide tables cheap to query when only some columns are read.

dbt compiles models to SQL and runs them on a Snowflake warehouse through the dbt-snowflake adapter, supporting tables, views, incremental models (merge, delete+insert, append, microbatch), dynamic tables, transient tables, query tags and clustering configs.

Through the Spark connector, which reads and writes Snowflake tables with query pushdown, or by sharing Iceberg tables through a catalog so Spark and Snowflake both work on the same files.

With a MERGE that closes changed current rows and inserts new versions (often two statements or a union trick), with streams and tasks for incremental processing, or with dbt snapshots, which handle it automatically.

A wide denormalized table with everything a use case needs, which is fast to query in a columnar engine and simple for BI. It fits stable, specific use cases; for general analytics, star schemas stay more flexible and consistent.

As conformed dimensions owned by one model, with surrogate keys and SCD history as needed, referenced by all marts so every report uses the same definitions.

Create a storage integration and external stage on the S3 prefix, a Snowpipe with auto-ingest from S3 event notifications into a raw VARIANT or typed table, a stream on the raw table, and dynamic tables (or tasks with MERGE) building cleaned and aggregated tables at the needed freshness. Add data metric functions, alerts and resource monitors, and manage everything as code.

Make it a multi-cluster warehouse with enough maximum clusters (Standard policy for interactive users), check whether the slow queries can be optimized or served from aggregates, and separate other workloads that hit the same warehouse at that time.

The table isn't clustered by customer_id. For point lookups, enable search optimization on that column; for range filters used often, add a clustering key (possibly combined with date); otherwise reconsider the query or create a pre-aggregated table.

Use Time Travel: insert back the rows from orders AT (OFFSET => -3600) or BEFORE (STATEMENT => '<query id>'), or clone the table at that point and swap it in with ALTER TABLE … SWAP WITH. Then restrict delete rights on production tables.

Compare WAREHOUSE_METERING_HISTORY and serverless metering by service and warehouse, find the warehouses and query tags that grew, look at QUERY_ATTRIBUTION_HISTORY for the costliest queries, and check for warehouses no longer auto-suspending, new dynamic tables with tight lag, reclustering on high-churn tables, or full refreshes.

A row access policy on the tables using a mapping table of roles to countries, and a masking policy (or tag-based masking) on email columns that reveals values only to a privileged role. Grant analysts functional roles mapped to their countries.

Inventory objects, workloads and dependencies; translate DDL and SQL (SnowConvert helps); move historical data through cloud storage and COPY; set up incremental loading; rebuild transformations (often in dbt); re-create security with roles and policies; validate with row counts, checksums and report comparisons; run both in parallel before cutover; then tune warehouses and costs.

Land compressed files of ~100–250 MB in S3, load with Snowpipe or scheduled COPY into a VARIANT column in a transient raw table, extract commonly used fields into typed columns in a cleaned table clustered by event date, and aggregate into marts. Keep raw data short-lived if it can be reloaded from S3.

One governed place for the company's data that scales instantly for any number of users, costs nothing when idle, and lets teams share and analyze the same numbers without managing servers.

Whether the target is pruned (add a date or key range filter in the ON clause, cluster by the merge key), whether the source has duplicates or is far larger than the changes, the warehouse size and spilling, and whether an incremental approach (streams, delete+insert on recent partitions) fits better.

The same data arrived in different files (producer retries, renamed files), or files were reloaded with FORCE. Make producers write deterministic file names, deduplicate downstream on a business key with QUALIFY, and keep the raw layer append-only with file metadata for tracing.

Create secure views on the curated tables, put them in a share (or a private listing), and add the partner's account as a consumer; if they have no Snowflake account, create a reader account. Use row access policies or separate views to limit what they see.