What will the below query return SELECT TOP 10 GRADES FROM STUDENT;
Correct Answer: C
Question 112
Which command will create a schema without Fail-safe and will restrict object owners from passing on access to other users?
Correct Answer: D
A transient schema in Snowflake is designed without a Fail-safe period, meaning it does not incur additional storage costs once it leaves Time Travel, and it is not protected by Fail-safe in the event of a data loss. The WITH MANAGED ACCESS option ensures that all privilege grants, including future grants on objects within the schema, are managed by the schema owner, thus restricting object owners from passing on access to other users1. Reference = * Snowflake Documentation on creating schemas1 * Snowflake Documentation on configuring access control2 * Snowflake Documentation on understanding and viewing Fail-safe3
Question 113
A table contains five columns and it has millions of records. The cardinality distribution of the columns is shown below: Column C4 and C5 are mostly used by SELECT queries in the GROUP BY and ORDER BY clauses. Whereas columns C1, C2 and C3 are heavily used in filter and join conditions of SELECT queries. The Architect must design a clustering key for this table to improve the query performance. Based on Snowflake recommendations, how should the clustering key columns be ordered while defining the multi-column clustering key?
Correct Answer: D
According to the Snowflake documentation, the following are some considerations for choosing clustering for a table1: Clustering is optimal when either: You require the fastest possible response times, regardless of cost. Your improved query performance offsets the credits required to cluster and maintain the table. Clustering is most effective when the clustering key is used in the following types of query predicates: Filter predicates (e.g. WHERE clauses) Join predicates (e.g. ON clauses) Grouping predicates (e.g. GROUP BY clauses) Sorting predicates (e.g. ORDER BY clauses) Clustering is less effective when the clustering key is not used in any of the above query predicates, or when the clustering key is used in a predicate that requires a function or expression to be applied to the key (e.g. DATE_TRUNC, TO_CHAR, etc.). For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key. Adding more than 3-4 columns tends to increase costs more than benefits. Based on these considerations, the best option for the clustering key columns is C. C1, C3, C2, because: These columns are heavily used in filter and join conditions of SELECT queries, which are the most effective types of predicates for clustering. These columns have high cardinality, which means they have many distinct values and can help reduce the clustering skew and improve the compression ratio. These columns are likely to be correlated with each other, which means they can help co-locate similar rows in the same micro-partitions and improve the scan efficiency. These columns do not require any functions or expressions to be applied to them, which means they can be directly used in the predicates without affecting the clustering.
Question 114
You have create a table in snowflake as below CREATE TABLE EMPLOYEE(EMPLOYEE_NAME STRING, SALARY NUMBER); When you do a DESCRIBE TABLE EMPLOYEE, what will you see as the data type of EMPLOYEE_NAME?
Correct Answer: D
Question 115
A company has a table with that has corrupted data, named Data. The company wants to recover the data as it was 5 minutes ago using cloning and Time Travel. What command will accomplish this?
Correct Answer: C
This is the correct command to create a clone of the table Data as it was 5 minutes ago using cloning and Time Travel. Cloning is a feature that allows creating a copy of a database, schema, table, or view without duplicating the data or metadata. Time Travel is a feature that enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. To create a clone of a table at a point in time in the past, the syntax is: CREATE TABLE <clone_name> CLONE <source_table> AT (OFFSET => <offset_in_seconds>); The OFFSET parameter specifies the time difference in seconds from the present time. A negative value indicates a point in the past. For example, -60*5 means 5 minutes ago. Alternatively, the TIMESTAMP parameter can be used to specify an exact timestamp in the past. The clone will contain the data as it existed in the source table at the specified point in time12. Snowflake Documentation: Cloning Objects Snowflake Documentation: Cloning Objects at a Point in Time in the Past
