Snowflake SnowPro Core Certification — Questions 11–20 Practice Questions With Solutions. Part 2 of 10.

Source: Snowflake

In this blog post, I will be sharing practice questions 11–20 for the Snowflake SnowPro Core Certification exam. By practicing with these questions, you can refine your understanding of Snowflake’s core concepts and increase your chances of success on the certification exam. Answer Key is at the bottom.

This is Part 2.

Questions 1–10 can be found on the blog post for Part 1 .

Question 11

What is the recommended method for storing JSON data in Snowflake?

Options:

A) Using a column of the JSON data type.

B) Using a column of the VARCHAR data type.

C) Using a column of the VARIANT data type.

D) Using a column of the NULL data type.

Question 12

Is Fail-Safe period configurable?

1. Yes
2. No

Question 13

Can you use the GET command in Snowflake to download files from an external stage?

A) Yes, the GET command can be used to download files from external stages.

B) No, Snowflake does not support the GET command for downloading files.

C) The GET command is used for querying data, not for file downloads.

D) The DOWNLOAD command should be used instead of GET for file retrieval from external stages.

Question 14

What are the various types of stages that exist in Snowflake?

Options:

A) User.

B) Table.

C) Named internal.

D) Named external.

E) Account.

Question 15

Which command can be employed in SnowSQL to retrieve files from a stage?

A) GET.

B) PUT.

C) UNLOAD.

D) INSERT INTO.

Question 16

Which table function in Snowflake enables the transformation of semi-structured data into a relational format?

A) FLATTEN.

B) CHECK_JSON.

C) PARSE_JSON.

Question 17

What method does Snowflake employ to restrict the retrieval of a minimal number of micro-partitions as part of a query?

A) Pruning.

B) Clustering.

C) Indexing.

D) Computing.

Question 18

In which of the following situations is it advisable to utilize Snowpipe for loading data into Snowflake?

A) We have a small volume of frequent data.

B) We have a huge volume of data generated as part of a batch schedule.

C) In both of the previous scenarios.

D) For running real-time reports on large data sets.

Question 19

Does Fail-Safe on tables result in additional storage costs ?

A) Yes

B) No

Question 20

Does Snowflake trigger the re-clustering of a table only when it would benefit from the operation?

A) True

B) False

Answer Key

Question 11

Solution: C) Using a column of the VARIANT data type.

Explanation: In Snowflake, it is best practice to store JSON data as the VARIANT data type within the tables. VARIANT columns can accommodate semi-structured data, including JSON, with a maximum size limit of 16MB. This allows for efficient storage and querying of JSON data using JSON notation, enabling the storage of arrays, objects, and other JSON structures within Snowflake.

https://docs.snowflake.com/en/sql-reference/data-types-semistructured

Question 12

Solution: B) No

Explanation: Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.

https://docs.snowflake.com/en/user-guide/data-failsafe

Question 13

Solution: B) No, Snowflake does not support the GET command for downloading files.

Explanation:

Get command downloads data files from one of the following Snowflake stages to a local directory/folder on a client machine:

• Named internal stage.
• Internal stage for a specified table.
• Internal stage for the current user.
• GET does not support downloading files from external stages. To download files from external stages, use the utilities provided by the cloud service.

https://docs.snowflake.com/en/sql-reference/sql/get

Question 14

Solution:

A) User.

B) Table.

C) Named internal.

D) Named external.

Explanation: Snowflake provides different types of stages to handle data loading and retrieval. These include User stages, Table stages (associated with individual tables), Named internal stages (for internal storage), and Named external stages (for external data sources). Each type of stage serves distinct purposes in managing and organizing data within Snowflake. Snowflake supports creating named external stages.

https://docs.snowflake.com/en/sql-reference/sql/create-stage

https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage

https://docs.snowflake.com/en/user-guide/data-load-s3-create-stage

Question 15

Solution: A) GET.

Explanation: The GET command is utilized in SnowSQL to download files from a Snowflake internal stage, such as a user stage, table stage, or named internal stage, to a directory or folder on a client machine. This operation can be performed using SnowSQL, allowing for easy retrieval of data loaded using the COPY INTO command.

https://docs.snowflake.com/en/sql-reference/sql/get

Question 16

Solution: A) FLATTEN.

Explanation: The FLATTEN table function in Snowflake is designed for the conversion of semi-structured data into a relational representation. It works with VARIANT, OBJECT, or ARRAY columns and produces a lateral view, facilitating the handling of semi-structured data in a structured, tabular format.

https://www.snowflake.com/guides/semi-structured-data-101

Question 17

Solution: A) Pruning.

Explanation: Snowflake utilizes query pruning to analyze and access the minimum number of micro-partitions necessary to answer a query efficiently. This technique optimizes query performance by retrieving only the relevant data, significantly reducing query processing time.

https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions

Question 18

Solution: A) We have a small volume of frequent data.

Explanation: Snowpipe enables loading data from files as soon as they’re available in a stage. This means you can load data from files in micro-batches, making it available to users within minutes, rather than manually executing COPY statements on a schedule to load larger batches.

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro

Question 19

Solution: A) Yes

Explanation: Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake. Storage fees are incurred for maintaining historical data during both the Time Travel and Fail-safe periods.

https://docs.snowflake.com/en/user-guide/data-cdp-storage-costs

Question 20

A) True.

Explanation: In Snowflake, when performing Data Manipulation Language (DML) operations such as INSERT, UPDATE, DELETE, MERGE, or COPY, the data in a clustered table can become less optimally clustered. To address this, Snowflake provides automatic and periodic re-clustering to maintain optimal data organization. Importantly, Snowflake only initiates re-clustering for a table when it would result in a benefit to the table’s data organization and performance.

Thanks for Reading!

If you like my work and want to support me:

1. You can follow me on Medium here.
2. Feel free to clap if this post is helpful for you! :)