Snowflake Updated SnowPro-Core Exam Questions and Answers by pablo

In Snowflake, to create a materialized view, the user must have the necessary privileges on the schema where the view will be created. These privileges are granted through roles, not directly to individual users. Therefore, the correct process is to grant the role to the user and then grant the privilege to create the materialized view to the role itself.

The statement GRANT ROLE MYROLE TO USER USER1; grants the specified role to the user, allowing them to assume that role and exercise its privileges. The subsequent statement CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO MYROLE; grants the privilege to create a materialized view within the specified schema to the role MYROLE. Any user who has been granted MYROLE can then create materialized views in MYDB.MYSCHEMA.

References:

• Snowflake Documentation on Roles
• Snowflake Documentation on Materialized Views

The correct command to view all roles granted to a specific user in Snowflake is SHOW GRANTS TO USER ;. This command lists all access control privileges that have been explicitly granted to the specified user. References: SHOW GRANTS | Snowflake Documentation

 Micro-partitions in Snowflake are immutable objects, which means once they are written, they cannot be modified. This immutability supports the use of Time Travel, allowing users to access historical data within a defined period. Additionally, micro-partitions can significantly reduce the amount of I/O from object storage to virtual warehouses. This is because Snowflake’s query optimizer can skip over micro-partitions that do not contain relevant data for a query, thus reducing the amount of data that needs to be scanned and transferred.

References: [COF-C02] SnowPro Core Certification Exam Study Guide

https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html

 Query compilation in Snowflake occurs in the Cloud Services layer. This layer is responsible for coordinating and managing all aspects of the Snowflake service, including authentication, infrastructure management, metadata management, query parsing and optimization, and security. By handling these tasks, the Cloud Services layer enables the Compute layer to focus on executing queries, while the Storage layer is dedicated to persistently storing data.

References:

• [COF-C02] SnowPro Core Certification Exam Study Guide
• Snowflake Documentation on Snowflake Architecture1