Prepare with Snowflake DEA-C02 exam practice material, pass for sure

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions:

Question 1

A large e-commerce company is experiencing performance issues with its daily sales report queries. These queries aggregate data from a fact table 'SALES FACT (100 billion rows) and several dimension tables, including 'CUSTOMER DIM', 'PRODUCT DIM', and 'DATE DIM'. The queries are run every morning and are essential for business decision-making. The team has identified that the 'SALES FACT table's primary key is 'SALE ID, but the queries frequently filter and join on 'CUSTOMER and 'PRODUCT ID. You want to use query acceleration service for these reports without changing query logic. Which combination of actions will MOST effectively leverage query acceleration service, assuming sufficient credits?

A. Enable clustering on the 'CUSTOMER DIM' and 'PRODUCT DIMS tables.
B. Enable search optimization on the columns 'CUSTOMER ID' and 'PRODUCT ID of the 'SALES FACT table, then enable query acceleration on the virtual warehouse. Set the QUERY_ACCELERATION_MAX_SCALE_FACTOR parameter to a reasonable value based on testing.
C. Increase the size of the virtual warehouse used for running the reports and enable query acceleration. Set the parameter to a high value.
D. Create materialized views that pre-aggregate the sales data based on 'CUSTOMER ID', 'PRODUCT ID, and 'DATE ID, then enable query acceleration on the virtual warehouse.
E. Enable Automatic Clustering on the 'SALES FACT table based on 'CUSTOMER ID' and 'PRODUCT ID, then enable query acceleration on the virtual warehouse.

Question 2

You are designing a Snowpark Python application to process streaming data from a Kafka topic and land it into a Snowflake table 'STREAMED DATA. Due to the nature of streaming data, you want to achieve the following: 1. Minimize latency between data arrival and data availability in Snowflake. 2. Ensure exactly-once processing semantics to prevent data duplication. 3. Handle potential schema evolution in the Kafka topic without breaking the pipeline. Which combination of Snowpark and Snowflake features, applied with the correct configuration, would BEST satisfy these requirements? Select all that apply.

A. Implement a Snowpark Python UDF that consumes data directly from the Kafka topic using a Kafka client library. Write data into 'STREAMED_DATX within a single transaction. Use a structured data type for the 'STREAMED DATA'.
B. Utilize Snowflake Streams on in conjunction with Snowpark to transform and cleanse the data after it has been ingested by Snowpipe. Apply a merge statement to update an external table of parquet files.
C. Use Snowflake's native Kafka connector to load data into a staging table. Then, use a Task and Stream combination, using a Snowpark Python UDF, to transform and load the data into 'STREAMED DATA' within a single transaction, handling schema evolution by casting columns to their new types or dropping missing column data.
D. Use Snowpipe with auto-ingest and configure it to trigger on Kafka topic events. Define a VARIANT column in 'STREAMED_DATX to handle schema evolution.
E. Use Snowflake Connector for Kafka to load data into a staging table. Then, use Snowpark Python to transform and load the data into 'STREAMED_DATR within a single transaction. Implement schema evolution logic in the Snowpark code to handle changes in the Kafka topic schema.

Question 3

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

Question 4

You are designing a system to monitor data access patterns in Snowflake. You want to capture detailed information about all queries executed, including the user, query text, execution time, and any potential data access violations based on security policies. Which of the following approaches, used in combination, would provide the MOST comprehensive and scalable solution for this monitoring requirement? (Select TWO)

A. Configure the 'SNOWFLAKE database's audit logs and stream them to an external security information and event management (SIEM) system.
B. Implement Snowflake's Event Tables and configure them to capture security-related events, such as data access policy violations.
C. Create a stored procedure to intercept all SQL commands before execution, log them, and then execute them using 'EXECUTE IMMEDIAT
D. Enable query tagging and insert custom tags into each SQL statement indicating sensitive data access. Then, query 'QUERY HISTORY filtering on these tags.
E. Enable the 'QUERY_HISTORY view in the 'ACCOUNT_USAGE' schema and periodically query it using a scheduled task.

Question 5

A Snowflake table, contains product information in a VARIANT column named This column holds JSON structures. You need to create a view, , that exposes specific fields , and 'category') as structured columns, and should gracefully handle scenarios where may contain characters incompatible with VARCHAR, 'category' is nested inside an array called 'tags'. What is the BEST and the MOST robust approach?

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

Solutions: