Use DEA-C02 Exam Dumps (2025 PDF Dumps) To Have Reliable DEA-C02 Test Engine [Q49-Q67]

5/5 - (1 vote)

Use DEA-C02 Exam Dumps (2025 PDF Dumps) To Have Reliable DEA-C02 Test Engine

DEA-C02 PDF Recently Updated Questions Dumps to Improve Exam Score

NEW QUESTION 49
You have a large dataset stored in AWS S3 in Parquet format. The data is constantly updated by an external process, but you need to run read-only analytical queries against the most current data in Snowflake without ingesting it. Which approach is the MOST efficient and cost-effective way to achieve this, considering minimal latency for query results?

Create a Snowflake internal stage, load the Parquet files into the stage periodically, and create a table from the staged files.

Create an external table in Snowflake pointing to the S3 location. Configure automatic data refresh using the ‘AUTO REFRESH’ parameter and a notification integration.

Create a Snowflake external table pointing to the S3 location. Manually refresh the external table metadata periodically using the ‘ALTER EXTERNAL TABLE REFRESH’ command.

Use Snowpipe to continuously ingest the Parquet files from S3 into a Snowflake table.

Create a view on top of the S3 files using a custom Java UDF to read Parquet data directly. Then, query the view.

NEW QUESTION 50
Which of the following statements are true regarding data masking policies in Snowflake? (Select all that apply)

Data masking policies can be applied to both tables and views.

Once a masking policy is applied to a column, the original data is permanently altered.

Different masking policies cannot be applied to different columns within the same table.

The ‘CURRENT_ROLE()’ function can be used within a masking policy to implement role-based data masking.

Data masking policies are supported on external tables.

NEW QUESTION 51
You need to unload data from a Snowflake table named ‘CUSTOMER DATA to an AWS S3 bucket The data should be unloaded in Parquet format, partitioned by the ‘CUSTOMER REGION’ column, and automatically compressed with GZIP. Furthermore, you only want to unload customers whose ‘REGISTRATION DATE is after ‘2023-01-01’. Which of the following ‘COPY INTO’ statement correctly achieves this?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 52
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?

Run ‘OPTIMIZE TABLE ORDERS’ to recluster the table.

Drop and recreate the table with a different clustering key.

Change the ORDER DATE column to VARCHAR and then cluster.

Increase the virtual warehouse size used for loading data into the “ORDERS’ table.

Change the clustering key to a composite key including ORDER DATE and another relevant column.

NEW QUESTION 53
You’re using Snowpark Python to transform data in a Snowflake table called ’employee_data’ which includes columns , ‘department, ‘salary’ , and ‘performance_rating’. You need to identify the top 3 highest-paid employees within each department based on their salary, but only for departments where the average performance rating is above 4.0. Which of the following approaches using Snowpark efficiently combines window functions, filtering, and aggregations to achieve this?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 54
You are tasked with implementing a data governance strategy in Snowflake for a large data warehouse. Your objective is to classify sensitive data columns, such as customer phone numbers and email addresses, using tags. You want to define a flexible tagging system that allows different levels of sensitivity (e.g., ‘Confidential’, ‘Restricted’) to be applied to various columns. Furthermore, you need to ensure that any data replicated to different regions maintains these classifications. Which of the following statements accurately describe best practices for implementing and maintaining data classification using tags in Snowflake, especially in a multi-region setup? Choose TWO.

Define tag schemas at the account level and replicate them to all regions. This ensures consistency of tag definitions across the entire organization.

Create a scheduled task that automatically identifies sensitive data based on regular expressions and applies the appropriate tags. This automates the classification process.

When replicating data between regions, the tags are automatically replicated along with the data, provided that replication is configured using database replication or failover groups including the tagging schema.

Always grant the ACCOUNTADMIN role to users who need to apply tags. This simplifies the process and ensures they have all necessary privileges.

Tags and tag values must be uniquely defined across all schemas to avoid conflicts and ensure accurate data classification; Snowflake enforces uniqueness implicitly.

NEW QUESTION 55
You have created a Snowflake Iceberg table that points to data in an AWS S3 bucket. After some initial data ingestion, you realize that the schema in the Iceberg table does not perfectly match the schema of the underlying Parquet files in S3. Specifically, one of the columns in the Iceberg table is defined as ‘VARCHAR , while the corresponding column in the Parquet files is stored as ‘INT. What will be the most likely behavior when you query this Iceberg table in Snowflake?

Snowflake will automatically cast the SINT’ data in the Parquet files to ‘VARCHAR during query execution, and the query will succeed without any errors or warnings.

The query will fail with an error indicating a data type mismatch between the Iceberg table schema and the underlying Parquet file schema.

The query will succeed, but the ‘VARCHAR column will contain ‘NULL’ values for all rows where the underlying Parquet files contain ‘INT’ values.

The query will succeed, but the result will be unpredictable and may vary depending on the specific data values in the Parquet files.

Snowflake will attempt to cast the data, and if a cast fails (e.g., ‘INT’ value is too large to fit in ‘VARCHAR), the query will return an error only for those specific rows. Other rows will be processed correctly.

NEW QUESTION 56
Which of the following statements are accurate regarding the differences between SQL UDFs and Java UDFs in Snowflake? (Select two)

SQL UDFs can only be used for simple transformations and cannot execute external calls, while Java UDFs can perform complex logic and interact with external services via libraries.

Java UDFs always execute faster than SQL UDFs due to JVM optimizations.

SQL UDFs are defined using SQL code within Snowflake, whereas Java UDFs require uploading a JAR file containing the compiled Java code.

SQL UDFs and Java UDFs are interchangeable, and there is no performance difference between them.

Java UDFs are deprecated and should not be used; instead, SQL UDFs are recommended for all scenarios.

NEW QUESTION 57
A data engineering team is building a real-time fraud detection system. They have a large ‘TRANSACTIONS table that grows rapidly. They need to calculate the average transaction amount per merchant daily. The following query is used:

This query is run every hour and is performance-critical. Which of the following materialized view definitions would provide the BEST performance improvement, considering the need for near real-time data and minimal latency?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 58
You are tasked with building a data pipeline that ingests JSON data from a series of publically accessible URLs. These URLs are provided as a list within a Snowflake table ‘metadata_table’, containing columns ‘file_name’ and ‘file url’. Each JSON file contains information about products. You need to create a view that extracts product name, price, and a flag indicating whether the product description contains the word ‘discount’. Which of the following approaches correctly implements this, optimizing for both performance and minimal code duplication, using external functions for text processing?

Create a stored procedure that iterates through ‘metadata_table’, downloads each JSON file using ‘SYSTEM$URL GET, parses the JSON, extracts the required fields, and inserts the data into a target table. Then, create a view on top of the target table. Use ‘LIKE ‘%discount%’ to identify if a product description contains the word ‘discount’.

Create an external function that takes a URL as input and returns a JSON variant containing the extracted product name, price, and discount flag (using ‘LIKE Then, create a view that selects from calls the external function with ‘SYSTEM$URL as input, and extracts the desired attributes from the returned JSON variant. A stage must also be created to host external function code.

Create a pipe using ‘COPY INTO’ statement with ‘FILE FORMAT = (TYPE = JSON)’ and ‘ON_ERROR = CONTINUE that loads the JSON files directly into a staging table. Create a view on top of the staging table to extract the required fields. The must have = TRUE’ configured if JSON files are nested array. Use ‘ ILIKE in your view for the discount flag.

Create an external function that takes a URL as input and returns a BOOLEAN indicating if any error occured while processing the URL and the data. Create a stored procedure that iterates through ‘metadata_table’ , calls external function for each URL, reports error and then processes the data. A stage must also be created to host external function code.

Create an external function that takes a string as input and returns a BOOLEAN whether that string contains ‘discount. Create a view on top of metadata_table’, and using ‘SYSTEM$URL_GET’ fetch the content from ‘file_url’. The JSON can then be parsed and the fields like price, name and description can be fetched. Use within the view to flag the presence of discount.

NEW QUESTION 59
You are tasked with processing streaming data in Snowflake using Snowpark Python. The raw data arrives in a DataFrame raw events’ with the following schema: ‘event id: string’, ‘event_time: timestamp’, ‘user id: string’, and ‘event data: string’. You need to perform the following data transformations: 1 . Extract a specific value from the JSON ‘event_data’ using the ‘get’ function to find the ‘product_id’ and create a new column named ‘product id’ of type STRING. 2. Filter the DataFrame to include only events where the is NOT NULL and the is within the last hour. 3. Aggregate the filtered data to count the number of events per ‘product id’. Which of the following code snippets correctly performs these transformations in an efficient and performant manner?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 60
You are implementing a data pipeline in Snowpark that reads data from an external stage (e.g., AWS S3) and performs complex transformations, including joins with large Snowflake tables. You notice that the pipeline’s performance is significantly slower than expected, despite having sufficient warehouse resources. Which of the following actions would MOST likely improve the performance of the Snowpark data pipeline?

Increase the warehouse size to the largest available option (e.g., X-Large or larger).

Optimize the SQL joins within the Snowpark DataFrame operations by using broadcast joins when appropriate and ensuring correct join key data types.

Persist the DataFrame representing the data from the external stage using ‘df.cache()’ before performing the joins.

Reduce the number of partitions in the DataFrame representing the data from the external stage using ‘df.repartition(l )’.

Ensure that the external stage is properly configured with appropriate data formats (e.g., Parquet) and partitioning schemes that align with the join keys.

NEW QUESTION 61
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.

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.

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.

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.

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.

Leverage Snowflake’s differential privacy features to add noise to the patient ID data, share the modified dataset and perform a JOIN.

NEW QUESTION 62
You are developing a Snowpark Python application that reads data from a large Snowflake table, performs several transformations, and then writes the results back to a new table. You notice that the write operation is taking significantly longer than the read and transformation steps. The target table is not clustered. Which of the following actions, either individually or in combination, would likely improve the write performance most significantly ?

Increase the size of the Snowflake warehouse used for the Snowpark session.

Use the FILE SIZE’, value)’ method to reduce the size of the output files, potentially leading to more parallelism during the write operation.

Use the ‘DataFrame.repartition(numPartitions)’ method before writing to the table. Choose a ‘numPartitionS value that is significantly higher than the number of virtual warehouses in your warehouse size.

Cluster the target table on the primary key before writing to it. Then, ensure the data being written is pre-sorted according to the clustering key.

Disable auto-tuning for the warehouse to ensure consistent performance

NEW QUESTION 63
A data engineer observes that a Snowflake query, used for generating a daily sales report, consistently runs slower each day, despite the dataset size remaining relatively stable. The query joins a large sales table (SALES) with a smaller product dimension table (PRODUCT) on PRODUCT ID. You’ve already confirmed that virtual warehouse sizing is adequate and data clustering is enabled on SALES(SALE DATE). Analyze the following scenarios and identify the MOST likely cause of the performance degradation and potential solution:

The statistics on the PRODUCT table are outdated. Run ‘ANALYZE TABLE PRODUCT’ to refresh them.

The join between SALES and PRODUCT is causing excessive data spill to local storage due to an inefficient join order. Consider using a JOIN hint, specifically a BROADCAST hint on the PRODUCT table (small table).

The data clustering on SALES(SALE DATE) is ineffective as the query also filters on PRODUCT CATEGORY, which isn’t clustered. Re-cluster SALES by both SALE DATE and PRODUCT CATEGORY.

The Snowflake query optimizer is selecting a suboptimal execution plan because the query is complex. Rewrite the query using temporary tables to break down the logic into smaller steps.

The virtual warehouse is being overwhelmed by concurrent queries. Implement workload management rules to prioritize the sales report query.

NEW QUESTION 64
A data engineering team has implemented a continuous data pipeline that loads data into a Snowflake table named ‘SALES DATA’ They notice that the pipeline intermittently experiences performance degradation, particularly during peak business hours. The team wants to implement alerts to proactively identify and address these performance issues. Which of the following approaches would be MOST effective for monitoring the pipeline and triggering alerts based on specific performance metrics related to data loading?

Create a Snowflake Task that periodically queries the ‘QUERY_HISTORY view, calculates the average load duration for ‘SALES_DATX, and triggers an alert if the duration exceeds a predefined threshold. Use a Stored Procedure to handle the alert logic and send notifications.

Implement a data streaming service that monitors the ‘SALES_DATX table in real-time. The streaming service should track the number of rows inserted per minute and trigger an alert if the insertion rate drops below a predefined threshold. No Snowflake object or Alert required.

Enable Snowflake’s query acceleration service. This service automatically analyzes query performance and identifies opportunities for optimization, removing the need for manual monitoring and alerting. Use Snowflake’s resource monitors to track credit usage.

create a custom Snowflake Alert that triggers when the function for the ‘SALES_DATA’ table indicates significant delay in data loading. Use a Snowflake Notification Integration to send alerts via email or Slack.

Create a Snowflake Alert based on a metric in the Account Usage views (e.g., that identifies when load durations for the data warehouse associated with the data pipeline exceed a specified threshold. Configure a Notification Integration to route alerts to a designated channel.

NEW QUESTION 65
You are designing a Snowflake data pipeline that continuously ingests clickstream dat a. You need to monitor the pipeline for latency and throughput, and trigger notifications if these metrics fall outside acceptable ranges. Which of the following combinations of Snowflake features and techniques would be MOST effective for achieving this goal?

Use Snowflake’s ‘QUERY_HISTORY view to track query execution times and implement a scheduled task that queries this view, calculates latency and throughput, and sends email notifications using Snowflake’s built-in email integration if thresholds are exceeded.

Implement a combination of Snowflake Streams, Tasks, and external functions. Streams capture changes, Tasks process the changes, and external functions send notifications to a monitoring service when latency or throughput issues are detected.

Rely on Snowflake’s default resource monitors to track warehouse usage. If warehouse usage exceeds a certain threshold, assume there are performance issues and send a notification.

Use Snowflake’s Event Tables and Event Notifications to capture events related to data ingestion and processing. Configure alerts based on event patterns that indicate latency or throughput issues.

Create a custom dashboard using a Bl tool that connects to Snowflake via JDBC/ODBC and visualizes data ingestion and processing metrics. Manually monitor the dashboard for anomalies.

NEW QUESTION 66
You are implementing row access policies on a ‘SALES DATA table to restrict access based on the ‘REGION’ column. Different users are allowed to see data only for specific regions. You have a mapping table ‘USER REGION MAP’ with columns ‘USERNAME’ and ‘REGION’. You want to create a row access policy that dynamically filters the ‘SALES DATA’ based on the user and their allowed region. Which of the following options represents a correct approach to create and apply this row access policy?

Option A

Option B

Option C

Option D

Option E

NEW QUESTION 67
You have a large Snowflake table ‘WEB EVENTS that stores website event data’. This table is clustered on the ‘EVENT TIMESTAMP column. You’ve noticed that certain queries filtering on a specific ‘USER ID’ are slow, even though ‘EVENT TIMESTAMP clustering should be helping. You decide to investigate further Which of the following actions would be MOST effective in diagnosing whether the clustering on ‘EVENT TIMESTAMP is actually benefiting these slow queries?

Run ‘ EXPLAIN’ on the slow query and examine the ‘partitionsTotal’ and ‘partitionsScanned’ values. A significant difference indicates effective clustering.

Use the SYSTEM$CLUSTERING_INFORMATIOW function to get the ‘average_overlaps’ for the table and ‘EVENT_TIMESTAMP’ column. A low value indicates good clustering.

Query the ‘QUERY_HISTORY view to see the execution time of the slow query and compare it to the average execution time of similar queries without a ‘USER filter.

Execute ‘SHOW TABLES’ and check the ‘clustering_key’ column to ensure that the table is indeed clustered on ‘EVENT _ TIMESTAMP’.

Run ‘SYSTEM$ESTIMATE QUERY COST to estimate the query cost to see if the clustering is impacting the cost.