Snowflake SnowPro Core COF-C02 mock test 8

Welcome to your Snowflake Test-8

Name

Email

Phone

Q1. What is the data type provided by Snowflake to store semi structure data?

Select 1 option(s):

Semi-structure

Variant

Varchar

Text

Q2. What all objects can be shared?

Select 3 option(s):

Secure UDF

Table

Standard View

Secure View

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

Select 1 option(s):

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

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

Provide a hint to the optimizer

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

Q4. Which cache stores object definitions and statistics?

Select 1 option(s):

Metadata Cache

Results Cache

Warehouse Cache

Q5. Which of the following Snowflake Editions automatically store data in an encrypted state?

Select 3 option(s):

Enterprise

Business Critical

Standard

Unattempted

Q6. John created a table EMPLOYEE in worksheet 1 (Session1 ) but when he ran SELECT * FROM EMPLOYEE in worksheet 2 (Session 2) after setting up the correct Role, Database and Schema, he received error ‘ Table doesn‘t exist‘. What could be the reason?

Select 1 option(s):

XYZ is Transient Table

XYZ was created as EXTERNAL TABLE

EMPLOYEE is Temporary table

XYZ is a Permanent Table

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

Select 4 option(s):

PERMANENT

TRANSIENT

EXTERNAL

TEMPORARY

INTERNAL

Q8. Clustering keys are not intended for all tables. (TRUE / FALSE)

Select 1 option(s):

FALSE

TRUE

Q9. When a database or schema that contains a source table and stream is cloned, any unconsumed records in the stream (in the clone) are inaccessible

Select 1 option(s):

FALSE

TRUE

Q10. Which of the following are valid context functions?

Select 3 option(s):

CURRENT_REGION( )

CURRENT_CLIENT( )

CURRENT_WORKSHEET( )

CURRENT_CLOUD_INFRASTRUCTURE( )

CURRENT_SESSION( )

Q11. True or false: You can point Snowflake at any S3 bucket to directly query the files in that bucket as long as the files are in Parquet or ORC format.

Select 1 option(s):

TRUE

FALSE

Q12. What is the full form of JSON?

Select 1 option(s):

Java Object Notation

Javascript Notation

JavaScript Object Notation

Java Semi Object Notation

Q13. JSON arrays are written inside which type of brackets?

Select 1 option(s):

Round Brackets

Square Brackets

Curly Braces

Q14. The VARIANT data type imposes, how much size limit on individual rows

Select 1 option(s):

64 MB Compressed

16 MB Uncompressed

64 MB Uncompressed

16 MB Compressed

Q15. Which two table types dont have fail safe?

Select 2 option(s):

TEMPORARY

TRANSIENT

PERMANENT

Q16. If a row is added and then updated in the current offset,then the METADATA$ISUPDATE row records a TRUE value.

Select 1 option(s):

TRUE

FALSE

Q17. Extended time travel of 90 days is supported starting which snowflake edition

Select 1 option(s):

Standard

Enterprise

Business Critical

Premier

Virtual Private Snowflake

Q18. True or false: A table in Snowflake can contain both a variant columns and a timestamp.

Select 1 option(s):

FALSE

TRUE

Q19. What is the right term for Snowflake Architecture

Select 1 option(s):

Shared Network

Shared Data

Shared Nothing

Shared Disk

Shared Memory

Q20. Which cache type gets purged regularly?

Select 1 option(s):

Warehouse Cache

Metadata Cache

Results Cache

Q21. Which type of view has an extra layer of protection to hide the SQL code from unauthorized viewing?

Select 1 option(s):

Standard

Secure

Materialized

Permanent

Q22. What is the name of the Snowflake-produced Command Line Interface tool?

Select 1 option(s):

SnowConnect

Snow Command

SnowSQL

SnowCLI

Q23. When choosing a geographic deployment region, what factors might an enrollee consider?

Select 2 option(s):

End-user perceptions of glamorous or trendy geographic locations

Proximity to the point of service

Number of availability zones within a region

Additional fees charged for regions with geo-political unrest

Q24. Which is not the function of Optimization activities of Cloud Services layer?

Select 1 option(s):

Pruning using metadata about micro-partitions

Handles Queries that can be processes completely from metadata

Automatic JOIN order optimization

Automatic statistic gathering

Q25. Two virtual warehouses can access the same data at the same time without causing contention issues

Select 1 option(s):

TRUE

FALSE

Q26. Which SQL statements will need a running Warehouse and consume credit? Consider table created as – CREATE OR REPLACE TABLE T1 ( ID number(2), Name varchar(20), Salary number (11 , 2) ) ;

Select 1 option(s):

Q27. Snowflake offers rich set of drivers and connectors to connect with external applications. Select the Connectors from the list. (Choose 3)

Select 3 option(s):

Kafka

JDBC

Spark

Python

Node.js

Q28. There are two types of Snowflake planned releases. Please choose all applicable.

Select 2 option(s):

Full Release

Upgrade Release

Installation release

Patch Release

Q29. Each Snowflake account comes with two shared databases. One is a set of sample data and the other contains Account Usage information. Check all true statements about these shared databases.

Select 2 option(s):

SNOWFLAKE contains a table called ACCOUNT_USAGE

ACCOUNT USAGE is a schema filled with secure views

SNOWFLAKE contains a schema called ACCOUNT_USAGE

SNOWFLAKE_SAMPLE_DATA contains a schema called ACCOUNT_USAGE

ACCOUNT_USAGE is a schema filled with external tables

Q30. To change the warehouse that will be used to run a SQL command within a specific worksheet (for example, changing the worksheet so that it uses LARGE), what two options are available?

Select 2 option(s):

Update the Warehouse field in the Context Menu located above the worksheet.

Run a SQL command like “SET WAREHOUSE CONTEXT=LARGE_WH“

Run a SQL Command like “USE WAREHOUSE LARGE_WH;“

Go to Worksheet properties page and set the Warehouse field to LARGE_WH

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

Select 1 option(s):

Used for storing physical micro partition

Handles Queries that can be processes completely from metadata

Used for Time Travel and Cloning

Stores Metadata as data is loaded into the system

Q32. 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 should contact Snowflake Personnel

John is using comparatively smaller warehouse

Warehouse size has no impact on Bytes spilling

John is using very large warehouse

Q33. Select all the features Snowflake supports:

Select 3 option(s):

Semi-Structured data

Dedicated resource for compute

Unstructured data

SQL-based transformation

A graphical UI to build data pipelines

Q34. The time travel retention period can only be set at individual object level, and cannot be setup at account level

Select 1 option(s):

FALSE

TRUE

Q35. Which type of table stream is supported only on external tables?

Select 1 option(s):

Native

Append-Only

Insert-Only

Update-Only

Standard

Q36. If you have a table with a 10-day retention period and increase the period to 20 days, then the data that was moved to fail safe on 11th day, will be moved back to Time Travel, and will have more 9 days left for time travel retention end

Select 1 option(s):

FALSE

TRUE

Q37. Which parameter is used to check if the stream contains change data for a table or not?

Select 1 option(s):

SYSTEM$STREAM_HAS_CHANGED_DATA

SYSTEM$STREAM_HAS_NEW_DATA

SYSTEM$STREAM_HAS_DATA

SYSTEM$STREAM_HAS_MODIFIED_DATA

Q38. Fail Safe is configurable by the user

Select 1 option(s):

TRUE

FALSE

Q39. MAX_DATA_EXTENSION_TIME_IN_DAYS – This parameter is used for what purpose?

Select 1 option(s):

Maximum number of days for which Snowflake can extend the data retention period for time travel of an object

Maximum number of days for which Snowflake can extend the data retention period for fail safe of an object

Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale

Q40. A table named “MY_TABLE“ has a retention period of 7 days. The table is dropped, and a new table with the same name “MY_TABLE“ has been created. Now if the UNDROP MY_TABLE command is executed what will happen?

Select 1 option(s):

The UNDROP command will fail

The UNDROP command will restore the dropped version with a new name

The UNDROP command will overwrite the already existing table with the previously dropped table

Q41. Many logical data models refer to entities, attributes and values. In JSON, and some other semi-structured types, the word “attribute“ is often replaced with what word?

Select 1 option(s):

Relationships

Columns

Keys

Values

Rows

Q42. The FLATTEN command will parse nested objects into separate rows. One version of the FLATTEN command uses a join and the other uses an object keyword. Select the two words that represent the options used with the FLATTEN command

Select 2 option(s):

SIDEWAYS

TABLE

SCHEMATA

LATERAL

Q43. Which of the following are true about the variant data type in Snowflake? Select all that apply.

Select 2 option(s):

Can be queried using JSON path notation

Optimized storage based on repeated elements

Stored in a separate file format from structured data

Requires a custom mapping for each record type

Q44. When working with unfamiliar semi-structured data, you might not know the key names in an object. You can use the FLATTEN function with which argument to return the list of distinct key names in all nested elements in an object?

Select 1 option(s):

NESTING

GROUPING

RECURSIVE

DISTINCT

Q45. Dropped tables, schemas, and databases can be listed using the SHOW commands with which keyword specified?

Select 1 option(s):

DROPPED_RECORDS

PREVIOUS VERSIONS

HISTORY

OLD VERSIONS

Q46. If you have a table with a 10-day retention period and you decrease the period to 1-day, then what happens to the data that is already in time travel for 4 days

Select 1 option(s):

The data would be removed and will not even be available in fail safe

The data would be moved to fail safe

The data would move to fail safe after 1 day

The data would continue to be in time travel, and new time travel retention of 1 day will be applied to only active data which is not already in time travel window

Q47. An individual task in the tree can have multiple predecessor task

Select 1 option(s):

TRUE

FALSE

Q48. What are the additional metadata columns available in Stream to track changes?

Select 3 option(s):

METADATA$ISDELETE

METADATA$ISINSERT

METADATA$ACTION

METADATA$ISUPDATE

METADATA$ROW_ID

Q49. Snowflake Tasks executions are associated with the user who created the task, i.e. in the history database we can see that the task is getting executed under the username of the User who created that task

Select 1 option(s):

TRUE

FALSE

Q50. JSON data is a hierarchical collection of name/value pairs grouped into objects and arrays

Select 1 option(s):

FALSE

TRUE

Q51. To restore the table by undrop command what all conditions should be met?

Select 3 option(s):

The table can be restored in same schema only from which it has been dropped

User must of CREATE privileges on the schema in which table is getting restored

The table can be restored in any schema irrespective from whichever schema the table was dropped.

User executing undrop command should have ownership privilege on the table

Q52. What features are provided by Snowflake to enable continuous data pipelines?

Select 1 option(s):

All options are correct

Continous Data Loading

Recurring Tasks

Change Data Tracking

Q53. To help manage the storage costs associated with Time Travel and Fail-safe, Snowflake provides which two table types

Select 2 option(s):

TRANSIENT

MATERIALIZED

PERMANENT

TEMPORARY

Q54. If a task is still running when the next scheduled execution time occurs, what happens?

Select 1 option(s):

The tasks that is running fails and stops

The tasks that is running fails and retriggers

The next execution of the tasks starts as soon as the current execution gets over

That schedule time is skipped

Q55. An append-only table stream tracks row inserts only. Update and delete operations (including table truncates) are not recorded

Select 1 option(s):

TRUE

FALSE

Q56. Fail Safe and time travel are one and the same thing

Select 1 option(s):

TRUE

FALSE

Q57. What is the limit of number of tasks that can be included in a tree of tasks?

Select 1 option(s):

100

Unlimited

1000

10000

Q58. Non-native values such as dates and timestamps are stored as strings when loaded into a VARIANT column

Select 1 option(s):

TRUE

FALSE

Q59. A task object defines a recurring schedule for executing a SQL statement, including statements that call stored procedures

Select 1 option(s):

FALSE

TRUE

Q60. What data structure types can be ingested into a VARIANT column in a Snowflake table? (Check all that apply)

Select 5 option(s):

JSON

XML

ORC

PARQUET

HADOOP

AVRO

Q61. UNDROP command is applicable to which all objects

Select 3 option(s):

DATABASES

SCHEMA

TABLES

ROLES

USERS

Q62. Which file format option can be enabled in JSON or AVRO file to remove the outer array structure and load the records into separate table rows

Select 1 option(s):

REMOVE_OUTER_ARRAY

STRIP_OUTER_ARRAY

ELIMINATE_OUTER_ARRAY

READ_INTERNAL_ARRAYS

Q63. Check all true statements about Fail-safe:

Select 2 option(s):

Only a Snowflake employee can recover data from fail-safe storage

Fail-safe is not available for tables that have Time Travel.

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

The data stored as part of fail-safe is part of storage costs charged to customers

Q64. Time Travel in Snowflake is useful for which of the following tasks

Select 2 option(s):

Analyzing data usage/manipulation over specified periods of time

Restoring data-related objects (tables, schemas, and databases) that might have been accidentally or intentionally deleted

Restoring data in event of a system failure or other catastrophic event, e.g. a hardware failure or security breach.

Duplicating and backing up data from key points in the past

Q65. What is the standard time travel retention period which is enabled for all snowflake accounts?

Select 1 option(s):

7 days

14 days

24 hours

48 hours

Snowflake Test-8

Q1. What is the data type provided by Snowflake to store semi structure data?

Q2. What all objects can be shared?

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

Q4. Which cache stores object definitions and statistics?

Q5. Which of the following Snowflake Editions automatically store data in an encrypted state?

Q6. John created a table EMPLOYEE in worksheet 1 (Session1 ) but when he ran SELECT * FROM EMPLOYEE in worksheet 2 (Session 2) after setting up the correct Role, Database and Schema, he received error ‘ Table doesn‘t exist‘. What could be the reason?

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

Q8. Clustering keys are not intended for all tables. (TRUE / FALSE)

Q9. When a database or schema that contains a source table and stream is cloned, any unconsumed records in the stream (in the clone) are inaccessible

Q10. Which of the following are valid context functions?

Q11. True or false: You can point Snowflake at any S3 bucket to directly query the files in that bucket as long as the files are in Parquet or ORC format.

Q12. What is the full form of JSON?

Q13. JSON arrays are written inside which type of brackets?

Q14. The VARIANT data type imposes, how much size limit on individual rows

Q15. Which two table types dont have fail safe?

Q16. If a row is added and then updated in the current offset,then the METADATA$ISUPDATE row records a TRUE value.

Q17. Extended time travel of 90 days is supported starting which snowflake edition

Q18. True or false: A table in Snowflake can contain both a variant columns and a timestamp.

Q19. What is the right term for Snowflake Architecture

Q20. Which cache type gets purged regularly?

Q21. Which type of view has an extra layer of protection to hide the SQL code from unauthorized viewing?

Q22. What is the name of the Snowflake-produced Command Line Interface tool?

Q23. When choosing a geographic deployment region, what factors might an enrollee consider?

Q24. Which is not the function of Optimization activities of Cloud Services layer?

Q25. Two virtual warehouses can access the same data at the same time without causing contention issues

Q26. Which SQL statements will need a running Warehouse and consume credit? Consider table created as – CREATE OR REPLACE TABLE T1 ( ID number(2), Name varchar(20), Salary number (11 , 2) ) ;

Q27. Snowflake offers rich set of drivers and connectors to connect with external applications. Select the Connectors from the list. (Choose 3)

Q28. There are two types of Snowflake planned releases. Please choose all applicable.

Q29. Each Snowflake account comes with two shared databases. One is a set of sample data and the other contains Account Usage information. Check all true statements about these shared databases.

Q30. To change the warehouse that will be used to run a SQL command within a specific worksheet (for example, changing the worksheet so that it uses LARGE), what two options are available?

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

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

Q33. Select all the features Snowflake supports:

Q34. The time travel retention period can only be set at individual object level, and cannot be setup at account level

Q35. Which type of table stream is supported only on external tables?

Q36. If you have a table with a 10-day retention period and increase the period to 20 days, then the data that was moved to fail safe on 11th day, will be moved back to Time Travel, and will have more 9 days left for time travel retention end

Q37. Which parameter is used to check if the stream contains change data for a table or not?

Q38. Fail Safe is configurable by the user

Q39. MAX_DATA_EXTENSION_TIME_IN_DAYS – This parameter is used for what purpose?

Q40. A table named “MY_TABLE“ has a retention period of 7 days. The table is dropped, and a new table with the same name “MY_TABLE“ has been created. Now if the UNDROP MY_TABLE command is executed what will happen?

Q41. Many logical data models refer to entities, attributes and values. In JSON, and some other semi-structured types, the word “attribute“ is often replaced with what word?

Q42. The FLATTEN command will parse nested objects into separate rows. One version of the FLATTEN command uses a join and the other uses an object keyword. Select the two words that represent the options used with the FLATTEN command

Q43. Which of the following are true about the variant data type in Snowflake? Select all that apply.

Q44. When working with unfamiliar semi-structured data, you might not know the key names in an object. You can use the FLATTEN function with which argument to return the list of distinct key names in all nested elements in an object?

Q45. Dropped tables, schemas, and databases can be listed using the SHOW commands with which keyword specified?

Q46. If you have a table with a 10-day retention period and you decrease the period to 1-day, then what happens to the data that is already in time travel for 4 days

Q47. An individual task in the tree can have multiple predecessor task

Q48. What are the additional metadata columns available in Stream to track changes?

Q49. Snowflake Tasks executions are associated with the user who created the task, i.e. in the history database we can see that the task is getting executed under the username of the User who created that task

Q50. JSON data is a hierarchical collection of name/value pairs grouped into objects and arrays

Q51. To restore the table by undrop command what all conditions should be met?

Q52. What features are provided by Snowflake to enable continuous data pipelines?

Q53. To help manage the storage costs associated with Time Travel and Fail-safe, Snowflake provides which two table types

Q54. If a task is still running when the next scheduled execution time occurs, what happens?

Q55. An append-only table stream tracks row inserts only. Update and delete operations (including table truncates) are not recorded

Q56. Fail Safe and time travel are one and the same thing

Q57. What is the limit of number of tasks that can be included in a tree of tasks?

Q58. Non-native values such as dates and timestamps are stored as strings when loaded into a VARIANT column

Q59. A task object defines a recurring schedule for executing a SQL statement, including statements that call stored procedures

Q60. What data structure types can be ingested into a VARIANT column in a Snowflake table? (Check all that apply)

Q61. UNDROP command is applicable to which all objects

Q62. Which file format option can be enabled in JSON or AVRO file to remove the outer array structure and load the records into separate table rows

Q63. Check all true statements about Fail-safe:

Q64. Time Travel in Snowflake is useful for which of the following tasks

Q65. What is the standard time travel retention period which is enabled for all snowflake accounts?

Q15. Which two table types dont have fail safe?