Snowflake SnowPro Certification Practice Exams: Questions and Answers, Exams of Database Programming

Download Snowflake SnowPro Certification Practice Exams: Questions and Answers and more Exams Database Programming in PDF only on Docsity!

Snowflake SnowPro Certification - Practice Exams

Updated 2024-2025. Questions and Correct Verified

Answers. Already Graded A

Assuming no data changes have occurred, how long are query results stored in the result cache after being last accessed? A) 1 Hours B) 3 Hours C) 12 Hours D) 24 Hours

• ANS-D 24 Hours For a multi-cluster warehouse, the number of credits billed is calculated on ________ (Choose 1) A) The number of queries ran B) The size of the warehouse and number of cluster ran within a time period C) The size of the warehouse and maximum clusters configured for the warehouse D) The number of users who accessed the warehouse - ANSB For unloading data, which of the following data formats are supported? A) Delimited (CSV, TSV, etc) B) Avro C) JSON D) ORC

E) All of the Above - ANSE How are Snowpipe charges calculated? A) Per second and per warehouse size B) Per second, per core granularity, complexity of COPY statements, file sizes, file formats C) Number of pipes in the account D) Total storage bucket size - ANSB Given the number of factors that can differentiate Snowpipe loads, it is very difficult for Snowflake to provide sample costs. File formats and sizes, and the complexity of COPY statements (including SELECT statement used for transformations), all impact the resource consumption and file overhead charged for a Snowpipe load. How many shares can a single data consumer consume? A) 1 B) 10 C) 100 D) Unlimited E) Depends on Snowflake edition – ANS D A data consumer can consume unlimited shares Identify system-defined roles in Snowflake from the roles given: A) ACCOUNTADMIN

B) SYSADMIN

C) AUDITOR

D) ROLEADMIN

E) SECURITYADMIN

F) PUBLIC - ANSA, B, E and F ACCOUNTADMIN, SYSADMIN, SECURITYADMIN and PUBLIC In the case of a Snowflake account created on AWS, which is responsible for the management of Availability Zones? A) Customer B) Snowflake C) Cloud Provider D) It's a shared responsibility - ANSC The Cloud Provider manages Availability Zones Informatica, Matillion, Azure data factory are all examples of _____________ partners of the Snowflake partner ecosystem? A) Data Management B) Business Intelligence C) Data Integration D) Data Science - ANSData Integration

Each of these partners provide extract, transform and load functionalities Once the time-travel period has expired, it is possible to request Snowflake support to retrieve historical data for a period of: A) 1 Day B) 7 Days C) 30 Days D) Depends on Snowflake Edition E) User-Configurable Duration - ANSB The fail-safe period is 7 days. This can't be changed. Only Snowflake personnel can help you recover the data using fail-safe Selecting the correct clustering key can dramatically improve query performance. Which of the following are good rules to follow when choosing appropriate clustering keys? A) Use columns that are most frequently found in WHERE clause B) A date column is usually not a good candidate to be in the clustering key C) A join column can be considered as a clustering key D) Using expressions in the clustering key may help cardinality - ANSA, C and D Using columns that are most frequently found in WHERE clause, a join column and expressions

Snowflake is designed for which type of workloads? (Choose 2) A) OLAP (Analytics) workloads B) OLTP (Transactional) workloads C) Concurrent workloads D) On-premise workloads - ANSA and C OLAP and Concurrent Snowflake recommends that at a MINIMUM, all users of which roles should be enrolled in Multi-Factor Authentication (MFA)? A) SECURITYADMIN, ACCOUNTADMIN, PUBLIC, SYSADMIN B) SECURITYADMIN, ACCOUNTADMIN, SYSADMIN C) SECURITYADMIN, ACCOUNTADMIN D) ACCOUNTADMIN - ANSD At a minimum, ACCOUNTADMIN is recommended to have MFA Snowflake replicates the following layer(s) across availability zones (Choose two) A) Cloud Services Layer B) Warehouse Layer C) Storage Layer

D) Cloud Agnostic Layer E) Data Sharing Layer - ANSA and C The Cloud Services Layer and the Storage Layer are replicated across availability zones SQL statements fall into several categories, include DDL and DML. What does DDL stand for? A) Data Delineation Language B) Data Definition Language C) Definition Delineation Language - ANSB DDL = Data Definition Language SQL statements fall into several categories, include DDL and DML. What does DML stand for? A) Data Marketing Language B) Delineation Manipulation Language C) Data Manipulation Language - ANSC DML = Data Manipulation Language The Fail-Safe retention period is how long?

A) 1 Day B) 7 Days C) 45 Days D) 90 Days - ANSB 7 Days The History tab in the Snowflake web interface supports viewing of user queries submitted in the past _______ days. A) 1 B) 7 C) 14 D) 90 - ANSC 14 Days True or False, A customer needs to have a pre-existing account with AWS or Microsoft Azure before creating a Snowflake account. - ANSFalse True or False, A Snowflake account is charged for data stored in both internal and external storage - ANSTrue True or False, A user can run a query against the result cache without requiring an active warehouse. - ANSFalse

True or False, After choosing a cloud platform upon account creation you can change it at any time. - ANSFalse Each snowflake account is hosted on one cloud platform. To change, a new account must be created. True or False, All virtual warehouses have access to all data - ANSTrue True or False, An active warehouse is required to run a COPY INTO statement - ANSTrue True or False, Data in fail-safe can be deleted by a member of the Snowflake team before it expires. - ANSFalse True or False, Each worksheet in the Snowflake UI can be associated with different roles, databases, schemas and Virtual Warehouses. - ANSTrue True or False, In order to execute, the COPY command requires the user to specify a file format. - ANSFalse True or False, It is necessary to stop a virtual warehouse to resize it. - ANSFalse True or False, It is possible to load data into Snowflake without creating a named file format object. - ANSTrue

True or False, It is possible to unload structured data to semi-structured formats like JSON and parquet. - ANSTrue True or False, Micro-partition metadata enables some operations to be completed without requiring compute. - ANSTrue True or False, More than one clustering key can co-exist in a Snowflake table. - ANSFalse True or False, Only one stream object can be created on a source table - ANSFalse Any number of streams can be created on a source table True or False, Reader accounts are able to extract data from shared data objects for use outside of Snowflake. - ANSTrue True or False, Snowflake allows customers to directly access the micro-partition files that make up each table. - ANSFalse True or False, Snowflake supports federated authentication in all editions. - ANSTrue True or False, Snowpipe via REST API can only reference External Stages as source.

• ANSTrue True or False, The Query History tab allows users to download the result set of queries within 24 hours, unless the users' roles are different. - ANSTrue

True or False, When a new Snowflake object is created, it is automatically owned by the user who created it. - ANSTrue True or False, When a user creates a new role, they are initially assigned ownership of the role and they maintain ownership until it's transferred to another user. - ANSTrue True or False, When a virtual warehouse is stared, resized or resumed, the minimum billing charge is 1 minute. - ANSTrue True or False, When active, a pipe requires a dedicated virtual warehouse to execute. - ANSFalse True or False, When you create a custom role, it is best practice to immediately grant that role to ACCOUNTADMIN - ANSFalse True or False, You can resize a virtual warehouse while queries are running. - ANSTrue We loaded an XML file that included a header entity called genre. In order to bypass this entity and treat each AUTHOR object as a separate record (loading each into a separate row), what FILE_FORMAT property did we change? A) PRESERVE SPACE B) STRIP OUTER ELEMENT C) DISABLE SNOWFLAKE DATA

D) DISABLE AUTO CONVERT

E) IGNORE UTF-8 ERROR - ANSB

STRIP OUTER ELEMENT

What are the minimum and the maximum number of clusters in a multi-cluster warehouse? A) Min: 1 Max: 99 B) Min: 1 Max: 100 C) Min: 1 Max: 10 D) Min: 1 Max: Unlimited - ANSC Min: 1 Max: 10 What does the acronym 'ETL' stand for? A) Extract, Transfer, Load B) Enterprise Transition Loading C) Extract, Transform, Load D) Extract To Location - ANSC What happens when a database provider revokes privileges to a Share on an object in their source database?

A) The object immediately becomes unavailable for all data consumers B) Any additional data arriving after this point will not be visible to data consumers C) The data consumers stop seeing data updates and become responsible for storage charges D) A static copy of the object at the time is created in the data consumers' accounts - ANSA The object is immediately unavailable for all data consumers What is the MAXIMUM compressed row size in Snowflake? A) 8kb B) 16mb C) 50mb D) 40gb - ANSB 16mb What is the minimum Snowflake edition that customers planning on storing protected information in Snowflake should consider for regulatory compliance? A) Standard B) Premier C) Enterprise D) Business Critical - ANSD

Business Critical What is the most granular object that Time Travel retention period can be defined on? A) Account B) Database C) Schema D) Table - ANSD Table When a multi-cluster warehouse is suspended, which of the following Snowflake cache will be purged? A) Metadata cache B) Remote disk cache (Result set cache) C) Local disk cache D) All of the above - ANSC Local Disk cache When a pipe is recreating using the CREATE OR REPLACE PIPE command:

A) The pipe load history is reset to empty B) The REFRESH parameter is set to TRUE C) Previously loaded files will be ignored D) All of the above - ANSB The REFRESH parameter is set to TRUE When can a virtual warehouse start running queries? A) 12am-5am local time B) Only during admin-defined time slots C) When it's provisioning is complete D) After replication - ANSC When coding in SQL, which of the following can be typed in front of a line to make it a comment rather than be treated as code? A) Double slash: // B) Double dash: -- C) Slash dash slash: /-/ D) Question Mark:? - ANSA and B When starting a virtual warehouse, what is the minimum duration charged? A) 1 second

B) 1 minute C) 3 minutes D) 1 hour - ANSB 1 Minute is the minimum duration charged Which cache does the query optimizer refer to first? Second? Third? A) Warehouse cache, Results cache, Metadata cache B) Warehouse cache, Metadata cache, Results cache C) Metadata cache, Results cache, Warehouse cache D) Metadata cache, Warehouse cache, Results cache - ANSC Metadata cache, Results cache and then Warehouse cache Which file size is the optimal recommendation for loading data into Snowflake? A) 10MB to 100MB, uncompressed B) 100MB to 250MB, compressed C) 100MB to 250 MB, uncompressed D) 10GB to 100GB, compressed - ANSB The optimal file size while loading data is between 100 and 250MB compressed

Which is the recommended way to load data in Snowflake? A) Load row by row B) Load data in batches C) Load data by writing it in the Snowflake Web Interface (UI) D) Load data via frequent, angle row DMLs - ANSB Load data in batches Which of the following are options when creating a virtual warehouse? A) Auto-Drop B) Auto-Resize C) Auto-Resume D) Auto-Suspend - ANSA, C and D Auto Drop, Auto Resume and Auto Suspend are all options Which of the following are the key considerations when choosing a specific region for your Snowflake account? A) Cost B) Proximity to the end-users C) Data sovereignty D) Regulatory requirements

E) All of the Above - ANSE Cost, Proximity to end-users, Data sovereignty and Regulatory requirements are all key considerations Which of the following are true of Virtual Warehouses? (Choose all that apply) A) Warehouse sizes can be changed after they're created B) Warehouses can be resized while running C) Warehouses can be configured to suspend after a period of inactivity D) Warehouses can be configured to auto-resume when new queries are submitted E) All of the Above - ANSE All of the Above Which of the following are types of tables that exist within Snowflake? A) Temporary B) Transient C) Provisioned D) Permanent - ANSA, B and C Temporary, Transient and Permanent

Which of the following are valid Snowflake Virtual Warehouse Scaling Policies? A) Custom B) Economy C) Optimized D) Standard - ANSA, B and D Custom, Economy and Standard Which of the following best describes "clustering"? A) Clustering represents the way the data is grouped together and stored within Snowflake's micro-partitions B) The database administrator must define the clustering methodology for each Snowflake table C) The clustering key must be included on the COPY command when loading data into Snowflake D) Clustering can be disabled within a Snowflake account - ANSA Clustering represents the way the data is grouped together and stored within Snowflake's micro-partitions Which of the following commands are not blocking operations? A) UPDATE B) INSERT

C) MERGE

D) COPY - ANSC

MERGE

Which of the following data types are supported in Snowflake for semi-structured data? A) VARIANT B) OBJECT C) TEXT D) CLOB E) ARRAY - ANSA, B and E VARIANT, OBJECT and ARRAY are supported as semi-structured data types Which of the following is true of micro-partitioning? A) It has been known to introduce data-skew B) It requires a partitioning schema to be defined up front C) It's completed using the ordering that occurs when data is inserted/loaded D) It can be disabled within a Snowflake account - ANSC Micro-partitioning is completed using the ordering that occurs when data is loaded/inserted

Which of the following programming languages are supported in Snowflake to write User-Defined Functions (UDFs)? A) SQL B) Java C) JavaScript D) Python - ANSA and C SQL and JavaScript are supported for writing UDFs Which of the following statements about schemas are true? A) A schema may contain one or more databases B) A database may contain one or more schemas C) A schema is a logical grouping of database objects D) each schema is contained within a warehouse - ANSB and C A database may contain more than one schemas and a schema is a logical grouping of database objects Which of the following statements describes a benefit of Snowflake's separation of compute and storage? A) Growth and storage and compute are tightly coupled together

B) Storage expands without the requirement to add more compute C) Compute can be scaled up or down without the requirement to add more storage D) Multiple compute clusters can access stored data without contention - ANSB, C and D Storage can be expanded and compute can be scaled independently of each other and multiple clusters can access stored data at the same time Which of the following statements is true of Snowflake? A) It was built specifically for the cloud B) It was built as an on-premesis solution and then ported to the cloud C) It was designed as a hybrid database to allow customers to store data either on premise or in the cloud D) It was built for Hadoop architecture E) It's based on Oracle architecture - ANSA It was build specifically for the cloud Which of the following statements would you use to change the warehouse for workload processing to a warehouse named 'COMPUTE_WH_XL' A) SET CURRENT_WAREHOUSE = COMPUTE_WH_XL B) USE WAREHOUSE COMPUTE_WH_XL; C) USE CURRENT_WAREHOUSE('COMPUTE_WH_XL')

D) USE CURRENT_WAREHOUSE = COMPUTE_WH , SIZE = XL - ANSB

USE WAREHOUSE COMPUTE_WH_XL;

Which Snowflake edition supports the Search Optimization Service to improve performance of point lookup queries? A) All editions B) Enterprise Edition and above C) Business Critical Edition and above D) All except Virtual Private Snowflake Edition - ANSB Enterprise Edition and above Which Snowflake edition supports transmitting data in encrypted form over the network between VPCs (Virtual Private Cloud) A) All editions B) Enterprise edition and above C) Business Critical edition and above D) All except for the Virtual Private Snowflake edition - ANSC Business Critical and above Which statement best described Snowflake tables? (Choose 1)

A) Tables are logical representations of underlying physical data B) Tables are the physical instantiation of data loaded into Snowflake C) Tables require that clustering keys be defined to perform optimally D) Tables are owned by a user - ANSA Tables are logical representations of underlying physical data Which three of the cloud services providers are supported by Snowflake architecture: A) Amazon Web Services (AWS) B) Google (GCP) C) HP Virtual Private Cloud D) Cloud Foundry E) Microsoft Azure - ANSA, B and E AWS, GCP and Microsoft Azure You own table T1 in schema S1. The schema lives in database D1. In order to grant read-only permissions to this table to a newly created role, R1, you need to... A) Grant USAGE on D1 B) Grant SELECT on D1 C) Grant USAGE on S1

D) Grant SELECT on S1 E) Grant USAGE on T1 F) Grant SELECT on T1 - ANSA, C and F Grant USAGE on D1, USAGE on S1 and SELECT on T1