Blog Cloud Computing 25 Free Questions – SnowFlake SnowPro Core Certification Free Questions
SnowFlake SnowPro Core

25 Free Questions – SnowFlake SnowPro Core Certification Free Questions

The SnowPro ™ Core Certification tests your core expertise implementing and migrating to Snowflake. As a certified professional you can design, develop and manage scalable and secure Snowflake solutions to drive business objectives.

This exam covers architectural principles, core aspects and best practices of Snowflake solutions covering Data Loading/Unloading, Performance, concurrency, scaling, data types, connectors, security and Account Management.

SnowFlake SnowPro Core Exam is always highly recommended to go through practice exams/practice questions to make yourself familiar with the real exam pattern. Whizlabs offers a great set of practice questions for this certification exam

This set consists of 25 questions across all the domains in a SnowFlake SnowPro Core certification. It will give you a clear idea about the type of questions that are asked in the real exam.

SnowFlake SnowPro Core Certification Free Questions

Please find the below set as a free exam exercise that might help you to understand the exam pattern.

Account and Security

Q 1. You are the owner of a table T1 which is in schema S1. The schema is in database D1. In order to grant read-only permissions of this table to a newly created role R1, you will need to…(select all that apply)

A. Grant ‘USAGE’ on database D1
B. Grant ‘SELECT’ on database D1
C. Grant ‘USAGE’ on schema S1
D. Grant ‘SELECT’ on schema S1
E. Grant ‘USAGE’ on table T1
F. Grant ‘SELECT’ on table T1

Correct​ ​Answers:​ A, C, and F

To allow a role to use your database objects, you, as the owner must grant appropriate privileges on the database, schema, and objects. First up, you will need to grant USAGE privilege on the parent database and schema. USAGE privilege enables the grantee to refer to the database and schema in the query. Then, you will need to grant SELECT privilege on the table. The SQL statements are given below:
Grant usage on the database:
grant usage on database D1 to role R1;
Grant usage on the schema:
grant usage on schema D1.S1to role R1;
Grant the ability to query an existing table:
grant select on table D1.S1.T1 to role R1;

Q 2. Identify system-defined roles in Snowflake from the roles given below. (select four)

A. AccountAdmin
B. SysAdmin
C. Auditor
D. RoleAdmin
E. SecurityAdmin
F. Public

Correct Answers: A, B, E, and F

A role in snowflake is essentially a container of privileges on objects. Roles are assigned to users to allow them to perform actions on the objects. A role can be directly assigned to the user, or a role can be assigned to a different role leading to the creation of role hierarchies. The role at a higher level in the hierarchy inherits the privileges of the role at a lower level in the hierarchy. Snowflake defined 5 system-defined roles. These roles, the key features, and the hierarchy of system-defined roles are shown in the diagram below.

account admin snowflake
account admin snowflake

Snowflake Virtual Warehouses

Q 3. What are the minimum and the maximum number of clusters in a multi-cluster warehouse?

A. Minimum: 1, Maximum: 99
B. Minimum: 1, Maximum: 100
C. Minimum: 1, Maximum: 10
D. Minimum: 1, Maximum: unlimited

Correct​ ​Answer:​ C

  • Answer choice-C is correct. In a multi-cluster warehouse, the minimum number of clusters can be 1 and the maximum number of clusters can be 10.

Q 4. When a virtual warehouse is started or resized or resumed, the minimum billing charge is 1 minute.

A. True
B. False

Correct​ ​Answer:​ A

The above statement is True. When a new warehouse is started afresh, or when you resize an existing running warehouse or when a warehouse is resumed (automatically or manually), the warehouse is billed for a minimum of 1 minute’s worth of usage. After the 1st minute has elapsed, all subsequent billing is charged per second.
Practical Info – There is no benefit in stopping a warehouse before the first 1st minute is over because you are anyway going to pay for the first 60-seconds, as the discussion given above shows.
Exam Tip– It is NOT necessary to stop a virtual warehouse to resize it. You can resize a virtual warehouse even when it is executing a query.

Q 5 . When a multi-cluster warehouse is suspended, which of the following Snowflake cache will be purged?

A. Metadata cache
B. Remote disk cache (Resultset cache)
C. Local disk cache
D. All of the above

Correct​ ​Answer: C

  • When a multi-cluster warehouse (or a single cluster warehouse) is suspended, only the local disk cache is dropped. This cache is also known as warehouse cache. This is because this cache is essentially the fast SSD of the warehouse. So when the warehouse is gone, this cache is also gone with it. Answer choice-C is correct.
  • Metadata cache contains Snowflake account object information and statistics. It is always on and it is never dropped. Answer choice-A is incorrect.
  • Remote disk cache (resultset cache) caches exact query results including aggregations etc. and it persists the exact resultset for 24-hours after the query is executed irrespective of the state of the warehouse. Answer choice-C is incorrect.

 Snowflake Overview and Architecture

Q 6. From the given options, select the cloud services provider supported by Snowflake architecture. (three choices)

A. Amazon Web Services (AWS)
B. Google (GCP)
C. HP Virtual Private Cloud
D. Cloud Foundry
E. Microsoft Azure

Correct Answers: A, B, and E

Snowflake is a SaaS product. It supports three public cloud platforms given below.

  1. Amazon Web Services (AWS)
  2. Microsoft Azure (Azure)
  3. Google Cloud Platform (GCP)

Snowflake does NOT support any other cloud platform, any private cloud deployment, or on-prem deployment. Therefore, option choices – HP Virtual Private Cloud and Cloud Foundry – are not correct.

Practical Info: It is not necessary that you have to host Snowflake on the same cloud platform where your organization’s other IT applications are hosted. For example, it is perfectly okay to have Snowflake hosted on Azure and your other IT applications in your organization hosted on AWS/GCP.
Exam Tip: It must be noted that each Snowflake account is hosted in one cloud platform and one region, and you must choose cloud platform and region while opening an account. You cannot change it thereafter. If you want to change region and/or cloud platform at a later point, you will need to open a new Snowflake account in that region and/or cloud platform.
Your organization may open multiple Snowflake accounts (such as Dev, Test, Prod), and they can all reside on the same cloud platform and the same region, OR you may choose to open them on different cloud platforms and/or regions.

Q 7 . What are the key considerations for choosing a specific region for your Snowflake account? (Select all that apply)

A. Cost
B. Proximity to the end-users
C. Data sovereignty
D. Regulatory requirements

Correct Answers: A, B, C, and D

  • Option 1 is correct. Snowflake offers different pricing for computing and storage depending upon the region you choose. For example, a Snowflake instance running in the Mumbai region costs $3.30/credit, whereas the same instance running in the Frankfurt region costs $4.00/credit. So if you are looking to save costs, you would choose a low-cost region.
  • Option 2 is correct. The closer you are to your end-users, data transfer latency would be lower so you can transfer and access data faster. Hence, it is always a good practice to set up your Snowflake account in the region closer to your end-users.
  • Option 3 and 4 are correct. As is often the case in most countries, there are data sovereignty and regulatory restrictions on where the data of users is stored. For example, in the EU, certain sensitive user data is now allowed to cross the EU boundary. India also has similar restrictions enforced by the law. Hence, if you build software that will store such sensitive user data, you will need to check the regulatory requirements and choose the Snowflake region accordingly.

Q 8. Informatica, Matillion, Azure data factory are examples of ………………….. partners of the Snowflake partner ecosystem.

A. Data Management
B. Business Intelligence
C. Data Integration
D. Data Science

Correct Answer: C

Informatica, Matillion, and Talend are data integration partners that provide the following functionalities:
Extract – Exporting data from the data source.
Transform – Modifying data according to business rules.
Load – Loading data into Snowflake.
The below diagram shows Snowflake partner ecosystem players divided based on the functional categories.

ecosystem-overview
ecosystem-overview

Snowflake partner eco-system (image source: Snowflake official documentation)

Q 9 . Which of the following programming languages are supported in Snowflake to write user-defined functions (UDFs)?

A. SQL
B. Java
C. JavaScript
D. Python

Correct​ ​Answers:​ A and C

User-defined functions (UDFs) can be used in Snowflake to write custom business logic which is hard to encapsulate within individual SQL statements. UDFs (together with Stored Procedures) enable database-level programming in Snowflake.
At present, Snowflake supports two programming languages to write UDFs: Javascript UDFs and SQL UDFs.
Practical Info– Stored procedures in Snowflake are written in Javascript. The javascript code can issue SQL statements to process data in the Snowflake database. An example of a Snowflake stored procedure that clears a log table using SQL is given below: (this is only for your understanding, we are not expecting coding related questions in the exam)
create or replace procedure clear_log()
returns int not null
language javascript
as
$$
var delete_log_sql_command = “delete from user_log”;
var return_value = 0;
var statement1 = snowflake.createStatement( {sqlText: delete_log_sql_command} );
var result_set1 = statement1.execute();
return_value = 1;
return return_value;
$$

Q 10. Which Snowflake edition supports transmitting data in encrypted form over the network between VPCs (virtual private cloud)?

A. All editions
B. Enterprise edition and above
C. Business Critical edition and Above
D. All except for the Virtual Private Snowflake (VPS) edition

Correct​ ​Answer: C

A Snowflake account on AWS (or Azure) is implemented as a VPC. There are two ways to establish communication between your Snowflake VPC and other VPCs (e.g. your organization’s VPC). One is to transmit the traffic over the public internet. Other (and safer) option is to establish an exclusive, highly secure network between your Snowflake account and your other AWS VPCs (in the same AWS region), fully protected from unauthorized access. To implement this secure channel of communication between VPCs, AWS supports a feature called AWS PrivateLink (Azure also supports a similar feature called Azure PrivateLink). Snowflake offers support for AWS PrivateLink (and Azure PrivateLink) based communication in Business Critical Edition and above.

Q 11. More than one clustering key can co-exist in a Snowflake table

A. True
B. False

Correct​ ​Answer: B

This statement is false. You can define at most one clustering key in a Snowflake table to organize micro-partitions. When you define a clustering key, Snowflake will reorganize the naturally clustered micro-partitions and will relocate related rows to the same micro-partition and group them according to the clustering key. This process is called Reclustering.
Practical Info – Reclustering happens automatically once a clustering key is defined for a table. The process consumes credits. So be cognizant of the cost when you go for reclustering.

Q 12. Which of the following statements will you use to change the warehouse for workload processing to a warehouse named ‘COMPUTE_WH_XL’?

A. SET CURRENT_WAREHOUSE = COMPUTE_WH_XL
B. USE WAREHOUSE COMPUTE_WH_XL;
C. USE CURRENT_WAREHOUSE(‘COMPUTE_WH_XL’);
D. SET CURRENT_WAREHOUSE = COMPUTE_WH, SIZE = XL;

Correct​ ​Answer: B

A session context in Snowflake consists of 4 objects:

  1. Role
  2. Warehouse  
  3. Database
  4. Schema

You can set appropriate session context using a set of SQL statements given below. These statements specify the role, warehouse, database, or schema to use for the current session:

You can set appropriate session context using a set of SQL statements given below. These statements specify the role, warehouse, database, or schema to use for the current session:
Use warehouse <<warehouse-name>>
Use role <<role-name>>
Use database <<database-name>>
Use schema <<schema-name>>
So the correct answer of this question is:
Use warehouse COMPUTE_WH_XL;

Q 13. In the case of a Snowflake account created on AWS, ……………….. is responsible for the management of Availability Zones?

A. Customer
B. Snowflake
C. Cloud Provider
D. It is a shared responsibility

Correct​ ​Answer:​ C

An Availability Zone (AZ) is essentially a group of one or more physically separated data centers with redundant power, networking, and connectivity and located within a single cloud region. Each cloud region has multiple AZs (most have 3 or more). The AZs are connected with high-bandwidth, low-latency network infrastructure and support synchronous replication. Due to the automatic, synchronous replication and physical separation plus isolation, AZs enable your applications and databases to be highly available. When one AZ within a region fails, another one should remain active and the switch between the two zones is silent and transparent to customers. The cloud providers are responsible for the maintenance of AZ infrastructure and replication of your databases and switching to alternate AZ in case of any failure.

Snowflake Storage and Protection

Q 14. Once the time-travel period has expired, it is possible to request Snowflake support to retrieve historical data for a period of

A. Day
B. Days
C. Days
D. It depends on the Snowflake edition
E. It is user-configurable

Correct​ ​Answer:​ B

After the time travel data retention period is over, you can use Snowflake’s fail-safe feature to recover your data. The duration of the fail-safe period is 7 days (Answer choice 2 is correct). This is a fixed duration and cannot be changed. Only Snowflake support personnel can help recover data during the fail-safe period. The fail-safe feature is available to all customers irrespective of the Snowflake edition.
The below diagram succinctly summarizes key differences between Snowflake’s two important data protection features – time-travel and fail-safe.

Data protection features in Snowflake
Data protection features in Snowflake

 

Q 15. Which of the following statements are TRUE concerning a stream object in Snowflake? (select all that apply)

A. A stream object provides a record of DML changes (inserts, updates, deletes) made to a table at row level.
B. A stream object can keep track of DML changes for the entire life of a table.
C. Streams on materialized views are not supported.
D. Streams on external tables are not supported.

Correct​ ​Answers:​ A and C

Answer choice-A is correct. A stream object provides change tracking over a source table. It records DML changes made to tables, (inserts, updates, and deletes) as well as metadata about each change. This is referred to as Change Data Capture (CDC), and this feature is extensively used in data warehousing scenarios to create data pipelines. Please note that the stream object itself does not store this data. It relies on the version history of source data maintained in the metadata layer.
Answer choice-B is incorrect. Stream object keeps track of DML changes of a source table up until the data retention period of the source table. After that, the DML changes are no longer accessible.
Answer choice-C is correct. Currently, Snowflake does not support creating stream objects on materialized views.
Answer choice-D is incorrect. Snowflake supports creating insert-only stream objects on external tables.

Q 16. Only one stream object can be created on a source table

A. True
B. False

Correct​ ​Answer:​ B

  • The above statement is false. You can create any number of streams on a source table. These streams can have the same or different offset positions. One example of creating multiple streams is when you want to report month-on-month changes, week-on-week changes, and day-on-day changes happening in a product inventory table of your POS database. In this case, you may create three streams on the table to record monthly, weekly and daily changes. All three streams exist independently of each other with their respective offset positions.

Q 17. Snowflake replicates the following layer(s) across availability zones (select two)

A. Cloud Services Layer
B. Warehouse Layer
C. Storage Layer
D. Cloud Agnostic Layer
E. Data Sharing Layer

Correct​ ​Answers: A and C

Snowflake replicates the cloud services layer (Answer choice-A) and the storage layer (Answer choice-C) across the availability zones of your cloud and region.

Data loading/unloading

Q 18. Which Snowflake edition supports Search Optimization Service to improve performance of point lookup queries?

A. All editions
B. Enterprise edition and above
C. Business Critical edition and Above
D. All except Virtual Private Snowflake (VPS) edition

Correct​ ​Answer:​ B

Search Optimization Service in Snowflake is a background service that is designed to improve the performance of point lookup queries. A point lookup query is essentially a SELECT statement that returns only a small number of distinct rows from a large dataset. The service runs transparently from an end-user’s standpoint. Only the Enterprise edition and above provide this feature.

Q 19. Consider the XML given below. The XML file is loaded in a User Stage:

<bookshelf>
<book>
<name>Famous Five</name>
<author> Roald dahl</author>
</book>
<book>
<name>Secret Seven</name>
<author> Enid Blayton</author>
</book>
<book>
<name>Mahashweta</name>
<author> Sudha Murthy</author>
</book>
</bookshelf>

To remove the top-level element <bookshelf/> and load <book/> elements into separate rows in a Snowflake table, which of the following file format options will be used in the COPY INTO command?

A. STRIP_TOP_ELEMENT
B. STRIP_OUTER_ELEMENT
C. STRIP_OUTER_ARRAY
D. STRIP_FIRST_ELEMENT

Correct​ ​Answer:​ B

Enabling the STRIP_OUTER_ELEMENT file format option for the COPY INTO <table> command to remove the outer element and load the <book/> records into separate table rows:
copy into <table> from @~/<file>.xml
file_format = (type = ‘XML’ strip_outer_element = true);

Exam Tip: If the semi-structured file format is JSON (instead of XML), you will need to enable STRIP_OUTER_ARRAY file format option for the COPY INTO command.

Performance and Tuning

Q 20. History’ tab in the Snowflake Web Interface supports viewing of user queries submitted in the past………………… day(s)

A. 24 hours or 1 day
B. 7 days
C. 14 days
D. 90 days

Correct Answer: C

‘Query History’ view in Snowflake Web UI stores queries executed and query profile information for all queries, executed by all users, using any interface (e.g., Web UI, SnowSQL, Business Application… or any other interface) for a period of 14 days
Exam Tip: In addition to storing queries and profiler, it also allows users to download the resultset of queries within 24 hours. However, the most important point here is: You cannot view other people’s query results if your role and the role of the user who executed the query are different.

Q 21. What is the optimal file sizing recommendation for loading data into Snowflake?

A. 10 MB to 100 MB, uncompressed
B. 100 MB to 250 MB, compressed
C. 100 MB to 250 MB, uncompressed
D. 10 GB to 100 GB, compressed

Correct​ ​Answer: B

Snowflake recommends that to optimize the number of parallel operations for a load, data files should roughly be 100-250 MB in size, compressed. In case input data files are smaller than this, it is better to aggregate multiple input files. On the other hand, if the input data files are larger than this, it is better to split them into smaller files to match this guidance.
Snowflake also recommends against loading very large files (e.g., 100 GB+ sizes) without splitting them as splitting of files will take advantage of parallelism.
Practical Info – The recommendation applies to both modes of data loading – bulk load using SnowSQL (COPY INTO) and continuous/micro-batch using Snowpipe.

Q 22. Selecting the right clustering key can dramatically improve query performance. Following are the good yardsticks to choose an appropriate clustering key:

A. Use columns that are most frequently found in WHERE clause
B. A date column is usually a not a good candidate to be in the clustering key
C. A join column can be considered as clustering key
D. Using expressions in the clustering key may help reduce cardinality.

Correct​ ​Answers:​ A, C, and D

Answer choice-A is correct. Choosing the clustering keys from dimensions (i.e. columns in the WHERE clause) used in often repeating queries will significantly improve the performance of these queries.
Answer choice-B is incorrect. If you have queries that filter the data based on date criteria, you can use the date column in the clustering key. However, you will need to check the cardinality of the date column before using it in the clustering key.
Answer choice-C is correct. Columns used in the join clauses with other tables are worthy candidates to be in the clustering key.
Answer choice-D is correct. In case you would like to use a particular column in a clustering key but the column has very high cardinality, using expressions may help reduce cardinality. For example, you can use SUBSTR expression to get a relevant portion of a string column for use in the clustering key.

Q 23. Selecting the right clustering key can dramatically improve query performance. Following are the good yardsticks to choose an appropriate clustering key:

A. Use columns that are most frequently found in WHERE clause
B. A date column is usually a not a good candidate to be in the clustering key
C. A join column can be considered as clustering key
D. Using expressions in the clustering key may help reduce cardinality.

Correct​ ​Answers:​ A, C, and D

Answer choice-A is correct. Choosing the clustering keys from dimensions (i.e. columns in the WHERE clause) used in often repeating queries will significantly improve the performance of these queries.
Answer choice-B is incorrect. If you have queries that filter the data based on date criteria, you can use the date column in the clustering key. However, you will need to check the cardinality of the date column before using it in the clustering key.
Answer choice-C is correct. Columns used in the join clauses with other tables are worthy candidates to be in the clustering key.
Answer choice-D is correct. In case you would like to use a particular column in a clustering key but the column has very high cardinality, using expressions may help reduce cardinality. For example, you can use SUBSTR expression to get a relevant portion of a string column for use in the clustering key.

Q 24. Which cache does the query optimizer refer to first? Second? And Third?

A. First: warehouse cache. Second: results cache. Third: metadata cache
B. First: warehouse Cache. Second: metadata Cache. Third: results cache
C. First: metadata Cache. Second: results Cache. Third: warehouse cache
D. First: metadata Cache. Second: warehouse Cache. Third: results cache

Correct​ ​Answer: C

The query optimizer in the Snowflake cloud services layer examines the metadata cache first. Thereafter it refers to the results cache next and finally the warehouse cache.

Semi-Structured data

Q 25. Which of the following data types are supported in Snowflake for semi-structured data? (select all that apply)

A. VARIANT
B. OBJECT
C. TEXT
D. CLOB
E. ARRAY

Correct​ ​Answer​s:​ A, B, and E

Snowflake supports three data types for processing semi-structured data.
VARIANT – The basic (and most used) data type for storing semi-structured data.
OBJECT – Used to store key-value pairs, where the key is a non-empty string, and value is semi-structured data in VARIANT data type.
ARRAY – Used to store arrays of semi-structured data where array index is non-negative integer and value is semi-structured data in VARIANT data type.

When are you planning to take SnowFlake SnowPro Core Exam? Do share your feedback in comments and help other learners with real exam experience. Whizlabs practice tests are based on real exam pattern; we have received good feedback from exam takers.

Reference links:

 

 

 

 

 

About Jeevitha TP

Jeevitha has a proven experience with a solid understanding of SEO activities such as content strategy, link building, and keyword strategy to increase rankings on all major search networks. Further, she works closely with the editorial and marketing teams to drive SEO in content creation and programming.
Spread the love

LEAVE A REPLY

Please enter your comment!
Please enter your name here