SnowPro Advanced: Data Engineer Complete Guide

The SnowPro Advanced: Data Engineer Certification validates advanced expertise in building, optimizing, and operating data pipelines on Snowflake. It focuses on topics that map directly to day-to-day data engineering work, including Streams, Tasks, Snowpipe, Dynamic Tables, and semi-structured data. This article covers the exam overview, scoring domains, key study points for each topic, and a strategy to pass.

Exam Overview

Exam Domains and Weighting

Streams & Tasks

Streams are Snowflake's Change Data Capture (CDC) feature, tracking INSERT/UPDATE/DELETE operations against a table. Combined with Tasks, you can build schedule-based data pipelines.

Tasks execute SQL on a schedule (CRON expression or minute-based interval) or in response to a Stream containing data (SYSTEM$STREAM_HAS_DATA). You can define parent-child relationships as a task tree to build pipelines in a DAG (directed acyclic graph) structure.

Snowpipe & Snowpipe Streaming

Snowpipe is a serverless data ingestion feature that detects new files arriving in a stage and automatically runs COPY INTO.

• Auto-ingest Snowpipe: Auto-ingest triggered by cloud event notifications (AWS SQS, Azure Event Grid, GCS Pub/Sub)
• REST API Snowpipe: Manually triggered by calling the insertFiles REST API
• Snowpipe Streaming: Row-level streaming ingestion using the Snowflake Ingest SDK (no files required, low latency)

Dynamic Tables

Dynamic Tables let you define data pipelines declaratively on Snowflake. You define the target table with a SQL query, and it automatically refreshes as the source data changes.

• Control refresh cadence with the TARGET_LAG parameter (e.g. 1 MINUTE / 1 HOUR / DOWNSTREAM)
• Specifying DOWNSTREAM aligns the refresh with the lag of downstream Dynamic Tables
• You can chain multiple Dynamic Tables to build a medallion architecture (Bronze → Silver → Gold)
• Simpler to define and maintain pipelines compared to Streams + Tasks

Semi-structured Data

Snowflake stores semi-structured data such as JSON, XML, Avro, Parquet, and ORC in the VARIANT type, and you can query it with SQL.

• Storage: Convert with PARSE_JSON and store in VARIANT / OBJECT / ARRAY types
• Access: Dot notation (col:key) or bracket notation (col['key'])
• Flattening: Expand nested arrays into rows with the FLATTEN function combined with LATERAL JOIN
• Schema detection: Auto-infer the schema of files on a stage with the INFER_SCHEMA function
• Type casting: Cast values inside a VARIANT to a specific type with the :: operator (e.g. col:price::NUMBER)

Performance Optimization Tips

Study Strategy

The Data Engineer exam tests more practical knowledge than the Core exam, so hands-on study is essential.

• Build an actual Streams + Tasks pipeline and verify how SYSTEM$STREAM_HAS_DATA behaves
• Create Dynamic Tables and experiment with TARGET_LAG behavior
• Set up Snowpipe auto-ingest with S3 event notifications and check pipe status
• Store JSON in the VARIANT type and write queries that expand it with FLATTEN + LATERAL JOIN
• Learn how to check spilling and pruning rates in Query Profile

Data Protection Essentials

• Time Travel: Query historical data for up to 90 days on Enterprise Edition. Restore or reference via UNDROP TABLE or SELECT ... AT(TIMESTAMP =>) syntax
• Fail-safe: A 7-day Snowflake-managed backup after the Time Travel period ends. Not accessible to users
• Zero-Copy Clone: Copy only metadata via CREATE TABLE ... CLONE syntax. Storage costs are incurred only for changed data
• Data encryption: Transparent AES-256 encryption that is always on with no user action required

Try a Sample Question

Frequently Asked Questions