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

 

Question 81

Which stream type tracks only inserted rows?

1. Standard stream
2. Append-only stream
3. Update stream
4. Change-table stream

Correct Answer: 2

Explanation:

An append-only stream in Snowflake tracks newly inserted rows without recording updates or deletes. This makes it useful for ingestion pipelines where source data is continuously appended and existing records are not modified. Compared with a standard stream, append-only streams can reduce change-tracking requirements for workloads focused exclusively on new records. Data engineers commonly use them with tasks to process incremental arrivals efficiently. Choosing the appropriate stream type depends on whether downstream processing needs inserts only or must also identify changes caused by updates and deletions.

Question 82

Which function checks whether a stream contains change data?

1. SYSTEM$STREAM_STATUS
2. SYSTEM$CHECK_STREAM
3. SYSTEM$STREAM_HAS_DATA
4. SYSTEM$DATA_CHANGED

Correct Answer: 3

Explanation:

SYSTEM$STREAM_HAS_DATA determines whether a specified stream contains change data that can be consumed. It is particularly useful in task definitions because a task can use the function in a WHEN condition to avoid unnecessary execution when no new changes exist. This pattern helps build event-driven incremental pipelines. Instead of running a transformation on every scheduled interval, the task can execute only when the stream indicates available changes. This reduces unnecessary compute usage and keeps downstream processing focused on actual source activity.

Question 83

What happens when a stream becomes stale?

1. Its unread changes become unavailable
2. Its table automatically becomes cloned
3. Its records move into Fail-safe
4. Its definition converts into a view

Correct Answer: 1

Explanation:

A Snowflake stream can become stale when its offset cannot be advanced within the applicable data-retention period. Once stale, the stream can no longer provide the expected historical change records that were waiting to be consumed. Downstream processing therefore cannot rely on the unavailable changes and may require reinitialization or another recovery approach. Data engineers should consume streams regularly or design pipelines that prevent excessive delays. Monitoring task execution, stream consumption, and retention settings helps reduce the possibility of losing the incremental change information represented by an outdated stream.

Question 84

Which setting lets a task run only when a condition evaluates true?

1. EXECUTE IF
2. RUN WHEN
3. WHEN
4. START CONDITION

Correct Answer: 3

Explanation:

The WHEN clause in a Snowflake task defines a Boolean condition that must evaluate to true before the task runs. It is commonly combined with system functions such as SYSTEM$STREAM_HAS_DATA for event-driven processing. For example, a scheduled task can check whether a stream contains changes and execute its SQL only when new data is available. This design avoids unnecessary transformations during idle periods. The WHEN condition is therefore useful for coordinating efficient incremental pipelines where execution should depend on the current state of upstream data.

Question 85

Which task configuration uses Snowflake-managed compute?

1. Serverless task execution
2. Dedicated warehouse binding
3. Manual cluster assignment
4. User-managed compute pool

Correct Answer: 1

Explanation:

Serverless tasks use Snowflake-managed compute rather than requiring the task definition to reference a user-managed virtual warehouse. Snowflake determines the resources needed for execution based on the task workload and service behavior. This can simplify operational management because engineers do not need to maintain a dedicated warehouse for each serverless task. Serverless execution is especially useful for intermittent workloads where maintaining a continuously available warehouse would be unnecessary. Task design should still consider execution frequency, workload size, and resource consumption when selecting between serverless and warehouse-based execution.

Question 86

Which clause defines a recurring task execution interval?

1. INTERVAL
2. SCHEDULE
3. FREQUENCY
4. RUN_PERIOD

Correct Answer: 2

Explanation:

The SCHEDULE clause specifies when a Snowflake task should execute. A schedule can define recurring execution using a time interval or a cron expression, depending on the required orchestration pattern. This allows engineers to automate ingestion, transformation, validation, or maintenance activities without manually starting each run. Scheduling can also be combined with a WHEN condition, allowing the task to check whether processing is actually necessary before execution. Proper scheduling is important for maintaining predictable pipeline behavior while avoiding excessive task invocations.

Question 87

Which option allows a task to execute without a warehouse?

1. Explicit warehouse assignment
2. Multi-cluster warehouse
3. Serverless compute
4. Dedicated compute tier

Correct Answer: 3

Explanation:

Serverless compute allows eligible Snowflake tasks to execute without specifying a user-managed virtual warehouse. Snowflake manages the underlying compute resources for the task. This model is useful for automated workloads where engineers prefer not to provision and maintain warehouse resources specifically for task execution. A task using serverless compute can still have scheduling and conditional execution logic. The choice between serverless execution and a user-managed warehouse depends on workload characteristics, operational preferences, and resource-management requirements.

Question 88

What does EXECUTE AS OWNER provide for a stored procedure?

1. Invoker-based privileges
2. Owner-context privileges
3. Session-only authorization
4. Role-selection prompts

Correct Answer: 2

Explanation:

EXECUTE AS OWNER causes a stored procedure to execute using the privileges associated with its owning role rather than the caller’s active privileges. This model can be useful when a controlled procedure should perform operations that callers themselves do not directly have permission to execute. Security-sensitive procedures should be designed carefully because owner rights can provide broader access than the invoking user possesses. Developers should limit procedure functionality, protect sensitive operations, and understand exactly which privileges are available through the owning role.

Question 89

What is the primary output of a user-defined function?

1. Warehouse configuration
2. Task dependency graph
3. Stage file listing
4. Computed value

Correct Answer: 4

Explanation:

A user-defined function, or UDF, is designed to accept inputs and return a computed value. UDFs are useful for encapsulating reusable business logic that can be invoked within SQL expressions. They differ from stored procedures, which are generally intended for procedural operations and can perform broader workflow activities. Data engineers can use UDFs to standardize calculations, transformations, or reusable expressions across queries. Selecting a UDF is appropriate when the requirement centers on producing a value from supplied arguments rather than orchestrating a multi-step operational process.

Question 90

Which metadata column identifies the source filename?

1. METADATA$FILENAME
2. METADATA$SOURCE
3. METADATA$FILEPATH
4. METADATA$OBJECT_NAME

Correct Answer: 1

Explanation:

METADATA$FILENAME provides the name of the source file associated with staged data during supported file-query and loading workflows. It can help data engineers trace records back to their originating files, build ingestion auditing logic, or retain source-file information during transformations. Metadata columns are especially useful when multiple files are processed through a common pipeline. Capturing the filename can support troubleshooting and reconciliation by allowing engineers to identify which staged object contributed particular records.

Question 91

Which option controls how COPY INTO handles row-level load errors?

1. ERROR_POLICY
2. ON_ERROR
3. FAILURE_MODE
4. LOAD_EXCEPTION

Correct Answer: 2

Explanation:

The ON_ERROR option controls how Snowflake handles errors encountered while loading data with COPY INTO. Depending on the selected behavior, Snowflake can stop the statement, continue processing, or skip problematic files under supported settings. This option is important when designing resilient ingestion pipelines because error handling determines whether one problematic record or file interrupts broader ingestion. Engineers should select an appropriate strategy based on data-quality requirements and downstream reconciliation processes rather than simply allowing malformed input to pass unnoticed.

Question 92

What does INFER_SCHEMA primarily determine?

1. Warehouse sizing
2. Task dependencies
3. File column structure
4. Stream retention

Correct Answer: 3

Explanation:

INFER_SCHEMA examines supported staged files and derives structural information such as column names and data types. It is useful when building tables from semi-structured or structured files whose schema needs to be discovered automatically. Data engineers can combine schema inference with mechanisms such as USING TEMPLATE to simplify table creation. This approach reduces manual schema-definition work and can be valuable in ingestion environments where file structures are known but must be translated into Snowflake table definitions.

Question 93

Which feature supports automatic column additions during loading?

1. Schema evolution
2. Static column mapping
3. Manual table revision
4. Fixed-schema loading

Correct Answer: 1

Explanation:

Snowflake schema evolution can allow supported loading workflows to automatically accommodate certain source-schema changes, such as newly appearing columns, when the required configuration and privileges are in place. This capability is useful for ingestion pipelines where upstream producers may add fields over time. It reduces the need for manual table alterations for every compatible schema addition. Engineers should still establish controls around evolving structures because automatic changes can affect downstream transformations, validation rules, reporting logic, and data contracts.

Question 94

Which metadata function records the row position within a source file?

1. METADATA$ROW_ID
2. METADATA$POSITION
3. METADATA$FILE_ROW_NUMBER
4. METADATA$LINE_INDEX

Correct Answer: 3

Explanation:

METADATA$FILE_ROW_NUMBER identifies the row number associated with a record within its source file during supported staged-file querying and loading scenarios. This metadata can assist with troubleshooting, source tracing, and duplicate investigation. When combined with the filename metadata, engineers can obtain useful source-level identifiers for incoming records. Such information can be especially helpful when investigating malformed records or reconciling source files against loaded table contents. Metadata columns should be used according to the specific ingestion operation and supported file-processing behavior.

Question 95

What does ZERO-COPY CLONING primarily avoid?

1. Query compilation
2. Data duplication
3. Role inheritance
4. Metadata tracking

Correct Answer: 2

Explanation:

Zero-copy cloning creates a new database object without immediately making a separate physical copy of all underlying data. The clone initially references the existing micro-partition data, while subsequent changes can cause new storage to be created as needed. This makes cloning useful for development, testing, experimentation, and environment preparation. Engineers can create isolated objects quickly without initially duplicating the entire dataset. Although cloning avoids immediate full physical duplication, storage can increase later as the source and clone diverge through modifications.

Question 96

Which command restores a dropped object within retention limits?

1. RESTORE
2. RECOVER
3. UNDROP
4. REINSTATE

Correct Answer: 3

Explanation:

The UNDROP command restores a supported dropped Snowflake object when it remains recoverable under the applicable Time Travel retention period. This capability can help recover from accidental object deletion without reconstructing the object manually. Recovery availability depends on the object’s type and retention conditions. Data engineers should understand the distinction between Time Travel and longer-term recovery mechanisms because retention windows are finite. Using UNDROP is therefore an operational recovery technique rather than a substitute for proper backup, replication, or disaster-recovery planning.

Question 97

Which function retrieves a previous query’s result set?

1. PREVIOUS_RESULT
2. QUERY_OUTPUT
3. RESULT_SCAN
4. RESULT_FETCH

Correct Answer: 3

Explanation:

RESULT_SCAN allows Snowflake users to access the result set produced by a previous query. It can work with a query identifier or related result reference, making it useful when subsequent SQL needs to process an earlier query’s output. This can support administrative workflows, metadata analysis, and multi-step SQL processing without rerunning the original statement. Because query-result availability has applicable limits, engineers should design workflows with the relevant session and result-retention behavior in mind when relying on previous query outputs.

Question 98

Which function returns the identifier of a previous query?

1. LAST_QUERY_ID
2. CURRENT_QUERY_ID
3. PREVIOUS_STATEMENT_ID
4. QUERY_REFERENCE

Correct Answer: 1

Explanation:

LAST_QUERY_ID() returns the identifier associated with a previously executed query in the current session context. It is commonly useful with RESULT_SCAN, where the query identifier can be used to reference a prior result set. This enables multi-step workflows in which one statement produces information and a subsequent statement processes that output. Query identifiers are also useful when troubleshooting or examining execution history. Engineers should understand session context because query identifiers and accessible results depend on how and where the statements were executed.

Question 99

Which policy controls which rows a user can see?

1. Column masking policy
2. Object tagging policy
3. Row access policy
4. Encryption enforcement policy

Correct Answer: 3

Explanation:

A row access policy controls whether particular rows are visible to a querying user or role based on defined policy logic. Unlike column masking, which modifies or hides column values, row access policies filter the records available to the requester. This makes them useful for implementing data-isolation requirements such as regional, departmental, or tenant-specific visibility. Data engineers should design the policy conditions carefully and test them across relevant roles because incorrect row-filtering logic can expose too much data or unintentionally restrict legitimate access.

Question 100

What does COPY INTO’s PATTERN option filter?

1. Destination columns
2. Source filenames
3. Warehouse clusters
4. Target partitions

Correct Answer: 2

Explanation:

The PATTERN option in COPY INTO uses a regular expression to select staged files whose names match the specified pattern. This allows ingestion pipelines to process only the intended subset of files from a stage. For example, a pipeline can target files belonging to a particular naming convention, date, or data domain without loading unrelated objects. Careful pattern design is important because an overly broad expression can ingest unintended files, while an overly restrictive pattern can leave expected files unprocessed.