SnowPro Core flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Three Snowflake layers

Storage holds data in cloud object storage, compute runs virtual warehouses, and cloud services manages metadata, security, optimization, and transactions across the account.

Micro-partition size range

Snowflake stores table data in immutable, compressed, columnar micro-partitions typically between 50 and 500 MB, with metadata used for pruning and clustering.

Virtual warehouse sizing

Warehouses range from X-Small to 6X-Large. Larger sizes add compute for bigger workloads and consume more credits per hour; they do not always speed small queries.

Result cache lifetime

Query results are cached for 24 hours by default and reused when the same query text runs and underlying data has not changed, without using warehouse compute.

Multi-cluster warehouse purpose

A multi-cluster warehouse starts additional compute clusters automatically when concurrency rises. It reduces queuing for many concurrent queries but does not speed a single slow query.

Snowflake account editions

Standard, Enterprise, Business Critical, and Virtual Private Snowflake. Higher editions add capabilities such as longer Time Travel, multi-cluster warehouses, Tri-Secret Secure, and dedicated infrastructure.

ACCOUNTADMIN role

The highest-level system role with all account privileges. It should be granted sparingly; SECURITYADMIN manages grants, SYSADMIN manages objects, and USERADMIN manages users and roles.

Future grants

Privileges that apply automatically to objects created later in a database or schema. They reduce the need to grant permissions on each new table or view.

Network policy

An account-level or user-level control that allows or blocks access by IP address. A user-level policy overrides the account-level policy for that user.

Tri-Secret Secure

A Business Critical feature that combines a Snowflake-managed key with a customer-managed key. Both keys are required to decrypt data at rest.

Masking policy

A schema-level object that changes column values at query time based on the user's role. The stored data remains unchanged and access is controlled by the policy.

Query Profile spilling

Local or remote spilling shown in Query Profile indicates the warehouse lacks memory for the query. Increasing warehouse size adds memory and can reduce or eliminate spilling.

Clustering key

Defines the physical ordering of micro-partitions for a table. It improves pruning for large tables filtered on high-cardinality or range columns.

Search optimization service

Maintains a search access path that speeds selective point lookups and substring queries. It is most useful for equality and IN filters on large tables.

Materialized view

A precomputed query result that Snowflake refreshes automatically. It can accelerate repeated aggregations but adds storage cost and maintenance overhead.

Micro-partition pruning

Snowflake uses micro-partition metadata to skip partitions that cannot match filter predicates. Wrapping filter columns in functions can prevent pruning and slow queries.

FLATTEN table function

Explodes semi-structured arrays or objects into rows. Use LATERAL FLATTEN to join each flattened element with its source row.

QUALIFY clause

Filters the results of window functions without requiring a subquery. It is evaluated after window functions and before ORDER BY.

Zero-copy clone

CREATE TABLE ... CLONE creates a new table that shares existing micro-partitions. No data is copied until either table is modified.

Time Travel AT clause

Queries historical table data using AT or BEFORE with TIMESTAMP, OFFSET, or STATEMENT. Available history depends on the table's data retention period.

Stream object

Captures change data for a table, view, or directory. It records inserts, updates, and deletes so downstream tasks can process changed rows.

Task object

Schedules SQL statements such as INSERT, MERGE, or stored procedure calls. Tasks can run on warehouse or serverless compute and be chained into DAGs.

COPY INTO command

Loads staged files into a table or unloads table data to a stage. Key options include ON_ERROR, PURGE, VALIDATION_MODE, and FILE_FORMAT.

Snowpipe

A continuous ingestion service that uses serverless compute. It loads new staged files automatically when event notifications or REST calls signal availability.

Recommended file size

For bulk loading, compressed files of roughly 100 to 250 MB are recommended. Many tiny files add overhead and degrade pruning performance.

Storage integration

An account-level object that stores cloud storage credentials for external stages. It avoids embedding access keys directly in stage definitions.

VALIDATION_MODE option

A COPY INTO option that checks files without loading data. It returns errors and can be set to RETURN_ERRORS or RETURN_N_ROWS.

Fail-safe period

Seven days of recovery after Time Travel ends. Fail-safe data is not queryable and can be restored only by Snowflake Support.

Time Travel retention

Standard accounts allow 0 or 1 day; Enterprise and higher allow up to 90 days. Retention is set per table or schema.

Secure data sharing

Provides read-only access to a database without copying data. Consumers query shared data using their own virtual warehouses and pay for their compute.

Reader account

A separate Snowflake account created by a provider for consumers without their own account. The provider pays for storage and compute used by the reader account.

Database replication

Copies databases to another account or region for disaster recovery or sharing. Secondary databases are read-only until a failover promotes them.