SnowPro-Advanced-Data-Engineer sample questions

Question 1

**Case Study:** A retail company, GlobalMart, is building a centralized data platform in Snowflake. They have two main data sources: transactional data from on-premises POS systems and streaming clickstream data from their e-commerce website via Kafka. **Current Situation:** - POS data is batch-extracted daily as 10GB of compressed CSV files and uploaded to an AWS S3 bucket. - Clickstream data is available on a Kafka topic with an average of 1,000 messages per second. - A `TRANSACTIONS` table needs to be populated from the POS data, and a `CLICKSTREAM` table from the Kafka topic. - A `CUSTOMER_ACTIVITY` materialized view is planned to join `TRANSACTIONS` and `CLICKSTREAM` data for real-time dashboarding. **Requirements:** 1. POS data must be loaded into the `TRANSACTIONS` table within one hour of its arrival in S3. 2. Clickstream data must be available in the `CLICKSTREAM` table with an end-to-end latency of less than 15 seconds. 3. The data loading process must be serverless and automated. 4. Data engineers need a way to monitor the health and cost of both ingestion pipelines. Which solution design BEST meets all of GlobalMart's requirements?

Accepted Answer

Use Snowpipe with auto-ingest for the S3 data. Use the Snowflake Kafka Connector configured with Snowpipe Streaming for the clickstream data. Monitor using the PIPE_USAGE_HISTORY and SNOWPIPE_STREAMING_FILE_MIGRATION_HISTORY views.

Answer

Use a single scheduled Task running a COPY INTO command for both S3 and Kafka data. Monitor using TASK_HISTORY.

Answer

Use Snowpipe with auto-ingest for the S3 data. Use the Snowflake Kafka Connector configured with Snowpipe for the clickstream data. Monitor using PIPE_USAGE_HISTORY and COPY_HISTORY.

Answer

Use an external data integration tool (like Fivetran or Stitch) for both data sources to simplify management. Monitor using the tool's dashboard.

Question 2

A data pipeline uses a stream to capture changes on a source table. A downstream task consumes the stream within a `BEGIN...COMMIT` transaction block. During a specific run, the task successfully reads the stream data into a staging table but fails before the final `COMMIT` statement due to a transient network error. What happens to the stream's offset?

Accepted Answer

The offset does not advance because the enclosing transaction was rolled back.

Answer

The offset advances because the stream data was successfully read.

Answer

The stream becomes stale because a transaction failed.

Answer

The offset advances partially to the last successfully processed record before the failure.

Question 3

A data engineer is tasked with optimizing a large, heavily queried table `USER_SESSIONS`. The table is currently clustered by `SESSION_START_TIME`. However, a critical dashboard frequently queries the table with highly selective filters on `USER_ID`, which has very high cardinality. These queries are slow due to poor pruning. Adding `USER_ID` to the clustering key is not feasible as it would conflict with existing range-based queries on `SESSION_START_TIME`. What is the most appropriate and cost-effective solution?

Accepted Answer

Enable the Search Optimization Service on the `USER_ID` column.

Answer

Create a secondary table clustered by `USER_ID` and duplicate the data.

Answer

Increase the warehouse size to a larger T-shirt size.

Answer

Create a materialized view that is clustered by `USER_ID`.

Question 4

A data engineer observes that a table's clustering depth is consistently increasing, and query performance is degrading. The table is configured with a clustering key and automatic clustering is enabled. What is the MOST likely reason that automatic clustering is not maintaining the table's clustering health?

Accepted Answer

There has not been enough DML activity on the table to trigger a reclustering operation.

Answer

The virtual warehouse used for queries is too small.

Answer

The clustering key is defined on a column with low cardinality.

Answer

The table's data retention period is set too high.

Question 5

A data engineer needs to create a stored procedure in Snowflake Scripting to iterate through a list of table names and perform a `TRUNCATE` operation on each. The procedure must handle cases where a table in the list does not exist without halting execution. Which Snowflake Scripting construct is best suited for this requirement? ```mermaid flowchart TD Start([Start Procedure]) --> Loop{For each table in list} Loop --> |Table Exists| Truncate[TRUNCATE TABLE] Loop --> |Table Doesn't Exist| Continue[Continue to next table] Truncate --> Loop Continue --> Loop Loop -->|End of List| End([End Procedure]) ```

Accepted Answer

A `FOR` loop iterating over a cursor, with each `TRUNCATE` statement wrapped in a `BEGIN...EXCEPTION...END` block.

Answer

A `LOOP` with an `IF-ELSE` block that checks `INFORMATION_SCHEMA.TABLES` before each truncate.

Answer

A `WHILE` loop that uses `SYSTEM$TABLE_EXISTS` to control the loop condition.

Answer

Using the `EXECUTE IMMEDIATE` command with a dynamically generated `TRUNCATE IF EXISTS` statement.

Question 6

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 7

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 8

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 9

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 10

A data architect needs to enforce column-level security on a table containing employee data, including PII like SALARY and SSN. The requirements are:

Analysts in the HR_ANALYST role should see the full, unmasked data.
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()`.

SnowPro-Advanced-Data-Engineer Practice Questions

Create an account to continue.

Create an account to continue.

Related exams