DEA-C02 by Snowflake Actual Free Exam Q&As

Question 1

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. Create an internal stage, copy all JSON Files, create and load the target table, and drop external table 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. Increase the size of the Snowflake virtual warehouse to provide more processing power. E. Convert the JSON files to Parquet format and recreate the external table to point to the Parquet files.

Discussion 0

Correct Answer: C,E Vote an answer

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

Question 2

You're designing a Snowpark data transformation pipeline that requires running a Python function on each row of a large DataFrame. The Python function is computationally intensive and needs access to external libraries. Which of the following approaches will provide the BEST combination of performance, scalability, and resource utilization within the Snowpark architecture?

A. Create a Snowpark UDF using input_types=[StringType()], return_type=StringType())' and apply it to the DataFrame using B. Define a stored procedure in Snowflake and use it to execute the Python code on each row by calling it in a loop. C. Use 'DataFrame.foreach(lambda row: my_python_function(row))' to iterate through each row and apply the Python function. D. Create a Snowpark UDTF using gudtf(output_schema=StructType([StructField('result', StringType())]), and apply it to the DataFrame using with a lateral flatten operation. E. Load the DataFrame into a Pandas DataFrame using and then apply the Python function using Pandas DataFrame operations.

Discussion 0

Correct Answer: A,D Vote an answer

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

Question 3

You have a Snowflake table named 'CUSTOMER DATA that contains sensitive Personally Identifiable Information (PII). You need to grant a data analyst access to a subset of the data while masking specific columns containing PII. Which of the following Snowflake features, when used in combination, provides the MOST secure and efficient solution?

A. Create a new table containing only the non-sensitive columns and grant SELECT privileges on this table. B. Grant the analyst the 'SELECT privilege on the 'CUSTOMER_DATA' table and implement row-level security based on the analyst's role. C. Create a view on 'CUSTOMER_DATA' with column masking policies and grant SELECT privileges on the view. D. Create a stored procedure that retrieves the data with masking applied and grant the analyst EXECUTE privilege on the stored procedure. E. Create a secure view on 'CUSTOMER DATA' with column masking policies and grant SELECT privileges on the secure view.

Discussion 0

Correct Answer: E Vote an answer

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

Question 4

You need to unload data from a Snowflake table named 'CUSTOMER DATA to an AWS S3 bucket The data should be unloaded in Parquet format, partitioned by the 'CUSTOMER REGION' column, and automatically compressed with GZIP. Furthermore, you only want to unload customers whose 'REGISTRATION DATE is after '2023-01-01'. Which of the following 'COPY INTO' statement correctly achieves this?

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

Discussion 0

Correct Answer: A Vote an answer

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

Question 5

You have data residing in AWS S3 in Parquet format, which is updated daily with new columns being added occasionally. The data is rarely accessed, but when it is, it needs to be queried using SQL within Snowflake. You want to minimize storage costs within Snowflake while ensuring the data can be queried without requiring manual table schema updates every time a new column is added to the S3 data'. Which approach is MOST suitable?

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

Discussion 0

Correct Answer: D Vote an answer

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

Question 6

You have a 'SALES table and a 'PRODUCTS table. The 'SALES table contains daily sales transactions, including 'SALE DATE , 'PRODUCT ID', and 'QUANTITY. The 'PRODUCTS table contains 'PRODUCT and 'CATEGORY. You need to create a materialized view to track the total quantity sold per category daily, optimized for fast query performance. You anticipate frequent updates to the 'SALES table but infrequent changes to the 'PRODUCTS table. Which of the following strategies would provide the MOST efficient materialized view implementation, considering both data freshness and query performance?

A. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE_DATE and 'CATEGORY, and defining a clustering key on 'SALE DATE' and 'CATEGORY. B. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE DATE and 'CATEGORY without any specific clustering key. C. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE_DATE and 'CATEGORY, and defining a clustering key on 'SALE DATE. D. Create two materialized views: one for daily sales by product and another joining the first with 'PRODUCTS' to aggregate by category. Cluster the first view by 'SALE DATE' and the second by 'CATEGORY'. E. Create a standard materialized view that joins 'SALES' and 'PRODUCTS' , grouping by 'SALE_DATE and 'CATEGORY, and defining a clustering key on ' CATEGORY.

Discussion 0

Correct Answer: C Vote an answer

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

Question 7

A data engineering team is implementing column-level security on a Snowflake table named 'CUSTOMER DATA containing sensitive PII. They want to mask the 'EMAIL' column for users in the 'ANALYST role but allow users in the 'DATA SCIENTIST role to view the unmasked email addresses. The 'ANALYST role already has SELECT privileges on the table. Which of the following steps are necessary to achieve this using a masking policy?

A. Create a masking policy with a CASE statement that checks the CURRENT ROLE() function to see if it's 'ANALYST'. If true, mask the email; otherwise, return the original email. B. Create a masking policy that uses the CURRENT_USER() function to check if the current user belongs to the 'ANALYST' role. C. Create a masking policy that uses the CURRENT ROLE() function to return a masked value if the current role is 'ANALYST and the original value otherwise. D. Create a masking policy that uses the IS_ROLE_IN_SESSION('ANALYST') function to return a masked value if the analyst role is active in current session and the original value otherwise. E. Create a dedicated view on 'CUSTOMER DATA' for analysts with the 'EMAIL' column masked using a CASE statement within the view's SELECT statement. Grant SELECT privilege to the ANALYST role on the view only.

Discussion 0

Correct Answer: A,C Vote an answer

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

Question 8

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. 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. B. Use the 'VALIDATE(O)' command before the COPY INTO command to identify files with invalid date formats and then process them separately. C. Use the 'ON_ERROR = 'SKIP FILE" option to skip files with invalid date formats. 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 'STRTOK TO DATE function within a SELECT statement in a Snowpipe transformation to dynamically parse the dates based on different patterns.

Discussion 0

Correct Answer: A Vote an answer

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

Question 9

A data engineer observes that a Snowflake query, used for generating a daily sales report, consistently runs slower each day, despite the dataset size remaining relatively stable. The query joins a large sales table (SALES) with a smaller product dimension table (PRODUCT) on PRODUCT ID. You've already confirmed that virtual warehouse sizing is adequate and data clustering is enabled on SALES(SALE DATE). Analyze the following scenarios and identify the MOST likely cause of the performance degradation and potential solution:

A. The data clustering on SALES(SALE DATE) is ineffective as the query also filters on PRODUCT CATEGORY, which isn't clustered. Re-cluster SALES by both SALE DATE and PRODUCT CATEGORY. B. The virtual warehouse is being overwhelmed by concurrent queries. Implement workload management rules to prioritize the sales report query. C. The join between SALES and PRODUCT is causing excessive data spill to local storage due to an inefficient join order. Consider using a JOIN hint, specifically a BROADCAST hint on the PRODUCT table (small table). D. The Snowflake query optimizer is selecting a suboptimal execution plan because the query is complex. Rewrite the query using temporary tables to break down the logic into smaller steps. E. The statistics on the PRODUCT table are outdated. Run 'ANALYZE TABLE PRODUCT' to refresh them.

Discussion 0

Correct Answer: C Vote an answer

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

Question 10

You are designing a data governance strategy for a Snowflake data warehouse. One of the key requirements is to track data lineage for sensitive data, specifically Personally Identifiable Information (PII). You need to understand how PII data flows through various transformations and tables. Which Snowflake feature, when combined with appropriate tagging and metadata management practices, can BEST help you achieve this?

A. Snowflake Secure Data Sharing B. Snowflake INFORMATION_SCHEMA views and Account Usage views C. Snowflake's Resource Monitors D. Snowflake Cloning E. Snowflake Data Masking policies

Discussion 0

Correct Answer: B Vote an answer

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

Question 11

You are tasked with building a User-Defined Aggregate Function (UDAF) in Snowflake to calculate the weighted average of product prices. The weight is determined by the quantity sold for each product. You have the following table: 'SALES (product_id INTEGER, price quantity INTEGER)'. Which of the following UDAF definitions would correctly calculate the weighted average?

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

Discussion 0

Correct Answer: B Vote an answer

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

Question 12

You accidentally truncated a large table named 'SALES DATA' in your 'REPORTING DB" database. You realize this happened 2 days ago, and your account has the default Time Travel retention of 1 day. You need to recover this table with minimal downtime. Analyze the situation and determine the best course of action, considering cost and recovery time.

A. Increase the account-level to 2 days and then use the UNDROP TABLE SALES_DATA' command. B. Create a clone of the table using the 'AT clause and a timestamp from 1 day ago. This would prevent any additional cost. C. Immediately contact Snowflake Support to initiate a restore from Fail-safe, understanding that this process may take several hours or even days. D. Raise a support ticket requesting data recovery from failsafe. Since data retention period has expired. E. Because the data retention period has expired, the table is unrecoverable using Snowflake's built-in features; you must restore from an external backup solution if available.

Discussion 0

Correct Answer: C Vote an answer

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

Question 13

You have a table 'EMPLOYEE DATA' containing Personally Identifiable Information (PII), including 'salary' and 'email'. You need to implement column-level security such that: 1) The 'salary' column is only visible to users in the 'FINANCE ROLE. 2) The 'email' column is masked with a SHA256 hash for all users except those in the 'HR ROLE. You create the following masking policies:

Which of the following SQL statements correctly applies these masking policies to the 'EMPLOYEE DATA table?

A. ALTER TABLE EMPLOYEE_DATA MODIFY COLUMN salary SET MASKING POLICY = mask_salary; ALTER TABLE EMPLOYEE_DATA MODIFY COLUMN email SET MASKING POLICY = mask email; B. ALTER TABLE EMPLOYEE_DATA MODIFY COLUMN salary SET MASKING POLICY mask_salary; ALTER TABLE EMPLOYEE_DATA MODIFY COLUMN email SET MASKING POLICY mask email; C. ALTER TABLE EMPLOYEE_DATAAPPLY MASKING POLICY mask_salary ON COLUMN salary; ALTER TABLE EMPLOYEE_DATAAPPLY MASKING POLICY mask email ON COLUMN email; D. ALTER TABLE EMPLOYEE_DATAALTER COLUMN salary SET MASKING POLICY mask_salary; ALTER TABLE EMPLOYEE_DATAALTER COLUMN email SET MASKING POLICY mask email; E. CREATE OR REPLACE TAG employee_data.salary VALUE 'mask_salary'; CREATE OR REPLACE TAG employee_data.email VALUE 'mask_email';

Discussion 0

Correct Answer: D Vote an answer

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

Question 14

You are implementing a data pipeline to load data from AWS S3 into Snowflake. The source data consists of CSV files with a header row. Some of the CSV files have inconsistent data types in a specific column (e.g., sometimes an integer, sometimes a string). You want to use the 'COPY' command to load the data and handle these data type inconsistencies gracefully. Which of the following 'COPY' command options, used in conjunction, would BEST address this issue and avoid load failures? Assume the file format is already defined to specify CSV type, header skip, and field delimiter.

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

Discussion 0

Correct Answer: E Vote an answer

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

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