Certbie

Free practice · No sign-up required

SnowPro Advanced: Data Engineer free practice test

15 questions with answer explanations. Choose an answer below to begin.

Question 1 of 15

Which capability best describes Snowflake’s automatic clustering?

Show hint

Understand data organization and performance optimization features.

0/15 answered · Bookmark questions to review later. Focus the practice area to use keys 1–9 and Enter.

Learn by doing

Put your answers into practice

Use official documentation and hands-on labs to work through topics you missed.

Explore all study resources →

All questions and explanations

Prefer to read at your own pace? Work through each question below, then open its answer to check your reasoning. You can link directly to a question or print this reference.

  1. Question 1

    Link to question

    Which capability best describes Snowflake’s automatic clustering?

    1. It automatically organizes data to optimize query performance based on clustering keys.

    2. It replicates data across regions for disaster recovery.

    3. It converts relational tables to semi-structured formats.

    4. It schedules periodic data backups.

    Show answer and explanation

    Correct answer

    • It automatically organizes data to optimize query performance based on clustering keys.

    Automatic clustering helps maintain optimal data layout by organizing rows based on clustering keys, improving query efficiency. It does not handle replication, format conversion, or backup scheduling.

  2. Question 2

    Link to question

    When using Snowflake Time Travel, what is the primary purpose of the BEFORE parameter?

    1. To query data as it existed at a previous point in time.

    2. To permanently delete historical data.

    3. To accelerate data loading into tables.

    4. To change the current schema of a table.

    Show answer and explanation

    Correct answer

    • To query data as it existed at a previous point in time.

    The BEFORE parameter allows you to reference a prior state for querying or restoring, supporting point-in-time analysis. It does not delete, load, or alter schema.

  3. Question 3

    Link to question

    Which statement is true regarding Snowflake’s zero-copy cloning?

    1. Cloning creates a new copy of data without immediate storage overhead.

    2. Cloning requires a full data export and import cycle.

    3. Cloning can only be performed on databases, not schemas or tables.

    4. Cloning increases storage costs immediately upon creation.

    Show answer and explanation

    Correct answer

    • Cloning creates a new copy of data without immediate storage overhead.

    Zero-copy cloning leverages Snowflake’s architecture to share data blocks until changes occur, minimizing storage use. It supports databases, schemas, and tables and does not require export/import.

  4. Question 4

    Link to question

    What is the main benefit of using Snowflake’s external functions?

    1. They allow invoking external services or user-defined code within SQL queries.

    2. They replace the need for stored procedures entirely.

    3. They automatically optimize query execution plans.

    4. They provide built-in encryption for all data in transit.

    Show answer and explanation

    Correct answer

    • They allow invoking external services or user-defined code within SQL queries.

    External functions enable integration with external services or custom code, extending Snowflake’s processing capabilities. They do not replace stored procedures or handle encryption or optimization directly.

  5. Question 5

    Link to question

    Which feature helps ensure that sensitive data is masked based on user roles?

    1. Dynamic Data Masking

    2. Row Access Policies

    3. Network Policies

    4. Session Parameters

    Show answer and explanation

    Correct answer

    • Dynamic Data Masking

    Dynamic Data Masking obscures sensitive data in query results depending on the role of the user. Row Access Policies control row visibility, while network and session policies address connectivity and settings.

  6. Question 6

    Link to question

    What does the use of a virtual warehouse primarily enable in Snowflake?

    1. On-demand compute resources for query processing and data loading.

    2. Permanent storage of historical query logs.

    3. Direct integration with third-party BI tools.

    4. Automatic schema evolution for incoming data.

    Show answer and explanation

    Correct answer

    • Permanent storage of historical query logs.

    Virtual warehouses provide scalable compute resources that can be started or stopped as needed, supporting queries and data loading. They do not store logs, integrate BI tools, or evolve schemas automatically.

  7. Question 7

    Link to question

    Which approach is recommended for optimizing query performance on large datasets?

    1. Use clustering keys and appropriate data types.

    2. Store all data in a single table to reduce complexity.

    3. Disable result caching for all queries.

    4. Avoid using stages for data loading.

    Show answer and explanation

    Correct answer

    • Use clustering keys and appropriate data types.

    Clustering keys help organize data efficiently, and suitable data types reduce storage and improve access speed. The other options generally degrade performance or usability.

  8. Question 8

    Link to question

    What is a common use case for Snowflake Streams?

    1. Tracking changes to data for incremental processing.

    2. Encrypting data at rest automatically.

    3. Replacing traditional database indexes.

    4. Managing user authentication.

    Show answer and explanation

    Correct answer

    • Tracking changes to data for incremental processing.

    Streams capture data manipulation changes, enabling incremental ETL and CDC patterns. They do not handle encryption, indexing, or authentication.

  9. Question 9

    Link to question

    Which statement correctly describes Snowflake’s architecture separation?

    1. Storage and compute are independent and can be scaled separately.

    2. Compute and network layers are tightly coupled.

    3. Storage is always tied to a specific virtual warehouse.

    4. Scaling storage requires scaling compute at the same time.

    Show answer and explanation

    Correct answer

    • Storage and compute are independent and can be scaled separately.

    Snowflake’s architecture decouples storage and compute, allowing independent scaling of each resource. This design avoids forced co-scaling and tight coupling.

  10. Question 10

    Link to question

    When would you use a Snowflake task automation?

    1. To schedule and chain SQL statements or procedures based on time or result conditions.

    2. To permanently store large volumes of unstructured data.

    3. To manage network firewall rules.

    4. To configure user permissions across roles.

    Show answer and explanation

    Correct answer

    • To schedule and chain SQL statements or procedures based on time or result conditions.

    Tasks enable workflow automation by scheduling or conditionally executing SQL and procedures. They do not store data, manage networks, or configure permissions.

  11. Question 11

    Link to question

    Which data format is natively supported for loading into Snowflake?

    1. CSV, JSON, Avro, ORC, and Parquet.

    2. Only CSV and fixed-width text files.

    3. Executable binaries and compiled code.

    4. Graph databases and adjacency lists.

    Show answer and explanation

    Correct answer

    • CSV, JSON, Avro, ORC, and Parquet.

    Snowflake supports a wide variety of structured and semi-structured formats including CSV, JSON, Avro, ORC, and Parquet. It does not natively support binaries or graph-specific formats.

  12. Question 12

    Link to question

    What is the purpose of a Snowflake stage?

    1. A temporary holding area for data files during loading or unloading.

    2. A permanent backup storage location.

    3. A tool for rewriting SQL queries automatically.

    4. A method to compress data within tables.

    Show answer and explanation

    Correct answer

    • A temporary holding area for data files during loading or unloading.

    Stages act as intermediate locations for data files being loaded into or unloaded from tables. They are not backups, query rewriters, or compression tools.

  13. Question 13

    Link to question

    Which practice helps maintain data consistency during concurrent writes?

    1. Using transactions and appropriate isolation levels.

    2. Disabling clustering to reduce overhead.

    3. Increasing virtual warehouse size only.

    4. Avoiding the use of streams.

    Show answer and explanation

    Correct answer

    • Using transactions and appropriate isolation levels.

    Transactions and isolation levels manage concurrent access and maintain consistency. The other options do not directly address write consistency.

  14. Question 14

    Link to question

    What does the Result Cache improve in Snowflake?

    1. Query performance by reusing results of identical queries.

    2. Data durability across regions.

    3. The physical storage structure of tables.

    4. The security of user credentials.

    Show answer and explanation

    Correct answer

    • Query performance by reusing results of identical queries.

    The Result Cache stores query outputs to avoid redundant computation for repeated queries, speeding up response times. It does not affect durability, storage, or credentials.

  15. Question 15

    Link to question

    Which statement best describes Snowflake’s support for semi-structured data?

    1. It allows querying nested data types like VARIANT and OBJECT directly.

    2. It requires all semi-structured data to be flattened before storage.

    3. It only supports semi-structured data in external stages.

    4. It converts semi-structured data into fixed schemas automatically.

    Show answer and explanation

    Correct answer

    • It allows querying nested data types like VARIANT and OBJECT directly.

    Snowflake natively supports VARIANT and similar types, enabling direct querying of nested and semi-structured data. Flattening is optional, and conversion is not automatic.

Read all flashcards

Recall the answer before opening each card.

What is the primary benefit of automatic clustering?

It continuously organizes data based on clustering keys to optimize query performance without manual intervention, reducing scan overhead and improving efficiency.

How does the BEFORE parameter work in Time Travel?

The BEFORE parameter lets you reference data state prior to a specific statement or timestamp, enabling point-in-time querying or restoration.

What storage advantage does zero-copy cloning provide?

It creates a writable clone that shares underlying data blocks with the source until modifications occur, minimizing immediate storage consumption.

What is the function of external functions in Snowflake?

External functions allow SQL queries to call external services or custom code, extending Snowflake's capabilities beyond built-in operations.

Which feature masks sensitive data based on user roles?

Dynamic Data Masking obscures column values in query results depending on the role of the user executing the query.

What is the actual role of a virtual warehouse?

A virtual warehouse provides scalable compute resources for queries, data loading, and DML operations; it does not permanently store data or logs.

What are two recommended practices for optimizing queries on large datasets?

Use clustering keys to organize data and choose appropriate data types to reduce storage and improve access speed.

What is the primary use case for Snowflake Streams?

Streams capture insert, update, and delete changes for incremental processing, such as ETL and change data capture (CDC) pipelines.

What is the benefit of separating storage and compute in Snowflake?

It allows independent scaling of storage and compute resources, enabling cost-effective sizing and concurrency without affecting each other.

When would you use a Snowflake task?

To schedule and chain SQL statements or stored procedures based on time schedules or conditional results, automating workflows.

Which file formats are natively supported for data loading into Snowflake?

CSV, JSON, Avro, ORC, and Parquet are supported for efficient loading of structured and semi-structured data.

What is the purpose of a Snowflake stage?

A stage is a temporary storage location for data files before loading into tables or after unloading, facilitating data movement.

How can you maintain data consistency during concurrent writes?

Use transactions with appropriate isolation levels to ensure that concurrent writes do not create data anomalies or corruption.

What does the Result Cache improve in Snowflake?

The Result Cache stores outputs of queries so that identical subsequent queries are served from cache, reducing compute time and improving performance.

How does Snowflake support querying semi-structured data?

Snowflake supports VARIANT and OBJECT types natively, allowing direct querying of nested data using dot notation without mandatory flattening.

What is the difference between a stream and a task?

A stream records changes (CDC) on a table; a task executes SQL on a schedule or when a stream has data. Tasks often consume streams for incremental processing.

How does zero-copy cloning affect storage cost?

Zero-copy cloning shares data blocks until changes, so storage cost increases only after modifications are made to either source or clone.

What is the purpose of the AT clause in Time Travel?

AT allows querying data as it existed at a specific timestamp or offset, similar to BEFORE but with precise time points.

What role does a masking policy play in Dynamic Data Masking?

A masking policy defines how a column's data is presented to users based on their role, enforcing column-level security.

Why is it important to avoid repeating the disputed answer for virtual warehouses?

The marked answer claimed virtual warehouses store logs, but the explanation clarifies they provide compute, not permanent storage. Accurate understanding is essential for the exam.

Active recall deck

Practice SnowPro Advanced: Data Engineer with real flashcards

Read the prompt, commit to an answer, then flip the card. Move through the deck at your own pace and repeat any topic that does not come back quickly.

20 free cards

Card 1 of 20

1 reviewed this session

Connect the concepts

Your SnowPro Advanced: Data Engineer study mind map

Open a branch to connect a topic with its practice questions and explanations. Use the map to choose your next review topic.

SnowPro Advanced: Data Engineer15 questions in this practice set
Understand data organization and performance optimization features.(1)
Use Time Travel to access historical data states.(1)
Understand efficient data duplication methods.(1)
Extend Snowflake capabilities using external functions.(1)
Implement data security and privacy controls.(1)
Understand compute-resource management with virtual warehouses.(1)
Apply performance tuning best practices.(1)
Use streams for change data capture and incremental processing.(1)
Understand the benefits of storage-compute separation.(1)
Automate workflows using task scheduling and dependencies.(1)
Load data efficiently using supported file formats.(1)
Use stages for data movement into and out of Snowflake.(1)
More practice connections(3)
Exam-focused analysis

What this DEA-C02 practice set measures

This is an analysis of the practice bank, not a claim about the vendor's live exam blueprint. Use it to identify the knowledge, judgment, and recall patterns exercised here, then verify your coverage against the current official exam guide.

Data Performance and Optimization

This practice bank assesses your ability to optimize data storage and query performance. Questions on automatic clustering, clustering keys, appropriate data types, and result caching appear. Automatic clustering organizes data based on clustering keys to maintain query performance. The use of clustering keys and suitable data types reduces storage and improves access speed. The Result Cache avoids redundant computation for repeated queries. These features are critical for managing large datasets efficiently.

  • Automatic clustering maintains data layout based on clustering keys.
  • Clustering keys and proper data types optimize query performance on large datasets.
  • Result Cache improves response times for repeated identical queries.

Data Security and Access Control

Security is a key responsibility for data engineers. The practice bank covers Dynamic Data Masking, which obscures sensitive data based on user roles. Row Access Policies control row-level visibility, while network and session policies address connectivity. Understanding these controls ensures compliance and data privacy. Also, the concept of Stage as a temporary holding area for data files is related to secure data movement.

  • Dynamic Data Masking masks data in query results per role.
  • Row Access Policies filter rows based on user attributes.
  • Stages are temporary locations for data loading/unloading.

Data Architecture and Compute Management

Snowflake's separation of storage and compute is fundamental. The virtual warehouse provides scalable compute resources, not storage of logs. Zero-copy cloning shares data blocks until changes, minimizing storage. Time Travel allows querying historical data states. Architecture independence means storage and compute can scale separately. These concepts enable cost-effective and flexible data management.

  • Storage and compute are decoupled for independent scaling.
  • Zero-copy cloning shares data blocks until modification.
  • Virtual warehouses provide compute resources, not permanent storage.

Data Integration and Automation

Automation and integration are vital for data pipelines. Tasks schedule and chain SQL statements or procedures. Streams capture changes for incremental processing. External functions invoke external services within SQL. Semi-structured data like VARIANT and OBJECT can be queried directly. The stage is used for loading/unloading data. These tools help build robust ETL and CDC workflows.

  • Tasks automate SQL or procedure execution based on time or conditions.
  • Streams track DML changes for incremental processing.
  • External functions integrate external services into SQL queries.

Study workflow

Turn one DEA-C02 attempt into a study plan

  1. 1

    Optimize Queries with Clustering Keys

    Define clustering keys on large tables to group related rows together. Use automatic clustering to maintain this organization. This reduces the amount of data scanned per query, improving performance. Monitor clustering depth and recluster if needed.

  2. 2

    Implement Dynamic Data Masking

    Identify sensitive columns in your tables. Create masking policies that conditionally reveal data based on user roles. Apply these policies to columns. Test with different roles to ensure the correct level of masking. This protects sensitive data without altering underlying storage.

  3. 3

    Use Time Travel for Point-in-Time Recovery

    Set appropriate data retention time for tables (up to 90 days). Use the BEFORE or AT clause to query or restore data as it existed at a specific timestamp or before a statement. This enables recovery from accidental changes or analysis of historical states.

  4. 4

    Automate Data Pipelines with Tasks and Streams

    Create streams on source tables to capture insert, update, and delete operations. Define tasks that run on a schedule or when a stream contains data. Chain tasks to execute SQL statements or stored procedures. This enables incremental data processing and automation.

  5. 5

    Leverage Zero-Copy Cloning for Testing

    To create test environments, clone databases, schemas, or tables using the CLONE command. This creates a writable copy that shares underlying data until modifications occur. No additional storage is used until changes are made. This is ideal for refreshing development or testing without extra cost.

FAQ

Questions about this DEA-C02 practice page

Clear boundaries on what the bank covers, how to use it, and where official vendor information still matters.

How does automatic clustering differ from manual clustering in Snowflake?+

Automatic clustering continuously maintains the optimal data layout based on clustering keys by reorganizing rows, while manual clustering requires running manual recluster statements. Automatic clustering reduces administrative overhead and ensures consistent performance without user intervention.

What is the difference between a stream and a task in Snowflake?+

A stream records changes (inserts, updates, deletes) to a table as a change data capture (CDC) mechanism. A task is a scheduler that executes SQL, including consuming stream data. Tasks can be set to run before a stream's change tracking offset advances, enabling incremental processing.

Can you use external functions to call AWS Lambda from Snowflake?+

Yes. External functions allow you to invoke external services, including AWS Lambda, Azure Functions, or Google Cloud Functions, from within SQL. You define a remote service and a Snowflake API integration, then call the function in queries to extend Snowflake's capabilities.

What is the purpose of the BEFORE parameter in Time Travel?+

The BEFORE parameter is used to query or restore data as it existed at a point in time before a specific statement or timestamp. For example, you can use BEFORE (statement => 'xxxx') to see the state prior to a delete. This enables point-in-time recovery and analysis.

How does zero-copy cloning save storage costs?+

Zero-copy cloning creates a separate logical copy of a database, schema, or table without physically duplicating the underlying micro-partitions. Storage is shared between the source and clone until modifications are made, meaning no additional storage cost until changes occur. This is efficient for testing and development.

Keep studying

Build the next review session

Browse another free bank or use the study strategy guide to turn your misses into spaced review.

All free testsStudy strategy