Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions:

1. You are designing a data pipeline that uses the Snowflake SQLAPI to execute a series of complex SQL queries. These queries involve multiple joins, aggregations, and user-defined functions (UDFs). You need to ensure that the pipeline is resilient to transient network errors and can handle a large volume of concurrent requests. Which of the following strategies would you implement to enhance the reliability and performance of your pipeline?

A) Implement exponential backoff and retry logic in your client application to handle transient errors when calling the SQL API.
B) Use the SQL API's asynchronous execution mode and poll for query status to handle long-running queries without blocking.
C) Increase the timeout value for the SQL API requests to accommodate potentially long-running queries.
D) Bundle all the SQL queries into a single, large SQL statement to reduce the number of API calls.
E) Disable query result caching in Snowflake to ensure that the pipeline always retrieves the latest data.

2. You are tasked with building a Snowpipe to ingest JSON data from an AWS S3 bucket into a Snowflake table named 'SALES DATA'. The data is landing in the bucket frequently, and you want to use Snowpipe's auto-ingest feature. However, you are observing significant latency in data appearing in your Snowflake table after it lands in S3, despite verifying that S3 event notifications are correctly configured and the Snowflake event queue is receiving them. You've already checked that the pipe is enabled and has the necessary permissions. The Snowflake Pipe definition is as follows:

What is the MOST LIKELY reason for this delay, and what steps can you take to further troubleshoot?

A) Snowflake's internal metadata cache is out of sync. Run 'ALTER PIPE SALES PIPE to refresh the cache.
B) Snowpipe auto-ingest only supports CSV files. Convert your JSON data to CSV format before loading.
C) There is a backlog of files in the internal Snowflake queue waiting to be processed. Monitor the 'SYSTEM$PIPE STATUS' function and consider increasing the 'MAX CONCURRENCY' parameter (if applicable, based on underlying infrastructure considerations) on the pipe definition.
D) The S3 bucket is not in the same region as the Snowflake account. Ensure the S3 bucket and Snowflake account are in the same region to reduce network latency.
E) The Snowflake virtual warehouse associated with the pipe is undersized. Increase the warehouse size to improve ingestion performance.

3. You are designing a data governance strategy for a Snowflake data warehouse. One of the key requirements is to track data lineage for sensitive data, specifically Personally Identifiable Information (PII). You need to understand how PII data flows through various transformations and tables. Which Snowflake feature, when combined with appropriate tagging and metadata management practices, can BEST help you achieve this?

A) Snowflake Cloning
B) Snowflake Data Masking policies
C) Snowflake INFORMATION_SCHEMA views and Account Usage views
D) Snowflake's Resource Monitors
E) Snowflake Secure Data Sharing

4. You're building a data pipeline that ingests JSON data from URLs representing real-time weather information. The data structure varies slightly between different weather providers, but all contain a 'location' object with 'city' and 'country' fields, and a 'temperature' field. You need to create a generic function that can handle these variations and extract the location and temperature, returning a flattened JSON object with keys 'city', 'country', and 'temperature'. You want to avoid explicit schema definition and take advantage of Snowflake's VARIANT data type flexibility Given the following sample JSON structures, which approach will best accomplish this?

A) Define a Snowflake stored procedure that uses 'SYSTEM$URL_GET to fetch the JSON data, then uses conditional logic with 'TRY TO BOOLEANS and STRY TO DATE to handle different data types. The stored procedure constructs a new JSON object with 'city', 'country', and 'temperature' fields using 'OBJECT_CONSTRUCT.
B) Create a pipe that uses 'COPY INTO to ingest JSON data directly from the URLs into a VARIANT column. The 'FILE FORMAT object is configured to use = TRUE to handle different data types. Post ingestion create a view to query data.
C) Create a Snowflake external function written in Java that uses 'java.net.lJRL' to fetch the JSON data and 'com.fasterxml.jackson.databind' library to parse it. Use Jackson's 'JsonNode' to navigate the varying JSON structure and extract 'city', 'country', and 'temperature' fields. Return a JSON string of the result.
D) Define a Snowflake view that selects from a table containing the URLs, using 'SYSTEM$URL GET to fetch the JSON data and to extract the 'city', 'country', and 'temperature' fields. Use 'TRY_CAST to convert the 'temperature' to a numeric type.
E) Define a Snowflake external function (UDF) that fetches the JSON data using a Python library like 'requests' or The function then parses the JSON and extracts the required fields, handling potential missing fields using 'try...except' blocks. The function returns a JSON string representing the flattened object.

5. You have a table named 'ORDERS' with a column 'ORDER DETAILS' that contains JSON data'. You want to extract a specific nested value ('customer id') from this JSON data using a SQL UDE The JSON structure varies, and sometimes the 'customer id' field might be missing. You need to create a UDF that handles missing fields gracefully and returns NULL if 'customer id' is not found. Also, You are looking for a performant solution that is highly scalable. Which of the following SQL UDF definitions is most appropriate?

A)

B)

C)

D)

E)

Solutions: