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

 

Question 241

Which object stores reusable SQL transformation logic?

1. Table
2. Stage
3. View
4. File format

Correct Answer: 3

Explanation:

A view stores a reusable SQL query definition that can present transformed or filtered data without storing a separate physical copy of the underlying result. Views are useful for abstraction, data access patterns, and reusable transformation logic. Data engineers can use views to expose curated datasets while keeping the underlying tables separate from consumer-facing structures. Unlike materialized views, standard views do not maintain a physically stored result set. Choosing between these objects depends on performance, freshness, storage, and transformation requirements.

Question 242

Which privilege permits inserting rows into a table?

1. INSERT
2. LOAD
3. WRITE
4. MODIFY

Correct Answer: 1

Explanation:

The INSERT privilege allows a role to insert rows into a table. Snowflake uses role-based access control to separate object ownership from individual data-operation permissions. When designing data pipelines, engineers should grant only the privileges required by the workload. A transformation task that writes into a target table may require INSERT along with other privileges depending on the SQL operation. Carefully scoped grants reduce unnecessary access while still allowing automated processing to complete successfully.

Question 243

Which object can encapsulate procedural SQL logic?

1. View
2. Stored procedure
3. Stage
4. File format

Correct Answer: 2

Explanation:

A stored procedure can encapsulate procedural logic and execute multiple operations according to its implementation. Procedures are useful for orchestration, administrative workflows, conditional processing, and reusable database operations. Unlike a simple view, a procedure can contain procedural statements and perform actions rather than merely returning a query result. Data engineers should select procedures when workflow behavior requires procedural control, while simpler transformations can often remain within SQL statements or views.

Question 244

Which object is designed for reusable scalar calculations?

1. Stream
2. Task
3. User-defined function
4. Pipe

Correct Answer: 3

Explanation:

A user-defined function can encapsulate reusable calculation logic and return a value based on supplied arguments. Scalar UDFs are useful when the same business calculation must be applied consistently across multiple SQL statements or datasets. Centralizing the calculation can reduce duplicated SQL and improve maintainability. Engineers should consider whether the function’s logic is appropriate for row-level execution and evaluate its performance when used across large datasets.

Question 245

Which UDF type returns multiple rows through a table result?

1. Table function
2. Scalar function
3. Aggregate function
4. Boolean function

Correct Answer: 1

Explanation:

A table function returns a tabular result that can be queried as part of a SQL statement. This makes table functions useful for reusable logic that needs to produce multiple rows or columns rather than a single scalar value. Engineers can use table functions when a transformation needs parameterized, reusable result sets. The distinction between scalar and table functions is important because their invocation patterns and output structures differ.

Question 246

Which procedure execution mode uses the caller’s privileges?

1. EXECUTE AS OWNER
2. EXECUTE AS CALLER
3. EXECUTE AS ROLE
4. EXECUTE AS USER

Correct Answer: 2

Explanation:

EXECUTE AS CALLER causes a stored procedure to execute using the privileges of the user invoking it, subject to Snowflake’s authorization rules. This can be useful when the procedure should respect the caller’s existing access rather than using the procedure owner’s privileges. Engineers should choose execution context deliberately because it directly affects security behavior. Procedures that need controlled elevated access may use a different execution model when appropriate.

Question 247

Which object can expose a curated SQL result without duplicating table data?

1. View
2. Stage
3. Pipe
4. Stream

Correct Answer: 1

Explanation:

A standard view exposes the result of its stored SQL definition without creating a separate physical copy of the underlying table data. This makes views useful for presenting filtered, joined, or transformed datasets to downstream consumers. They can also provide an abstraction layer between physical storage and analytical users. Engineers should remember that query performance still depends on the underlying execution plan and data structures because a standard view does not independently materialize its result.

Question 248

Which object maintains a physically stored query result?

1. Standard view
2. External table
3. Materialized view
4. Directory table

Correct Answer: 3

Explanation:

A materialized view maintains a stored representation of its query result that Snowflake can maintain automatically. This can improve performance for suitable repeated query patterns because consumers may avoid recomputing the complete underlying transformation each time. Materialized views are different from standard views, which store only the query definition. Engineers should evaluate workload patterns, maintenance overhead, supported SQL constructs, and performance requirements before choosing a materialized view.

Question 249

Which table type is automatically temporary for a session?

1. Permanent table
2. Transient table
3. Temporary table
4. External table

Correct Answer: 3

Explanation:

A temporary table exists only within the session in which it is created and is automatically removed when that session ends. Temporary tables are useful for intermediate processing, session-specific transformations, and short-lived analytical workloads. They differ from transient and permanent tables because their lifecycle is tied directly to the session. Engineers should avoid relying on temporary objects for data that must survive beyond the current session or be accessed by other sessions.

Question 250

Which table type omits Fail-safe storage?

1. Permanent table
2. Transient table
3. Hybrid table
4. Temporary table

Correct Answer: 2

Explanation:

Transient tables do not have the Fail-safe protection associated with permanent tables. They are intended for data that does not require the same long-term recovery characteristics. This can make transient tables useful for intermediate or reproducible datasets where extended recovery protection is unnecessary. Engineers should choose table types according to retention, recovery, governance, and workload requirements rather than using transient tables solely as a performance optimization.

Question 251

Which command removes all rows while retaining a table definition?

1. DELETE TABLE
2. CLEAR TABLE
3. TRUNCATE TABLE
4. EMPTY TABLE

Correct Answer: 4

Explanation:

TRUNCATE TABLE removes all rows from a table while retaining the table object and its structure. It is useful when a table needs to be emptied before being repopulated by a pipeline. This differs from dropping the table because the schema object remains available. Engineers should consider transaction behavior, downstream dependencies, and recovery requirements before truncating production tables because the operation removes the existing table contents.

Question 252

Which SQL construct filters rows after window calculations?

1. WHERE
2. QUALIFY
3. HAVING
4. FILTER

Correct Answer: 2

Explanation:

QUALIFY filters results after window functions have been evaluated. This makes it particularly useful for queries that rank, sequence, or otherwise calculate window values before selecting the required rows. For example, engineers can use ROW_NUMBER() together with QUALIFY to retain a single record per business key. This avoids nesting the window calculation inside another query solely for filtering purposes and can make analytical transformation SQL easier to read.

Question 253

Which function converts a string into a timestamp when parsing succeeds?

1. TO_TIMESTAMP
2. PARSE_TIMESTAMP_TEXT
3. STRING_TIMESTAMP
4. CAST_TIMESTAMP_TEXT

Correct Answer: 1

Explanation:

TO_TIMESTAMP converts an expression into a timestamp according to the supported input and conversion rules. It is commonly used when ingestion pipelines receive timestamps as strings and transformations need a proper temporal data type. Correct timestamp conversion is important for filtering, ordering, window calculations, and incremental processing. Engineers should account for input formats, time zones, and invalid values when designing robust timestamp transformations.

Question 254

Which function safely converts invalid values to NULL?

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

Correct Answer: 3

Explanation:

TRY_CAST performs a conversion and returns NULL when the conversion cannot be performed instead of raising a conversion error. This behavior is useful when data pipelines contain potentially malformed values and should continue processing valid records. Engineers can use TRY-based conversion functions to make ingestion and transformation logic more resilient. However, silently converting invalid values to NULL can hide source-quality problems, so pipelines should still include appropriate validation and monitoring.

Question 255

Which function extracts elements from semi-structured data into rows?

1. FLATTEN
2. EXPAND_JSON
3. UNNEST_OBJECT
4. ARRAY_ROWS

Correct Answer: 1

Explanation:

FLATTEN converts semi-structured data into a relational representation by producing rows for elements within arrays or objects. It is frequently used with VARIANT data when engineers need to transform nested JSON structures into queryable rows. FLATTEN exposes useful metadata such as sequence, key, path, index, and value. Data engineers can combine these outputs with lateral joins and filtering to build structured transformation pipelines from nested source data.

Question 256

Which data type can store semi-structured JSON-like values?

1. STRING
2. VARIANT
3. BINARY
4. NUMBER

Correct Answer: 2

Explanation:

VARIANT is designed to store semi-structured data such as JSON, XML, and other supported hierarchical values. It allows Snowflake to retain nested structures while providing SQL functionality for querying and transforming the contents. Data engineers frequently use VARIANT during ingestion before extracting selected attributes into relational columns. This approach can be useful when source schemas evolve or contain nested structures that are difficult to model immediately as fixed relational columns.

Question 257

Which function creates an object from named key-value pairs?

1. OBJECT_AGG
2. OBJECT_KEYS
3. OBJECT_CONSTRUCT
4. OBJECT_BUILD

Correct Answer: 3

Explanation:

OBJECT_CONSTRUCT creates an object from supplied key-value expressions. It is useful when engineers need to construct semi-structured objects during transformations or prepare structured payloads for downstream processing. OBJECT_AGG serves a different purpose by aggregating values into an object across rows. Understanding these distinctions helps data engineers choose the appropriate function when converting relational data into hierarchical representations.

Question 258

Which function aggregates values into an object by key?

1. OBJECT_KEYS
2. OBJECT_AGG
3. OBJECT_CONSTRUCT
4. OBJECT_VALUES

Correct Answer: 2

Explanation:

OBJECT_AGG aggregates key-value pairs into an object across rows. It is useful when a relational dataset needs to be transformed into a hierarchical representation where multiple records contribute properties to a single object. Data engineers can use it in reporting, API-oriented transformations, and semi-structured data preparation. The keys should be managed carefully because duplicate-key behavior and aggregation semantics need to match the intended transformation.

Question 259

Which function returns the keys from an object?

1. OBJECT_KEYS
2. OBJECT_FIELDS
3. GET_OBJECT_KEYS
4. OBJECT_NAMES

Correct Answer: 1

Explanation:

OBJECT_KEYS returns an array containing the keys from a Snowflake object. It is useful when processing semi-structured data whose attributes need to be inspected dynamically. Engineers can combine it with other semi-structured functions when building metadata-driven transformations or exploring variable JSON structures. Because the returned result is an array rather than a relational rowset, additional processing may be required when each key needs to become an individual row.

Question 260

Which function counts rows satisfying a Boolean condition?

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

Correct Answer: 3

Explanation:

COUNT_IF counts rows for which a specified condition evaluates to true. It is useful for conditional metrics such as counting successful records, failed validations, active entities, or records meeting a business threshold. Using COUNT_IF can simplify aggregation logic compared with manually constructing equivalent conditional expressions. Data engineers should ensure that the Boolean condition correctly represents the intended business rule and understand how NULL values affect the condition’s evaluation.