DEA-C02 by Snowflake Actual Free Exam Questions And Answers

Question 1

You are tasked with creating a SQL UDF in Snowflake to mask sensitive customer data (email addresses) before it's used in a reporting dashboard. The masking should replace all characters before the '@' symbol with asterisks, preserving the domain part. For example, '[email protected]' should become ' @example.com'. Which of the following SQL UDF definitions correctly implements this masking logic, while minimizing the impact on Snowflake compute resources?

A. Option B

B. Option A

C. Option D

D. Option C

E. Option E

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 2

You have a base table 'ORDERS' with columns 'ORDER ID, 'CUSTOMER D', 'ORDER DATE, and 'ORDER AMOUNT'. You need to create a view that aggregates the total order amount per customer per month. However, for data governance purposes, you need to ensure that the view only shows data for the last 3 months. What is the MOST efficient and secure way to create this view in Snowflake?

A. Option B

B. Option A

C. Option D

D. Option C

E. Option E

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 3

A data engineer is tasked with optimizing query performance on a Snowflake table named 'SALES DATA, which currently has no clustering key defined. The table contains 'SALE (unique identifier), 'SALE DATE, 'PRODUCT CATEGORY, and 'SALE AMOUNT. The business analysts frequently run queries filtering on 'SALE DATE and then aggregating by 'PRODUCT CATEGORY'. Choosing the right clustering keys for the SALES DATA table is crucial for minimizing disk 1/0 and enhancing query speed. Which of the following clustering key strategies would be MOST effective for the specified query patterns, considering both performance and the potential impact on data loading and DML operations?

A. Creating separate tables for each 'PRODUCT CATEGORY.

B. Clustering on followed by 'SALE_DATE'.

C. Clustering only on 'SALE DATE

D. Clustering on 'SALE DATE followed by 'PRODUCT CATEGORY.

E. Clustering only on PRODUCT_CATEGORY.

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 4

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 and create a separate database for each consumer, cloning the data into each consumer's database daily.

B. Create a data share containing external tables pointing to data stored in cloud storage (e.g., AWS S3), updated daily using a pipeline.

C. 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.

D. Create a data share and grant access to all consumers directly on the base tables.

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.

Discussion 0

Correct Answer: E Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 5

You are using Snowpipe to load data from an AWS S3 bucket into Snowflake. The data files are compressed using GZIP and are being delivered frequently. You have observed that the pipe's backlog is increasing and data latency is becoming unacceptable. Which of the following actions could you take to improve Snowpipe's performance? (Select all that apply)

A. Ensure that the S3 event notifications are correctly configured and that there are no errors in the event delivery mechanism.

B. Reduce the number of columns in the target Snowflake table. Fewer columns reduce the overhead of data loading.

C. Check if the target table has any active clustering keys defined which could be causing slow down

D. Increase the virtual warehouse size associated with the pipe.

E. Optimize the file size of the data files in S3. Smaller files are processed faster by Snowpipe.

Discussion 0

Correct Answer: A,C,D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 6

You have a data pipeline that loads data from an internal stage into a Snowflake table Craw_data'). The pipeline is experiencing intermittent failures with the error 'SQL compilation error: Stage 'MY INTERNAL STAGE' is immutable'. What are the potential causes of this error and how would you troubleshoot it?

A. The internal stage has been accidentally dropped and recreated with the same name during the COPY operation. Verify the stage's existence and creation timestamp.

B. The user executing the COPY INTO command lacks the necessary privileges (USAGE on the stage). Grant the appropriate privileges to the user or role.

C. Another concurrent process is attempting to drop or alter the internal stage while the COPY INTO command is running. Implement proper locking mechanisms to prevent concurrent modifications.

D. This error is caused by insufficient warehouse size. Increase the warehouse size to accommodate the COPY INTO operation.

E. The internal stage is being used by multiple COPY INTO commands simultaneously, causing a resource contention issue. Implement queuing or throttling mechanisms to manage concurrent data loading.

Discussion 0

Correct Answer: A,C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 7

You have a Snowflake table, 'CUSTOMER ORDERS', with columns like 'CUSTOMER ID', 'ORDER DATE', 'ORDER AMOUNT', and 'REGION'. A Bl dashboard relies on a query that aggregates data from this table, but the query performance is consistently slow. The query frequently filters by 'ORDER DATE and groups by 'REGION'. Based on the following 'EXPLAIN' output, which combination of techniques should be considered to improve the performance the most?

A. Create a materialized view that pre-aggregates the data by 'ORDER DATE and 'REGION'

B. Create an index on the 'ORDER DATE column.

C. Cluster the 'CUSTOMER ORDERS table on 'ORDER DATE' and 'REGION'.

D. Redesign the dashboard to minimize the data being displayed at once to the user.

E. Increase the virtual warehouse size to 'LARGE or 'XLARGE.

Discussion 0

Correct Answer: A,C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 8

A financial institution needs to implement both dynamic data masking and column-level security on the 'CUSTOMER DATA table, which contains sensitive information like 'CREDIT CARD NUMBER and 'SSN'. The requirement is: all users except those in the 'DATA ADMIN' role should see masked credit card numbers (last 4 digits unmasked) and masked SSNs. Users in 'DATA ADMIN' should see the original data'. Which of the following combination of policies and grants will achieve this?

A. Create two masking policies: one for 'CREDIT CARD NUMBER' and another for 'SSN'. Grant APPLY MASKING POLICY privilege to the 'DATA ADMIN' role. Apply masking policies to the appropriate columns. Grant SELECT privilege on the table to PUBLI

B. Create two masking policies: one for 'CREDIT CARD NUMBER and another for 'SSN' using the 'CASE' statement to apply masking logic based on the current_role(). Apply masking policies to the appropriate columns. Grant SELECT privilege on the table to PUBLIC.

C. Create two masking policies: one for 'CREDIT CARD NUMBER' and another for 'SSN'. Grant APPLY MASKING POLICY privilege to the 'DATA ADMIN' role and then apply masking policies to the appropriate columns. Grant SELECT privilege on the table to PUBLIC.

D. Create two masking policies: one for 'CREDIT CARD NUMBER' and another for 'SSN'. Grant APPLY MASKING POLICY privilege to the 'DATA ADMIN' role. Do not grant any SELECT privileges on the table.

E. Create two masking policies: one for 'CREDIT CARD NUMBER and another for 'SSN'. Apply masking policies to the appropriate columns. Create a custom role with the 'APPLY MASKING POLICY privilege, grant this custom role to the 'DATA_ADMIN' role. Grant SELECT privilege on the table to PUBLIC.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 9

A financial institution is using Snowflake to store transaction data for millions of customers. The data is stored in a table named 'TRANSACTIONS with columns such as 'TRANSACTION ID, 'CUSTOMER ID', 'TRANSACTION DATE, 'TRANSACTION_AMOUNT, and 'MERCHANT CATEGORY'. Analysts are running complex analytical queries that often involve filtering transactions by 'TRANSACTION_DATE, 'MERCHANT CATEGORY' , and 'TRANSACTION_AMOUNT ranges. These queries are experiencing performance bottlenecks. The data team wants to leverage query acceleration service to improve performance without significantly altering the existing query patterns. Which of the following actions or combination of actions would be MOST beneficial, considering the constraints and the nature of the queries? (Select TWO)

A. Create separate virtual warehouses dedicated to reporting queries and ad-hoc queries respectively. Enable query acceleration only for the warehouse running reporting queries.

B. Increase the size of the virtual warehouse used for running the queries and enable query acceleration on the warehouse without further modifications.

C. Enable Search Optimization Service for the 'TRANSACTIONS' table, specifically targeting the 'MERCHANT_CATEGORY column. Enable query acceleration on the virtual warehouse.

D. Create materialized views pre-aggregating the transaction data by 'MERCHANT_CATEGORY and 'TRANSACTION_DATE, and enable query acceleration on the virtual warehouse.

E. Enable Automatic Clustering on the 'TRANSACTIONS' table, ordering the keys as 'TRANSACTION_DATE, 'MERCHANT_CATEGORY', 'CUSTOMER_ID. Then, enable query acceleration on the virtual warehouse.

Discussion 0

Correct Answer: C,E Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 10

You are responsible for monitoring the performance of a Snowflake data pipeline that loads data from S3 into a Snowflake table named 'SALES DATA. You notice that the COPY INTO command consistently takes longer than expected. You want to implement telemetry to proactively identify the root cause of the performance degradation. Which of the following methods, used together, provide the MOST comprehensive telemetry data for troubleshooting the COPY INTO performance?

A. Use Snowflake's partner connect integrations to monitor the virtual warehouse resource consumption and query the 'VALIDATE function to ensure data quality before loading.

B. Query the 'COPY_HISTORY view and the view in 'ACCOUNT_USAG Also, check the S3 bucket for throttling errors.

C. Query the 'COPY HISTORY view in the 'INFORMATION SCHEMA' and monitor CPU utilization of the virtual warehouse using the Snowflake web I-Jl.

D. Query the ' LOAD_HISTORY function and monitor the network latency between S3 and Snowflake using an external monitoring tool.

E. Query the 'COPY HISTORY view in the 'INFORMATION SCHEMA' and enable Snowflake's query profiling for the COPY INTO statement.

Discussion 0

Correct Answer: B,E Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 11

You have a Snowflake table named 'ORDERS clustered on 'ORDER DATE. After a significant data load, you want to evaluate the effectiveness of the clustering. Which of the following SQL queries, using Snowflake system functions, will provide insights into the clustering depth and overlap of micro-partitions in the 'ORDERS' table, specifically helping you identify whether re-clustering is necessary? Assume that the table

Discussion 0

Correct Answer: E Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 12

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?

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

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

C. 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.

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

E. Use the 'ON_ERROR = 'SKIP FILE" option to skip files with invalid date formats.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 13

You are tasked with ingesting data from an external stage into Snowflake. The data is in JSON format and compressed using GZIP. The JSON files contain nested arrays. You need to create a file format object that Snowflake can use to properly parse the dat a. Which of the following options represents the MOST efficient and correct file format definition to achieve this? Assume the stage is already created and accessible.

A. Option B

B. Option A

C. Option D

D. Option C

E. Option E

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 14

You are setting up a Kafka connector to load data from a Kafka topic into a Snowflake table. You want to use Snowflake's automatic schema evolution feature to handle potential schema changes in the Kafka topic. Which of the following is the correct approach to enable and configure automatic schema evolution using the Kafka Connector for Snowflake?

A. Set the 'value.converter.schemas.enable' to 'true' and provide Avro schemas and also, configure the Snowflake table with appropriate data types for each field. Schema Evolution is not supported by the Kafka Connector for Snowflake.

B. Set the property to 'true' and the 'snowflake.ingest.stage' to an existing stage.

C. Automatic schema evolution is not directly supported by the Kafka Connector for Snowflake. You must manually manage schema changes in Snowflake.

D. Set 'snowflake.ingest.file.name' to an existing file in a stage.

E. Set the 'snowflake.data.field.name' property to the name of the column in the Snowflake table where the JSON data will be stored as a VARIANT, and set 'snowflake.enable.schematization' to 'true'.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) - DEA-C02 Exam Practice Test

Contact Us

Useful Links

Latest Updated