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

 

Question 181

Which Snowflake object stores reusable procedural SQL logic?

1. User-defined function
2. Stream
3. Stored procedure
4. Stage

Correct Answer: 3

Explanation:

A stored procedure encapsulates procedural logic that can perform multiple operations and execute SQL statements. It is useful when a workflow requires branching, variables, exception handling, or several coordinated database actions. Unlike a UDF, which is primarily designed to return a value within an expression, a stored procedure can implement broader operational logic. Data engineers can use procedures as reusable components within data pipelines, administrative workflows, and task-based orchestration. Proper privilege management is important because procedures may execute with owner or caller rights depending on their configuration.

Question 182

Which procedure setting executes using the caller’s privileges?

1. EXECUTE AS CALLER
2. EXECUTE AS USER
3. RUN AS INVOKER
4. CALLER RIGHTS MODE

Correct Answer: 1

Explanation:

EXECUTE AS CALLER causes a stored procedure to execute using the privileges of the user invoking it. This differs from owner-rights execution, where the procedure operates using privileges associated with its owning role. Caller rights can be useful when the procedure should respect the permissions of each individual user rather than providing access through the procedure owner. Engineers should choose the execution model deliberately because it directly affects authorization behavior and the resources that the procedure can access during execution.

Question 183

Which function removes duplicate rows from an aggregated array?

1. ARRAY_UNIQUE
2. DISTINCT_ARRAY
3. ARRAY_DISTINCT
4. UNIQUE_ELEMENTS

Correct Answer: 3

Explanation:

ARRAY_DISTINCT returns an array containing distinct elements from an input array. It is useful when semi-structured transformations produce repeated values that need to be consolidated before downstream processing. For example, a pipeline might aggregate identifiers from several source records and then remove duplicate identifiers from the resulting array. Engineers should consider the data type and semantics of the array elements when applying this function. Deduplication at the array level can simplify downstream consumption while preserving the compact semi-structured representation.

Question 184

Which function splits a string into an array using a delimiter?

1. STRING_ARRAY
2. SPLIT
3. ARRAY_SPLIT_TEXT
4. TOKENIZE_STRING

Correct Answer: 2

Explanation:

SPLIT divides a string into an array using a specified separator. This is useful when source systems provide multiple values in a single delimited field and the pipeline needs to transform them into individual elements. The resulting array can subsequently be processed using array functions or expanded with other Snowflake functionality. Engineers should verify the source delimiter carefully because inconsistent separators can produce unexpected elements. String splitting is commonly used when normalizing semi-structured or legacy source attributes during ingestion.

Question 185

Which function combines array elements into a single string?

1. ARRAY_JOIN
2. JOIN_ARRAY_TEXT
3. ARRAY_TO_TEXT
4. ARRAY_STRINGIFY

Correct Answer: 1

Explanation:

ARRAY_TO_STRING is used in Snowflake to convert array elements into a single string with a specified separator. It can be useful when a transformed array must be exported into a delimited representation for downstream systems. Engineers should select a separator that cannot be confused with meaningful data or apply appropriate escaping conventions when necessary. The function is effectively the reverse of splitting a delimited string into an array and can therefore be useful in both ingestion and outbound transformation workflows.

Question 186

Which function expands array or object elements into separate rows?

1. EXPAND_VARIANT
2. FLATTEN
3. UNNEST_JSON
4. EXPLODE_OBJECT

Correct Answer: 2

Explanation:

FLATTEN converts elements of arrays or objects stored in semi-structured data into a tabular representation. It is commonly used with LATERAL to expand nested JSON structures so individual elements can be processed as rows. This is valuable when source payloads contain arrays of transactions, products, events, or other repeated entities. Engineers should carefully consider the resulting row multiplication because flattening nested structures can significantly increase the number of rows processed by a transformation.

Question 187

Which function returns the keys from an object as an array?

1. OBJECT_KEYS
2. GET_OBJECT_KEYS
3. KEY_LIST
4. OBJECT_ATTRIBUTES

Correct Answer: 1

Explanation:

OBJECT_KEYS returns the keys associated with an OBJECT value as an array. It is useful when engineers need to inspect dynamic JSON structures whose attributes are not completely known beforehand. For example, schema-profiling workflows can use object keys to identify fields appearing across semi-structured records. This can help data engineers understand source evolution before defining downstream transformations. Because different records may contain different keys, the resulting information should be handled carefully when building automated schema-discovery or validation processes.

Question 188

Which function returns the number of elements in an array?

1. ARRAY_COUNT
2. COUNT_ARRAY
3. ARRAY_SIZE
4. ELEMENTS_TOTAL

Correct Answer: 3

Explanation:

ARRAY_SIZE returns the number of elements in an array. It can be used to validate incoming JSON structures, determine whether a collection contains records, or drive conditional transformation logic. For example, a pipeline can check whether an array is empty before attempting to flatten it. Array-size checks are particularly useful when source systems produce optional or variable-length collections. Engineers should distinguish array length from the number of rows generated after flattening because nested structures can produce different row counts depending on their shape.

Question 189

Which function converts an object into an array of key-value pairs?

1. OBJECT_ENTRIES
2. OBJECT_TO_PAIRS
3. KEY_VALUE_ARRAY
4. OBJECT_ELEMENTS

Correct Answer: 1

Explanation:

Snowflake provides OBJECT_ENTRIES for converting an object into an array containing key-value representations. This can help when dynamic object attributes need to be processed as individual elements rather than accessed through fixed field names. It is particularly useful for schema-flexible transformations, profiling, and generic processing of semi-structured objects. Engineers can combine object-entry processing with flattening techniques to transform dynamic attributes into rows. This approach is useful when upstream systems introduce varying sets of object keys across records.

Question 190

Which function concatenates multiple strings into one value?

1. STRING_MERGE
2. CONCAT
3. TEXT_COMBINE
4. JOIN_TEXT

Correct Answer: 2

Explanation:

CONCAT combines two or more expressions into a single string value. It is useful for constructing identifiers, labels, paths, or other derived text attributes during transformations. Engineers should consider NULL behavior and data types when combining values because unexpected missing inputs can affect the resulting expression. For more complex formatting, Snowflake also provides other string functions that may be more appropriate. String concatenation is frequently used when building deterministic keys or assembling human-readable attributes from multiple source columns.

Question 191

Which function returns the number of characters in a string?

1. LENGTH
2. CHARACTER_TOTAL
3. STRING_COUNT
4. TEXT_LENGTH_VALUE

Correct Answer: 1

Explanation:

LENGTH returns the length of a string expression. It can support data-quality checks, validation rules, profiling, and transformation logic where the expected size of a text value matters. For example, an ingestion pipeline can identify values exceeding an expected business limit before loading them into a constrained downstream system. Engineers should distinguish character length from byte length when working with multilingual or encoded data because these measurements can differ. Applying the appropriate length function helps avoid incorrect validation assumptions.

Question 192

Which function extracts a substring from text using a starting position and length?

1. TEXT_SLICE
2. SUBSTRING
3. STRING_EXTRACT
4. PARTIAL_TEXT

Correct Answer: 2

Explanation:

SUBSTRING extracts a portion of a string beginning at a specified position and continuing for a defined length when provided. It is useful for parsing fixed-format identifiers, extracting portions of codes, and transforming legacy text fields. Data engineers can combine substring logic with other string functions when source values contain predictable positional structures. Care should be taken with indexing and values shorter than the requested range. Clear source specifications help ensure substring transformations consistently produce the intended attributes.

Question 193

Which function removes leading and trailing whitespace?

1. SPACE_REMOVE
2. TRIM
3. CLEAN_TEXT
4. STRIP_WHITESPACE

Correct Answer: 2

Explanation:

TRIM removes specified characters from the beginning and end of a string, with whitespace being the common default use. It is useful for normalizing source values before comparison, deduplication, or validation. Untrimmed data can cause apparently identical values to behave differently in joins or equality checks. Engineers often apply trimming during staging transformations when source systems are known to introduce unnecessary whitespace. However, trimming should be used intentionally because spaces may sometimes carry meaningful information in specialized data.

Question 194

Which function converts text to uppercase characters?

1. UPPERCASE
2. TO_UPPER
3. UPPER
4. CAPITALIZE_TEXT

Correct Answer: 3

Explanation:

UPPER converts alphabetic characters in a string to uppercase. It can help normalize textual attributes before comparisons, grouping, validation, or matching operations. For example, source systems may represent the same categorical value using different capitalization conventions. Applying a consistent transformation can simplify downstream logic. Engineers should remember that capitalization normalization does not automatically resolve other differences such as whitespace, punctuation, or language-specific representations. Text normalization should therefore be designed according to the actual characteristics of the source data.

Question 195

Which function returns a lowercase representation of text?

1. LOWER
2. TO_LOWERCASE
3. LOWERCASE_TEXT
4. DOWNCASE_VALUE

Correct Answer: 1

Explanation:

LOWER converts alphabetic characters in a string to lowercase. It is commonly used when normalizing textual fields for comparisons, deduplication, matching, and categorization. For example, converting email-domain or category values to a consistent case can prevent unnecessary distinctions between otherwise equivalent source values. Engineers should combine lowercase conversion with other normalization rules when source systems may contain whitespace or formatting variations. Case normalization should also respect business requirements when capitalization itself has semantic significance.

Question 196

Which function replaces matching text within a string?

1. TEXT_REPLACE
2. REPLACE
3. SUBSTITUTE_TEXT
4. STRING_SWAP

Correct Answer: 2

Explanation:

REPLACE substitutes occurrences of a specified substring within another string. It is useful for cleaning source values, standardizing formatting, removing unwanted characters, and transforming text during ingestion. For example, a pipeline can replace legacy separators or known formatting artifacts before applying downstream parsing logic. Engineers should ensure that replacement patterns are specific enough to avoid changing legitimate content. For large-scale transformations, testing the replacement expression against representative source data can help prevent unintended modifications.

Question 197

Which function identifies whether a string matches a regular expression?

1. REGEXP_LIKE
2. REGEX_MATCH
3. PATTERN_TEST
4. STRING_REGEX_CHECK

Correct Answer: 1

Explanation:

REGEXP_LIKE evaluates whether a string matches a specified regular expression. It is useful for validating formats such as identifiers, codes, structured text, and source-system fields. Data engineers can apply it during staging or quality checks to identify records that do not conform to expected patterns. Regular expressions should be designed carefully because overly permissive patterns can allow invalid values, while overly restrictive patterns can reject legitimate variations. Testing expressions against representative data is an important part of reliable validation design.

Question 198

Which function extracts the first matching substring using a regular expression?

1. REGEXP_EXTRACT
2. REGEXP_SUBSTR
3. REGEX_CAPTURE
4. PATTERN_SUBSTRING

Correct Answer: 2

Explanation:

REGEXP_SUBSTR returns a substring that matches a specified regular expression pattern. It is useful when engineers need to extract structured components embedded within larger text values. For example, a transformation could extract an identifier or code from a composite source field. Regular-expression extraction can reduce complicated string parsing when the source format follows predictable patterns. Engineers should validate patterns against real source variations because malformed or unexpected values can result in missing matches and NULL outputs.

Question 199

Which function safely parses a JSON string and returns NULL on invalid input?

1. TRY_PARSE_JSON
2. SAFE_JSON_PARSE
3. PARSE_JSON_SAFE
4. JSON_TRY_CONVERT

Correct Answer: 1

Explanation:

TRY_PARSE_JSON attempts to parse a string as JSON while returning NULL when the input cannot be parsed successfully. This can be useful in ingestion pipelines where malformed JSON records should be isolated instead of causing the entire transformation to fail. Engineers can combine the function with validation logic to identify invalid payloads for remediation. Safe parsing should not replace data-quality monitoring, however. Pipelines should track rejected or unparseable records when those records are important to business completeness.

Question 200

Which function counts rows satisfying a conditional expression?

1. COUNT_IF
2. CONDITIONAL_COUNT
3. COUNT_WHERE
4. FILTER_COUNT

Correct Answer: 1

Explanation:

COUNT_IF counts rows for which a specified Boolean condition evaluates to true. It is useful for profiling, validation, monitoring, and analytical transformations where engineers need conditional record counts without writing a separate filtered query. For example, a data-quality report can count records meeting or violating a particular condition. Using conditional aggregation can make monitoring queries concise and efficient. Engineers should ensure that the condition accurately represents the intended business rule and consider NULL behavior when designing the expression.