Snowflake Certification Practice Test: 60 Exam Questions

Welcome to your Snowflake Test-20

Name

Phone

Q1. There are 300 concurrent users on a production Snowflake account using a single cluster virtual warehouse. The queries are small, but the response time is very slow.

What is causing this to occur?

Select 1 option(s):

The queries are not taking advantage of the data cache.

The warehouse is queuing the queries, increasing the overall query execution time.

The warehouse parameter STATEMENT_QUEUED_TIMEOUT_IN_SECONDS is set too low.

The application is not using the latest native ODBC driver which is causing latency.

Q2. What criteria does Snowflake use to determine the current role when initiating a session? (Choose two.)

Select 2 option(s):

If a role was specified as part of the connection and that role has not been granted to the Snowflake user, the role is automatically granted and it becomes the current role.

If a role was specified as part of the connection and that role has not been granted to the Snowflake user, it will be ignored and the default role will become the current role.

If no role was specified as part of the connection and a default role has not been set for the Snowflake user, the session will not be initiated and the log in will fail.

If no role was specified as part of the connection and a default role has been defined for the Snowflake user, that role becomes the current role.

If a role was specified as part of the connection and that role has been granted to the Snowflake user, the specified role becomes the current role.

Q3. Which services does the Snowflake Cloud Services layer manage? (Choose two.)

Select 2 option(s):

Query execution

Compute resources

Data storage

Authentication

Metadata

Q4. Column level security in Snowflake allows the application of a masking policy to a column within a table or view. Which two features are related to column-level security? (Choose two.)

Select 2 option(s):

Data Encryption

Dynamic Data Masking.

External Tokenization

Lock Databases.

Conditional Tokenization.

Q5. Which COPY INTO command outputs the data into one file?

Select 1 option(s):

FILE_NUMBER=1

MAX_FILE_NUMBER=1

MULTIPLE=FALSE

SINGLE=TRUE

Q6. What is the default Time Travel retention period?

Select 1 option(s):

1 day

7 days

45 days

90 days

Q7. Which of the following statements apply to Snowflake in terms of security? (Choose two.)

Select 2 option(s):

Snowflake leverages a Role-Based Access Control (RBAC) model.

All data in Snowflake is encrypted.

Snowflake requires a user to configure an IAM user to connect to the database.

All data in Snowflake is compressed.

Snowflake can run within a user‘s own Virtual Private Cloud (VPC).

Q8. Increasing the size of a virtual warehouse from an X-Small to an X-Large is an example of which of the following?

Select 1 option(s):

Right sizing

Scaling out

Concurrent sizing

Scaling up

Q9. Which Snowflake edition supports private communication between Snowflake and your other VPCs through AWS PrivateLink?

Select 1 option(s):

Standard.

Business Critical.

Enterprise.

All Snowflake editions supports private communication between Snowflake and your other VPCs through AWS PrivateLink.

Q10. Which of the below columns will you consider while choosing a cluster key? (Choose two.)

Select 2 option(s):

Columns with extremely high cardinality.

Columns are frequently used in join predicates.

Columns that are typically used in the selective filters.

Columns with extremely low cardinality.

Q11. The following JSON is stored in a VARIANT column called src of the CAR_SALES table:

A user needs to extract the dealership information from the JSON.

How can this be accomplished?

Select 1 option(s):

Q12. Which Snowflake role can set up a Snowflake Share by default?

Select 1 option(s):

ACCOUNTADMIN

SYSADMIN

PUBLIC

SECURITYADMIN

Q13. What are ways to create and manage data shares in Snowflake? (Choose two.)

Select 2 option(s):

Through the Snowflake web interface (UI)

Through the ENABLE_SHARE=TRUE parameter

Using the CREATE SHARE AS SELECT * FROM TABLE command

Through the DATA_SHARE=TRUE parameter

Through SQL commands

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

Select 1 option(s):

Snowflake users are automatically enrolled in MFA.

Any Snowflake user can self-enroll in MFA through the web interface.

MFA can be enforced or applied for a given role.

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

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

How can the key be removed?

Select 1 option(s):

ALTER TABLE REMOVE CLUSTERING KEY

ALTER TABLE PURGE CLUSTERING KEY

ALTER TABLE DROP CLUSTERING KEY

ALTER TABLE DELETE CLUSTERING KEY

Q16. What happens when a virtual warehouse is resized?

Select 1 option(s):

When reducing the size of a warehouse the compute resources are removed only when they are no longer being used to execute any current statements.

Users who are trying to use the warehouse will receive an error message until the resizing is complete.

The warehouse will be suspended while the new compute resource is provisioned and will resume automatically once provisioning is complete.

When increasing the size of an active warehouse the compute resource for all running and queued queries on the warehouse are affected.

Q17. What is the purpose of an External Function?

Select 1 option(s):

To call code that executes outside of Snowflake

To share data in Snowflake with external parties

To run a function in another Snowflake database

To ingest data from on-premises data sources

Q18. Which SQL commands should be used to write a recursive query if the number of levels is unknown? (Choose two.)

Select 2 option(s):

QUALIFY

LISTAGG

MATCH RECOGNIZE

WITH

Oracle Cloud

CONNECT BY

Q19. In which editions can we configure MFA Authentication?

Select 1 option(s):

Standard.

Enterprise.

Business Critical.

All of them.

Q20. How does Snowflake Fail-safe protect data in a permanent table?

Select 1 option(s):

Fail-safe makes data available for 7 days, recoverable by user operations.

Fail-safe makes data available up to 1 day, recoverable only by Snowflake Support.

Fail-safe makes data available for 7 days, recoverable only by Snowflake Support.

Fail-safe makes data available up to 1 day, recoverable by user operations.

Q21. What are supported file formats for unloading data from Snowflake? (Choose three.)

Select 3 option(s):

CSV

Parquet

XML

JSON

ORC

AVRO

Q22. How a Snowpipe charges calculated?

Select 1 option(s):

Number of Pipes in account

Per-second/per Warehouse size

Total storage bucket size

Per-second/per-core granularity

Q23. A running virtual warehouse is suspended.

What is the MINIMUM amount of time that the warehouse will incur charges for when it is restarted?

Select 1 option(s):

60 seconds

1 second

5 minutes

60 minutes

Q24. Which are the additional columns that the streams create? (Choose three.)

Select 3 option(s):

METADATA$COLUMN_ID

METADATA$ROW_ID

METADATA$ACTION

METADATA$ISUPDATE

METADATA$IS_DELETED

METADATA$ISREAD

Q25. What is used to diagnose and troubleshoot network connections to Snowflake?

Select 1 option(s):

Snowpark

Snowsight

SnowCD

SnowSQL

Q26. Which of the following are best practices for loading data into Snowflake? (Choose three.)

Select 3 option(s):

Split large files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse.

Load data from files in a cloud storage service in a different region or cloud platform from the service or region containing the Snowflake account, to save on cost.

Partition the staged data into large folders with random paths, allowing Snowflake to determine the best way to load each file.

Enclose fields that contain delimiter characters in single or double quotes.

When planning which warehouse(s) to use for data loading, start with the largest warehouse possible.

Aim to produce data files that are between 100 MB and 250 MB in size, compressed.

Q27. Which functions can be used to share unstructured data through a secure view? (Choose two.)

Select 2 option(s):

GET_ABSOLUTE_PATH

GET_PRESIGNED_URL

BUILD_STAGE_FILE_URL

GET_RELATIVE_PATH

BUILD_SCOPED_FILE_URL

Q28. Which semi-structured file formats are supported when unloading data from a table? (Choose two.)

Select 2 option(s):

JSON

Avro

ORC

XML

Parquet

Q29. After how many days does the COPY INTO load metadata expire?

Select 1 option(s):

64 days.

180 days.

1 day.

14 days.

Q30. When reviewing a query profile, what is a symptom that a query is too large to fit into the memory?

Select 1 option(s):

The query is spilling to remote storage

An AggregateOperator node is present

A single join node uses more than 50% of the query time

Partitions scanned is equal to partitions total

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

Select 1 option(s):

Virtual Private Snowflake.

Business Critical.

Enterprise.

Standard.

Q32. Which property needs to be added to the ALTER WAREHOUSE command to verify the additional compute resources for a virtual warehouse have been fully provisioned?

Select 1 option(s):

SCALING_POLICY

RESOURCE_MONITOR

WAIT_FOR_COMPLETION

QUERY_ACCELERATION_MAX_SCALE_FACTOR

Q33. Which of the following statements would be used to export/unload data from Snowflake?

Select 1 option(s):

EXPORT_TO_STAGE(stage => @stage, select => ‘select * from t1‘);

EXPORT TO @stage

INSERT INTO @stage

COPY INTO @stage

Q34. What transformations are supported in a CREATE PIPE … AS COPY ′¦ FROM (′¦) statement? (Choose two.)

Select 2 option(s):

Row level access can be defined.

Columns can be omitted.

Incoming data can be joined with other tables.

Columns can be reordered.

Data can be filtered by an optional WHERE clause.

Q35. Which are the metadata columns for staged files? (Choose two.)

Select 2 option(s):

METADATA$FILE_ROW_NUMBER

METADATA$FILE_ROW_ID

METADATA$FILE_SIZE

METADATA$FILEFORMAT

METADATA$FILENAME

Q36. What does the average_overlaps in the output of SYSTEM$CLUSTERING_INFORMATION refer to?

Select 1 option(s):

The average number of micro-partitions stored in Time Travel.

The average number of micro-partitions in the table associated with cloned objects.

The average number of micro-partitions which contain overlapping value ranges.

The average number of partitions physically stored in the same location.

Q37. What information is stored in the ACCESS_HISTORY view?

Select 1 option(s):

Details around the privileges that have been granted for all objects in an account

History of the files that have been loaded into Snowflake

Query details such as the objects included and the user who executed the query

Names and owners of the roles that are currently enabled in the session

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

Select 1 option(s):

Clustering ratio.

Clustering depth.

Clustering status.

Values in a table.

Q39. In an auto-scaling multi-cluster virtual warehouse with the setting SCALING_POLICY = ECONOMY enabled, when is another cluster started?

Select 1 option(s):

When the system has enough load for 8 minutes

When the system has enough load for 6 minutes

When the system has enough load for 10 minutes

When the system has enough load for 2 minutes

Q40. What types of constraints from the ANSI SQL Standard does Snowflake enforce?

Select 1 option(s):

UNIQUE.

FOREIGN KEY.

NOT NULL.

PRIMARY KEY.

Q41. What is the minimum Snowflake edition required for row level security?

Select 1 option(s):

Business Critical

Enterprise

Standard

Virtual Private Snowflake

Q42. Which service does Snowflake use to provide the Zero-Copy cloning functionality?

Select 1 option(s):

Backup management services.

Cache.

Metadata from the service layer.

SSD Cache of the Virtual Warehouses.

Q43. While loading data through the COPY command, you can transform the data.

Which of the below transformations is not allowed?

Select 1 option(s):

Omit columns.

Cast.

Truncate columns.

Filters.

Reorder columns.

Q44. Which tasks are performed in the Snowflake Cloud Services layer? (Choose two.)

Select 2 option(s):

Computing the data

Parsing and optimizing queries

Maintaining Availability Zones

Management of metadata

Infrastructure security

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

Select 1 option(s):

External.

Temporary.

Transient.

Permanent.

Q46. Which of the following is not a valid context functions in Snowflake?

Select 1 option(s):

Q47. What is a core benefit of clustering?

Select 1 option(s):

To improve performance by creating a separate file for point lookups

To provide data redundancy by duplicating micro-partitions

To increase scan efficiency in queries by improving pruning

To guarantee uniquely identifiable records in the database

Q48. What file format provides the fastest load performance?

Select 1 option(s):

JSON.

Avro.

Parquet.

CSV.

Q49. When cloning a database containing stored procedures and regular views, that have fully qualified table references, which of the following will occur?

Select 1 option(s):

An error will occur, as stored objects cannot be cloned.

An error will occur, as views with qualified references cannot be cloned.

The stored procedures and views will refer to tables in the source database.

The cloned views and the stored procedures will reference the cloned tables in the cloned database.

Q50. After how many days does the load history of Snowpipe expire?

Select 1 option(s):

180 days.

90 days.

1 day.

14 days.

Q51. Which statements about Snowflake tasks are true? (Choose two.)

Select 2 option(s):

A task can execute a call to a Stored Procedure.

A task can execute multiple SQL Statements.

A task can not execute a call to a Stored Procedure.

A task can execute a single SQL Statement.

A task can execute a function.

Q52. A team runs the same query daily, generally with a frequency of fewer than 24 hours, and it takes around 10 minutes to execute. They realized that the underlying data changes because of an ETL process that runs every morning.

How can they use the results cache to save the 10 minutes that the query is being executed?

Select 1 option(s):

After the ETL run, use Time-Travel feature.

After the ETL run, copy the tables to another database for the team to query.

After the ETL run, increase the warehouse size. Decrease it after the query runs.

After the ETL run, execute the identical queries so that the result remains in the cache.

Q53. If a transaction disconnects and goes into a detached state, which cannot be committed or rolled back, how long will Snowflake take to abort the transaction?

Select 1 option(s):

60 minutes.

12 hours.

4 hours.

15 minutes.

Q54. A company needs to allow some users to see Personally Identifiable Information (PII) while limiting other users from seeing the full value of the PII.

Which Snowflake feature will support this?

Select 1 option(s):

Data encryption

Row access policies

Data masking policies

Role based access control

Q55. The is the minimum Fail-safe retention time period for transient tables?

Select 1 option(s):

1 day

0 days

12 hours

7 days

Q56. What is the minimum Snowflake edition that you need for the Data Sharing capability?

Select 1 option(s):

Virtual Private Snowflake

Standard

Enterprise

Business Critical

Q57. Which character identifies a user stage?

Select 1 option(s):

“ ~ ”

“ @~ ”

“ @% ”

“ @ ”

Q58. Which possibilities give us Snowflake to resize a Warehouse? (Choose two.)

Select 2 option(s):

Using the SQL command ALTER WAREHOUSE MODIFY warehouse_size=

Using the SQL command ALTER WAREHOUSE SET warehouse_size=

Creating a new warehouse with the new size.

Using the Snowflake UI in the Warehouse configuration.

Using the SQL command CHANGE WAREHOUSE SET warehouse_size=

Q59. The Snowflake Search Optimization Services supports improved performance of which kind of query?

Select 1 option(s):

Selective point lookup queries

Queries against a subset of columns in a table

Queries against tables larger than 1 TB

Queries against large tables where frequent DML occurs

Q60. Which encryption algorithm is used by Snowflake tables when we load data into them?

Select 1 option(s):

AES 128.

SHA 256.

AES 256.

SHA 128.