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

 

Question 381

Which command changes a table’s clustering configuration?

1. ALTER TABLE
2. MODIFY CLUSTER
3. SET CLUSTERING
4. UPDATE TABLE CLUSTER

Correct Answer: 1

Explanation:

ALTER TABLE is used to modify supported properties of an existing Snowflake table, including clustering-related configuration. Engineers can use it when adding, changing, or removing a clustering key as workload requirements evolve. Clustering decisions should be based on actual query patterns rather than applied automatically to every table. A well-designed clustering strategy can improve pruning for appropriate workloads, while unnecessary clustering may introduce additional maintenance activity. Engineers should monitor performance and storage behavior after making clustering changes.

Question 382

Which function provides clustering metadata for a table?

1. TABLE_CLUSTER_STATS
2. SYSTEM$CLUSTERING_INFORMATION
3. GET_CLUSTERING_DATA
4. CLUSTER_STATUS

Correct Answer: 2

Explanation:

SYSTEM$CLUSTERING_INFORMATION provides information that can help engineers evaluate the clustering state of a table. The returned information can be used to understand clustering depth, overlapping ranges, and related characteristics. This makes the function useful when investigating whether a clustering strategy is effectively supporting micro-partition pruning. Engineers should interpret the results alongside actual query performance rather than relying on a single metric. Clustering information is especially valuable when deciding whether a table needs additional optimization.

Question 383

Which feature automatically maintains materialized view data?

1. Query Scheduler
2. View Refresher
3. Automatic Clustering
4. Automatic Maintenance

Correct Answer: 4

Explanation:

Snowflake automatically maintains materialized views as underlying data changes, subject to the platform’s supported maintenance behavior. This allows queries against the materialized view to benefit from maintained precomputed results without requiring engineers to build a separate refresh task. Engineers should still evaluate whether a materialized view provides enough performance benefit to justify its storage and maintenance costs. Workload characteristics, query frequency, underlying-table changes, and data freshness requirements should all be considered before creating one.

Question 384

Which object stores precomputed query results for repeated access?

1. Materialized view
2. Standard view
3. External stage
4. Stream

Correct Answer: 3

Explanation:

A materialized view stores maintained, precomputed results derived from its defining query. Unlike a standard view, which generally stores the query definition rather than its result data, a materialized view can reduce the computation required for eligible queries. Snowflake manages maintenance of the materialized data as underlying tables change. Engineers should use materialized views selectively because maintenance consumes resources. They are particularly relevant for expensive recurring transformations where improved query performance can justify the additional maintenance overhead.

Question 385

Which feature can accelerate repeated equality searches?

1. Search Optimization Service
2. Query Result Archive
3. Warehouse Scaling
4. Resource Monitoring

Correct Answer: 1

Explanation:

Search Optimization Service can accelerate certain selective search patterns, including supported equality lookups, by maintaining additional search access structures. It is particularly useful when queries frequently locate a small number of records within large tables and ordinary micro-partition pruning does not provide sufficient filtering. Engineers should evaluate actual workload patterns before enabling the feature because it introduces additional maintenance and storage considerations. Search optimization complements other techniques such as clustering and appropriate query predicates rather than replacing general table-design practices.

Question 386

Which parameter limits warehouse credit consumption over time?

1. CREDIT_CAP
2. RESOURCE_MONITOR
3. COMPUTE_LIMITER
4. WAREHOUSE_QUOTA

Correct Answer: 2

Explanation:

A resource monitor can define credit-related limits and actions for Snowflake warehouses. It helps administrators control consumption by monitoring credit usage over a configured period and applying supported actions when thresholds are reached. Engineers can use resource monitors as part of broader cost-governance strategies, particularly for development, testing, or workload-specific environments. Resource monitors do not optimize individual queries; instead, they provide operational controls around credit consumption. Thresholds and actions should be selected according to the organization’s usage policies.

Question 387

Which warehouse property controls automatic suspension?

1. IDLE_SUSPENSION
2. SUSPEND_TIMEOUT
3. AUTO_SUSPEND
4. WAREHOUSE_IDLE_LIMIT

Correct Answer: 3

Explanation:

AUTO_SUSPEND determines how long a virtual warehouse can remain inactive before Snowflake suspends it automatically. This setting is important for controlling compute consumption during periods without query activity. Engineers should consider workload frequency when selecting the suspension interval. A very short interval may lead to frequent resume operations for workloads with intermittent queries, while a long interval may keep compute running unnecessarily. Proper configuration should balance responsiveness, startup behavior, and expected warehouse utilization.

Question 388

Which property enables automatic warehouse startup?

1. AUTO_RESUME
2. RESUME_AUTOMATIC
3. START_ON_QUERY
4. ENABLE_RESUME

Correct Answer: 4

Explanation:

AUTO_RESUME allows a suspended virtual warehouse to resume automatically when a query requires it. This is useful for scheduled or interactive workloads where engineers do not want to manually start the warehouse before every execution. Auto-resume works together with auto-suspend to manage warehouse availability and compute consumption. Engineers should consider startup latency and workload frequency when configuring both properties. Automated pipelines generally benefit from explicit warehouse configuration so execution does not depend on an unexpected session state.

Question 389

Which command displays grants assigned to an object or role?

1. SHOW GRANTS
2. LIST PRIVILEGES
3. DISPLAY ACCESS
4. SHOW PERMISSIONS

Correct Answer: 1

Explanation:

SHOW GRANTS displays privilege information for supported Snowflake objects and roles. It is useful for auditing access, troubleshooting authorization failures, and verifying whether expected privileges have been assigned. Engineers working with role hierarchies should inspect both direct and inherited access when investigating permissions. Access reviews should also consider ownership and role inheritance because effective privileges may come from multiple grants. Regular grant inspection can help identify unnecessary permissions and improve governance in shared Snowflake environments.

Question 390

Which command displays object metadata interactively?

1. SHOW DETAILS
2. DESCRIBE
3. DISPLAY OBJECT
4. INSPECT OBJECT

Correct Answer: 2

Explanation:

DESCRIBE can return metadata about supported Snowflake objects, including structural information for tables, views, stages, file formats, and other objects. Engineers commonly use it during development and troubleshooting to verify definitions without querying underlying metadata tables directly. The exact information returned depends on the object being described. DESCRIBE is especially useful when validating column definitions, properties, and configuration details before modifying a production pipeline or diagnosing unexpected object behavior.

Question 391

Which command displays available schemas in a database?

1. LIST SCHEMAS
2. SHOW SCHEMAS
3. DISPLAY SCHEMAS
4. ENUMERATE SCHEMAS

Correct Answer: 2

Explanation:

SHOW SCHEMAS lists schemas visible to the executing role within the relevant Snowflake context. It can help engineers discover available namespaces and verify whether a schema exists before executing metadata-driven operations. Because visibility depends on privileges and context, the returned list should not automatically be interpreted as a complete account-wide inventory. Engineers can use SHOW commands during deployment and troubleshooting, while more detailed metadata analysis can be performed through supported information schemas or account-level views.

Question 392

Which metadata view contains table column definitions?

1. INFORMATION_SCHEMA.COLUMNS
2. TABLE_DEFINITION_VIEW
3. COLUMN_METADATA
4. ACCOUNT_COLUMNS

Correct Answer: 3

Explanation:

INFORMATION_SCHEMA.COLUMNS provides metadata about columns for objects visible within the applicable Snowflake database context. It can be used by metadata-driven pipelines to inspect names, data types, ordinal positions, and other column attributes. Engineers frequently use this information when generating dynamic transformations, validating schemas, or comparing expected structures with deployed tables. Because Information Schema visibility follows Snowflake access rules and database context, queries should be designed with the required scope and privileges in mind.

Question 393

Which view provides account-level query execution history?

1. QUERY_LOG
2. ACCOUNT_USAGE.QUERY_HISTORY
3. SYSTEM_QUERY_HISTORY
4. QUERY_EXECUTION_TABLE

Correct Answer: 4

Explanation:

Snowflake provides query-history information through the ACCOUNT_USAGE schema, including the QUERY_HISTORY view. Engineers can use query history for operational analysis, workload investigation, troubleshooting, and usage reporting. The metadata can help identify execution patterns, durations, users, warehouses, and other query characteristics. When analyzing historical workloads, engineers should account for the applicable retention and data-availability characteristics of the selected metadata source. Query history is particularly valuable when performance investigations require evidence from actual workload execution.

Question 394

Which table function can inspect recent query history interactively?

1. QUERY_HISTORY()
2. GET_QUERY_HISTORY()
3. QUERY_LOG()
4. RECENT_QUERIES()

Correct Answer: 1

Explanation:

Snowflake provides query-history table functions that can return query execution information for supported time ranges and scopes. These functions are useful when engineers need programmatic access to recent query activity rather than manually reviewing a user interface. Query history can support troubleshooting, workload analysis, and operational monitoring. Engineers should choose the appropriate query-history source based on the required time range, account scope, and available metadata. Filtering results by warehouse, user, or query characteristics can make investigations more efficient.

Question 395

Which object can centralize reusable file-format settings?

1. Data format profile
2. Named file format
3. Stage schema
4. File configuration table

Correct Answer: 3

Explanation:

A named file format stores reusable configuration for how Snowflake interprets or generates files. Settings can include delimiters, compression, record handling, null representation, and other supported format-specific options. Using a named file format can reduce duplication across stages and COPY commands and make ingestion configurations easier to manage consistently. Engineers should select settings that match the actual source files because incorrect format definitions can cause parsing errors, unexpected values, or failed loads.

Question 396

Which option specifies how enclosed CSV fields are represented?

1. FIELD_WRAPPER
2. QUOTE_CHARACTER
3. FIELD_OPTIONALLY_ENCLOSED_BY
4. CSV_QUOTE_MODE

Correct Answer: 4

Explanation:

FIELD_OPTIONALLY_ENCLOSED_BY specifies a character used to optionally enclose CSV fields, commonly a quotation mark. This setting is important when source data contains delimiters, embedded characters, or values that need explicit field boundaries. Engineers should configure it according to the source system’s actual serialization behavior. Incorrect enclosure settings can cause columns to shift or values to be parsed incorrectly. File-format definitions should therefore be tested against representative source files before being used in production ingestion pipelines.

Question 397

Which file-format option defines values interpreted as NULL?

1. NULL_IF
2. NULL_VALUES
3. EMPTY_AS_NULL
4. NULL_MARKER

Correct Answer: 1

Explanation:

NULL_IF specifies one or more strings that Snowflake should interpret as SQL NULL during supported file loading operations. This is useful when source systems represent missing values with specific tokens rather than an actual database NULL concept. Engineers should define the values carefully because a legitimate business value could accidentally be converted to NULL if the token is too broad. File-format behavior should be tested with representative source records, particularly when multiple systems produce files with different missing-value conventions.

Question 398

Which command copies staged data into a table?

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

Correct Answer: 2

Explanation:

COPY INTO loads data from supported staged locations into Snowflake tables. It is one of the core commands used in batch ingestion pipelines and supports options for file formats, patterns, validation, error handling, and other loading behaviors. Engineers should configure the source stage and file format carefully before executing a production load. COPY INTO can also be used with load-history and validation techniques to help diagnose unsuccessful files and maintain reliable ingestion workflows.

Question 399

Which option restricts COPY loading to matching filenames?

1. FILE_FILTER
2. REGEX_PATTERN
3. PATTERN
4. MATCH_FILES

Correct Answer: 3

Explanation:

The PATTERN option in COPY operations allows engineers to restrict which staged files are selected for loading by applying a regular-expression pattern to filenames. This is useful when a stage contains multiple file types, historical exports, or unrelated objects that should not enter the same target table. Engineers should design patterns carefully and test them against representative stage contents. An overly broad pattern can ingest unintended files, while an overly restrictive expression can silently exclude expected source data.

Question 400

Which COPY option prevents loading a file more than once?

1. LOAD_ONCE
2. SKIP_DUPLICATES
3. IGNORE_REPEATED_FILES
4. PREVENT_RELOAD

Correct Answer: 4

Explanation:

Snowflake’s LOAD_UNCERTAIN_FILES and related load-history behavior should be understood when designing repeatable ingestion, but the standard COPY option used to control whether previously loaded files are considered again is FORCE. Setting FORCE = FALSE, which is the normal behavior, allows Snowflake to skip files already recognized as successfully loaded under applicable load history. Engineers should understand load metadata and file identity rather than relying only on filename conventions when designing duplicate-load protection.