SnowPro Advanced Data Engineer study plan
SnowPro Advanced Data Engineer · Give each week a clear focus.
8 weeks · About 7 hours per week
Data Movement (Data Loading and Unloading)
Configure internal and external stages with directory tables, encryption options, and appropriate file formats
Write COPY INTO statements that apply pattern matching, file-format options, validation mode, and error-handling parameters
Differentiate full-refresh versus incremental/continuous loading patterns and select the correct one for a given source
Diagnose and correct common load failures such as schema mismatches, truncated columns, and duplicate-file suppression
Your checkpoint
A decision table mapping eight source scenarios (batch file, streaming, CDC, semi-structured) to stage type, file format, COPY options, and failure-handling settings, with each row justified in one sentence
Data Movement (Data Loading and Unloading)
Unload query results to internal and external stages using COPY INTO with partitioning, single-file, and header options
Compare Snowpipe, Snowpipe Streaming, and scheduled COPY for latency, cost, and operational trade-offs
Set up file-format definitions that handle CSV delimiters, NULL handling, compression, and semi-structured variants correctly
Measure load throughput and identify which parameters to tune when ingestion is slower than target
Your checkpoint
A written 12-question self-test on data loading and unloading, scored, with every missed item rewritten as a corrected rule statement
Data Transformation
Write transformation SQL using CTEs, window functions, QUALIFY, and PIVOT/UNPIVOT to reshape raw loads into modeled outputs
Flatten and query VARIANT data with LATERAL FLATTEN, path navigation, and type casting
Implement deduplication and change-application logic using MERGE and streams
Choose between SQL, JavaScript, and Python UDFs and justify the choice on performance and maintainability
Your checkpoint
A set of five transformation scripts (dedupe, slowly changing dimension, flatten-to-relational, aggregation, incremental merge) executed successfully against a sample dataset with expected row counts documented
Data Transformation & Performance Tuning and Optimization
Optimize transformation queries by eliminating exploding joins, reducing shuffle, and pruning columns early
Read query profiles to identify the dominant operator and propose a concrete fix for each of five bottleneck types
Apply clustering keys and search optimization appropriately and explain when each is counterproductive
Select warehouse size and multi-cluster settings that match a stated concurrency and workload shape
Your checkpoint
A one-page tuning report on three slow queries showing before/after query profiles, the change applied, and measured improvement in runtime or bytes scanned
Performance Tuning and Optimization & Data Modeling and Schema Design
Design schemas that choose between normalized, star, and wide denormalized models based on query access patterns
Implement key constraints, clustering, and micro-partition pruning that align with the chosen model
Compare table types (permanent, transient, temporary) and select one for each of several retention and cost scenarios
Evaluate materialized views and dynamic tables as alternatives to repeated batch computation
Your checkpoint
A schema design document for a supplied business scenario containing an entity diagram, chosen table types, clustering keys, and a written defense of each modeling decision
Data Governance and Security
Configure role hierarchies and object privileges following least-privilege principles for a multi-team account
Apply masking policies, row access policies, and secure views to protect sensitive columns without breaking consumers
Set up tag-based governance, data classification, and access auditing for a defined data set
Configure network policies, key-pair authentication, and account-level security controls
Your checkpoint
An implemented governance blueprint in a trial account: roles created, masking and row access policies applied, tags assigned, and a verification query proving unauthorized roles see masked or zero rows
Data Pipelines and Orchestration
Build a task DAG with dependencies, scheduling, and error notification that chains ingestion through transformation
Implement idempotent, restartable pipeline steps that tolerate partial failure and reprocessing
Compare tasks, dynamic tables, and external orchestrators on dependency handling, latency, and cost
Instrument pipelines with logging and alerting so failures are detected and attributable to a step
Your checkpoint
A working end-to-end pipeline in a trial account (stage to raw to transformed to consumption) with a task DAG, an intentional failure injected and recovered, and a runbook describing the recovery steps
Data Sharing and Collaboration & Data Movement (Data Loading and Unloading) & Data Transformation & Performance Tuning and Optimization
Set up a share and a reader account or direct share, then apply secure views and consumer-side filtering
Differentiate listings, data exchange, and Snowflake Marketplace use cases and select the right one per scenario
Re-drill weak areas by re-attempting every previously missed item across loading, transformation, and tuning
Complete a timed full-scope practice set and classify every error by root cause
Your checkpoint
A final error log across all seven domains with root causes and corrected rules, plus a passing score on a timed full-scope practice set
Milestones
Week 2: Explain, without notes, the difference between Snowpipe, Snowpipe Streaming, and scheduled COPY, and correctly select one for four given latency and cost constraints
Week 4: Read an unfamiliar query profile and name the bottleneck plus a specific remediation within five minutes
Week 6: Demonstrate that a low-privilege role sees masked values and zero unauthorized rows while an analyst role sees permitted data
Week 8: Score above 80 percent on a timed full-scope practice set with every missed item logged and corrected