Snowflake SNOWPRO ADVANCED DATA ENGINEER Exam Questions
Questions for the SNOWPRO ADVANCED DATA ENGINEER were updated on : Sep 09 ,2024
Page 1 out of 7. Viewing questions 1-10 out of 65
Question 1
A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure:
The engineer is using the following COPY INTO statement:
However, the following error is received:
Number of columns in file (6) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error File 'address.csv.gz', line 3, character 1 Row 1 starts at line 2, column STGCUSTOMER[6] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option.
Which file format option should be used to resolve the error and successfully load all the data into the table?
- A. ESCAPE_UNENCLOSED FIELD = '\\'
- B. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
- C. FIELD_DELIMITER = ','
- D. FIELD_OPTIONALLY_ENCLOSED_BY = '"'
Answer:
d
Question 2
A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive).
Which query will provide the status of the pipe?
- A. SELECT SYSTEM$PIPE_STATUS("test.'extract'.my_pipe");
- B. SELECT SYSTEM$PIPE_STATUS('test."Extract".my_pipe');
- C. SELECT * FROM SYSTEM$PIPE_STATUS('test."Extract".my_pipe');
- D. SELECT * FROM SYSTEM$PIPE_STATUS("test.'extract'.my_pipe");
Answer:
b
Question 3
A Data Engineer is evaluating the performance of a query in a development environment.
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
Answer:
ac
Question 4
At what isolation level are Snowflake streams?
- A. Snapshot
- B. Repeatable read
- C. Read committed
- D. Read uncommitted
Answer:
b
Question 5
A secure function returns data coming through an inbound share.
What will happen if a Data Engineer tries to assign USAGE privileges on this function to an outbound share?
- A. An error will be returned because the Engineer cannot share data that has already been shared.
- B. An error will be returned because only views and secure stored procedures can be shared.
- C. An error will be returned because only secure functions can be shared with inbound shares.
- D. The Engineer will be able to share the secure function with other accounts.
Answer:
a
Question 6
A database contains a table and a stored procedure defined as:
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.
Answer:
d
Question 7
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.
Answer:
b
Question 8
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
Answer:
abc
Question 9
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.
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?
- D. None
Answer:
b
Question 10
What is a characteristic of the operations of streams in Snowflake?
- A. Whenever a stream is queried, the offset is automatically advanced.
- B. When a stream is used to update a target table, the offset is advanced to the current time.
- C. Querying a stream returns all change records and table rows from the current offset to the current time.
- D. Each committed and uncommitted transaction on the source table automatically puts a change record in the stream.
Answer:
b