Free Practice Questions for Snowflake SnowPro-Core Exam

The property mins_to_bypass_network_policy is set at the account level in Snowflake. This setting allows administrators to specify a time frame during which users can bypass network policies that have been set on their account. It is particularly useful in scenarios where temporary access needs to be granted from IPs not covered by the existing network policies. By adjusting this property at the account level, Snowflake administrators can manage and enforce network access controls efficiently across the entire account.

References:

Snowflake Documentation on Network Policies: Network Policies

In Snowflake, a Temporary table is a type of table that is only visible to the user who creates it, can have the same name as permanent tables in the same schema, and is automatically dropped at the end of the session in which it was created. Temporary tables are designed for transient data processing needs, where data is needed for the duration of a specific task or session but not beyond. Since they are automatically cleaned up at the end of the session, they help manage storage usage efficiently and ensure that sensitive data is not inadvertently persisted.

References:

Snowflake Documentation on Temporary Tables: Temporary Tables

If any compute resources for a virtual warehouse fail to provision during startup, Snowflake will attempt to restart those failed resources. The system is designed to automatically handle transient issues that might occur during the provisioning of compute resources. By restarting the failed resources, Snowflake aims to ensure that the virtual warehouse has the necessary compute capacity to handle the user's workloads without manual intervention.References: Snowflake Documentation on Virtual Warehouses

Snowflake implements Discretionary Access Control (DAC) by using a role-based access control model. In this model, access privileges are not directly assigned to individual objects or users but are encapsulated within roles. These roles are then assigned to users, effectively granting them the access privileges contained within the role. This approach allows for granular control over database access, making it easier to manage permissions in a scalable and flexible manner.References: Snowflake Documentation on Access Control

Snowflake's access control framework utilizes a combination of Discretionary Access Control (DAC) and Role-based Access Control (RBAC). DAC in Snowflake allows the object owner to grant access privileges to other roles. RBAC involves assigning roles to users and then granting privileges to those roles. Through roles, Snowflake manages which users have access to specific objects and what actions they can perform, which is central to security and governance in the Snowflake environment.References: Snowflake Documentation on Access Control,

Snowflake streams are objects that enable users to track changes (inserts, updates, and deletes) to the data in tables, facilitating real-time data processing and integration workflows. Streams can be created on standard tables, capturing DML changes made to these tables so that downstream processes can consume the changes incrementally. This feature supports efficient data ETL, replication, and real-time analytics by providing a mechanism to process only the data that has changed. Note: The correct options should likely include a distinction between "Standard tables" and another object type such as "External tables" rather than repeating "Standard tables" twice.References: Snowflake Documentation on Streams

The ACCESS_HISTORY view in Snowflake is a powerful tool for enhancing data governance, especially concerning monitoring and auditing data access patterns for both read and write operations. The key ways in which ACCESS_HISTORY enhances overall data governance are:

B. Provides a unified picture of what data was accessed and when it was accessed: This view logs details about query executions, including the objects (tables, views) accessed and the timestamps of these accesses. It's instrumental in auditing and compliance scenarios, where understanding the access patterns to sensitive data is critical.

E. Determines whether a given row in a table can be accessed by the user by filtering the data based on a given policy: While this option is a bit of a misinterpretation of what ACCESS_HISTORY directly offers, it indirectly supports data governance by providing the information necessary to analyze access patterns. This analysis can then inform policy decisions, such as implementing Row-Level Security (RLS) to restrict access to specific rows based on user roles or attributes.

ACCESS_HISTORY does not automatically apply data masking or tag columns with personal information. However, the insights derived from analyzing ACCESS_HISTORY can be used to identify sensitive data and inform the application of masking policies or other security measures.

References:

Snowflake Documentation on ACCESS_HISTORY: Access History

In Snowflake, a user within a reader account primarily has read-only access to the shared data. Reader accounts are created to enable third parties or separate business units to access and query data shared with them without allowing them to modify the underlying data. This means a user with a reader account can perform operations like querying shared data to analyze or report on it but cannot load new data, update existing data, or create new shares. This setup is crucial for maintaining data governance and security while enabling data sharing and collaboration.References: Snowflake Documentation on Reader Accounts

Snowflake's Streams feature is specifically designed for change data capture (CDC). A stream records insert, update, and delete operations performed on a table, and allows users to query these changes. This enables actions to be taken on the changed data, facilitating processes like incremental data loads and real-time analytics. Streams provide a powerful mechanism for applications to respond to data changes in Snowflake tables efficiently.References: Snowflake Documentation on Streams

The LOGIN_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE shows from which IP address a user connected to Snowflake. This view is particularly useful for auditing and monitoring purposes, as it helps administrators track login attempts, successful logins, and the geographical location of users based on their IP addresses.

Reference to Snowflake documentation on LOGIN_HISTORY:

Monitoring Login Attempts

In Snowflake, permissions for accessing database objects, including tables, are not granted directly to users but rather to roles. A role encapsulates a collection of privileges on various Snowflake objects. Users are then granted roles, and through those roles, they inherit the permissions necessary to read a table or perform other actions. This approach adheres to the principle of least privilege, allowing for granular control over database access and simplifying the management of user permissions.

For users assigned the ACCOUNTADMIN role, Snowflake recommends enforcing Multi-Factor Authentication (MFA) to enhance security. The ACCOUNTADMIN role has extensive permissions, making it crucial to secure accounts held by such users against unauthorized access. MFA adds an additional layer of security by requiring a second form of verification beyond just the username and password, significantly reducing the risk of account compromise.References: Snowflake Security Best Practices

The GET_PRESIGNED_URL file function in Snowflake provides a URL with access to a file on a stage without requiring authentication and authorization. This is particularly useful for sharing data files stored in Snowflake stages with external parties securely and conveniently. The presigned URL generated by this function gives temporary access to the file, which expires after a specified duration.

Example usage of GET_PRESIGNED_URL:

SELECT GET_PRESIGNED_URL('', '');

This function generates a URL that can be used to directly access a file in the stage, making it easier to share data without compromising security.

In addition to performing all the standard steps to share data, the USAGE privilege must be granted on each database referenced by a secure view in order to be shared. When sharing a database or specific objects like secure views, the receiving account needs to have the USAGE privilege on the database and schema to access the shared data. This privilege enables the receiving account to access the database and its schemas but does not allow for any DML operations. It's a prerequisite for accessing any objects within the database.

For a secure view to be part of a share, not only does the view itself need to be shared, but the underlying database (and schema, if applicable) must also be accessible to the recipients. Granting USAGE privilege on the database ensures that the receiving account can access the database in a read-only mode to utilize the shared view.

References:

Snowflake Documentation on Shares: Creating and Managing Shares

In a scenario where a Snowflake user queries only a small number of rows that require significant processing and the data in the table does not change frequently, the most effective way to optimize performance is by creating a materialized view based on the query. Materialized views store the result of the query and can significantly reduce the computation time for queries that are executed frequently over unchanged data.

Why Materialized Views: Materialized views precompute and store the result of the query. This is especially beneficial for queries that require heavy processing. Since the data does not change frequently, the materialized view will not need to be refreshed often, making it an ideal solution for this use case.

Implementation Steps:

To create a materialized view, use the following SQL command:

CREATE MATERIALIZED VIEW my_materialized_view AS SELECT ... FROM my_table WHERE ...;

When the query is run, Snowflake uses the precomputed results from the materialized view, thus skipping the need for recalculating the data and improving query performance.