Features and Architecture

Snowflake Architecture

Snowflake uses a multi-cluster shared data architecture, means

  1. Storage is centralised and shared
    1. All data is stored once in Snowflake-managed cloud storage (s3, Azure Blob, GCS).
    2. All compute clusters access the same centralised data storage simultaneously
    3. Centralised data storage simplifies management
      1. data exists in one place
      2. all compute clusters see the same data
      3. no replication or synchronisation is needed
      4. consistency is easier to maintain
  2. Compute is independent and elastic.
    1. Multiple virtual warehouses (clusters) can run queries concurrently without interfering.
  3. Services layer orchestrates metadata, optimisation, authentication.
    1. A third layer that sits above compute & storage.

Snowflake consists of three main layers:

  1. Storage Layer.
    1. Stores data in micro-partitions (but does NOT execute queries).
  2. Computer Layer (Virtual Warehouses).
    1. Executes queries, perform joins, aggregations, scans, etc.
  3. Cloud Services Layer.
    1. Authentication, Metadata, Query Optimisation, Transactions.

Snowflake Editions

There are four editions of Snowflake:

  1. Standard Edition
  2. Enterprise Edition
  3. Business Critical Edition
  4. Virtual Private Snowflake (VPS)

Standard Edition

  • Target User: introductory level, early stage analytics
  • Core functionality
    • Basic Snowflake features
  • Key Differences
    • Cost effective entry point
  • Security Focus
    • Standard Security and encryption
  • Disaster Recovery
    • Time travel up to 1 day
    • non-configurable 7 day failsafe after time travel ends

Enterprise Edition

  • Target User: Large-scale organisation with daily workloads
  • Core Functionality
    • All standard features
    • Advanced capabilities
  • Key differences
    • Multi-cluster warehouses
    • Extended time travel (up to 90 days)
    • Materialised views
    • Row-level security via Row Access Policies
    • SAML 2.0 based Single Sign-On (SSO)
    • Automatic Clustering for micro-partition management
  • Security Focus
    • Stronger data governance and security features
  • Disaster Recovery
    • Time travel up to 90 days

Business Critical Edition

  • Target User: Highly regulated industries (e.g. healthcare, finance)
  • Core Functionality
    • All Enterprise features
    • Enhanced Security
  • Key Differences
    • Enhanced Security
    • Customer-Managed Keys & Encryption
    • Failover & failback
    • AWS PrivateLink support
  • Security Focus
    • Designed for highly sensitive data and compliance (e.g. HIPAA, HITRUST CSF)
  • Disaster Recovery
    • Database failover and failback

Virtual Private Snowflake (VPS)

  • Target User: Organisation requiring maximum security and isolation
  • Core Functionality
    • All features of the chosen underlying edition
    • Dedicated Environment
  • Key Differences
    • Full isolated environment
    • Dedicated metadata store
    • Highest level of security
  • Security Focus
    • Complete logical isolation from the public platform
  • Disaster Recovery
    • Available within the dedicated environment

Table Types

There are four main table types in Snowflake

  1. Temporary table.
  2. Transient table.
  3. Permanent table.
  4. External table.

Temporary Table

Transient Table

A transient table in Snowflake is

  • Does not have Fail-safe (0-day fail-safe)
  • Support Time Travel only up to 1 day (even in Enterprise Edition)
  • Is intended for non-critical, re-creatable, or intermediate data
  • Reduce storage cost by avoiding 7 days of Fail-Safe retention.
  • It behaves like a permanent table for most SQL operations (INSERT, UPDATE, DELETE, CLONE), but with reduced recovery guarantees.
  • A table that is persistent across sessions, supports Time Travel, but does not maintain Fail-safe storage.

Use Cases

  • Staging tables (bronze layer)
  • Intermediate ETL results (silver layer)
  • Pre-aggregation that can be re-run
  • Data is frequently overwritten

Permanent Table

  • Time Travel
    • Default (Standard Edition): 1 day
    • Enterprise Edition & Above: configurable up to 90 days
    • Fail-safe: Additional 7 days

External Table

When you create or enable an external table in Snowflake

  1. Snowflake does NOT automatically load file metadata from the external stage.
  2. You must manually run ALTER EXTERNAL TABLE my_ext_table REFRESH; to populates or updates the external table’s metadata (list of files + partitions).

You cannot use clustering keys on external tables, because External tables

  1. Store data outside Snowflake
  2. Are metadata-only structures
  3. Do no have Snowflake-managed micro-partitions
  4. Are scanned based on file metadata and partition columns, not clustering keys.

Because Snowflake cannot rearrange external storages, features like

  1. Clustering keys
  2. Search Optimisation Service (SOS)
  3. Automatic clustering
  4. Time Travel
  5. Fail-safe are not available on external tables.

Virtual Warehouse

Data Spilling

Data Spilling occurs when Snowflake runs a query and the virtual warehouse does not have enough memory to hold all intermediate data for

  • Joins
  • Aggregations
  • Window Functions
  • ORDER BY operations
  • Large result sets

When memory is insufficient, Snowflake spills the intermediate results to local SSD storage attached to the compute nodes. It’s not an error, but it impacts performance.

If SSD space is also insufficient, Snowflake will spills to cloud storage (S3, Azure Blob, GCS), which is much slower.

Caching Layers

Snowflake has 4 caching mechanisms, each operating in a different layer of its architecture

  1. Query Result Cache (24-hour Service layers cache)
  2. Metadata Cache
  3. Warehouse Data Cache (Local SSD & Memory)
  4. Cloud Service-level Optimisation (Partition Pruning & Clustering Cache)

Query Result Cache

  • Layer: Cloud Services Layer
  • Requires Warehouse Running: No
  • Cost: FREE (no compute usage)
  • Duration: This cache stores entire query results for 24 hours.
  • How it works
    • If a user issues an identical query (same text, same role, same warehouse size), and no underlying data changes,
    • Snowflake returns the results instantly from the Service Layer - without computing anything.
  • Key Facts
    • Valid for 24 hours
    • Invalidated by data changes
    • Shared across users in the same account
    • The only cache that works even when no warehouse is running.
    • Queries using UDFs are not eligible for result cache because the results may vary based on the function execution.

Metadata Cache

  • Layer: Cloud Services Layer
  • Required Warehouse Running: Yes
  • Purpose: reduce planning time
  • Metadata includes
    • Column statistics
    • Micro-partition boundaries
    • File metadata (external tables)
    • Min/Max values for partition pruning
  • Benefits
    • Faster query planning
    • Better micro-partition pruning
    • Quicker execution initialisation
    • Even though it lives in the Cloud Services Layer, the query still requires a warehouse to perform the scan.

Warehouse Data Cache

  • Layer: Compute Layer (Virtual Warehouse)
  • Requires Warehouse Running: Yes
  • Live in: Warehouse memory & local SSD storage
  • Benefits:
    • dramatically faster repeated scans
    • Helps analytical workloads with repeated filters
    • Reduced cloud storage I/O costs
  • Behaviour
    • Cache is warehouse-specific
    • Cache is lost when warehouse:
      • is suspended
      • resized
      • scaled out
      • restarted

Clustering Cache (aka Partition Pruning Efficiency)

  • Layer: Cloud Service Layer (Metadata)
  • Require Warehouse Running: Yes
  • Snowflake Prunes micro-partitions by checking:
    • Min/max values of columns
    • Clustering depth
    • Partition statistics
  • Benefits
    • Reduces data scanned
    • Improves performance of range-filter workloads
    • Works best with clustering keys
  • It does not store data - it stores metadata about micro-partitions.
  • It’s often called ‘Clustering cache’, but technically it’s metadata-driven pruning, not cached table data.

Key Takeaways

  1. Only the Query Result Cache works without warehouses.
  2. SSD / Data Cache is lost when warehouse is suspended or resized.
  3. Metadata Cache & Pruning always require active computation.
  4. Clustering cache != storing data, it’s pruning metadata.
  5. Result cache is 24 hours and invalid when data changes.

Time Travel

  1. Permanent tables -> Up to 90 days (Enterprise & above); 1 day Standard Edition
  2. Transient table -> 1 day max
  3. Temporary table -> 1 day max (session-lifetime)
  4. External table -> No Time Travel

Fail-safe

  1. Fail-safe applies ONLY to permanent tables
    1. Permanent tables -> 7-day Fail-safe after Time Travel ends
    2. Transient table -> No Fail-safe
    3. Temporary table -> No Fail-safe
    4. External table -> No fail-safe
  2. Fail-safe is performed only by Snowflake Support team.
    1. Users cannot access Fail-safe directly.

Important Notes

  1. Transient & Temporary tables still max out at 1-day Time travel, and no Fail-safe, regardless of edition.
  2. External tables have no Time Travel or Fail-safe.

Summary

Standard Edition

  • Time Travel: 1 day
  • Fail-safe: 7 days

Enterprise Edition

  • Time Travel: up to 90 days configurable
  • Fail-safe: 7 days

Business Critical Edition

  • Time Travel: 90 days
  • Fail-safe: 7 days

Task

A Snowflake Task is a built-in, serverless scheduling and orchestration feature that allows you to run SQL statements (often ETL/ELT logic) on a defined schedule or in response to upstream events.

Snowflake Tasks let you run pipelines inside Snowflake without external tools like Airflow or Cron.

  1. Run SQL automatically on a schedule with CRON expression.
  2. Run SQL based on dependencies.
    1. Tasks can form task trees (DAGs), where downstream tasks run only after upstream tasks complete.
  3. Execute SQL or call Stored Procedures.
  4. Use serverless compute or a virtual warehouse.

Stored Procedure

In Snowflake, Stored Procedures have two possible execution contexts:

  1. Execute as caller
  2. Execute as owner

If you do not explicitly specify one, Snowflake defaults to Execute as OWNER.

Snowflake supports creating Stored Procedures using following languages:

  1. JavaScript
  2. SQL (Snowflake Scripting)
  3. Python

Stored Procedures can perform multiple operations without returning a value, while UDFs must return a value and are typically used in queries.

System Tables and Views

  1. ACCOUNT_USAGE schema includes information on dropped objects, while INFORMATION_SCHEMA does not.
  2. TABLE_STORAGE_METRICS views in Snowflake can obtain
    1. Total storage used for Time Travel.
    2. Amount of storage consumed by deleted tables.
    3. You will need ACCOUNTADMIN or MONITOR role to access this view.

Resource Monitors

  1. Resource Monitors can suspend warehouses.
    1. When a credit threshold is reached, a resource monitor can automatically
      1. notify administrators
      2. suspend one or more warehouses immediately.
  2. Resource Monitors apply at both the account and warehouse levels.
    1. Resource monitors are created at the account level.
    2. But they can be assigned to individual warehouses, or used to monitor total account-wide credit usage.
  3. When a Resource Monitor reaches the SUSPEND threshold
    1. Snowflake prevents NEW queries from starting
    2. Running queries are allowed to complete normally
    3. It’s called soft suspension.
    4. At 100%, SUSPEND = no immediate termination
    5. At 110%, SUSPEND_IMMEDIATE = immediate termination of all running queries.

Account Access and Security

Snowflake Account

  1. An Snowflake Account can be created with specifying a cloud provider.
  2. Multiple accounts can be managed centrally using an Org Admin role.
  3. A single Snowflake Account can exist within one cloud provider.
  4. Multiple accounts under one organisation can be in different regions.

Roles

  1. PUBLIC
    1. Lowest-privilege role
    2. Cannot manage security or access.
  2. SYSADMIN
    1. Manage Objects (warehouse, tables, schemas, etc).
    2. When creating custom roles in Snowflake, the recommended practice is to grant them to the SYSADMIN role (or make them part of SYSADMIN’s hierarchy).
  3. SECURITYADMIN
    1. Manage users, roles
    2. Grant and revoke privileges
    3. Manages Row Access Policies, Masking Policies, and Network Policies.
    4. Assign roles to users.
  4. ACCOUNTADMIN
    1. Highest-level administrative role.
    2. Can do everything, but not typically used for day-to-day access management.
    3. Best practice is to reserve it for rare, critical tasks.

Privileges

Snowflake uses a role-based access control (RBAC) model to manage privileges

  1. Privileges are granted to roles.
  2. Roles are granted to users.
  3. Users never receive object privileges directly.
  4. Objects are not accessible by default - all access must be explicitly granted.

Ownership

  1. Ownership is the highest privilege on any Snowflake object (table, schema, database, stage, etc).
  2. The owning role can
    1. Modify the object
    2. Drop the object
    3. Grant and revoke privileges on the object
    4. Transfer ownership to another role

Monitor

To view executed queries, inspect query history, and analyse performance in Snowflake (e.g. via QUERY_HISTORY), a role must have the MONITOR privilege.

Monitor privilege allows a role to

  1. View query history
  2. View warehouse history and usage
  3. View load history
  4. Access the Account Usage views
  5. Monitor running queries

Privilege Requirements for Common Use Cases

ObjectMinimum Privileges Needed
Access tableUSAGE on DB + USAGE on Schema + SELECT/INSERT/UPDATE
Create tableUSAGE on DB + USAGE on Schema + CREATE TABLE
Clone tableUSAGE on DB + USAGE on Schema + CREATE TABLE
View query historyMONITOR
Start/stop warehouseOPERATE
Create databaseCREATE DATABASE on ACCOUNT
Create userCREATE USER
Grant/revokeMANAGE GRANTS
Apply policiesAPPLY POLICY
Enable Search Optimisation Service on a tableOWNERSHIP

Authentication Methods

Multi-Factor Authentication (MFA)

  1. Multi-Factor Authentication (MFA) is available for all Snowflake Editions, starting from Standard Edition.
    1. MFA in Snowflake is enabled by default for all users, but each user must manual enrol to activate it.
  2. In Snowflake, the following authentication methods support MFA:
    1. Snowflake Web UI
    2. SnowSQL
    3. ODBC/JDBC (when using username/password + MFA)
    4. Key Pair Authentication DOES NOT support MFA.
      1. You authenticate with a private key instead of a password
      2. Snowflake cannot prompt for MFA
      3. Often used for service accounts and automation.
  3. MFA Management in Snowflake is an account-level security configuration, and only the ACCOUNTADMIN role has the necessary privileges to
    1. Enable or disable MFA policies.
    2. Enforce MFA requirements.
    3. Manage global account security settings.
    4. Reset MFA enrollment for users.
    5. Even though SECURITYADMIN manages users, roles and grants, it cannot configure or enforce MFA account-wide.

Federated Authentication (SSO)

Snowflake natively supports SAML 2.0 based Identity Providers (IdPs) for Single Sign-on, including:

  1. Okta
  2. Microsoft ADFS
  3. Azure AD
  4. Ping Identity
  5. Other SAML 2.0 providers

Duo or Google Authenticator are used for MFA, not SSO purposes.

Network Policy

In Snowflake network policies

  1. If an IP address appears in both the ALLOWED_IP_LIST and BLOCKED_IP_LIST, the BLOCK rule wins.
  2. Blocked IPs always take precedence over allowed IPs.

Key Rotation

Snowflake uses a hierarchical key model where keys rotate regularly (monthly by default).

When Key Rotation occurs in Snowflake:

  1. New data uses the new key.
    1. Any data loaded after the rotation is encrypted with the newly rotated key.
  2. Existing data is not re-encrypted immediately.
    1. Old data continues to use the key with which it was originally encrypted.
    2. The old keys remain active in the key hierarchy until they naturally expire.
  3. No downtime, no customer action required.
  4. Re-encryption only happens during automatic key material refresh cycles, not during rotation.

Performance Concepts

Scaling Policy

  1. Standard.
    1. Default scaling policy.
    2. Adds new clusters quickly when concurrency or queueing increases.
    3. Focus on minimising query wait times.
    4. Suitable for SLAs, dashboards, large concurrent workloads.
  2. Economy.
    1. Add new clusters more slowly and conservatively.
    2. Ties to avoid spinning up additional clusters unless absolutely necessary.
    3. Allows more queueing vs Standard.

Search Optimisation Service (SOS)

  1. Search Optimisation Service (SOS) is a serverless Snowflake feature that accelerates point lookups and selective filters on large tables, without requiring clustering keys.
  2. It works by building search access paths (index-like structures) that make Snowflake extremely fast at locating a small subset of rows in a massive table.
  3. Search Optimisation Service (SOS) runs outside of your virtual warehouses.
    1. Snowflake provides the compute automatically.
  4. SOS improves the performance when
    1. Queries search for few rows in very large tables
    2. Filter use high-selectivity predicates
    3. You query semi-structured VARIANT data frequently.
    4. You want performance without manually managing clustering keys.
  5. SOS is best for high-concurrency serving workloads, lookup tables, and customer-facing apps built on Snowflake.
  6. To enable Search Optimisation Service on a table, a user either have ownership privilege on the table or the ADD SEARCH OPTIMIZATION privilege on the schema level.

You pay for

  1. Search Optimisation compute credits (serverless compute).
  2. Additional storage for the search access paths (index-like structures).

Table Clustering Depth

  1. SYSTEM$CLUSTERING_INFO is the system function that returns detailed clustering metrics for a table, including
    1. Average clustering depth
    2. Partition pruning effectiveness
    3. Clustering key quality
    4. Micro-partition overlap
    5. Deviation from ideal clustering

Example usage

1

SELECT SYSTEM$CLUSTERING_INFO('my_database.my_schema.my_table');

Materialised View

A Materialised View in Snowflake stores physically precomputed results in Snowflake’s storage.

  1. Stores data physically (cost storage).
  2. Fast query performance (no re-computation).
  3. Automatically maintained by Snowflake
  4. Only allowed on a single based table.
  5. No joins, no window functions, no subqueries referencing other tables.
  6. Async incremental refresh (not immediately real-time)
  7. Best for frequently repeated queries or large tables.

Snowflake materialised views have several important restrictions:

  1. Materialised views must be based on a single base table.
  2. Self joins are not allowed.

Data Loading and Unloading

Upload into Stage

  1. PUT is used to upload files from your local machine into a Snowflake Internal Stage (user stage, table stage, or named internal stage).

Snowflake Stage

  1. A Snowflake Stage is a location where Snowflake reads or write data files for loading, unloading, or external ingestion.
  2. Stage act as the interface between Snowflake and file storage.

There are three types of Snowflake Stages:

  1. Internal Stage.
    1. Snowflake-managed storage.
    2. @~: User stage
    3. @%table: table stage
    4. Named internal stage.
  2. External Stage.
    1. cloud storages like (AWS S3, Azure Blob Storage, Google Cloud Storage).
    2. External stages in Snowflake DO NOT always require storage credentials to be included in the stage definition.
  3. Temporary Stage.
    1. Internal stages tied to sessions or temporary objects.

Stages are used for

  1. Copying data into tables (COPY INTO).
  2. Exporting data from tables (COPY INTO @stage).
  3. Integrating Snowpipe / Snowpipe Streaming.
  4. Querying external data directly (external tables).
  5. Storing semi-structured files (JSON, Parquet, CSV).

Internal Stage

Internal Stage also supports directory table.

  1. When you first enable a directory table on an internal stage, you must manually run the command
    1. ALTER STAGE my_int_stage REFRESH;
    2. This will load metadata for existing files.
  2. After initial population, for new files uploaded using PUT command, metadata is updated automatically by Snowflake.

External Stage

External Storage Credentials

  1. External stages in Snowflake do NOT always require storage credentials to be included in the stage definition.
  2. The preferred method is to use a storage integration object to securely manage credentials separately from your stage definition.
  3. Credentials are required only when
    1. you are accessing public cloud storage directly (S3, GCS, Azure Blob)
    2. not using an identity federation or role-based mechanism.
  4. Credentials are not required if you use
    1. AWS IAM Role (Storage_Integration with IAM_ROLE)
    2. Azure SAS / Managed Identity
    3. GCP Service Account via Storage Integration
    4. Snowflake External Volume

Temporary Stage

Directory Table in Snowflake Stage

  1. Enabling a directory table allows Snowflake to track and query the list of files in the stage - essentially metadata about files.
    1. It creates a virtual filesystem catalog Snowflake maintains.
    2. Enable incremental file processing.
    3. Improve COPY performance.
  2. When you enable a directory table on an existing stage, Snowflake does not automatically load metadata for files that were already present in the stage.
    1. To populate the directory table with metadata for existing files, you must run ALTER STAGE my_ext_stage REFRESH;.

COPY INTO

COPY INTO is a bulk data movement command in Snowflake.

  1. Loads data INTO a Snowflake table from a stage (internal or external).
  2. Unloads data FROM a Snowflake table to a stage (internal or external).
  3. In COPY INTO
    1. You can use SELECT statement to cast column data types during loading
    2. You cannot
      1. use GROUP BY inside a COPY INTO command
      2. use aggregation functions
      3. use JOINs

Load data into Table

  1. Buck loading from
    1. Internal stage
    2. external stage (S3, GCS, Azure Blob)
    3. Named stages
    4. Snowflake-managed stages
  2. Supports parallel loading for high performance.
  3. Error handling with ON_ERROR.
  4. File tracking to prevent duplicates.
  5. Transformation via SELECT in staging loads.
  6. Supports COPY history for auditing.
  7. File format can be specified directly in the COPY INTO command, but not mandatory.
    1. The file format can be predefined in a stage or a separated file format object, allowing for flexibility.
    2. A separate File format object is recommended when specifying file format properties.

Example

1
2
3
4

COPY INTO my_table
FROM @my_stage/orders/
FILE_FORMAT = (TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1)
;

Unload data from Table to Stage

  1. Unload query results into files (CSV, Parquet, JSON, ORC).
  2. Supports compression.
  3. Supports parallel file generation.
  4. Often used for downstream systems, backup, ML pipelines, etc.

Example

1
2
3
4

COPY INTO @my_stage/unload/
FROM (SELECT * FROM my_table)
FILE_FORMAT = (TYPE='CSV' FIELD_DELIMITER=',' HEADER=TRUE)
;

ON_ERROR

  1. The default behaviour for the ON_ERROR option is ABORT_STATEMENT. 2. This means if any error occurs during loading (bad row, wrong format, missing column), Snowflake aborts the entire COPY command.

Truncating column values

  1. Truncating column values using TRUNCATECOLUMNS option is supported for managing column length.

PURGE=TRUE

  1. Snowflake will permanently delete the source files after they successfully loaded.
    1. PURGE only deletes files that were successfully loaded, failed or skipped files remain.
    2. PURGE cannot be undone (not recoverable with Time Travel or Fail-safe).
  2. This applies to Internal Stage only (user, table, or named internal stages).
  3. For external stages (S3, GCS, Azure Blob), the files are deleted from your cloud storage bucket.
  4. PURGE is useful for ETL pipelines where files should not be reloaded.

INSERT OVERWRITE

  1. INSERT OVERWRITE in Snowflake replaces all existing rows in a table with the result set of the query.

Example

1
2
3

INSERT OVERWRITE INTO my_table
SELECT * FROM staging_table
;

TRUNCATE and INSERT is a concept, not a single Snowflake command.

Snowpipe

  1. Snowpipe can be paused and resumed using the ALTER PIPE command.
  2. Snowpipe does NOT have a file-size restriction, but Snowflake recommends smaller files for efficiency, but. this is a best practice, not a hard requirement.
  3. Snowpipe’s cost is based on a per-second per-core granularity.
  4. Snowpipe is a serverless feature that does not require a user-created warehouse.

Data Transformation

VARIANT Data Type

  1. In Snowflake, the colon operator (:) is used to extract fields from VARIANT, ARRAY, or OBJECT types

Example

1
2
3
4
5

SELECT
    data:customer.name as customer_name
    , data:items[0].product_id as first_product
FROM orders
;

Clustering Key

A Clustering Key is a column (or a set of columns) that Snowflake uses to organise micro-partitions so that data is commonly filtered together is physically grouped on storage.

Clustering Keys can only apply to internal Snowflake tables (permanent, transient temporary). External tables cannot have clustering keys.

A clustering key improves

  1. Query performance for large tables by skipping irrelevant partitions during query execution.
  2. Pruning efficiency (fewer micro-partitions scanned).
  3. Cost reduction (less data read).
  4. Cluster key can be used in both new and existing tables.

When should you use a clustering key?

  1. A table is very large (100M+ rows or > 1GB).
  2. Queries frequently filter by a specific column.
  3. The nature load pattern does not cluster the data well.

Which column benefits MOST?

  1. It is used heavily and frequently in WHERE clauses.
  2. It has high cardinality (many distinct values).
  3. It correlates with how data is queried (e.g. timestamp filters).

Usage

  • New table definition
1
2
3
4
5
6

CREATE TABLE events (
    id NUMBER,
    event_ts TIMESTAMP,
    user_id NUMBER
)
CLUSTER BY (event_ts, user_id);
  • Existing table
1

ALTER TABLE events CLUSTER BY (event_ts);

Automatic Clustering

  1. Continuous monitors tables with defined clustering keys.
  2. Automatically re-clusters micro-partitions in the background.
  3. Maintains optimal clustering depth and pruning efficiency.
  4. Eliminates the need for manual ALTER TABLE ... RECLUSTER command.

Flatten()

  1. The FLATTEN() function is a table function used to explode semi-structured data (arrays, objects) into multiple rows. Returns multiple columns including
    1. VALUE: the flattened element value.
    2. INDEX: position in array.
    3. KEY: key name.
    4. PATH: hierarchical path to the element.
    5. SEQ: unique sequence number.
    6. THIS: the original input value.
  2. FLATTEN() function works with VARIANT, ARRAY, and OBJECT data types directly.
  3. The RECURSIVE parameter is optional.
    1. RECURSIVE=TRUE is default, it only flattens the top-level array.
    2. RECURSIVE=FALSE will recursively flattens all nested levels.

Example usage:

1
2
3

SELECT f.value from TABLE,
LATERAL FLATTEN(input => mytable.array_column) f
;

While FLATTEN() is commonly used with LATERAL in JOIN operations, it’s not the only way to use it. You can also use the TABLE() syntax as well

For example,

1
2
3

SELECT f.value from my_table,
TABLE(FLATTEN(input => my_table.array_column)) f
;

Table Clone

Privilege

  1. In Snowflake when cloning a schema
    1. Schema-level privileges (USAGE, CREATE TABLE, etc) are inherited automatically by the cloned schema because they follow the schema object itself.
    2. Privileges on child objects (tables, views, stages, functions etc) are NOT CLONED and must be granted separately.
  2. This is a key Snowflake RBAC behaviour
    1. Cloning does clone the object (tables, views)
    2. Cloning does NOT clone privileges on these objects.
  3. To clone a table in Snowflake, the user doesn’t need the SELECT privilege.
    1. This is because cloning in Snowflake is zero-copy cloning, meaning
      1. Snowflake copies metadata, not data rows.
      2. No access to the actual data is required.
      3. Therefore, SELECT is not part of cloning privileges.
    2. Instead, the user must have the OWNERSHIP privilege or CREATE ... ON SCHEMA privilege, plus USAGE on the database and schema.

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT uses probabilistic algorithms (HyperLogLog) to provide a fast, memory-efficient approximation of distinct counts on very large datasets.

  • Much faster than COUNT(DISTINCT column).
  • Uses far less memory
  • Excellent for large-scale analytics
  • Very small error margin (acceptable for BI workloads)

Sampling

Snowflake supports two main sampling methods

  1. System (Block) Sampling
    1. Samples entire micro-partitions (blocks) instead of individual rows
    2. Very fast and efficient for large datasets
    3. Requires less compute
    4. Provides approximate randomness but excellent performance.
    5. SELECT * FROM my_table SAMPLE SYSTEM (10);
  2. Bernoulli (Row) Sampling
    1. Evaluate each row individually
    2. Much slower on large tables
    3. More resource-intensive.
    4. Provides true random row sampling but not ideal for big data
    5. SELECT * FROM my_table SAMPLE BERNOULLI (10);

Data Protection and Data Sharing

Data Sharing

With Snowflake’s. Secure Data Sharing, no physical data is copied to the consumer’s account

  1. Data is not duplicated.
    1. The consumer accesses the same micro-partitions stored in the provider's account.
  2. Zero additional storage cost for the consumer. Consumers only pay for
    1. Compute used to query the shared data.
    2. (optionally) Caching in their virtual warehouse.
  3. Consumers do not pay for storage
    1. Because the data is not copied or replicated as part of sharing.

Row Access Policy

  1. A Row Access Policy in Snowflake filters rows based on conditions using context functions.
  2. To check which user is executing the query, Snowflake provides CURRENT_USER() to return the Snowflake username of the session’s authenticated user.
  3. A Row Access Policy is created separately, then attached to a specific column in a table.
  4. Even through the policy is applied to a column, it controls row-level visibility for the entire table.
  5. Only ONE Row Access Policy can be applied per table.
  6. Policies are enforced automatically on all queries.
  7. Snowflake starts to support Row Access Policy from Enterprise Edition.

Create the policy

1
2

CREATE ROW ACCESS POLICY region_filter AS (region STRING)
RETURNS BOOLEAN -> region = CURRENT_USER();

Attach the policy to a table

1
2

ALTER TABLE sales
ADD ROW ACCESS POLICY region_filter ON (region);

External Functions

  1. External Functions are designed to integrate Snowflake with external services, and execute code outside of Snowflake.
  2. They allow users to leverage third-party APIs, external data sources, and additional programming languages beyond what is available internally.
  3. Other options
    1. User-Defined Functions (UDSs) run inside Snowflake (SQL, JavaScript or Snowpark).
    2. Secure Function same as UDFs, but mask internal logic for sharing data; they still run inside Snowflake.
    3. Store Procedures execute. procedural code (JavaScript or Snowpark) inside Snowflake, not external.

Reference