FullCertified LogoFullCertified
  • Home
  • Pricing
  • FAQ
  • Blog

    • Free Snowflake SnowPro Core Practice Questions

    May 16, 2024

    Free Questions

    The Snowflake SnowPro Core exam is a pivotal step in your certification path. By obtaining the SnowPro Core Certification, individuals can showcase their ability to effectively leverage Snowflake's powerful features, optimize performance, and ensure data integrity. Achieving Snowflake SnowPro Core certification signifies mastery of Snowflake's foundational concepts.

    Free Snowflake Practice Exam Questions

    At FullCertified, we totally know the importance of Snowflake Certifications. To help you prepare for the Snowflake SnowPro Core Certification exam, here are some free questions to test your understanding of the core concepts. Let's review them in depth!

    1) Is re-clustering in Snowflake only triggered if the table would benefit from the operation?

    1. True

    2. False

    Solution: A.

    DML operations (INSERT, UPDATE, DELETE, MERGE, COPY) can make the data in the table become less clustered. To solve that, Snowflake provides periodic & automatic re-clustering to maintain optimal clustering. It only reclusters a clustered table if it benefits from the operation.

    2) What are the usual data loading steps in Snowflake?

    1. Source → Snowflake Stage → Snowflake Table

    2. Source → Snowflake Table → Snowflake Stage

    3. Snowflake Table → Source → Snowflake Stage

    Solution: A.

    A source system (like a web application) loads the data into a Snowflake Stage (for example, an external stage like Amazon S3). Then we can copy the data into a Snowflake table. You can see this behavior in the following image:

    3) How will you store JSON data in Snowflake?

    1. Using a column of the JSON type

    2. Using a column of the VARCHAR type

    3. Using a column of the VARIANT type

    4. Using a column of the NULL type

    Solution: C.

    Semi-structured data is saved as VARIANT type in Snowflake tables, with a maximum limit size of 16 MB of uncompressed data, and it can be queried using JSON notation.

    A VARIANT can store a value of any other type, including OBJECT and ARRAY.

    For example, you can store the following JSON in a VARIANT column:

    {"a":null,"b":"test", "c":[null, 2,3]}

    4) Do tables with Fail-Safe turned on incurs additional storage costs compared to tables where Fail-Safe is turned off?

    1. True

    2. False

    Solution: A.

    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:

    5) In which of the below scenarios is Snowpipe recommended to load data?

    1. When we have a huge volume of data generated as part of a batch schedule

    2. When we have a small volume of frequent data

    3. In both of the previous scenarios.

    Solution: B.

    Snowpipe is a serverless service that enables loading data when the files are available in any (internal/external) stage. It is used when you have a small volume of frequent data and want to load it continuously (micro-batches). In the following image, you can see how Snowpipe works:

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

    1. Clustering

    2. Indexing

    3. Computing

    4. Pruning

    Solution: D.

    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.

    7) 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: A.

    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.

    The command cannot be executed from the Snowflake Web Interface; you need to use SnowSQL. For example, if we want to download data from an internal stage called “my_int_stage” into our “data” folder, you can run the following command:

    GET @my_int_stage file:///tmp/data/;

    8) Can virtual warehouses be resized while they are running?

    1. True

    2. False

    Solution: A.

    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:

    You can also resize them using SQL. For example:

    ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = SMALL;

    9) For which activities does Snowflake have administration settings to help with resource consumption?

    1. Manage access to Snowflake for specific users

    2. Manage the availability of the product

    3. Help control costs associated with unexpected warehouse credit usage

    Solution: C.

    Snowflake provides different administration settings to help control costs and avoid unexpected credit usage caused by running warehouses.

    For example, you can impose limits on the number of credits that warehouses consume by using Resource Monitors.

    10) Does GET support downloading files from external stages?

    1. True

    2. False

    Solution: B.

    When you use an external stage in Snowflake, you reference files stored in the external storage service without physically moving the data into Snowflake's internal storage.

    The GET command in Snowflake is used to download files from internal stages within Snowflake, not from external stages. Internal stages are storage areas within Snowflake where data files are stored temporarily during data loading processes. This command does not support downloading files from external stages.

    If you want to download data from external stages, you need to use the cloud provider; for example, download them directly from Amazon S3.

    11) A query executed a couple of hours ago, which spent 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: B.

    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 or consuming credits.

    Thanks to this Snowflake feature, the results, even if they took a long time to be returned the first time you executed the query, will be returned quickly in the following executions.

    12) Which table function allows you to convert semi-structured data to a relational representation?

    1. FLATTEN

    2. CHECK_JSON

    3. PARSE_JSON

    Solution: A.

    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.

    13) What are the different stages available in Snowflake?

    1. User

    2. Table

    3. Named internal

    4. Named external

    5. Account

    Solution: A, B, C, D.

    Snowflake stages are a big topic in this exam. Let's review them:

    • External stages reference data files stored outside Snowflake

    • Internal stages store data files internally within Snowflake.

    • The User stage (represented with "@~") is a personal stage for each user

    • The Table stage (represented with "@%") is the one each table has by default.

    You can see the different stages in the following image:

    14) Compute cost in Snowflake depends on…

    1. The query execution time

    2. The query execution time and the waiting query time

    3. The warehouse size and for how long it runs

    Solution: C.

    [AI Generated explanation]

    In Snowflake, the cost of compute depends on the warehouse size and the duration for which it runs. The correct answer is C) The warehouse size and for how long it runs.

    The query execution time (A) and the waiting query time (B) are not directly related to the cost of compute in Snowflake. These factors may affect the efficiency and performance of the queries, but they do not determine the cost. Snowflake's pricing is primarily based on the size of the warehouse (which determines the available compute resources) and the duration for which the warehouse is running.

    By selecting an appropriate warehouse size and managing the duration effectively, users can optimize and control their compute costs in Snowflake.

    15) Can you resize the warehouse once you have selected the size?

    1. True

    2. False

    Solution: A

    You can always change the warehouse size depending on your needs, even when it's running.

    You can easily resize them using web interface or with the ALTER WAREHOUSE command:

    ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = SMALL;

    16) Do UDFs support both SQL & JavaScript in Snowflake?

    1. True

    2. False

    Solution: A

    User-defined functions (UDFs) let you extend the system to perform operations that are not available through Snowflake’s built-in, system-defined functions. They support SQL, JavaScript, Java, Python, and Scala (this last one under Preview Feature).

    17) You have two virtual warehouses in your Snowflake account. If one of them updates the data in the storage layer, when will the other one see it?

    1. Immediately

    2. After an average time of 5 seconds

    3. After the sync process

    Solution: A

    All the warehouses of your account share the storage layer, so if the data is updated, all the warehouses will be able to see it.

    You can see this behavior in the following image:

    18) Select the term that is associated with the compute layer:

    1. Query optimization

    2. Query planning

    3. Query processing

    Solution: C

    You can find the name of the layers in different ways, like Query Processing for the Compute Layer.

    19) What property from the Resource Monitors lets you specify whether you want to control the credit usage of your entire account or a specific set of warehouses

    1. Credit Quota

    2. Monitor Level

    3. Schedule

    4. Notification

    Solution: B

    The monitor level is a property that specifies whether the resource monitor is used to monitor the credit usage for your entire account or individual warehouses.

    20) You have two types of named stages, one is an external stage, and the other one is an internal stage. Will external stages always require a cloud storage provider?

    1. True

    2. False

    Solution: A

    External stages reference data files stored in a location outside of Snowflake. These stages serve as pointers or references to the location of files stored externally rather than physically storing the files within Snowflake.

    Amazon S3 buckets, Google Cloud Storage buckets, and Microsoft Azure containers are the currently supported cloud storage services.

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

    1. True

    2. False

    Solution: A

    Clustering is a feature that allows you to organize data in a table based on the values of one or more columns. This can improve query performance by minimizing the amount of data that needs to be scanned when querying the table.

    The more frequently a table is queried, the more benefit clustering provides. However, the more frequently a table changes, the more expensive it will be to keep it clustered.

    22) Which cloud provider is not supported by Snowflake?

    1. AWS

    2. Azure

    3. Google Cloud Platform

    4. IBM

    Solution: D

    A Snowflake account can only be hosted on Amazon Web Services, Google Cloud Platforms, and Microsoft Azure for now.

    23) Is shared data always available to Consumer Accounts?

    1. True

    2. False

    Solution: A

    New objects added to a share become immediately available to all consumers, providing real-time access to shared data, which is always up-to-date. Also, consumers don’t pay for storage, as the producer account already pays for it.

    24) Which function returns the name of the warehouse of the current session

    1. ACTIVE_WAREHOUSE()

    2. RUNNING_WAREHOUSE()

    3. CURRENT_WAREHOUSE()

    4. WAREHOUSE()

    Solution: C

    You have other commands to show the current database and schema, as you can see by executing the following command:

    SELECT CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();

    25) How is query processing done in Snowflake?

    1. AWS EMR with Spark

    2. AWS EC2 with Spark

    3. Virtual Warehouses

    Solution: C

    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.

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

    1. True

    2. False

    Solution: A

    [AI Generated explanation]

    The correct answer is A) True.

    In Snowflake, multiple virtual warehouses can access the same data simultaneously without any contention issue. This is due to Snowflake's ability to decouple compute and storage.

    Snowflake's architecture allows multiple virtual warehouses to operate independently and scale horizontally. Each virtual warehouse has its own set of resources, including CPU, memory, and storage, which ensures that there is no contention for resources.

    The data in Snowflake is stored in a highly scalable and efficient manner, and it is accessible to all virtual warehouses simultaneously. This means that different virtual warehouses can perform operations, such as querying or loading data, on the same data set without interfering with each other.

    27) What are the names of the three main Snowflake layers?

    1. Extraction, Ingestion, Load

    2. Centralized Storage, Compute & Cloud Services

    3. Database, Virtual Warehouse, Data Experience

    Solution: B

    You can see them in the following image (via docs.snowflake.com):

    28) Which Snowflake cache runs for 24 hours?

    1. Metadata cache

    2. Results cache

    3. Warehouse cache

    Solution: B

    The Query Result cache, also known as the Results Cache, holds the results of every query executed in the past 24 hours.

    29) Which is the minimum Snowflake edition that allows until 90 days of Time Travel

    1. Standard

    2. Enterprise

    3. Business Critical

    Solution: B

    By default, Time travel is enabled with a 1-day retention period. However, we can increase it to 90 days if we have (at least) the Snowflake Enterprise Edition. It requires additional storage, which will be reflected in your monthly storage charges.

    30) What is the name of Snowflake’s Command Line Interface tool?

    1. SnowCLI

    2. SnowTerminal

    3. SnowSQL

    4. SnowCMD

    Solution: C

    SnowSQL is the command line client for connecting to Snowflake to execute SQL queries and perform all DDL and DML operations, including loading data into and unloading data out of database tables.

    Get ready to prepare like you've never prepared before

    At FullCertified, we understand that studying for exams can be overwhelming, which is why we have put in hours of hard work, expert preparation, and continuous feedback to create the ultimate study guide for you.

    At FullCertified, we take immense pride in our material. Our study guides are the real deal, encompassing comprehensive information that covers all the essential topics you need to know. Let's take a look to what it's included with your subscription!

    1. The SnowPro Core certification exam is a challenging journey, but we're here to guide you every step of the way. Our practice tests have been carefully created to reflect the new Snowflake SnowPro Core COF-C02 exam, with 300 practice questions designed to prepare you for success.

    2. Experience the convenience of downloading our three practice exams in PDF format. Study them offline, print them, or go at your own pace without an internet connection.

    3. We also provide a downloadable Cheat Sheet that covers key concepts, tips, and strategies to streamline your revision and reinforce your understanding.

    4. Engage with our Exam Simulator to immerse yourself in a simulated exam environment, providing invaluable practice and confidence as you approach the real test.

    5. Additionally, leverage our AI explanations to deepen your understanding of complex concepts and refine your problem-solving skills.

    But that's not all! At FullCertified, we're dedicated to empowering you with the tools and support you need to excel. Stay tuned because a huge content update for the Snowflake SnowPro Core Certification is coming soon! We are sure you are going to love it!