Free SNOWPRO-CORE Practice Test

The ACCESSHISTORY view, located in the ACCOUNTUSAGE schema, is designed for long-term auditing of data access and modification. It retains historical data for 365 days (1 year). This view logs which columns were accessed in a read operation and which columns were modified in a write operation. The latency for data to appear in this view can be up to 180 minutes (3 hours). This extended retention period is essential for compliance, governance, and security analysis over an annual cycle.

A. 7 days: This is the data retention period for the LOGINHISTORY view, which tracks user login events.
B. 14 days: This is the data retention period for several other ACCOUNTUSAGE views, such as QUERYHISTORY and WAREHOUSEMETERINGHISTORY.
C. 28 days: This is not a standard retention period for the common ACCOUNTUSAGE history views.

1. Snowflake Documentation. (n.d.). ACCESSHISTORY View. Retrieved from https://docs.snowflake.com/en/sql-reference/account-usage/accesshistory.

Reference Point: The "Data Retention" section explicitly states, "The view displays the history of the last 365 days (1 year)."

2. Snowflake Documentation. (n.d.). Account Usage. Retrieved from https://docs.snowflake.com/en/sql-reference/account-usage.

Reference Point: The table summarizing views in the schema shows a "1 Year" retention for ACCESSHISTORY and contrasts it with the retention periods for other views like QUERYHISTORY (14 days) and LOGINHISTORY (7 days).

The BUILDSTAGEFILEURL function is specifically designed to generate a permanent, Snowflake-hosted file URL for a file located in a stage. This function takes the name of the stage and the relative path of the file as its arguments. The resulting URL can be used to access the file, provided the user has the necessary privileges on the stage. This directly addresses the requirement of creating a Snowflake-hosted URL from a stage name and file path for working with unstructured data.

A. GETPRESIGNEDURL: This function generates a temporary, pre-signed URL for accessing files directly from external cloud storage (e.g., S3, Azure Blob), not a Snowflake-hosted URL.
B. GETABSOLUTEPATH: This function returns the absolute path of a file within a stage as a string. It does not generate a web-accessible URL.
D. BUILDSCOPEDFILEURL: This function generates a temporary, scoped URL that grants access to a specific user for a limited time. It is for temporary access, unlike the more general-purpose BUILDSTAGEFILEURL.

1. Snowflake Documentation: SQL Function Reference > BUILDSTAGEFILEURL. This page explicitly states, "Generates a Snowflake-hosted file URL to a staged file using the stage name and relative file path as inputs."

2. Snowflake Documentation: Unstructured Data Support > Overview of Unstructured Data Support. This section describes the various functions for working with unstructured data, differentiating between file URLs (BUILDSTAGEFILEURL), scoped URLs (BUILDSCOPEDFILEURL), and pre-signed URLs (GETPRESIGNEDURL).

3. Snowflake Documentation: SQL Function Reference > GETPRESIGNEDURL. The documentation clarifies that this function is for generating a URL to access files "in their cloud storage location" for external stages.

4. Snowflake Documentation: SQL Function Reference > GETABSOLUTEPATH. The documentation confirms this function "Retrieves the absolute path of a staged file," not a URL.

A Directory Table is a specialized, read-only table in Snowflake designed specifically to store a catalog of files located in a stage (either internal or external). This feature is central to managing and querying unstructured data, as it allows users to see metadata about the staged files (e.g., file name, size, last modified date) and generate URLs to access them directly. This enables seamless integration of unstructured data files with SQL queries without ingesting the file content into a standard table.

B. Transient: A transient table is a standard relational table that persists beyond a session but lacks a Fail-safe period. It is not specifically designed for unstructured data.
C. Temporary: A temporary table is a standard relational table that exists only for the duration of a user session. It is not the primary mechanism for managing unstructured data.
D. Permanent: A permanent table is the default relational table type in Snowflake. While it can store references (like URLs) to unstructured data, it is not the specialized object for cataloging and managing staged unstructured files like a Directory Table is.

1. Snowflake Documentation: Directory Tables

Section: Directory Tables

Content: "Directory tables store a catalog of staged files in cloud storage... You can query a directory table to retrieve the Snowflake-hosted file URL for each file in the stage. You can use the file URL to access the file..." This directly links Directory Tables to the management of staged files, which is how Snowflake handles unstructured data.

2. Snowflake Documentation: Working with Unstructured Data

Section: Working with Unstructured Data

Content: "To work with unstructured data, you can store the files in an internal or external stage, and create a directory table for the stage. A directory table provides a file catalog of the unstructured data files in the stage." This explicitly states that Directory Tables are the feature used to work with unstructured data.

3. Snowflake Documentation: Overview of Table Types

Section: Comparison of Table Types

Content: This page details the differences between Permanent, Transient, and Temporary tables based on their data lifecycle (Time Travel, Fail-safe) and scope (session vs. permanent). It makes no mention of any special capabilities for unstructured data for these table types, reinforcing that this is not their primary design purpose.

The ALLOWCLIENTMFACACHING is a user or account-level parameter that, when set to TRUE, enables supported clients to cache a user's successful Multi-Factor Authentication (MFA) token. According to official Snowflake documentation, this cached token remains valid for a default period of 4 hours. After this time expires, the user must re-authenticate with MFA on their next connection. This functionality balances security with user convenience by reducing the number of authentication prompts during a session.

A. 1 hour: This is an incorrect duration. The documented default caching period is explicitly set to 4 hours to reduce authentication friction.
B. 2 hours: This is not the standard validity period. Snowflake has defined a longer default time of 4 hours for the cached token.
D. 8 hours: This duration is incorrect. The default caching period is shorter than a full workday to maintain a stronger security posture.

1. Snowflake Inc. (n.d.). Multi-factor Authentication (MFA). Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/user-guide/security-mfa. In the "MFA Token Caching" section, it states: "When enabled, Snowflake caches the MFA token for a period of 4 hours."

2. Snowflake Inc. (n.d.). Parameters. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/sql-reference/parameters#allow-client-mfa-caching. The documentation for the ALLOWCLIENTMFACACHING parameter confirms its purpose is to cache the second-factor authentication for a period of time, with the specific duration detailed in the primary MFA guide.

Snowflake Time Travel is a core feature of Continuous Data Protection (CDP) that allows users to access historical versions of data at any point within a defined retention period (up to 90 days). This enables the maintenance of historical data by allowing users to perform queries on past data, restore tables to a previous state, and clone historical versions of tables. It is specifically designed for users to easily access and manipulate recent historical data without needing to restore from traditional backups.

A. Access control: This is a security feature that manages user permissions and privileges to access and modify objects, not a feature for maintaining historical data versions.
B. Fail-safe: This is a disaster recovery feature for data that is lost after the Time Travel retention period has ended. It is not directly accessible by users for maintenance.
C. Network policies: This is a security feature used to restrict access to a Snowflake account based on user IP addresses, which is unrelated to data history.

1. Snowflake Documentation - Understanding & Using Time Travel: "Snowflake Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period." This directly supports its use for maintaining and accessing historical data.

Source: Snowflake Inc. (2023). Understanding & Using Time Travel. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/user-guide/data-time-travel

2. Snowflake Documentation - Introduction to Continuous Data Protection: This document outlines the features of CDP, explicitly listing Time Travel and Fail-safe. It describes Time Travel as the mechanism for accessing recent historical data.

Source: Snowflake Inc. (2023). Introduction to Continuous Data Protection. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/user-guide/data-cdp

3. Snowflake Documentation - Understanding & Using Fail-safe: "Fail-safe is not intended as a means for accessing historical data... For accessing historical data, Snowflake provides Time Travel, which is a much more cost-effective and efficient solution."

Source: Snowflake Inc. (2023). Understanding & Using Fail-safe. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/user-guide/data-failsafe

4. Snowflake Documentation - Overview of Access Control: This page defines access control in terms of privileges, roles, and securable objects, confirming it is a security, not a data history, feature.

Source: Snowflake Inc. (2023). Overview of Access Control. Snowflake Documentation. Retrieved from https://docs.snowflake.com/en/user-guide/security-access-control-overview

Virtual Private Snowflake (VPS) provides the highest level of security by deploying a completely isolated Snowflake environment. Due to this isolation, sharing data with accounts outside of the VPS is disabled by default. To ensure security and proper network configuration, customers must contact Snowflake Support to enable data sharing between their VPS account and any non-VPS accounts. This process allows Snowflake to configure the necessary private connectivity for secure data exchange.

B. Business Critical: Data sharing is enabled by default in the Business Critical edition and does not require intervention from Snowflake Support to use.
C. Enterprise: Data sharing is a standard feature in the Enterprise edition, enabled by default for use between any Snowflake accounts.
D. Standard: The Standard edition includes Secure Data Sharing as a core feature, which is enabled by default without needing to contact support.

1. Snowflake Documentation: Virtual Private Snowflake (VPS), Section: "Data Sharing".

Quote: "By default, data sharing is not enabled for your VPS. To enable sharing data from your VPS account to a non-VPS account (or vice versa), please contact Snowflake Support."

2. Snowflake Documentation: Overview of Snowflake Editions.

Reference: The feature comparison table shows "Secure Data Sharing" is available for Standard, Enterprise, and Business Critical editions. The documentation for these editions does not mention a requirement to contact support for enablement, unlike the specific documentation for VPS.

A key characteristic of Snowflake materialized views is their ability to be defined by a query that joins multiple tables. This includes both inner and outer joins. This feature allows for the pre-computation of complex, frequently executed join operations, which significantly improves query performance by allowing the optimizer to use the pre-computed results instead of re-calculating the join for each query. The maintenance of the materialized view is handled automatically by Snowflake in the background.

A. Materialized views do not allow joins. This is incorrect. The Snowflake documentation explicitly states that the definition for a materialized view can include joins, including both inner and outer joins on multiple tables.
B. Clones of materialized views can be created directly by the user. This is incorrect. A materialized view is not a directly clonable object in Snowflake. While a materialized view will be cloned as part of a larger database or schema clone operation, a user cannot execute a CREATE MATERIALIZED VIEW ... CLONE command.
D. Aggregate functions can be used as window functions in materialized views. This is incorrect. Snowflake has specific limitations on the functions allowed in a materialized view's definition. Window functions, which use the OVER() clause, are explicitly not supported. This restriction includes aggregate functions used as window functions (e.g., SUM(c1) OVER (...)). ---

1. Snowflake Documentation: Working with Materialized Views > Limitations on Creating Materialized Views.

For Correct Answer C & Incorrect Answer A: The documentation states, "A materialized view can query only one table, or can perform an inner join or an outer join on multiple tables." This confirms that joins involving multiple tables are a supported characteristic.

For Incorrect Answer D: The same section lists unsupported query syntax, explicitly including: "Window functions (i.e. functions that use the OVER() clause), including aggregate functions used as window functions."

2. Snowflake Documentation: SQL Command Reference > CREATE