Real Snowflake DEA-C02 Exam Dumps with Correct 354 Questions and Answers [Q146-Q160]

QUESTION 146
You have a table ‘ORDERS in your Snowflake database. You are implementing a new data transformation pipeline. Before deploying the pipeline to production, you want to validate the changes in a development environment. You decide to use Time Travel to create a snapshot of the ‘ORDERS’ table before the transformation and compare it with the transformed data’. Which sequence of SQL commands would best facilitate this validation, assuming your development database and schema structure mirrors production?

QUESTION 147
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 148
You are building a data pipeline using Snowflake Tasks to orchestrate a series of transformations. One of the tasks, ‘task _ transform data’, depends on the successful completion of another task, ‘task extract_data’. However, occasionally fails due to transient network issues. You want to implement a retry mechanism for ‘task_extract data’ without impacting the overall pipeline execution time significantly. Which of the following approaches is the most appropriate and efficient way to achieve this within the Snowflake Task framework?

Implement a TRY…CATCH block within the task definition to catch any exceptions. Inside the CATCH block, use SYSTEM$WAIT to pause for a few seconds, then re- execute the core logic of the task. Repeat this process a limited number of times before failing the task permanently.

Modify the task definition to call a stored procedure. The stored procedure implements a loop with a retry counter. Inside the loop, execute the data extraction logic. If an error occurs, catch the exception, wait for a few seconds, and retry the extraction. After a specified number of retries, raise an exception to signal task failure.

Configure the task with an error notification integration that sends alerts upon failure. Manually monitor these alerts and manually resume the task if it fails. Use ‘ALTER TASK task extract data RESUME;’

Use the ‘AFTER keyword in the ‘CREATE TASK’ statement for ‘task_transform_data’ to only execute if succeeds on its first attempt. If fails, the entire pipeline will stop, ensuring data consistency.

Create a new root-level task that checks the status of ‘task_extract_data’. If it failed, the root-level task will execute a copy of the ‘task_extract data’ task. After this, it updates the ‘task_transform_data”s ‘AFTER’ condition to depend on the new task that retries extraction.

QUESTION 149
You are troubleshooting a slowly performing query in Snowflake that aggregates data from a large ORDERS table (10 billion rows) partitioned by ORDER DATE. The query execution plan shows significant ‘Remote Spill to Disk’. Which of the following actions would be MOST effective in reducing the spill and improving query performance? Assume all statistics are up-to-date and the data is properly clustered by ORDER_DATE.

Increase the virtual warehouse size. This will provide more memory for the query to execute.

Increase the value of the parameter. This allows the warehouse to scale up further if needed.

Rewrite the query to use window functions instead of aggregate functions.

Reduce the number of columns selected in the query, only selecting those that are essential for the aggregation.

Optimize the query to leverage data pruning based on ORDER DATE by ensuring the query filters on a specific or limited range of ORDER DATE values.

QUESTION 150
You have a VARIANT column named ‘raw_data’ in a Snowflake table ‘eventS , containing nested JSON data’. You need to extract specific fields Cevent_id’, ‘timestamp’ , and ‘user.user_id’) and load them into a relational table ‘structured_events’ with columns ‘event_id’ , ‘timestamp’ , and ‘user_id’, respectively. However, some entries may be missing the ‘user’ object. Which of the following SQL statements will achieve this while handling missing ‘user’ objects gracefully and ensuring data integrity, and also efficiently handle potentially large JSON payloads?

Option A

Option B

Option C

Option D

Option E

QUESTION 151
A data engineering team is responsible for processing a high volume of semi-structured JSON data ingested daily into Snowflake. The ingestion process currently uses a single ‘X-Large’ virtual warehouse. During peak hours, the data loading latency increases significantly, impacting downstream reporting. The team is considering either scaling up to a ‘3X-Large’ warehouse or scaling out by creating a multi- cluster warehouse with a minimum of 2 and a maximum of 4 ‘X-Large’ clusters. Which of the following factors should be prioritized when making this decision to optimize performance, considering cost and concurrency requirements?

The anticipated number of concurrent data loading jobs and the ability of Snowflake to automatically distribute these jobs across multiple clusters.

The complexity of the JSON data and the potential for improved parsing performance by a larger, single warehouse due to increased memory.

The budget constraints and the higher per-second cost of a larger warehouse compared to the potential cost savings from reduced overall execution time.

The impact on other workloads sharing the same virtual warehouse, favoring scaling up to isolate the data loading process from other query activities.

The type of JSON processing used. If using SQL functions like PARSE JSON, scaling out is more beneficial than scaling up.

QUESTION 152
You are tasked with designing a data pipeline that ingests JSON data from an external stage (AWS S3). The JSON files contain records for various product types, each having a different set of attributes. Some product types might have attributes that are not present in other types. You want to create a single Snowflake table that can accommodate all product types without defining a rigid schema upfront and also be queryable efficiently. Which of the following approaches, combining external tables, schema evolution and querying, would be MOST effective? (Choose two)

Create a separate external table for each product type, defining the schema for each table based on the attributes present in the corresponding JSON files.

Create a single external table with a VARIANT column to store the entire JSON record for each product. Use LATERAL FLATTEN to extract specific attributes during querying.

Create a stored procedure that dynamically infers the schema from the JSON files and creates a new Snowflake table based on the inferred schema.

Create a single external table with a VARIANT column and use the ‘VALIDATE function to identify and handle schema inconsistencies during data loading.

Load all the data into a raw Snowflake internal table. Use dynamic SQL to infer distinct product types and create views on top of the raw table for each product type.

QUESTION 153
You are loading data from an S3 bucket into a Snowflake table using the COPY INTO command. The source data contains dates in various formats (e.g., ‘YYYY-MM-DD’, ‘MM/DD/YYYY’, ‘DD-Mon-YYYY’). You want to ensure that all dates are loaded correctly and consistently into a DATE column in Snowflake. Which of the following COPY INTO options and commands is the MOST appropriate to handle this?

Use the ‘DATE FORMAT option in the COPY INTO command with a single format string that covers all possible date formats.

Use the ‘ON_ERROR = ‘SKIP FILE” option to skip files with invalid date formats.

Use the ‘VALIDATE(O)’ command before the COPY INTO command to identify files with invalid date formats and then process them separately.

Use the ‘STRTOK TO DATE function within a SELECT statement in a Snowpipe transformation to dynamically parse the dates based on different patterns.

Utilize the ‘DATE’ function with explicit format strings inside a Snowpipe transformation pipeline. This involves pattern matching using ‘CASE WHEN’ statements to identify date formats before converting to the DATE data type.

QUESTION 154
You are designing a data protection strategy for a Snowflake environment that processes sensitive payment card industry (PCI) data’. You decide to use a combination of column-level security and external tokenization. Which of the following statements are TRUE regarding the advantages of using both techniques together? (Select TWO)

Combining masking policies and external tokenization allows for complete elimination of PCl data from the Snowflake environment, even during processing.

Masking policies and external tokenization provide independent layers of security. If one is compromised, the other still provides protection.

Tokenization ensures compliance with PCl DSS standards, while masking policies are primarily useful for internal access control and obfuscation for development environments. Using both doesn’t increase security.

Column-level security can be used to restrict access to the tokenization UDF itself, ensuring that only authorized users can perform tokenization or detokenization operations.

The use of both techniques increases query performance drastically.

QUESTION 155
A data engineer is tasked with creating a Snowpark Python UDF to perform sentiment analysis on customer reviews. The UDF, named ‘analyze_sentiment’ , takes a string as input and returns a string indicating the sentiment (‘Positive’, ‘Negative’, or ‘Neutral’). The engineer wants to leverage a pre-trained machine learning model stored in a Snowflake stage called ‘models’. Which of the following code snippets correctly registers and uses this UDF?

Option A

Option B

Option C

Option D

Option E

QUESTION 156
You have a Snowflake table, ‘raw_data’, which contains a column ‘data url’ storing URLs pointing to CSV files with varying schemas. Each CSV file represents sales data, but the column names and data types can differ. You need to create a process to automatically discover the schema of each CSV file, load the data into Snowflake, and standardize the column names to ‘order id’, ‘product id’, ‘quantity’, and ‘price’. Which of the following approaches best addresses this requirement, considering scalability and minimal manual intervention?

Create a stored procedure that iterates through each URL in ‘raw_data’ , downloads the CSV file using ‘SYSTEM$URL_GET , parses the CSV header to determine the column names, manually maps the discovered column names to the standardized names, creates a temporary table with the discovered schema, loads the data into the temporary table, transforms the data to use the standardized column names, and then inserts the transformed data into a final target table. Drop the temporary table after successful insertion.

Use Snowpipe with auto-ingest to continuously load the CSV files into a VARIANT column in a staging table. Create a series of views on top of the staging table, each view attempting to extract data based on different potential schema variations. Union all the views together to create a single consolidated view.

Create a Python-based external function that downloads the CSV file from the URL using a library like ‘pandas’, infers the schema using ‘pandas.read_csv’ , maps the discovered column names to the standardized names, and returns the data as a JSON string. Then, create a Snowflake table with a VARIANT column, call the external function for each URL, and load the returned JSON data into the table. Create a view on top of it.

Create a Snowflake external table that points to the external stage. Define a single file format to be used by external table. Define a pipe that uses ‘COPY INTO’ to ingest data into external table from the files found at the file URLs.

Leverage a combination of Snowflake Scripting and External functions: create external function that infer the schema of the CSV, create temporary table based on identified schema, fetch the CSV data using SYSTEM$URL GET using snowflake scripting, copy the data into the temporary table, tranform the data into required structure, ingest into target table and finally drop the temporary table

QUESTION 157
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?

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.

Use ‘ to extract item names and quantities, then use ‘ARRAY_CONSTRUCT and ‘OBJECT_CONSTRUCT to create the JSON array.

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.

Use ‘SPLIT with ‘;’ as delimiter, then apply ‘SPLIT again with ‘:’ as delimiter. Finally, construct the JSON array using ‘ARRAY_AGG’ and ‘OBJECT CONSTRUCT

Utilize a Java UDF to parse the string and directly generate the JSON array.

QUESTION 158
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

QUESTION 159
You are tasked with setting up a Kafka Connector to ingest data into Snowflake. You need to ensure fault tolerance. Which of the following Kafka Connect configurations are essential for enabling fault tolerance and ensuring minimal data loss during connector failures? Select all that apply.

Set ‘tasks.max’ to a value greater than 1.

Configure ‘errors.tolerance’ to ‘all’.

Enable Kafka Connect’s internal offset storage by configuring ‘offset.storage.topic’ and ‘config.storage.topic’.

Utilize Snowflake’s auto-ingest feature alongside the Kafka Connector.

Configure ‘errors.deadletterqueue.topic.name’ to specify a Dead Letter Queue (DLQ) topic.

QUESTION 160
You have created a masking policy called which redacts salary information based on the user’s role. You have applied this policy to the ‘SALARY column in the ‘EMPLOYEES table. However, after applying the policy, you notice that even users with the ‘ACCOUNTADMIN’ role are seeing the masked data, which is not the intended behavior. The intention is that ‘ACCOUNTADMIN’ and ‘SECURITYADMIN’ roles should always see the real salary data’. What is the MOST likely cause of this issue and what would you suggest fix that?

The ‘ACCOUNTADMIN’ role does not have the ‘OWNERSHIP’ privilege on the table. Grant the ‘OWNERSHIP’ privilege to ‘ACCOUNTADMIN’ on the ‘EMPLOYEES’ table.

The masking policy does not explicitly exclude the ‘ACCOUNTADMIN’ and ‘SECURITYADMIN’ roles. Modify the masking policy to include a condition that checks for these roles and returns the original value if they are active. e.g., ‘CASE WHEN IN (‘ACCOUNTADMIN’, ‘SECURITYADMIN’) THEN val ELSE END’

The masking policy is not properly activated. Run the ALTER TABLE EMPLOYEES MODIFY COLUMN SALARY SET MASKING POLICY salary_mask’ command again.

The ‘ACCOUNTADMIW and ‘SECURITYADMIIV roles do not have the ‘APPLY MASKING POLICY privilege. Grant this privilege to the roles.

The ‘ACCOUNTADMIN’ and roles need to have ‘SELECT’ privilege on the ‘SNOWFLAKACCOUNT USAGE.MASKING POLICIES view