Snowflake COF-C02 - SnowPro Core Certification Exam

Details around the privileges that have been granted for all objects in an account. TheACCESS_HISTORYview in Snowflake provides a comprehensive log of access control changes, including grants and revocations of privileges on all securable objects within the account. This information is crucial for auditing and monitoring the security posture of your Snowflake environment.

Here's how to understand and use theACCESS_HISTORYview:

Purpose of ACCESS_HISTORY View:It is designed to track changes in access controls, such as when a user or role is granted or revoked privileges on various Snowflake objects. This includes tables, schemas, databases, and more.

Querying ACCESS_HISTORY:To access this view, you can use the following SQL query pattern:

SELECT*FROMSNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORYWHEREEVENT_TYPE ='GRANT'OREVENT_TYPE ='REVOKE';

Interpreting the Results:The results from theACCESS_HISTORYview include the object type, the specific privilege granted or revoked, the grantee (who received or lost the privilege), and the timestamp of the event. This data is invaluable for audits and compliance checks.

A materialized view in Snowflake gets suspended when structural changes that could impact the view's integrity are made to the base table, such asB. When a column is dropped from the base table. Dropping a column from the base table on which a materialized view is defined can invalidate the view's data, as the view might rely on the column that is being removed. To maintain data consistency and prevent the materialized view from serving stale or incorrect data, Snowflake automatically suspends the materialized view.

Upon suspension, the materialized view does not reflect changes to the base table until it is refreshed or re-created. This ensures that only accurate and current data is presented to users querying the materialized view.

In Snowflake, theCONNECT BYclause allows users to perform recursive queries. Recursive queries are used to process hierarchical or tree-structured data, such as organizational charts or file systems. TheCONNECT BYclause is used in conjunction with theSTART WITHclause to specify the starting point of the hierarchy and the relationship between parent and child rows.

A Reader account in Snowflake can be used to share data with a consumer who does not have a Snowflake account. Reader accounts are a type of shared account provided by data providers to external data consumers, allowing them to access and query shared data using Snowflake's web interface without needing their own Snowflake account.

Temporary and transient tables in Snowflake share several characteristics, notably, neither table type has a Fail-safe period. Fail-safe is a feature that provides additional data protection beyond the Time Travel period. However, this feature does not apply to temporary or transient tables. Additionally, for both types of tables, the data retention period effectively ends when the tables are dropped. This means that once these tables are deleted, their data is not recoverable, distinguishing them from permanent tables, which benefit from Snowflake's Time Travel and Fail-safe features.