View Full Snowflake SnowPro Advanced Data Engineer Exam Dumps and Practice Test Dumps

 

Question 101

Which feature creates an independent copy of a table without immediate full data duplication?

1. Materialized view
2. Temporary stage
3. Zero-copy clone
4. Secure view

Correct Answer: 3

Explanation:

Snowflake zero-copy cloning creates a new database object that initially references the existing micro-partition data rather than making a complete physical copy. This makes cloning useful for development, testing, experimentation, and other environments that need an isolated object quickly. As the source and clone change independently, additional storage can be consumed by newly created or modified micro-partitions. Data engineers can therefore use cloning to create practical development environments while avoiding the immediate storage cost of duplicating an entire dataset.

Question 102

Which option distinguishes SQL NULL from JSON null in VARIANT data?

1. IS_NULL_VALUE
2. NULL_CHECK
3. JSON_NULL_TEST
4. VARIANT_NULL

Correct Answer: 1

Explanation:

IS_NULL_VALUE helps determine whether a VARIANT expression contains a JSON null value. This distinction is important because SQL NULL and JSON null are different concepts in Snowflake’s semi-structured data model. A SQL NULL represents the absence of a SQL value, while JSON null is an explicit null value within JSON content. Data engineers working with VARIANT columns should account for this distinction when filtering, transforming, and validating semi-structured records. Correct null handling prevents unexpected results in analytical transformations and downstream data-quality checks.

Question 103

Which function converts a JSON string into a VARIANT value?

1. TO_VARIANT
2. PARSE_JSON
3. JSON_CONVERT
4. CAST_JSON

Correct Answer: 2

Explanation:

PARSE_JSON converts a string containing valid JSON into a Snowflake VARIANT value. This allows structured JSON content to be queried using Snowflake’s semi-structured data capabilities. Once parsed, engineers can access nested objects and arrays using path expressions or functions designed for VARIANT data. This is different from simply storing the JSON as plain text because the parsed representation can be navigated and processed as semi-structured data. Proper validation is still important because malformed JSON input can cause parsing failures.

Question 104

Which function returns the number of elements in an array?

1. OBJECT_SIZE
2. ARRAY_LENGTH
3. ARRAY_SIZE
4. ELEMENT_COUNT

Correct Answer: 3

Explanation:

ARRAY_SIZE returns the number of elements in a Snowflake array. It is useful when processing semi-structured data stored in VARIANT values where arrays can contain variable numbers of elements. Engineers can use the function in transformations, validation rules, and conditional logic to determine whether an array contains expected content. When combined with other semi-structured functions, it can help build robust pipelines for nested data. Understanding array-specific functions is important when transforming JSON payloads that contain repeated elements or collections.

Question 105

Which function reveals the Snowflake data type of an expression?

1. TYPEOF
2. DATA_TYPE_OF
3. VALUE_TYPE
4. EXPRESSION_KIND

Correct Answer: 1

Explanation:

TYPEOF returns a string describing the data type of an expression. It is especially useful when working with VARIANT values because semi-structured data can contain different underlying types within the same column. Engineers can use TYPEOF during profiling, validation, and transformation logic to determine whether a value represents an object, array, string, number, Boolean, or another supported type. This can make ingestion pipelines more resilient when source systems produce heterogeneous JSON structures that require different processing paths.

Question 106

Which function aggregates values into a Snowflake array?

1. ARRAY_BUILD
2. ARRAY_AGG
3. COLLECT_ARRAY_VALUES
4. GROUP_ARRAY

Correct Answer: 2

Explanation:

ARRAY_AGG aggregates values from multiple rows into an array. It is useful when a transformation needs to represent related values as a collection rather than separate records. For example, a data engineer can group transaction attributes or related identifiers into an array associated with a parent entity. The resulting array can then be stored or further processed as semi-structured data. Ordering requirements should be considered when deterministic element order is important, because aggregation behavior should not be assumed to provide an arbitrary order unless ordering is explicitly defined.

Question 107

Which function builds an object from key-value pairs?

1. OBJECT_CONSTRUCT
2. OBJECT_CREATE
3. JSON_OBJECT_BUILD
4. KEY_VALUE_OBJECT

Correct Answer: 1

Explanation:

OBJECT_CONSTRUCT creates an OBJECT value from supplied key-value pairs. It is useful for dynamically constructing semi-structured records during transformations. Data engineers can use it to combine selected relational columns into a JSON-like object, making it convenient to produce nested or flexible output structures. The function is particularly valuable when preparing data for downstream systems that consume semi-structured payloads. Engineers should understand how NULL values and keys are handled so that generated objects contain the intended attributes and do not unexpectedly omit information.

Question 108

What does TARGET_LAG control for a dynamic table?

1. Storage retention
2. Desired freshness
3. Query timeout
4. File expiration

Correct Answer: 2

Explanation:

TARGET_LAG specifies the desired freshness of a dynamic table relative to its upstream data. Snowflake uses this target when determining when the dynamic table should refresh. A shorter target generally represents a stronger freshness requirement, while a longer target permits more delay between upstream changes and refreshed results. Data engineers should select a target appropriate to business requirements rather than automatically choosing the smallest possible value. Refresh frequency, dependencies, workload characteristics, and compute consumption should all be considered when designing dynamic-table pipelines.

Question 109

Which dynamic table setting allows refresh timing to follow downstream demand?

1. TARGET_LAG = AUTO
2. TARGET_LAG = DOWNSTREAM
3. REFRESH = CASCADE
4. DEPENDENCY = FOLLOW

Correct Answer: 2

Explanation:

TARGET_LAG = DOWNSTREAM allows a dynamic table’s refresh behavior to be driven by the freshness requirements of downstream dynamic tables that depend on it. This can be useful when building chains of dynamic tables because upstream objects do not necessarily need independent freshness targets. Instead, the dependency relationship can help coordinate refresh requirements through the pipeline. This design can reduce unnecessary refresh activity when intermediate results do not need to be refreshed more frequently than the downstream consumers require.

Question 110

Which refresh mode can process a dynamic table using complete recomputation?

1. FULL
2. TOTAL
3. COMPLETE_SCAN
4. REBUILD

Correct Answer: 1

Explanation:

The FULL refresh mode recomputes the dynamic table result rather than relying exclusively on incremental change processing. Full refreshes can be useful when a query cannot efficiently support incremental refresh or when the workload requires complete recomputation. The appropriate refresh mode depends on the query definition and workload characteristics. Data engineers should evaluate refresh behavior because full recomputation can require more processing than incremental approaches. Understanding refresh modes is important when designing dynamic-table pipelines with predictable performance and resource requirements.

Question 111

Which Snowflake object provides maintained precomputed query results?

1. Materialized view
2. External table
3. File format
4. Stream

Correct Answer: 1

Explanation:

A materialized view stores maintained, precomputed results derived from its underlying query. Snowflake can maintain the materialized data as underlying information changes, allowing eligible queries to benefit from previously computed results. Materialized views differ from standard views because a standard view stores query logic rather than maintaining its own persisted query result. They can be useful for frequently accessed transformations or aggregations where repeatedly computing the same logic would otherwise consume additional resources. Engineers should evaluate maintenance overhead and query patterns before choosing this design.

Question 112

Which command can exchange the contents of two tables efficiently?

1. EXCHANGE TABLES
2. SWAP WITH
3. TABLE_SWITCH
4. REPLACE CONTENTS

Correct Answer: 2

Explanation:

Snowflake supports SWAP WITH for exchanging the data and metadata of two compatible tables through an efficient metadata operation. This can be useful in deployment workflows where a newly prepared table needs to replace an existing production table with minimal operational complexity. Instead of copying all records between objects, the operation exchanges their identities and associated metadata. Data engineers can use this pattern for controlled table replacement scenarios, but they should carefully verify dependencies, permissions, object characteristics, and downstream references before performing a swap.

Question 113

Which statement creates a table using another table’s structure and data reference?

1. CREATE TABLE AS VIEW
2. CREATE TABLE FROM STAGE
3. CREATE TABLE … CLONE
4. CREATE TABLE … MIRROR

Correct Answer: 3

Explanation:

CREATE TABLE … CLONE creates a clone of an existing table using Snowflake’s zero-copy cloning capabilities. The new table initially references the source table’s existing micro-partitions rather than physically duplicating the complete dataset. This is useful for development environments, testing, experimentation, and controlled data-management workflows. The clone becomes independently changeable after creation, and later modifications can result in additional storage consumption. Cloning provides a fast way to create isolated table environments while minimizing immediate physical data duplication.

Question 114

Which function extracts a value from a VARIANT object using a path?

1. PATH_VALUE
2. GET_PATH
3. JSON_LOCATE
4. OBJECT_PATH

Correct Answer: 2

Explanation:

GET_PATH retrieves a value from semi-structured data using a specified path. It is useful when working with nested objects stored in VARIANT values and when the desired location needs to be expressed explicitly. Snowflake also provides path notation for many common access patterns, but GET_PATH can make path-based extraction explicit in transformation logic. Data engineers should account for missing paths and differing source structures when designing transformations because semi-structured data may not consistently contain every expected attribute.

Question 115

What does COPY INTO’s FILE_FORMAT reference define?

1. Source parsing rules
2. Warehouse scaling policy
3. Table retention period
4. Role inheritance behavior

Correct Answer: 1

Explanation:

The file format associated with COPY INTO defines how Snowflake interprets staged files during loading or unloading. Depending on the format, settings can specify delimiters, compression, quotation characters, headers, null representations, and other parsing behavior. Reusable named file formats help standardize ingestion configurations across pipelines. Data engineers should ensure the selected format matches the actual source files because incorrect parsing settings can produce shifted columns, malformed records, unexpected NULL values, or failed loads. Centralizing file-format definitions can also simplify operational maintenance.

Question 116

Which option skips the first rows of a delimited input file?

1. OMIT_ROWS
2. SKIP_HEADER
3. IGNORE_FIRST
4. HEADER_OFFSET

Correct Answer: 2

Explanation:

SKIP_HEADER specifies how many initial rows Snowflake should skip when processing supported delimited files. This is commonly used when source files contain column-header rows that should not be loaded as data. For example, a CSV file with one header row can use an appropriate SKIP_HEADER setting so that only actual records are processed. Engineers should confirm the source format before applying the option because incorrectly skipping rows can remove legitimate data. Consistent source-file conventions make header handling easier to automate.

Question 117

Which file-format option treats spaces around fields as removable?

1. SPACE_CLEANUP
2. TRIM_SPACE
3. STRIP_SPACES
4. FIELD_TRIMMER

Correct Answer: 2

Explanation:

TRIM_SPACE is a file-format option that controls whether spaces surrounding fields are removed during supported data loading operations. This can help normalize incoming delimited data when source systems introduce unnecessary whitespace around values. Without appropriate handling, leading or trailing spaces may cause unexpected comparisons, duplicate-looking records, or validation issues. Data engineers should apply trimming deliberately because whitespace can sometimes be meaningful. File-format configuration should therefore reflect the source system’s actual data conventions and the semantic requirements of downstream transformations.

Question 118

Which option allows a field to be enclosed only when necessary?

1. FIELD_OPTIONALLY_ENCLOSED_BY
2. CONDITIONAL_QUOTE
3. OPTIONAL_FIELD_QUOTES
4. FLEXIBLE_ENCLOSURE

Correct Answer: 1

Explanation:

FIELD_OPTIONALLY_ENCLOSED_BY specifies a character that can optionally surround fields in supported delimited files. This is useful for source formats where values may be enclosed only when they contain delimiters, special characters, or other content requiring protection. Correctly configuring field enclosure helps Snowflake interpret complex delimited records without incorrectly splitting values. Engineers should match this setting to the conventions used by the producing system. Incorrect enclosure settings can result in parsing errors or improperly separated fields during ingestion.

Question 119

Which command can recover a dropped schema during its retention period?

1. RESTORE SCHEMA
2. UNDROP SCHEMA
3. RECOVER SCHEMA
4. REBUILD SCHEMA

Correct Answer: 2

Explanation:

UNDROP SCHEMA can restore a dropped schema when the schema remains recoverable under the applicable Snowflake retention rules. Recovery through UNDROP can help reverse accidental deletion without recreating the schema and its objects manually. The ability to recover depends on object type and available retention conditions. Data engineers should still maintain appropriate operational controls and recovery procedures because retention-based recovery is not unlimited. Understanding object recovery commands is important when designing safe administrative processes for environments containing critical production data.

Question 120

Which Snowflake feature can automatically maintain a searchable optimization structure for selective lookups?

1. Query Acceleration Service
2. Automatic Clustering
3. Search Optimization Service
4. Warehouse Result Cache

Correct Answer: 3

Explanation:

Snowflake’s Search Optimization Service creates and maintains additional structures that can accelerate highly selective lookup queries on eligible tables. It is particularly useful when queries frequently search for specific values without naturally benefiting from conventional clustering. The service is different from Automatic Clustering, which organizes micro-partitions according to clustering keys, and from result caching, which reuses previously computed results. Data engineers should evaluate query patterns before enabling search optimization because the service introduces maintenance considerations and is most valuable for appropriate selective-access workloads.