
Snowflake SnowPro Advanced: Data Engineer - Practice Exam 1
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?
What is the function of SnowCD?
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?
Does the Search Optimization maintenance service automatically updates the search access path to reflect the changes to the data of a table?
What can we do with a single MERGE statement?
Which of these are considered best practices for Data Engineers?
Are synchronous and asynchronous queries allowed using the Python Connector?
Does Snowflake support a streamlined development architecture?
Which types of objects and functions CANNOT be invoked by a serverless task?
Can the query activity be returned using the QUERY_HISTORY function for the last ten days?
Has the Snowflake Data Load wizard a limitation on file size?
How can you determine if a UDF or Procedure is Secure?
Which semi-structured elements are currently not extracted into column data when inserted into a VARIANT column?
Does Snowflake charge data ingress fees?
What is the main difference between the LOAD_HISTORY and COPY_HISTORY commands?
Can you use Context Function to authorize users to see the data using masking policies?
Which load metadata does Snowflake maintain for each table?
How can you convert an existing view to a secure view and vice versa?
Does the SECURITYADMIN role inherit the privileges of the USERADMIN role?
What will happen if we specify the name of the stage instead of the file in the FROM line of the COPY INTO statement?
What is the function of Resource Monitors regarding data pipelines?
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?
Which command can we use to convert a timestamp to another time zone?
What is the recommended file size for the best load performance and to avoid size limitations?
Does the Kafka connector create one pipe for each partition in a Kafka topic?
Is using different warehouses recommended when loading and querying large data sets?
What does the LOAD_UNCERTAIN_FILES copy option mean?
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?
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?
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?
Do streams on shared tables extend the data retention period of the table?
What is the purpose of the PIPE_USAGE_HISTORY Snowflake function?
Which of these functions are QUERY_HISTORY family functions?
Can we perform reading/writing operations with the Spark connector for Snowflake?
Which techniques does Snowflake provide to query over hierarchical data?
Which of the following features is not supported by Snowpipe for data loading?
What techniques can we use to improve load performance when loading terabytes of data?
After performing the following query:
SELECT *
FROM MYTABLE
WHERE email=’test@test.com’
you see in the query profiler the following information:
*Subscribe now to see this picture*
Can you spot the issue?
Is it good practice for a better pruning and less storage solution to flatten your OBJECT and key data into separate relational columns?
Which of the following option is incorrect regarding Materialized Views?
Which commands can we use to return the last 1000 tasks that failed or were canceled in the last seven days?
What will happen with the DATA_RETENTION_TIME_IN_DAYS parameter of a Schema when we modify this value in the parent database?
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?
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?
Which of these functions can you use to identify the queries that benefit from Search Optimization?
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';
When should a Data Engineer enable the “STRIP_NULL_VALUES” file format option in the COPY INTO command?
Can Virtual Columns be specified as the first column argument in a conditional masking policy?
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?
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
)’);
Which of the following practices will provide the MOST efficient load performance when loading staged data from an external stage using a task object?
Which two options can we use to remove staged files after Snowpipe loads the data?
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;
After calling the insertReport API endpoint from Snowpipe, the response payload contains a field called “status”. Which are the possible values of this field?
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"
}
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?
*Subscribe now to see this picture*
Which of the following options is not a compression technique for CSV, JSON, and XML file formats?