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

 

Question 221

Which stream metadata value identifies the type of change?

1. METADATA$ACTION
2. METADATA$CHANGE_TYPE
3. METADATA$EVENT_TYPE
4. METADATA$OPERATION

Correct Answer: 1

Explanation:

METADATA$ACTION identifies the change represented by a stream record. Its values indicate whether the record represents an insertion or deletion. This metadata is useful when processing change data because downstream logic can distinguish the type of modification without comparing complete table snapshots. Data engineers commonly use stream metadata together with the source columns when building incremental transformation or synchronization pipelines. Understanding these metadata columns is important when designing merge logic, audit processes, and downstream change-processing workflows.

Question 222

Which stream metadata column identifies update pairs?

1. METADATA$UPDATE_FLAG
2. METADATA$ISUPDATE
3. METADATA$MODIFIED
4. METADATA$CHANGE_PAIR

Correct Answer: 2

Explanation:

METADATA$ISUPDATE indicates whether a stream record is associated with an update operation. Snowflake represents updates through corresponding change records, allowing downstream consumers to distinguish update-related activity from ordinary inserts or deletes. This information can be particularly useful when constructing incremental processing logic. Engineers should examine both METADATA$ACTION and METADATA$ISUPDATE when processing streams because the two metadata values provide complementary information about the underlying change.

Question 223

Which stream metadata column provides a unique row identifier?

1. METADATA$RECORD_ID
2. METADATA$SOURCE_ID
3. METADATA$ROW_ID
4. METADATA$UNIQUE_KEY

Correct Answer: 3

Explanation:

METADATA$ROW_ID provides an identifier associated with a row captured by a stream. It can help data engineers distinguish individual change records when implementing downstream processing or auditing logic. Stream metadata is especially valuable because change processing often requires more context than the business columns alone provide. Engineers should understand the semantics of each metadata field rather than treating stream records as ordinary table rows. Combining row identifiers with action and update indicators can make incremental pipelines easier to reason about.

Question 224

Which option includes existing rows when creating a stream?

1. INCLUDE_EXISTING = TRUE
2. INITIAL_ROWS = TRUE
3. SHOW_INITIAL_ROWS = TRUE
4. CAPTURE_HISTORY = TRUE

Correct Answer: 3

Explanation:

SHOW_INITIAL_ROWS = TRUE can be specified when creating a stream to make existing rows available as the stream’s initial contents. This behavior can be useful when a downstream process needs an initial snapshot before continuing with subsequent changes. Without understanding the initialization behavior, engineers may incorrectly assume that a newly created stream automatically exposes all historical table rows. Stream initialization should therefore be considered carefully when designing bootstrap and incremental-processing workflows.

Question 225

Which command manually triggers processing for a task?

1. RUN TASK
2. EXECUTE TASK
3. START TASK NOW
4. TRIGGER TASK

Correct Answer: 2

Explanation:

EXECUTE TASK can be used to manually execute a task. This is useful when engineers need to test task logic, initiate processing outside the normal schedule, or troubleshoot an orchestration workflow. Manual execution does not eliminate the need to configure task dependencies and scheduling appropriately for regular production operation. Data engineers should distinguish manually executing a task from resuming a suspended task, because those actions serve different operational purposes within Snowflake task management.

Question 226

Which clause establishes a task dependency?

1. AFTER
2. DEPENDS_ON
3. PARENT_TASK
4. FOLLOWING

Correct Answer: 1

Explanation:

The AFTER clause establishes a dependency between tasks in a task graph. A child task can be configured to run after its predecessor completes according to the task graph’s execution rules. This allows data engineers to coordinate multi-step workflows without relying entirely on external orchestration software. Task dependencies are useful for sequencing ingestion, transformation, validation, and publishing operations. Engineers should design task graphs carefully so dependencies represent the actual processing order required by the data pipeline.

Question 227

Which task parameter sets an initial warehouse size for serverless execution?

1. SERVERLESS_WAREHOUSE_SIZE
2. USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE
3. TASK_INITIAL_COMPUTE
4. INITIAL_SERVERLESS_CLUSTER

Correct Answer: 2

Explanation:

USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE can specify the initial warehouse size for a serverless task when Snowflake determines managed compute resources. This setting can help influence the starting resource size for serverless task execution. Engineers should understand that serverless task sizing is managed by Snowflake rather than operating exactly like a user-managed virtual warehouse. Monitoring task execution and workload behavior remains important when tuning resource requirements and pipeline performance.

Question 228

Which task property defines a recurring schedule?

1. RUN_INTERVAL
2. EXECUTION_PERIOD
3. SCHEDULE
4. TASK_TIMER

Correct Answer: 3

Explanation:

The SCHEDULE property defines when a scheduled task should run. Snowflake supports interval-based and cron-style scheduling through this property. Scheduling is useful for pipelines that must execute automatically at defined times or intervals. Engineers should ensure that the selected schedule aligns with source-data availability and downstream dependencies. Time-zone behavior should also be considered when using cron expressions, particularly for workloads that operate across regions or have business schedules tied to a specific local time.

Question 229

Which task property specifies an error notification integration?

1. ERROR_INTEGRATION
2. TASK_ALERT
3. FAILURE_CHANNEL
4. ERROR_HANDLER

Correct Answer: 1

Explanation:

ERROR_INTEGRATION specifies the notification integration used for task error notifications. This provides a mechanism for connecting task failures with supported notification workflows. Error notifications are valuable for production data pipelines because scheduled processing can fail without a user actively watching the workload. Engineers should combine notifications with task history and operational monitoring so that alerts can be investigated and resolved rather than simply acknowledged.

Question 230

Which refresh mode performs complete dynamic table refreshes?

1. COMPLETE
2. FULL
3. TOTAL
4. REBUILD

Correct Answer: 2

Explanation:

The FULL refresh mode causes a dynamic table to be refreshed by recomputing the complete result rather than relying on incremental changes. This can be useful when the transformation or workload is not appropriate for incremental processing. Engineers should consider the computational implications because full refreshes can process substantially more data than incremental refreshes. Selecting a refresh mode should therefore reflect the transformation logic, data volume, freshness requirement, and available compute resources.

Question 231

Which dynamic table setting determines when it first initializes?

1. START_MODE
2. INITIALIZE
3. FIRST_REFRESH
4. BOOTSTRAP_MODE

Correct Answer: 2

Explanation:

The INITIALIZE setting controls when a dynamic table is initialized. Snowflake supports initialization behavior that can determine whether initialization occurs when the dynamic table is created or according to its scheduling behavior. This distinction can matter when deploying pipelines because initialization may consume significant resources for large datasets. Engineers should plan deployment order and dependencies so that initial population does not interfere with downstream processing or operational readiness.

Question 232

Which dynamic table refresh mode can automatically select a strategy?

1. AUTO
2. SMART
3. ADAPTIVE
4. FLEXIBLE

Correct Answer: 1

Explanation:

AUTO allows Snowflake to determine the refresh approach for a dynamic table based on supported capabilities and workload characteristics. This can reduce the need for engineers to explicitly select a refresh strategy during initial configuration. However, automatic selection does not remove the need for monitoring. Engineers should evaluate refresh behavior, freshness requirements, query complexity, and compute consumption to ensure that the resulting pipeline operates within its intended performance and cost expectations.

Question 233

Which command displays task definitions and properties?

1. DESCRIBE TASK
2. SHOW TASK
3. SHOW TASKS
4. LIST TASKS

Correct Answer: 3

Explanation:

SHOW TASKS returns information about tasks available within the relevant scope. It is useful when engineers need to inspect task definitions, states, ownership, schedules, or related properties. This command is particularly helpful when troubleshooting orchestration configurations or auditing deployed tasks. SHOW TASKS provides object metadata, while task history provides execution information. Using both perspectives allows engineers to distinguish configuration problems from runtime failures.

Question 234

Which command provides details about one task definition?

1. DESCRIBE TASK
2. INSPECT TASK
3. TASK DETAILS
4. SHOW TASK DEFINITION

Correct Answer: 1

Explanation:

DESCRIBE TASK provides details about a specific task definition. Engineers can use it to inspect task properties and configuration when troubleshooting or validating deployment settings. This is different from task execution history, which focuses on runs rather than the task’s stored definition. Reviewing the task definition is especially useful after changing schedules, dependencies, warehouse settings, or SQL logic because it confirms how the object is currently configured.

Question 235

Which command reveals task execution history?

1. TASK_HISTORY
2. EXECUTION_LOG
3. RUN_HISTORY
4. TASK_RUNS

Correct Answer: 1

Explanation:

TASK_HISTORY provides information about task executions and is useful for operational troubleshooting. Engineers can examine execution status, timing, task names, and other available metadata to investigate successful or failed runs. Task history is especially important in automated pipelines because a scheduled task may execute without a human observing it directly. Combining task-history information with query history and notification mechanisms can provide a broader view of pipeline behavior.

Question 236

Which function checks whether a stream contains change records?

1. SYSTEM$STREAM_HAS_DATA
2. SYSTEM$STREAM_READY
3. SYSTEM$STREAM_PENDING
4. SYSTEM$STREAM_CHANGES

Correct Answer: 1

Explanation:

SYSTEM$STREAM_HAS_DATA checks whether a stream contains data that indicates changes since its current offset. It can be used in task conditions so that downstream processing occurs only when relevant changes are available. This can reduce unnecessary task executions in event-driven pipelines. Engineers should understand that the function checks stream data availability and does not itself consume or advance the stream. Consumption occurs through statements that process the stream’s records.

Question 237

Which stream option captures only inserts for supported tables?

1. INSERT_ONLY
2. APPEND_ONLY
3. CAPTURE_INSERTS
4. INSERT_EVENTS

Correct Answer: 2

Explanation:

APPEND_ONLY = TRUE configures an append-only stream for supported source scenarios. Such streams focus on newly inserted rows rather than tracking the full set of update and delete changes. This can simplify change processing for workloads where source data is naturally append-only. Engineers should choose this option only when its semantics match the source workload because update and delete information will not be represented in the same manner as a standard stream.

Question 238

Which Snowpipe property enables cloud event-driven ingestion?

1. AUTO_INGEST
2. EVENT_TRIGGER
3. CLOUD_LISTENER
4. FILE_AUTORUN

Correct Answer: 1

Explanation:

AUTO_INGEST = TRUE enables Snowpipe to use supported cloud notification mechanisms for automated file ingestion. When new files arrive in the configured storage location, notifications can cause Snowpipe to process them without requiring an engineer to repeatedly issue loading commands. This architecture is useful for continuously arriving files. Proper cloud notification configuration, permissions, stage settings, and pipe definitions are necessary for reliable operation.

Question 239

Which command can refresh a Snowpipe pipe for staged files?

1. ALTER PIPE … REFRESH
2. REFRESH PIPE NOW
3. UPDATE PIPE FILES
4. PIPE REFRESH FILES

Correct Answer: 1

Explanation:

ALTER PIPE … REFRESH can be used to refresh a pipe so that eligible staged files can be considered for ingestion. This is useful in operational scenarios where files may already exist in a stage and need to be queued for Snowpipe processing. Engineers should understand the difference between automatic notification-driven ingestion and manually refreshing a pipe. Manual refreshes can be useful for recovery or operational workflows, but they should not replace a correctly configured continuous-ingestion architecture.

Question 240

Which Snowpipe Streaming feature tracks ingestion progress per channel?

1. Sequence counter
2. Offset token
3. Batch marker
4. Stream checkpoint

Correct Answer: 2

Explanation:

Snowpipe Streaming uses offset information to track ingestion progress for channels. Offset tokens can help applications identify the position of successfully ingested records and support reliable delivery semantics. This is especially important in streaming systems where applications continuously send records rather than producing complete files. Engineers designing Snowpipe Streaming pipelines should understand channel management, offset handling, error recovery, and producer behavior to maintain reliable ingestion across application restarts or transient failures.