SnowPro Advanced Data Engineer flashcards

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

VALIDATION_MODE RETURN_ERRORS

COPY INTO option that validates staged files without loading data; RETURN_ERRORS returns all error messages, while RETURN_n_ROWS returns the first n error rows.

ON_ERROR SKIP_FILE

COPY INTO error-handling option that skips an entire file when any of its rows fails, continuing with the remaining files instead of aborting the whole statement.

MATCH_BY_COLUMN_NAME

COPY INTO option, CASE_SENSITIVE or CASE_INSENSITIVE, that loads Parquet, Avro, ORC, or JSON fields into table columns by name rather than by position.

Snowpipe auto_ingest

Pipe setting that uses cloud event notifications such as SQS, GCS Pub/Sub, or Azure Event Grid to trigger loading of newly staged files for near-real-time serverless ingestion.

PURGE copy option

When PURGE=TRUE, COPY INTO deletes staged files after they load successfully, preventing duplicate reloads but removing the staged copy of the data.

Stream metadata columns

A stream exposes METADATA$ACTION for insert or delete, METADATA$ISUPDATE, and METADATA$ROW_ID so consumers can identify the change type and row identity of each record.

MERGE statement

Single statement that inserts, updates, and deletes rows in a target table by joining it to a source on a condition using WHEN MATCHED and WHEN NOT MATCHED clauses.

LATERAL FLATTEN

Table function that explodes a VARIANT array or object into one row per element, commonly called as LATERAL FLATTEN(input => column) to normalize semi-structured data.

Dynamic table target lag

TARGET_LAG sets the maximum acceptable staleness of a dynamic table relative to its sources; Snowflake schedules refreshes automatically to meet that freshness target.

Append-only stream

Stream type that records only inserted rows and ignores updates and deletes, used for insert-only sources such as tables loaded by Snowpipe.

NOT NULL constraint

The only Snowflake constraint enforced on insert and update; PRIMARY KEY, FOREIGN KEY, and UNIQUE are declared for metadata and optimizer hints but are not enforced.

Zero-copy clone

CREATE ... CLONE builds a new object sharing the source's micro-partitions, so no data is copied and the operation is instant; the objects diverge only after modification.

Micro-partition

Immutable columnar storage unit of a Snowflake table, roughly 50 to 500 MB uncompressed, whose per-column min and max metadata enables pruning during scans.

Transient table Fail-safe

Transient tables allow Time Travel up to one day but have no Fail-safe period, reducing storage cost while removing recovery after Time Travel expires.

Clustering key limit

A clustering key may contain up to four columns or expressions; Snowflake stores their per-partition min and max values to enable pruning on range and equality filters.

Result cache

Query results retained for 24 hours and reused when identical query text runs against unchanged data; reuse requires no running warehouse and consumes no credits.

Clustering depth

Average number of overlapping micro-partitions per clustering-key value; a depth close to one indicates well-clustered data and effective partition pruning.

Local disk spill

Query Profile metric showing bytes spilled to local disk by an operator, which signals insufficient warehouse memory; a larger warehouse size usually reduces or eliminates it.

Multi-cluster warehouse

Scale-out configuration that adds clusters up to a set maximum when queued queries exceed a target, improving concurrency rather than the latency of a single query.

Search optimization service

Serverless feature that builds a search access path so equality, IN, and substring predicates avoid full table scans on highly selective point lookups.

Masking policy

Column-level security object whose expression evaluates per role and returns a masked value when the role fails the condition, for example revealing only the last four digits.

Row access policy

Policy attached to a table or view that returns a boolean expression evaluated for each row and role, so users see only rows satisfying the predicate.

Access history

ACCOUNT_USAGE.ACCESS_HISTORY records, per query, which objects and columns were read and written, supporting data lineage and audit of access.

Tri-Secret Secure

Snowflake feature that combines a Snowflake-managed key, a customer-managed key in a cloud KMS, and a customer-held secret, letting the customer revoke access.

Secure data sharing

Capability that lets a provider grant a share so another account queries live provider data without copying it; the provider pays storage and the consumer pays compute.

Reader account

Account created and paid for by a provider, with no Snowflake subscription of its own, that can consume the provider's shares and is managed by the provider.

CREATE DATABASE FROM SHARE

Consumer-side command that creates a read-only database from a share; its objects cannot be modified or cloned, only queried and used in consumer views.

Marketplace listing

A packaged share published publicly or privately for consumers to discover and attach; free listings attach immediately and paid listings are billed through Snowflake Marketplace.

Serverless task

Task created without a WAREHOUSE parameter so Snowflake provisions and scales compute automatically, billing credits based on actual usage of each run.

Task DAG

Directed acyclic graph of tasks where the root task holds the schedule and child tasks run after predecessors succeed, often guarded by stream-has-data conditions.

CREATE ALERT

Object with a schedule, a CONDITION query that fires when it returns a row, and an ACTION statement, used for threshold monitoring inside Snowflake.

SYSTEM$STREAM_HAS_DATA

Function returning true when a stream holds unconsumed change records; placed in a task WHEN clause it skips runs when no new data exists.