Valid DEA-C02 Exam Dumps Ensure you a HIGH SCORE (2025) [Q192-Q206]

QUESTION 192
You are tasked with optimizing a Snowpipe Streaming pipeline that ingests data from Kafka into a Snowflake table named ‘ORDERS’ You notice that while the Kafka topic has high throughput, the data ingestion into Snowflake is lagging. The pipe definition is as follows: “sql CREATE OR REPLACE PIPE ORDERS_PIPEAS COPY INTO ORDERS FROM @KAFKA STAGE FILE_FORMAT = (TYPE = JSON); Which of the following actions, taken individually, would be MOST effective in improving the ingestion rate, assuming sufficient compute resources are available in your Snowflake virtual warehouse?

Increase the size of the virtual warehouse associated with the Snowflake account.

Implement batching within the Kafka producer to send larger messages.

Tune the parameter in the file format definition to a smaller value.

Enable auto-ingest for the Snowpipe.

Increase the number of Kafka partitions and ensure Snowflake has enough compute to consume them in parallel using Snowpipe Streaming.

QUESTION 193
You have implemented a row access policy on a ‘products’ table to restrict access based on the user’s group. The policy uses a mapping table ‘user_groups’ to determine which products a user is allowed to see. After implementing the policy, users are reporting significant performance degradation when querying the ‘products’ table. What are the MOST likely causes of this performance issue, and what steps can you take to mitigate them? Select all that apply.

The ‘user_groups’ table is not properly indexed, causing slow lookups during policy evaluation. Create an index on the ‘username’ and ‘group’ columns of the ‘user_groups’ table.

The row access policy is overly complex and contains computationally expensive functions. Simplify the policy logic and avoid using UDFs or complex subqueries within the policy definition.

The row access policy is interfering with Snowflake’s data pruning capabilities. Ensure that the policy expression can be evaluated efficiently by Snowflake’s query optimizer by using the ‘USING’ clause of the ROW ACCESS POLICY.

The users do not have sufficient privileges to access the ‘user_groups’ table. Grant the necessary SELECT privileges to the users on the ‘user_groupS table.

The row access policy is causing full table scans on the ‘products’ table. Review the query patterns and consider adding clustering keys to the ‘products’ table to improve data access patterns.

QUESTION 194
Consider the following Snowflake UDTF definition written in Python:

Which of the following statements are TRUE regarding the deployment and usage of this UDTF?

The UDTF will automatically be available in all schemas across all databases in the Snowflake account.

The library needs to be explicitly installed and configured within the UDTF’s environment using a Snowpark session.

The UDTF needs to be registered using ‘session.udtf.register’ or ‘create or replace function’ with the ‘imports’ clause referencing the Python file, and the handler’ specifying the function name.

The UDTF can be called directly in SQL using ‘SELECT FROM TABLE(process_json(VARlANT COLUMN));’ without any prior registration.

The return type of the generator ‘yield’ must strictly adhere to the declared output schema , or errors will occur during execution.

QUESTION 195
You’re building a data product on the Snowflake Marketplace that includes a view that aggregates data from a table containing Personally Identifiable Information (PII). You need to ensure that consumers of your data product CANNOT directly access the underlying PII data but can only see the aggregated results from the view. What is the MOST secure and recommended approach to achieve this?

Grant the ‘SELECT privilege directly on the underlying PII table to the share used for the Marketplace listing, along with the ‘SELECT privilege on ‘sensitive data view’.

Grant the ‘SELECT privilege only on the to the share used for the Marketplace listing. Do not grant any privileges on the underlying PII table.

Create a stored procedure that returns the aggregated data, and grant EXECUTE privilege on the stored procedure to the share. The stored procedure SELECTs from the PII table.

Grant USAGE privilege on the database containing the PII table and to the share.

Grant ‘READ privilege on the internal stage containing the data files backing the PII table.

QUESTION 196
You have a ‘SALES table and a ‘PRODUCTS table. The ‘SALES table contains daily sales transactions, including ‘SALE DATE , ‘PRODUCT ID’, and ‘QUANTITY. The ‘PRODUCTS table contains ‘PRODUCT and ‘CATEGORY. You need to create a materialized view to track the total quantity sold per category daily, optimized for fast query performance. You anticipate frequent updates to the ‘SALES table but infrequent changes to the ‘PRODUCTS table. Which of the following strategies would provide the MOST efficient materialized view implementation, considering both data freshness and query performance?

Create a standard materialized view that joins ‘SALES’ and ‘PRODUCTS’ , grouping by ‘SALE DATE and ‘CATEGORY without any specific clustering key.

Create a standard materialized view that joins ‘SALES’ and ‘PRODUCTS’ , grouping by ‘SALE_DATE and ‘CATEGORY, and defining a clustering key on ‘SALE DATE.

Create a standard materialized view that joins ‘SALES’ and ‘PRODUCTS’ , grouping by ‘SALE_DATE and ‘CATEGORY, and defining a clustering key on ‘ CATEGORY.

Create a standard materialized view that joins ‘SALES’ and ‘PRODUCTS’ , grouping by ‘SALE_DATE and ‘CATEGORY, and defining a clustering key on ‘SALE DATE’ and ‘CATEGORY.

Create two materialized views: one for daily sales by product and another joining the first with ‘PRODUCTS’ to aggregate by category. Cluster the first view by ‘SALE DATE’ and the second by ‘CATEGORY’.

QUESTION 197
Your team is developing a set of complex analytical queries in Snowflake that involve multiple joins, window functions, and aggregations on a large table called ‘TRANSACTIONS. These queries are used to generate daily reports. The query execution times are unacceptably high, and you need to optimize them using caching techniques. You have identified that the intermediate results of certain subqueries are repeatedly used across different reports, but they are not explicitly cached. Given the following options, which combination of strategies would MOST effectively utilize Snowflake’s caching capabilities to optimize these analytical queries and improve report generation time?

Use temporary tables to store the intermediate results of the subqueries. These tables will be automatically cached by Snowflake and can be reused by subsequent queries within the same session.

Create common table expressions (CTEs) for the subqueries and reference them in the main query. CTEs will force Snowflake to cache the results of the subqueries, improving performance.

Utilize the “RESULT_SCAN’ function in conjunction with the query ID of the initial subquery execution to explicitly cache and reuse the results in subsequent queries. This approach requires careful management of query IDs.

Create materialized views that pre-compute the intermediate results of the subqueries. This will allow Snowflake to automatically refresh the materialized views when the underlying data changes and serve the results directly from the cache.

Consider using ‘CACHE RESULT for particularly expensive subqueries or views. This is a hint to snowflake to prioritize caching the result set for future calls.

QUESTION 198
Which of the following statements are true regarding using Dynamic Data Masking and Column-Level Security in Snowflake? (Select all that apply)

Dynamic Data Masking is applied at query runtime, while Column-Level Security through views or roles is applied when the object is created.

Dynamic Data Masking can be used to apply different masking rules based on the user’s role, IP address, or other contextual factors.

Column-Level Security via views provides more fine-grained control over data access compared to Dynamic Data Masking.

Dynamic Data Masking policies can reference external tables directly without requiring special grants.

Using both Dynamic Data Masking and Column-Level Security (e.g. views) on the same column is redundant and will result in an error.

QUESTION 199
A data engineer is tasked with migrating data from a large on-premise Hadoop cluster to Snowflake using Spark. The Hadoop cluster contains nested JSON dat a. To optimize performance and minimize data transformation in Spark, what is the most efficient approach to read the JSON data into a Spark DataFrame and write it directly to a Snowflake table?

Read the JSON data as text files, then use Spark to parse and flatten the JSON structure before writing to Snowflake using the Snowflake JDBC connector.

Use the Snowflake Spark connector with the ‘inferSchema’ option set to ‘true’ when reading the JSON data. This allows Spark to automatically infer the schema and write directly to Snowflake.

Define a schema manually in Spark, then read the JSON data into a Spark DataFrame. Use the Snowflake Spark connector to write the data to Snowflake, specifying the schema explicitly.

Use the ‘STORAGE_INTEGRATION’ feature in Snowflake to directly access the JSON files in Hadoop (via an external stage) and load the data without using Spark at all.

Read the JSON data as strings and utilize Snowflake’s ‘PARSE JSON’ function within a Spark SQL query to transform and load the data into a variant column in Snowflake.

QUESTION 200
You have configured a Kafka Connector to load JSON data into a Snowflake table named ‘ORDERS. The JSON data contains nested structures. However, Snowflake is only receiving the top- level fields, and the nested fields are being ignored. Which configuration option within the Kafka Connector needs to be adjusted to correctly flatten and load the nested JSON data into Snowflake?

Enable the ‘snowflake.ingest.stage’ property and set it to a Snowflake internal stage.

Set the ‘value.converter.schemas.enable’ property to ‘true’.

Use the ‘transforms’ configuration with the ‘org.apache.kafka.connect.transforms.ExtractField$Value’ transformation to extract specific fields.

Configure the ‘snowflake.data.field.name’ property to specify the column in the Snowflake table where the entire JSON should be loaded as a VARIANT.

Apply the ‘org.apache.kafka.connect.transforms.Flatten’ transformation to the ‘transforms’ configuration.

QUESTION 201
Consider the following scenario: You are ingesting JSON data from an external stage into Snowflake. The JSON data contains an array of objects, where each object represents a product with attributes like ‘product id’, ‘name’, and ‘price’. However, sometimes the ‘price’ field is missing entirely from some product objects. You want to load this data into a Snowflake table with columns ‘product_id’, ‘name’, and ‘price’ (defined as NUMBER). How can you handle the missing ‘price’ field gracefully during the COPY INTO operation, ensuring that missing prices are represented as NULL in the Snowflake table without causing errors?

Define the ‘price’ column in the Snowflake table as NUMBER and use the DEFAULT NULL clause. The COPY INTO statement will automatically insert NULL values for missing fields.

Define the ‘price’ column in the Snowflake table as NUMBER and use a transformation within the COPY INTO statement to handle missing prices: ‘TRANSFORMATION = (price =

Define the ‘price’ column in the Snowflake table as VARCHAR. During data loading use the NULLIFEMPTY function within the COPY INTO statement: ‘TRANSFORMATION=

Define the ‘price’ column in the Snowflake table as VARIANT. After the data is loaded, create a view that extracts the price using the ‘GET’ function and converts it to NUMBER using ‘TO NUMBER. Handle NULL values in the view using ‘price’)), NULL, TO ‘price’)))’.

Pre-process the JSON data before loading into Snowflake, adding a ‘price’: null field to any product object missing the price.

QUESTION 202
You are tasked with loading a large dataset (50TB) of JSON files into Snowflake. The JSON files are complex, deeply nested, and irregularly structured. You want to maximize loading performance while minimizing storage costs and ensuring data integrity. You have a dedicated Snowflake virtual warehouse (X-Large).
Which combination of approaches would be MOST effective?

Use Snowpipe with auto-ingest, create a single VARIANT column in your target table, and rely solely on Snowflake’s automatic schema detection.

Pre-process the JSON data using a Python script with Pandas to flatten the structure and convert it into a relational format like CSV. Then, load the CSV files using the COPY INTO command with gzip compression.

Use Snowpipe with auto-ingest, create a raw VARIANT column alongside projected relational columns for frequently accessed fields, and use search optimization on those projected columns.

Load the JSON data using the COPY INTO command with no pre-processing. Create a VIEW on top of the raw VARIANT column to flatten the data for querying.

Load the JSON data using the COPY INTO command with gzip compression. Create a raw VARIANT column alongside projected relational columns for frequently accessed fields, and use materialized views to improve query performance.

QUESTION 203
You have a Snowflake table named ‘ORDERS clustered on ‘ORDER DATE. After a significant data load, you want to evaluate the effectiveness of the clustering. Which of the following SQL queries, using Snowflake system functions, will provide insights into the clustering depth and overlap of micro-partitions in the ‘ORDERS’ table, specifically helping you identify whether re-clustering is necessary? Assume that the table

QUESTION 204
You are designing a data sharing solution for a multi-tenant application where each tenant’s data must be isolated. You have a ‘sales’ table with a ‘tenant_id’ column. You need to implement row-level security to ensure that each tenant can only access their own data when querying the shared table. Which of the following approaches, considering performance and security, is the MOST suitable for implementing this row-level filtering in Snowflake?

Create a separate VIEW for each tenant, filtering by ‘tenant_id’. Grant each tenant access only to their respective view.

Implement a user-defined function (UDF) that checks the current user’s tenant ID and returns a boolean value indicating whether the row should be visible. Use this UDF in a WHERE clause in every query.

Implement a row access policy on the ‘sales’ table that filters data based on the ‘tenant_id’ column and the current role or user context.

Use Snowflake’s data masking policies to mask all data for tenants other than the one currently querying the table.

Create a scheduled task that duplicates the sales table into a new table for each tenant, filtering by the tenant_id.

QUESTION 205
You are using Snowpark to perform a complex join operation between two large tables: ‘ORDERS (1 OOGB) and ‘CUSTOMER (50GB). The join is performed on ‘ORDERS.CUSTOMER ID = CUSTOMER.ID. The query is running slower than expected. You have already confirmed that the warehouse size is adequate. Which of the following strategies, applied in combination , would most likely improve the join performance within a Snowpark context?

Ensure both tables are clustered on the join keys CORDERS.CUSTOMER_ID’ and ‘CUSTOMER.ID’).

Use Snowpark’s ‘hint function to force a broadcast join, assuming the ‘CUSTOMER table can fit into memory on the worker nodes.

Increase the ‘AUTO RESIZE’ setting on the warehouse to automatically scale up the warehouse size when the load increases.

Analyze the query profile in Snowflake’s web UI to identify the specific bottleneck (e.g., excessive data spilling, high CPU utilization) and address it directly.

Use ‘session.add_import to add external JAR dependencies. This would enable use of external libraries and improve performance.

QUESTION 206
You have a Snowflake table named ‘CUSTOMER DATA that contains sensitive Personally Identifiable Information (PII). You need to grant a data analyst access to a subset of the data while masking specific columns containing PII. Which of the following Snowflake features, when used in combination, provides the MOST secure and efficient solution?

Create a view on ‘CUSTOMER_DATA’ with column masking policies and grant SELECT privileges on the view.

Create a new table containing only the non-sensitive columns and grant SELECT privileges on this table.

Grant the analyst the ‘SELECT privilege on the ‘CUSTOMER_DATA’ table and implement row-level security based on the analyst’s role.

Create a secure view on ‘CUSTOMER DATA’ with column masking policies and grant SELECT privileges on the secure view.

Create a stored procedure that retrieves the data with masking applied and grant the analyst EXECUTE privilege on the stored procedure.