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

 

Question 261

Which function converts JSON text into a VARIANT value?

1. JSON_TO_VARIANT
2. OBJECT_PARSE
3. CONVERT_JSON
4. PARSE_JSON

Correct Answer: 4

Explanation:

PARSE_JSON converts a string containing valid JSON into a Snowflake VARIANT value. This is commonly used when ingestion pipelines receive JSON content as text and need to access nested properties using Snowflake’s semi-structured data capabilities. Once parsed, the resulting VARIANT can be queried with path expressions and processed with functions such as FLATTEN. Engineers should validate source formatting and account for malformed JSON when designing production ingestion pipelines.

Question 262

Which function determines the Snowflake data type of an expression?

1. TYPEOF
2. DATA_TYPE
3. VALUE_TYPE
4. EXPRESSION_TYPE

Correct Answer: 1

Explanation:

TYPEOF returns information about the data type of an expression. It is particularly useful when working with VARIANT values because semi-structured data can contain different underlying types. Engineers can use TYPEOF during exploration, validation, and transformation development to understand whether a value represents a string, number, array, object, or another supported type. This helps prevent incorrect assumptions when processing heterogeneous source data.

Question 263

Which function returns the number of elements in an array?

1. ARRAY_COUNT
2. ARRAY_SIZE
3. ELEMENT_COUNT
4. SIZE_ARRAY

Correct Answer: 2

Explanation:

ARRAY_SIZE returns the number of elements in an array. It is useful when transforming semi-structured data and validating whether arrays contain expected numbers of values. Data engineers can combine ARRAY_SIZE with conditional logic to identify empty or unusually large arrays before flattening them. When working with JSON-derived VARIANT values, engineers should also distinguish arrays from objects because their structures require different functions and access patterns.

Question 264

Which function extracts a value using a JSON path?

1. GET_PATH
2. JSON_EXTRACT_PATH
3. PATH_VALUE
4. EXTRACT_JSON

Correct Answer: 1

Explanation:

GET_PATH extracts a value from a VARIANT expression using a path specification. It is useful when engineers need to access nested attributes dynamically or construct transformations around semi-structured data. Snowflake also supports path-style notation for direct access, but GET_PATH can make path extraction explicit in SQL expressions. Engineers should ensure that the supplied path matches the actual structure of the source JSON and account for missing attributes.

Question 265

Which function returns a hexadecimal representation of binary data?

1. TO_HEX
2. HEX_ENCODE
3. BINARY_HEX
4. ENCODE_HEX

Correct Answer: 1

Explanation:

TO_HEX converts binary data into its hexadecimal representation. This can be useful when binary values need to be displayed, logged, compared, or transferred in a textual representation. Data engineers working with encoded identifiers or binary payloads may use hexadecimal conversion as part of transformation or debugging workflows. The resulting representation is text, so downstream processing should account for the difference between the original binary value and its textual hexadecimal form.

Question 266

Which function converts hexadecimal text into binary?

1. FROM_HEX
2. HEX_TO_BINARY
3. DECODE_HEX_TEXT
4. BINARY_FROM_HEX

Correct Answer: 1

Explanation:

FROM_HEX converts hexadecimal-encoded text into a binary value. This is useful when source systems represent binary information as hexadecimal strings and Snowflake processing requires the underlying binary representation. Engineers should ensure that incoming strings contain valid hexadecimal characters and use the appropriate encoding conventions. Conversion functions are particularly helpful when integrating systems that represent the same payload differently across APIs, files, or database platforms.

Question 267

Which command creates a table from a query result?

1. CREATE TABLE AS SELECT
2. CREATE RESULT TABLE
3. BUILD TABLE FROM QUERY
4. MATERIALIZE SELECT TABLE

Correct Answer: 1

Explanation:

CREATE TABLE AS SELECT, commonly abbreviated CTAS, creates a table using the result of a query. It is useful for materializing transformed datasets, creating derived tables, or preparing intermediate structures. Engineers can use CTAS to combine table creation with transformation logic in a single statement. The resulting table is independent of the original query after creation, so subsequent source-table changes do not automatically update the CTAS result.

Question 268

Which command changes a table’s column definition?

1. MODIFY TABLE
2. ALTER TABLE
3. UPDATE TABLE SCHEMA
4. CHANGE TABLE

Correct Answer: 2

Explanation:

ALTER TABLE is used for supported structural changes to an existing table. Depending on the operation, engineers can add, rename, modify, or otherwise manage table properties and columns through appropriate ALTER TABLE syntax. Structural changes should be considered carefully in production pipelines because downstream views, transformations, and applications may depend on existing column names or data types. Schema changes should therefore be coordinated with affected consumers.

Question 269

Which operation changes a column name without rebuilding the table?

1. RENAME COLUMN
2. ALTER COLUMN LABEL
3. CHANGE COLUMN TITLE
4. SET COLUMN NAME

Correct Answer: 1

Explanation:

Snowflake supports column renaming through the appropriate ALTER TABLE … RENAME COLUMN syntax. Renaming changes the column’s identifier while retaining the underlying table structure. Engineers should evaluate dependencies before changing column names because downstream SQL, views, procedures, applications, and transformation jobs may reference the original identifier. A controlled schema-change process helps prevent unexpected failures in dependent data pipelines.

Question 270

Which command swaps the names and metadata of two tables?

1. EXCHANGE TABLES
2. SWAP TABLES
3. ALTER TABLE … SWAP WITH
4. SWITCH TABLE OBJECTS

Correct Answer: 3

Explanation:

ALTER TABLE … SWAP WITH exchanges the metadata and names of two tables. This operation can be useful in deployment patterns where a newly prepared table must replace an existing table with minimal operational complexity. Engineers may use this technique when building replacement datasets before exposing them under the production table name. Dependencies and permissions should be reviewed carefully because swapping objects changes which physical table is associated with each name.

Question 271

Which Snowflake feature provides point-in-time historical access?

1. Time Travel
2. Historical Tables
3. Snapshot Storage
4. Archive Querying

Correct Answer: 1

Explanation:

Snowflake Time Travel provides access to historical data within the applicable retention period. It can be used to investigate previous table states, recover accidentally changed data, or create objects from earlier points in time. Data engineers should understand the distinction between Time Travel and Fail-safe because they serve different recovery purposes. Historical access should also be planned around retention requirements, object type, and the available account configuration.

Question 272

Which clause identifies a historical timestamp for Time Travel?

1. AT
2. TIMESTAMP_AT
3. HISTORY_AT
4. POINT_IN_TIME

Correct Answer: 1

Explanation:

The AT clause can specify a historical point for Time Travel operations. It can be used with supported timestamp, offset, statement, or other historical-reference expressions according to Snowflake syntax. This allows engineers to query or create objects based on a prior state. Choosing the correct historical reference is important when recovering data because the selected point must correspond to the desired state within the object’s available retention window.

Question 273

Which clause references a point immediately before a statement?

1. PRIOR
2. BEFORE
3. PREVIOUS
4. EARLIER

Correct Answer: 2

Explanation:

The BEFORE clause can identify a point immediately before a specified statement for supported Time Travel operations. This is useful when engineers need to reconstruct or inspect a table state preceding a particular SQL statement. Statement-based historical references can be especially helpful during troubleshooting because they provide a precise relationship to a known operation. Engineers should confirm the relevant query or statement identifier before attempting recovery.

Question 274

Which object provides a recoverable deleted table during retention?

1. Deleted-table archive
2. Time Travel state
3. Recovery snapshot
4. Backup table

Correct Answer: 2

Explanation:

A dropped table can remain accessible through Snowflake Time Travel during the applicable retention period. This enables supported recovery operations before the historical data becomes unavailable through Time Travel. Engineers should distinguish this capability from a conventional backup table because the recovered object is based on Snowflake’s historical retention mechanisms. Prompt response is important when accidental deletion occurs because recovery options depend on retention and object state.

Question 275

Which command restores a dropped table when available?

1. RESTORE TABLE
2. UNDROP TABLE
3. RECOVER TABLE
4. RECREATE TABLE

Correct Answer: 2

Explanation:

UNDROP TABLE restores a dropped table when the object remains recoverable under Snowflake’s retention rules. This can be valuable after accidental deletion or during operational recovery. Engineers should verify whether an object with the same name already exists and understand applicable retention constraints before performing the operation. UNDROP is different from recreating a table because it restores the previously dropped object rather than constructing a new empty structure.

Question 276

Which feature improves lookup performance for selective predicates?

1. Search Optimization Service
2. Query Index Service
3. Predicate Cache
4. Lookup Accelerator Table

Correct Answer: 1

Explanation:

Snowflake Search Optimization Service can improve query performance for certain selective lookup patterns by maintaining additional search access structures. It is designed for workloads where queries frequently locate small portions of large datasets using supported predicates. Engineers should evaluate workload characteristics before enabling it because not every query benefits equally. Monitoring query behavior and considering storage and maintenance implications are important when introducing search optimization into production environments.

Question 277

Which feature automatically reorganizes table storage for clustering?

1. Automatic Clustering
2. Storage Rebalancing
3. Table Compaction
4. Data Sorting Service

Correct Answer: 1

Explanation:

Automatic Clustering manages reclustering work for tables that use clustering keys and require maintenance as data changes. This can help maintain effective data organization for workloads that benefit from clustering. Engineers should not assume that every table requires a clustering key because poorly suited clustering can add unnecessary maintenance. Workload access patterns, filtering behavior, table size, and query performance should be evaluated before implementing clustering.

Question 278

Which setting controls the maximum clustering depth targeted for a table?

1. CLUSTERING_TARGET
2. MAX_CLUSTER_DEPTH
3. CLUSTER_AT_DEPTH
4. TARGET_CLUSTER_LEVEL

Correct Answer: 1

Explanation:

Snowflake clustering behavior can be evaluated using clustering-related metrics such as clustering depth. Engineers use these metrics to understand how well data is organized relative to the clustering key. Clustering depth is an analytical measure rather than a generic table-size setting. When optimizing clustered tables, engineers should examine query pruning and workload behavior rather than relying on a single metric to determine whether clustering is beneficial.

Question 279

Which function generates a sequence of rows for testing?

1. GENERATOR
2. ROW_SEQUENCE
3. TEST_ROWS
4. SEQUENCE_GENERATOR

Correct Answer: 1

Explanation:

The GENERATOR table function can generate a specified number of rows or rows for a specified time period. It is useful for testing transformations, creating synthetic datasets, and developing SQL logic without requiring a source table containing the desired number of records. Engineers can combine GENERATOR with functions such as SEQ4 or other expressions to produce test values. Synthetic workloads should still reflect realistic distributions when they are being used for performance experiments.

Question 280

Which function creates sequential numeric values in generated rows?

1. ROW_NUMBER
2. SEQ4
3. COUNTER_VALUE
4. AUTO_SEQUENCE

Correct Answer: 2

Explanation:

SEQ4() generates a sequence of increasing integers and is commonly used with the GENERATOR function to create synthetic row identifiers or test data. It is useful for building deterministic-looking development datasets and supporting row-generation patterns. Engineers should distinguish sequence functions from analytical window functions such as ROW_NUMBER because they have different semantics and use cases. Generated values should not automatically be treated as business keys unless the application explicitly requires that behavior.