Practice Tests COF-C02 - Mock Exam 2 Flashcards
What is the minimum Snowflake edition that supports Database replication between Snowflake accounts (within an organization)?
Standard
Database replication is supported in all Snowflake editions; thus, the minimum edition that supports it is the Standard edition.
https://docs.snowflake.com/en/user-guide/intro-editions.html
Temporary tables can be cloned to permanent tables?
Temporary tables can NOT be cloned to a permanent table.
Doing so will typically show the following error “Temp table cannot be cloned to a permanent table; clone to a transient table instead.”
However, a temporary table may be cloned to a transient table or another temporary table.
Which of the following roles can import a dataset from Snowflake Marketplace?
ACCOUNTADMIN or SECURITYADMIN?
Although any user or role can explore the Snowflake Marketplace, you need a user with the ACCOUNTADMIN privilege or the IMPORT SHARE privilege for consuming data.
For simplicity, we suggest you utilize a user with the ACCOUNTADMIN privilege.
Can you CAST or TRUNCATE during loading data using COPY command?
Yes
During the load process, the COPY command allows for modifying the order of columns, omitting one or more columns, casting data into specified data types, and truncating values
Complex transformations such as joins, filters, aggregations, and the use of FLATTEN are not supported as they are not essential data transformations. Therefore, joining, filtering, and aggregating the data are supported ONLY after the data has been loaded into a table.
https://docs.snowflake.com/en/user-guide/data-load-overview#id2
Can Snowpipe load data directly from Internal Stages and Snowflake Tables?
No. It can from Internal Stages but not from Snowflake Tables
Snowpipe can load data from an external stage as well as an internal stage.When using an external stage, you can use the cloud platform notifications to trigger your Snowpipe.
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro
True or False
Automatic Clustering Service is responsible for Managing multi-cluster virtual warehouses.
False
It is for Redistributing data in micro-partitions according to the clustering key.
Automatic Clustering only adjusts those micro-partitions which benefit from the re-clustering process.
https://docs.snowflake.com/en/user-guide/tables-auto-reclustering
True or False
The default privileges provided to the built-in roles can NOT be revoked.
True
The built-in system-defined roles cannot be dropped. The default privileges granted to those roles cannot be revoked.
https://docs.snowflake.com/en/user-guide/security-access-control-overview#roles
Is Operator tree shown in the Statistics box in the Query Profile?
No.
https://docs.snowflake.com/en/user-guide/ui-query-profile#statistics
True or False
Snowflake guarantees that files are loaded in the order they arrived.
False
Each time data is loaded, metadata is created, called load metadata.
By utilizing this load metadata, Snowflake ensures that it will not reprocess a previously loaded file. Snowflake doesn’t ensure that the files are loaded in the order they arrived.
https://docs.snowflake.com/en/user-guide/data-load-considerations-load
What is the MAXIMUM compute size that Snowflake may allocate to a serverless task?
2X-Large
This sizing is determined dynamically by analyzing recent statistics of similar task executions to optimize performance. The largest compute size available for serverless tasks is equivalent to the capacity of an XXLARGE or 2X-Large user-managed virtual warehouse.
https://docs.snowflake.com/en/user-guide/tasks-intro#serverless-tasks
True or False
When loading data through COPY command, it is required that your table and the file from where the data is being loaded should have the same number of columns.
False
a SELECT statement can be used to select only the required columns from the stage. When loading data into a table using the COPY command, Snowflake allows you to do simple transformations on the data as it is being loaded by using a SELECT statement. During the load process, the COPY command allows for modifying the order of columns, omitting one or more columns, and casting data into specified data types. It is also possible to truncate data using the COPY command if it is larger than the desired column width.
https://docs.snowflake.com/en/user-guide/data-load-overview#simple-transformations-during-a-load
True or False
External Tokenization provides Row-level security
FALSE
Snowflake supports masking policies that may be applied to columns and enforced at the column level to provide column-level security. Column-level security is achieved by dynamic data masking or external Tokenization.
https://docs.snowflake.com/en/user-guide/security-column
True or False
When defining a clustering key, you should choose columns that have very low cardinality.
FALSE
When defining clustering keys, the initial candidate clustering columns are those columns that are frequently used in the WHERE clause or other selective filters.
Additionally, columns that are used for joining can also be considered.
Furthermore, the columns’ cardinality (number of distinct values) is also important. It is crucial to choose a column with a high enough cardinality to allow effective partition pruning while having a low enough cardinality for Snowflake to group data into micro-partitions efficiently. A column with too few distinct values (e.g., gender) will result in minimal partition pruning. On the other hand, a column that has too many distinct values (e.g., customer id) will result in too much overhead when maintaining the partitions.
Also, when creating a multi-column cluster key, order the columns from the lowest cardinality to the highest cardinality; otherwise, the effectiveness of clustering will be reduced.
https://docs.snowflake.com/en/user-guide/tables-clustering-keys
True or False:
When exporting data using the COPY command, the exported file(s) are automatically compressed.
TRUE
When data is unloaded from Snowflake, it is automatically compressed using gzip compression. This is the default behavior; however, you can specify alternate compression methods or turn off compression entirely.
The default size of each output file is 16 MB but can be changed using the MAX_FILE_SIZE parameter. The maximum allowed size per file is 5GB if you export data to cloud storage.
https://docs.snowflake.com/en/user-guide/data-unload-considerations#unloading-to-a-single-file
True or False
A consumer of a shared database can add new tables or views to the shared database.
FALSE
Shared objects are read-only for the consumer and cannot be modified by the consumer.
A database created on Share contains the tables and other objects that the data provider added, but the consumer cannot add additional objects.
True or False
A reader account can consume data from sources other than the producer that created the reader account.
FALSE
A reader account cannot consume data from any data provider other than the data provider that created and owns the reader account.
https://docs.snowflake.com/en/user-guide/data-sharing-reader-create#what-is-restricted-allowed-in-a-reader-account
True or False
Increase the size of the virtual warehouse will help reduce query queuing on a virtual warehouse
FALSE
Queuing can be reduced in a variety of ways.
1) Consider creating additional virtual warehouses and distributing the query workload if using a standard virtual warehouse.
2) Convert a standard virtual warehouse to a multi-cluster virtual warehouse
3) If already using a multi-cluster virtual warehouse, increase the maximum cluster size.
https://docs.snowflake.com/en/user-guide/performance-query-warehouse-queue#options-for-reducing-queues
True or False
Compute Layer and Storage Layer are replicated by Snowflake to ensure high availability
FALSE
Snowflake automatically replicates the Cloud Services layer & the Storage Layer across three availability zones. The storage layer, which uses cloud providers’ blob stores, is replicated synchronously across multiple disk devices and at least three availability zones, transparent to the users.
Similarly, the cloud services layer, primarily composed of the metadata storage system, is deployed, and replicated across 3 availability zones.
The Compute Layer, i.e., the virtual warehouses, is not replicated, although Snowflake can spin up compute instances in a different availability zone if required.
https://developers.snowflake.com/wp-content/uploads/2021/06/Snowflake-High-Availability-for-Data-Apps-Whitepaper.pdf
True or False
I can Use the QUERY_HISTORY view in the ACCOUNT_USAGE schema to see the history of all queries executed in the last 60 minutes.
FALSE
The QUERY_HISTORY table function in the INFORMATION schema provides up-to-date information without latency. The QUERY_HISTORY view in ACCOUNT_USAGE schema can have 3 hours of latency, so it will not be suitable for viewing the last 60 minutes of query history.
True or False
SECURITYADMIN is required to alter the property MINS_TO_BYPASS_NETWORK_POLICY for a user
FALSE
Only Snowflake support can set the value for the MINS_TO_BYPASS_NETWORK_POLICY property for a user.
https://docs.snowflake.com/en/user-guide/network-policies#bypassing-a-network-policy
True or False
The search optimization service in Snowflake is similar to the secondary index concept in typical databases.
TRUE
The search optimization service can significantly enhance the performance of some lookup and analytical queries that use many predicates for filtering.
https://docs.snowflake.com/en/user-guide/search-optimization-service
To create a SHARE, what is the minimum required role?
By default, ACCOUNTADMIN is the only role with the privileges required to create & manage a share because managing Share is an account-level activity. Alternatively, using the ACCOUNTADMIN role, you can grant the privileges to manage shares to other roles.
https://docs.snowflake.com/en/user-guide/data-sharing-gs
ACCOUNTADMIN inherits the privileges of which roles?
Due to the role hierarchy and privileges inheritance, the ACCOUNTADMIN has all the privileges that SECURITYADMIN, USERAMDIN, and SYSADMIN have.
https://docs.snowflake.com/en/user-guide/security-access-control-overview#system-defined-roles.
True or False
An external table can only be created using an external stage.
TRUE
An external table is a metadata definition; that is, you register the definition of an external table, but the external table itself doesn’t contain any data.
The external stage, in turn, points to object storage on the cloud, for example, an AWS bucket or Azure Blob storage, which contains the data for the external table.
Note that an external table can only point to an external stage. An internal stage cannot be used to create an external table.
https://docs.snowflake.com/en/user-guide/tables-external-intro
True or False
Querying a directory table provides a File URL for each file in the corresponding stage. The URL is valid for 90 days.
FALSE
The File URL provided by a directory table is a long-term URL and doesn’t expire.
https://docs.snowflake.com/en/user-guide/data-load-dirtables-intro#what-are-directory-tables
True or False
Snowflake encrypts all data in transit end to end using TLS 1.2.
TRUE
Snowflake encrypts all data in transit using Transport Layer Security (TLS) 1.2. This applies to all Snowflake connections, including those made through the Snowflake Web interface, JDBC, ODBC, and the Python connector.
https://docs.snowflake.com/en/user-guide/security-encryption-end-to-end