2026 Correct Practice Tests of DEA-C02 Dumps with Practice Exam
Certification Sample Questions of DEA-C02 Dumps With 100% Exam Passing Guarantee
NEW QUESTION # 78
A data engineer is tasked with creating an external table that points to a directory in AWS S3 containing CSV files. The files have a header row and are comma-delimited. The engineer executes the following DDL statement:
- A. The statement will fail because external tables cannot be created directly from CSV files without defining a stage.
- B. The statement will fail because the FIELD_DELIMITER property should be 'DELIMITER.
- C. The statement will succeed if a file format named 'csv_format' with the specified properties already exists.
- D. The statement will fail because the 'LOCATION' is missing the protocol (e.g., 's3:/P).
- E. The statement will fail because the 'SKIP HEADER property is invalid and the 'skip_header' parameter should be used in the 'CREATE FILE FORMAT statement.
Answer: E
Explanation:
The 'SKIP_HEADER property is deprecated in the 'CREATE EXTERNAL TABLE statement. You must define this property in the file format. A stage is not strictly necessary for creating an external table, the location can be specified directly if the proper access permissions are in place. The 'FIELD_DELIMITER property is valid. Also if a File Format is already created correctly, it does not mean this statement will succeed, the parameters passed on the statement itself are relevant. So statement will fail because the 'SKIP HEADER property is invalid and the skip_header' parameter should be used in the 'CREATE FILE FORMAT statement.
NEW QUESTION # 79
A Snowflake Data Engineer is tasked with identifying all downstream dependencies of a view named 'CUSTOMER SUMMARY. This view is used by multiple dashboards and reports. They want to use SQL to efficiently find all tables and views that directly depend on 'CUSTOMER SUMMARY. Which of the following SQL queries against the ACCOUNT USAGE schema is the MOST efficient and accurate way to achieve this?
- A. Option D
- B. Option E
- C. Option A
- D. Option C
- E. Option B
Answer: E
Explanation:
The view in the ACCOUNT_USAGE schema is specifically designed to track object dependencies. option B directly queries this view using the and to find objects that depend on the 'CUSTOMER_SUMMARY view. Options A and C rely on parsing 'QUERY_TEXT , which is less reliable and can lead to false positives or misses. Option D looks for base object which has the opposite meaning of finding dependancies on a target view. Option E finds the OBJECT_ID for the view and is unnecessary and introduces an extra step.
NEW QUESTION # 80
Consider the following Snowflake UDTF definition written in Python:
Which of the following statements are TRUE regarding the deployment and usage of this UDTF?
- A. The return type of the generator 'yield' must strictly adhere to the declared output schema , or errors will occur during execution.
- B. 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.
- C. The library needs to be explicitly installed and configured within the UDTF's environment using a Snowpark session.
- D. The UDTF will automatically be available in all schemas across all databases in the Snowflake account.
- E. The UDTF can be called directly in SQL using 'SELECT FROM TABLE(process_json(VARlANT COLUMN));' without any prior registration.
Answer: A,B
Explanation:
UDTFs in Snowflake require explicit registration using 'session.udtf.register' or 'create or replace function' command, defining the location of the source code (Python file) and the function to be executed. Also, data types of values which is 'yield'ed in the body of UDTF must strictly adhere with Output schema. Libraries from 'snowflake.snowpark' are usually available and does not needs explicit configuration. UDTFs are schema-bound, not automatically available everywhere. Direct call to UDTF without creation isn't possible.
NEW QUESTION # 81
You are troubleshooting a slow-running query that joins a large fact table 'SALES DATA' (100 billion rows) with a smaller dimension table 'CUSTOMER DIM' (1 million rows) on 'CUSTOMER ID. Initial analysis shows that the query is spending significant time in the join operation. You suspect the issue lies with the join strategy being used by Snowflake. Which of the following actions are MOST likely to improve query performance and optimize the join?
- A. Increase the virtual warehouse size and monitor for spillover to local disk. If spilling occurs, further increase the warehouse size.
- B. Ensure both 'SALES DATA' and 'CUSTOMER DIM' are clustered on 'CUSTOMER ID.
- C. Ensure that the 'CUSTOMER_ID column in both tables has compatible datatypes and that no implicit type conversions are happening during the join. Also check cardinality of 'CUSTOMER_ID in the SALES DATA table.
- D. Convert the query to use a LATERAL FLATTEN function to pre-process the 'CUSTOMER_DIW table before the join.
- E. Analyze the query profile in Snowflake's web UI and identify if a broadcast join is occurring. If so, consider increasing session parameter (within limits) or re-designing the query to avoid the broadcast join.
Answer: A,C,E
Explanation:
Options C, D, and E are the most effective. Increasing the warehouse size (C) provides more resources for the join. Analyzing the query profile for broadcast joins and adjusting 'AUTO BROADCAST JOIN SIZE (D) can prevent inefficient join strategies where the smaller table is unnecessarily broadcast to all nodes. Ensuring data type compatibility (E) prevents performance-impacting implicit conversions. While clustering on the join key (A) can help, it may not be sufficient on its own, especially for very large tables. LATERAL FLATTEN (B) is generally used for semi- structured data and is not relevant to this scenario. Understanding data skewness in 'SALES_DATX is important too. A highly skewed distribution of 'CUSTOMER_ID' could lead to hot spots.
NEW QUESTION # 82
You have implemented a masking policy on the 'EMAIL' column of a 'USERS' table. The policy masks the email address for all users except those with the 'SUPPORT' role. You now need to grant the 'SELECT' privilege on this table to a new role, 'ANALYST. You want to ensure that the masking policy continues to work as expected for the 'ANALYST' role. Which of the following SQL statements should you execute?
- A.
- B.
- C.
- D.
- E.
Answer: E
Explanation:
Masking policies in Snowflake are automatically applied when a user queries a table with a column that has a masking policy set. Granting 'SELECT privilege to the 'ANALYST role does not require any further action related to the masking policy. The masking policy will automatically be applied based on the role of the user executing the query. Options B, C and E involve unnecessary or incorrect steps after granting the select privilege.
NEW QUESTION # 83
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?
- A. The query will fail with an error indicating a data type mismatch between the Iceberg table schema and the underlying Parquet file schema.
- B. The query will succeed, but the 'VARCHAR column will contain 'NULL' values for all rows where the underlying Parquet files contain 'INT' values.
- C. The query will succeed, but the result will be unpredictable and may vary depending on the specific data values in the Parquet files.
- D. 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.
- E. 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.
Answer: A
Explanation:
Snowflake enforces schema validation for Iceberg tables. If the data types in the Iceberg table schema do not match the data types in the underlying Parquet files, the query will fail with an error. This is because Snowflake relies on the Iceberg metadata to understand the data types and structure of the data in the Parquet files. A mismatch indicates a problem with the Iceberg table definition or the underlying data and should be corrected to ensure data integrity. While Snowflake is often flexible with implicit casting, in the context of Iceberg tables and schema enforcement, a type mismatch will lead to a query failure.
NEW QUESTION # 84
A data pipeline ingests clickstream data from various sources into a raw Snowflake table CRAW CLICKS). A transformation job then processes this data and loads it into a more structured 'CLICK EVENTS table, performing filtering, cleaning, and data enrichment. The data engineering team notices significant performance bottlenecks during this transformation process, leading to data freshness issues.
The team wants to optimize this process, considering the following:
- A. Create a materialized view on top of 'RAW CLICKS' that pre-computes the necessary transformations and aggregations, allowing the 'CLICK EVENTS' table to be populated directly from the materialized view.
- B. Replace the transformation job with a series of smaller, more specialized jobs, each running on a separate virtual warehouse optimized for the specific task, and orchestrate these jobs using a data pipeline tool.
- C. Use a larger virtual warehouse for the transformation job and partition the 'RAW CLICKS table on the ingestion timestamp to improve data pruning and reduce the amount of data processed during the transformation.
- D. Implement a change data capture (CDC) mechanism on the source systems to only ingest changed data into 'RAW CLICKS, reducing the overall data volume and the amount of data processed by the transformation job.
- E. Optimize the transformation queries by identifying and rewriting inefficient SQL patterns, ensuring appropriate use of joins, filtering conditions, and data type conversions.
Answer: A,D,E
Explanation:
Implementing CDC reduces the amount of data processed. Materialized views pre-compute results, avoiding repeated transformations. Optimizing SQL queries improves the efficiency of the transformation logic. Using a larger warehouse helps, but doesn't address the fundamental issue of processing unnecessary data. Breaking the transformation job into smaller jobs may add overhead and complexity without a clear performance benefit. Partitioning is a database term and clustering is snowflake, the question relates to snowflake.
NEW QUESTION # 85
You are tasked with ingesting a large volume of CSV files from an external stage into a Snowflake table. Some of these CSV files contain corrupted records with inconsistent delimiters or missing values. You need to ensure that only valid records are loaded into the table, and the corrupted records are captured for further analysis. Which of the following COPY INTO options would BEST address this requirement?
- A. Option B
- B. Option D
- C. Option E
- D. Option A
- E. Option C
Answer: C
Explanation:
Option E is the most comprehensive solution. ERROR = CONTINUE allows the COPY INTO statement to proceed despite errors. 'SKIP HEADER = 1' handles potential header issues in corrupted files. MISMATCH = FALSE' allows for varying column counts. 'VALIDATION _ MODE' and 'RESULT provide a mechanism to capture and analyze the rejected records, satisfying the requirement to analyze corrupted records. Options A, B, C, and D are insufficient for capturing corrupted records for analysis or may lead to data loss.
NEW QUESTION # 86
A data engineering team is building a real-time data pipeline in Snowflake. Data arrives continuously and needs to be processed with minimal latency. The team is using Snowflake Streams and Tasks for incremental data processing. However, they are encountering issues where the tasks are sometimes skipped or delayed, leading to data inconsistencies. Which combination of actions would BEST address these issues and ensure reliable near real-time data processing?
- A. Configure the tasks to run using a serverless compute model (Snowflake-managed compute). Ensure the parameter is set to a higher value and implement error handling within the task using TRY/CATCH blocks.
- B. Disable task scheduling and rely solely on Snowflake's Auto-Resume feature for warehouses. This simplifies the pipeline and reduces the chance of errors.
- C. Monitor the 'TASK HISTORY view regularly to identify skipped or delayed tasks and manually re-run them as needed. This is a reactive approach and does not prevent future occurrences.
- D. Adjust the ' ERROR_INTEGRATION' parameter on the task definition to send notifications when tasks fail. This allows for manual intervention but does not prevent skipping.
- E. Increase the warehouse size to ensure sufficient compute resources. This will prevent tasks from being skipped due to resource contention.
Answer: A
Explanation:
Option C is the best solution. Serverless compute allows Snowflake to automatically manage resources for the tasks, ensuring they are not skipped due to insufficient compute. Setting 'SUSPEND TASK AFTER NUM FAILURES' avoids immediate suspension after a transient failure, and TRY/CATCH allows for robust error handling. Increasing warehouse size (A) may help, but serverless provides better elasticity. B only provides notification. D is incorrect as disabling tasks removes automation. E is a reactive approach.
NEW QUESTION # 87
You're designing a near real-time data pipeline for clickstream data using Snowpipe Streaming. The data volume is extremely high, with bursts exceeding 1 million events per second. Your team reports intermittent ingestion failures and latency spikes. Considering the constraints of Snowpipe Streaming, which of the following strategies would be MOST effective in mitigating these issues, assuming the data format is optimized and network latency is minimal?
- A. Switch from Snowpipe Streaming to Classic Snowpipe, as it is more resilient to high data volumes.
- B. Implement client-side retry logic with exponential backoff and jitter to handle transient errors and avoid overwhelming the service.
- C. Increase the number of Snowflake virtual warehouses to handle the increased load.
- D. Reduce the size of each micro-batch being sent to Snowpipe Streaming to minimize the impact of individual failures.
- E. Implement a message queue (e.g., Kafka) in front of Snowpipe Streaming to buffer incoming events and smooth out the traffic spikes.
Answer: B,E
Explanation:
B and C are correct. Implementing client-side retry logic with exponential backoff (B) prevents overwhelming the service during transient errors. Using a message queue like Kafka (C) buffers the data, smoothing out traffic spikes and providing better resilience. A is less effective as scaling warehouses won't directly address client-side issues like retry logic and buffering. D can help but is not as effective as a buffering mechanism or robust retry strategy. E is incorrect as Snowpipe Streaming is designed for lower latency than classic Snowpipe.
NEW QUESTION # 88
You have configured a Snowpipe to load data from an AWS S3 bucket into a Snowflake table. The data in S3 is updated frequently. You've noticed that despite the Snowpipe being active and the S3 event notifications being configured correctly, some newly added files are not being picked up by the Snowpipe. You run 'SYSTEM$PIPE and see the 'executionstate' is 'RUNNING' but the 'pendingFileCount' remains at O, even after new files are placed in the S3 bucket. Choose all of the reasons that could explain the observations.
- A. The IAM role associated with your Snowflake account does not have sufficient permissions to read from the S3 bucket. Specifically, it lacks the 's3:GetObject' permission.
- B. The file format specified in the Snowpipe definition does not match the actual format of the files being placed in the S3 bucket.
- C. There is an insufficient warehouse size configured for the Snowpipe. Increase the warehouse size for optimal performance.
- D. The S3 event notification configuration is missing the 's3:ObjectCreated: event type, meaning that new file creation events are not being sent to the SQS queue or SNS topic.
- E. The SQS queue or SNS topic associated with the S3 event notifications has a message retention period that is too short. Messages containing event details for new files are being deleted before Snowpipe can process them.
Answer: A,B,D
Explanation:
The problem states that the snowpipe is active, but 'pendingFileCount' remains zero. This means that the events are not making its way to snowpipe. Insufficient permissions (A) on the IAM role will prevent Snowflake from accessing the files. Incorrect event notification settings (B) would stop events from being sent to the queue/topic. Mismatched file format will not generate any events, and will cause files to be skipped. Incorrect message retension period may result in loss of messages before snowpipe could process them, hence can be the root cause. (C) is incorrect as it does not impact picking up of event notifications. Warehouse size mainly affects processing data and not the notification retrieval.
NEW QUESTION # 89
You are tasked with implementing row-level filtering on a 'customers' table in Snowflake. You need to restrict access to customer data based on the user's region. The 'customers' table has a 'region' column, and you have a mapping table 'user regions' that associates users with the regions they are allowed to access. The 'user_regions' table has columns 'username' and 'region'. Which of the following SQL statements correctly creates and applies a row access policy to achieve this, minimizing complexity and maximizing performance? Select all that apply:
- A. Option B
- B. Option D
- C. Option E
- D. Option C
- E. Option A
Answer: C,D
Explanation:
Options C and E are correct. They both create a row access policy that checks if the current role is ACCOUNTADMIN (C) or either ACCOUNTADMIN/SECURITYADMIN (E). If not, it checks if the current user is associated with the region in the 'user_regions' table. The policy is then applied to the 'customers table on the 'region' column. Option A is incorrect because it only checks for 'admin_role' but ACCOUNTADMIN is needed. Option B only provides row access functionality and no additional administrative access. Option D is incorrect because it doesn't have administrative override. The OR REPLACE Clause is used to ensure there are no errors when running this for multiple times or running if exists scenarios.
NEW QUESTION # 90
A financial services company is implementing Snowflake. They have a table 'CUSTOMER DATA' containing sensitive information like 'CREDIT CARD NUMBER, 'SSN', and 'ADDRESS'. They need to ensure that: 1) Analysts can only see the last four digits of the 'CREDIT CARD NUMBER. 2) Data scientists require full access to the 'ADDRESS' but should not see the 'SSN'. 3) A dedicated compliance role should be able to view all data in its original format for auditing purposes. Which of the following is the MOST efficient and secure approach to implement this using Snowflake's data masking and RBAC?
- A. Create masking policies on the 'SSN' , and 'ADDRESS' columns. Use conditional masking expressions based on the CURRENT ROLE() function to determine what data to show to each role (analysts, data scientists, compliance).
- B. Use data encryption for the entire 'CUSTOMER_DATA table and provide decryption keys to specific roles based on their access requirements. Provide the compliance role with the master key.
- C. Replicate the CUSTOMER_DATA table three times, once for each user group (Analysts, Data Scientist and Compliance). Mask sensitive information by altering the data with the respective masking function.
- D. Create dynamic data masking policies on each sensitive column in the 'CUSTOMER_DATA table, associating these policies with specific roles using Snowflake's tag-based masking. Grant roles only the privileges needed to select the columns based on their requirements.
- E. Create separate views for analysts and data scientists, applying masking policies within the views, and grant access to these views based on their respective roles. Additionally, grant the compliance role direct access to the base table.
Answer: A
Explanation:
Conditional masking using the CURRENT ROLE() function within masking policies is the most efficient and secure approach. It allows a single table to be used while dynamically controlling data visibility based on the user's role. Views (Option A) can introduce maintenance overhead. Encryption (Option C) is generally used for data at rest and in transit and is not the correct solution for masking. Tag-based masking can add complexity when direct role-based masking is simpler. Replicating the table (Option E) would consume a huge amount of resource, and increase data duplication issues.
NEW QUESTION # 91
You have an external table named in Snowflake that points to a set of CSV files in an AWS S3 bucket. The CSV files have a header row, and the data is comma-separated. However, some of the files in the S3 bucket are gzipped. You need to define the external table to correctly read both compressed and uncompressed files. Which of the following SQL statements BEST achieves this?
- A. Option D
- B. Option E
- C. Option A
- D. Option C
- E. Option B
Answer: E
Explanation:
The 'COMPRESSION = AUTO' parameter in the file format definition allows Snowflake to automatically detect and decompress gzipped files while also reading uncompressed files. The 'SKIP HEADER = 1 ' parameter ensures that the header row in the CSV files is skipped.
NEW QUESTION # 92
You are tasked with designing a data sharing solution where data from multiple tables residing in different databases within the same Snowflake account needs to be combined into a single view that is then shared with a consumer account. The view must also implement row-level security based on the consumer's role. Which of the following options represent valid approaches for implementing this solution? Select all that apply.
- A. Create a secure view that joins tables from different databases and implement row-level security using a row access policy based on the CURRENT ROLE() function. Masking policy cannot provide role based access control so will not work.
- B. Create a secure view that joins tables from different databases using fully qualified names (e.g., 'DATABASEI .SCHEMAI . TABLET) and implement row-level security using a masking policy based on the CURRENT_ROLE() function.
- C. Create a view for each table and then build a final view using 'UNION ALL' to combine data from all the views and implement row-level security with a role based row access policy. Standard views should not be used in data sharing.
- D. Create a standard view that joins tables from different databases using aliases and implement row-level security using a UDF that checks the consumer's role and filters the data accordingly.
- E. Create a standard view with a stored procedure to handle the joins across databases and use EXECUTE AS OWNER to avoid permission issues. This standard view should be shared.
Answer: A,B
Explanation:
Options A and C are the valid approaches. A secure view is essential for data sharing. Fully qualified names are required to reference objects across databases. Row-level security can be implemented using either a row access policy or a masking policy (with some limitations). Option A incorrectly mentions a masking policy for row level access control. Option C is correct as row access policies are designed for that case. Option D create standard views and sharing standard views is not a good practice. Stored procedures cannot be used in the definition of a view for data sharing, so E is invalid. B is also invalid as standard view.
NEW QUESTION # 93
......
DEA-C02 Sample Practice Exam Questions 2026 Updated Verified: https://www.actual4cert.com/DEA-C02-real-questions.html
Pass Key features of DEA-C02 Course with Updated 354 Questions: https://drive.google.com/open?id=1_2lPcv2u-mIT6G4SW5HSzIK0itQygTlj