Snowflake Certification Practice Test: Test-13

Welcome to your Snowflake Test-13

Name

Phone

Q1. An hour ago, you ran a complex query. You then ran several simple queries from the same worksheet. You want to export the results from the complex query but they are no longer loaded in the Results pane of the worksheet. What is the least costly way to download the results?

Select 1 option(s):

Click on History -> Locate the Query -> Click the QueryID -> Use the “Export Result“ button

Click on History -> Locate the Query -> Click “Download Results“ in column 3

Type the command Select RESULTS(,,) in the Worksheet and click “Run“

Type the command SELECT RESULTS(-3) into the Worksheet and click “Run“

Q2. Which type of Tables has 7 days Fail-Safe period.

Select 1 option(s):

Permanent

Temporary

External

Transient

Q3. if another objects gets added to a shared database. Will consumer able to see immediately?

Select 1 option(s):

Yes

Q4. If a query is slow, how can the query be changed to improve the query?

Select 1 option(s):

If possible, use the tables defined CLUSTER KEY in the query

Use an ORDER BY on the query to order the data before its searched

Provide a hint to the optimizer

Reorder the list of tables in the query to change the order the tables are read in the query

Change the order of the where clause to force the optimizer to consider the most effective filter first

Q5. What is the right term for Snowflake Architecture

Select 1 option(s):

Shared Network

Shared Disk

Shared Data

Shared Nothing

Shared Memory

Q6. Which tables will experience the most benefit from clustering?

Select 1 option(s):

Tables with sizes between the range of 100 MB to 1 GB compressed

Tables with sizes between the range of 1 GB to 10 GB compressed

All sizes of tables

Tables in the multi-terabyte (TB) range

Q7. Which table type disappears after the close of the session and therefore has no fail-safe, and no time travel options after the close of the session?

Select 1 option(s):

Permanent

External

Transient

Temporary

Q8. Select the right SQL statement which creates a Large Sized Warehouse and can spin new clusters if needed up to 5 and auto suspend in 10 minutes if not in use.

Select 1 option(s):

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = ‘LARGE‘ WAREHOUSE_TYPE = ‘STANDARD‘ AUTO_SUSPEND = 10 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = ‘STANDARD‘;

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = ‘LARGE‘ WAREHOUSE_TYPE = ‘STANDARD‘ AUTO_SUSPEND = NULL AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = ‘STANDARD‘;

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = ‘LARGE‘ WAREHOUSE_TYPE = ‘STANDARD‘ AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = ‘STANDARD‘;

CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = ‘XLARGE‘ WAREHOUSE_TYPE = ‘STANDARD‘ AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = ‘STANDARD‘;

Q9. A CAST command (symbol) will force a value to be output as a certain datatype. Which of the following code samples will result in the “employeename“ being output using the VARCHAR datatype?

Select 1 option(s):

Q10. Which are the correct statements about STREAMS?

Select 2 option(s):

Streams is used to identify and act on changed table records

STREAMS is used to scheduled SQL execution

It is used for Change Data Capture (CDC)

it can not be used with TASKS

Q11. Which is not the function under Metadata Management of Cloud Services Layer?

Select 1 option(s):

Used for storing physical micro partition

Stores Metadata as data is loaded into the system

Handles Queries that can be processes completely from metadata

Used for Time Travel and Cloning

Q12. Which Snowflake Object is a named wrapper around a cluster of servers with CPU, memory, and disk

Select 1 option(s):

Schema

Database

Storage

Virtual Warehouse

Q13. How can you remove the outer array structure of JSON data?

Select 1 option(s):

Set STRIP_INNER_ARRAY = TRUE

Set STRIP_OUTER_BRACKET = TRUE

Set STRIP_OUTER_ARRAY = 1

Set STRIP_OUTER_ARRAY = TRUE

Q14. User-Defined Function (UDF) supports

Select 3 option(s):

Python

Java

JavaScript

SQL

Q15. What is the recommendation for file size for Parquet files for loading?

Select 1 option(s):

16 MB

2 GB

3 GB

1 GB

Q16. Which Snowflake edition does allow for customer-managed keys encryption.

Select 1 option(s):

Only VPS

Standard (or higher)

Enterprise (or higher)

Business Critical (of higher)

Q17. Which type of view is most like a table?

Select 1 option(s):

Standard

Secure

Materialized

External

Q18. How much is the Time-Travel retention period of Transient Table?

Select 1 option(s):

1 day

7 days

0 days

90 days

Q19. Select the Snowflake edition that automatic encryptions all the data.?

Select 1 option(s):

All the editions

VPS

Enterprise

Standard

Business Critical

Q20. A user cannot view the result set from a query that another user executed except ACCOUNTADMIN role. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q21. John has table T1 with Time-Travel retention time period set to 20 days. He decreases the retention period by 15 days to make it 5 days. What impacts will happen on Table data. Please select 2.

Select 2 option(s):

Changes will be ONLY effective for new data coming to Time-Travel

No impact to existing Time Travel data. That will still complete the longer period of 20 days before going to Fail-safe

The data that is currently in Time Travel and if the data is still within the new shorter period, it remains in Time Travel

The data which was in Time Travel for more than 5 days will move to Fail-safe by Snowflake by background process

Q22. Snowflake provides standard and powerful features that ensure the highest levels of security for your account and users if used properly. Which are the true statements about Snowflake Security?

Select 2 option(s):

Federated authentication in Snowflake is compliant with SAML 2.0

Tri-secret requires that customers manage their own keys

Snowflake supports user-based access control

Q23. Which of the following database objects can be cloned in Snowflake?

Select 1 option(s):

Tables

Schemas

File Formats

Databases

All the editions

Q24. Which three objects did we explicitly refer to using the COPY INTO command in the lesson on using external stages?

Select 3 option(s):

SCHEMA

STAGE

FILE FORMAT

TABLE

DATABASE

VIEW

Q25. What is the best recommended size of data file in case of SNOWPIPE continuous loading?

Select 2 option(s):

Same as of Bulk Loading (10 MB - 100 MB uncompressed)

Same as of Bulk Loading (10 MB - 100 MB compressed)

1 GB Compressed

if file taking more than a minute, then split the files into more files

Q26. What is the best use of SCALING OUT?

Select 1 option(s):

Better Concurrency

Better Performance

Q27. How can you ignore metadata and load the already loaded staged file into table?

Select 1 option(s):

Using LOAD = TRUE parameter with COPY INTO command

Using FORCE = YES parameter with COPY INTO command

Using FORCE = TRUE parameter with COPY INTO command

Using LOAD = FORCE parameter with COPY INTO command

Using INSERT = TRUE parameter with COPY INTO command

Using ACCEPT = TRUE parameter with COPY INTO command

Q28. Which are the correct statements for a Virtual Warehouse?

Select 3 option(s):

Standard Virtual Warehouse can Scale-Out

Size determines the number of servers that comprise each cluster in a ware

Warehouse are Sized in “t-shirt“ sizing

Each larger size is double the preceding, in both VMs in the cluster and in Snowflake credits consumed

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

Select 1 option(s):

Resize virtual warehouse

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

All of the above

Consider the practice of organizing data by granular path

Q30. Monica has a EMPLOYEE_DATA table. Monica wants to create another table EMPLOYEE_DATA_OTHER which should be same as EMPLOYEE_DATA table with same data. What is the best option for Monica?

Select 1 option(s):

CREATE SHARE EMPLOYEE_DATA;

Clone the table with same data with SQL command as follows - CREATE TABLE EMPLOYEE_DATA_OTHER CLONE EMPLOYEE_DATA;

Create the table with LIKE SQL command as follows - CREATE TABLE EMPLOYEE_DATA_OTHER LIKE EMPLOYEE_DATA;

Create the table with same data with SQL command as follows - CREATE TABLE EMPLOYEE_DATA_OTHER AS SELECT * FROM EMPLOYEE_DATA;

Q31. John is having SECURITYADMIN role. He created a custom DBA_ROLE. He Granted SYSADMIN role to DBA_ROLE. He created a user ‘Monica‘. John granted DBA_ROLE to Monica. Monica creates a Database Monica_DB. Monica then created a Table T1 in Monica_DB under PUBLIC schema. What should John do to access Table T1 created by Monica?

Select 1 option(s):

USE ROLE SECURITYADMIN; USE DATABASE monica_db; Select * from t1;

GRANT ROLE DBA_ROLE TO John; USE DATABASE monica_db; Select * from t1;

USE ROLE dba_role; USE DATABASE monica_db; Select * from t1;

GRANT TOLE DBA_ROLE TO John; USE ROLE DBA_ROLE; USE DATABASE monica_db; Select * from t1;

Q32. Which cache stores object definitions and statistics?

Select 1 option(s):

Results Cache

Metadata Cache

Warehouse Cache

Q33. How to change the current warehouse of a session?

Select 1 option(s):

None of these

Execute SET WAREHOUSE command

Execute SELECT WAREHOUSE command

Execute USE WAREHOUSE command

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

Select 1 option(s):

Cloud provider deletes the file automatically

Setting DELETE = TRUE in COPY INTO command

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

Setting PURGE = TRUE in COPY INTO command

Q35. Which role at least should have MFA configured?

Select 1 option(s):

ACCOUNTADMIN

SECURITYADMIN

PUBLIC

SYSADMIN

Q36. Snowflake black list some certain IPs by default to ensure that customer is getting highest level of Network security. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q37. Which command will help you get the lists of pipes for which you have access privileges?

Select 1 option(s):

SHOW PIPE

LIST PIPES

LIST PIPE

SHOW PIPES()

SELECT PIPE()

Q38. Which of the following industry compliance standards has Snowflake been audited and certified for?

Select 5 option(s):

SOC1

HIPAA

SOC Type 2

Cloud GBDQ

PCI DSS

FedRamp

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

Select 1 option(s):

FALSE

TRUE

Q40. John has setup process to load specific set of files using both Bulk and Snowpipe. This is best practice to avoid any missed loading either by Bulk loading or Snowpipe. (TRUE /FALSE)

Select 1 option(s):

TRUE

FALSE

Q41. All files stored in stages (for data loading/unloading) automatically encrypted (using either AES 256 standard or 512 strong encryption). (TRUE/FALSE)

Select 1 option(s):

TRUE

FALSE

Q42. Snowflake maintains detailed metadata for each table into which data is loaded. For how long Snowflake keeps the metadata active?

Select 1 option(s):

16 Days

64 Days

32 Days

24 Hours

31 Days

Q43. If a DBA_ROLE creates a Database. Later the DBA_ROLE was dropped. Who will own the Database now which was created by DBA_ROLE?

Select 1 option(s):

Database will be dropped

No one can access

Role who dropped the DBA_ROLE

DBA_ROLE cant be dropped as it is the owner of the Database.

Q44. Semi-structured data types can be cast using what method?

Select 1 option(s):

Column_name::

Column_name AS

() Column_name

Column_name CAST TO

Column_name AS_

Q45. What all commands will you use to unload data from your Snowflake table to your local file systems?

Select 1 option(s):

Only COPY INTO command

COPY INTO command followed by GET command

Only GET command

COPY INTO followed by PUT command

Q46. Snowflake has a default file format if none is explicitly set or declared in SQL syntax. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

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

Select 1 option(s):

50 MB - 500 MB Compressed

10 MB - 100 MB Compressed

10 MB - 100 MB uncompressed

16 MB Compressed

Q48. The VARIANT data type imposes a 10 MB to 100 MB (compressed) size limit on individual rows.(TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q49. Semi-Structured data must be transformed prior to loading the data into a Snowflake table. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q50. Which parameter does help in loading files whose metadata has expired?

Select 1 option(s):

Set LAST_MODIFIED_DATE to more than 64 days

set LOAD_UNCERTAIN_FILES to TRUE

set LOAD_EXPIRED_FILES to TRUE

set LAST_MODIFIED_DATE to within 64 days

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

Select 5 option(s):

Column reordering

String Truncation

Join

Casts

Column omission

Unattempted

Q52. AWS PrivateLink is an AWS service for creating private VPC endpoints that allow direct, secure connectivity between AWS VPCs without traversing the public Internet. (TRUE / FALSE)

Select 1 option(s):

TRUE

FALSE

Q53. Dynamic Data Masking is supported by:

Select 3 option(s):

Standard Edition

Business Critical

Enterprise Edition

VPS

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

Select 1 option(s):

Simply count the loaded records and match with stage

USE parameter VALIDATION_MODE = RETURN__ROWS | RETURN_ERRORS |RETURN_ALL_ERRORS

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

None of these

Q55. If snowflake hostname is https://bbx99999.us-east-1.snowflakecomputing.com, what is the customer Full account name?

Select 1 option(s):

bbx99999

bbx99999.us-east-1.snowflakecomputing

bbx99999.us-east-1

us-east-1

snowflakecomputing

Q56. Which of the following workload is Snowflake suited for?

Select 6 option(s):

Data Lakes

Data Warehouse

Data Science

Data Engineering

Data Sharing and Exchanges

Data Applications

Unattempted

Q57. Dynamic Data Masking is supported by:

Select 3 option(s):

Enterprise Edition

VPS

Business Critical

Standard Edition

Q58. Reclustering in Snowflake is automatic. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q59. John wants to create a multi-cluster warehouse and wants to make sure that whenever new queries are queued, additional clusters should start immediately. How should he configure the Warehouse?

Select 1 option(s):

Configure as SCALE-MAX so that the warehouse is always using maximum number of specified clusters

Snowflake takes care of this automatically so, John does not have to worry about it

Set the SCALING POLICY as ECONOMY

Set the SCALING POLICY as STANDARD

Q60. At what frequency does Snowflake rotate the object keys?

Select 1 option(s):

60 Days

16 Days

30 Days

1 Year

Q61. What is the default compression algorithm Snowflake apply while unloading data?

Select 1 option(s):

GZIP3

BZ2

ZSTD

NONE

GZIP

Q62. What is the column datatype used for ingesting semi-structured data?

Select 1 option(s):

VARCHAR

VARIANT

NUMBER

STRING

FLOAT

Q63. What are the correct SQL extensions used in querying data from Time Travel? Select 2.

Select 2 option(s):

AFTER

INITIAL

BEFORE

Q64. Snowflake supports transforming data while loading it into a table using the COPY command. What all options you have?

Select 4 option(s):

Column reordering

String Truncation

Join

Column omission

Casts

Q65. Named stages come in two varieties, what are they?

Select 2 option(s):

External

Permanent

Internal

Materialized

Secure