Snowflake Certification Practice Test: 60 Essential Questions

Welcome to your Snowflake Test-17

Name

Phone

Q1. The Query History in the Snowflake Web Interface (UI) is kept for approximately:

Select 1 option(s):

60 minutes

24 hours

14 days

1 year

30 days

Q2. A Virtual Warehouse‘s auto-suspend and auto-resume settings apply to:

Select 1 option(s):

The entire Virtual Warehouse

The queries currently being run by the Virtual Warehouse

The primary cluster in the Virtual Warehouse

The database the Virtual Warehouse resides in

Q3. Which statement accurately describes how a virtual warehouse functions?

Select 1 option(s):

Each virtual warehouse is a compute cluster composed of multiple compute nodes allocated by Snowflake from a cloud provider.

Increasing the size of a virtual warehouse will always improve data loading performance.

Each virtual warehouse is an independent compute cluster that shares compute resources with other warehouses.

All virtual warehouses share the same compute resources so performance degradation of one warehouse can significantly affect all the other warehouses.

Q4. What happens to historical data when the retention period for an object ends?

Select 1 option(s):

The data is cloned into a historical object.

The object containing the historical data is dropped.

Time Travel on the historical data is dropped.

The data moves to Fail-safe

Q5. Which Snowflake object can be used to record DML changes made to a table?

Select 1 option(s):

Stream

Snowpipe

Task

Stage

Q6. A deterministic query is run at 8am, takes 5 minutes, and the results are cached. Which of the following statements are true? (Choose two.)

Select 2 option(s):

The 24 hours timer on the precomputed results gets renewed every time the exact query is executed

Snowflake edition is Enterprise or higher

The same exact query will return the precomputed results if the underlying data hasn‘t changed and the results were last accessed within previous 24 hour period

The exact query will ALWAYS return the precomputed result set for the RESULT_CACHE_ACTIVE = time period

The same exact query will return the precomputed results even if the underlying data has changed as long as the results were last accessed within the previous 24 hour period

Q7. Which is true of Snowflake network policies? A Snowflake network policy: (Choose two.)

Select 2 option(s):

Is only available to customers with Business Critical Edition

Only ACCOUNTADMIN role or a role with the global CREATE NETWORK POLICY privilege can create network policies

Is activated using an ALTER DATABASE command

Restricts or enables access to specific IP addresses

Is available to all Snowflake Editions

Q8. Which Snowflake feature will allow small volumes of data to continuously load into Snowflake and will incrementally make the data available for analysis?

Select 1 option(s):

TABLE STREAM

COPY INTO

INSERT INTO

CREATE PIPE

Q9. By default, which Snowflake role is required to create a share?

Select 1 option(s):

ORGADMIN

SHAREADMIN

ACCOUNTADMIN

SECURITYADMIN

Q10. How often does Snowpipe load the data?

Select 1 option(s):

When we manually execute the COPY procedure.

Once every 5 minutes.

Once every 1 minute.

As soon as they are available in a stage.

Q11. If auto-suspend is enabled for a Virtual Warehouse, the Warehouse is automatically suspended when:

Select 1 option(s):

There are no users logged into Snowflake.

The Warehouse is inactive for a specified period of time.

The last query using the Warehouse completes.

All Snowflakes sessions using the Warehouse are terminated.

Q12. Which cloud provider is not supported by Snowflake?

Select 1 option(s):

IBM.

Google Cloud Platform.

Azure.

AWS.

Q13. How will you store JSON data in Snowflake?

Select 1 option(s):

Using a column of the VARCHAR type.

Using a column of the JSON type.

Using a column of the NULL type.

Using a column of the VARIANT type.

Q14. Which item in the Data Warehouse migration process does not apply in Snowflake?

Select 1 option(s):

Migrate Schemas

Migrate Users

Build the Data Pipeline

Migrate Indexes

Q15. Which statistic displayed in a Query Profile is specific to external functions?

Select 1 option(s):

Total invocations

Bytes sent over the network

Partitions scanned

Bytes written

Q16. A role is created and owns 2 tables. This role is then dropped. Who will now own the two tables?

Select 1 option(s):

SYSADMIN

The assumed role that dropped the role

The user that deleted the role

The tables are now orphaned

Q17. Which command will we use to download the files from the stage/location loaded through the COPY INTO command?

Select 1 option(s):

UNLOAD.

PUT.

GET.

INSERT INTO.

Q18. Which of the following statements are true concerning the Snowflake release process? (Choose three.)

Select 3 option(s):

It is possible for you as a user to request 24-hour early access to the upcoming releases so that you can do additional release testing before the release is rolled out.

A customer is assigned a 30 minute window (that can be moved anytime within a week) during which the system will be unavailable and customer is upgraded

Snowflake deploys patch releases every week, but new feature releases happen once a month.

There is usually some minimal downtime associated with Snowflake during the deployments.

Snowflake deploys new feature releases and releases every week.

Snowflake deploys new Behavior change releases every month.

Q19. Which of the following sizes is not a Warehouse Size?

Select 1 option(s):

XXS

Q20. Which statement best describes Snowflake tables?

Select 1 option(s):

Snowflake tables require that clustering keys be defined to perform optimally

Snowflake tables are the physical instantiation of data loaded into Snowflake

Snowflake tables are logical representations of underlying physical data

Snowflake tables are owned by a user

Q21. Which of the following objects is not covered by Time Travel?

Select 1 option(s):

Tables

Schemas

Databases

Stages

Q22. When can a Virtual Warehouse start running queries?

Select 1 option(s):

When its provisioning is complete

Only during administrator defined time slots

After replication

12am-5am

Q23. What are the key characteristics of ACСOUNT_USAGE views? (Choose two.)

Select 2 option(s):

Records for dropped objects are included in each view.

There is no data latency.

The historical data can be retained from 7 days to 6 months.

The data latency can vary from 45 minutes to 3 hours.

The historical data is not retained.

Q24. How does Snowflake store a table‘s underlying data? (Choose two.)

Select 2 option(s):

Columnar file format

Text file format

User-defined partitions

Micro-partitions

Uncompressed

Q25. In which layer of its architecture does Snowflake store its metadata statistics?

Select 1 option(s):

Compute Layer

Database Layer

Storage Layer

Cloud Services Layer

Q26. Which of the following are options when creating a Virtual Warehouse? (Choose two.)

Select 2 option(s):

Auto-drop

Auto-resize

Auto-suspend

Auto-disable

Auto-resume

Auto-enable

Q27. How many tasks can a tree of tasks have?

Select 1 option(s):

1000, including the root task.

1000 without including the root task.

100

Q28. Which statement best describes ‘clustering‘?

Select 1 option(s):

The clustering key must be included on the COPY command when loading data into Snowflake

Clustering represents the way data is grouped together and stored within Snowflake‘s micro-partitions

The database administrator must define the clustering methodology for each Snowflake table

Clustering can be disabled within a Snowflake account

Q29. In which layer of Snowflake architecture is stored all security-related information?

Select 1 option(s):

All of the above.

Compute.

Cloud Services.

Storage.

Q30. Which Snowflake edition allows only one day of Time Travel?

Select 1 option(s):

Business Critical.

Standard.

No version allows only one day of Time Travel.

Enterprise.

Q31. What is the MOST performant file format for loading data in Snowflake?

Select 1 option(s):

CSV (Gzipped)

ORC

Parquet

CSV (Unzipped)

Q32. What is the advantage of using a reader account?

Select 1 option(s):

It provides limited access to the data share and is therefore cheaper for the data provider

It can be connected to a Snowflake account in a different region

It can be used by a client that does not have a Snowflake account

It is read-only and prevents the shared data from being updated by the provider

Q33. Which of the below APIs are NOT Snowpipe REST APIs? (Choose two.)

Select 2 option(s):

insertFiles

insertHistoryScan

loadHistoryScan

insertReport

loadFiles

Q34. What is the storage hierarchy in Snowflake?

Select 1 option(s):

Account → Schemas → Databases → Objects.

Account → Databases → Objects → Schemas.

Account → Databases → Schemas → Objects.

Account → Databases → Warehouses → Objects.

Q35. A Snowflake user wants to optimize performance for a query that queries only a small number of rows in a table. The rows require significant processing. The data in the table does not change frequently.

What should the user do?

Select 1 option(s):

Create a materialized view based on the query.

Enable the query acceleration service for the virtual warehouse.

Add the search optimization service to the table.

Add a clustering key to the table.

Q36. Snowflake provides two mechanisms to reduce data storage costs for short-lived tables. These mechanisms are: (Choose two.)

Select 2 option(s):

Materialized views

Transient Tables

Provisional Tables

Temporary Tables

Permanent Tables

Q37. What columns are returned when performing a FLATTEN command on semi-structured data? (Choose two.)

Select 2 option(s):

LEVEL

NODE

VALUE

KEY

ROOT

Q38. How is table data compressed in Snowflake?

Select 1 option(s):

The text data in a micro-partition is compressed with GZIP but other types are not compressed.

Each column is compressed as it is stored in a micro-partition.

The micro-partitions are stored in compressed cloud storage and the cloud storage handles compression.

Each micro-partition is compressed as it is written into cloud storage using GZIP.

Q39. What should be the first option to restore data into a table?

Select 1 option(s):

Time-Travel.

Zero-Copy Cloning.

Fail-Safe.

Ask Snowflake Support

Q40. 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?

Select 1 option(s):

Credit Quota.

Schedule.

Notification.

Monitor Level.

Q41. How many children tasks can a task have?

Select 1 option(s):

1000

100

Q42. Which command is used to generate a zero-copy “snapshot“ of any table, schema, or database?

Select 1 option(s):

CREATE REPLICATION GROUP

CREATE ... CLONE

ALTER

COPY INTO

Q42. When should you consider disabling auto-suspend for a Virtual Warehouse? (Choose two.)

Select 2 option(s):

When you do not want to have to manually turn on the Warehouse each time a user needs it

When you want to avoid queuing and handle concurrency

When the compute must be available with no delay or lag time

When users will be using compute at different times throughout a 24/7 period

When managing a steady workload

Q44. A developer is granted ownership of a table that has a masking policy. The developer’s role is not able to see the masked data.

Will the developer be able to modify the table to read the masked data?

Select 1 option(s):

Yes, because a table owner has full control and can unset masking policies.

Yes, because masking policies only apply to cloned tables.

No, because masking policies must always reference specific access roles.

No, because ownership of a table does not include the ability to change masking policies.

Q45. Query results are stored in the Result Cache for how long after they are last accessed, assuming no data changes have occurred?

Select 1 option(s):

3 Hours

1 Hour

24 hours

12 hours

Q46. Which loop type iterates until a condition is true?

Select 1 option(s):

REPEAT

WHILE

LOOP

FOR

Q47. Snowflake recommends, as a minimum, that all users with the following role(s) should be enrolled in Multi-Factor Authentication (MFA):

Select 1 option(s):

SECURITYADMIN, ACCOUNTADMIN, SYSADMIN

SECURITYADMIN, ACCOUNTADMIN

ACCOUNTADMIN

SECURITYADMIN, ACCOUNTADMIN, PUBLIC, SYSADMIN

Q48. Which Snowflake edition (and above) allows until 90 days of Time Travel?

Select 1 option(s):

Business Critical.

Enterprise.

Standard.

Virtual Private Snowflake

Q49. 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?

Select 1 option(s):

Once all the compute resources are provisioned for the second warehouse.

After an average time of 5 seconds.

Immediately.

After the sync process.

Q50. During periods of warehouse contention, which parameter controls the maximum length of time a warehouse will hold a query for processing?

Select 1 option(s):

STATEMENT_TIMEOUT_IN_SECONDS

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

MAX_CONCURRENCY_LEVEL

QUERY_TIMEOUT_IN_SECONDS

Q51. A Snowflake user wants to share data using my_share with account xy12345.

Which command should be used?

Select 1 option(s):

grant select on share my_share to account xy12345;

grant usage on share my_share to account xy12345;

alter account xy12345 add share my_share;

alter share my_share add accounts = xy12345;

Q52. Files have been uploaded to a Snowflake internal stage. The files now need to be deleted.

Which SQL command should be used to delete the files?

Select 1 option(s):

DELETE

REMOVE

MODIFY

PURGE

Q53. What are common issues found by using the Query Profile? (Choose two.)

Select 2 option(s):

Identifying logical issues with the queries

Data spilling to a local or remote disk

Locating queries that consume a high amount of credits

Identifying inefficient micro-partition pruning

Identifying queries that will likely run very slowly before executing them

Q54. What are the types of data consumer accounts available in Snowflake? (Choose two.)

Select 2 option(s):

Public Account

Shared Account

Reader Account

Subscriber account

Full Account

Q55. Which Snowflake table type is only visible to the user who creates it, can have the same name as permanent tables in the same schema, and is dropped at the end of the session?

Select 1 option(s):

Transient

Temporary

Local

User

Q56. Which statement describes how Snowflake supports reader accounts?

Select 1 option(s):

A reader account can consume data from the provider account that created it and combine it with its own data.

The SHOW MANAGED ACCOUNTS command will view all the reader accounts that have been created for an account.

The users in a reader account can query data that has been shared with the reader account and can perform DML tasks.

A consumer needs to become a licensed Snowflake customer as data sharing is only supported between Snowflake accounts.

Q57. What information is included in the display in the Query Profile? (Choose two.)

Select 2 option(s):

Graphical representation of the query processing plan

Credit usage details

Details and statistics for the overall query

Clustering keys details

Index hints used in query

Q58. Which function returns the name of the warehouse of the current session?

Select 1 option(s):

RUNNING_WAREHOUSE()

WAREHOUSE()

CURRENT_WAREHOUSE()

ACTIVE_WAREHOUSE()

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

Select 1 option(s):

Indexing.

Pruning.

Computing.

Clustering.

Q60. A medium (M) warehouse has auto-suspend configured after 15 minutes. You have noticed that all of the queries that run on this warehouse finish within a minute. What will you do to optimize compute costs?

Select 1 option(s):

Delete the warehouse after a minute.

Disable the auto-suspend option.

Use another data-warehouse.

Reduce the auto-suspend time to 1 minute.