SnowPro Advanced Data Engineer Practice Exam

Join Us Among the Stars

Sign Up & unlock 100% of Exam Questions

Log in / Sign up

No Strings Attached!

161Practice Questions

3Study Modes

Free

TopicSort

Question 1

Implement Data Protection and Recovery

A new CUSTOMER table is created by a data pipeline in a Snowflake schema where MANAGED ACCESS is enabled.
Which roles can grant access to the CUSTOMER table? (Choose three.)

A The role that owns the schema
B The role that owns the database
C The role that owns the CUSTOMER table
D The SYSADMIN role
E The SECURITYADMIN role
F The USERADMIN role with the MANAGE GRANTS privilege

Question 2

Design and Manage Snowflake Resources and Performance

Question 3

Ingest and Process Data

Question 4

Design and Manage Snowflake Resources and Performance

Question 5

Implement Data Protection and Recovery

Question 6

Design and Manage Snowflake Resources and Performance

Question 7

Design and Manage Snowflake Resources and Performance

Question 8

Ingest and Process Data

Question 9

Design and Manage Snowflake Resources and Performance

Question 10

Design and Manage Snowflake Resources and Performance

Question 11

Ingest and Process Data

Question 12

Ingest and Process Data

Question 13

Transform Data

Question 14

Ingest and Process Data

Question 15

Ingest and Process Data

Question 16

Transform Data

Question 17

Design and Manage Snowflake Resources and Performance

Question 18

Transform Data

Question 19

Design and Manage Snowflake Resources and Performance

Question 20

Implement Data Protection and Recovery

Question 21

Design and Manage Snowflake Resources and Performance

Question 22

Implement Data Protection and Recovery

Question 23

Transform Data

Question 24

Design and Manage Snowflake Resources and Performance

Question 25

Transform Data

Page 1 of 7 • Questions 1-25 of 161

1 2 3 4 5

→

Know a question that should be here? Contribute to this exam

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?

A select system$clustering_information('Sales', 'sales_representative', 'region = ''North America''');
B select system$clustering_depth('Sales', 'sales_representative', 'region = ''North America''');
C select system$clustering_depth('Sales', 'sales_representative') where region = 'North America';
D select system$clustering_information('Sales', 'sales_representative') where region = 'North America’;

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.)

A Internal stage on GCP us-central1 (Iowa)
B Internal stage on AWS eu-central-1 (Frankfurt)
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)
F SSD attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland)

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:

Question Image

Using a size 2XL virtual warehouse, this query took over an hour to complete.
What will improve the query performance the MOST?

A Increase the size of the virtual warehouse.
B Increase the number of clusters in the virtual warehouse.
C Implement the search optimization service on the table.
D Add a date column as a cluster key on the table.

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?

A CREATE DATABASE DEV -CLONE PROD -FAIL_SAFE = FALSE;
B CREATE DATABASE DEV -CLONE PROD;
C CREATE TRANSIENT DATABASE DEV -CLONE PROD;
D CREATE DATABASE DEV -CLONE PROD -DATA_RETENTION_TIME_IN DAYS = 0;

Question 6

Design and Manage Snowflake Resources and Performance

Question 7

Design and Manage Snowflake Resources and Performance

Question 8

Ingest and Process Data

Question 9

Design and Manage Snowflake Resources and Performance

Question 10

Design and Manage Snowflake Resources and Performance

Question 11

Ingest and Process Data

Question 12

Ingest and Process Data

Question 13

Transform Data

Question 14

Ingest and Process Data

Question 15

Ingest and Process Data

Question 16

Transform Data

Question 17

Design and Manage Snowflake Resources and Performance

Question 18

Transform Data

Question 19

Design and Manage Snowflake Resources and Performance

Question 20

Implement Data Protection and Recovery

Question 21

Design and Manage Snowflake Resources and Performance

Question 22

Implement Data Protection and Recovery

Question 23

Transform Data

Question 24

Design and Manage Snowflake Resources and Performance

Question 25

Transform Data

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?

A
B
C
D

A stream called TRANSACTIONS_STM is created on top of a TRANSACTIONS table in a continuous pipeline running in Snowflake. After a couple of months, the TRANSACTIONS table is renamed TRANSACTIONS_RAW to comply with new naming standards.
What will happen to the TRANSACTIONS_STM object?

A TRANSACTIONS_STM will keep working as expected.
B TRANSACTIONS_STM will be stale and will need to be re-created.
C TRANSACTIONS_STM will be automatically renamed TRANSACTIONS_RAW_STM.
D Reading from the TRANSACTIONS_STM stream will succeed for some time after the expected STALE_TIME.

A database contains a table and a stored procedure defined as:

Question Image

The log_table is initially empty and a Data Engineer issues the following command:
CALL insert_log(NULL::VARCHAR);
No other operations are affecting the log_table.
What will be the outcome of the procedure call?

A The log_table contains zero records and the stored procedure returned 1 as a return value.
B The log_table contains one record and the stored procedure returned 1 as a return value.
C The log_table contains one record and the stored procedure returned NULL as a return value.
D The log_table contains zero records and the stored procedure returned NULL as a return value.

A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.
How long will Snowflake use the persisted query results from the result cache, provided that the underlying data has not changed?

A 12 hours
B 24 hours
C 14 days
D 31 days

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.)

A The results must be reused within 72 hours.
B The query must be executed using the same virtual warehouse.
C The USED_CACHED_RESULT parameter must be included in the query.
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.

Which Snowflake objects does the Snowflake Kafka connector use? (Choose three.)

A Pipe
B Serverless task
C Internal user stage
D Internal table stage
E Internal named stage
F Storage integration

A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe.
Which recommendations apply to this scenario? (Choose three.)

A Load large files (1 GB or larger).
B Ensure that data files are 100-250 MB (or larger) in size, compressed.
C Load a single huge array containing multiple records into a single table row.
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.
F Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute.

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

Question Image

A Use the TO_JSON function.
B Use the PARSE_JSON function to produce a VARIANT value.
C Use the OBJECT_CONSTRUCT function to return a Snowflake object.
D Use the TO_VARIANT function to convert each of the relational columns to VARIANT.

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

A session.jdbc_connection()
B session.read.json()
C session.table()
D DataFrame.write()
E session.builder()
F session.sql()

What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?

A It generates an encrypted URL for accessing a file in a stage.
B It generates a staged URL for accessing a file in a stage.
C It generates a permanent URL for accessing files in a stage.
D It generates a temporary URL for accessing a file in a stage.

A company has an extensive script in Scala that transforms data by leveraging DataFrames. A Data Engineer needs to move these transformations to Snowpark.
What characteristics of data transformations in Snowpark should be considered to meet this requirement? (Choose two.)

A It is possible to join multiple tables using DataFrames.
B Snowpark operations are executed lazily on the server.
C User-Defined Functions (UDFs) are not pushed down to Snowflake.
D Snowpark requires a separate cluster outside of Snowflake for computations.
E Columns in different DataFrames with the same name should be referred to with squared brackets.

The following is returned from SYSTEM$CLUSTERING_INFORMATION() for a table named ORDERS with a DATE column named O_ORDERDATE:

Question Image

What does the total_constant_partition_count value indicate about this table?

A The table is clustered very well on O_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering.
B The table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro-partition in the table.
C The data in O_ORDERDATE does not change very often, as there are 493 micro-partitions containing rows where that column has not been modified since the row was created.
D The data in O_ORDERDATE has a very low cardinality, as there are 493 micro-partitions where there is only a single distinct value in that column for all rows in the micro-partition.

The JSON below is stored in a VARIANT column named V in a table named jCustRaw:

Question Image

Which query will return one row per team member (stored in the teamMembers array) along with all of the attributes of each team member?

A
B
C
D

A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions vary by topic according to what data each Analyst needs. Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved.
How should the Data Engineer configure the compute resources to support this dashboard?

A Assign queries to a multi-cluster virtual warehouse with economy auto-scaling. Allow the system to automatically start and stop clusters according to demand.
B Assign all queries to a multi-cluster virtual warehouse set to maximized mode. Monitor to determine the smallest suitable number of clusters.
C Create a virtual warehouse for every 250 Analysts. Monitor to determine how many of these virtual warehouses are being utilized at capacity.
D Create a size XL virtual warehouse to support all the dashboard queries. Monitor query runtimes to determine whether the virtual warehouse should be resized.

A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolling back. The transaction was left in a detached state and created a lock on resources.
What step must the Engineer take to immediately run a new transaction?

A Call the system function SYSTEM$ABORT_TRANSACTION.
B Call the system function SYSTEM$CANCEL_TRANSACTION.
C Set the LOCK_TIMEOUT to FALSE in the stored procedure.
D Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

Which output is provided by both the SYSTEM$CLUSTERING_DEPTH function and the SYSTEM$CLUSTERING_INFORMATION function?

A average_depth
B notes
C average_overlaps
D total_partition_count

A table is loaded using Snowpipe and truncated afterwards. Later, a Data Engineer finds that the table needs to be reloaded, but the metadata of the pipe will not allow the same files to be loaded again.
How can this issue be solved using the LEAST amount of operational overhead?

A Wait until the metadata expires and then reload the file using Snowpipe.
B Modify the file by adding a blank row to the bottom and re-stage the file.
C Set the FORCE=TRUE option in the Snowpipe COPY INTO command.
D Recreate the pipe by using the CREATE OR REPLACE PIPE command.

Which methods will trigger an action that will evaluate a DataFrame? (Choose two.)

A DataFrame.random_split()
B DataFrame.collect()
C DataFrame.select()
D DataFrame.col()
E DataFrame.show()

A Data Engineer is evaluating the performance of a query in a development environment.

Question Image

Based on the Query Profile, what are some performance tuning options the Engineer can use? (Choose two.)

A Add a LIMIT to the ORDER BY if possible
B Use a multi-cluster virtual warehouse with the scaling policy set to standard
C Move the query to a larger virtual warehouse
D Create indexes to ensure sorted access to data
E Increase the MAX_CLUSTER_COUNT

A Data Engineer has created table t1 with one column c1 with datatype VARIANT: create or replace table t1 (c1 variant);
The Engineer has loaded the following JSON data set, which has information about 4 laptop models, into the table.

Question Image

The Engineer now wants to query that data set so that results are shown as normal structured data. The result should be 4 rows and 4 columns, without the double quotes surrounding the data elements in the JSON data.
The result should be similar to the use case where the data was selected from a normal relational table t2, where t2 has string data type columns model_id, model, manufacturer, and model_name, and is queried with the SQL clause select * from t2;
Which select command will produce the correct results?

A
B
C
D

Join Us Among the Stars

SnowPro Advanced Data EngineerPreview

About the SnowPro Advanced Data Engineer Exam

Mode Selection

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25