SnowPro-Advanced-Data-Engineer sample questions

Question 1

A financial services company is implementing a near real-time fraud detection pipeline. Transaction data arrives via a Kafka topic. The data engineering team must choose between Snowpipe and Snowpipe Streaming for ingestion. A key requirement is to minimize ingestion latency to under 5 seconds per batch of records. Which factor is the MOST critical in deciding to use Snowpipe Streaming over the traditional Snowpipe REST API?

Accepted Answer

The requirement to bypass cloud storage staging for lower latency.

Answer

The ability to use the Kafka Connector for Snowflake.

Answer

The need for transactional, exactly-once delivery semantics.

Answer

The capability to handle semi-structured JSON data natively.

Question 2

A data science team is developing a sentiment analysis model using a custom Python library packaged as a `.whl` file. This library is not available on Anaconda or PyPI. A data engineer needs to make this library available to a Snowpark Python UDF for batch scoring. The security policy prohibits direct runtime package installation from public repositories. What is the recommended approach to securely deploy and use this custom library?

Accepted Answer

Upload the `.whl` file to a secure internal stage and reference its path in the `IMPORTS` clause of the `CREATE FUNCTION` statement.

Answer

Unzip the wheel file and use `session.add_import()` for each source `.py` file.

Answer

Install the library on the client machine and rely on the Snowpark client to serialize and transfer it to the server.

Answer

Host the package on a private Git repository and use `session.add_packages()` with a Git URL.

Question 3

A data engineer is analyzing the query profile of a long-running query that joins a large fact table (`TRANSACTIONS`, 5TB) with several small dimension tables. The profile indicates significant remote disk I/O (65% of execution time) and poor partition pruning (90% of partitions scanned). The `TRANSACTIONS` table is clustered by `TRANSACTION_DATE`. The problematic query filters on `CUSTOMER_ID`. Which action would provide the MOST significant and targeted performance improvement for this specific query?

Accepted Answer

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

Answer

Increase the virtual warehouse size from X-Large to 2X-Large.

Answer

Create a materialized view on the `TRANSACTIONS` table pre-joining the dimension tables.

Answer

Change the clustering key on the `TRANSACTIONS` table to `(TRANSACTION_DATE, CUSTOMER_ID)`.

Question 4

A data engineer has created a stream object on a `RAW_EVENTS` table to capture changes for an ELT pipeline. The stream is consumed by a task that runs every 5 minutes. The task failed to run for 3 hours due to a permission issue, which has now been resolved. The `RAW_EVENTS` table has a data retention period of 1 day. What will be the state of the stream when the task runs successfully for the first time after the outage?

Accepted Answer

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

Answer

The stream will be empty because the offset is older than the task's schedule.

Answer

The stream will contain all changes that occurred in the last 5 minutes before the task ran.

Answer

The stream will become stale and must be recreated because it was not consumed for an extended period.

Question 5

A data architect needs to enforce column-level security on a table containing employee data, including PII like `SALARY` and `SSN`. The requirements are:
1. Analysts in the `HR_ANALYST` role should see the full, unmasked data.
2. All other roles, including `ACCOUNTADMIN`, should see masked values (e.g., 'XXX-XX-XXXX' for SSN).

Which combination of objects and privileges is required to correctly implement this? (Select TWO)

Accepted Answer

A masking policy that returns the original value if `CURRENT_ROLE()` is 'HR_ANALYST'.

Accepted Answer

Applying the masking policy to the `SSN` and `SALARY` columns of the employee table.

Answer

A secure view that uses a `CASE` statement to mask data for non-HR roles.

Answer

Granting the `HR_ANALYST` role the `UNMASK` privilege on the specific columns.

Answer

Creating a Row Access Policy that filters data based on `CURRENT_ROLE()`.

Question 6

True or False: When a stored procedure written in Python (using Snowpark) is called, it executes with the rights of the caller (invoker's rights), not the rights of the procedure's owner (owner's rights).

Accepted Answer

False

Answer

True

Question 7

A data engineer needs to call an external machine learning model hosted on a cloud provider's serverless function endpoint to enrich data within a Snowflake query. The endpoint requires an API key for authentication. What Snowflake objects must be configured to enable this workflow securely? (Select THREE)

Accepted Answer

A SECRET object to securely store the API key.

Accepted Answer

An API INTEGRATION object that defines the remote service endpoint and references the SECRET.

Accepted Answer

An EXTERNAL FUNCTION that links to the API INTEGRATION.

Answer

A STORAGE INTEGRATION to manage access to the external stage.

Answer

A NETWORK POLICY to whitelist the IP address of the serverless function.

Question 8

An IoT company ingests billions of small JSON events daily into an external S3 stage. The data needs to be loaded into a `RAW_EVENTS` table. A data engineer implemented a Snowpipe with auto-ingest, but the ingestion credits are significantly higher than expected. Upon investigation, the engineer finds that files are being created in S3 every few seconds, and most are under 1 MB. What is the MOST effective strategy to reduce Snowpipe costs while maintaining the continuous ingestion flow?

Accepted 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.

Answer

Switch from auto-ingest Snowpipe to a scheduled task that runs a COPY INTO command every minute.

Answer

Increase the `PIPE_EXECUTION_PAUSED` parameter to batch more files before processing.

Answer

Use Snowpipe Streaming to ingest the events directly, bypassing S3.

Question 9

A data engineer is designing a development workflow. The `PROD` database is 10TB. The team needs a full, isolated copy of the `PROD` database for development (`DEV`) and another for QA (`QA`). A key requirement is to minimize storage costs. Additionally, the `DEV` database must not have a Fail-safe period. Which set of commands achieves these requirements MOST efficiently?

Accepted Answer

CREATE TRANSIENT DATABASE DEV CLONE PROD;
CREATE DATABASE QA CLONE PROD;

Answer

CREATE DATABASE DEV CLONE PROD;
CREATE DATABASE QA CLONE PROD;

Answer

CREATE DATABASE DEV; CREATE DATABASE QA;
-- Manually copy all tables from PROD

Answer

CREATE DATABASE DEV CLONE PROD DATA_RETENTION_TIME_IN_DAYS = 0;
CREATE DATABASE QA CLONE PROD;

Question 10

A data engineer needs to flatten a deeply nested JSON structure stored in a VARIANT column named `EVENT_DATA`. The structure contains an array of `transactions`, and each transaction has an array of `items`. The goal is to produce a flat table with `event_id`, `transaction_id`, and `item_id`. Which SQL construct is essential for achieving this transformation efficiently in Snowflake?

Accepted Answer

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

Answer

A recursive Common Table Expression (CTE).

Answer

A series of `PIVOT` and `UNPIVOT` functions.

Answer

A JavaScript User-Defined Table Function (UDTF).

Top vendors

Top exams

Free SnowPro-Advanced-Data-Engineer sample questions

True or False: When a stored procedure written in Python (using Snowpark) is called, it executes with the rights of the caller (invoker's rights), not the rights of the procedure's owner (owner's rights).

10 more free samples are waiting