Free Snowflake SnowPro Core Certification (COF-C03) Sample Questions

Correct answers: B, D — It provides SQL syntax highlighting and autocompletion specific to Snowflake.; It enables running queries and viewing results directly within the VS Code interface.

• A. Incorrect. The Snowflake VS Code extension is a development tool focused on SQL editing, query execution, and object management. It does not have features for creating interactive dashboards. Dashboarding and data visualization are typically done in Snowsight or dedicated third-party BI tools.
• B. Correct. A key feature of the extension is its rich SQL editing support, which includes syntax highlighting, intelligent code completion (IntelliSense) for Snowflake-specific functions and keywords, and snippets. This significantly improves developer productivity and reduces errors.
• C. Incorrect. The VS Code extension does not manage or automatically optimize virtual warehouse configurations. Warehouse sizing, scaling policies, and other optimizations are configured by users through SQL commands or the Snowsight interface, not through the development extension.
• D. Correct. The extension allows developers to execute SQL queries and view the results in a new tab directly within the VS Code environment. This creates a seamless workflow, enabling developers to write, test, and iterate on their code without switching applications.
• E. Incorrect. This is a false statement. While the extension can be used to run `PUT` and `COPY` commands, Snowflake supports numerous data loading methods, including the Snowsight UI, the SnowSQL command-line client, Snowpipe for continuous loading, and a wide ecosystem of connectors and third-party ETL/ELT tools.

Correct answer: C — By using the built-in database explorer panel provided by the extension.

• A. Incorrect. While it's possible to run SnowSQL in the integrated terminal, this method would still require the developer to manually write SQL commands like `SHOW TABLES` or `DESCRIBE TABLE`, which contradicts the requirement to browse objects without writing commands.
• B. Incorrect. The `config.json` file is used for storing configuration and settings for the VS Code extension or workspace. It does not contain a live view or browser for database objects.
• C. Correct. The Snowflake Extension for VS Code includes a feature called the Database Explorer. This panel provides a graphical, tree-view interface that allows developers to connect to Snowflake and browse databases, schemas, tables, views, and columns visually, without writing any SQL `SHOW` commands.
• D. Incorrect. This functionality is a core feature of the Snowflake Extension for VS Code. The database explorer panel is specifically designed for this purpose, making object browsing intuitive and efficient.

Correct answer: C — Cortex Search

• A. Incorrect. The standard SQL `LIKE` operator is used for simple pattern matching within string columns in structured tables. It cannot index or search the content of unstructured files like PDFs and lacks the semantic understanding required to interpret a natural language query.
• B. Incorrect. Snowflake Cortex Analyst is a feature that allows users to ask analytical questions about their structured data in natural language to get insights and generate SQL. It is not designed to build a unified search application across both structured and unstructured data sources.
• C. Correct. Snowflake Cortex Search is a fully managed service specifically designed for this use case. It uses vector embeddings to enable semantic search across both structured data (tables) and unstructured data (like PDFs in a stage). This allows users to ask natural language questions and get relevant results from a unified index of all their data.
• D. Incorrect. The Snowsight search bar is a user interface element for navigating the Snowflake environment. It helps find Snowflake objects like databases, schemas, tables, and warehouses, but it does not perform content-based searches within the data itself.

Correct answer: B — Organization

• A. Incorrect. An Account is a primary container for Snowflake objects like databases, warehouses, and users, representing a single customer environment. However, it is not the highest-level object, as multiple accounts can be grouped under a single Organization.
• B. Correct. An Organization is the highest-level object in the Snowflake hierarchy. It is designed to group and manage multiple Snowflake accounts, providing a way to centralize billing, governance, and administration across an entire enterprise.
• C. Incorrect. A User is an identity or principal defined within an account for authentication and authorization purposes. Users operate inside an account and are not structural containers in the object hierarchy.
• D. Incorrect. A Database is a logical container for schemas, tables, views, and other data objects. Databases are contained within a single account and are much lower in the overall Snowflake object hierarchy.

Correct answer: B — To securely share database objects for reading with other Snowflake accounts.

• A. Incorrect. SHARE objects are used to share data, not compute resources. In Snowflake's data sharing model, the data provider shares the data, but the data consumer uses their own virtual warehouse (compute resources) to query that data.
• B. Correct. A SHARE object is the core component of Snowflake's Secure Data Sharing feature. It is used to grant other Snowflake accounts (consumers) secure, read-only access to specific database objects like tables, secure views, and UDFs without creating a physical copy of the data.
• C. Incorrect. A SHARE object provides live, read-only access to the original data; it does not create a copy of a table. This is a fundamental principle of Snowflake's Secure Data Sharing, which avoids data duplication and storage costs associated with traditional sharing methods.
• D. Incorrect. This describes the function of a FILE FORMAT object. FILE FORMAT objects are used to define the structure and formatting of data files for loading into or unloading from Snowflake stages. SHARE objects are exclusively for sharing database objects between accounts.

Correct answer: C — Time Travel

• A. Incorrect. The Result Cache is a performance optimization feature that stores the results of previously executed queries. It does not store historical versions of table data and cannot be used to undo DML operations like an accidental UPDATE.
• B. Incorrect. Zero-Copy Cloning creates an instant, metadata-only copy of a table. While it could be used to restore data if a clone was proactively created *before* the accidental UPDATE, it does not provide a mechanism to revert the original, modified table to a previous state.
• C. Correct. Time Travel is a core Snowflake feature, enabled by its immutable storage layer, that preserves the state of data before it is modified. It allows users to query or restore data from any point within a defined retention period (up to 90 days for Enterprise Edition). This feature is designed specifically for scenarios like recovering from accidental DML operations.
• D. Incorrect. Data Masking is a security feature used to obfuscate sensitive data in columns for specific roles or users at query time. It has no functionality related to data recovery or reversing data modifications.

Correct answer: B — Multiple virtual warehouses can access the same single copy of data simultaneously.

• A. Incorrect. This describes a shared-nothing architecture. In Snowflake's multi-cluster shared data architecture, data is stored centrally in a single storage layer. Virtual warehouses access this central copy and do not maintain their own persistent, synchronized copies of the data, although they do maintain a local data cache for performance.
• B. Correct. This is the fundamental principle of Snowflake's architecture. It decouples storage and compute, allowing multiple, independent virtual warehouses (compute clusters) to concurrently access the same single, centralized copy of data. This design enables workload isolation, elastic scaling, and high concurrency without data duplication.
• C. Incorrect. Data is isolated within a Snowflake account by default. Sharing data with other accounts is a powerful feature, but it is not automatic and must be explicitly configured by a data provider using Snowflake's Secure Data Sharing functionality.
• D. Incorrect. This describes a traditional, monolithic data warehouse architecture. Snowflake utilizes multiple, independent virtual warehouses that can be started, stopped, and scaled independently to serve different workloads and users, thus avoiding a single compute bottleneck and enabling concurrent, isolated processing.

Correct answer: B — It has more memory per node for memory-intensive workloads like ML training.

• A. Incorrect. Data ingestion performance is primarily related to COPY command configurations, file formats, and stage settings, rather than the type of virtual warehouse. Snowpark-optimized warehouses are not specifically designed to accelerate data loading.
• B. Correct. The primary and key benefit of a Snowpark-optimized warehouse is that it provides significantly more memory per node compared to a standard warehouse of the same size. This additional memory is crucial for memory-intensive operations common in machine learning (ML) model training and large-scale data transformations executed via Snowpark (Python, Java, or Scala).
• C. Incorrect. A Snowpark-optimized warehouse consumes credits at the same rate as a standard warehouse of the same T-shirt size. While it might complete a specific memory-intensive job faster, potentially reducing the total credits for that job, it does not have an inherently lower credit consumption rate.
• D. Incorrect. Snowpark-optimized warehouses are specifically designed for programmatic and memory-intensive workloads, particularly those using the Snowpark library, not for running general SQL queries from the Snowsight UI. Standard warehouses are typically used for UI-driven SQL analytics.

Correct answer: B — Downsize to a smaller warehouse (e.g., Medium) and configure it as a multi-cluster warehouse to handle the peak load.

• A. Incorrect. Increasing the AUTO_SUSPEND time would worsen the cost issue by keeping the expensive X-LARGE warehouse running longer during idle periods, leading to unnecessary credit consumption. This change does not address the core problem of query queuing during the peak period.
• B. Correct. This is the ideal solution for workloads with fluctuating concurrency. Downsizing to a smaller base warehouse reduces the baseline credit consumption during the long periods of underutilization. Configuring it as a multi-cluster warehouse allows it to automatically scale out by adding more clusters of the same size during the one-hour peak, handling the increased query load and eliminating queuing. This provides both cost-efficiency and performance elasticity.
• C. Incorrect. Creating multiple X-LARGE warehouses would significantly increase the overall credit consumption and resource footprint, exacerbating the cost problem. While it might isolate workloads, it is not a cost-effective strategy for managing a short, daily peak on a single workload.
• D. Incorrect. The scaling policy applies to multi-cluster warehouses. Switching a single, oversized warehouse's policy doesn't solve the fundamental issue. Furthermore, the Economy policy is designed to conserve credits by being slower to start new clusters, which would likely worsen query queuing during a sudden peak. The primary problem is the warehouse size, not the scaling policy.

Correct answers: A, B — A Time Travel retention period greater than 1 day; Fail-safe

• A. Correct. According to Snowflake documentation, transient tables support a Time Travel retention period of either 0 or 1 day. This is a significant difference from permanent tables, which can support up to 90 days of Time Travel (depending on the Snowflake edition). Therefore, a retention period greater than 1 day is not an available feature for transient tables.
• B. Correct. A primary characteristic of transient tables is that they do not have a Fail-safe period. Fail-safe is Snowflake's 7-day data recovery service for permanent tables after the Time Travel period has ended. The absence of Fail-safe is a key reason why transient tables incur lower storage costs.
• C. Incorrect. Zero-copy cloning is available for transient tables. A transient table can be cloned to create another transient table instantly. The main limitation is that a transient table cannot be cloned to create a permanent table.
• D. Incorrect. Standard Snowflake role-based access control (RBAC) applies to transient tables. You can grant and revoke privileges on transient tables just as you would with permanent tables. Unlike temporary tables, they are visible to all users who have the necessary permissions.
• E. Incorrect. The `COPY INTO <table>` command is a core data loading feature in Snowflake and is fully supported for all table types, including transient tables. There are no restrictions on using this command to load data into a transient table.

Correct answer: B — To allow developers to query and process data in Snowflake using familiar programming languages.

• A. Incorrect. Snowpark is a developer framework and API, not a graphical interface for building dashboards. Dashboarding and data visualization are typically done using Snowflake's UI (Snowsight) or third-party Business Intelligence (BI) tools.
• B. Correct. Snowpark's primary purpose is to provide APIs for languages like Python, Java, and Scala, allowing developers to query and process data using familiar programming constructs. This enables the creation of data pipelines, User-Defined Functions (UDFs), and applications that execute directly within Snowflake's engine, processing data where it resides without needing to move it.
• C. Incorrect. Managing user access, security roles, and authentication are fundamental administrative tasks in Snowflake handled through SQL commands and the platform's Identity and Access Management (IAM) features, not through the Snowpark framework.
• D. Incorrect. Virtual warehouses, which provide the compute resources for queries and Snowpark jobs, are created and managed via the Snowflake UI or SQL commands (e.g., CREATE WAREHOUSE). While Snowpark code execution relies on a virtual warehouse, Snowpark itself is not the tool used to manage these compute resources.

Correct answers: A, C, D — Permanent; Transient; Temporary

• A. Correct. Permanent tables are the default table type in Snowflake. They persist data until explicitly dropped and fully support both Time Travel and Fail-safe features. They are the most common type used for long-lived, business-critical data.
• B. Incorrect. 'Volatile' is not a recognized table type in Snowflake. While this term is used in some other database systems, Snowflake uses Temporary tables for session-scoped, non-persistent data.
• C. Correct. Transient tables are designed for data that needs to persist beyond a user session but does not require the high level of data protection offered by Permanent tables. They have a Time Travel period but do not have a Fail-safe period, which results in lower storage costs.
• D. Correct. Temporary tables exist only for the duration of the user session in which they were created and are automatically dropped when the session ends. They are not visible to other users or sessions and do not have Fail-safe or a configurable Time Travel period beyond one day, making them ideal for storing intermediate results during data processing.
• E. Incorrect. 'Archived' is not a native table type in Snowflake. While archiving data is a common data lifecycle management strategy, it is handled through other methods like moving data to external stages or using partner tools, not by defining a table with this specific type.

Correct answer: B — 1 credit

• A. Incorrect. 0.5 credits would be the correct amount if the warehouse consumed 1 credit per hour. However, a Small warehouse consumes 2 credits per hour. The calculation is (2 credits/hour) * (0.5 hours) = 1 credit.
• B. Correct. A standard Small virtual warehouse consumes 2 credits for a full hour of usage. Snowflake's billing for warehouse compute is prorated per-second with a 60-second minimum. Since the warehouse runs for 30 minutes (0.5 hours), which is well over the minimum, it will consume exactly half of its hourly rate: 2 credits/hour * 0.5 hours = 1 credit.
• C. Incorrect. 2 credits is the amount consumed for a full 60 minutes of continuous runtime for a Small warehouse. Since it only ran for 30 minutes, the consumption is half of this amount.
• D. Incorrect. 4 credits would correspond to a Small warehouse running for two full hours, or a Medium warehouse running for one full hour. The consumption for a Small warehouse running for 30 minutes is 1 credit.

Correct answer: A — Key-pair Authentication

• A. Correct. Key-pair authentication is specifically designed for secure, non-interactive, programmatic access to Snowflake. It uses a public/private key pair, where the private key is held securely by the client application and the public key is assigned to the Snowflake user. This method is more secure than password-based authentication because the private key is never transmitted and can be rotated easily, making it ideal for automated data pipelines.
• B. Incorrect. Multi-Factor Authentication (MFA) requires a second form of verification from the user, such as a code from an authenticator app. This interactive step makes it fundamentally unsuitable for fully automated, unattended processes like a data pipeline.
• C. Incorrect. Storing a password directly in source code is a major security vulnerability. It exposes the credential to anyone with access to the codebase, makes rotation difficult, and violates security best practices. While it would allow automation, it is not a secure method.
• D. Incorrect. Single Sign-On (SSO) is designed for interactive user authentication, typically involving a browser-based redirection to an Identity Provider (IdP). It is not suitable for headless, automated services like a data pipeline. Key-pair authentication is the standard and recommended method for this use case.

Correct answers: A, B, D — Warehouses; Tables; Users

• A. Correct. Warehouses are taggable objects in Snowflake. This allows for attaching metadata, such as cost center, environment (e.g., dev, prod), or department, which is useful for governance, tracking costs, and managing resource allocation.
• B. Correct. Tables, along with their columns, are fundamental objects that can be tagged in Snowflake. Tagging is commonly used to classify data at a granular level (e.g., PII, confidential, public) and to implement data governance policies such as dynamic data masking and row-access policies.
• C. Incorrect. Query results are transient and not persistent Snowflake objects. Therefore, they cannot be tagged. Tagging is reserved for persistent database objects that have a defined metadata lifecycle.
• D. Correct. Users are taggable objects in Snowflake. This enables attaching metadata like department, business unit, or project affiliation to user accounts, which is valuable for auditing, governance, and applying security policies.
• E. Incorrect. Time Travel is a data retention feature associated with tables, not a distinct, taggable object itself. Tags are applied to the table object, and this tagging implicitly covers the historical data accessible via Time Travel, but 'Time Travel data' itself cannot be separately tagged.

Correct answer: C — Ownership is transferred to the `ADMIN_ROLE`.

• A. Incorrect. Dropping a role does not drop the objects it owns. Snowflake's design prevents objects from being orphaned by automatically transferring ownership.
• B. Incorrect. Ownership is not transferred to `ACCOUNTADMIN` by default. It is transferred to the specific role that executed the `DROP ROLE` command, which was `ADMIN_ROLE` in this scenario.
• C. Correct. According to Snowflake documentation, if a role that owns objects is dropped, ownership of those objects is automatically transferred to the role that executed the `DROP ROLE` command. In this case, `ADMIN_ROLE` becomes the new owner.
• D. Incorrect. Objects in Snowflake cannot become ownerless. The system automatically transfers ownership to the role that dropped the previous owning role to ensure they can still be managed.

Correct answer: B — The movement of data from its source to its destination, including transformations.

• A. Incorrect. Tracking the total expense of executing queries, including compute and storage fees, is a function of cost monitoring and billing, not data lineage. Data lineage focuses on the provenance and transformation of data itself, rather than the economic metrics of its usage.
• B. Correct. Data lineage precisely documents the lifecycle of data, tracking its movement from its source to its destination, including all transformations. This visibility is crucial for auditing, impact analysis, debugging data pipelines, and ensuring regulatory compliance.
• C. Incorrect. Tracking the list of users who have been granted access to a dataset and their permissions is a matter of access control and security auditing, managed through roles and privileges. Data lineage is concerned with how the data flows and changes, not who has the rights to view or modify it.
• D. Incorrect. The replication status of a dataset, tracking its consistency and availability across regions, relates to data availability, redundancy, and disaster recovery strategies. Data lineage tracks the historical journey and transformation of data, not its physical replication state across different locations.

Correct answer: B — An External Function

• A. Incorrect. Stored procedures are blocks of SQL or procedural code (like JavaScript) that execute within the Snowflake environment. They do not require an API INTEGRATION object for their creation or invocation. While a stored procedure can be granted network access to call external APIs, this is a separate feature and does not use the API INTEGRATION object, which is specifically for External Functions.
• B. Correct. An External Function allows Snowflake to call custom code running on an external platform (e.g., AWS Lambda, Azure Functions). The API INTEGRATION object is a mandatory prerequisite for creating an external function. It acts as a secure proxy, storing essential information like the URL of the remote service (e.g., API Gateway endpoint), allowed network prefixes, and authentication credentials, enabling Snowflake to securely authorize and manage outbound calls.
• C. Incorrect. A Secure View is a database object used for data security and abstraction. It restricts data visibility based on user access privileges and encapsulates query logic entirely within Snowflake. It has no interaction with external services and therefore does not require an API INTEGRATION object.
• D. Incorrect. A Stream is a Snowflake object that provides Change Data Capture (CDC) capabilities by tracking changes (inserts, updates, deletes) made to a table. It is an internal object used for incremental data processing and does not involve calling external services, so it does not require an API INTEGRATION object.

Correct answer: C — The Snowflake Connector for Spark

• A. Incorrect. While a JDBC driver can establish a connection, it is not optimized for large-scale, parallel data transfers between Spark and Snowflake. It lacks Spark-specific integrations like predicate pushdown and can lead to performance bottlenecks, high memory overhead, and requires manual connection management, making it inefficient for large jobs.
• B. Incorrect. This describes the underlying mechanism that the Snowflake Connector for Spark uses, but performing it as a manual, multi-step process is inefficient. It requires extra orchestration to manage the export to an S3 stage and then execute the COPY command, adding latency and complexity compared to the dedicated connector which automates this entire workflow.
• C. Correct. The Snowflake Connector for Spark is the purpose-built solution for this use case. It provides a high-performance, parallel data source and sink for Spark DataFrames. It integrates directly with Spark's APIs and optimizes data transfer by using Snowflake's internal stages and parallel COPY/UNLOAD commands under the hood. It also supports performance-enhancing features like predicate and query pushdown, making it the most efficient and scalable option.
• D. Incorrect. The Snowflake Connector for Kafka is designed specifically for ingesting streaming data from Apache Kafka topics into Snowflake tables. It is not intended for use with Apache Spark batch or streaming jobs and does not integrate with the Spark DataFrame API.

Correct answer: C — SKIP_FILE

• A. Incorrect. `ABORT_STATEMENT` is the default behavior. It stops the entire load operation upon encountering the first error in any file, which is the opposite of the desired outcome.
• B. Incorrect. The `CONTINUE` option is used to skip individual erroneous rows within a file and continue loading the valid rows from that same file. It does not skip an entire file if the file itself is corrupted and cannot be parsed.
• C. Correct. The `SKIP_FILE` option instructs the COPY command to skip the current file entirely if any error is found within it and move on to the next file. This perfectly matches the requirement to bypass a single corrupted file and load the remaining valid ones.
• D. Incorrect. `ON_ERROR = 'SKIP_CORRUPTED'` is not a valid syntax for the ON_ERROR copy option in Snowflake. The correct value to achieve the desired behavior is `SKIP_FILE`.

Correct answer: B — To continuously load small volumes of data as soon as it's available in a stage.

• A. Incorrect. Snowpipe is a data ingestion service, not a query execution engine. Complex analytical queries are handled by Snowflake's virtual warehouses and query processing engine.
• B. Correct. Snowpipe is specifically designed for continuous, near real-time data ingestion. It automates the process of loading small volumes of data (micro-batches) as soon as new files are detected in an external or internal stage, making it ideal for event-driven data pipelines.
• C. Incorrect. Snowpipe's function is to load data into Snowflake tables. Unloading data from Snowflake to an external stage is accomplished using the `COPY INTO <location>` command, not Snowpipe.
• D. Incorrect. The scheduling of recurring SQL statements or tasks is the specific purpose of Snowflake Tasks. Snowpipe is focused on continuous data ingestion triggered by file arrival, not on a fixed schedule.

Correct answer: B — PATTERN

• A. Incorrect. The `FILES` copy option is used to specify an explicit list of one or more file names to be loaded. It does not accept a regular expression for pattern matching.
• B. Correct. The `PATTERN` copy option is used to specify a POSIX-style regular expression that filters the set of files to be loaded from a stage. For example, `PATTERN='.*data_2024.*.csv.gz'` would load all gzipped CSV files from the stage path that contain 'data_2024' in their names.
• C. Incorrect. While the functionality involves using a regular expression, the keyword for the copy option in a `COPY INTO <table>` command is `PATTERN`, not `REGEX`.
• D. Incorrect. `FILTER` is not a valid copy option for the `COPY INTO <table>` command to filter files based on their names. The correct option is `PATTERN`.

Correct answers: B, D — A `GROUP BY` operation on a column with very high cardinality.; A window function with a very large partition.

• A. This is incorrect. Data spilling occurs precisely when the data being processed by an operation *exceeds* the available memory of a warehouse node. If the data fits entirely within memory, there is no need to spill to storage.
• B. This is correct. A `GROUP BY` operation on a high-cardinality column requires creating and maintaining a state for each unique group. If there are millions or billions of unique values, the memory required to hold this aggregation state can easily exceed the available RAM, forcing Snowflake to spill intermediate results to local storage.
• C. This is incorrect. Processing a very small table requires minimal memory and does not generate large intermediate data structures. It is highly unlikely to cause memory pressure that would lead to data spilling.
• D. This is correct. Window functions operate on partitions of data. If a single partition is very large, Snowflake may need to hold all the rows for that partition in memory simultaneously to perform the calculation (e.g., sorting). This can exhaust available memory and cause a spill to storage.
• E. This is incorrect. The result cache is a performance feature that bypasses query execution entirely by returning a previously computed result set. Since the query does not execute, it consumes no warehouse resources and therefore cannot cause data spilling. It is a mechanism to avoid resource usage, not a cause of it.

Correct answer: B — FLATTEN()

• A. Incorrect. The PARSE_JSON() function is used to convert a JSON-formatted string into a VARIANT data type. In this scenario, the data is already in a VARIANT column, so this function is not needed. More importantly, it does not perform the required transformation of exploding array elements into separate rows.
• B. Correct. The FLATTEN() function is a table function specifically designed to expand semi-structured data, such as an array within a VARIANT, into multiple rows. It produces one row for each element in the array, which is exactly the transformation required to get each user tag on a separate row.
• C. Incorrect. The SPLIT() function operates on a string, dividing it into an array of substrings based on a specified delimiter. It does not operate on an existing array within a VARIANT column and does not produce separate rows.
• D. Incorrect. UNPIVOT is a relational operator that transforms columns into rows within a traditional table structure. It is not designed to work with semi-structured data types like arrays stored within a VARIANT column.

Correct answer: B — Yes, by using the `ALTER SESSION SET USE_CACHED_RESULT = FALSE;` command.

• A. Incorrect. The use of the Query Result Cache is not strictly a global-only setting. It can be controlled and disabled at the session level by individual users.
• B. Correct. A user can disable the use of the Query Result Cache for their current session by executing the `ALTER SESSION SET USE_CACHED_RESULT = FALSE;` command. This is a session-level parameter that instructs Snowflake not to return cached results for any subsequent queries within that specific session.
• C. Incorrect. Any user can disable the result cache for their own session. The ACCOUNTADMIN role is not required for this action. While an ACCOUNTADMIN can change account-level defaults for this parameter, they are not required for session-level overrides.
• D. Incorrect. Although caching is a core and fundamental part of Snowflake's performance architecture, its use is not mandatory and can be disabled for a specific session using the `USE_CACHED_RESULT` session parameter.

Correct answer: C — Define a clustering key on the `TRANSACTION_DATE` column.

• A. Incorrect. A materialized view is designed to pre-compute the results of complex queries, particularly those involving aggregations or joins. While it can improve performance for queries that match its definition, creating a view that simply aggregates all sales data does not directly address the need to reduce data scanned from the base table for arbitrary date-range filters. It wouldn't improve the micro-partition pruning on the base table.
• B. Incorrect. The Search Optimization Service is a performance feature that accelerates highly selective point lookup queries (e.g., `WHERE id = 'value'`) and certain substring or text searches. It is not designed or optimized for range-based queries, such as filtering on a `TRANSACTION_DATE` range, and therefore would not be the best solution to reduce data scanned in this scenario.
• C. Correct. Defining a clustering key on the `TRANSACTION_DATE` column co-locates rows with similar dates into the same or adjacent micro-partitions. This physical organization of data allows Snowflake's query optimizer to perform effective micro-partition pruning. When a query filters by `TRANSACTION_DATE`, Snowflake can use the metadata to quickly identify and scan only the relevant micro-partitions, significantly reducing the total amount of data scanned and improving query performance.
• D. Incorrect. The Query Acceleration Service (QAS) improves the performance of large, complex queries by providing additional, serverless compute resources to offload portions of the query workload. QAS speeds up the query *execution* time but does not reduce the amount of data that needs to be *scanned* from the storage layer. The primary goal stated in the question is to reduce the volume of data scanned.

Correct answers: A, D — `SYSTEM$CLUSTERING_DEPTH`; `SYSTEM$CLUSTERING_INFORMATION`

• A. Correct. According to Snowflake documentation, the `SYSTEM$CLUSTERING_DEPTH` function specifically computes and returns the average clustering depth for a table. A smaller average depth indicates better clustering, as fewer micro-partitions need to be scanned for queries.
• B. Incorrect. The `GET_DDL` function is a utility that returns the DDL statement required to recreate a specified object, such as a table. It provides metadata about the table's structure, not performance or health metrics like clustering depth.
• C. Incorrect. The `TABLE_STORAGE_METRICS` view in the Information Schema provides details about the storage usage of tables, including active bytes, time-travel bytes, and failsafe bytes. It does not provide metrics related to clustering performance or health.
• D. Correct. The `SYSTEM$CLUSTERING_INFORMATION` function returns a comprehensive JSON object with detailed clustering metrics. This object includes the `average_depth` key, which represents the clustering depth, as well as other useful metrics like `average_overlaps` and a `partition_depth_histogram`.

Correct answer: C — It reduces the amount of data scanned from storage, improving performance.

• A. Incorrect. Query pruning's main purpose is to optimize data scanning for individual queries, not to manage compute infrastructure. While more efficient queries can lead to lower overall compute consumption, pruning does not directly reduce the number of virtual warehouses needed, which is a separate administrative decision.
• B. Incorrect. Query pruning and the result cache are two distinct and independent performance features in Snowflake. Pruning minimizes the data read from storage based on query predicates, while the result cache stores the final results of previously executed queries to avoid re-computation. Pruning has no effect on the size or operation of the result cache.
• C. Correct. The primary and most direct benefit of query pruning is that it significantly reduces the amount of data that needs to be scanned from storage. By leveraging metadata stored for micro-partitions, Snowflake's optimizer can determine which partitions do not contain relevant data for a query's filters and skip reading them entirely. This reduction in I/O is the core mechanism through which pruning improves query performance and reduces compute costs.
• D. Incorrect. Query pruning is a data access optimization technique, not a code modification one. It involves eliminating unnecessary micro-partitions from a table scan. While Snowflake's optimizer does perform various query rewrites to improve efficiency, this is a separate and distinct optimization mechanism from pruning.

Correct answer: B — To reduce network latency by storing data closer to the compute resources.

• A. Incorrect. This describes the Query Result Cache, which stores the results of previously executed queries in the Cloud Services Layer for up to 24 hours (extendable to 30 days with reuse). The Warehouse Cache stores raw data micro-partitions, not aggregated results.
• B. Correct. The Warehouse Cache (Local Disk Cache) stores micro-partitions on the local SSDs of the virtual warehouse nodes. This reduces the need to fetch data from remote cloud storage, thereby lowering network latency and improving query performance for subsequent queries accessing the same data.
• C. Incorrect. Metadata caching is handled by the Metadata Cache in the Cloud Services Layer, which stores table schemas, statistics, and other metadata to accelerate query planning and pruning. The Warehouse Cache stores actual data, not metadata.
• D. Incorrect. Role and permission information is managed by Snowflake's access control system and is not stored in the Warehouse Cache. The Warehouse Cache is solely for caching data micro-partitions to speed up query execution.

Correct answers: A, B, D — Public listings are discoverable by any Snowflake user in the Marketplace.; Private listings can only be accessed by accounts specified by the provider.; Private listings are used for sharing within a Snowflake organization or with specific partners.

• A. Correct. Public listings are designed for broad visibility. They are searchable and discoverable by any consumer account within the Snowflake Marketplace, making them suitable for mass distribution of data products.
• B. Correct. Private listings provide a mechanism for controlled access. The provider explicitly specifies which consumer accounts can discover and access the listing, making it invisible to all other Snowflake accounts. This is also referred to as sharing with specific consumers.
• C. Incorrect. Both public and private listings support various pricing models, including free, trial-based, and paid offerings. The monetization strategy is independent of the listing's visibility type (public vs. private).
• D. Correct. This describes a primary use case for private listings. They are the ideal mechanism for sharing data, data services, or Native Apps with a select group of consumers, such as specific business partners, customers, or other accounts within the same organization.
• E. Incorrect. Snowflake Native Apps can be distributed through both public listings for broad access and private listings for targeted sharing, such as with beta testers or specific enterprise customers.

Correct answer: C — The `AT` or `BEFORE` clause.

• A. Incorrect. Snowflake does not have a `HISTORY()` clause for querying the historical state of table data. While Snowflake provides access to historical metadata through functions like `QUERY_HISTORY` or views in the `INFORMATION_SCHEMA`, these are used for auditing and performance analysis, not for retrieving the data within a table as it existed at a past point in time.
• B. Incorrect. The `AS OF YESTERDAY` syntax is not a valid SQL clause in Snowflake. To query historical data, Snowflake's Time Travel feature requires the use of the `AT` or `BEFORE` clauses with a specific timestamp, offset, or statement ID.
• C. Correct. The `AT` and `BEFORE` clauses are the core components of Snowflake's Time Travel feature. They allow users to query the state of a table at a specific point in the past without restoring a backup or creating a clone. The analyst could use a query like `SELECT * FROM INVENTORY AT (TIMESTAMP => 'YYYY-MM-DD 23:59:59'::timestamp_ltz)` to see the table's data at the end of the previous day.
• D. Incorrect. Snowflake SQL does not use a `VERSION()` clause to query historical table data. The correct and supported syntax for accessing past data states is through the `AT` or `BEFORE` clauses as part of Snowflake's Time Travel functionality.

Correct answers: B, D — The number of days configured for the data retention period.; The amount of data that is changed or deleted in the tables (data churn).

• A. Incorrect. The number of users querying historical data impacts compute costs, as queries are processed by virtual warehouses. It does not affect the amount of storage consumed by Time Travel, which is based on the volume of historical data retained, not query activity.
• B. Correct. The data retention period is a primary factor in Time Travel storage costs. A longer retention period (e.g., 90 days for Enterprise Edition) means Snowflake keeps historical data versions for a longer time, which directly increases the amount of stored historical data and the associated costs.
• C. Incorrect. The size of a virtual warehouse affects compute costs and query performance. It is completely independent of storage costs, which are billed separately. Time Travel storage is determined by the volume of data, not the compute resources used to query it.
• D. Correct. The volume of data that is changed or deleted, often referred to as 'data churn', directly impacts Time Travel storage. Every UPDATE or DELETE operation causes Snowflake to retain the previous state of the data in new micro-partitions. High data churn results in more historical data versions being stored, thus increasing storage costs.
• E. Incorrect. Creating a clone is a zero-copy operation and does not immediately increase storage. While modifications to the clone or the source table after cloning will generate new micro-partitions and accrue storage costs, the mere number of clones created is not a direct factor influencing the Time Travel storage costs of the original table.

Correct answers: B, C — The data can be joined and analyzed based on a common identifier.; The raw data from each party remains in their own account and is not exposed to the other.

• A. Incorrect. The fundamental purpose of a Data Clean Room is to prevent parties from seeing each other's raw, row-level data. The goal is to enable joint analysis and derive aggregate insights without exposing sensitive or personally identifiable information, thus preserving data privacy and control.
• B. Correct. A primary function of a Data Clean Room is to allow different parties to join their respective datasets on a common identifier (e.g., an email address, user ID). This enables collaborative analysis to identify overlaps and insights while using privacy-enhancing techniques to prevent exposure of the underlying raw data.
• C. Correct. In the Snowflake Data Clean Room model, data does not move or get copied. Each party's raw data remains securely within their own Snowflake account. Access for analysis is provided through secure data sharing, often using secure views or secure user-defined functions (UDFs) that enforce specific privacy rules and prevent direct exposure of the source data.
• D. Incorrect. While using a neutral third-party account is one possible architectural pattern for a Data Clean Room, it is not a requirement. Queries can be initiated by any of the participating accounts, depending on the specific governance and trust model agreed upon by the parties.
• E. Incorrect. Data Clean Rooms in Snowflake leverage the platform's native secure data sharing capabilities, which operate on a zero-copy principle. This means data is not copied or moved into a shared account; instead, it is shared live from the provider's account, ensuring data providers retain full control and security over their data.

Correct answer: B — SELECT * FROM PROD_SHARE_DB.PUBLIC.CUSTOMERS;

• A. This query is incorrect because it uses an unqualified table name. It would only succeed if the session's current context (database and schema) was already set to `PROD_SHARE_DB` and `PUBLIC` respectively. Without that context, Snowflake cannot locate the `CUSTOMERS` table. A fully-qualified name is the most reliable and explicit way to reference an object.
• B. This query is correct. It uses a fully-qualified three-part identifier (`database.schema.table`), which explicitly tells Snowflake where to find the `CUSTOMERS` table. When a consumer creates a database from a share, they query it just like any other database in their account using the name they assigned (`PROD_SHARE_DB` in this case).
• C. This query is incorrect. Snowflake does not use a four-part identifier that includes the provider's account name for querying shared data. Once a consumer creates a database from a share, that database becomes part of their own account's namespace and is referenced using its local name (`PROD_SHARE_DB`).
• D. This query is incorrect because it is syntactically invalid. The standard SQL keyword to retrieve data from a table in Snowflake is `SELECT`, not `QUERY`.

Correct answer: B — The user's session lacks an active, running virtual warehouse selection.

• A. Incorrect. Marketplace data access is not restricted to the SYSADMIN role by default; any role with the necessary privileges, including ACCOUNTADMIN, can query the data. The error message specifically indicates a missing warehouse, not a role-based permission issue.
• B. Correct. The error "user does not have a warehouse selected" directly means the user's session lacks an active, running virtual warehouse selection. In Snowflake, all queries require a virtual warehouse to execute, regardless of the user's role, including ACCOUNTADMIN.
• C. Incorrect. If the provider had not granted permission, the error would relate to object existence or authorization, not a missing warehouse. The given error is specific to the absence of a compute resource in the session.
• D. Incorrect. Marketplace data is shared via Secure Data Sharing and is available immediately upon acquisition; it is not copied into the account. The error points to a missing warehouse, not a data availability or replication delay.