In Snowflake, when dealing with semi-structured data such as a JSON document stored in aVARIANTcolumn, the proper syntax to extract a value is to use the column name followed by the path to the specific element. Since arrays in JSON are zero-indexed, the first element is referenced with[0]. Therefore, to extract thenamefrom the first element of theelementsarray, the correct syntax isSource_column:elements[0] .name.

With reader accounts , the provider pays for:

Storage of shared data

Compute used by the reader account

The consumer does not pay , as the infrastructure is provisioned and managed by the provider.

The correct answer is C. Duo Security .

Snowflake provides integrated Multi-Factor Authentication through Duo Security . MFA adds an additional verification step beyond username and password authentication, improving account security.

Why C is correct:

Snowflake’s native MFA integration uses Duo Security. Users can enroll in MFA and authenticate using Duo-supported methods.

Why the other options are incorrect:

A. Microsoft Entra ID can be used as an identity provider for federated authentication, but it is not the MFA service provided natively by Snowflake.

B. Google Authenticator is a common authenticator app, but it is not Snowflake’s native MFA service.

D. Microsoft Authenticator is also a common MFA app, but Snowflake’s built-in MFA service is Duo Security.

Official Snowflake documentation reference:

Snowflake documentation describes Snowflake MFA as being powered by Duo Security.

The correct answers are C. Data is not transferred between accounts and D. Consumer account will incur compute costs .

Important correction:

The provided answer A, B is not correct.

Secure Data Sharing allows data providers to share selected database objects with consumer accounts without copying or transferring the underlying data. Consumers query the shared data using their own virtual warehouses, so the consumer generally pays for the compute used to query the share.

Why C is correct:

With Secure Data Sharing, the provider’s data remains in the provider account. Snowflake shares metadata and access, not a physical copy of the data.

Why D is correct:

Consumers use their own compute resources to query shared data. Therefore, the consumer account incurs compute costs when querying the share.

Why the other options are incorrect:

A. Regular, non-secure views generally cannot be shared in the standard secure sharing model. Views shared with consumers should be secure views, unless specific account-level settings allow non-secure objects. The general SnowPro Core concept is that secure views are used to share view logic safely.

B. Sensitive data is not automatically masked simply because data is shared. Masking policies or secure views must be designed and applied intentionally.

E. Reader accounts can query shared data but cannot edit the provider’s source data.

Official Snowflake documentation reference:

Snowflake documentation explains that Secure Data Sharing does not copy or move data between accounts. Consumers use their own warehouses to query shared data, while providers retain control of the shared objects.

Access History in Snowflake is a feature designed to support auditing by tracking access to data within Snowflake, including when a user ' s query reads column data. It provides detailed information on queries executed, including the user who ran the query, the query text, and the objects (e.g., tables, views) accessed by the query. This feature is instrumental for auditing purposes, helping organizations to monitor and audit data access for security and compliance.

The correct answers are A. Document AI and E. Cortex Analyst .

Snowflake Cortex is Snowflake’s integrated AI and machine learning capability set. It includes features for working with language models, document extraction, natural language analytics, and AI-powered application development.

Why A is correct:

Document AI is part of Snowflake Cortex. It is used to extract information from unstructured documents using AI.

Why E is correct:

Cortex Analyst is part of Snowflake Cortex. It enables natural language questions over structured data by using semantic models and Cortex capabilities.

Why the other options are incorrect:

B. Snowpipe is a data ingestion service, not part of the Cortex AI suite.

C. Streams are change data capture objects used to track table changes, not Cortex AI components.

D. Secure views are security/governance objects used to protect view definitions and support secure data sharing, not Cortex AI components.

Official Snowflake documentation reference:

Snowflake documentation describes Snowflake Cortex as a set of AI and ML capabilities and includes features such as Document AI and Cortex Analyst. Snowpipe, streams, and secure views belong to other Snowflake feature areas.

In addition to performing all the standard steps to share data, theUSAGEprivilege 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 theUSAGEprivilege on the database and schema to access the shared data. Thisprivilege 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. GrantingUSAGEprivilege on the database ensures that the receiving account can access the database in a read-only mode to utilize the shared view.

The correct answers are A. SELECT column:key_1 FROM my_table; and C. SELECT column[ ' key_1 ' ] FROM my_table; .

Snowflake supports path notation for querying semi-structured data stored in columns such as VARIANT, OBJECT, and ARRAY. For a JSON object key, Snowflake supports colon path notation and bracket notation.

Why A is correct:

The colon path notation accesses a field inside a semi-structured column.

Example:

SELECT column:key_1

FROM my_table;

This extracts the value associated with the key key_1.

Why C is correct:

Bracket notation also accesses an object element by key.

Example:

SELECT column[ ' key_1 ' ]

FROM my_table;

This is especially useful when key names require quoting or contain special characters.

Why the other options are incorrect:

B. Dot notation is supported for nested traversal after the initial colon path, such as column:key_1.subkey, but column.key_1 by itself is interpreted as regular SQL object/column qualification, not as extracting a key from a semi-structured column.

D. Parentheses are not valid JSON object key extraction syntax in Snowflake.

E. Casting a column to string and aliasing it does not extract the value for the key key_1.

Official Snowflake documentation reference:

Snowflake documentation explains that elements in semi-structured data can be accessed using colon path notation and bracket notation. Dot notation may also be used for nested paths after the semi-structured column reference.

When loading data into Snowflake, the file format options specified in theCOPY INTO < table > statement take the highest precedence over other locations such as the stage or table definitions. This ensures that any specific settings for a particular load operation are applied correctly.

File Format Hierarchy:

Stage Definition:Specifies default file format options for files staged in the location.

Table Definition:Can specify default file format options associated with the table.

COPY INTO Statement:Overrides both the stage and table definitions with the file format options specified directly in the statement.

Example Usage:

COPY INTO my_table

FROM @my_stage

FILE_FORMAT = (FORMAT_NAME = ' my_format ' FIELD_OPTIONALLY_ENCLOSED_BY = ' " ' );

The correct answer is C. To track how data has been transformed among columns .

Snowflake’s data lineage capability is used to understand how data flows and changes across objects. It helps users see relationships between source and target objects and understand how data is transformed, especially at the column level.

Corrected wording:

The original option said “records,” but Snowflake data lineage is more accurately described as tracking how data is transformed among columns, not individual row-level record transformations.

Why C is correct:

Data lineage helps track how data moves from source columns to target columns through operations such as views, CTAS statements, INSERT statements, and other transformations. This is useful for governance, impact analysis, auditing, and understanding downstream dependencies.

Why the other options are incorrect:

A. Tracking how records are read by a user is more closely related to access history or query history, not data lineage.

B. Tracking dependencies at the object level is related to object dependencies. Data lineage is more specific because it can show how data flows between columns and objects.

D. Snowflake data lineage is not primarily a row-level feature for tracking the impact of transformations on individual records.

Official Snowflake documentation reference:

Snowflake documentation describes data lineage as a governance capability that helps track how data moves and transforms across Snowflake objects, including column-level relationships.

The correct answers are A. Query pruning and E. Operations using Data Manipulation Language, or DML .

Snowflake automatically gathers and maintains metadata about micro-partitions. This metadata includes information such as the range of values in columns, distinct value counts, and other statistics. Snowflake uses this metadata to optimize query execution and table operations.

Why A is correct:

Query pruning is one of the most important uses of micro-partition metadata. Snowflake can skip micro-partitions that do not contain values needed by a query predicate.

Example:

SELECT *

FROM sales

WHERE sale_date = ' 2026-01-01 ' ;

If Snowflake knows from micro-partition metadata that certain micro-partitions do not contain sale_date = ' 2026-01-01 ' , those micro-partitions can be skipped.

Why E is correct:

DML operations such as UPDATE, DELETE, and MERGE can also benefit from micro-partition metadata. Snowflake can use metadata to identify which micro-partitions are affected by the operation instead of scanning unnecessary partitions.

Why the other options are incorrect:

B. The result cache returns previously computed query results. It avoids re-executing the query and therefore does not rely on micro-partition pruning in the same way.

C. Local disk cache stores data on warehouse compute resources after access, but it is not the main feature that uses micro-partition metadata.

D. “Remote disk cache” is not the standard Snowflake concept tested here. Snowflake has remote storage, warehouse cache, and result cache, but query pruning is the metadata-driven feature.

Official Snowflake documentation reference:

Snowflake documentation explains that micro-partition metadata is used for efficient query pruning and optimization. It also supports efficient table maintenance and DML operations by helping Snowflake identify relevant micro-partitions.

In Snowflake, the use of streams impacts how the data retention period for a table is handled, particularly in scenarios where the stream has not been consumed. The key point to understand is that Snowflake ' s streams are designed to capture data manipulation language (DML) changes such as INSERTS, UPDATES, and DELETES that occur on asource table. Streams maintain a record of these changes until they are consumed by a DML operation or a COPY command that references the stream.

When a stream is created on a table and remains unconsumed, Snowflake extends the data retention period of the table to ensure that the changes captured by the stream are preserved. This extension is specifically up to the point in time represented by the stream ' s offset, which effectively ensures that the data necessary for consuming the stream ' s contents is retained. This mechanism is in place to prevent data loss and ensure the integrity of the stream ' s data, facilitating accurate and reliable data processing and analysis based on the captured DML changes.

This behavior emphasizes the importance of managing streams and their consumption appropriately to balance between data retention needs and storage costs. It ' s also crucial to understand how this temporary extension of the data retention period impacts the overall management of data within Snowflake, including aspects related to data lifecycle, storage cost implications, and the planning of data consumption strategies.

To convert semi-structured data into rows and columns in Snowflake, theFLATTENfunction is utilized.

FLATTEN Function: This function takes semi-structured data (e.g., JSON) and transforms it into a relational table format by breaking down nested structures into individual rows. This process is essential for querying and analyzing semi-structured data using standard SQL operations.

Example Usage:

SELECT

f.value:attribute1 AS attribute1,

f.value:attribute2 AS attribute2

FROM

my_table,

LATERAL FLATTEN(input = > my_table.semi_structured_column) f;

Snowflake Documentation on FLATTEN

When unloading data using the COPY INTO < location > command, Snowflake offers options to compress files. The default behavior is governed by the COMPRESSION parameter. Setting COMPRESSION = AUTO allows Snowflake to automatically use its default compression algorithm, which is GZIP for most formats like CSV.