32. What are the different locations where you can define the file format options?

a) COPY INTO TABLE statement.

b) Stage definition.
c) Table definition.
d) Database definition

Answer: a,b,c

33. A high churn table has active data size of only 700 GB, however the allocated storage is more than 1
TB. What may be the potential reasons?
a) The table is truncated and loaded everyday. The table has time travel and fail safe enabled.
b) Large loads happen everyday, hence existing micropartitions are deleted and new micro partitions get
created
c) The size of the virtual warehouse is too small to handle the table data
d) Incorrect sizing of the table is done

Answer: a,b

34. In which of the below use cases does Snowflake applies data egress charge?
a) Unloading data from Snowflake
b) Database replication
c) External functions
d) Loading data into Snowflake

Answer: a,b,c

35. What are the two techniques available to query hierarchical data?
a) RECURSIVE CTEs
b) CONNECT WITH
c) CONNECT BY
d) RECURSION

Answer: a,c

36. You get 2MB files per minute everyday. If you consider only snowflake cost, what is the best option to
process such files. You can choose any file format that you want.
a) CSV with snowpipe
b) AVRO with snowpipe
c) Create a dedicated virtual warehouse and use JSON file format
d) Create a dedicated virtual warehouse and use AVRO file format

Answer: a

37. Which information can be obtained from system$clustering_information?

a) max_depth
b) average_depth
c) average_overlaps
d) total_partition_count

Answer: b,c,d

38. While loading a table from an internal stage, you received the below error. What two options can you
choose from below to fix this?
a) Use FIELD_OPTIONALLY_ENCLOSED_BY to enclose strings
b) Add the required column in the TABLE
c) Use SKIP_FILE to skip the file and continue
d) Use error_on_column_count_mismatch=false

Answer: a,b

39. Which of the below tools can be used to evaluate the network connection to Snowflake at any time to
verify the required configuration settings are correct?
a) SNOWSQL
b) SNOWPIPE
c) SNOWSIGHTS
d) SNOWCD
e) SNOWPARK

Answer: d

40. When queried, a stream accesses and returns the historic data in the same shape as the source table
(i.e. the same column names and ordering) with additional columns.
What are those columns?
a) METADATA$ACTION
b) METADATA$ROW_NUMBER
c) METADATA$ISUPDATE
d) METADATA$ROW_ID

Answer: a,c,d

41. Which of the below are benefits of micro partitioning?

a) Micro partitions are derived automatically
b) Micro partitions need to be maintained by users
c) Micro partitions enables extremely efficient DML and fine-grained pruning for faster queries
d) Columns are stored independently within micro-partitions
e) Columns are compressed individually within micro-partitions

Answer: a,c,d,e

Question 42
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 43
Which functions will compute a ’fingerprint’ over an entire table, query result, or window to quickly detect
changes to table contents or query results? (Choose two.)

A. HASH(*)
B. HASH_AGG(*)
C. HASH_AGG(<expr>, <expr>)
D. HASH_AGG_COMPARE(*)
E. HASH_COMPARE(*)

Answer : BD

Question 44
Which stages support external tables?

A. Internal stages only; within a single Snowflake account

B. Internal stages only; from any Snowflake account in the organization
C. External stages only; from any region, and any cloud provider
D. External stages only; only on the same region and cloud provider as the Snowflake account

Answer : C

Question 45
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 46
Company A and Company B both have Snowflake accounts. Company A’s account is hosted on a
different cloud provider and region than Company B’s account. Companies A and B are not in the same
Snowflake organization.
How can Company A share data with Company B? (Choose two.)

A. Create a share within Company A’s account and add Company B’s account as a recipient of that
share.
B. Create a share within Company A’s account, and create a reader account that is a recipient of the
share. Grant Company B access to the reader account.
C. Use database replication to replicate Company A’s data into Company B’s account. Create a share
within Company B’s account and grant users within Company B’s account access to the share.
D. Create a new account within Company A’s organization in the same cloud provider and region as
Company B’s account. Use database replication to replicate Company A’s data to the new account.
Create a share within the new account, and add Company B’s account as a recipient of that share.
E. Create a separate database within Company A’s account to contain only those data sets they wish to
share with Company B. Create a share within Company A’s account and add all the objects within this
separate database to the share. Add Company B’s account as a recipient of the share.

Answer : AB

Question 16
A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis
Firehose and loads the data into a staging table which will later be used in the data transformation
process. The average file size is 300-500 MB.
The Engineer needs to ensure that Snowpipe is performant while minimizing costs.
How can this be achieved?

A. Increase the size of the virtual warehouse used by Snowpipe.

B. Split the files before loading them and set the SIZE_LIMIT option to 250 MB.
C. Change the file compression size and increase the frequency of the Snowpipe loads.
D. Decrease the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose.

Answer : D

Question 47
Within a Snowflake account. permissions have been defined with custom roles and role hierarchies.
To set up column-level masking using a role in the hierarchy of the current user, what command would be
used?

A. CURRENT_ROLE
B. INVOKER_ROLE
C. IS_ROLE_IN_SESSION
D. IS_GRANTED_TO_INVOKER_ROLE

Answer : D

Question 48
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? (Choose two.)

A. SHOW USER FUNCTIONS LIKE ’REVENUE_BY_REGION’ IN SCHEMA SALES;

B. SELECT IS_SECURE FROM SNOWFLAKE.INFORMATION_SCHEMA.FUNCTIONS WHERE
FUNCTION_SCHEMA = ’SALES’ AND FUNCTION_NAME = ’REVENUE_BY_REGION’;
C. SELECT IS_SECURE FROM INFORMATION_SCHEMA.FUNCTIONS WHERE FUNCTION_SCHEMA
= ’SALES’ AND FUNCTION_NAME = ’REVENUE_BY_REGION’;
D. SHOW EXTERNAL FUNCTIONS LIKE ’REVENUE_BY_REGION’ IN SCHEMA SALES;
E. SHOW SECURE FUNCTIONS LIKE ’REVENUE_BY_REGION’ IN SCHEMA SALES;

Answer : AC

Question 49
A Data Engineer has written a stored procedure that will run with caller’s rights. The Engineer has granted
ROLEA the right to use this stored procedure.
What is a characteristic of the stored procedure being called using ROLEA?

A. The stored procedure must run with caller’s rights; it cannot be converted later to run with owner’s
rights.
B. If the stored procedure accesses an object that ROLEA does not have access to, the stored procedure
will fail.
C. The stored procedure will run in the context (database and schema) where the owner created the
stored procedure.
D. ROLEA will not be able to see the source code for the stored procedure, even though the role has
usage privileges on the stored procedure.
Answer : B

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

A. All types of JavaScript variables can be bound.

B. All Snowflake first-class objects can be bound.
C. Only JavaScript variables of type number, string, and SfDate can be bound.
D. Users are restricted from binding JavaScript variables because they create SQL injection attack
vulnerabilities.

Answer : C

Question 51
Which use case would be BEST suited for the search optimization service?

A. Analysts who need to perform aggregates over high-cardinality columns.

B. Business users who need fast response times using highly selective filters.
C. Data Scientists who seek specific JOIN statements with large volumes of data.
D. Data Engineers who create clustered tables with frequent reads against clustering keys.

Answer : B

Question 52
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

Question 53
At what isolation level are Snowflake streams?

A. Snapshot
B. Repeatable read
C. Read committed
D. Read uncommitted

Answer : B