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

 

Question 121

Which command removes a table while preserving its recoverability?

1. DROP TABLE
2. DELETE TABLE
3. REMOVE TABLE
4. PURGE TABLE

Correct Answer: 1

Explanation:

DROP TABLE removes the table object while allowing recovery through Snowflake’s retention mechanisms when the object remains within the applicable recovery period. This differs from operations that delete individual rows because the table object itself is removed. Data engineers should distinguish between dropping an object and permanently eliminating recoverable data. Understanding Snowflake’s retention behavior helps teams design safer operational procedures and provides an opportunity to recover accidentally dropped objects when the necessary retention conditions are still available.

Question 122

Which statement permanently removes a table without Time Travel recovery?

1. DROP TABLE
2. DROP TABLE … PURGE
3. DELETE FROM
4. TRUNCATE TABLE

Correct Answer: 2

Explanation:

DROP TABLE … PURGE permanently removes a supported table and bypasses the normal Time Travel recovery capability for that object. This makes it substantially different from a regular DROP TABLE, where the object may remain recoverable during the applicable retention period. Engineers should use permanent removal carefully because the ability to recover accidentally deleted data is reduced or eliminated. Before performing irreversible cleanup, teams should verify dependencies, retention requirements, and any organizational recovery policies associated with the data.

Question 123

Which Snowflake capability supports restoring dropped objects within retention?

1. Fail-safe
2. Time Travel
3. Data Exchange
4. Query Acceleration

Correct Answer: 2

Explanation:

Time Travel preserves historical data and object states for supported recovery operations during the configured retention period. It enables capabilities such as querying earlier versions of data and recovering certain dropped objects. Data engineers can use Time Travel to investigate accidental changes, compare historical states, or restore objects when recovery conditions are satisfied. Time Travel should not be confused with Fail-safe, which serves a different recovery purpose and is not intended as a normal user-controlled historical query mechanism.

Question 124

Which clause queries a table at an earlier historical point?

1. HISTORY AT
2. AS OF
3. PREVIOUS VERSION
4. TIMEPOINT

Correct Answer: 2

Explanation:

Snowflake’s AT clause is used with Time Travel syntax to reference historical data at a specified timestamp, offset, or statement-related point. It allows engineers to inspect an earlier state of a table without modifying the current table contents. Historical queries are valuable for investigating data changes, recovering information, and validating pipeline behavior. The requested historical point must remain within the applicable retention period. Data engineers should choose the appropriate timestamp or statement reference based on the incident or analytical requirement being investigated.

Question 125

Which clause identifies a point immediately before a specified statement?

1. BEFORE
2. PRIOR
3. EARLIER
4. PREVIOUS

Correct Answer: 1

Explanation:

The BEFORE clause can be used with Snowflake Time Travel to reference data as it existed before a specified statement or timestamp-related point. This is useful when engineers need to inspect the state of a table immediately preceding a particular operation. Historical access can support troubleshooting, validation, and recovery workflows without altering the current object. When investigating an incident, selecting the correct statement identifier or timestamp is important because the historical result must correspond precisely to the state the engineer wants to examine.

Question 126

Which feature provides historical access to changed table data?

1. Fail-safe
2. Time Travel
3. Secure Data Share
4. Search Optimization

Correct Answer: 2

Explanation:

Time Travel provides access to historical versions of supported Snowflake data within the applicable retention period. It can help engineers investigate accidental updates, deletions, or other changes by allowing historical queries against earlier table states. This capability is valuable for data engineering because pipelines can sometimes produce unexpected transformations or load results. Rather than modifying the current dataset immediately, engineers can inspect historical information first. Retention configuration determines how far back historical data can be accessed, so appropriate settings should reflect operational recovery requirements.

Question 127

Which object is designed to expose queryable data from external files?

1. External table
2. Dynamic table
3. Temporary table
4. Materialized view

Correct Answer: 1

Explanation:

An external table represents data stored outside Snowflake while providing metadata that allows the external data to be queried from Snowflake. It is commonly used with data stored in supported cloud storage locations. Unlike a standard Snowflake table, the underlying records remain in the external storage system. External tables are useful in architectures where data should remain in a data lake while still being accessible through Snowflake SQL. Engineers should account for metadata refresh and external storage organization when designing these pipelines.

Question 128

Which command refreshes metadata for an external table?

1. ALTER EXTERNAL TABLE … REFRESH
2. REFRESH EXTERNAL TABLE
3. UPDATE EXTERNAL METADATA
4. SYNC EXTERNAL TABLE

Correct Answer: 1

Explanation:

ALTER EXTERNAL TABLE … REFRESH can refresh the metadata associated with an external table so Snowflake can recognize applicable changes in the referenced external storage. This is important when new files are added or the external data layout changes. External tables rely on metadata describing files rather than loading all underlying records into native Snowflake storage. Data engineers should establish an appropriate refresh strategy so newly available source files become visible to queries without unnecessary metadata operations.

Question 129

Which stage type is managed internally by Snowflake?

1. External stage
2. Internal stage
3. Cloud stage
4. Remote stage

Correct Answer: 2

Explanation:

An internal stage is storage managed within Snowflake for staging files before loading or after unloading data. Internal stages can simplify data movement because engineers do not need to configure an external cloud-storage location for every staging workflow. External stages, by comparison, reference supported cloud storage locations outside Snowflake. Choosing between stage types depends on architecture, data-transfer requirements, governance, and integration needs. Internal stages are especially convenient for workflows where files need to be temporarily or directly staged within the Snowflake environment.

Question 130

Which stage type references storage outside Snowflake?

1. User stage
2. Table stage
3. External stage
4. Named internal stage

Correct Answer: 3

Explanation:

An external stage points to data stored in an external cloud-storage location supported by Snowflake. It can centralize connection details and file-location configuration for repeated loading or unloading operations. External stages are common in modern data-lake architectures because source files can remain in object storage while Snowflake accesses them through configured integrations or credentials. Engineers should secure external-stage access carefully and ensure storage permissions, integration settings, and file paths are aligned with the intended ingestion workflow.

Question 131

Which identifier refers to files within a stage during COPY operations?

1. FILES
2. OBJECTS
3. SOURCES
4. INPUT_SET

Correct Answer: 1

Explanation:

The FILES option in COPY INTO allows engineers to specify particular staged files for processing. This can be useful when a pipeline needs to load a controlled subset of files rather than everything matching a broader stage selection. Explicit file selection can support targeted reprocessing, troubleshooting, or controlled ingestion. Engineers should understand that file-selection behavior interacts with Snowflake’s load metadata and duplicate-file tracking. Careful file management helps prevent unintended reloads while allowing legitimate recovery or replay operations.

Question 132

Which COPY option uses a regular expression to select files?

1. FILTER
2. PATTERN
3. REGEX_FILES
4. MATCH_FILES

Correct Answer: 2

Explanation:

The PATTERN option in COPY INTO uses a regular expression to identify staged files that should be processed. This is useful when files follow predictable naming conventions such as dates, source identifiers, or data domains. Engineers can use patterns to narrow ingestion without manually listing every file. However, regular expressions should be tested carefully because an overly broad expression may include unwanted files, while an overly restrictive expression may exclude valid input. Good naming conventions make pattern-based ingestion easier to maintain.

Question 133

Which COPY option removes successfully loaded files from a stage?

1. DELETE_AFTER_LOAD
2. CLEANUP
3. PURGE
4. REMOVE_LOADED

Correct Answer: 3

Explanation:

The PURGE option can remove successfully loaded files from the stage after a COPY INTO operation. This can help manage staged-file storage when source files no longer need to remain available after successful ingestion. Engineers should use this behavior carefully because deleting staged files can affect replay, auditing, or troubleshooting workflows. It is particularly important to establish whether another process or downstream recovery procedure depends on the original files before enabling automatic cleanup.

Question 134

Which COPY option validates data without loading it into the target table?

1. VALIDATION_MODE
2. CHECK_ONLY
3. DRY_RUN
4. LOAD_VALIDATE

Correct Answer: 1

Explanation:

VALIDATION_MODE allows engineers to validate files for loading-related errors without actually loading the data into the target table. This is useful when assessing source quality before committing a batch to production tables. Depending on the selected validation behavior, Snowflake can return information about records or files that would cause errors. Validation is valuable in ingestion pipelines because it can identify malformed input before it affects downstream data. Engineers should choose an appropriate validation strategy based on file volume and operational requirements.

Question 135

Which load-history view records COPY activity for recent table loads?

1. LOAD_HISTORY
2. TABLE_LOAD_LOG
3. COPY_EVENTS
4. INGESTION_HISTORY

Correct Answer: 1

Explanation:

LOAD_HISTORY provides information about loading activity for tables within the applicable information scope and retention behavior. It can help data engineers determine which files were processed, when loading occurred, and whether records were loaded successfully. Load-history information is valuable for troubleshooting ingestion failures, auditing pipeline activity, and investigating unexpected duplicates or missing data. Engineers should select the appropriate Snowflake metadata source based on the required scope, retention period, and operational reporting needs.

Question 136

Which metadata field identifies when a scanned file began processing?

1. METADATA$SCAN_TIME
2. METADATA$START_SCAN_TIME
3. METADATA$BEGIN_TIME
4. METADATA$FILE_START

Correct Answer: 2

Explanation:

METADATA$START_SCAN_TIME provides metadata associated with when Snowflake began scanning the source file for the relevant operation. Such metadata can help with source tracing, diagnostics, and ingestion analysis when querying supported staged files. Combining scan-time information with filename and row metadata can provide useful context for understanding how records entered a pipeline. Engineers should remember that metadata columns are associated with supported file-querying scenarios and should not assume every metadata field is available in every Snowflake operation.

Question 137

Which object coordinates SQL actions according to dependencies?

1. Stream
2. Task graph
3. File format
4. External table

Correct Answer: 2

Explanation:

A task graph coordinates multiple Snowflake tasks through defined predecessor and successor relationships. This allows data engineers to organize multi-step workflows such as ingestion, validation, transformation, aggregation, and downstream publishing. Dependencies ensure that a later task does not execute until its required upstream task has completed according to the graph’s orchestration rules. Task graphs are useful for complex pipelines because they provide structured execution relationships rather than requiring every transformation to run independently on unrelated schedules.

Question 138

Which task property permits overlapping scheduled executions?

1. ALLOW_OVERLAPPING_EXECUTION
2. ENABLE_PARALLEL_RUNS
3. OVERLAP_TASKS
4. CONCURRENT_SCHEDULE

Correct Answer: 1

Explanation:

ALLOW_OVERLAPPING_EXECUTION controls whether scheduled executions of a task can overlap under the supported task behavior. This setting matters when a task may take longer to complete than the interval between scheduled runs. Allowing overlap can increase concurrency, while preventing overlap can ensure that a new execution does not begin while an earlier execution remains active. Engineers should evaluate workload characteristics carefully because overlapping executions can introduce competing writes, duplicate processing, resource contention, or other pipeline-coordination concerns.

Question 139

Which command suspends a running task schedule?

1. DISABLE TASK
2. ALTER TASK … SUSPEND
3. STOP TASK
4. PAUSE TASK

Correct Answer: 2

Explanation:

ALTER TASK … SUSPEND changes a task so that its scheduled execution is suspended. This is useful when engineers need to temporarily stop automated processing during maintenance, incident investigation, deployment changes, or dependency updates. Suspending a task does not mean that the task definition itself is removed. After required work is completed, the task can be resumed using the corresponding task-management operation. Engineers should consider downstream and upstream dependencies before suspending production orchestration components.

Question 140

Which Snowflake feature enables cross-region database replication?

1. Data Marketplace
2. Replication
3. Search Optimization
4. Dynamic Tables

Correct Answer: 2

Explanation:

Snowflake replication supports copying supported databases and related objects between Snowflake accounts and regions according to configured replication architecture. It can be used to support business continuity, disaster-recovery, and geographically distributed data-access requirements. Replication differs from data sharing because sharing provides governed access to data without the same objective of maintaining a replicated database copy. Data engineers designing cross-region architectures should evaluate supported objects, replication schedules, failover requirements, and consistency expectations before implementing the solution.