Free SnowPro Advanced Data Engineer Exam Questions and Answers

What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?

• AA. All types of JavaScript variables can be bound
• BB. All Snowflake first-class objects can be bound
• CC. Only JavaScript variables of type number, string and SfDate can be bound
• DD. Users are restricted from binding JavaScript variables because they create SQL injection attack vulnerabilities

A Data Engineer is building a pipeline to transform a 1 TB table by joining it with supplemental tables. The Engineer is applying filters and several aggregations leveraging Common Table Expressions (CTEs) using a size Medium virtual warehouse in a single query in Snowflake. After checking the Query Profile, what is the recommended approach to MAXIMIZE performance of this query if the Profile shows data spillage?

• AA. Enable clustering on the table
• BB. Increase the warehouse size
• CC. Rewrite the query to remove the CTEs.
• DD. Switch to a multi-cluster virtual warehouse

A company is using Snowpipe to bring in millions of rows every day of Change Data Capture (CDC) into a Snowflake staging table on a real-time basis. The CDC needs to get processed and combined with other data in Snowflake and land in a final table as part of the full data pipeline. How can a Data engineer MOST efficiently process the incoming CDC on an ongoing basis?

• AA. Create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data.
• BB. Transform the data during the data load with Snowpipe by modifying the related copy into statement to include transformation steps such as case statements and JOIN's.
• CC. Schedule a task that dynamically retrieves the last time the task was run from information_schema.task_history and use that timestamp to process the delta of the new rows since the last time the task was run.
• DD. Use a create or replace table as statement that references the staging table and includes all the transformation SQL. Use a task to run the full create or replace table as statement on a scheduled basis.

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 look over an hour to complete What will improve the query performance the MOST?

• AA. increase the size of the virtual warehouse.
• BB. Increase the number of clusters in the virtual warehouse
• CC. Implement the search optimization service on the table
• DD. Add a date column as a cluster key on the table

A CSV file around 1 TB in size is generated daily on an on-premise server. A corresponding table, internal stage, and file format have already been created in Snowflake to facilitate the data loading process. How can the process of bringing the CSV file into Snowflake be automated using the LEAST amount of operational overhead?

• AA. Create a task in Snowflake that executes once a day and runs a copy into statement that references the internal stage. The internal stage will read the files directly from the on-premise server and copy the newest file into the table from the on-premise server to the Snowflake table
• BB. On the on-premise server schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage. Create a task that executes once a day in Snowflake and runs a COPY INTO statement that references the internal stage. Schedule the task to start after the file lands in the internal stage
• CC. On the on-premise server schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage. Create a pipe that runs a copy into statement that references the internal stage. Snowpipe auto-ingest will automatically load the file from the internal stage when the new file lands in the internal stage.
• DD. On the on-premise server schedule a Python file that uses the Snowpark Python library. The Python script will read the CSV data into a DataFrame and generate an insert into statement that will directly load into the table. The script will bypass the need to move a file into an internal stage

The following code is executed ina Snowflake environment with the default settings: What will be the result of the select statement?

• AA. SQL compilation error object CUSTOMER' does not exist or is not authorized.
• BB. John
• CC. 1
• DD. 1John

A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector. Based on the user's selections, the system generates the SQL queries needed to fetch the data for the report First it gets the customers that meet the given query parameters (on average 1000 customer records for each report run) and then it loops the customer records sequentially Inside that loop it runs the generated SQL clause for the current customer to get the detailed data for that customer number from the sales data table When the Data Engineer tested the individual SQL clauses they were fast enough (1 second to get the customers 0 5 second to get the sales data for one customer) but the total runtime of the report is too long How can this situation be improved?

• AA. Increase the size of the virtual warehouse
• BB. Increase the number of maximum clusters of the virtual warehouse
• CC. Define a clustering key for the sales data table
• DD. Rewrite the report to eliminate the use of the loop construct

The JSON below is stored in a variant column named v in a table named jCustRaw: Which query will return one row per team member (stored in the teamMembers array) along all of the attributes of each team member?

• AA. Option A
• BB. Option B
• CC. Option C
• DD. Option D

Assuming a Data Engineer has all appropriate privileges and context, which statements would be used to assess whether the User-Defined Function (UDF), MYDATABASE.SALES.REVENUE_BY_REGION, exists and is secure? (Select TWO)

Select 2 answers.

• AA. SHOW USER FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
• BB. SELECT IS_SECURE FROM SNOWFLAKE.INFORMATION_SCHEMA.FUNCTIONS WHERE FUNCTION_SCHEMA = 'SALES' AND FUNCTION_NAME = 'REVENUE_BY_REGION';
• CC. SELECT IS_SECURE FROM INFORMATION_SCHEMA.FUNCTIONS WHERE FUNCTION_SCHEMA = 'SALES' AND FUNCTION_NAME = 'REVENUE_BY_REGION';
• DD. SHOW EXTERNAL FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
• EE. SHOW SECURE FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;

A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure: ....engineer is using the following COPY INTO statement: However, the following error is received. Which file format option should be used to resolve the error and successfully load all the data into the table?

• AA. ESC&PE_UNENGLO9ED_FIELD = '\\'
• BB. ERROR_ON_COLUMN_COUKT_MISMATCH = FALSE
• CC. FIELD_DELIMITER = ","
• DD. FIELD OPTIONALLY ENCLOSED BY = " "