Snowflake SnowPro Advanced: Data Engineer - Practice Exam 1

The SnowPro Advanced: Data Engineer credential recognizes specific knowledge and skills used to apply comprehensive data engineering principles using Snowflake. Passing this Certification showcases your ability to source and transform data from Data Lakes and APIs, design end-to-end near-time streams, and evaluate performance metrics, among other things. 2 + years of hands-on Snowflake Practitioner experience in a Data Engineering role prior is recommended for attempting this exam.

PDF Exam Questions

What are the steps to load data from an archival cloud storage class (for example, Amazon S3 Glacier or Microsoft Azure Archive Storage) into Snowflake?

After creating a database and a schema using the following commands:

CREATE OR REPLACE DATABASE MY_DB DATA_RETENTION_TIME_IN_DAYS=30;
CREATE OR REPLACE SCHEMA S1 DATA_RETENTION_TIME_IN_DAYS=50;

How long will we be able to access the data from the schema using the Time Travel functionality if we drop the database?

How should a Data Engineer interpret the following results (days 0 and 30), considering that he has run the following command on both occasions?

SELECT SYSTEM$CLUSTERING_INFORMATION( ‘orders’, ‘(o_orderdate)’);

How does Snowflake recommend splitting files?

Does an external function's identifier need to be unique for the schema in which the function is created?

Which of the following options are correct regarding the SECURITYADMIN and USERADMIN roles?

Which column specifies the timestamp in which the stream may become stale if not consumed?

What is the BEST way to optimize a task that runs every 5 minutes in a warehouse designed to auto-suspend in 5 minutes?

A table called MY_TABLE contains the following information:

Which command should we use to convert the array into individual rows?

Q10

What is the function of SnowCD?

Q11

After executing the following commands:

CREATE OR REPLACE DATABASE MY_DB DATA_RETENTION_TIME_IN_DAYS=30;
CREATE SCHEMA S1;
CREATE OR REPLACE TABLE T1 (ID NUMBER) DATA_RETENTION_TIME_IN_DAYS = 20;
CREATE OR REPLACE TABLE T2 (ID NUMBER) DATA_RETENTION_TIME_IN_DAYS = 40;

What will be the DATA_RETENTION_TIME_IN_DAYS for the Schema and the two tables?

Q12

Does the Search Optimization maintenance service automatically updates the search access path to reflect the changes to the data of a table?

Q13

What can we do with a single MERGE statement?

Q14

Which of these are considered best practices for Data Engineers?

Q15

Are synchronous and asynchronous queries allowed using the Python Connector?

Q16

Does Snowflake support a streamlined development architecture?

Q17

Which types of objects and functions CANNOT be invoked by a serverless task?

Q18

Can the query activity be returned using the QUERY_HISTORY function for the last ten days?

Q19

Does the Snowflake Web Interface have a limitation on file size while loading files?

Q20

What function can you use to determine if a UDF or Procedure is Secure?

Q21

Which semi-structured elements are currently not extracted into column data when inserted into a VARIANT column?

Q22

Does Snowflake charge data ingress fees?

Q23

What is the main difference between the LOAD_HISTORY and COPY_HISTORY commands?

Q24

Can you use Context Function to authorize users to see the data using masking policies?

Q25

Which load metadata does Snowflake maintain for each table?

Q26

How can you convert an existing view to a secure view and vice versa?

Q27

Does the SECURITYADMIN role inherit the privileges of the USERADMIN role?

Q28

What will happen if we specify the name of the stage instead of the file in the FROM line of the COPY INTO statement?

Q29

What is the function of Resource Monitors regarding data pipelines?

Q30

How can we re-load all files in a COPY INTO statement, regardless of whether they’ve been loaded previously and have not changed since they were loaded?

Q31

Which command can we use to convert a timestamp to another time zone?

Q32

What is the recommended file size for the best load performance and to avoid size limitations?

Q33

Does the Kafka connector create one pipe for each partition in a Kafka topic?

Q34

Is using different warehouses recommended when loading and querying large data sets?

Q35

What does the LOAD_UNCERTAIN_FILES copy option mean?

Q36

After running the following command:

SELECT SYSTEM$CLUSTERING_INFORMATION(
	'INVENTORY', '(INV_ITEM_SK, INV_WAREHOUSE_SK)'
);

It returns a note specifying “Clustering key columns contain high cardinality key INV_ITEM_SK which might result in expensive re-clustering”. Which of the following statements are true about a high cardinality column?

Q37

Which parameter should we specify when creating external tables if we want the owner of the external table to add partitions to the external metadata manually?

Q38

Which function that returns the pipe activity within the last 14 days can be used to validate data files processed by Snowpipe within a specified time range?

Q39

Do streams on shared tables extend the data retention period of the table?

Q40

What is the purpose of the PIPE_USAGE_HISTORY Snowflake function?

Q41

Which of these functions are QUERY_HISTORY family functions?

Q42

Can we perform reading/writing operations with the Spark connector for Snowflake?

Q43

Which techniques does Snowflake provide to query over hierarchical data?

Q44

Which of the following features is not supported by Snowpipe for data loading?

Q45

What techniques can we use to improve load performance when loading terabytes of data?

Q46

After performing the following query:

SELECT * 
FROM MYTABLE 
WHERE email='test@test.com'

you see in the query profiler the following information:

Can you spot the issue?

Q47

Is it good practice for a better pruning and less storage solution to flatten your OBJECT and key data into separate relational columns if your data includes numbers within strings?

Q48

Which of the following option is incorrect regarding Materialized Views?

Q49

Which commands can we use to return the last 1000 tasks that failed or were canceled in the last seven days?

Q50

What will happen with the DATA_RETENTION_TIME_IN_DAYS parameter of a Schema when we modify this value in the parent database?

Q51

In which of the following situations can the COPY_HISTORY command indicate that a subset of files was not loaded by the pipe using the auto-ingest configuration?

Q52

A Data Engineer is working on a project requiring data to be moved directly from an internal to an external stage. Which of the following is the QUICKEST way to accomplish this task?

Q53

Which of these functions can you use to identify the queries that benefit from Search Optimization?

Q54

After running the following COPY INTO command, we realize that no data has been copied into Snowflake. Why is this happening?

COPY INTO MYTABLE VALIDATION_MODE= 'RETURN_ERRORS';

Q55

When should a Data Engineer enable the “STRIP_NULL_VALUES” file format option in the COPY INTO command?

Q56

Can Virtual Columns be specified as the first column argument in a conditional masking policy?

Q57

We are trying to clone a database and all its objects using the following command:

CREATE DATABASE MY_TABLE_RESTORED CLONE MY_TABLE
	BEFORE (statement => '02b9b2b3-1322-2f4f-0001-42cd01bc97ff');

However, the previous statement failed. What are the possible reasons?

Q58

What data will return the Snowflake after running the following command on the NOT-clustered INVENTORY table?

SELECT SYSTEM$CLUSTERING_INFORMATION(
	'INVENTORY' , '(INV_ITEM_SK, INV_WAREHOUSE_SK)'
);

Q59

Which of the following practices will provide the MOST efficient load performance when loading staged data from an external stage using a task object?

Q60

Which two options can we use to remove staged files after Snowpipe loads the data?

Q61

The S1 schema contains two permanent tables that were created as shown below:

CREATE TABLE table_b (s1 VARIANT)
	DATA_RETENTION_TIME_IN_DAYS = 10;
CREATE TABLE table_c (s1 VARIANT);

What will be the impact of running the following command?

ALTER SCHEMA S1 SET DATA_RETENTION_TIME_IN_DAYS = 20;

Q62

After calling the insertReport API endpoint from Snowpipe, the response payload contains a field called “status”. Which are the possible values of this field?

Q63

Select all the different ways that we can access to the USER_NAME field if we have a table called MYTABLE with a variant column called JSONTEXT with the following structure:

{
	"USER_NAME": "Bob",
	"USER_AGE ": 40,
	"TECHNOLOGY": "Snowflake"
}

Q64

After querying a timezone column, you see that the data has a Z at the end, as we can see in the following picture. Assuming it’s a valid zone information, what does it tell us about the time zone of the data?

Q65

Which of the following options is not a compression technique for CSV, JSON, and XML file formats?