Prepare DEA-C02 Question Answers - DEA-C02 Exam Dumps

Real Snowflake DEA-C02 Exam Questions [Updated 2026]

NEW QUESTION # 122
You are using Snowflake Iceberg tables to manage a large dataset stored in AWS S3. Your team needs to perform several operations on this data, including updating existing records, deleting records, and performing time travel queries to analyze data at different points in time. Which of the following statements regarding the capabilities and limitations of Snowflake Iceberg tables are TRUE? (Select all that apply)

• A. Snowflake Iceberg tables do not support transaction isolation levels, so concurrent write operations may lead to data inconsistencies.
• B. Snowflake Iceberg tables support 'UPDATE, ' DELETE, and 'MERGE operations, allowing you to modify existing data directly in the data lake.
• C. Snowflake automatically manages the Iceberg metadata, including snapshots and manifests, eliminating the need for manual metadata management tasks.
• D. Snowflake Iceberg tables support time travel queries using the 'AT(timestamp => ...y syntax, allowing you to query the state of the data at a specific point in time.
• E. Snowflake Iceberg tables support both row-level and column-level security policies, allowing you to control access to sensitive data at a granular level.

Answer: B,C,D

Explanation:
Snowflake Iceberg tables do support 'UPDATE' , 'DELETE' , and 'MERGE operations to modify data directly in the data lake (A). They do support time travel using the 'AT(timestamp => ...y syntax (B). Snowflake does automatically manage the Iceberg metadata (D). Snowflake Iceberg tables provide ACID guarantees and transaction isolation, so concurrent writes are handled safely. Row and column level security can be applied using Snowflake's masking policies and row access policies, but it is not a feature directly built into the Iceberg specification; rather it is a feature of the Snowflake platform. Thus, choice E is incorrect.

NEW QUESTION # 123
You have a table 'CUSTOMERS' with columns 'CUSTOMER ID', 'FIRST NAME', 'LAST NAME, and 'EMAIL'. You need to transform this data into a semi-structured JSON format and store it in a VARIANT column named 'CUSTOMER DATA' in a table called 'CUSTOMER JSON'. The desired JSON structure should include a root element 'customer' containing 'id', 'name', and 'contact' fields. Which of the following SQL statements, used in conjunction with a CREATE TABLE and INSERT INTO statement for CUSTOMER JSON, correctly transforms the data?

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

Answer: A

Explanation:
The correct answer constructs the JSON structure using nested 'OBJECT_CONSTRUCT functions. Option A directly creates a Snowflake VARIANT, which can be inserted into the 'CUSTOMER_DATR column. While many other approaches exist that involve parsing or converting to and from string values, those approaches are unnecessary because OBJECT_CONSTRUCT supports the correct desired behavior directly.

NEW QUESTION # 124
You are tasked with building a data pipeline to process image metadata stored in JSON format from a series of URLs. The JSON structure contains fields such as 'image_url', 'resolution', 'camera_model', and 'location' (latitude and longitude). Your goal is to create a Snowflake table that stores this metadata along with a thumbnail of each image. Given the constraints that you want to avoid downloading and storing the images directly in Snowflake, and that Snowflake's native functions for image processing are limited, which of the following approaches would be most efficient and scalable?

• A. Create a Snowflake stored procedure that iterates through each URL, downloads the JSON metadata using 'SYSTEM$URL_GET, extracts the image URL from the metadata, downloads the image using 'SYSTEM$URL_GET , generates a thumbnail using SQL scalar functions, and stores the metadata and thumbnail in a Snowflake table.
• B. Create a Snowflake view that selects from a table containing the metadata URLs, using 'SYSTEM$URL GET to fetch the metadata. For each image URL found in the metadata, use a JavaScript UDF to generate a thumbnail. Embed the thumbnail into a VARCHAR column as a Base64 encoded string.
• C. Store just the 'image_url' in snowflake. Develop a separate application using any programming language to pre generate the thumbnails and host those at publicly accessible URLs. Within Snowflake, create a view to generate the links for image and thumbnail using 'CONCAT.
• D. Create a Python-based external function that fetches the JSON metadata and image from their respective URLs. The external function uses libraries like PIL (Pillow) to generate a thumbnail of the image and returns the metadata along with the thumbnail's Base64 encoded string within a JSON object.
• E. Create a Snowflake external table that points to an external stage which holds the JSON metadata files. Develop a spark process to fetch image URL, create thumbnails and store as base64 encoded strings in an external stage, create a view using the external table and generated thumbnails data

Answer: C,D

Explanation:
Option C is the most appropriate solution. By using an external function with Python and libraries like PIL, you can efficiently handle image processing tasks that are difficult or impossible to perform natively within Snowflake. The external function encapsulates the image processing logic, keeping the Snowflake SQL code cleaner. Option E is also a valid solution as it leverages external processing. Option A is not performant as it tries to download image in snowflake which is not the best way to process image. Option B is not recommended because using JavaScript UDFs for binary data (images) can be inefficient. External Tables as described in Option D require pre-processing of data and storage to an external stage. Option D doesn't use the 'SYSTEM$URL GET' function that this question is trying to assess.

NEW QUESTION # 125
You are tasked with creating an external function in Snowflake that calls a REST API. The API requires a bearer token for authentication, and the function needs to handle potential network errors and API rate limiting. Which of the following code snippets demonstrates the BEST practices for defining and securing this external function, including error handling?

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

Answer: B

Explanation:
Option A uses SECURITY_INTEGRATION, which is suitable for cloud provider-managed security but doesn't directly handle the API key. Option B uses CREDENTIAL, which is deprecated. Option C and D use AUTH POLICY and SECRET, but C doesn't use SYSTEM$GET_SECRET within a 'USING' clause or CONTEXT_HEADERS. Option D uses the 'USING' clause but does not use 'CONTEXT HEADERS to pass the token correctly. Option E is the BEST approach because it utilizes 'SECURITY INTEGRATION' along with 'CONTEXT_HEADERS' to pass the Bearer token securely retrieved from the Snowflake secret, ensuring proper authentication. Using CONTEXT HEADERS allows setting the authorization header directly. Also, its importand to create the 'SECRET api_secret' for this code to work correctly and this options uses it.

NEW QUESTION # 126
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. Convert the JSON files to Parquet format and recreate the external table to point to the Parquet files.
• E. Create an internal stage, copy all JSON Files, create and load the target table, and drop external table

Answer: C,D

Explanation:
Partitioning the data (B) allows Snowflake to prune unnecessary files during query execution, significantly improving performance. Converting to Parquet (C) provides a columnar storage format that is more efficient for analytical queries compared to JSON, reducing 1/0 and processing time. Increasing warehouse size (A) might help but is not the most effective strategy. Materialized views (D) are not directly applicable to external tables. Copying all files and creating internal tables is not using the external table functionality (E).

NEW QUESTION # 127
A healthcare provider stores patient data in Snowflake, including 'PATIENT ID', 'NAME, 'MEDICAL HISTORY , and 'INSURANCE ID. They need to comply with HIPAA regulations. As a data engineer, you need to ensure that PHI (Protected Health Information) is masked appropriately based on user roles. Which of the following steps are NECESSARY to achieve this using Snowflake's data masking features and RBAC? (Select all that apply)

• A. Enforce multi-factor authentication (MFA) for all users accessing the Snowflake environment to enhance security and prevent unauthorized access to sensitive data.
• B. Create custom roles representing different user groups within the organization (e.g., 'DOCTOR, 'NURSE, 'ADMIN') and grant them the necessary privileges to access the data, including 'SELECT on the tables and views containing patient data.
• C. Apply the created masking policies to the corresponding columns in the patient data tables, ensuring that the masking policies are designed to reveal only the necessary information based on the user's role (e.g., doctors see full medical history, nurses see limited medical history, admins see de-identified data).
• D. Grant the 'OWNERSHIP privilege on the 'PATIENT table to the 'ACCOUNTADMIN' role, ensuring complete control and management of the data by the administrator.
• E. Identify the columns containing PHI and create appropriate masking policies for each column (e.g., masking 'NAME, 'MEDICAL HISTORY, INSURANCE_ID).

Answer: B,C,E

Explanation:
Options A, B, and C are all necessary steps for implementing data masking and RBAC for PHI protection. Identifying PHI and creating masking policies is crucial. Defining roles and granting privileges aligns access with job functions. Applying the masking policies enforces role-based data visibility. Option D is not as important, as another admin role may be more suitable (SECURITYADMIN) than the ACCOUNTADMIN, and option E, MFA does enhance security but is not directly related to Data Masking with RBAC in Snowflake.

NEW QUESTION # 128
You are tasked with building a data pipeline that ingests customer interaction data from multiple microservices using Snowpipe Streaming. Each microservice writes data in JSON format to its own Kafka topic. You need to design an efficient and scalable solution to ingest this data into a single Snowflake table, while ensuring data integrity and minimizing latency. Consider these constraints: 1. High data volume with variable ingestion rates. 2. The need to correlate data from different microservices based on a common 'customer id'. 3. Potential for schema evolution in the microservices. Given these requirements and constraints, which of the following architectural approaches, leveraging Snowpipe Streaming features and Snowflake capabilities, would be the MOST appropriate and robust?

• A. Use a single Snowpipe Streaming client to ingest data from all Kafka topics into a single VARIANT column in the Snowflake table. Then, use Snowflake's external functions to transform and load the data into the final target table based on the 'customer_id'
• B. Develop a Spark Streaming application that reads data from Kafka, transforms it, and then uses the Snowflake Connector for Spark to write the data to Snowflake in micro-batches.
• C. Implement a custom Kafka Connect connector that directly writes data to Snowflake using Snowpipe Streaming. The connector should handle schema evolution and routing based on topic name. Define a clustering key on the Snowflake table on the 'customer id'
• D. Develop a single Snowpipe Streaming client that consumes data from all Kafka topics, using a transformation function to route the data to the correct table based on the topic name. Use Snowflake's clustering key on 'customer _ id' for efficient querying.
• E. Create a separate Snowpipe Streaming client for each Kafka topic, ingesting data into separate staging tables. Then, use a scheduled task to merge the data into the final target table based on 'customer id'.

Answer: C

Explanation:
D is the MOST appropriate solution. A custom Kafka Connect connector provides the most robust and scalable approach. It can handle consuming from multiple topics, manage schema evolution, and use Snowpipe Streaming directly for ingestion. A is less efficient due to multiple Snowpipe clients and scheduled merging. B is difficult to maintain with schema evolution and routing using transformation functions. C is inefficient due to post-ingestion transformation. E, while viable, introduces the complexity and overhead of Spark when Snowpipe Streaming offers a more direct solution.

NEW QUESTION # 129
You are responsible for monitoring data quality in a Snowflake data warehouse. Your team has identified a critical table, 'CUSTOMER DATA, where the 'EMAIL' column is frequently missing or contains invalid entries. You need to implement a solution that automatically detects and flags these anomalies. Which of the following approaches, or combination of approaches, would be MOST effective in proactively monitoring the data quality of the 'EMAIL' column?

• A. Create a Snowflake Task that executes a SQL query to count NULL 'EMAIL' values and invalid 'EMAIL' formats (using regular expressions). The task logs the results to a separate monitoring table and alerts the team if the count exceeds a predefined threshold.
• B. Schedule a daily full refresh of the 'CUSTOMER DATA' table from the source system, overwriting any potentially corrupted data.
• C. Implement a Streamlit application connected to Snowflake that visualizes the percentage of NULL and invalid 'EMAIL' values over time, allowing the team to manually monitor trends.
• D. Utilize an external data quality tool (e.g., Great Expectations, Deequ) to define and run data quality checks on the 'CUSTOMER DATA' table, integrating the results back into Snowflake for reporting and alerting.
• E. Use Snowflake's Data Quality features (if available) to define data quality rules for the 'EMAILS column, specifying acceptable formats and thresholds for missing values. Configure alerts to be triggered when these rules are violated.

Answer: A,D,E

Explanation:
Options A, B, and D are the most effective. Option A provides a programmatic approach within Snowflake. Option B leverages Snowflake's built-in data quality capabilities (if available, check documentation for supported features and editions). Option D integrates with external specialized tools. Option C relies on manual monitoring, which is less proactive. Option E does not address the root cause of data quality issues and could potentially overwrite valid data with erroneous data.

NEW QUESTION # 130
A data engineer is facing performance issues with a complex analytical query in Snowflake. The query joins several large tables and uses multiple window functions. The query profile indicates that a significant amount of time is spent in the 'Remote Spill' stage. This means the data from one of the query stages is spilling to the remote disk. What are the possible root causes for 'Remote Spill' and what steps can be taken to mitigate this issue? Select two options.

• A. The 'Remote Spill' indicates network latency issues between compute nodes. There is nothing the data engineer can do to fix this; it is an infrastructure issue.
• B. The virtual warehouse is not appropriately sized for the volume of data and complexity of the query. Increasing the virtual warehouse size might provide sufficient memory to avoid spilling.
• C. The data being queried is stored in a non-Snowflake database, making it difficult to optimize the join.
• D. The window functions are operating on large partitions of data, exceeding the available memory on the compute nodes. Try to reduce the partition size by pre- aggregating the data or using filtering before applying the window functions.
• E. The query is using a non-optimal join strategy. Review the query profile and consider using join hints to force a different join order or algorithm.

Answer: B,D

Explanation:
Options A and D are the correct root causes and solutions for remote spill. Remote spill indicates that a query stage is exceeding the available memory on the compute node, and the overflow data is written to remote storage (usually S3 for Snowflake). Option A addresses this by increasing the warehouse size. Option D addresses the root cause by suggesting a reduction in the amount of data being processed by the window functions. Option B could help but does not directly address the spill. Option C is unlikely as Snowflake manages the network internally, and while network issues could exist, it's not the first thing to troubleshoot. Option E is unlikely since Snowflake only queries data within Snowflake itself, unless its connecting to external tables.

NEW QUESTION # 131
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?

• A. Create a Snowflake external table pointing to the S3 location. Manually refresh the external table metadata periodically using the 'ALTER EXTERNAL TABLE REFRESH' command.
• B. Create a view on top of the S3 files using a custom Java UDF to read Parquet data directly. Then, query the view.
• C. Create a Snowflake internal stage, load the Parquet files into the stage periodically, and create a table from the staged files.
• D. Create an external table in Snowflake pointing to the S3 location. Configure automatic data refresh using the 'AUTO REFRESH' parameter and a notification integration.
• E. Use Snowpipe to continuously ingest the Parquet files from S3 into a Snowflake table.

Answer: D

Explanation:
Option B is the most efficient. External tables allow querying data directly from S3 without ingestion. 'AUTO_REFRESH' and notification integration ensures the metadata is automatically updated when new files are added to S3, providing near real-time access. Options A and D involve data ingestion, which adds latency and storage costs. Option C requires manual intervention, and Option E using a Java UDF for Parquet reading is inefficient.

NEW QUESTION # 132
You are tasked with implementing data masking on a 'CUSTOMER' table. The requirement is to mask the 'EMAIL' column for all users except those with the 'DATA ADMIN' role. You have the following code snippet. What is wrong with it?

• A. There is no code provided, so there is nothing wrong with it.
• B. The masking policy is applied to the wrong column. It should be applied to the ID column, not the EMAIL column.
• C. The masking policy syntax is incorrect. It should use 'CASE WHEN IS_ROLE_IN_SESSION('DATA_ADMIN') THEN EMAIL ELSE '[email protected]' END'.
• D. The WITH clause is unneccessary.
• E. Without masking poliy code, it's impossible to determine if there is anything wrong.

Answer: E

Explanation:
Without the masking policy code, it's impossible to determine if there are any errors. Option A is wrong without any data, Option B can be correct but we cannot know without code. Option C may be right but we do not know as well. Option D assumes there is no code provided, but we simply can't see it here. The correct answer is E, since we cannot determine the answer without code.

NEW QUESTION # 133
A data provider wants to share a large dataset (several TB) with multiple consumers. The dataset is updated daily. The provider wants to minimize the cost associated with data sharing and ensure that consumers receive consistent data'. Which of the following strategies would be the MOST cost-effective and maintainable?

• A. Create a data share containing external tables pointing to data stored in cloud storage (e.g., AWS S3), updated daily using a pipeline.
• B. Create a data share and grant access to all consumers directly on the base tables.
• C. Create a data share and create a separate database for each consumer, cloning the data into each consumer's database daily.
• D. Create a data share with views that point to the base tables, and clone the base tables daily into a separate 'staging' database before sharing.
• E. Create a data share with views that point to the base tables. Use time travel to allow consumers to query data from a specific point in time before the daily update.

Answer: E

Explanation:
Using time travel allows consumers to query a consistent snapshot of the data before the daily updates without requiring expensive data cloning or maintaining separate databases for each consumer. Cloning data daily (Options B and D) is extremely costly. Sharing the base tables directly (Option A) may not be desirable due to security or management considerations. Using external tables (option E) could introduce latency or require the consumer to manage their own compute resources.

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

Answer: B,C,E

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 # 135
You need to implement both a row access policy and a dynamic data masking policy on the 'EMPLOYEE table in Snowflake. The requirements are as follows: 1. Employees should only be able to see their own record in the 'EMPLOYEE table. 2. The 'SALARY' column should be masked for all employees except those with the 'HR ADMIN' role. Unmasked values are required for compliance reasons, they need to be available for 'HR ADMIN' role. Given the following table structure: CREATE TABLE EMPLOYEE ( EMPLOYEE ID INT, EMPLOYEE NAME STRING, SALARY NUMBER, EMAIL STRING ) ; Which of the following sets of steps correctly implement the row access policy and dynamic data masking policy?

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

Answer: D

Explanation:
Option B implements both policies correctly. The row access policy correctly checks if the 'EMPLOYEE ID matches the 'CURRENT_USER()'. Although the use of is not correct in this situation, it is being used with 'employee_id' so can only see his own record in the 'EMPLOYEE table. The masking policy uses 'CURRENT correctly to check if the role in the session is 'HR_ADMIN'. If it is, the original salary value is returned; otherwise, it masks it to Other masking policy options will return a string representation ("MASKED") or return a hash of the value, which is not a valid 'NUMBER. Option A uses IS ROLE IN SESSION rather than CURRENT_ROLE. 'CURRENT_ROLE only returns the primary role used to initialize the session whereas will return TRUE if the role is the primary role or any of the active secondary roles in the current session.

NEW QUESTION # 136
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?

• A. 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.
• B. 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.
• C. 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.
• D. 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.
• E. 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.

Answer: A,E

Explanation:
Options A and E offer the most effective approaches. Option A leverages Snowflake's Task and Stored Procedure capabilities to monitor query history and trigger alerts based on load duration. Option E utilizes Snowflake Alerts based on Account Usage views to monitor warehouse load history. Option B is not Snowflake native, and doesn't directly leverage snowflake alerting capabilities. Option C, while helpful for overall performance, doesn't directly address the specific alerting requirements of the scenario. Option D's 'SYSTEM$LAST CHANGE COMMIT TIME function may not provide granular enough information for performance monitoring.

NEW QUESTION # 137
You have a large dataset of JSON documents stored in AWS S3, each document representing a customer order. You want to ingest these documents into Snowflake using Snowpipe and transform the nested 'address' field into separate columns in your target table. Considering data volume, complexity, and cost efficiency, which approach is MOST suitable?

• A. Use Snowpipe to ingest the raw JSON data into a VARIANT column, then create a view that flattens the 'address' field.
• B. Pre-process the JSON documents using an external compute service (e.g., AWS Lambda) to flatten the 'address' field before ingesting into Snowflake via Snowpipe.
• C. Use Snowpipe with a user-defined function (UDF) written in Python to parse the JSON and flatten the 'address' field.
• D. Use a COPY INTO statement with a transform clause to flatten the 'address' field during ingestion.
• E. Create an external table on the S3 bucket and then use CREATE TABLE AS SELECT (CTAS) to transform the data.

Answer: A

Explanation:
Using Snowpipe to ingest into a VARIANT column and then creating a view is generally the most cost-effective and flexible approach for handling semi- structured data and performing transformations in Snowflake. CTAS involves full table scans and is less efficient for ongoing ingestion. COPY INTO with transforms has limitations for complex nested structures. Pre-processing with Lambda adds complexity and cost. UDFs can be expensive for large datasets compared to Snowflake's native JSON processing capabilities.

NEW QUESTION # 138
......

DEA-C02 Exam Dumps Pass with Updated 2026: https://passguide.vce4dumps.com/DEA-C02-latest-dumps.html