SnowPro-Advanced-Data-Engineer Sample Questions

SnowPro-Advanced-Data-Engineer Sample Questions & Answers

Loading data in ties with transforming it through UDFs, external functions and stored procedures for the top share, next to troubleshooting query performance and caching, recovering data through time travel, and security roles and governance.

Launch the full SnowPro-Advanced-Data-Engineer simulator →

Showing 10 of 20 free samples.

  1. Question 1Beginner

    Domain: Data Transformation · Design, Build, and Leverage Stored Procedures.

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

    Show answer & explanation

    Correct answer: B

    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.

  2. Question 2AdvancedSelect 3

    Domain: Data Movement · Define and create External Functions.

    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)

    Show answer & explanation

    Correct answers: A, B, D

  3. Question 3Intermediate

    Domain: Performance Optimization · Monitor continuous data pipelines.

    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?

    Show answer & explanation

    Correct answer: B

    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.

  4. Question 4Intermediate

    Domain: Storage & Data Protection · Use Time Travel and Cloning to create new development environments.

    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?

    Show answer & explanation

    Correct answer: B

    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.

  5. Question 5Intermediate

    Domain: Data Transformation · Handle and transform semi-structured data.

    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?

    Show answer & explanation

    Correct answer: C

    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.

  6. Question 6BeginnerSelect 2

    Domain: Security · Outline the system defined roles and when they should be applied.

    An organization is migrating its data warehouse to Snowflake. The security team wants to enforce the principle of least privilege for administration. They need to separate user and security management from system and resource management. Which two system-defined roles should be assigned to accomplish this separation of duties? (Select TWO)

    Show answer & explanation

    Correct answers: A, C

  7. Question 7Intermediate

    Domain: Data Movement · Outline when to use External Tables and define how they work.

    A data engineer is building a pipeline that requires unloading data from a Snowflake table to an external stage for a downstream application. The table is very large, and the downstream system can process files in parallel. To maximize the performance of the data unload operation, which COPY INTO @location option should be used?

    Show answer & explanation

    Correct answer: B

    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.

  8. Question 8Advanced

    Domain: Performance Optimization · Troubleshoot underperforming queries.

    A query that previously completed in 2 minutes is now taking over 30 minutes. The query profile shows a significant amount of time spent in External Scan, and the pruning statistics show that almost all micro-partitions of an external table are being scanned. The external table is partitioned by date (.../yyyy/mm/dd/) in the S3 bucket. The data engineer confirms the query has a WHERE clause filtering on the partition key. What is the MOST likely cause of the performance degradation?

    Show answer & explanation

    Correct answer: B

    External tables rely on metadata stored in Snowflake to know which files exist in the external stage for partition pruning. If new files (partitions) are added to the S3 bucket and the external table's metadata is not refreshed using ALTER EXTERNAL TABLE ... REFRESH, Snowflake's query optimizer will not be aware of them. This forces a full scan of the external stage location, bypassing partition pruning and causing a massive performance drop.

  9. Question 9Beginner

    Domain: Data Movement · Design, build and troubleshoot continuous data pipelines.

    True or False: A STANDARD stream on a table can be used to track changes for both INSERT and UPDATE operations, but it cannot be used to track DELETE operations.

    Show answer & explanation

    Correct answer: B

    A STANDARD stream, the default type, tracks all DML changes (INSERT, UPDATE, DELETE) on a source table. It includes metadata columns like METADATA$ACTION ('INSERT' or 'DELETE') and METADATA$ISUPDATE (TRUE/FALSE) to identify the type of change for each record in the stream.

  10. Question 10Advanced

    Domain: Data Transformation · Design, Build, and Leverage Stored Procedures.

    A data engineer is writing a Python stored procedure that needs to perform a series of DML operations as a single atomic transaction. If any of the operations fail, all previous operations within the procedure should be rolled back. How should the engineer implement this transactional logic within the Snowpark Python code?

    Show answer & explanation

    Correct answer: A

    Snowpark sessions are auto-committing by default. To manage transactions explicitly within a stored procedure, you should use standard SQL transaction commands executed via session.sql(). The standard pattern is to wrap the DML operations in a try...except block. You execute session.sql('BEGIN') before the block, perform DML inside the try clause, call session.sql('COMMIT') at the end of the try clause, and call session.sql('ROLLBACK') in the except clause to handle any failures.

Ready for the real thing?

The full SnowPro-Advanced-Data-Engineer simulator has every exam-style question, timed mode, and instant scoring.

Go to the SnowPro-Advanced-Data-Engineer simulator →