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

 

Question 341

Which option controls concurrent clusters in a warehouse?

1. MAX_CLUSTER_COUNT
2. CLUSTER_LIMIT
3. COMPUTE_INSTANCES
4. WAREHOUSE_NODES

Correct Answer: 1

Explanation:

MAX_CLUSTER_COUNT defines the maximum number of compute clusters that a multi-cluster warehouse can use. Snowflake can add clusters as workload concurrency increases, subject to the configured limits and scaling behavior. This setting is particularly relevant for environments where many users or automated workloads submit queries simultaneously. Engineers should distinguish cluster count from warehouse size because warehouse size affects compute resources within a cluster, while cluster count primarily addresses concurrency. Proper configuration can help manage query queuing during periods of increased demand.

Question 342

Which setting defines the minimum clusters retained for concurrency?

1. BASE_CLUSTER_COUNT
2. MIN_CLUSTER_COUNT
3. LOW_CLUSTER_LIMIT
4. START_CLUSTER_TOTAL

Correct Answer: 2

Explanation:

MIN_CLUSTER_COUNT specifies the minimum number of clusters that a multi-cluster warehouse maintains when operating in multi-cluster mode. Keeping more than one cluster available can provide additional concurrency capacity without waiting for another cluster to start. Engineers should balance this setting against workload patterns because maintaining multiple active clusters can increase compute consumption. The minimum cluster count should be selected based on expected concurrent demand rather than simply matching the maximum cluster configuration.

Question 343

Which scaling policy prioritizes maintaining running clusters?

1. BALANCED
2. PERFORMANCE
3. ECONOMY
4. CONSERVATIVE

Correct Answer: 3

Explanation:

The ECONOMY scaling policy attempts to conserve credits by keeping additional clusters running only when workload demand justifies them for longer periods. Snowflake multi-cluster warehouses use scaling policies to determine how aggressively additional clusters are started or stopped as concurrency changes. Engineers should evaluate the policy against workload latency requirements and cost objectives. A concurrency-heavy workload may behave differently from an intermittent batch workload, so scaling configuration should be aligned with actual execution patterns rather than applied uniformly across every warehouse.

Question 344

Which scaling policy favors faster cluster availability?

1. STANDARD
2. RAPID
3. PERFORMANCE
4. IMMEDIATE

Correct Answer: 1

Explanation:

The STANDARD scaling policy is designed to prioritize responsiveness when additional compute capacity is needed for concurrent workloads. Compared with the economy-oriented approach, it can make additional clusters available more readily as query demand increases. This can help workloads where reducing queueing is more important than minimizing every possible credit expenditure. Engineers should still monitor actual warehouse behavior because concurrency requirements vary significantly between interactive analytics, scheduled transformations, and high-volume data processing.

Question 345

Which command changes the active role?

1. ACTIVATE ROLE
2. USE ROLE
3. SET ACTIVE ROLE
4. SWITCH SECURITY ROLE

Correct Answer: 2

Explanation:

USE ROLE changes the active role within the current Snowflake session. The active role determines which privileges are available for subsequent operations, subject to Snowflake’s role hierarchy and authorization model. Engineers frequently use this command when testing access behavior or switching between administrative and workload-specific roles. Production automation should establish the required role explicitly rather than relying on an inherited session state. This helps make security behavior predictable and reduces accidental execution with broader privileges.

Question 346

Which command changes the active database context?

1. SELECT DATABASE
2. OPEN DATABASE
3. USE DATABASE
4. SET DATABASE

Correct Answer: 3

Explanation:

USE DATABASE establishes the database context for the current Snowflake session. Once selected, unqualified object references can resolve within that database, subject to the active schema and permissions. Engineers should understand session context because scripts that depend heavily on implicit database selection may behave differently when executed through different clients or automation frameworks. Explicit context-setting commands or fully qualified object names can make production data pipelines more deterministic and easier to troubleshoot.

Question 347

Which command changes the active schema context?

1. USE SCHEMA
2. SET SCHEMA
3. OPEN SCHEMA
4. ACTIVATE SCHEMA

Correct Answer: 1

Explanation:

USE SCHEMA sets the active schema for the current Snowflake session. This affects how unqualified table, view, and other object references are resolved. In automated data-engineering workflows, explicitly establishing schema context can prevent accidental references to objects in an unintended namespace. Engineers should also remember that schema selection is separate from database selection, so both contexts may need to be configured. Fully qualified names can provide additional clarity when transformations span multiple databases or schemas.

Question 348

Which function returns the active warehouse name?

1. WAREHOUSE_CONTEXT
2. CURRENT_WAREHOUSE
3. ACTIVE_WAREHOUSE_NAME
4. SESSION_WAREHOUSE

Correct Answer: 2

Explanation:

CURRENT_WAREHOUSE() returns the warehouse associated with the current session. It can be useful for diagnostics, auditing, and validating execution context in SQL workflows. Engineers can use session-context functions when troubleshooting why a query is running on an unexpected warehouse or when validating deployment scripts. Because warehouse selection influences compute resources and workload costs, confirming the active warehouse can be valuable during operational investigations and automated pipeline testing.

Question 349

Which object stores reusable SQL query results temporarily?

1. Result cache
2. Query archive
3. SQL buffer
4. Execution store

Correct Answer: 1

Explanation:

Snowflake’s result cache can reuse eligible results from previously executed queries, reducing the need to perform the same computation again. When cache reuse is possible, users may receive results without consuming the same level of warehouse compute that a fresh execution would require. Engineers should understand that cache eligibility depends on query and underlying-data conditions. Result caching is different from warehouse caching because it concerns query results rather than simply retaining data in compute-layer memory.

Question 350

Which cache stores frequently accessed table data in memory?

1. Result cache
2. Metadata cache
3. Local disk cache
4. Warehouse cache

Correct Answer: 4

Explanation:

The warehouse cache refers to data cached within the compute resources associated with a Snowflake virtual warehouse. Repeated queries can sometimes benefit when required data remains available in the warehouse’s local cache, reducing the need to retrieve the same micro-partitions again from remote storage. Cache behavior depends on workload patterns and warehouse lifecycle. Suspending a warehouse can affect cached data availability, so engineers should not design pipelines assuming that a particular cache state will always persist.

Question 351

Which feature can reduce scanning for selective predicates?

1. Search Optimization Service
2. Query History
3. Result Cache
4. Resource Monitor

Correct Answer: 1

Explanation:

Search Optimization Service can improve performance for certain selective lookup patterns by maintaining additional access structures that help Snowflake locate qualifying rows more efficiently. It is particularly useful when queries frequently search for specific values in large tables and ordinary micro-partition pruning is insufficient. Engineers should evaluate query patterns before enabling it because the feature introduces additional maintenance considerations and credit consumption. It complements, rather than replaces, appropriate table design, clustering, and query optimization.

Question 352

Which command creates a sequence object?

1. MAKE SEQUENCE
2. CREATE SEQUENCE
3. BUILD SEQUENCE
4. DEFINE SEQUENCE

Correct Answer: 2

Explanation:

CREATE SEQUENCE creates a Snowflake sequence object that can generate numeric values for applications such as surrogate keys or ordered identifiers. Sequences are useful when generated values do not need to originate from the source business data. Engineers should understand that sequence values are not necessarily gap-free because concurrent execution, transaction behavior, and other operational conditions can result in unused values. Therefore, sequences should generally not be treated as guaranteed contiguous numbering mechanisms.

Question 353

Which function generates sequential values within generated rows?

1. ROW_SEQUENCE
2. SEQ4
3. NUMBER_SEQUENCE
4. SERIAL_VALUE

Correct Answer: 2

Explanation:

SEQ4() generates increasing 4-byte integer sequence values and is commonly used with Snowflake’s GENERATOR table function for creating synthetic rows or test datasets. Engineers can combine generated sequences with expressions such as date calculations to produce controlled sample data. Sequence functions should not be interpreted as guaranteed gap-free row numbering in every use case. For deterministic analytical row numbering based on an ordering requirement, window functions such as ROW_NUMBER() may be more appropriate.

Question 354

Which clause filters rows after window functions execute?

1. FILTER
2. HAVING
3. QUALIFY
4. WINDOW WHERE

Correct Answer: 3

Explanation:

QUALIFY filters the results of window functions after those functions have been evaluated. This allows engineers to write patterns such as selecting the latest record per business key without nesting the query in another subquery solely to filter a window-function result. It is especially useful in deduplication, ranking, and change-selection transformations. Engineers should distinguish QUALIFY from WHERE because WHERE filters rows before window-function evaluation, while QUALIFY operates on the resulting window-function calculations.

Question 355

Which function assigns sequential row numbers within partitions?

1. ROW_NUMBER
2. SEQUENCE_RANK
3. PARTITION_INDEX
4. ROW_COUNTER

Correct Answer: 1

Explanation:

ROW_NUMBER() assigns a unique sequential number to rows within each window partition according to the specified ordering. It is widely used in data-engineering transformations for deduplication, latest-record selection, ranking, and deterministic record prioritization. Engineers should provide an appropriate ORDER BY expression because the ordering determines which record receives each number. When selecting a single preferred record per business key, ROW_NUMBER combined with QUALIFY is a common and efficient SQL pattern.

Question 356

Which function retrieves a preceding row’s value?

1. PREVIOUS
2. PRIOR_VALUE
3. LAG
4. BACKWARD

Correct Answer: 3

Explanation:

LAG() accesses a value from a preceding row within a window according to the specified ordering. It is useful for comparing current and previous events, calculating changes between measurements, detecting state transitions, and analyzing sequential data. Engineers should define the partition and ordering carefully because these determine which row is considered the predecessor. LAG can be especially useful in event-processing pipelines where detecting changes between consecutive records is more efficient than performing repeated self-joins.

Question 357

Which function retrieves a following row’s value?

1. NEXT_VALUE
2. LEAD
3. FORWARD_ROW
4. FUTURE_VALUE

Correct Answer: 2

Explanation:

LEAD() accesses a value from a subsequent row within a window based on the specified ordering. It is useful when data-engineering transformations need to compare an event with the next event, calculate durations, or identify transitions between records. The partition and ordering expressions determine what constitutes the following row. Engineers should also consider default values for cases where no subsequent row exists. LEAD is particularly helpful for event sequences and temporal analysis without requiring a self-join.

Question 358

Which command starts an explicit transaction?

1. BEGIN
2. START QUERY
3. OPEN TRANSACTION
4. CREATE TRANSACTION

Correct Answer: 1

Explanation:

BEGIN starts an explicit transaction in Snowflake. Explicit transactions allow multiple supported statements to be grouped into a transactional unit so that the workflow can commit or roll back according to the desired outcome. This can be useful in data-engineering operations where related changes should succeed together. Engineers should understand which statements participate in the transaction and carefully manage transaction boundaries. Long-running transactions can also have operational implications, so they should be designed deliberately.

Question 359

Which command permanently confirms a transaction?

1. ACCEPT
2. APPLY
3. COMMIT
4. CONFIRM TRANSACTION

Correct Answer: 3

Explanation:

COMMIT makes the changes performed within the current transaction permanent according to Snowflake transaction semantics. It is normally used after all required statements complete successfully. Engineers should place transaction boundaries carefully because committing too early can leave a multi-step pipeline partially applied, while unnecessarily broad transactions can increase operational complexity. In data-processing workflows, explicit commit behavior is particularly useful when several related modifications must be treated as one logical unit.

Question 360

Which command reverses uncommitted transaction changes?

1. CANCEL
2. UNDO
3. REVERT TRANSACTION
4. ROLLBACK

Correct Answer: 4

Explanation:

ROLLBACK reverses changes made within the current transaction that have not been committed. It provides an important safety mechanism for multi-step data modifications because a failed validation or processing step can prevent incomplete changes from becoming permanent. Engineers should understand the transaction boundaries involved and ensure that error-handling logic performs rollback when appropriate. Proper transaction management can help maintain consistency when pipelines execute multiple dependent DML statements as part of a single logical operation.