You have created a table as below CREATE TABLE SNOWFLAKE_BLOG(BLOG_ID NUMBER, BLOG_CONTENT VARCHAR,BLOG_AUTHOR VARCHAR,BLOG_CREATE_DATE TIMESTAMP ); Now you want to cluster it by BLOG_AUTHOR and BLOG_CREATE_DATE. Which command below will you choose?
Correct Answer: A
Question 42
Assuming all Snowflake accounts are using an Enterprise edition or higher, in which development and testing scenarios would be copying of data be required, and zero-copy cloning not be suitable? (Select TWO).
Correct Answer: A,C
Zero-copy cloning is a feature that allows creating a clone of a table, schema, or database without physically copying the data. Zero-copy cloning is suitable for scenarios where the cloned object needs to have the same data and metadata as the original object, and where the cloned object does not need to be modified or updated frequently. Zero-copy cloning is also suitable for scenarios where the cloned object needs to be shared within the same Snowflake account or across different accounts in the same cloud region2 However, zero-copy cloning is not suitable for scenarios where the cloned object needs to have different data or metadata than the original object, or where the cloned object needs to be modified or updated frequently. Zero-copy cloning is also not suitable for scenarios where the cloned object needs to be shared across different accounts in different cloud regions. In these scenarios, copying of data would be required, either by using the COPY INTO command or by using data sharing with secure views3 The following are examples of development and testing scenarios where copying of data would be required, and zero-copy cloning would not be suitable: * Developers create their own datasets to work against transformed versions of the live data. This scenario requires copying of data because the developers need to modify the data or metadata of the cloned object to perform transformations, such as adding, deleting, or updating columns, rows, or values. Zero-copy cloning would not be suitable because it would create a read-only clone that shares the same data and metadata as the original object, and any changes made to the clone would affect the original object as well4 * Data is in a production Snowflake account that needs to be provided to Developers in a separate * development/testing Snowflake account in the same cloud region. This scenario requires copying of data because the data needs to be shared across different accounts in the same cloud region. Zero-copy cloning would not be suitable because it would create a clone within the same account as the original object, and it would not allow sharing the clone with another account. To share data across different accounts in the same cloud region, data sharing with secure views or COPY INTO command can be used5 The following are examples of development and testing scenarios where zero-copy cloning would be suitable, and copying of data would not be required: * Production and development run in different databases in the same account, and Developers need to see production-like data but with specific columns masked. This scenario can use zero-copy cloning because the data needs to be shared within the same account, and the cloned object does not need to have different data or metadata than the original object. Zero-copy cloning can create a clone of the production database in the development database, and the clone can have the same data and metadata as the original database. To mask specific columns, secure views can be created on top of the clone, and the developers can access the secure views instead of the clone directly6 * Developers create their own copies of a standard test database previously created for them in the development account, for their initial development and unit testing. This scenario can use zero-copy cloning because the data needs to be shared within the same account, and the cloned object does not need to have different data or metadata than the original object. Zero-copy cloning can create a clone of the standard test database for each developer, and the clone can have the same data and metadata as the original database. The developers can use the clone for their initial development and unit testing, and any changes made to the clone would not affect the original database or other clones7 * The release process requires pre-production testing of changes with data of production scale and complexity. For security reasons, pre-production also runs in the production account. This scenario can use zero-copy cloning because the data needs to be shared within the same account, and the cloned object does not need to have different data or metadata than the original object. Zero-copy cloning can create a clone of the production database in the pre-production database, and the clone can have the same data and metadata as the original database. The pre-production testing can use the clone to test the changes with data of production scale and complexity, and any changes made to the clone would not affect the original database or the production environment8 References: * 1: SnowPro Advanced: Architect | Study Guide 9 * 2: Snowflake Documentation | Cloning Overview * 3: Snowflake Documentation | Loading Data Using COPY into a Table * 4: Snowflake Documentation | Transforming Data During a Load * 5: Snowflake Documentation | Data Sharing Overview * 6: Snowflake Documentation | Secure Views * 7: Snowflake Documentation | Cloning Databases, Schemas, and Tables * 8: Snowflake Documentation | Cloning for Testing and Development * : SnowPro Advanced: Architect | Study Guide * : Cloning Overview * : Loading Data Using COPY into a Table * : Transforming Data During a Load * : Data Sharing Overview * : Secure Views * : Cloning Databases, Schemas, and Tables * : Cloning for Testing and Development
Question 43
Which Snowflake objects can be used in a data share? (Select TWO).
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: C
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. References: 1: Considerations for Choosing Clustering for a Table | Snowflake Documentation
Question 45
What considerations need to be taken when using database cloning as a tool for data lifecycle management in a development environment? (Select TWO).
Correct Answer: A,D
Database cloning is a feature of Snowflake that allows creating a copy of a database, schema, table, or view without consuming any additional storage space. Database cloning can be used as a tool for data lifecycle management in a development environment, where developers and testers can work on isolated copies of production data without affecting the original data or each other1. However, there are some considerations that need to be taken when using database cloning in a development environment, such as: * Any pipes in the source are not cloned. Pipes are objects that load data from a stage into a table continuously. Pipes are not cloned because they are associated with a specific stage and table, and cloning them would create duplicate data loading and potential conflicts2. * The clone inherits all granted privileges of all child objects in the source object, including the database. Privileges are the permissions that control the access and actions that can be performed on an object. When a database is cloned, the clone inherits all the privileges that were granted on the source database and its child objects, such as schemas, tables, and views. This means that the same roles that can access and modify the source database can also access and modify the clone, unless the privileges are explicitly revoked or modified3. The other options are not correct because: * B. Any pipes in the source referring to internal stages are not cloned. This is a subset of option A, which states that any pipes in the source are not cloned, regardless of the type of stage they refer to. * C. Any pipes in the source referring to external stages are not cloned. This is also a subset of option A, which states that any pipes in the source are not cloned, regardless of the type of stage they refer to. * E. The clone inherits all granted privileges of all child objects in the source object, excluding the database. This is incorrect, as the clone inherits all granted privileges of the source object, including the database. References: * 1: Database Cloning | Snowflake Documentation * 2: Pipes | Snowflake Documentation * 3: Access Control Privileges | Snowflake Documentation
