SnowPro Advanced Data Engineer study guide
SnowPro Advanced Data Engineer · Work through your exam, one topic at a time.
The SnowPro Advanced: Data Engineer certification is Snowflake's advanced-level credential for practitioners who build and operate data pipelines on the Snowflake Data Cloud. It sits above the core SnowPro Core certification and assumes you already know the platform's fundamentals - virtual warehouses, stages, roles, and basic SQL - so the exam focuses on the harder engineering decisions: designing idempotent ingestion with Snowpipe and continuous loading, choosing file formats and copy options that actually perform, building transformation layers with streams, tasks, and Dynamic Tables, modeling schemas for both analytical and semi-structured workloads, and tuning warehouses and queries when volume grows. The SnowPro Advanced Data Engineer study guide has 7 sections and about 2,368 words.
01Data Movement: Loading and Unloading
Loading is stage-centric: COPY INTO <table FROM @stage , while unloading is COPY INTO @stage FROM (SELECT ...) . Internal stages are the user stage ( @~ ), the table stage ( @%tbl ) and named stages, populated with PUT / GET ; external stages point at S3, Azure or GCS through a storage integration.
367 words, about 2 min02Data Transformation
Transformation questions test SQL fluency plus Snowflake-specific constructs: CTEs and recursive CTEs, joins, window functions, QUALIFY , PIVOT / UNPIVOT , MERGE , multi-table INSERT ALL / INSERT FIRST , and semi-structured handling with FLATTEN (comma-joined or LATERAL ), OBJECT CONSTRUCT , ARRAY AGG , and higher-order functions such as TRANSFORM , FILTER and REDUCE . Beyond SQL, know UDFs (SQL, JavaScript, Python, Java), Snowflake Scripting stored procedures, Snowpark DataFrames, and declarative pipelines built with dynamic tables.
326 words, about 2 min03Data Modeling and Schema Design
Design decisions hinge on Snowflake's storage model: tables are stored in immutable, columnar micro-partitions whose natural clustering follows ingestion order and drives pruning. Understand when a clustering key is worth it (large tables with selective, repeated filters) versus harmful (small tables, high-cardinality keys with heavy DML).
346 words, about 2 min04Performance Tuning and Optimization
Optimization questions resolve to a few levers. Warehouse sizing scales up (bigger warehouse, more credits per second, more memory for sorts and joins); multi-cluster warehouses scale out for concurrency.
324 words, about 2 min05Data Governance and Security
Snowflake security is RBAC plus policy objects. Privileges are granted to roles, roles are granted to roles and users, and effective access is the union of all inherited roles; object ownership confers full control and can be transferred.
344 words, about 2 min06Data Sharing and Collaboration
A share is a named, account-level object that grants read-only access to databases, schemas, tables, views and secure UDFs to other accounts or reader accounts. Sharing is zero-copy: no data is duplicated, the provider pays storage, and the consumer pays compute for its own warehouse.
329 words, about 2 min07Data Pipelines and Orchestration
Continuous pipelines combine streams, tasks and Snowpipe. A stream records change data capture on a table or view with metadata columns METADATA ACTION , METADATA ISUPDATE and METADATA$ROW ID ; reading it with DML advances its offset.
332 words, about 2 min