SnowPro Advanced: Data Engineer (DEA-C02) - DEA-C02 Exam Practice Test

Question 1

You are tasked with building a data pipeline to process image metadata stored in JSON format from a series of URLs. The JSON structure contains fields such as 'image_url', 'resolution', 'camera_model', and 'location' (latitude and longitude). Your goal is to create a Snowflake table that stores this metadata along with a thumbnail of each image. Given the constraints that you want to avoid downloading and storing the images directly in Snowflake, and that Snowflake's native functions for image processing are limited, which of the following approaches would be most efficient and scalable?

A. Create a Snowflake external table that points to an external stage which holds the JSON metadata files. Develop a spark process to fetch image URL, create thumbnails and store as base64 encoded strings in an external stage, create a view using the external table and generated thumbnails data

B. Create a Snowflake stored procedure that iterates through each URL, downloads the JSON metadata using 'SYSTEM$URL_GET, extracts the image URL from the metadata, downloads the image using 'SYSTEM$URL_GET , generates a thumbnail using SQL scalar functions, and stores the metadata and thumbnail in a Snowflake table.

C. Store just the 'image_url' in snowflake. Develop a separate application using any programming language to pre generate the thumbnails and host those at publicly accessible URLs. Within Snowflake, create a view to generate the links for image and thumbnail using 'CONCAT.

D. Create a Snowflake view that selects from a table containing the metadata URLs, using 'SYSTEM$URL GET to fetch the metadata. For each image URL found in the metadata, use a JavaScript UDF to generate a thumbnail. Embed the thumbnail into a VARCHAR column as a Base64 encoded string.

E. Create a Python-based external function that fetches the JSON metadata and image from their respective URLs. The external function uses libraries like PIL (Pillow) to generate a thumbnail of the image and returns the metadata along with the thumbnail's Base64 encoded string within a JSON object.

Correct Answer: C,E

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 2

You are designing a data loading process for a high-volume streaming data source. The data arrives as Avro files in an AWS S3 bucket. You need to load this data into a Snowflake table with minimal latency and operational overhead. Which of the following combinations of Snowflake features and configurations would be MOST suitable for this scenario? (Select TWO)

A. Implement Snowpipe with auto-ingest configured to listen for S3 event notifications whenever a new Avro file is added to the bucket.

B. Use the 'COPY INTO' command with a scheduled task that runs every 5 minutes to load new files from the S3 bucket.

C. Configure an external table pointing to the S3 bucket and query the Avro files directly from Snowflake.

D. Create a custom Spark application that reads Avro files from S3, transforms the data, and then writes it to Snowflake using the Snowflake Spark connector.

E. Use a Kafka connector to stream data directly from the Kafka topic to Snowflake.

Correct Answer: A,E

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 3

You are tasked with designing a data pipeline to load data from an Azure Blob Storage container into Snowflake using an external stage. The data is in CSV format, compressed using GZIP. The container contains millions of small CSV files. To optimize the data loading process and minimize cost, which of the following strategies would you implement, considering both stage configuration and COPY INTO options? Choose TWO that apply.

A. Create a pipe object with 'AUTO INGEST = TRUE to automatically ingest new files as they are added to the Azure Blob Storage container. This ensures near real-time data ingestion.

B. Leverage Snowflake's Snowpipe with a REST API endpoint to trigger data loads whenever new files are available in the Azure Blob Storage container.

C. Use the 'VALIDATION MODE = RETURN ERRORS option in the 'COPY INTO' statement to identify and correct any data quality issues during the load. This ensures that only clean data is loaded into Snowflake.

D. Consolidate the small CSV files in the Azure Blob Storage container into larger files before loading them into Snowflake. This reduces the overhead of processing numerous small files.

E. Use the 'MATCH BY COLUMN NAME = CASE INSENSITIVE option with a copy transformation in the 'COPY INTO' statement to ensure that the column order in the CSV files doesn't affect the data load.

Correct Answer: B,D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 4

You're designing a data masking solution for a 'CUSTOMER' table with columns like 'CUSTOMER ID', 'NAME', 'EMAIL', and 'PHONE NUMBER. You want to implement the following requirements: 1. The 'SUPPORT' role should be able to see the last four digits of the 'PHONE NUMBER and a hashed version of the 'EMAIL'. 2. The 'MARKETING' role should be able to see the full 'NAME' and a domain-only version of the 'EMAIL' (everything after the '@' symbol). 3. All other roles should see masked values for 'EMAIL' and 'PHONE NUMBER. Which of the following masking policy definitions BEST achieves these requirements using Snowflake's built-in functions and RBAC?

A.

B.

C.

D.

E.

Correct Answer: A

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 5

You are building a data pipeline to ingest clickstream data into Snowflake. The raw data is landed in a stage and you are using a Stream on this stage to track new files. The data is then transformed and loaded into a target table 'CLICKSTREAM DATA. However, you notice that sometimes the same files are being processed multiple times, leading to duplicate records in 'CLICKSTREAM DATA. You are using the 'SYSTEM$STREAM HAS DATA' function to check if the stream has data before processing. What are the possible reasons this might be happening, and how can you prevent it? (Select all that apply)

A. The stream offset is not being advanced correctly after processing the files. Ensure that the files are consumed completely and a DML operation is performed to acknowledge consumption.

B. The transformation process is not idempotent. Even with the same input files, it produces different outputs each time it runs.

C. The COPY INTO command used to load the files into Snowflake has the 'ON ERROR = CONTINUE option set, allowing it to skip corrupted files, causing subsequent processing to pick them up again.

D. The 'SYSTEM$STREAM HAS DATA' function is unreliable and should not be used for production data pipelines. Use 'COUNT( on the stream instead.

E. The auto-ingest notification integration is configured incorrectly, causing duplicate notifications to be sent for the same files. This is particularly applicable when using cloud storage event triggers.

Correct Answer: A,B,E

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 6

A data engineering team is running a series of complex analytical queries against a large Snowflake table. They notice that query performance is inconsistent, with some queries running much slower than others. After investigation, they determine that the queries are not properly leveraging the data clustering. Which of the following actions could improve the query performance related to the data clustering? Select all that apply.

A. Run 'SHOW TABLES LIKE ' ' and review the 'clustering_information' column to understand the current clustering depth and benefit.

B. Execute SYSTEM$CLUSTERING INFORMATION( ' )' y to assess the impact of clustering on query performance and determine if re-clustering is needed.

C. Run 'ALTER TABLE CLUSTER BY (columnl, column2Y to explicitly define a clustering key on the table.

D. Run 'ALTER TABLE DROP CLUSTERING KEY to remove the existing clustering key and allow Snowflake to automatically cluster the data.

E. Increase the virtual warehouse size to improve the performance of all queries, regardless of clustering.

Correct Answer: A,B,C

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 7

A financial services company stores sensitive customer data, including credit card numbers, in a Snowflake table called 'CUSTOMER DATA. You need to implement dynamic data masking on the 'CREDIT CARD NUMBER column. You want to ensure that only users with the FINANCE ADMIN' role can view the unmasked credit card numbers. All other users should see a masked version of the data'. Which of the following set of commands is the MOST efficient and secure way to achieve this?

A. Option E

B. Option D

C. Option B

D. Option A

E. Option C

Correct Answer: C

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 8

You have an external table named in Snowflake that points to a set of CSV files in an AWS S3 bucket. The CSV files have a header row, and the data is comma-separated. However, some of the files in the S3 bucket are gzipped. You need to define the external table to correctly read both compressed and uncompressed files. Which of the following SQL statements BEST achieves this?

A. Option E

B. Option D

C. Option B

D. Option A

E. Option C

Correct Answer: C

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 9

A retail company wants to store product data in a Snowflake VARIANT column. The product data is currently in a relational table called 'PRODUCTS' with columns 'PRODUCT ID', 'PRODUCT NAME, 'CATEGORY, 'PRICE, and 'DISCOUNT. They want to create a JSON structure where each product is represented as a JSON object, and the entire result set is a JSON array. Which of the following SQL statements will achieve this transformation most efficiently?

A. Option E

B. Option D

C. Option B

D. Option A

E. Option C

Correct Answer: C

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 10

A. Create a new table with columns for 'item_id' and 'price' using the 'EVENT DATA column. Refreshed in a regular interval and used in Downstream querying.

B. Use the ' GET_PATH' function repeatedly to extract 'item_id' and 'price' in the main query.

C. Create a virtual column for 'item_id' and 'price' using JSON path expressions and create indexes on these virtual columns.

D. Create a view that casts the 'EVENT DATA' column to VARCHAR before extracting attributes.

E. Create a search optimization service for the table 'USER_ACTIVITY to help filtering data in downstream

Correct Answer: A,E

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) - DEA-C02 Exam Practice Test

Latest Update

Useful Links

Contact Us