19. Snowflake Snow Pro Core Certification

1. What tasks can be completed using the COPY command? (Choose two.)

A. Columns can be aggregated.

B. Columns can be joined with an existing table.

C. Columns can be reordered.

D. Columns can be omitted.

E. Data can be loaded without the need to spin up a virtual warehouse.

The correct answers are:

C. Columns can be reordered. D. Columns can be omitted.

Explanation: The Snowflake COPY command allows you to load data into a table while performing some transformations, such as reordering and omitting columns from the source file. However, tasks like aggregating columns or joining them with other tables are not supported within the COPY command itself. Additionally, a virtual warehouse is required to load data using the COPY command.

2. Which Snowflake layer can be configured?

A. Database Storage

B. Cloud Services

C. Query Processing

D. Application Services

The correct answer is:

C. Query Processing

Explanation: The Query Processing layer in Snowflake, represented by virtual warehouses, can be configured by users. This layer is responsible for executing queries, and users can adjust aspects such as warehouse size, auto-suspend, auto-resume, and multi-cluster scaling settings.

The Database Storage and Cloud Services layers are managed by Snowflake and cannot be configured by users.

3. Query compilation occurs in which architecture layer of the Snowflake Cloud Data Platform?

A. Compute layer

B. Storage layer

C. Cloud infrastructure layer

D. Cloud services layer

The correct answer is:

D. Cloud services layer

In Snowflake, the Cloud Services layer is responsible for query compilation, optimization, and overall query management, including metadata management, security, and authentication. This layer coordinates the entire system and interacts with both the compute and storage layers to manage resources efficiently.

4. If a size Small virtual warehouse is made up of two servers, how many servers make up a Large warehouse?

A. 4

B. 8

C. 16

D. 32

• Small: 2 servers
• Medium: 4 servers
• Large: 8 servers
• X-Large: 16 servers

So, the correct answer should be:

B. 8

A Large warehouse consists of 8 servers.

5. A clustering key was defined on a table, but it is no longer needed.

How can the key be removed?

A. ALTER TABLE [TABLE NAME] PURGE CLUSTERING KEY

B. ALTER TABLE [TABLE NAME] DELETE CLUSTERING KEY

C. ALTER TABLE [TABLE NAME] DROP CLUSTERING KEY

D. ALTER TABLE [TABLE NAME] REMOVE CLUSTERING KEY

The correct syntax to remove a clustering key from a table in Snowflake is:

C. ALTER TABLE [TABLE NAME] DROP CLUSTERING KEY

This command removes the defined clustering key from the specified table.

6. What is a core benefit of clustering?

A. To guarantee uniquely identifiable records in the database

B. To increase scan efficiency in queries by improving pruning

C. To improve performance by creating a separate file for point lookups

D. To provide data redundancy by duplicating micro-partitions

The correct answer is:

B. To increase scan efficiency in queries by improving pruning

Clustering helps Snowflake optimize how it organizes data in micro-partitions, which improves pruning, making queries more efficient by reducing the amount of data scanned.

7. Which statement is true about Multi-Factor Authentication (MFA) in Snowflake?

A. MFA can be enforced or applied for a given role.

B. Snowflake users are automatically enrolled in MFA.

C. Users enroll in MFA by submitting a request to Snowflake Support.

D. MFA is an integrated Snowflake feature.

The correct answer is:

D. MFA is an integrated Snowflake feature.

Snowflake provides integrated Multi-Factor Authentication (MFA) that can be enabled by account administrators for additional security. It is not automatically applied to users, but users are not required to submit requests to Snowflake Support for enrollment. Administrators can enforce MFA for specific users or roles.

8. What data type should be used to store JSON data natively in Snowflake?

A. JSON

B. String

C. Object

D. VARIANT

The correct answer is:

D. VARIANT

In Snowflake, the VARIANT data type is used to store semi-structured data natively, including JSON, Avro, Parquet, and XML formats.

9. What should be considered when deciding to use a Secure View? (Choose two.)

A. No details of the query execution plan will be available in the query profiler.

B. Once created there is no way to determine if a view is secure or not.

C. Secure views do not take advantage of the same internal optimizations as standard views.

D. It is not possible to create secure materialized views.

E. The view definition of a secure view is still visible to users by way of the information schema.

The correct answers are:

A. No details of the query execution plan will be available in the query profiler.

• Secure views hide details such as the query execution plan, ensuring that underlying details are not exposed.

C. Secure views do not take advantage of the same internal optimizations as standard views.

• Secure views may not benefit from the same internal performance optimizations as standard views, since their primary goal is data protection rather than optimization.

10. The information schema provides storage information for which of the following objects? (Choose two.)

A. Users

B. Databases

C. Internal stages

D. Resource monitors

E. Pipes

The correct answers are:

B. Databases

• The information schema provides metadata, including storage information, about databases.

C. Internal stages

• The information schema includes storage information for internal stages used in data loading and unloading operations.

11. What is a responsibility of Snowflake’s virtual warehouses?

A. Infrastructure management

B. Metadata management

C. Query execution

D. Query parsing and optimization

E. Management of the storage layer

The correct answer is:

C. Query execution

Snowflake’s virtual warehouses are responsible for query execution. They provide the compute resources required to process SQL queries, and each warehouse is independent of others, allowing for scalable and concurrent query execution.

12. Which data type is supported by Snowflake data classification?

A. Binary

B. Float

C. Geography

D. Variant

Snowflake data classification supports some data types but excludes certain ones, such as ARRAY, BINARY, GEOGRAPHY, OBJECT, and VARIANT (unless castable to NUMBER or STRING).

Therefore, **FLOAT** would indeed be supported for classification.

13. When unloading data to an external stage, which compression format can be used for Parquet files with the COPY INTO command?

A. BROTLI

B. GZIP

C. LZO

D. ZSTD

The correct answer is:

D. LZO

When unloading data to an external stage with the COPY INTO command, Snowflake supports LZO, whereas the default file type is snappy.

14. Which SQL command can be used to verify the privileges that are granted to a role?

A. SHOW GRANTS ON ROLE

B. SHOW ROLES

C. SHOW GRANTS TO ROLE

D. SHOW GRANTS FOR ROLE

The correct answer is:

C. SHOW GRANTS TO ROLE

This SQL command shows the privileges that have been granted to a specific role.

15. Which Query Profile result indicates that a warehouse is sized too small?

A. There are a lot of filter nodes.

B. Bytes are spilling to external storage.

C. The number of processed rows is very high.

D. The number of partitions scanned is the same as partitions total.

The correct answer is:

B. Bytes are spilling to external storage.

This indicates that the warehouse does not have enough memory to process the query, causing it to offload data to external storage, which can significantly slow down performance. This is a common indicator that the warehouse is sized too small for the workload.

16. What is the default Time Travel retention period?

A. 1 day

B. 7 days

C. 45 days

D. 90 days

The correct answer is:

A. 1 day

The default Time Travel retention period in Snowflake is 1 day. Depending on the edition, this can be extended up to a maximum of 90 days for Enterprise and Business Critical editions.

17. Which of the following are best practice recommendations that should be considered when loading data into Snowflake? (Choose two.)

A. Load files that are approximately 25 MB or smaller.

B. Remove all dates and timestamps.

C. Load files that are approximately 100–250 MB (or larger).

D. Avoid using embedded characters such as commas for numeric data types.

E. Remove semi-structured data types.

The correct answers are:

C. Load files that are approximately 100–250 MB (or larger). D. Avoid using embedded characters such as commas for numeric data types.

These practices help ensure efficient loading and processing of data in Snowflake. Snowflake recommends loading files in the range of 100–250 MB for optimal performance and to avoid potential parsing errors, it’s best to avoid embedded characters in numeric data types.

18. Which schema has the RESOURCE_MONITORS view?

A. ACCOUNT_USAGE

B. READER_ACCOUNT_USAGE

C. INFORMATION_SCHEMA

D. WAREHOUSE_USAGE_SCHEMA

The correct answer is:

B. READER_ACCOUNT_USAGE

The RESOURCE_MONITORS view is available in the READER_ACCOUNT_USAGE schema. It provides details about resource monitors created in reader accounts managed by the account.

19. What is the purpose of enabling Federated Authentication on a Snowflake account?

A. Disables the ability to use key pair and basic authentication (e.g., username/password) when connecting

B. Allows dual Multi-Factor Authentication (MFA) when connecting to Snowflake

C. Forces users to connect through a secure network proxy

D. Allows users to connect using secure single sign-on (SSO) through an external identity provider

The correct answer is:

D. Allows users to connect using secure single sign-on (SSO) through an external identity provider

Enabling Federated Authentication allows Snowflake users to connect using SSO through an external identity provider, such as Okta, Azure AD, or other SAML 2.0 compliant providers. This streamlines the authentication process and improves security.

20. Which Snowflake partner category is represented at the top of this diagram (labeled 1)?

A. Business Intelligence

B. Machine Learning and Data Science

C. Security and Governance

D. Data Integration

D. Data Integration is the correct answer.

The Snowflake partner category represented at the top of the diagram (labeled 1) appears to be Data Integration & ETL (Extract, Transform, Load) Partners. These partners provide tools that help integrate data from various sources into Snowflake, allowing for efficient data transformation and loading.

In the image, companies such as Fivetran, Matillion, Informatica, Talend, and SnapLogic fall into this category, which are well-known for their data integration and ETL capabilities.