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

 

Question 161

Which Snowflake object captures row-level changes for incremental processing?

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

Correct Answer: 4

Explanation:

A stream captures change information from a supported source object, allowing downstream processes to identify inserted, updated, or deleted records. This makes streams valuable for incremental data pipelines where processing the entire source repeatedly would be inefficient. A stream maintains an offset representing the changes available for consumption. Engineers commonly combine streams with tasks to build automated change-processing workflows. The appropriate stream type depends on whether the pipeline needs inserts only or also requires information about updates and deletes.

Question 162

Which privilege permits inserting rows into a table?

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

Correct Answer: 1

Explanation:

The INSERT privilege allows a role to insert rows into a table. Snowflake’s role-based access control separates object privileges according to the operations users or roles are permitted to perform. Data engineers should grant only the permissions required by a pipeline or application rather than providing unnecessarily broad access. For ingestion workflows, the executing role may need additional privileges on the database, schema, stage, or warehouse. Understanding privilege dependencies helps prevent authorization failures while maintaining controlled access to production data.

Question 163

Which command changes a column’s data type when supported?

1. MODIFY COLUMN TYPE
2. CHANGE COLUMN
3. ALTER TABLE
4. UPDATE COLUMN TYPE

Correct Answer: 3

Explanation:

ALTER TABLE is the command used for supported table-definition changes, including applicable column alterations. The exact syntax depends on the specific modification and Snowflake’s rules for converting between data types. Engineers should verify that existing values are compatible before changing a column’s type. Schema changes can affect views, procedures, applications, ingestion mappings, and downstream transformations. Therefore, production column-type changes should be tested against representative data and dependent objects before being applied broadly.

Question 164

Which object stores reusable SQL transformation logic that returns a value?

1. Stored procedure
2. User-defined function
3. Task
4. Stream

Correct Answer: 2

Explanation:

A user-defined function encapsulates reusable logic that produces a return value from supplied arguments. UDFs are useful for calculations, transformations, validation logic, and repeated expressions that need to be called from SQL statements. A stored procedure is generally better suited to procedural workflows and operations involving multiple statements or actions. Data engineers should choose the object based on the required behavior: value-oriented reusable logic is commonly represented by a function, while orchestration or procedural operations may be better suited to procedures.

Question 165

Which Snowflake capability helps accelerate queries using cached results?

1. Search Optimization
2. Automatic Clustering
3. Query Acceleration
4. Result caching

Correct Answer: 4

Explanation:

Result caching allows Snowflake to reuse eligible results from previously executed queries rather than recomputing the same result. When applicable conditions are satisfied, this can reduce both execution time and compute consumption. Result caching differs from warehouse cache and optimization services because it reuses a previously generated query result. Engineers should not design pipelines assuming every query will always benefit from cached results, since changes to data, query text, session conditions, or other factors can affect cache reuse.

Question 166

Which privilege allows querying data from a table?

1. SELECT
2. READ_DATA
3. QUERY
4. VIEW_ROWS

Correct Answer: 1

Explanation:

The SELECT privilege allows a role to query data from a table or other supported object. Snowflake access control is based on privileges granted through roles, and a role typically needs the appropriate privileges on the database and schema in addition to the object-level privilege. Data engineers should separate read permissions from write or administrative permissions when designing pipeline access. Granting only required privileges supports least-privilege access and reduces the risk of unintended modifications to production datasets.

Question 167

Which command grants an object privilege to a role?

1. GRANT
2. ASSIGN
3. PROVIDE
4. AUTHORIZE

Correct Answer: 1

Explanation:

The GRANT command assigns privileges or roles to authorized recipients. For example, an administrator can grant a role permission to select from a table or operate on another supported object. Snowflake’s role hierarchy can then be used to organize permissions across users and workloads. Engineers should carefully define which role receives each privilege because access granted through a parent role may also become available to roles beneath it. Regular privilege reviews help keep permissions aligned with current pipeline requirements.

Question 168

Which object groups privileges for assignment to users?

1. Access bundle
2. Role
3. Permission set
4. Security profile

Correct Answer: 2

Explanation:

A Snowflake role is a logical collection of privileges that can be granted to users or other roles. Role-based access control allows organizations to define permissions according to responsibilities instead of granting every privilege individually to each user. Roles can also inherit privileges through role hierarchies. For data engineering environments, separate roles can represent ingestion, transformation, analyst, or administrative responsibilities. This approach simplifies permission management and makes access changes easier to audit as teams and pipeline requirements evolve.

Question 169

Which privilege allows creating objects inside a schema?

1. CREATE
2. BUILD
3. DEFINE
4. OBJECT_CREATE

Correct Answer: 1

Explanation:

Schema-level CREATE privileges control whether a role can create supported objects within the schema. The specific object privilege can determine which type of object may be created, such as tables, views, stages, or other supported resources. Data engineers should distinguish schema-level creation privileges from privileges on already existing objects. Granting creation rights broadly can allow users or automated processes to introduce new objects, so production environments should apply these permissions deliberately and according to established deployment practices.

Question 170

Which Snowflake feature provides accelerated processing for eligible large queries?

1. Search Optimization
2. Result Cache
3. Query Acceleration Service
4. Automatic Clustering

Correct Answer: 3

Explanation:

Query Acceleration Service can provide additional compute resources for eligible portions of query workloads, helping certain large or scan-intensive queries execute more efficiently. It is different from Search Optimization, which is designed for selective lookup patterns, and Automatic Clustering, which organizes table data according to clustering keys. Engineers should evaluate workload characteristics before enabling acceleration because not every query benefits equally. Query history and workload analysis can help determine whether acceleration addresses an actual performance bottleneck.

Question 171

Which object can expose a controlled subset of table columns and rows?

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

Correct Answer: 1

Explanation:

A view can expose a controlled query result based on selected columns, filters, joins, or other SQL logic without requiring consumers to access the underlying table directly. Views are useful for creating simplified interfaces for downstream users and applications. Security-focused designs can combine views with additional Snowflake access-control features when sensitive data requires restricted exposure. Engineers should remember that a standard view primarily stores query logic rather than an independently maintained copy of its result data.

Question 172

Which Snowflake feature can restrict rows based on session context or user attributes?

1. Column policy
2. Row access policy
3. File policy
4. Query restriction

Correct Answer: 2

Explanation:

A row access policy can control which rows are visible based on policy conditions and contextual information such as roles or other supported session attributes. This enables fine-grained row-level access controls without requiring separate physical tables for every consumer group. Data engineers can use such policies to implement tenant, region, department, or business-unit isolation. Policy logic should be carefully tested because overly broad conditions can expose unintended records, while overly restrictive conditions can prevent legitimate users from accessing required data.

Question 173

Which Snowflake feature protects sensitive column values through masking rules?

1. Row filtering
2. Secure staging
3. Dynamic masking
4. Masking policy

Correct Answer: 4

Explanation:

A masking policy defines rules for transforming or hiding sensitive column values based on access context. For example, authorized roles may see the original value while other roles receive a masked representation. This allows organizations to keep a common underlying dataset while applying different visibility rules to consumers. Data engineers should design masking expressions carefully and test them with every relevant role. Masking policies complement, rather than replace, broader role-based access controls and data-governance practices.

Question 174

Which object can automatically execute SQL on a defined schedule?

1. Stream
2. Task
3. Stage
4. File format

Correct Answer: 2

Explanation:

A Snowflake task can execute SQL statements or call supported procedures according to a defined schedule or triggering condition. Tasks are widely used for automated transformations, data movement, maintenance operations, and orchestration. They can also be connected through task graphs to establish dependencies between multiple processing stages. Engineers should consider execution frequency, warehouse or serverless compute, failure handling, and dependency relationships when designing scheduled tasks. A well-designed task pipeline can reduce manual intervention while keeping recurring data operations predictable.

Question 175

Which Snowflake feature allows data to be shared without copying it to another account?

1. Secure Data Sharing
2. Table Replication
3. Cross-Region Copy
4. Dataset Duplication

Correct Answer: 1

Explanation:

Secure Data Sharing allows providers to share supported Snowflake data with consumers without requiring the provider to create and transfer a traditional physical copy of the shared data. This can simplify controlled collaboration between accounts while allowing the provider to retain governance over the shared objects. Data sharing differs from replication, whose purpose is to maintain replicated data or objects across supported accounts and regions. Engineers should evaluate sharing requirements, consumer access, and governance rules when designing external data-distribution workflows.

Question 176

Which object provides a reusable definition for parsing staged files?

1. Stage policy
2. File format
3. Load template
4. Parsing profile

Correct Answer: 2

Explanation:

A file format stores reusable parsing and formatting rules for supported staged data. It can define characteristics such as delimiters, compression, field enclosure, headers, and other input or output behavior. Named file formats help standardize ingestion pipelines because multiple loading operations can reference the same configuration rather than repeating all parameters. Engineers should maintain separate formats when source systems have materially different conventions. Reusable definitions also make pipeline maintenance easier because changes can be managed centrally where appropriate.

Question 177

Which command copies staged files into a Snowflake table?

1. IMPORT INTO
2. LOAD DATA
3. COPY INTO
4. INGEST FILES

Correct Answer: 3

Explanation:

COPY INTO is Snowflake’s primary SQL command for bulk loading data from supported stages into tables. It can use named stages, file formats, patterns, explicit files, and load-error handling options to control ingestion behavior. Snowflake also tracks loaded-file metadata to help prevent unintended duplicate loading under supported conditions. Engineers should design COPY operations around source-file naming, error handling, validation, and operational monitoring. Proper staging and loading configuration is essential for reliable batch-ingestion pipelines.

Question 178

Which Snowflake feature supports event-driven task execution from stream activity?

1. Scheduled-only execution
2. Stream-triggered condition
3. Manual task invocation
4. Warehouse auto-start

Correct Answer: 2

Explanation:

A task can use a WHEN condition involving SYSTEM$STREAM_HAS_DATA to execute only when a stream contains available changes. This creates an event-driven processing pattern where a scheduled task checks the stream and avoids running its transformation when no new data is present. Such designs are useful for incremental ingestion and transformation pipelines. Engineers should still configure an appropriate schedule because the task evaluates its condition according to its execution configuration rather than continuously monitoring the stream like a traditional event listener.

Question 179

Which object temporarily stores files before table loading?

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

Correct Answer: 1

Explanation:

A stage provides a location for files used in Snowflake data-loading and unloading workflows. Depending on the architecture, the stage can be internal to Snowflake or reference external cloud storage. Staging separates file management from the target table and provides a controlled location from which COPY operations can process source data. Engineers can also use stage-related metadata and file-selection options to support auditing, filtering, and troubleshooting. Proper stage configuration is an important part of reliable batch-ingestion design.

Question 180

Which Snowflake capability provides historical recovery for dropped supported objects?

1. Search Optimization
2. Query Acceleration
3. Time Travel
4. Result Caching

Correct Answer: 3

Explanation:

Time Travel provides historical access and supports recovery operations for eligible dropped objects within the applicable retention period. It is especially valuable when an object is accidentally removed or when engineers need to investigate a previous state of data. Recovery capabilities depend on object type and retention settings, so teams should not assume unlimited historical availability. Time Travel is also distinct from Fail-safe, which serves a separate recovery purpose and is not intended as a normal user-driven historical querying mechanism.