The correct answers are A. JSON null values and E. Timestamps within strings .

Important correction:

The provided answer A, B is not the best answer. Snowflake documentation specifically calls out that certain values in semi-structured data may not be extracted into separate columnar storage, which can result in slower queries and increased storage usage. Two commonly tested examples are JSON null values and date or timestamp values stored as strings.

Why A is correct:

When a semi-structured element contains JSON null values, Snowflake might not extract that element into a separate column. This can reduce pruning efficiency and require Snowflake to scan more of the VARIANT structure during query execution.

Why E is correct:

Date and timestamp values inside semi-structured data that are stored as strings may not be optimized in the same way as native typed values. Storing timestamps as strings can increase storage and slow query performance because Snowflake may need to parse or cast the values at query time.

Why the other options are incorrect:

B. Floating-point numbers are supported inside VARIANT; they are not the main documented issue in this context.

C. Arrays are supported in semi-structured data. While very complex nested structures can affect query patterns, arrays alone are not the best answer here.

D. Vectors are a Snowflake data type used for vector operations and are not the standard answer for this semi-structured data optimization question.

Official Snowflake documentation reference:

Snowflake documentation explains that for semi-structured data, elements containing JSON null values may not be extracted into separate columns. It also recommends storing date and timestamp values in native typed columns when possible rather than as strings inside VARIANT, to improve query performance and storage efficiency.

The COPY_HISTORY table function in Snowflake is used to track data loading activities, including both manual COPY INTO < table > commands and automated data loading through Snowpipe. This function provides visibility into load operations, making it valuable for monitoring data ingestion, ensuring data integrity, and troubleshooting loading processes.

The correct answer is A. Set the warehouse scaling policy to ECONOMY .

A multi-cluster warehouse can use Auto-scale mode to add or remove clusters based on query concurrency. To help control credit consumption, Snowflake provides an Economy scaling policy.

Why A is correct:

The ECONOMY scaling policy is more conservative than the STANDARD scaling policy. It starts additional clusters only when Snowflake estimates there is enough query load to keep the new cluster busy for a meaningful amount of time. This helps reduce unnecessary cluster starts and can lower credit usage.

Why the other options are incorrect:

B. Setting MIN_CLUSTER_COUNT = 1 can help avoid running multiple clusters constantly, but it does not specifically control Auto-scale behavior as effectively as the Economy scaling policy.

C. Setting AUTO_SUSPEND = 0 disables automatic suspension, which can increase credit consumption.

D. Setting both minimum and maximum cluster count to 2 forces two clusters and removes Auto-scale flexibility. This can increase cost and does not control consumption efficiently.

Official Snowflake documentation reference:

Snowflake documentation explains that the Economy scaling policy conserves credits by starting additional clusters only when enough query load exists to justify them.

Access History in Snowflake is primarily utilized for compliance auditing. The Access History feature provides detailed logs that track data access and modifications, includingqueries that read from or write to database objects. This information is crucial for organizations to meet regulatory requirements and to perform audits related to data access and usage.

Role of Access History:Access History logs are designed to help organizations understand who accessed what data and when. This is particularly important for compliance with various regulations that require detailed auditing capabilities.

How Access History Supports Compliance Auditing:

By providing a detailed log of access events, organizations can trace data access patterns, identify unauthorized access, and ensure that data handling complies with relevant data protection laws and regulations.

Access History can be queried to extract specific events, users, time frames, and accessed objects, making it an invaluable tool for compliance officers and auditors.

Direct Shares allow secure data sharing between Snowflake accounts in the same region . No data is copied; Snowflake provides access to the original data using metadata and access control.

The ACCESS_HISTORY view in Snowflake provides detailed information about queries executed in the account, including metadata such as the time of execution, the user, and the SQL text of the queries. This view is available in the Snowflake Enterprise edition and higher editions. The Standard edition does not include this feature.

Snowflake Documentation: Access History

Snowflake Editions: Snowflake Pricing

To optimize parallelism and maximize performance, large files should be split into multiple smaller files (ideally 100–250 MB compressed) before loading. This allows Snowflake to load files in parallel using multiple threads or clusters.

In Snowflake, theCOPY INTO < location > command is used to unload (export) data from a Snowflake table to an external stage, such as an S3 bucket, Azure Blob Storage, or Google Cloud Storage. This command allows users to specify the format, file size, and other options for the data being unloaded, making it a flexible solution for exporting data from Snowflake to external storage solutions for further use or analysis.

In Snowflake, overloading refers to the creation of multiple User-Defined Functions (UDFs) with the same name but differing in the number or types of their arguments. This feature allows for more flexible function usage, as Snowflake can differentiate between functions based on the context of their invocation, such as the types or the number of arguments passed. Overloading helps to create more adaptable and readable code, as the same function name can be used for similar operations on different types of data.

Network policies in Snowflake are defined as a set of rules that manage the network-level access to Snowflake accounts. These rules specify which IP addresses or IP ranges are permitted to connect to Snowflake, enhancing the security of Snowflake accounts by preventing unauthorized access. Network policies are an essential aspect of Snowflake ' s security model, allowing administrators to enforce access controls based on network locations.

Object Name Resolution: When unqualified object names (e.g., table name without schema) are used in a UDF, Snowflake follows a specific hierarchy to resolve them. Here ' s the order:

Current Schema: Snowflake first checks if an object with the given name exists in the schema currently in use for the session.

PUBLIC Schema: If the object isn ' t found in the current schema, Snowflake looks in the PUBLIC schema of the current database.

Note: TheSEARCH_PATHparameter influences object resolution for queries, not within UDFs.

Snowflake ' s architecture is unique in that it combines elements of both traditional shared-disk and shared-nothing database architectures. This hybrid approach allows Snowflake to offer the scalability and performance benefits of a shared-nothing architecture (with compute and storage separated) while maintaining the simplicity and flexibility of a shared-disk architecture in managing data across all nodes in the system. This results in an architecture that provides on-demand scalability, both vertically and horizontally, without sacrificing performance or data cohesion.

To extract the URL of a directory table on an external stage for further transformation in Snowflake, the GET_ABSOLUTE_PATH function can be used. This function returns the full path of a file or directory within a specified stage, enabling users to dynamically construct URLs for accessing or processing data stored in external stages.

Query pruning in Snowflake refers to the optimization technique where the system reduces the amount of data scanned by a query based on the query conditions. This typically involves skipping unnecessary data partitions that do not contribute to the query result. The effectiveness of this technique can be observed through:

Option A: Partitions scanned. This statistic indicates how many data partitions were actually scanned as a result of query pruning, showing the optimization in action.

Option C: Bytes scanned. This measures the volume of data physically read during query execution, and a reduction in this number indicates effective query pruning, as fewer bytes are read when unnecessary partitions are skipped.

Options B, D, and E do not directly relate to observing the effects of query pruning. " Partitions total " shows the total available, not the impact of pruning, while " Bytes read from result " and " Bytes written " relate to output rather than the efficiency of data scanning.

When creating a CSV file format in Snowflake and the requirement is to wrap columns by single quotes or double quotes, theFIELD_OPTIONALLY_ENCLOSED_BYparameter should be used in the file format specification. This parameter allows you to define a character (either a single quote or a double quote) that can optionally enclose each field in the CSV file, providing flexibility in handling fields that contain special characters or delimiters as part of their data.