[DEA-C01] SnowPro Advanced: Data Engineer Exam Guide

Exam Domain Breakdown

This exam guide includes test domains, weightings, and objectives. It is not a comprehensive listing of all the content that will be presented on this examination. The table below lists the main content domains and their weighting ranges.

1.0 Domain: Data Movement

1.1 Given a data set, load data into Snowflake.

• Outline considerations for data loading
• Define data loading features and potential impact

1.2 Ingest data of various formats through the mechanics of Snowflake.

• Required data formats
• Outline stages

1.3 Troubleshoot data ingestion.

1.4 Design, build, and troubleshoot continuous data pipelines.

• Design a data pipeline that forces uniqueness but is not unique
• Stages
• Tasks
• Streams
• Snowpipe
• Auto ingest as compared to Rest API

1.5 Analyze and differentiate types of data pipelines.

• Understand Snowpark architecture (client vs server)
• Create and deploy UDFs and stored procedures using Snowpark
• Design and use the Snowflake SQL API

1.6 Install, configure, and use connectors to connect to Snowflake.

1.7 Design and build data sharing solutions.

• Implement a data share
• Create a secure view
• Implement row level filtering

1.8 Outline when to use external tables and define how they work.

• Partitioning external tables
• Materialized views
• Partitioned data unloading

2.0 Domain: Performance Optimization

2.1 Troubleshoot underperforming queries.

• Identify underperforming queries
• Outline telemetry around the operation
• Increase efficiency
• Identify the root cause

2.2 Given a scenario, configure a solution for the best performance.

• Scale out as compared to scale in
• Clustering as compared to increasing warehouse size
• Query complexity
• Micro-partitions and the impact of clustering
• Materialized views
• Search optimization service

2.3 Outline and use caching features.

2.4 Monitor continuous data pipelines.

• Snowpipe
• Stages
• Tasks
• Streams

3.0 Domain: Storage and Data Protection

3.1 Implement data recovery features in Snowflake.

• Time Travel
• Fail-safe

3.2 Outline the impact of streams on Time Travel.

3.3 Use system functions to analyze micro-partitions.

• Clustering depth
• Cluster keys

3.4 Use Time Travel and cloning to create new development environments.

• Backup databases
• Test changes before deployment
• Rollback

4.0 Domain: Security

4.1 Outline Snowflake security principles.

• Authentication methods (Single Sign-On (SSO), key authentication, username/password, Multi-Factor Authentication (MFA))
• Role Based Access Control (RBAC)
• Column level security and how data masking works with RBAC to secure sensitive data

4.2 Outline the system defined roles and when they should be applied.

• The purpose of each of the system defined roles including best practices usage in each case
• The primary differences between SECURITYADMIN and USERADMIN roles
• The difference between the purpose and usage of the USERADMIN/SECURITYADMIN roles and SYSADMIN

4.3 Manage data governance.

• Explain the options available to support column level security including Dynamic Data Masking and external tokenization
• Explain the options available to support row level security using Snowflake row access policies
• Use DDL required to manage Dynamic Data Masking and row access policies
• Use methods and best practices for creating and applying masking policies on data
• Use methods and best practices for object tagging

5.0 Domain: Data Transformation

5.1 Define User-Defined Functions (UDFs) and outline how to use them.

• Secure UDFs
• SQL UDFs
• JavaScript UDFs
• Returning table value compared to scalar value

5.2 Define and create external functions.

• Secure external functions

5.3 Design, build, and leverage stored procedures.

• Transaction management

5.4 Handle and transform semi-structured data.

• Traverse and transform semi-structured data to structured data
• Transform structured to semi-structured data

5.5 Use Snowpark for data transformation.

• Query and filter data using the Snowpark library
• Perform data transformations using Snowpark (ie., aggregations)
• Join Snowpark dataframes

Recommended Training

We recommend individuals have at least 2 + years of hands-on Snowflake Practitioner experience in a Data Engineering role prior to attempting this exam. The exam will assess skills through scenario-based questions and real-world examples. As preparation for this exam, we recommend a combination of hands-on experience, instructor-led training, and the utilization of self-study assets.

Instructor-Led Course recommended for this exam:
Snowflake Data Engineering Training

Free Self Study recommended for this exam:
SnowPro Advanced: Data Engineer Study Guide