Pass DEA-C02 Brain Dump Updated Certification Sample Questions [Q103-Q121]

Pass DEA-C02 Brain Dump Updated Certification Sample Questions

Online DEA-C02 Test Brain Dump Question and Test Engine

NEW QUESTION # 103
You have a Python UDF in Snowflake designed to enrich customer data by calling an external API to retrieve additional information based on the customer ID. Due to API rate limits, you need to implement a mechanism to cache API responses within the UDF to avoid exceeding the limits. The UDF is defined as follows:

Which caching mechanism can be implemented MOST effectively WITHIN the Python UDF to minimize API calls while adhering to Snowflake's UDF limitations?

• A. Utilize Snowflake's built-in caching mechanisms (result caching) by ensuring the UDF is deterministic and only depends on its input parameters. Snowflake will automatically cache the results of the UDF for subsequent calls with the same input.
• B. Persist the API responses in a temporary table within Snowflake. The UDF will first query the temporary table for the customer ID; if found, return the cached data. Otherwise, call the API and store the response in the temporary table for future use.
• C. Use the 'functools.lru_cache' decorator to cache the results of the 'get_customer details' function within the UDF's scope. This will automatically cache the most recently used API responses.
• D. Leverage external caching services like Redis by making API calls to Redis from the UDF to store and retrieve cached API responses. This would require configuring Snowflake to connect with external systems.
• E. Create a global dictionary within the UDF to store the API responses, using the customer ID as the key. Before calling the API, check if the customer ID exists in the dictionary; if it does, return the cached response. This approach will keep cached values during the session.

Answer: C

Explanation:
Using 'functools.lru_cache' (Option A) is the most efficient and straightforward solution. It provides a built-in caching mechanism within the Python UDF's scope without requiring external dependencies or complex manual caching logic. Option B is not the best, as it will cause issues in multithreaded environment where this is not thread safe and could cause data inconsistency. Option C is related to Snowflake result cache which is independent of UDF cache needs and concerns. The temp table (option D) adds overhead by querying external tables within the UDF, making API execution slower rather than faster. And Option E needs external connections which increase infrastructure complexity.

NEW QUESTION # 104
You are tasked with creating a resilient data pipeline using Snowpark Python. The pipeline transforms data from a raw stage to a processed stage. A key transformation involves joining two DataFrames, 'dfl' and 'df2 , based on a common column, 'id'. You want to ensure that even if 'df2 is temporarily unavailable or contains unexpected data, the pipeline continues to process 'dfl' using a default value for missing data from 'df2. Which of the following approaches provides the best balance of resilience and data integrity? Assume you have defined a default dataframe 'df default' already.

• A. Perform a 'left_outer' join of 'dfl' with 'df2. If 'df2 is unavailable or returns no data, replace 'df2' with a default DataFrame Cdf_default) and proceed with the join.
• B. Use 'broadcast hint on 'df2 before performing the join to reduce the chances of join failure, assuming 'df2 is a small dataframe.
• C. Use a 'try-except' block to catch any exceptions during the join operation. If an exception occurs, use the 'fillna()' method to replace missing values with the default data value.
• D. Perform a 'left_outer' join of 'dfl' with 'df2. If the join fails, catch the exception and proceed without the join.
• E. Write a custom Python UDF that attempts to retrieve the corresponding data from 'df2 based on the 'id' column. If the retrieval fails for a particular ID, return a default value.

Answer: A

Explanation:
Option C provides the best balance. A join ensures that all rows from 'dfl are preserved, even if there's no matching row in 'df2. By replacing 'df2 with 'df_default' when 'df2' is unavailable/empty, the pipeline continues processing 'dfl with predefined defaults, maintaining data integrity. Option A only bypasses the exception without using default value. Option B is incorrect because 'fillna' may not handle the case where 'df2 is unavailable and the whole join fails. Option D may only help with performance but does not guarantee that df2 will be available at all times. Option E is also not efficient due to UDF being slower than joins.

NEW QUESTION # 105
You are tasked with migrating data from a legacy SQL Server database to Snowflake. One of the tables, 'ORDERS' , contains a column 'ORDER DETAILS that holds concatenated string data representing multiple order items. The data is formatted as 'iteml :qtyl ;item2:qty2;...'. You need to transform this string data into a JSON array of objects, where each object represents an item with 'name' and 'quantity' fields. Which of the following steps and functions would you use in Snowflake to achieve this transformation, in addition to loading the data?

• A. Use to split the string into rows, then use 'SPLIT to separate item name and quantity, and finally use 'OBJECT_CONSTRUCT and to create the JSON array.
• B. Use ' STRTOK TO ARRAY' to split the string into an array, then iterate through the array using a JavaScript UDF to create the JSON objects.
• C. Utilize a Java UDF to parse the string and directly generate the JSON array.
• D. Use ' to extract item names and quantities, then use 'ARRAY_CONSTRUCT and 'OBJECT_CONSTRUCT to create the JSON array.
• E. Use 'SPLIT with ';' as delimiter, then apply 'SPLIT again with ':' as delimiter. Finally, construct the JSON array using 'ARRAY_AGG' and 'OBJECT CONSTRUCT

Answer: A,E

Explanation:
Options A and D correctly outline the process. (A) and multiple 'SPLIT calls (D) are valid approaches to break down the concatenated string. Then, 'OBJECT_CONSTRUCT builds the individual JSON objects, and aggregates them into a JSON array. While Javascript or Java UDFs (C, E) could solve the problem, they are generally less efficient than Snowflake's built-in functions. (B) might work but is overkill for this simple splitting task, also you would still need to combine the extracted arrays for items and quantities.

NEW QUESTION # 106
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. Increase the size of the virtual warehouse used for running the reports and enable query acceleration. Set the parameter to a high value.
• 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. Enable Automatic Clustering on the 'SALES FACT table based on 'CUSTOMER ID' and 'PRODUCT ID, then enable query acceleration on the virtual warehouse.
• 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.

Answer: E

Explanation:
Enabling search optimization on and in the 'SALES FACT table is the most effective approach for query acceleration in this scenario. This allows Snowflake to efficiently find the rows needed for the reports without scanning the entire table. Automatic Clustering improves data organization, but it doesn't directly accelerate individual queries in the same way. Materialized views are also useful, but require additional storage and maintenance. Simply increasing the warehouse size and enabling query acceleration without addressing the data organization or indexing might not be as cost-effective. Clustering dimension tables won't affect the performance on the large fact table.

NEW QUESTION # 107
A Snowflake data engineer is troubleshooting a slow-running query that joins two large tables, 'ORDERS' (1 billion rows) and 'CUSTOMER' (10 million rows), using the 'CUSTOMER ID' column. The query execution plan shows a significant amount of data spilling to local disk. The query is as follows:

Which of the following are the MOST likely root causes of the disk spilling and the best corresponding solutions? Select two options that directly address the disk spilling issue.

• A. The join operation is resulting in a large intermediate result set that exceeds the available memory. Apply a filter on the 'ORDERS' table to reduce the data volume before the join.
• B. The statistics on the tables are outdated. Run 'ANALYZE TABLE ORDERS' and 'ANALYZE TABLE CUSTOMER to update the statistics.
• C. The query is performing a full table scan on the 'ORDERS' table. Add an index on the 'CUSTOMER ID column in the 'ORDERS table.
• D. The virtual warehouse is undersized for the amount of data being processed. Increase the virtual warehouse size to provide more memory.
• E. The 'CUSTOMER_ID column is not properly clustered in either the 'ORDERS' or 'CUSTOMER table. Define a clustering key on 'CUSTOMER_ID for both tables.

Answer: A,D

Explanation:
Options A and D are the most direct solutions for disk spilling. A undersized warehouse directly impacts available memory, leading to disk spilling. Increasing the warehouse size (option A) provides more memory for the operation. When data spill happens increasing the warehouse size is the primary action to take. Option D correctly addresses the root cause of the spill an overly large intermediate result set. Reducing the data volume before the join minimizes the memory required. Option B could improve query performance overall, but doesn't directly address disk spilling. Option C is incorrect, as Snowflake does not support manual indexes. Option E would improve the accuracy of the query optimizer's decisions, which could indirectly improve performance, but is less direct than options A and D.

NEW QUESTION # 108
Your company has a Snowflake account in the AWS cloud (us-west-2). You are planning to implement a disaster recovery strategy by replicating data to a separate Snowflake account in the Azure cloud (eastus2). You need to replicate multiple databases and shared objects. Which of the following steps are REQUIRED to configure and manage the replication process successfully? (Choose all that apply)

• A. Create a replication group in the source AWS account and add the databases and shared objects to it.
• B. Configure network policies in both AWS and Azure accounts to allow communication between the Snowflake instances, particularly ingress and egress rules.
• C. Create a storage integration in the target Azure account and grant the 'USAGE privilege on it to the replication group.
• D. Grant the REPLICATE privilege on the source AWS account to the account locator of the target Azure account.
• E. Create a secondary database in the target Azure account using the 'CREATE DATABASE AS REPLICA OF command.

Answer: A,B,D

Explanation:
A, C, and D are required steps. Option A: Creating a replication group is essential to define what to replicate. Option C: The REPLICATE privilege allows the target account to pull data from the source. Option D: Network policies are crucial for establishing secure communication. Option B is incorrect; you create a secondary database using 'CREATE DATABASE AS REPLICA OF : after enabling replication on the source, not before. Option E is related to data loading from external stages, not replication itself in this direct account-to-account scenario.

NEW QUESTION # 109
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 'ALTER TABLE DROP CLUSTERING KEY to remove the existing clustering key and allow Snowflake to automatically cluster the data.
• B. Run 'ALTER TABLE CLUSTER BY (columnl, column2Y to explicitly define a clustering key on the table.
• C. Run 'SHOW TABLES LIKE ' ' and review the 'clustering_information' column to understand the current clustering depth and benefit.
• D. Increase the virtual warehouse size to improve the performance of all queries, regardless of clustering.
• E. Execute SYSTEM$CLUSTERING INFORMATION( ' )' y to assess the impact of clustering on query performance and determine if re-clustering is needed.

Answer: B,C,E

Explanation:
Option A is correct because explicitly defining a clustering key allows Snowflake to optimize data organization for common query patterns. Option C is correct because understanding the current clustering state helps diagnose if clustering is effective. Option E is correct, using SYSTEM$CLUSTERING INFORMATION is a crucial method to determine reclustering needs. Option B can worsen performance, especially if the automatic clustering does not align with common query patterns. Option D may improve performance to a small extend but doesn't directly address the clustering issue.

NEW QUESTION # 110
You are a data engineer responsible for data governance in a Snowflake environment. Your company has implemented data classification using tags to identify sensitive data'. The compliance team has requested a report detailing all tables and columns that contain PII data, specifically including the tag name, tag value, the fully qualified name of the table, and the column name. You have the necessary privileges to access the Snowflake metadata views. Which of the following queries would provide the MOST comprehensive and accurate report, considering performance and ease of understanding?

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

Answer: D

Explanation:
Option D provides the MOST comprehensive and accurate report. It directly queries the view, filtering for 'TAG_NAME = 'PII" and 'object_domain = 'COLUMN" to specifically target tags applied to columns. It selects the database, schema, table name, column name, tag name, and tag value, providing all the necessary information. Option A requires a JOIN between "snowflake.account_usage.columns' and , which is unnecessary for this use case and less efficient. option B is missing the OBJECT_DATABASE and OBJECT_SCHEMA which is needed to fully qualify the table. option C attempts to use a table function, which is unnecessary complexity and potentially less performant. Option E does not filter for column-level tags, potentially including tags applied to other object types (e.g., tables, views), leading to inaccurate results. The fully qualified name can be easily constructed from OBJECT DATABASE, OBJECT SCHEMA and OBJECT NAME.

NEW QUESTION # 111
You are configuring a Snowflake Data Clean Room for two healthcare providers, 'ProviderA' and 'ProviderB', to analyze patient overlap without revealing Personally Identifiable Information (PII). Both providers have patient data in their respective Snowflake accounts, including a 'PATIENT ID' column that uniquely identifies each patient. You need to create a secure join that allows the providers to determine the number of shared patients while protecting the raw 'PATIENT ID' values. Which of the following approaches is the most secure and efficient way to achieve this using Snowflake features? Select TWO options.

• A. Utilize Snowflake's Secure Aggregate functions (e.g., APPROX_COUNT_DISTINCT) on the 'PATIENT_ID' column without sharing the underlying data. Each provider calculates the approximate distinct count of patient IDs, and the results are compared to estimate the overlap.
• B. Leverage Snowflake's differential privacy features to add noise to the patient ID data, share the modified dataset and perform a JOIN.
• C. Create a hash of the 'PATIENT_ID' column in both ProviderA's and ProviderB's accounts using a consistent hashing algorithm (e.g., SHA256) and a secret salt known only to both providers. Share the hashed values through a secure view and perform a JOIN operation on the hashed values.
• D. Share the raw 'PATIENT_ID' columns between ProviderA and ProviderB using secure data sharing, and then perform a JOIN operation in either ProviderA's or ProviderB's account.
• E. Implement tokenization of the 'PATIENT_ID' column in both ProviderA's and ProviderB's accounts. Share the tokenized values through a secure view and perform a JOIN operation on the tokens. Use a third party to deanonymize the tokens afterwards.

Answer: C,E

Explanation:
Options B and C represents valid approach. B provides good utility and is consistent. C does the same using a third-party service, which also works. Option A exposes the raw PII data which is not acceptable. Option D only gets an approximate, not an exact figure. While useful, the other solutions are much better. Option E is incorrect, it sounds good, but is not real. Therefore the correct answer is B and C.

NEW QUESTION # 112
You are tasked with creating a Snowpark Python UDF that calculates the exponential moving average (EMA) of a time series dataset stored in a Snowflake table named 'SALES DATA'. The table has columns 'TIMESTAMP' (TIMESTAMP_NTZ) and 'SALES' (NUMBER). The EMA should be calculated for each product, identified by the 'PRODUCT ID' column. You want to optimize the calculation by using a Pandas DataFrame within the UDF and leveraging vectorized operations. Which of the following code snippets would be the MOST efficient and correct way to achieve this? Assume 'alpha' is a predefined float variable representing the smoothing factor.

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

Answer: D

Explanation:
Option E correctly defines a UDF that accepts a JSON string as input. The input JSON string represents a group of sales records which are converted to a Pandas Dataframe using 'pd.read_json'. The 'ewm' function is then used to calculate the EMA efficiently. The result is serialized back into JSON and returned. Other options fail because they incorrectly define the UDF either in terms of the types of parameters or not properly loading the dataframe. Options A uses Sprocs which is not the best fit for this scenario as it is meant for Stored procedures, and Option B and C have the wrong input and output tyypes.

NEW QUESTION # 113
You are developing a Snowpark Python stored procedure that performs complex data transformations on a large dataset stored in a Snowflake table named 'RAW SALES'. The procedure needs to efficiently handle data skew and leverage Snowflake's distributed processing capabilities. You have the following code snippet:

Which of the following strategies would be MOST effective to optimize the performance of this Snowpark stored procedure, specifically addressing potential data skew in the 'product id' column, assuming 'product_id' is known to cause uneven data distribution across Snowflake's micro-partitions?

• A. Utilize Snowflake's automatic clustering on the 'TRANSFORMED_SALES table by specifying 'CLUSTER BY when creating or altering the table to ensure future data is efficiently accessed.
• B. Use the 'pandas' API within the Snowpark stored procedure to perform the transformation, as 'pandas' automatically optimizes for data skew.
• C. Combine salting with repartitioning by adding a random number to the 'product_id' before repartitioning, then removing the salt after the transformation to break up the skew. Then, enable automatic clustering on the 'TRANSFORMED SALES' table.
• D. Implement a custom partitioning strategy using before the transformation logic to redistribute data evenly across the cluster.
• E. Increase the warehouse size significantly to compensate for the data skew and improve overall processing speed without modifying the partitioning strategy.

Answer: C

Explanation:
Option E is the most effective solution. Salting breaks up data skew before repartitioning. Automatic clustering on the transformed table optimizes future queries. Repartitioning redistributes the data across Snowflake's processing nodes, and Automatic Clustering will help in maintaining performance as the data changes in TRANSFORMED_SALES table over time. Option A, without salting, may still be inefficient due to the initial skew. Option B improves query performance but doesn't address the initial transformation skew. Option C is incorrect because 'pandas' in Snowpark does not automatically handle data skew at the Snowflake level. Option D is a costly workaround that doesn't fundamentally solve the skew problem.

NEW QUESTION # 114
You are tasked with implementing a Row Access Policy (RAP) on a table 'customer_data' that contains Personally Identifiable Information (PII). The policy must meet the following requirements: 1. Data analysts with the 'ANALYST role should only see anonymized customer data (e.g., masked email addresses, hashed names). 2. Data engineers with the 'ENGINEER role should see the full, unmasked customer data for data processing purposes. 3. No other roles should have access to the data'. You create the following UDFs: 'MASK EMAIL(email address VARCHAR)': Returns an anonymized version of the email address. 'HASH NAME(name VARCHAR): Returns a hash of the customer name. Which of the following is the most efficient and secure way to implement this RAP, assuming minimal performance impact is desired?

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

Answer: C

Explanation:
Option D is the most efficient because it filters access based on roles in the RAP without applying expensive UDFs within the policy itself. This minimizes the performance impact of the RAP. The view 'analyst_view' then applies the masking/hashing for analysts. Options A and B apply the UDFs within the RAP, which will significantly degrade performance. The 'MASK EMAIL(email_address) IS NOT NULL' conditions are also incorrect as they are not validating the email. Option C doesn't implement the required masking/hashing for analysts at all, and also is not as effecient. Option E allows both roles to see all data which does not meet requirement 1.

NEW QUESTION # 115
A data team is using Snowflake to analyze sensor data from thousands of IoT devices. The data is ingested into a table named 'SENSOR READINGS' which contains columns like 'DEVICE ID', 'TIMESTAMP', 'TEMPERATURE', 'PRESSURE', and 'LOCATION' (a GEOGRAPHY object). Analysts frequently run queries that calculate the average temperature and pressure for devices within a specific geographic area over a given time period. These queries are slow, especially when querying data from multiple months. Which of the following approaches, when combined, will BEST optimize the performance of these queries using the query acceleration service?

• A. Create a materialized view that pre-calculates the average temperature and pressure by device and location. Then enable query acceleration on the virtual warehouse.
• B. Enable Automatic Clustering on 'DEVICE_ID' , then enable query acceleration on the virtual warehouse.
• C. Cluster the table by 'LOCATION' and 'TIMESTAMP , and enable search optimization on the 'LOCATION' column, and then enable query acceleration.
• D. Enable search optimization on 'TEMPERATURE and 'PRESSURE columns and enable query acceleration.
• E. Partition the ' SENSOR_READINGS table by 'TIMESTAMP (e.g., daily partitions). Enable search optimization on the 'LOCATION' column and enable query acceleration.

Answer: C

Explanation:
Clustering by 'LOCATION' and 'TIMESTAMP' will group related data together, allowing Snowflake to quickly identify the relevant data for spatial queries. Enabling search optimization on the 'LOCATION' column allows queries filtering by geographic area to be accelerated. This combination provides the best performance because it addresses both the time-based and spatial aspects of the queries. Partitioning isn't directly supported by Snowflake but Clustering plays the equivalent role, and search optimization on Geography objects is critical. Materialized views can help, but might not be flexible enough for ad-hoc analysis. Automatic Clustering on 'DEVICE_ID won't help with spatial or time-based filtering. Search optimization on temperature and pressure will not help in spatial search.

NEW QUESTION # 116
You are tasked with calculating the daily moving average of sales for each product category in your Snowflake data warehouse using Snowpark Python. You need to handle cases where there might be missing sales data for certain days. You have the following sales data available in a table named with columns: (DATE), (VARCHAR), and 'sales_amount' (NUMBER). Which of the following Snowpark Python code snippets correctly calculates the 7-day moving average of sales amount per product category, filling in missing sales days with 0, and handles potential division by zero?

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

Answer: E

Explanation:
Option C correctly addresses the problem by first generating a date series to account for missing dates. The solution then does cross join to generate the data, followed by the window aggregation and coalesce, which correctly uses the avg window operation filling zero for the null values.

NEW QUESTION # 117
Consider the following Snowflake SQL API call to execute a stored procedure:

• A. Set the 'warehouse' parameter in the SQL API request to ensure the stored procedure uses a specific warehouse size.
• B. Set the parameter to and retrieve the result set directly from the API response.
• C. The stored procedure should handle the error handling for network disruptions and automatically retry.
• D. Use the parameter to specify which external functions are allowed to be called by the procedure.
• E. Include the stored procedure's fully qualified name (database.schema.procedure_name) in the 'statement' parameter.

Answer: A,B,E

Explanation:
A, B, and C are the most relevant.Setting 'wait_for result' allows direct retrieval. Explicitly setting the 'warehouse' parameter in the SQLAPI request ensures the stored procedure uses a specific warehouse size. The fully qualified name is best practice. D is incorrect because external access integrations are specified when creating the stored procedure, not within the SQLAPI call, unless you're altering a procedure that accepts it. E is incorrect, while a procedure can include error handling, it does not impact the actual API call.

NEW QUESTION # 118
You need to implement a data masking solution in Snowflake for a table 'CUSTOMER DATA' containing PII. The requirement is to mask the email address based on the user's role: if the user is in 'ANALYST ROLE , the email address should be partially masked (e.g., 'a @example.com'), otherwise, it should be fully masked (e.g., @ .com'). Which of the following masking policy definitions and subsequent actions will correctly implement this?

• A. Create a masking policy 'email_mask' using a 'CASE' statement that checks 'CURRENT_ROLE()'. If the role is 'ANALYST_ROLE, partially mask using 'LEFT and 'REGEXP REPLACE; otherwise, return original value. Apply this policy to the 'EMAIL' column of 'CUSTOMER DATA'.
• B. Create a masking policy 'email_mask' that always fully masks the email address. Grant the 'UNMASK' privilege on the 'EMAIL' column to the 'ANALYST ROLE
• C. Create two separate masking policies, one for 'ANALYST_ROLE' and one for all other roles. Apply both policies to the 'EMAIL' column of 'CUSTOMER DATA'. Grant the 'APPLY MASKING POLICY privilege on the 'CUSTOMER DATA' table to the 'ANALYST_ROLE.
• D. Create a masking policy 'email_mask' using 'REGEXP_REPLACE to replace the first part of the email with asterisks if the current role is not 'ANALYST_ROLE' , otherwise use 'LEFT and ' REGEXP_REPLACE to mask only part of the username. Apply this policy to the 'EMAIL ' column of 'CUSTOMER DATA'.
• E. Create a masking policy 'email_mask' using a 'CASE' statement that checks 'CURRENT_ROLE()'. If the role is 'ANALYST_ROLE, partially mask using 'LEFT and 'REGEXP REPLACE; otherwise, fully mask using 'REGEXP REPLACE. Apply this policy to the 'EMAIL' column of 'CUSTOMER DATA'.

Answer: E

Explanation:
Option C uses a single masking policy with a 'CASE statement to dynamically apply different masking logic based on the user's role. This is the most efficient and maintainable approach. Option A is conceptually correct but lacks the explicit use of 'CASE' which is preferred for role-based logic within masking policies. Option B is less efficient. Option D uses UNMASK which defeats masking. Option E does not mask for non-analyst role.

NEW QUESTION # 119
You have a table named 'EVENT LOGS with columns including 'EVENT ID, 'EVENT TIMESTAMP', 'USER ID, 'EVENT_TYPE, and 'EVENT DATA (which stores JSON data). Users frequently query the table filtering by specific key-value pairs within the 'EVENT DATA column. Which of the following approaches will BEST improve query performance when filtering on values inside the JSON column, considering the use of search optimization?

• A. Increase the warehouse size.
• B. Create a materialized view that extracts the key-value pairs from the ' EVENT_DATX column and enable search optimization on the materialized view's columns.
• C. Enable search optimization directly on the 'EVENT DATA' column.
• D. Convert the ' EVENT_DATX column to a VARCHAR column and enable search optimization on it.
• E. Extract the frequently queried key-value pairs from the 'EVENT_DATR JSON into separate virtual columns and enable search optimization on these virtual columns.

Answer: E

Explanation:
Extracting the frequently queried key-value pairs into separate virtual columns and then enabling search optimization on these columns is the most effective approach (Option B). Snowflake's search optimization works best on columns with well-defined data types. Direct search optimization on a JSON column (Option A) is not directly supported and will not provide the desired performance benefits. Materialized views (Option C) are an option, but virtual columns are generally more lightweight for this scenario. Converting to VARCHAR (Option D) is not the correct approach for JSON data and would prevent proper JSON parsing and filtering. Increasing the warehouse size (Option E) might improve overall performance but doesn't specifically address the JSON filtering bottleneck.

NEW QUESTION # 120
You're designing a data pipeline in Snowflake that utilizes an external function to perform sentiment analysis on customer reviews using a third-party NLP service. This service charges per request. You need to minimize costs while ensuring timely processing of the reviews.
Which of the following strategies would be most effective in optimizing the cost and performance of your external function?

• A. Bypass the external function completely and rely solely on Snowflake's built-in NLP functions for sentiment analysis.
• B. Implement rate limiting and error handling in the external service (e.g., AWS Lambda or Azure Function) to gracefully handle API usage limits and prevent excessive charges due to errors.
• C. Pre-process the customer reviews in Snowflake to filter out irrelevant reviews (e.g., very short reviews or reviews with stop words) before sending them to the external function.
• D. Set 'MAX BATCH_ROWS' to a very high value (e.g., 10000) to maximize the number of rows processed per API call, even if it increases latency for individual reviews.
• E. Implement a caching mechanism (e.g., using a Snowflake table or an external cache) to store the sentiment analysis results for frequently occurring reviews or similar text patterns, avoiding redundant API calls.

Answer: B,C,E

Explanation:
The correct answers are B, C, and D. Option B avoids redundant API calls by caching results. Option C handles API rate limits to prevent excessive charges. Option D reduces the number of API calls by pre-filtering irrelevant reviews. Option A will increase latency and is not a cost optimization. Option E is not always feasible if Snowflake's built-in functions are insufficient.

NEW QUESTION # 121
......

Real Snowflake DEA-C02 Exam Dumps with Correct 354 Questions and Answers: https://www.testkingpdf.com/DEA-C02-testking-pdf-torrent.html

Snowflake DEA-C02 Certification Real 2026 Mock Exam: https://drive.google.com/open?id=13DYVco9QokqVedgEK83qZfsahcrgoJzi