SnowPro Core cheat sheet

Snowflake uses three layers: cloud services (metadata, security, optimization), compute (virtual warehouses), and storage (cloud object storage).

Object hierarchy: Organization → Account → Database → Schema → objects such as tables, views, stages, and functions.

Virtual warehouse sizes range from X-Small to 6X-Large; resizing changes compute capacity and credit consumption per hour.

Micro-partitions are immutable, compressed, columnar storage units typically 50–500 MB; metadata enables pruning.

Multi-cluster warehouses add clusters automatically for concurrency; auto-suspend and auto-resume reduce idle cost.

Editions: Standard, Enterprise, Business Critical, and Virtual Private Snowflake; higher editions add features such as longer Time Travel and Tri-Secret Secure.

Result cache persists up to 24 hours by default and is reused when identical query text runs and data has not changed.

Snowpark, Cortex AI, Marketplace, Streamlit, and Iceberg tables are AI Data Cloud capabilities built on the core platform.

RBAC grants privileges to roles; roles are granted to users and to other roles to form a hierarchy.

System roles include ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, and PUBLIC.

Authentication options include password with MFA, SAML federated SSO, OAuth, and key-pair authentication.

Network policies allow or block IP ranges at account or user level; a user-level policy overrides the account default.

Encryption uses AES-256 at rest and TLS in transit; Tri-Secret Secure combines Snowflake and customer-managed keys in Business Critical.

Masking policies transform column values at query time; row access policies filter rows based on role or session context.

Future grants apply to objects created later in a database or schema; MANAGE GRANTS controls grant operations.

Secure views hide the view definition and underlying data from unauthorized users.

Larger warehouses add compute linearly but cost proportionally more credits per hour; test before resizing.

Use Query Profile to find pruning inefficiency, exploding joins, and local or remote spilling.

Spilling to local or remote storage suggests insufficient warehouse memory; increasing size reduces spills.

Cluster large tables on high-cardinality columns used in filters; automatic clustering can maintain keys.

Search optimization service benefits selective point lookups and substring searches.

Materialized views accelerate repeated aggregations but consume storage and require maintenance.

Avoid functions on filter columns and use explicit casts to preserve micro-partition pruning.

Result cache returns identical query results without using warehouse compute when data is unchanged.

Multi-cluster warehouses solve concurrency queuing, not slow single-query execution.

Semi-structured data uses VARIANT; access fields with colon notation and array elements with brackets.

LATERAL FLATTEN explodes arrays or objects into rows and joins them to the source row.

QUALIFY filters window function results without a subquery; it runs after window evaluation.

Time Travel queries use AT or BEFORE with TIMESTAMP, OFFSET, or STATEMENT.

CREATE ... CLONE creates zero-copy clones that share micro-partitions until modified.

Streams capture inserts, updates, and deletes; tasks schedule SQL and can form DAGs.

Stages hold data files: user stage (@~), table stage (@%table), named internal stage, or external stage.

MERGE performs insert, update, and delete in one statement based on a join condition.

Load with COPY INTO

FROM @stage; unload with COPY INTO @stage FROM .

PUT uploads local files to an internal stage; GET downloads staged files to a local directory.

ON_ERROR options include CONTINUE, SKIP_FILE, and ABORT_STATEMENT; VALIDATION_MODE checks files without loading.

Snowpipe provides continuous serverless ingestion triggered by event notifications or REST calls.

Recommended compressed file size is roughly 100–250 MB; avoid many small files.

External stages require a storage integration or credentials for S3, Azure Blob, or GCS.

File formats include CSV, JSON, Avro, ORC, Parquet, and XML; FILE_FORMAT defines parsing options.

Stage metadata columns include METADATA$FILENAME and METADATA$FILE_ROW_NUMBER.

Time Travel retention: 0–1 day in Standard, up to 90 days in Enterprise and higher.

Fail-safe adds 7 days after Time Travel; only Snowflake Support can restore fail-safe data.

Zero-copy cloning duplicates metadata, not data; changes to either object create new micro-partitions.

Secure data sharing grants read-only access without copying data; consumers use their own warehouses.

Reader accounts let providers share data with consumers who lack a Snowflake account; the provider pays compute.

Database replication copies objects to another region or account; secondary databases are read-only until failover.

Replication and failover groups support business continuity across accounts and regions.

Masking policies, row access policies, and secure views protect shared or sensitive data.