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

 

Question 141

Which function creates a secure hash representation of input values?

1. SHA2
2. HASHING_VALUE
3. SECURE_HASH
4. DIGEST_TEXT

Correct Answer: 1

Explanation:

SHA2 generates a cryptographic hash using a SHA-2 algorithm. Hashing can be useful for creating deterministic representations of values for comparison, pseudonymization, or data-processing workflows. Unlike encryption, hashing is designed as a one-way transformation rather than a mechanism for recovering the original value. Data engineers should select the appropriate hashing algorithm and consider whether additional security controls are required. Hashing sensitive information does not automatically make it suitable for every security scenario, so access control and data-governance requirements should still be applied.

Question 142

Which function converts text into binary data?

1. TEXT_TO_BINARY
2. TO_BINARY
3. BINARY_CAST
4. CONVERT_BINARY

Correct Answer: 2

Explanation:

TO_BINARY converts an expression into the BINARY data type using supported input formats. It is useful when data pipelines need to process encoded values or binary representations. Depending on the input and format specification, Snowflake can interpret textual representations such as hexadecimal or Base64. Engineers should explicitly understand the encoding of source data before conversion because treating encoded text incorrectly can produce unexpected binary values. Correct binary conversion is especially important when integrating Snowflake with systems that exchange encoded payloads.

Question 143

Which function safely converts invalid input into NULL instead of raising an error?

1. CAST_SAFE
2. CONVERT_OR_NULL
3. TRY_CAST
4. SAFE_CONVERT

Correct Answer: 3

Explanation:

TRY_CAST attempts to convert a value to a specified data type and returns NULL when the conversion cannot be performed instead of raising a conversion error. This behavior is useful in ingestion and transformation pipelines containing imperfect source data. Engineers can use it when malformed values should be identified or handled without terminating the entire query. However, silently producing NULL values can hide source-quality problems, so important pipelines should pair safe conversion with validation or monitoring logic where appropriate.

Question 144

Which command changes the definition of an existing table?

1. MODIFY TABLE
2. ALTER TABLE
3. CHANGE TABLE
4. EDIT TABLE

Correct Answer: 2

Explanation:

ALTER TABLE modifies the definition or properties of an existing table. Depending on the operation, engineers can use it to add or modify supported columns, constraints, clustering-related properties, or other table characteristics. This command is fundamental for controlled schema management in Snowflake. Changes should be coordinated with downstream transformations and applications because schema modifications can affect queries, views, procedures, and ingestion mappings. Automated schema-evolution workflows should also include governance controls to prevent unintended structural changes.

Question 145

Which table operation removes all rows while retaining the table structure?

1. DROP TABLE
2. DELETE OBJECT
3. TRUNCATE TABLE
4. REMOVE ROWS

Correct Answer: 3

Explanation:

TRUNCATE TABLE removes all rows from a table while retaining the table itself and its structural definition. It is different from DROP TABLE, which removes the table object, and from a filtered DELETE, which can remove selected rows. Truncation can be useful when a staging table needs to be cleared before a fresh batch is loaded. Engineers should understand the retention and transaction behavior relevant to the operation before using it in production workflows, especially when accidental data removal would affect downstream processing.

Question 146

Which SQL clause filters rows after window functions are calculated?

1. QUALIFY
2. WINDOW_FILTER
3. POST_FILTER
4. AFTER_WINDOW

Correct Answer: 1

Explanation:

QUALIFY filters query results after window functions have been evaluated. This makes it particularly useful for patterns such as selecting the latest record per customer without requiring an additional nested query solely for filtering the window-function result. For example, an engineer can calculate ROW_NUMBER() and then use QUALIFY to retain the desired row. This can make deduplication and ranking queries more readable while keeping the transformation logic in a single SQL statement.

Question 147

Which function generates sequential values for synthetic rows?

1. SEQUENCE_ROWS
2. GENERATOR
3. ROW_GENERATOR
4. SYNTHETIC_ROWS

Correct Answer: 2

Explanation:

Snowflake’s GENERATOR table function can produce a specified number of rows or rows based on a time limit. It is useful for generating synthetic datasets, testing transformations, creating date or number sequences, and supporting development workflows. When combined with functions such as SEQ4() or other expressions, engineers can generate predictable test values. Because generated rows are synthetic, they should be clearly separated from production source data and used carefully when validating pipeline behavior.

Question 148

Which function aggregates values into a JSON-like object?

1. OBJECT_AGG
2. OBJECT_SUM
3. JSON_GROUP
4. KEY_AGGREGATE

Correct Answer: 1

Explanation:

OBJECT_AGG aggregates key-value pairs into an OBJECT. It is useful when relational rows need to be transformed into a semi-structured representation where each key maps to a corresponding value. This can support nested payload construction, attribute aggregation, and transformations for downstream systems that consume JSON-like structures. Engineers should ensure that keys are appropriate for aggregation and understand how duplicate keys are handled. Object aggregation is especially useful when reshaping normalized records into compact semi-structured outputs.

Question 149

Which function returns the keys from a VARIANT object?

1. OBJECT_KEYS
2. VARIANT_KEYS_LIST
3. GET_KEYS
4. JSON_FIELDS

Correct Answer: 1

Explanation:

OBJECT_KEYS returns the keys contained in an OBJECT value. It is useful for inspecting or processing semi-structured data when the available attributes are not fully known in advance. Data engineers can use it during schema discovery, profiling, validation, and dynamic transformation workflows. This is particularly valuable when upstream JSON structures evolve over time. Because semi-structured objects may contain different attributes across records, key inspection can help identify structural variation before implementing rigid downstream transformations.

Question 150

Which function returns the first non-NULL expression?

1. FIRST_VALID
2. COALESCE
3. NON_NULL_VALUE
4. SELECT_FIRST

Correct Answer: 2

Explanation:

COALESCE returns the first expression in its argument list that is not NULL. It is widely used in data transformations to provide fallback values when preferred source attributes are missing. For example, a pipeline can select a primary customer attribute and fall back to an alternate field when the first value is unavailable. Engineers should use fallback logic carefully because replacing NULLs can change the semantic meaning of missing information. Clear business rules should determine which fallback values are appropriate.

Question 151

Which command renames an existing Snowflake object?

1. RENAME OBJECT
2. ALTER … RENAME TO
3. CHANGE NAME
4. UPDATE OBJECT NAME

Correct Answer: 2

Explanation:

Snowflake uses the ALTER … RENAME TO syntax for supported object-renaming operations. Renaming can be useful when promoting tables, reorganizing schemas, or applying controlled naming changes. However, engineers should examine dependencies before renaming because downstream objects, applications, scripts, or integrations may reference the original object name. A rename should therefore be treated as a schema-management change rather than merely a cosmetic operation. Proper deployment procedures can reduce the risk of breaking dependent workloads.

Question 152

Which function converts a value into VARIANT without parsing JSON text?

1. TO_VARIANT
2. VARIANT_CAST_JSON
3. MAKE_VARIANT
4. CONVERT_VARIANT_DATA

Correct Answer: 1

Explanation:

TO_VARIANT converts an expression into the VARIANT data type. This differs from PARSE_JSON, which interprets a string as JSON and constructs a corresponding semi-structured representation. TO_VARIANT is useful when different Snowflake-supported values need to be stored or manipulated through a common VARIANT interface. Engineers should choose the conversion function according to the source representation. If text actually contains JSON that must be interpreted structurally, simply converting the text to VARIANT is not equivalent to parsing its JSON content.

Question 153

Which feature can automatically recluster eligible table data?

1. Search Optimization
2. Query Acceleration
3. Automatic Clustering
4. Result Caching

Correct Answer: 3

Explanation:

Automatic Clustering maintains clustering information for eligible tables as data changes. When a table has a clustering key and its data becomes less well organized, Snowflake can perform background maintenance to improve clustering. This can help queries that benefit from pruning based on the clustering dimensions. Automatic Clustering is distinct from Search Optimization, which targets selective lookup patterns through additional search structures. Engineers should evaluate workload characteristics before enabling clustering because maintenance activity can introduce additional resource consumption.

Question 154

Which function converts a timestamp to a specified time zone?

1. CONVERT_TIMEZONE
2. CHANGE_TIMEZONE
3. TIMEZONE_SHIFT
4. TIMESTAMP_ZONE

Correct Answer: 1

Explanation:

CONVERT_TIMEZONE converts a timestamp from one time zone to another according to Snowflake’s supported timestamp semantics. This is useful in pipelines that combine data from systems operating in different geographic regions. Engineers should distinguish between changing the represented time zone and merely changing a timestamp’s display or data type. Consistent time-zone handling is important for event ordering, scheduling, reporting, and incremental processing. Pipelines should establish a clear convention for storing and presenting timestamps to avoid ambiguous temporal calculations.

Question 155

Which function extracts the year component from a date?

1. DATE_YEAR
2. EXTRACT
3. YEAR_VALUE
4. GET_YEAR

Correct Answer: 2

Explanation:

EXTRACT retrieves a specified date or time component from a temporal expression. For example, an engineer can use it to obtain the year, month, day, hour, or other supported component from a date or timestamp. This is useful when building dimensional attributes, grouping records by calendar periods, or creating time-based transformations. Snowflake also provides specialized date functions, but EXTRACT provides a general syntax for retrieving individual temporal components in SQL transformations.

Question 156

Which command creates a new schema inside a database?

1. NEW SCHEMA
2. CREATE SCHEMA
3. ADD SCHEMA
4. BUILD SCHEMA

Correct Answer: 2

Explanation:

CREATE SCHEMA creates a schema within a Snowflake database. Schemas provide logical organization for tables, views, stages, procedures, functions, and other database objects. Data engineering teams commonly use schemas to separate raw, staging, curated, or application-specific objects. Clear schema organization can simplify permissions, ownership, deployment processes, and pipeline maintenance. Engineers should establish naming and ownership conventions before creating many schemas because consistent organization becomes increasingly important as a Snowflake environment grows.

Question 157

Which function returns the current date?

1. TODAY_DATE
2. CURRENT_DATE
3. SYSTEM_DATE
4. NOW_DATE

Correct Answer: 2

Explanation:

CURRENT_DATE returns the current date according to the session context. It is commonly used in transformations, date filtering, retention calculations, and incremental processing logic. Unlike functions that return timestamps, it provides the date value without requiring a time component. Engineers should consider session and time-zone behavior when building pipelines where the boundary between dates matters. Date-based processing should be designed carefully around midnight transitions, particularly when source systems and Snowflake sessions operate across different time zones.

Question 158

Which function returns the current timestamp?

1. CURRENT_TIMESTAMP
2. NOW_TIMESTAMP_VALUE
3. SYSTEM_CURRENT_TIME
4. CURRENT_CLOCK

Correct Answer: 1

Explanation:

CURRENT_TIMESTAMP returns the current timestamp within the Snowflake session context. It is useful for adding ingestion timestamps, audit columns, processing markers, and operational metadata to transformed records. Engineers should understand the timestamp type and session time-zone behavior when using it in cross-system pipelines. Consistent timestamp conventions help with event ordering and operational troubleshooting. When exact source-event time is required, however, pipelines should prefer the timestamp supplied by the source system rather than replacing it with the processing-time value.

Question 159

Which function determines whether an expression is an array?

1. IS_ARRAY
2. ARRAY_CHECK
3. IS_ARRAY_VALUE
4. TYPE_ARRAY

Correct Answer: 1

Explanation:

IS_ARRAY can be used to test whether a value represents an array in Snowflake’s semi-structured data processing. This is useful when source records can contain different JSON structures and transformation logic must branch according to the actual data type. Engineers can combine type checks with functions such as ARRAY_SIZE or FLATTEN when processing nested content. Explicit validation helps prevent transformations from assuming that every VARIANT value has the same structure, which is a common challenge with flexible JSON sources.

Question 160

Which command creates a new table from a query result?

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

Correct Answer: 1

Explanation:

CREATE TABLE AS SELECT, commonly abbreviated as CTAS, creates a new table from the results of a query. It is useful for materializing transformed or filtered datasets into a persistent table. Data engineers can use CTAS during staging, analytical preparation, migration, or transformation workflows. The resulting table contains the query output rather than maintaining a live dependency on the source query in the same way as a view. Engineers should consider table ownership, clustering, data types, and refresh requirements when using CTAS in production pipelines.