Snowflake SnowPro Core Certification Practice Exam

Snowflake SnowPro Core Practice Exam with Solutions Part 1

Snowflake SnowPro Core Practice Exam.

During these last months, we have studied how Snowflake works in detail, along with some questions to prepare for the Snowflake SnowPro Core Certification. Apart from that, after each chapter, we solved exercises to see if we understood everything.
We will take random questions from each chapter to practice for the official exam in this chapter. Remember, we need 80 points to pass it! If we don’t reach this number, I recommend you reread the chapters in which we missed some questions and try the test again. Good luck, and go for it!

Remember that you can find this exam FOR FREE at FullCertified. Take it now with our exam simulator!

FullCertified | Snowflake, AWS, and Azure Practice Exams

All the chapters from the course at the following link:

Road to SnowFlake SnowPro Core Certification: Complete Course

QUESTIONS WITH SOLUTIONS

1: What types of views are available in Snowflake?

1. Regular
2. Secure View
3. Table View
4. Materialized View
5. External View

Solution: 1, 2, 4. You can see the differences between them in the following image:

Difference between Snowflake views.

2: Which character identifies a table stage?

1. “@”
2. “%”
3. “/@”
4. “@%”

Solution: 4. The following example shows how we can upload files from our local disk to a Table stage. As you can see, you need to specify the previous character while indicating the table:

PUT file://c:/myData/myCSV.csv @%myTable

3: Select two limitations with materialized views:

1. Time Travel is not supported.
2. We cannot define Cluster Keys.
3. Streams cannot track changes.

Solution: 1, 3

4: Can you resize the warehouse once you have selected the size?

1. True
2. False

Solution: 1. You can always change the warehouse size depending on your needs, even when it’s running.

5: Which of the below APIs are SnowPipe REST APIs?

1. insertFiles
2. insertReport
3. insertHistoryScan
4. loadFiles
5. loadHistoryScan

Solution: 1, 2, 5. You can make calls to REST endpoints to get information. For example, by calling the following insertReport endpoint, you can get a report of files submitted via insertFiles:

GET https://<account_id>.snowflakecomputing.com/v1/data/pipes/<pipe_name>/insertReport

6: What happens to the incoming queries when a warehouse does not have enough resources to process them?

1. Queries are aborted
2. Queries are queued and executed when the warehouse has resources
3. Snowflake resizes the warehouse

Solution: 2. If the warehouse does not have enough remaining resources to process a query, the query is queued, pending resources that become available as other running queries complete.

7: Which are the additional columns that the streams create?

1. METADATA$ACTION
2. METADATA$ISREAD
3. METADATA$ISUPDATE
4. METADATA$ROW_ID
5. METADATA$COLUMN_ID

Solution: 1, 3, 4. METADATA$ACTION Indicates the DML operation (INSERT, DELETE) recorded. METADATA$ISUPDATE indicates whether the operation was part of an UPDATE statement. METADATA$ROW_ID is a unique and immutable ID for the row.

8: Can two different virtual warehouses from the same account access the same data simultaneously without any contention issue?

1. True
2. False

Solution: 1. All the warehouses of your account share the storage layer, so they can access the same data simultaneously.

9:What technique does Snowflake use to limit the number of micro-partitions retrieved as part of a query?

1. Pruning
2. Clustering
3. Indexing
4. Computing

Solution: 1. Query pruning consists of analyzing the smallest number of micro-partitions to solve a query. This technique retrieves all the necessary data to give a solution without looking at all the micro-partitions, saving a lot of time to return for the result.

10: Which command will we use to download the files from the stage/location loaded through the COPY INTO <LOCATION> command?

1. GET
2. PUT
3. UNLOAD
4. INSERT INTO

Solution. 1. We will use the GET command to DOWNLOAD files from a Snowflake internal stage (named internal stage, user stage, or table stage) into a directory/folder on a client machine. You need to use SnowSQL to use this command.

11: Can virtual warehouses be resized while they are running?

1. True
2. False

Solution: 1. A warehouse can be resized up or down (through the web interface or using SQL) at any time, including while it is running and processing statements. In the following image, you can see how to resize them using the web interface:

Resizing Virtual Warehouse on Snowflake using the User Interface.

12: Which of the following roles are the default ones in Snowflake?

1. ACCOUNTADMIN
2. SECURITYADMIN
3. VIEWER
4. USERADMIN
5. SYSADMIN
6. NETWORKADMIN

Solution: 1, 2, 4, 5

13: Does Snowflake automatically stores data in encrypted form in all editions?

1. True
2. False

Solution: 1. Snowflake encrypts all customer data by default at no additional cost.

14: What different types of streams exist in Snowflake?

1. Standard
2. Append-only
3. Update-only
4. Insert-only

Solution: 1, 2, 4. Standard and Append-only streams are supported on tables, directory tables, and views. The Standard one tracks all DML changes to the source table, including inserts, updates, and deletes, whereas the Append-only one Tracks row inserts only. The Insert-only stream also tracks row inserts only. The difference with the previous one is that this one is only supported on EXTERNAL TABLES.

15: In which of the below scenarios will you use an external table?

1. You have data on the cloud providers, but the data cannot be copied or moved to any other location due to compliance regulations.
2. You have a high volume of data on the cloud providers, but we only need a part of the data in Snowflake.
3. You have data on the cloud providers that need to be updated by Snowflake.
4. You have XML data on the cloud provider.

Solution: 1, 2. The third answer is incorrect, as external tables can only read data. The fourth option is also wrong, as external tables don’t support XML.

16: Is SnowPipe Serverless?

1. True
2. False

Solution: 1. Snowpipe enables loading data when the files are available in any (internal/external) stage. You use it when you have a small volume of frequent data, and you load it continuously (micro-batches). Snowpipe is serverless, which means that it doesn’t use Virtual Warehouses. You can see how Snowpipe works in the following diagram:

How Snowpipe works.

17: Does data stored in a temporary table not contribute to Snowflake’s storage cost?

1. True
2. False

Solution: 2. With temporary tables, you can optimize storage costs, as when the Snowflake session ends, data stored in the table is entirely purged from the system. But they also require storage costs while the session is active. A temporary table is purged once the session ends, so the retention period is for 24 hours or the remainder of the session.

18: Which data-loading method requires a user-specified warehouse to execute COPY statements?

1. Bulk Data Load
2. SnowPipe
3. Both

Solution: 1. Snowpipe is serverless, meaning it doesn’t need a running warehouse to load data into Snowflake.

19: Is Snowflake available on-premise?

1. True
2. False

Solution: 2. Snowflake is 100% SaaS.

20: Which character identifies a user stage?

1. “@~”
2. “~”
3. “@”

Solution: 1. Each user has a Snowflake personal stage allocated to them by default for storing files, and no one can access them except the user it belongs to. It’s represented with the “@~” character. In the following example, we are uploading the file “myfile.csv” to the stage from the current user:

PUT file://C:\data\myfile.csv @~

21: Which table function allows you to convert semi-structured data to a relational representation?

1. FLATTEN
2. CHECK_JSON
3. PARSE_JSON

Solution: 1. FLATTEN is a table function that allows us to convert semi-structured data to a relational representation, taking a VARIANT, OBJECT, or ARRAY column and producing a lateral view.

22: How is the data storage cost computed for Snowflake?

1. Based on the average daily amount of uncompressed data stored.
2. Based on the average daily amount of compressed data stored.
3. Based on the amount of uncompressed data stored on the last day of the month.
4. Based on the amount of compressed data stored on the last day of the month.

Solution: 2. Storage costs benefit from the automatic compression of all data stored, and the total compressed file size is used to calculate the storage bill for an account.

23: Can two Virtual Warehouses access the same data simultaneously without any contention issues?

1. True
2. False

Solution: 1. Cloud services tie together all of the different components of Snowflake to process user requests, from login to query dispatch.

24: In which layer of Snowflake architecture is stored all security-related information?

1. Storage
2. Compute
3. Cloud Services
4. All of the above.

Solution: 3. The Cloud Services layer is a collection of services coordinating activities across Snowflake. It’s in charge of Authentication, Infrastructure management, Metadata management, Query parsing and optimization, and Access control.

25: While loading data through the COPY command, you can transform the data. Which of the below transformations are allowed?

1. Truncate columns
2. Omit columns
3. Filters
4. Reorder columns
5. Cast
6. Aggregate

Solution: 1, 2, 4, 5

26: Is clustering generally more cost-effective for tables that are queried frequently and do not change often?

1. True
2. False

Solution: 1. The more frequently a table changes, the more expensive it will be to keep it clustered.

27: Do tables with Fail-Safe turned on incur additional storage costs compared to tables with Fail-Safe turned off?

1. True
2. False

Solution: 1. Fail-safe ensures historical data is protected in the event of a system failure or other catastrophic event, providing a (NON-CONFIGURABLE) 7-day period during which Snowflake support may recover historical data. It requires additional storage (as other functionalities like Time Travel), that’s why it incurs additional storage costs. You can see an example of how Fail-Safe works in the following image:

How Fail-Safe works in Snowflake.

28:Which of the following clustering metadata for the micro-partitions is maintained by Snowflake in a table?

1. The number of micro-partitions that comprise the table.
2. The number of micro-partitions containing values that overlap with each other.
3. The depth of the overlapping micro-partitions.
4. None of the above.

Solution: 1, 2, 3.

29: For which activities does Snowflake have administration settings to help with resource consumption?

1. Help control costs associated with unexpected warehouse credit usage
2. Manage access to Snowflake for specific users
3. Manage the availability of the product

Solution: 1. Snowflake provides resource monitors to help control costs and avoid unexpected credit usage caused by running warehouses. You can impose limits on the number of credits that warehouses consume.

30: After how many days does the load history of SnowPipe expire?

1. One day
2. 14 days
3. 90 days
4. 180 days

Solution: 2. The load history is stored in the metadata of the pipe for 14 days. Must be requested from Snowflake via a REST endpoint, SQL table function, or ACCOUNT_USAGE view.

31: The three main layers of SnowFlare are…

1. Extraction, Ingestion, Load
2. Database Storage, Compute & Cloud Services
3. Database, Virtual Warehouse, Data Experience

Solution: 2.

32: Which Snowflake object stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations (Amazon S3, Google Cloud Storage, or Microsoft Azure)?

1. Storage Schema
2. Storage Integration
3. User Stage

Solution: 2. A storage integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage. This option will enable users to avoid supplying credentials when creating stages or when loading or unloading data.

Create storage integration commands on Snowflake.

33: Does SnowPipe guarantee that files are loaded in the same order they are staged?

1. True
2. False

Solution: 2. Snowpipe generally loads older files first, but there is no guarantee that files are loaded in the same order they are staged.

34: What statement is true about Snowflake’s unique architecture?

1. One Node Shared Data
2. Multi-Cluster Shared Data
3. One Node Private Data

Solution: 2. Snowflake’s architecture is a hybrid of traditional shared-disk and shared-nothing database architectures. Snowflake uses a central data repository for persisted data accessible from all compute nodes in the platform. At the same time, it processes queries using virtual warehouses where each node in the cluster stores a portion of the entire data set locally.

35: What will happen to the child task if you remove its predecessor?

1. The child task is removed from the system
2. The child task may become the root task
3. The child task may become a standalone task

Solution: 2, 3 Also, if the owner role of a task is deleted, the Task Ownership is reassigned to the role that dropped this role. This is a typical exam question.

36: Select the statements that are true about Fail-Safe:

1. There is no difference between Time-Travel and Fail-Safe
2. The data retention period for a permanent table with 30 days of Time-Travel is 37 days
3. Fail-Safe provides a non-configurable 7-day period
4. Fail-Safe provides a non-configurable 90-day period
5. Fail-Safe ensures that historical data is protected in the event of a system failure or other catastrophic events

Solution: 2, 3, 5. Regarding the second option, we have 30 days of Time-Travel, apart from 7 days of Fail-Safe. 37 days in total.

37: Which cache runs for 24 hours?

1. Metadata cache
2. Results cache
3. Warehouse cache

Solution: 2. Query Result cache is also known as Results Cache, which holds the results of every query executed in the past 24 hours.

38:Which factors influence the unit cost of Snowflake credits and data storage?

1. Snowflake Edition.
2. Region of the Snowflake account.
3. On-Demand or Pre-Paid account.
4. Users on Snowflake.

Solution: 1, 2, 3. You can create as many users as you want without additional cost.

39: What can you easily check to see if a large table will benefit from explicitly defining a clustering key?

1. Clustering depth
2. Clustering ratio
3. Values in a table

Solution: 1. The clustering depth measures the average depth of the overlapping micro-partitions for specified columns in a table (1 or greater). The smaller the cluster depth is, the better clustered the table is. You can get the clustering depth of a Snowflake table using this command:

SELECT SYSTEM$CLUSTERING_DEPTH('MY_TABLE');

40: If you want a multi-cluster warehouse, which is the lowest Snowflake edition that you should opt for?

1. Standard
2. Enterprise
3. Business Critical
4. Virtual Private Snowflake

Solution: 2. You can see some differences between the Snowflake editions in the following image:

Difference between Snowflake editions.

41: Which system functions are available in Snowflake to view/monitor the clustering metadata for a table?

1. SYSTEM$CLUSTERING_DEPTH
2. SYSTEM$CLUSTERING_INFORMATION
3. SYSTEM$CLUSTERING_METADATA

Solution: 1, 2. The clustering depth measures the average depth of the overlapping micro-partitions for specified columns in a table (1 or greater). The smaller the cluster depth is, the better clustered the table is. You can use any previous commands to get the Cluster Depth of a table.

42: A query executed a couple of hours ago, which took more than 5 minutes to run, is executed again, and it returned the results in less than a second. What might have happened?

1. Snowflake used the persisted query results from the metadata cache
2. Snowflake used the persisted query results from the query result cache
3. Snowflake used the persisted query results from the warehouse cache
4. A new Snowflake version has been released in the last two hours, improving the speed of the service

Solution: 2. The query result cache stores the results of our queries for 24 hours, so as long as we perform the same query and the data hasn’t changed in the storage layer, it will return the same result without using the warehouse and without consuming credits.

43: Do you need to contact Snowflake Support to retrieve data from Fail-Safe?

1. True
2. False

Solution: 1. Fail-Safe provides a (NON-CONFIGURABLE) 7-day period during which Snowflake support may recover historical data, but you cannot recover this data by yourself; you have to ask Snowflake support.

44: Which type of data incurs Snowflake storage costs?

1. Data Stored in permanent tables.
2. Data Stored in temporal tables.
3. Cache results.
4. Data retained for Fail-Safe & Time-Travel.

Solution: 1, 2, 4. Storage fees are incurred for maintaining historical data during both the Time Travel and Fail-safe periods. To help manage the storage costs, Snowflake provides temporary and transient tables, which do not incur the same fees as permanent tables but also incur charges. We can also include data stored in Snowflake locations (i.e., user and table stages or internal named stages).

45: What should be the first option to restore data into a table?

1. Time-Travel
2. Fail-Safe
3. Zero-Copy Cloning

Solution: 1. Time-Travel enables accessing historical data (i.e., data that has been changed or deleted) at any point within a defined period. If we drop a table, we can restore it with time travel. You can use it with Databases, Schemas & Tables. The following diagram explains how Time-Travel works:

How Time-Travel works

46: What statements are true about streams?

1. A Stream itself does NOT contain any table data.
2. A stream only stores the offset for the source table
3. The hidden columns used by a stream consume storage

Solution: 1, 2, 3. Streams are Snowflake objects that record data manipulation language (DML) changes made to tables and views, including INSERTS, UPDATES, and DELETES, as well as metadata about each change. All of the previous options are correct about them.

47: How is query processing done in Snowflake?

1. AWS EMR with Spark
2. AWS EC2 with Spark
3. Virtual Warehouses

Solution: 3. A Virtual Warehouse is a cluster of computing resources in Snowflake. It provides the resources necessary to perform queries and DML operations, such as CPU, memory, and temporary storage. While a warehouse is running, it consumes Snowflake credits.

48: What option will you specify to delete the stage files after a successful load into a Snowflake table with the COPY INTO command?

1. DELETE = TRUE
2. REMOVE = TRUE
3. PURGE = TRUE
4. TRUNCATE = TRUE

Solution: 3. If the PURGE option is set to TRUE, Snowflake will try its best to remove successfully loaded data files from stages. If the purge operation fails for any reason, it won’t return any error for now:

COPY INTO mytable PURGE = TRUE;

49: What actions can the resource monitor associated with a Warehouse take when it reaches (or is about to) hit the limit?

1. Suspend the WareHouse
2. Send notification alert
3. Kill the query that is running
4. Delete the Snowflake account

Solution: 1, 2, 3. A resource monitor can Notify, Notify & Suspend, and Notify & Suspend Immediately. You can see these three actions in the following image:

Actions that can be taken by a Resource Monitor on Snowflake.

50: We need to temporarily store intermediate data, which an ETL process will only use. We don’t need the data outside the ETL process. If you want to optimize storage cost, what type of table will you create to store this data?

1. Permanent
2. Temporary
3. Transient
4. External

Solution: 2. With temporary tables, you can optimize storage costs, as when the Snowflake session ends, data stored in the table is entirely purged from the system. But they also require storage costs while the session is active. A temporary table is purged once the session ends, so the retention period is for 24 hours or the remainder of the session.

More Practice Exam Questions?

The next 50 questions are available at the following link. And remember:

• Do you want to download THESE questions in PDF FOR FREE?
• Access to a real exam simulator to thoroughly prepare for the exam.
• Do you want more than 300 Snowflake SnowPro Core practice questions?
• Download the ultimate cheat sheet for the Snowflake SnowPro Core exam!

You can find 3 Snowflake SnowPro Core exams at FullCertified!

FullCertified | Snowflake, AWS, and Azure Practice Exams

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! :)