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

 

Question 201

Which Snowflake feature automates continuous file ingestion?

1. Dynamic Table
2. Materialized View
3. Snowpipe
4. Search Optimization

Correct Answer: 3

Explanation:

Snowpipe provides continuous, automated loading of files from supported stages into Snowflake tables. Instead of requiring engineers to execute recurring bulk COPY INTO statements manually, Snowpipe can process newly arrived files through an automated ingestion architecture. It is particularly useful for workloads where files arrive frequently throughout the day. Data engineers should design Snowpipe pipelines with appropriate file organization, notification mechanisms, error handling, and monitoring. Snowpipe is intended for continuous ingestion scenarios rather than replacing every type of scheduled bulk-loading workflow.

Question 202

Which service enables continuous data ingestion through streaming rows?

1. Snowpipe Streaming
2. External Tables
3. Dynamic Tables
4. Materialized Views

Correct Answer: 1

Explanation:

Snowpipe Streaming allows applications to ingest rows directly into Snowflake without first staging complete files for traditional bulk loading. This makes it suitable for low-latency streaming workloads where records become available continuously. Applications can send data through supported Snowpipe Streaming interfaces, allowing ingestion pipelines to reduce file-generation dependencies. Engineers should distinguish this approach from standard Snowpipe, which is primarily file-based. The appropriate ingestion technology depends on source behavior, latency requirements, throughput expectations, and the architecture of the upstream producing system.

Question 203

Which object defines notification and access configuration for Snowpipe automation?

1. Pipe
2. Stream
3. Task
4. Procedure

Correct Answer: 1

Explanation:

A Snowflake pipe contains the definition used by Snowpipe to load incoming files into a target table. Its definition commonly includes the COPY INTO statement that specifies how staged files should be processed. Pipes can also participate in automated ingestion architectures involving cloud notification services. Data engineers should treat pipes as ingestion components rather than transformation objects. The pipe’s target, file format, stage, and loading logic should be designed consistently with the source-file conventions to avoid missed or incorrectly processed files.

Question 204

Which function can retrieve whether a pipe has pending files?

1. SYSTEM$PIPE_STATUS
2. SYSTEM$PIPE_PENDING
3. PIPE_STATUS_CHECK
4. SYSTEM$PIPE_FILES

Correct Answer: 1

Explanation:

SYSTEM$PIPE_STATUS provides status information about a Snowpipe pipe, including information useful for understanding ingestion activity and pending work. This can help engineers troubleshoot automated file ingestion and monitor whether a pipe is actively processing source files. Operational monitoring is important because ingestion problems may originate from notification configuration, stage access, source-file formatting, or pipe state. Engineers can combine pipe status information with load-history and other metadata to investigate delays or failures in continuous ingestion workflows.

Question 205

Which Snowflake view records historical Snowpipe activity?

1. PIPE_HISTORY
2. PIPE_USAGE_HISTORY
3. SNOWPIPE_LOG
4. INGESTION_PIPE_LOG

Correct Answer: 2

Explanation:

PIPE_USAGE_HISTORY provides historical usage information associated with Snowpipe activity. Such metadata can help engineers analyze ingestion behavior, investigate operational patterns, and monitor Snowpipe usage over time. Historical usage information is different from the operational status of an individual pipe, which is useful for understanding its current state. Data engineering teams can use account-level monitoring views alongside pipe-status functions to build broader observability around continuous ingestion pipelines and identify unusual ingestion patterns.

Question 206

Which stage metadata identifies the relative row number within a file?

1. METADATA$FILE_ROW_NUMBER
2. METADATA$ROW_POSITION
3. METADATA$RECORD_INDEX
4. METADATA$SOURCE_ROW

Correct Answer: 1

Explanation:

METADATA$FILE_ROW_NUMBER provides the row number associated with a record in supported staged-file queries. This metadata can help engineers trace records back to their source position when investigating ingestion issues. When combined with METADATA$FILENAME, it provides useful source-level context for individual records. Such metadata can support auditing, troubleshooting, and duplicate analysis. Engineers should remember that these metadata columns describe staged-file processing and should be used within the Snowflake operations where the relevant metadata is available.

Question 207

Which stage component exposes file metadata through a queryable structure?

1. Directory table
2. Stream
3. Task graph
4. Dynamic table

Correct Answer: 1

Explanation:

A directory table provides metadata about files in a stage, such as file paths and other available file-related information. It can help data engineers build workflows that need to inspect staged-file contents or determine which files are available before processing them. Directory tables are associated with stages and are particularly useful in architectures requiring metadata-driven file management. Engineers should distinguish directory metadata from the actual file contents because the directory table describes staged objects rather than materializing the source records as relational table rows.

Question 208

Which integration allows Snowflake to access cloud storage securely?

1. Storage integration
2. File connection
3. Cloud bridge
4. Stage connector

Correct Answer: 1

Explanation:

A storage integration defines a secure Snowflake configuration for accessing supported external cloud storage services. It can centralize identity and access configuration instead of embedding long-lived cloud credentials directly in individual stages. External stages can reference the integration when accessing supported storage locations. This approach improves manageability and helps standardize access across ingestion pipelines. Engineers should configure cloud-side permissions and Snowflake integration settings together so that the Snowflake role and external storage authorization correctly support the intended data movement operations.

Question 209

Which Snowflake object connects a remote service to SQL through an API?

1. External function
2. Remote procedure
3. API table
4. Service view

Correct Answer: 1

Explanation:

An external function allows Snowflake SQL to invoke a remote service through a configured integration and API endpoint. This can extend data-processing workflows when specialized logic or services exist outside Snowflake. External functions are useful for controlled interactions with external applications or services, but they introduce network dependencies and potentially additional latency. Engineers should consider reliability, authentication, data-transfer requirements, and failure handling before incorporating external function calls into critical transformation pipelines.

Question 210

Which object controls permitted network locations for external access?

1. Network rule
2. URL policy
3. Endpoint table
4. Remote address profile

Correct Answer: 1

Explanation:

A network rule defines network identifiers such as hostnames, IP addresses, or other supported network locations that can be referenced by Snowflake security and connectivity configurations. Network rules are useful when external access needs to be restricted to explicitly approved destinations. They can work with other Snowflake objects involved in external network access. Data engineers should keep network rules narrowly scoped and review them regularly because broad network permissions can expose workloads to unintended external destinations.

Question 211

Which object grants external network access to supported Snowflake code?

1. External access integration
2. Network gateway
3. Remote access policy
4. API authorization object

Correct Answer: 1

Explanation:

An external access integration provides the authorization framework that allows supported Snowflake code, such as certain procedures or functions, to access external network locations. It works with network rules and, where required, secrets to establish controlled external connectivity. This allows engineers to define which destinations and credentials can be used rather than allowing unrestricted outbound communication. External access should be designed carefully because network connectivity introduces dependencies outside Snowflake and may require additional monitoring and security controls.

Question 212

Which object stores credentials for external service authentication?

1. Secret
2. Credential table
3. Password object
4. Authentication stage

Correct Answer: 1

Explanation:

A Snowflake secret is designed to securely store sensitive authentication information used by supported integrations and code. Secrets can contain credentials such as passwords, tokens, or other authentication material depending on the supported secret type. Using secrets avoids embedding sensitive values directly in procedural or function code. Data engineers should apply appropriate ownership and access controls because a secret can provide access to an external resource. External access integrations can reference approved secrets when configuring controlled outbound connectivity.

Question 213

Which feature enables controlled sharing of a database object with another account?

1. Secure Data Sharing
2. External Stage Access
3. Database Export
4. Account Copy

Correct Answer: 1

Explanation:

Secure Data Sharing allows supported Snowflake data to be shared with another Snowflake account without requiring the provider to create a conventional copy of the shared data. Providers can expose selected databases, schemas, tables, or other supported objects through controlled sharing mechanisms. This architecture is useful for data collaboration while allowing the provider to maintain governance over the source. Engineers should distinguish sharing from replication because the two capabilities solve different architectural requirements and have different operational models.

Question 214

Which Snowflake object represents a collection of databases for replication management?

1. Replication group
2. Database bundle
3. Failover group
4. Recovery cluster

Correct Answer: 3

Explanation:

A failover group is used to organize supported databases and related objects for replication and failover management across Snowflake accounts or regions. This allows organizations to design broader business-continuity architectures rather than managing every replicated component independently. Failover groups can support coordinated replication and failover operations for supported objects. Engineers should evaluate object support, replication direction, privileges, and failover requirements when designing cross-region recovery architectures.

Question 215

Which command changes the active primary database role during failover?

1. SWITCH PRIMARY
2. FAILOVER
3. PROMOTE DATABASE
4. ACTIVATE SECONDARY

Correct Answer: 2

Explanation:

Failover operations allow a configured secondary environment to become the active primary according to Snowflake’s replication and failover architecture. This capability supports disaster-recovery and business-continuity scenarios when the primary environment is unavailable or intentionally being changed. Engineers should plan failover procedures before an incident occurs, including permissions, application connection changes, replication status, and validation steps. A failover should be treated as an operational recovery event requiring testing and documented procedures rather than an ad hoc administrative action.

Question 216

Which command restores the original primary after a failover?

1. FAILBACK
2. REVERT PRIMARY
3. RESTORE PRIMARY
4. RETURN PRIMARY

Correct Answer: 1

Explanation:

Failback refers to returning operations to the original primary environment after a failover scenario has been resolved and the necessary synchronization or recovery procedures have been completed. A well-designed disaster-recovery process should define when failback is appropriate and how data consistency will be verified. Engineers should not treat failback as merely switching a connection back because changes may have occurred while the secondary environment was serving as primary. Careful sequencing and validation help prevent data divergence during recovery operations.

Question 217

Which view helps inspect recently executed SQL statements?

1. QUERY_HISTORY
2. SQL_ACTIVITY
3. STATEMENT_LOG
4. EXECUTION_RECORDS

Correct Answer: 1

Explanation:

QUERY_HISTORY provides information about executed queries and their execution characteristics. It is useful for troubleshooting, performance analysis, operational monitoring, and identifying workload patterns. Data engineers can inspect query text, timing, status, and other available metadata to understand how pipelines and users interact with Snowflake. Query history is especially useful when investigating slow transformations or failed SQL operations. Engineers should select the appropriate history scope and time range when building monitoring or diagnostic workflows.

Question 218

Which account-level view helps analyze object access and data lineage?

1. ACCESS_HISTORY
2. OBJECT_LINEAGE
3. DATA_ACCESS_LOG
4. SECURITY_HISTORY

Correct Answer: 1

Explanation:

ACCESS_HISTORY provides information about access to data and can help organizations understand how objects and columns are being used. It is valuable for governance, auditing, compliance analysis, and lineage-oriented investigations. Data engineers can use access information to identify relationships between workloads and sensitive data objects. Because access metadata can be detailed, teams should design monitoring queries carefully and consider the relevant retention and account-level availability when building governance reporting.

Question 219

Which loading option continues processing after certain file errors?

1. ON_ERROR = CONTINUE
2. ERROR_MODE = IGNORE
3. SKIP_ERRORS = TRUE
4. CONTINUE_ON_FAILURE = YES

Correct Answer: 1

Explanation:

ON_ERROR = CONTINUE instructs supported COPY INTO operations to continue processing after certain load errors rather than immediately terminating the entire statement. This can be useful when pipelines must process as much valid source data as possible while identifying problematic records for later remediation. Engineers should use this behavior carefully because continuing after errors can result in partial ingestion. Operational monitoring and error reporting should accompany permissive loading strategies so that failed records are not silently overlooked.

Question 220

Which COPY setting prevents loading when column counts differ?

1. ERROR_ON_COLUMN_COUNT_MISMATCH
2. COLUMN_COUNT_STRICT
3. REQUIRE_COLUMN_MATCH
4. FAIL_COLUMN_VARIANCE

Correct Answer: 1

Explanation:

ERROR_ON_COLUMN_COUNT_MISMATCH controls whether a load should raise an error when the number of columns in an input record does not match the expected table structure under the applicable loading conditions. This can help detect malformed or unexpectedly structured source files. Strict column-count validation is valuable when source schemas are expected to remain stable. Engineers should consider how the setting interacts with schema evolution, missing fields, extra fields, and other file-format configurations before applying it to automated ingestion pipelines.