Free Snowflake SnowPro Advanced Data Engineer DEA-C02 practice questions

10 free Snowflake SnowPro Advanced Data Engineer DEA-C02 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 65 questions. Work through them, then open each answer to check your reasoning.

Get all 65 questions (US$39) · Download these 10 as a PDF

Question 1

A Data Engineer is investigating a query that is taking a long time to return. The Query Profile shows the following:

What step should the Engineer take to increase the query performance?

Exhibit for question 1

  1. Add additional virtual warehouses.
  2. Increase the size of the virtual warehouse.
  3. Rewrite the query using Common Table Expressions (CTEs).
  4. Change the order of the joins and start with smaller tables first.
Show answer and explanation

Correct answer: B. Increase the size of the virtual warehouse.

The execution profile shows that Processing (CPU-bound operations) accounts for 77% of the total execution time, with Synchronization contributing 21%. This indicates the virtual warehouse is CPU-constrained and unable to handle the computational workload efficiently. The statistics show 5.61 GB scanned from cache with only 0.81 MB written, and 37.59 GB spilled to local storage, which confirms the warehouse is struggling with memory and compute resources. Increasing the virtual warehouse size provides more CPU cores and memory to handle the processing workload more efficiently, directly addressing the bottleneck.

Why the other options are wrong

  • A. Adding more virtual warehouses helps with concurrency and parallel query execution, not the execution time of a single query.
  • C. Rewriting the query with CTEs does not address the fundamental compute and memory bottleneck revealed by the profile.
  • D. Join order optimization is a query rewrite strategy that may help but is secondary to the fact that the warehouse lacks sufficient compute resources for the current workload.

Question 2

How can the following relational data be transformed into semi-structured data using the LEAST amount of operational overhead?

Exhibit for question 2

  1. Use the TO_JSON function.
  2. Use the PARSE_JSON function to produce a VARIANT value.
  3. Use the OBJECT_CONSTRUCT function to return a Snowflake object.
  4. Use the TO_VARIANT function to convert each of the relational columns to VARIANT.
Show answer and explanation

Correct answer: C. Use the OBJECT_CONSTRUCT function to return a Snowflake object.

Snowflake object. OBJECT_CONSTRUCT is the optimal function for converting relational data into sem-tructured format with minimal overhead. It directly constructs a Snowflake OBJECT (semi-structured data type) by accepting column names and values as key-value pairs, transforming the flat relational structure into a nested hierarchical object in a single operation. This approach requires no intermediate conversions and produces native sem-tructured data that Snowflake can efficiently process.

Why the other options are wrong

  • A. TO_JSON converts data to a JSON string representation rather than a sem-tructured VARIANT or OBJECT type, adding serialization overhead without creating true semi-structured data.
  • B. PARSE_JSON requires input as a string that must first be created or converted, adding unnecessary operational steps compared to directly constructing an object from relational columns.
  • D. TO_VARIANT converts individual columns separately to VARIANT type but does not organize them into a cohesive semi-structured object, requiring additional manual object construction logic afterward.

Question 3

A Data Engineer executes a complex query and wants to make use of Snowflake’s query results caching capabilities to reuse the results.

Which conditions must be met? (Choose three.)

  1. The results must be reused within 72 hours.
  2. The query must be executed using the same virtual warehouse.
  3. The USED_CACHED_RESULT parameter must be included in the query.
  4. The table structure contributing to the query result cannot have changed.
  5. The new query must have the same syntax as the previously executed query.
  6. The micro-partitions cannot have changed due to changes to other data in the table.
Show answer and explanation

Correct answer: D, E, F

D. The table structure contributing to the query result cannot have changed. E. The new query must have the same syntax as the previously executed query. F. The micro-partitions cannot have changed due to changes to other data in the table. Snowflake's query results cache reuses results when three conditions are met: the micr-artitions accessed by the query have not changed due to DML operations (option F), the table structure has not changed (option D), and the query syntax must be identical including whitespace and case (option E). The results are cached for 24 hours, not 72 hours, the same virtual warehouse is not required, and no parameter needs to be explicitly included in the query.

Why the other options are wrong

  • A. Query results are cached for 24 hours, not 72 hours.
  • B. The virtual warehouse does not need to be the same for results to be reused from cache.
  • C. There is no USED_CACHED_RESULT parameter; caching is automatic when conditions are met.

Question 4

A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe.

Which recommendations apply to this scenario? (Choose three.)

  1. Load large files (1 GB or larger).
  2. Ensure that data files are 100-250 MB (or larger) in size, compressed.
  3. Load a single huge array containing multiple records into a single table row.
  4. Verify each value of each unique element stores a single native data type (string or number).
  5. Extract semi-structured data elements containing null values into relational columns before loading.
  6. Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute.
Show answer and explanation

Correct answer: B, D, E

B. Ensure that data files are 100-250 MB (or larger) in size, compressed. D. Verify each value of each unique element stores a single native data type (string or number). E. Extract semi-structured data elements containing null values into relational columns before loading. For optimal Snowpipe performance with JSON data, files should be 100-250 MB or larger and compressed (option B), each unique element should store a single native data type to maintain relational integrity (option D), and semi-structured data containing null values should be extracted into relational columns before loading for better data modeling (option E). Large files over 1 GB are not recommended for Snowpipe, single huge arrays should be unpacked into individual records, and files should be staged regularly rather than less frequently.

Why the other options are wrong

  • A. Large files of 1 GB or larger are not recommended for Snowpipe; smaller batches perform better.
  • C. Multiple records should not be loaded as a single huge array into one row; they should be individual records.
  • F. Files should be staged frequently (more than once per minute if possible), and should be larger than 100 MB for efficiency.

Question 5

Given the table SALES which has a clustering key of column CLOSED_DATE, which table function will return the average clustering depth for the SALES_REPRESENTATIVE column for the North American region?

  1. select system$clustering_information('Sales', 'sales_representative', 'region = ''North America''');
  2. select system$clustering_depth('Sales', 'sales_representative', 'region = ''North America''');
  3. select system$clustering_depth('Sales', 'sales_representative') where region = 'North America';
  4. select system$clustering_information('Sales', 'sales_representative') where region = 'North America’;
Show answer and explanation

Correct answer: B. select system$clustering_depth('Sales', 'sales_representative', 'region = ''North America''');

'sales_representative', 'region = ''North America'''); The system$clustering_depth function returns the average clustering depth for a specified column, and it accepts a WHERE clause filter as part of its syntax to restrict the analysis to a specific region. This is the correct function and syntax for measuring clustering effectiveness for a non-clustering-key column within a filtered subset of data.

Why the other options are wrong

  • A. system$clustering_information exists but does not accept a WHERE clause as a parameter; it returns different information than clustering depth.
  • C. The WHERE clause cannot be applied after the function call; the filter must be part of the function syntax.
  • D. system$clustering_information does not measure clustering depth and does not properly accept region filters in the WHERE clause syntax.

Question 6

A large table with 200 columns contains two years of historical data.

When queried, the table is filtered on a single day. Below is the Query Profile:

Using a size 2XL virtual warehouse, this query took over an hour to complete.

What will improve the query performance the MOST?

Exhibit for question 6

  1. Increase the size of the virtual warehouse.
  2. Increase the number of clusters in the virtual warehouse.
  3. Implement the search optimization service on the table.
  4. Add a date column as a cluster key on the table.
Show answer and explanation

Correct answer: D. Add a date column as a cluster key on the table.

The query profile shows that 60% of execution time is spent on Remote Disk I/O, with only 36% on Processing. The query filters on a single day from a two-year historical dataset with 200 columns. A clustering key on the date column will physically organize the table so that rows from the same day are stored together, dramatically reducing the amount of data that must be scanned from disk. This addresses the primary bottleneck (Remote Disk I/O at 60%) by enabling pruning of irrelevant micro-partitions. While increasing warehouse size (A) provides more compute resources, it does not reduce the volume of data read from storage. The number of clusters (B) is a static warehouse parameter unrelated to this problem. Search optimization service (C) benefits pattern-matching queries, not range filters on dates.

Why the other options are wrong

  • A. Warehouse size increases compute power but does not reduce the 60% Remote Disk I/O bottleneck; the problem is data access volume, not processing speed.
  • B. Warehouse clusters are a fixed configuration property; this option conflates warehouse scaling with table optimization.
  • C. Search optimization service is designed for text pattern matching and SEARCH operations, not efficient date-range filtering on structured data.

Question 7

A Data Engineer is working on a Snowflake deployment in AWS eu-west-1 (Ireland). The Engineer is planning to load data from staged files into target tables using the COPY INTO command.

Which sources are valid? (Choose three.)

  1. Internal stage on GCP us-central1 (Iowa)
  2. Internal stage on AWS eu-central-1 (Frankfurt)
  3. External stage on GCP us-central1 (Iowa)
  4. External stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland)
  5. External stage in an Amazon S3 bucket on AWS eu-central-1 (Frankfurt)
  6. SSD attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland)
Show answer and explanation

Correct answer: C, D, E

C. External stage on GCP us-central1 (Iowa) D. External stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland) E. External stage in an Amazon S3 bucket on AWS eu-central-1 (Frankfurt) The COPY INTO command in a Snowflake account deployed in AWS eu-west-1 can load from: external stages referencing cloud storage in any region including GCP (option C), external stages in Amazon S3 buckets in the same AWS region (option D), and external stages in Amazon S3 buckets in other AWS regions (option E). Internal stages must be in the same Snowflake region, and local SSD storage is not a valid COPY INTO source.

Why the other options are wrong

  • A. Internal stages must be in the same Snowflake region; GCP us-central1 is not accessible as an internal stage from eu-west-1.
  • B. Internal stages must be in the same region; AWS eu-central-1 is a different region from eu-west-1 and cannot be used as an internal stage.
  • F. Local SSDs attached to EC2 instances are not valid COPY INTO sources; data must be staged in Snowflake stages or cloud storage.

Question 8

A Data Engineer wants to create a new development database (DEV) as a clone of the permanent production database (PROD). There is a requirement to disable Fail-safe for all tables.

Which command will meet these requirements?

  1. CREATE DATABASE DEV CLONE PROD FAIL_SAFE = FALSE;
  2. CREATE DATABASE DEV CLONE PROD;
  3. CREATE TRANSIENT DATABASE DEV CLONE PROD;
  4. CREATE DATABASE DEV CLONE PROD DATA_RETENTION_TIME_IN DAYS = 0;
Show answer and explanation

Correct answer: C. CREATE TRANSIENT DATABASE DEV CLONE PROD;

A TRANSIENT database automatically disables Fail-safe, meaning data cannot be recovered after the retention period expires. Creating a transient clone of the production database meets the requirement to disable Fail-safe for all tables while maintaining the clone relationship. Regular databases cannot have Fail-safe disabled through parameters, and there is no FAIL_SAFE or DATA_RETENTION_TIME_IN syntax that achieves this.

Why the other options are wrong

  • A. There is no FAIL_SAFE parameter in CREATE DATABASE syntax.
  • B. A standard database clone retains Fail-safe; this does not meet the requirement.
  • D. DATA_RETENTION_TIME_IN DAYS = 0 affects retention but does not disable Fai-afe.

Question 9

Which query will show a list of the 20 most recent executions of a specified task, MYTASK, that have been scheduled within the last hour that have ended or are still running?

Exhibit for question 9

Show answer and explanation

Correct answer: B. select * from

IS NOT NULL; Option B correctly queries the task history for MYTASK scheduled within the last hour (using scheduled_time_range_start with dateadd subtracting 1 hour) with a limit of 20 results. The WHERE clause 'query_id IS NOT NULL' filters to show only executions that have actually run or are currently running, excluding scheduled tasks that never executed. Option A lacks filtering and would include unexecuted scheduled tasks. Option C's STATE filter is overly restrictive by excluding other valid execution states. Option D uses the wrong parameter (scheduled_time_range_end instead of start) and has a limit of 10 instead of 20.

Why the other options are wrong

  • A. Returns all scheduled tasks including those never executed, not just actual or running executions.
  • C. Filters by specific states but does not distinguish between scheduled-only versus actually-executed tasks.
  • D. Uses scheduled_time_range_end instead of scheduled_time_range_start and limits results to 10 instead of 20.

Question 10

Which methods can be used to create a DataFrame object in Snowpark? (Choose three.)

  1. session.jdbc_connection()
  2. session.read.json()
  3. session.table()
  4. DataFrame.write()
  5. session.builder()
  6. session.sql()
Show answer and explanation

Correct answer: B, C, F

B. session.read.json() C. session.table() F. session.sql() DataFrames in Snowpark can be created using session.read.json() for reading JSON files (option B), session.table() for reading from existing tables (option C), and session.sql() for executing SQL queries and returning results as a DataFrame (option F). These are the primary methods for DataFrame instantiation in the Snowpark API.

Why the other options are wrong

  • A. session.jdbc_connection() establishes a JDBC connection but does not create a DataFrame object.
  • D. DataFrame.write() is a method for writing data, not for creating DataFrame objects.
  • E. session.builder() is used to configure the session, not to create DataFrame objects.

That was 10 of 65.

The full Snowflake SnowPro Advanced Data Engineer DEA-C02 pack has all 65 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.

Get the full pack