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

A Snowflake Data Engineer is tasked with identifying all downstream dependencies of a view named 'CUSTOMER SUMMARY. This view is used by multiple dashboards and reports. They want to use SQL to efficiently find all tables and views that directly depend on 'CUSTOMER SUMMARY. Which of the following SQL queries against the ACCOUNT USAGE schema is the MOST efficient and accurate way to achieve this?

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

You are planning to monetize a dataset on the Snowflake Marketplace. You want to provide potential customers with sample data to evaluate before they purchase a full subscription. Which of the following strategies are valid and recommended for offering a free sample of your data within the Snowflake Marketplace? (Select all that apply)

• A. Create a view that filters the dataset based on a sampling algorithm (e.g., 'SAMPLE ROW' clause) and share the view through the Marketplace.
• B. Create a separate share containing a subset (e.g., a smaller number of rows or columns) of the full dataset and offer this share as a free trial listing on the Marketplace.
• C. Upload a sample CSV file to a publicly accessible S3 bucket and provide the link in the Marketplace listing description. Consumers can download and load this data into their own Snowflake account for evaluation.
• D. Offer a 'free trial' subscription on the primary listing that automatically expires after a set period (e.g., 7 days), allowing customers to access the full dataset during the trial period. You will need to write custom code to manage trial expiration and data access restrictions based on the trial status.
• E. Provide the consumer with the script to create a database link to your data, allowing them read-only access to a pre-defined sample table, and then revoke the access after a set period.

You have a Snowpark Python application that performs complex calculations on a large dataset stored in Snowflake. The application is currently running slowly. After profiling, you've identified that the UDFs you're using are the bottleneck. These UDFs perform custom data transformations using a third-party Python library which has a significant initialization overhead. Which of the following strategies would be MOST effective to optimize performance, minimizing both runtime and resource consumption?

• A. Rewrite the UDFs in SQL using Snowflake's built-in functions to avoid the overhead of Python execution. If the library's functions aren't available, consider creating external functions using a cloud provider's serverless compute service.
• B. Implement UDF caching at the Snowflake level by setting the 'VOLATILE property to 'IMMUTABLE or 'STABLE' (if appropriate), and leverage the Snowflake query result cache.
• C. Increase the size of the Snowflake warehouse being used for the Snowpark workload. This will provide more CPU and memory resources.
• D. Convert the Snowpark Python application to a Snowpark Java application as Java generally offers better performance than Python.
• E. Use Snowpark's 'pandas_udf with 'vectorized=True' and pre-initialize the third-party library within the UDF's execution context using a closure or similar technique for reuse across batches.

You are designing a data product for the Snowflake Marketplace that provides daily weather forecasts. You need to ensure that consumers of your data receive the latest forecast data every morning automatically with minimal latency. Which of the following strategies offers the MOST efficient and cost-effective solution for updating the shared data?

• A. Share the raw data files stored in an external stage with the consumers. Consumers will then need to create their own pipelines to process and load the data.
• B. Implement a continuous data pipeline using Snowflake Streams and Tasks to incrementally update the shared tables as new forecast data becomes available. The stream tracks changes and tasks apply those changes to the shared tables.
• C. Manually upload a new CSV file containing the latest forecast data to a Snowflake stage and then load it into the shared tables every morning at 6 AM.
• D. Create a stored procedure that truncates and reloads the shared tables with the latest forecast data from a staging table. Schedule this stored procedure to run every morning at 6 AM using a Snowflake task.
• E. Create a scheduled task that executes a full refresh of the shared tables every morning at 6 AM. This task uses CREATE OR REPLACE TABLE AS SELECT to rebuild the tables with the latest forecast data.

You have created an external table in Snowflake that points to a large dataset stored in Azure Blob Storage. The data consists of JSON files, and you've noticed that query performance is slow. Analyzing the query profile, you see that Snowflake is scanning a large number of unnecessary files. Which of the following strategies could you implement to significantly improve query performance against this external table?

• A. Create a materialized view on top of the external table to pre-aggregate the data.
• B. Increase the size of the Snowflake virtual warehouse to provide more processing power.
• C. Partition the data in Azure Blob Storage based on a relevant column (e.g., date) and define partitioning metadata in the external table definition using PARTITION BY.
• D. Create an internal stage, copy all JSON Files, create and load the target table, and drop external table
• E. Convert the JSON files to Parquet format and recreate the external table to point to the Parquet files.