Snowflake Certification Practice Test 14: Master SQL & Architecture

Welcome to your Snowflake Test-14

Name

Phone

Q1. Which Snowflake Editions have Elastic Data Warehousing. Check all the Snowflake editions that have Elastic Data Warehousing enabled.

Select 3 option(s):

Standard

Premium

Virtual Private Snowflake

Enterprise

Business Critical Edition

Q2. A Snowflake customer is responsible for all the maintenance and services.

Select 1 option(s):

TRUE

FALSE

Q3. Fail-safe is a reliable way to create Dev/Test/QA and other environments.

Select 1 option(s):

TRUE

FALSE

Q4. When unloading data into multiple files, use the MAX_FILE_SIZE copy option to specify the maximum size of each file created. (TRUE / FALSE)

Select 1 option(s):

TRUE

FALSE

Q5. Some compute occurs in the cloud services layer. When customer is charged for compute which occurred in the cloud services layer?

Select 1 option(s):

Customers are charged for cloud computing that exceeds 10% of total compute costs for the account

Customers are charged for cloud computing that exceeds 50% of total compute costs for the account

There is no charge to customer for cloud services layer

Customers are charged for cloud computing that exceeds 10% of total storage costs for the account

Q6. Which of the following have drivers/connectors (or information about where to find them) available via Help->Downloads in the Snowflake WebUI? (Select 4)

Select 4 option(s):

Node.js

Spark

Kafka

JDBC

Q7. When creating a table, schema, or database, the account default can be overridden using the DATA_RETENTION_TIME_IN_DAYS parameter in the command.

Select 1 option(s):

TRUE

FALSE

Q8. John wants to create a Warehouse which can auto resume whenever there is a new load / query to execute. How should he configure the warehouse?

Select 1 option(s):

AUTO_SUSPEND = TRUE

AUTO_RESUME = TRUE

AUTO_RESUME = 1

It is always manual and cant be automated.

Q9. In the History Page, a query shows Bytes Scanned having Assigned Partitions: 110, Scanned Partitions 58, and Original Partitions 110. Why did the optimizer show fewer partitions scanned than assigned?

Select 1 option(s):

The static optimization determined the number of possible micro partitions would be 110 but the dynamic optimization was able to prune some of the partitions from a joined table

During the execution of the query, new data was added to the table and the optimizer had to add those micro partitions into the scan.

The query was using an Xlarge warehouse and could scan the partitions in parallel

The optimizer estimated only 58 partitions would need to be scan but during the execution of the query, the optimizer realized it would have to read all 110 micro partitions

One of the tables in the query was an external table and didnt have micro partitions The metadata for the table was out of date and there were really only 58 partitions total

Q10. When setting the password policy, what options are available?

Select 1 option(s):

All of the above

Prevent the reuse of a previous password

Force the use of a special character in a password

Snowflake password policy cannot be changed

Set the required length for a password

Q11. Secured view can be used to hide the definition but its performance can get degraded?

Select 1 option(s):

FALSE

TRUE

Q12. Which of the following are valid context functions?

Select 3 option(s):

CURRENT_SESSION( )

CURRENT_CLOUD_INFRASTRUCTURE

CURRENT_WORKSHEET( )

CURRENT_REGION( )

CURRENT_CLIENT( )

Q13. No need to grant usage on new objects created in a database in a share in order for them to be available to consumers.

Select 1 option(s):

FALSE

TRUE

Q14. Select all the features Snowflake supports:

Select 3 option(s):

SQL-based transformation

A graphical UI to build data pipelines

Dedicated resource for compute

Semi-Structured data

Unstructured data

Q15. What is the best practice after creating a custom role in a Snowflake account?

Select 1 option(s):

Grant the role to the USERADMIN role so users can be added to the role.

Grant ownership of the role to itself so a member of the role can control access to the role

Grant PUBLIC to the role so all database objects owned by PUBLIC will be available to the new role

Grant ownership to the SYSADMIN role or a child role under SYSADMIN

Q16. Can you have a database overlap across two Snowflake account?

Select 1 option(s):

Yes

Q17. Which approach would result in improved performance through linear scaling of data ingestion workload?

Select 1 option(s):

All of these

Consider the practice of splitting input file batch within the recommended range of 10MB to 100MB

Resize virtual warehouse

Consider the practice of organizing data by granular path

Q18. A user‘s default role is

Select 1 option(s):

The name used to log in to the WebUI

The user‘s default role is always PUBLIC

The role a user is set to each time the user logs in to the system

The user‘s default role changes each time the user logs in to the system.

Q19. The acronym ETL stands for what three words?

Select 1 option(s):

Extract, Transition, Loading

Extract, Transition, Load

Extract, Transfer, Load

Extract, Transform, Load

Q20. Monica is a new Snowflake user with ACCOUNTADMIN role. She wants to create a warehouse which can scale out if needed. She tried to create the warehouse using UI but she is not able to find the options to set Minimum and Maximum Clusters. What could be the issue?

Select 1 option(s):

She should use SQL command to create the Warehouse with Minimum and Maximum clusters

She should ALTER any existing warehouse to set the Maximum and Minimum clusters

She doesn‘t have enough privilege to create a warehouse

She is using STANDARD Snowflake edition. Multi-Cluster is not available in STANDARD edition.

Q21. What all commands will you use to load data from your Local File System to Snowflake table?

Select 1 option(s):

Only PUT command

COPY INTO followed by PUT command

PUT COMMAND followed by COPY INTO

Only COPY INTO command

Q22. What is the default Time-Travel retention period of Enterprise Edition?

Select 1 option(s):

1 day

7 days

90 days

0 days

Q23. All data transparently & synchronously replicated across minimum 2 availability zones. (TRUE / FALSE)

Select 1 option(s):

TRUE

FALSE

Q24. Which actions are not supported with shared data?

Select 4 option(s):

Can be re-shared by data consumer

Time Travel for a shared database or any schemas/tables in the database

Creating a clone of a shared database or any schemas/tables in the database

Editing the comments for a shared database

Unattempted

Q25. Micro-partitions are IMMUTABLE. What are features make micro-partitions IMMUTABLE?

Select 2 option(s):

When new data gets ingested Snowflake tries to insert data in existing micro-partition to save the storage.

Snowflake creates new micro-partitions every time there is data change.

Snowflake purges the micro-partitions as soon as you delete any record from table.

Micro-Partitions are not editable.

Q26. Which all commands COPY INTO supports while unloading from Snowflake tables to Internal or external stages?

Select 5 option(s):

Column reordering

Join

Casts

Column omission

String Truncation

Unattempted

Q27. What all file formats are supported by Snowflake for unloading data from Snowflake?

Select 3 option(s):

PARQUET

XML

JSON

CSV, TSV

AVRO

Q28. Which SQL statement will not consume Warehouse credit?

Select 4 option(s):

SHOW WAREHOUSE LIKE ‘MO‘;

DROP WAREHOUSE MY_WAREHOUSE;

SHOW WAREHOUSES;

ALTER WAREHOUSE MY_WAREHOUSE SET WAREHOUSE_SIZE = “SMALL“;

Unattempted

Q29. Which SQL statements can work on a cloned Table?

Select 1 option(s):

SHOW command

All of these

DROP table command

SELECT command

Q30. What are the key considerations for using warehouse effectively and efficiently?

Select 2 option(s):

Start with smallest warehouse always and Scale up if the performance is poor

Snowflake utilizes per-hour billing so, customer doesn‘t have to worry about the warehouse size.

Experiment with different types of queries and different warehouse sizes to determine the combinations that best meet your specific query needs and workload.

Dont focus on warehouse size. Snowflake utilizes per-second billing, so you can run larger warehouses (Large, X-Large, 2X-Large, etc.) and simply suspend them when not in use

Q31. John ran a query which took around 30 mins. He referred to Query profiler, and found the ‘Bytes spilled to local storage‘ has big number. What could be the issue?

Select 1 option(s):

John is using very large warehouse

John should contact Snowflake Personnel

John is using comparatively smaller warehouse

Warehouse size has no impact on Bytes spilling

Q32. Which of these must return a value?

Select 1 option(s):

Both

Stored Procedure

User-Defined Function

Q33. What is the best option to automatically delete the files from stage after it got loaded using COPY INTO command?

Select 1 option(s):

Setting DELETE = TRUE in COPY INTO command

Cloud provider deletes the file automatically

Delete manually using Cloud Provide UI delete option as soon as file is loaded

Setting PURGE = TRUE in COPY INTO command

Q34. How to choose the right size of warehouse to achieve the best results based on the Query processing?

Select 1 option(s):

Execute varieties of queries on same warehouse to achieve the best result

Execute relatively homogenous queries on the same warehouse

Q35. When using a LATERAL FLATTEN in a semi-structured data query, what does the LATERAL do?

Select 1 option(s):

Breaks an array into multiple elements

Causes the FLATTEN command to read an array in reverse order

Works as a self join to join the current record key / value pairs to an array in the same record

Modifies the data in an array to a variant data type so it will work with the FLATTEN command

Causes the FLATTEN command to be recursive and drill down into all levels of the record

Q36. How can you validate the COPY INTO command before actually loading data into the table?

Select 1 option(s):

None of these

Use parameter VALIDATION_COPY = TRUE | RETURN__ROWS | RETURN_ERRORS |RETURN_ALL_ERRORS with COPY INTO command

Simply count the loaded records and match with stage

USE parameter VALIDATION_MODE = RETURN__ROWS | RETURN_ERRORS |RETURN_ALL_ERRORS

Q37. Which security feature is supported in Snowflake?

Select 1 option(s):

Tri-Secret Secure Encryption

All of these

Role-Based Access Control

Multi-Factor Authentication

Q38. Two Virtual Warehouses can access the same data at the same time without causing contention issues.

Select 1 option(s):

TRUE

FALSE

Q39. Scaling up is intended for handling concurrency issues dure to more users or more queries

Select 1 option(s):

FASE

TRUE

Q40. We loaded an XML file that included a header entity called . In order to bypass this entity and treat each EMPLOYEE object as a separate record (loading each into a separate row), what FILE_FORMAT property did we change?

Select 1 option(s):

STRIP OUTER ELEMENT

IGNORE UTF-8 ERROR

DISABLE AUTO CONVERT

PRESERVE SPACE

DISABLE SNOWFLAKE DATA

Q41. Please choose the correct Table Type from the given access.

Select 4 option(s):

TRANSIENT

EXTERNAL

TEMPORARY

INTERNAL

PERMANENT

Q42. A 3X-LARGE cluster contains 64 Servers. if a 3X-Large multi-cluster warehouse runs 1 cluster for one full hour and then runs 3 clusters for the next full hour. How many credits will be billed?

Select 1 option(s):

512

256

128

212

Q43. What all commands will you use to unload data from your Snowflake table to External Stages?

Select 1 option(s):

COPY INTO followed by PUT command

COPY INTO command followed by GET command

Only GET command

Only COPY INTO command

Q44. You set up a Snowflake account, choosing AWS as your cloud platform provider. What stages can you use to load data files? (Check all that apply)

Select 6 option(s):

NAMED EXTERNAL - using GCS/GCP Buckets

NAMED INTERNAL

USER

NAMED EXTERNAL - using S3 Buckets

TABLE

NAMED EXTERNAL - using Azure BLOB storage

Unattempted

Q45. Snowflake offers multiple editions. Which one is not a offering from Snowflake.

Select 1 option(s):

Premium

Business Critical

Standard

Enterprise

Q46. Snowflake offers the early access of staged release to designated Standard Edition Customer. True or false?

Select 1 option(s):

FALSE

TRUE

Q47. If you want to view query history older than 14 days, where can you go to view it? Choose one path and one “term“ commonly used.

Select 2 option(s):

SNOWFLAKE (Database) -> ACCOUNT_USAGE (Schema) -> QUERY_HISTORY (Secure View)

History-> Profile -> Account_Usage

Account->Usage->History

The Usage Sharing Monitor

The Account Usage Share

Q48. What types of Infrastructure Security are available to ensure customer data is secured?

Select 3 option(s):

Cloud provider‘s redundancy

Regional data centers

Cloud Provider‘s physical security

Customer Admin takes the backup of data into local data disk

Q49. Which of the following terms or phrases can also be used to describe Snowflake? (Select 4)

Select 4 option(s):

Multi-cluster

Hybrid Columnar

Native SQL

Hadoop-Compliant

Build from the ground up for the cloud

Q50. If a server in warehouse cluster runs for 64 seconds, shuts down, and then restarts and runs for less than 60 seconds, for how many seconds it will be billed?

Select 1 option(s):

120 Seconds

124 Seconds

4 Seconds

64 Seconds

Q51. Generally, what is the size of a micro-partition in compresses format?

Select 1 option(s):

50 MB

500 MB

16 MB

16 GB

Q52. Snowflake data storage costs are calculated based on: (Select 2)

Select 2 option(s):

Amount Stored on First Day of Month

Uncompressed Size

Amount Stored on Last Day of Month

Amount Stored - Daily Average

Compressed Size

Q53. Snowflake Architecture consists of many separate layers. Please select the correct layers. (Select 3).

Select 3 option(s):

Cloud Services Layer

Database Storage Layer

Security Layer

Query Processing Layer

Q54. Who owns and manages the cloud storage layer hosted on Cloud Providers?

Select 1 option(s):

Customer

Snowflake

Cloud Provider

Q55. When configuring a Warehouse using a Snowflake edition that has Elastic Data Warehousing enabled, what facets or components will you need to configure that are not needed in accounts where Elastic Data Warehousing is not enabled. (Choose two)

Select 2 option(s):

Minimum and Maximum Servers

Minimum and Maximum Clusters

Auto-Suspend

Scaling Policy

Q56. Which vendors do support Snowflake natively for federated authentication and SSO?

Select 2 option(s):

Microsoft Azure Active Directory

Okta

Onelogin

Google G Suite

Microsoft ADFS

Q57. Which of the following Snowflake Editions encrypt all data transmitted over the network within a Virtual Private Cloud (VPC)?

Select 1 option(s):

Business Critical

Enterprise

Standard

Q58. What all objects can be shared?

Select 3 option(s):

Table

Standard View

Secure UDF

Secure View

Q59. Which query will require compute (Virtual Warehouse)? Consider table created as – CREATE or replace TABLE T1 ( ID number(2), Name varchar(20), ID number (11,2) );

Select 1 option(s):

Q60. Snowflake Resource Monitor allows administrators to set thresholds and triggers to track credit usage. What methods are most effective for resource monitoring? (Check all that apply).

Select 3 option(s):

Setting quota thresholds for trigger actions and notifications

Tracking and controlling credit consumption on a monthly basis

Control global monthly credit usage for an account

Tracking consumption on an hourly basis

Q61. What is the best recommended size of file for Bulk loading?

Select 1 option(s):

50 MB - 500 MB Compressed

10 MB - 100 MB uncompressed

16 MB Compressed

10 MB - 100 MB Compressed

Q62. Micro-partitioning is automatically performed on all the Snowflake tables.

Select 1 option(s):

TRUE

FALSE

Q63. John wants to create a warehouse which should not start immediately. Which property will him to create a warehouse in suspended mode.

Select 1 option(s):

AUTO_SUSPEND = INITIAL

INITIATE = FALSE

AUTO_START = FALSE

INITIALLY_SUSPENDED = TRUE

Q64. What action causes a Warehouse‘s cache to be purged?

Select 1 option(s):

The passing of 24 hours.

Resuming the warehouse.

Suspending the warehouse.

A change to the Metadata Cache size.

Q65. A multi-cluster virtual warehouse is Maximized when

Select 2 option(s):

MIN_CLUSTER_COUNT = 3 MAX_CLUSTER_COUNT = 3

MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1

Minimum and Maximum number of clusters are specified differently.

Minimum number of clusters and Maximum number of clusters are same and must be specified with a value of more than 1.