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

1. A large e-commerce company is experiencing performance issues with its daily sales report queries. These queries aggregate data from a fact table 'SALES FACT (100 billion rows) and several dimension tables, including 'CUSTOMER DIM', 'PRODUCT DIM', and 'DATE DIM'. The queries are run every morning and are essential for business decision-making. The team has identified that the 'SALES FACT table's primary key is 'SALE ID, but the queries frequently filter and join on 'CUSTOMER and 'PRODUCT ID. You want to use query acceleration service for these reports without changing query logic. Which combination of actions will MOST effectively leverage query acceleration service, assuming sufficient credits?

A) Enable Automatic Clustering on the 'SALES FACT table based on 'CUSTOMER ID' and 'PRODUCT ID, then enable query acceleration on the virtual warehouse.
B) Create materialized views that pre-aggregate the sales data based on 'CUSTOMER ID', 'PRODUCT ID, and 'DATE ID, then enable query acceleration on the virtual warehouse.
C) Enable clustering on the 'CUSTOMER DIM' and 'PRODUCT DIMS tables.
D) Increase the size of the virtual warehouse used for running the reports and enable query acceleration. Set the parameter to a high value.
E) Enable search optimization on the columns 'CUSTOMER ID' and 'PRODUCT ID of the 'SALES FACT table, then enable query acceleration on the virtual warehouse. Set the QUERY_ACCELERATION_MAX_SCALE_FACTOR parameter to a reasonable value based on testing.

2. You have an external table in Snowflake pointing to data in Azure Blob Storage. The data consists of customer transactions, and new files are added to the Blob Storage daily You want to ensure that Snowflake automatically picks up these new files and reflects them in the external table without manual intervention. However, you are observing delays in Snowflake detecting the new files. What are the potential reasons for this delay and how can you troubleshoot them? (Choose two)

A) The Azure Event Grid notification integration is not properly configured to notify Snowflake about new file arrivals in the Blob Storage.
B) The storage integration associated with the external table does not have sufficient permissions to access the Blob Storage.
C) The external table's 'AUTO_REFRESH' parameter is set to 'FALSE', which disables automatic metadata refresh.
D) Snowflake's internal cache is not properly configured; increasing the cache size will solve the problem.
E) The file format used for the external table is incompatible with the data files in Blob Storage.

3. A data engineer is using Snowpark Scala to create a UDF that calculates the distance between two geographical coordinates (latitude and longitude) using the Haversine formula'. The function should accept four 'Double' values (latl, lonl , lat2, lon2) and return the distance in kilometers as a 'Double'. The UDF must be named 'haversine distance'. What is the correct Scala code to define and register this UDF with Snowflake, including the import statements required for using Snowpark functions?

A) Option A
B) Option B
C) Option D
D) Option C
E) Option E

4. You are developing a JavaScript UDF in Snowflake to perform complex data validation on incoming data'. The UDF needs to validate multiple fields against different criteria, including checking for null values, data type validation, and range checks. Furthermore, you need to return a JSON object containing the validation results for each field, indicating whether each field is valid or not and providing an error message if invalid. Which approach is the MOST efficient and maintainable way to structure your JavaScript UDF to achieve this?

A) Utilize a JavaScript library like Lodash or Underscore.js within the UDF to perform data manipulation and validation. Return a JSON string containing the validation results.
B) Use a single, monolithic JavaScript function with nested if-else statements to handle all validation logic. Return a JSON string containing the validation results.
C) Create separate JavaScript functions for each validation check (e.g., 'isNull', 'isValidType', 'isWithinRange'). Call these functions from the main UDF and aggregate the results into a JSON object.
D) Define a JavaScript object containing validation rules and corresponding validation functions. Iterate through the object and apply the rules to the input data, collecting the validation results in a JSON object. This object is returned as a string.
E) Directly embed SQL queries within the JavaScript UDF to perform data validation checks using Snowflake's built-in functions. Return a JSON string containing the validation results.

5. You have a Snowflake table called 'ORDERS' with a column named 'ORDER DETAILS that contains JSON data'. You need to create a UDF to extract a specific value from the JSON and return it. The JSON structure is consistent: '{'customer': {'name': 'John Doe', 'email': '[email protected]'}, 'items': [{'item id': 1, 'quantity': 2}, {'item id': 2, 'quantity': You want to extract the customer's email address. Which of the following UDF definitions is the most efficient and correct?

A) Option A
B) Option B
C) Option D
D) Option C
E) Option E

Solutions: