SnowPro Advanced Data Engineer cheat sheet

COPY INTO

FROM @stage [FILE_FORMAT=...] loads staged files; ON_ERROR accepts CONTINUE, SKIP_FILE, SKIP_FILE_, or ABORT_STATEMENT (default).

VALIDATION_MODE=RETURN_ERRORS (default) or RETURN__ROWS validates staged files and returns errors without loading any rows.

PURGE=TRUE deletes staged files after a successful load; FORCE=TRUE reloads files already recorded in the table's load metadata.

MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE|CASE_SENSITIVE maps Parquet, Avro, ORC, and JSON fields to columns by name instead of position.

CREATE TABLE ... USING TEMPLATE combined with INFER_SCHEMA derives column names and types from a set of staged files.

Useful load-time metadata columns: METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, METADATA$FILE_LAST_MODIFIED, METADATA$START_SCAN_TIME.

Snowpipe loads files continuously; auto_ingest relies on cloud event notifications (SQS, Pub/Sub, Event Grid), while the REST insertFiles endpoint pushes paths directly.

COPY INTO @stage FROM

unloads data and supports SINGLE, MAX_FILE_SIZE, PARTITION BY, HEADER, OVERWRITE, and DETAILED_OUTPUT.

PUT uploads to internal stages (user, named, table stage) and GET downloads from them; neither command works against external stages.

Streams capture DML on tables, views, external tables, and directory tables; a standard stream records inserts, updates, and deletes for consumption.

MERGE applies INSERT, UPDATE, and DELETE in one statement through WHEN MATCHED and WHEN NOT MATCHED [BY SOURCE | BY TARGET] clauses.

LATERAL FLATTEN explodes a VARIANT array or object into rows; project attributes with f.value:field::type.

Dynamic tables declare a query plus TARGET_LAG; Snowflake schedules incremental or full refreshes to keep data within that freshness bound.

PIVOT rotates rows into columns and UNPIVOT reverses it; both are SELECT clauses, not standalone statements.

UDFs (SQL, JavaScript, Python, Java) and UDTFs execute inside Snowflake, while Snowpark DataFrames push transformation logic to warehouse compute.

CREATE TABLE ... AS SELECT (CTAS) materializes a transformation; INSERT OVERWRITE replaces a table's contents atomically.

QUALIFY filters on window-function results without a subquery, and recursive CTEs handle hierarchical or iterative transformations.

PRIMARY KEY, FOREIGN KEY, and UNIQUE are declarative only; NOT NULL is enforced. RELY constraints can still inform the optimizer.

Micro-partitions are immutable, columnar, and roughly 50-500 MB uncompressed; each stores per-column min/max values used for pruning.

A clustering key holds up to four columns or expressions; ALTER TABLE ... CLUSTER BY redefines it and automatic clustering maintains order.

Table types: permanent (Time Travel plus 7-day Fail-safe), transient (Time Travel up to 1 day, no Fail-safe), temporary (session-scoped), hybrid (row-level OLTP).

Zero-copy cloning (CREATE ... CLONE) copies metadata only; the clone shares the source's micro-partitions until either side is modified.

Use VARIANT for semi-structured payloads with ARRAY and OBJECT nested types; access elements with dot notation, brackets, and FLATTEN.

Schema evolution options include ALTER TABLE ADD/DROP COLUMN and ENABLE_SCHEMA_EVOLUTION=TRUE on COPY INTO, which adds missing columns automatically.

Type choices matter: NUMBER(38,0) for integers, VARCHAR up to 16 MB, and TIMESTAMP_LTZ/NTZ/TZ chosen explicitly because TIMESTAMP defaults to NTZ.

Pruning relies on selective filters over clustering columns; wrapping a filter column in a function or cast defeats micro-partition pruning.

The result cache serves identical query text for 24 hours without a running warehouse and is invalidated when underlying data changes.

Warehouse (local disk) cache survives only while the warehouse runs; suspended warehouses lose it and re-read from storage.

Scale up (larger warehouse) for memory-hungry queries and spilling; scale out (multi-cluster) for concurrency and queuing.

Query Profile: compare partitions scanned against total partitions, bytes spilled to local and remote disk, and per-operator time to locate bottlenecks.

SYSTEM$CLUSTERING_INFORMATION reports clustering depth and key columns; ALTER TABLE ... RECLUSTER or automatic clustering restores order after heavy DML.

The search optimization service accelerates equality, IN, and substring predicates and adds serverless maintenance cost to the table.

Materialized views precompute results and are maintained by Snowflake serverless compute; Enterprise Edition is required and the optimizer may rewrite queries to use them.

The query acceleration service offloads scan-heavy portions of eligible queries to shared compute to reduce warehouse time.

Access control is discretionary: privileges are granted to roles, roles are granted to other roles, and a session activates exactly one role at a time.

Masking policies attach to columns and return a value based on CURRENT_ROLE(); the policy expression must return the column's data type.

Row access policies return a boolean evaluated per row and are attached to tables and views; the policy owner bypasses the filter.

Object tagging enables tag-based masking, and tags can be inherited from database or schema down to tables and columns.

Secure views hide the view definition and prevent exposure of underlying objects through the optimizer.

Network policies allow or block client IP ranges, while MFA enrollment and password policies strengthen authentication.

Key-pair authentication suits service users and integrations; OAuth and SAML SSO cover application and human access.

ACCOUNT_USAGE.ACCESS_HISTORY records the objects and columns read and written per query, supporting lineage and audit reporting.

All data is encrypted at rest with AES-256 and in transit with TLS; Tri-Secret Secure adds a customer-held key that enables revocation.

CREATE SHARE, GRANT SELECT/USAGE on the objects to the share, then ALTER SHARE ... ADD ACCOUNTS to publish it to consumers.

Consumers run CREATE DATABASE FROM SHARE <provider_account>.<share_name>; the resulting database is read-only.

A share can contain tables, secure views, secure UDFs, external tables, and Iceberg tables; data is never copied to the consumer.

Providers pay storage for shared data and pay compute for reader accounts; consumers of direct shares pay their own compute.

Consumers cannot clone shared objects, run DML on them, or see provider objects that were not granted in the share.

Cross-region or cross-cloud sharing requires replicating the share to an account in the consumer's region before it can be consumed.

Marketplace listings package shares for public or private discovery; paid listings are billed through Snowflake Marketplace.

Expose only a subset of rows or columns through secure views rather than granting base tables to a share.

Tasks run SQL, stored procedures, or Snowpark code; SCHEDULE accepts minutes or a CRON expression, and AFTER creates the dependency DAG.

Only the root task of a DAG carries a schedule; child tasks run when predecessors succeed and support FINALIZE tasks and error handlers.

Serverless tasks omit the WAREHOUSE parameter, using Snowflake-managed compute billed by usage and suited to short or infrequent work.

WHEN SYSTEM$STREAM_HAS_DATA('stream') skips task runs when a stream has no changes; the task's DML must consume that stream.

Tasks must be resumed with ALTER TASK ... RESUME and the owner needs EXECUTE TASK; a suspended root task halts the whole DAG.

TASK_HISTORY in ACCOUNT_USAGE or INFORMATION_SCHEMA returns run status, error codes, and durations per task run.

Dynamic tables replace stream-plus-task plumbing for declarative pipelines: define the query and TARGET_LAG and Snowflake handles refreshes.

Snowpipe auto-ingest provides event-driven loading that pairs with tasks or dynamic tables for end-to-end ELT.

CREATE ALERT evaluates a CONDITION on a schedule and runs an ACTION statement, enabling in-warehouse threshold monitoring.