Free SnowPro-Advanced-Data-Engineer sample questions

Correct answer: Enable the Search Optimization Service on the `CUSTOMER_ID` column of the `TRANSACTIONS` table.

The query profile indicates poor pruning on a non-clustering key column (`CUSTOMER_ID`). The Search Optimization Service is specifically designed to improve the performance of selective point-lookup queries on high-cardinality columns that are not part of the clustering key. It creates a persistent data structure to accelerate these lookups, directly addressing the root cause of the poor pruning and remote I/O.

Correct answer: The stream will contain all changes captured during the 3-hour outage and will be consumed in the single task run.

A stream maintains its own offset and tracks all changes since it was last consumed. As long as the change data is still within the source table's Time Travel retention period (1 day in this case), the stream will not lose data. When the consuming task finally runs, it will read all accumulated changes from the stream since its last successful consumption, which includes the entire 3-hour period.

Correct answers: A masking policy that returns the original value if `CURRENT_ROLE()` is 'HR_ANALYST'.; Applying the masking policy to the `SSN` and `SALARY` columns of the employee table.

Correct answer: False

By default, stored procedures execute with owner's rights. This allows developers to create procedures that can perform actions on database objects that the calling user does not have direct privileges to access. While you can explicitly create a procedure to run with caller's rights, the default behavior is owner's rights.

Correct answers: A SECRET object to securely store the API key.; An API INTEGRATION object that defines the remote service endpoint and references the SECRET.; An EXTERNAL FUNCTION that links to the API INTEGRATION.

Correct answer: Introduce a data aggregation service (e.g., AWS Lambda) to compact the small files into larger, compressed files (100-250MB) before they are loaded by Snowpipe.

Snowpipe costs are influenced by the overhead of managing file loading events. Ingesting numerous small files is inefficient and costly. The best practice is to aggregate small files into larger chunks (ideally 100-250MB compressed) before ingestion. This reduces the number of notifications and file processing events, significantly lowering the per-byte ingestion cost and optimizing resource usage.

Correct answer: CREATE TRANSIENT DATABASE DEV CLONE PROD; CREATE DATABASE QA CLONE PROD;

Zero-copy cloning is the most storage-efficient way to create copies of a database. It only stores the metadata and any new or changed data (delta). To meet the requirement of no Fail-safe for the `DEV` database, it should be created as a `TRANSIENT` database. A standard clone for `QA` maintains the same data protection features as `PROD`. This combination perfectly meets all requirements.

Correct answer: Multiple `LATERAL FLATTEN` clauses in the `FROM` part of the query.

The `LATERAL FLATTEN` construct is Snowflake's primary tool for un-nesting semi-structured data arrays. To flatten a nested structure (an array within an array), you must chain multiple `LATERAL FLATTEN` clauses. The first `FLATTEN` would expand the `transactions` array, and the second `FLATTEN` would operate on the output of the first to expand the `items` array within each transaction.

Correct answers: SYSADMIN; SECURITYADMIN

Correct answer: `SINGLE = FALSE`

By default, Snowflake unloads data in parallel to multiple files to maximize performance. The `SINGLE` copy option controls this behavior. Setting `SINGLE = FALSE` (which is the default) ensures that the unload operation utilizes parallel processing to create multiple output files, which is ideal for large tables and parallel downstream consumption. Setting `SINGLE = TRUE` would force a single-threaded unload to one file, severely hindering performance.