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

A Snowflake table 'ORDERS' is clustered on the 'ORDER DATE column. After several months, you notice that many micro-partitions contain data from a wide range of 'ORDER DATE values, and query performance on date range filters is degrading. Which of the following actions could improve performance and reduce the overlap in micro-partitions?

• A. Increase the virtual warehouse size used for loading data into the "ORDERS' table.
• B. Change the ORDER DATE column to VARCHAR and then cluster.
• C. Run 'OPTIMIZE TABLE ORDERS' to recluster the table.
• D. Drop and recreate the table with a different clustering key.
• E. Change the clustering key to a composite key including ORDER DATE and another relevant column.

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. 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.
• 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. 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.
• D. 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
• E. 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.

A data engineering team is building a real-time dashboard in Snowflake to monitor website traffic. The dashboard relies on a complex query that joins several large tables. The query execution time is consistently exceeding the acceptable threshold, impacting dashboard responsiveness. Historical data is stored in a separate table and rarely changes. You suspect caching is not being utilized effectively. Which of the following actions would BEST improve the performance of this dashboard and leverage Snowflake's caching features?

• A. Use 'RESULT_SCAN' to cache the query result in the user session for subsequent queries. This is especially effective for large datasets that don't change frequently.
• B. Increase the size of the virtual warehouse. A larger warehouse will have more resources to execute the query, and the results will be cached for a longer period.
• C. Materialize the historical data into a separate table that utilizes clustering and indexing for faster query performance. Refresh this table periodically.
• D. Replace the complex query with a series of simpler queries. This will reduce the amount of data that needs to be processed at any one time.
• E. Create a materialized view that pre-computes the results of the complex query. Snowflake will automatically refresh the materialized view when the underlying data changes.

A data engineer is tasked with creating a Listing to share a large dataset stored in Snowflake. The dataset contains sensitive Personally Identifiable Information (PII) that must be masked for certain consumer roles. The data engineer wants to use Snowflake's dynamic data masking policies within the Listing to achieve this. Which of the following approaches is the MOST secure and maintainable way to implement this requirement, assuming that the consumer roles are pre-defined and known?

• A. Implement an external function that masks the data based on the consumer's role and share this function in the Listing. Use this external function in a view shared through the listing.
• B. Create multiple versions of the shared tables, each with different masking applied. The data engineer must manually manage which version each consumer can access.
• C. Create a view that applies conditional masking using 'CASE' statements based on the function and share the view in the Listing.
• D. Apply dynamic data masking policies directly to the base tables containing the PII and share these tables in the Listing. Policies should use the function to determine when to mask the data.

You are tasked with creating a JavaScript UDF in Snowflake to parse JSON data containing nested arrays of objects. The UDF needs to extract specific values from these nested objects and return them as a comma-separated string. Given the JSON structure below, and the requirement to extract the 'value' field from each object within the 'items' array located inside each element of the 'data' array, which of the following JavaScript UDF definitions will correctly achieve this, assuming the input JSON is passed as a string?

• A. Option D
• B. Option E
• C. Option A
• D. Option C
• E. Option B