Snowflake SnowPro Advanced: Data Engineer (DEA-C02) 認定 DEA-C02 試験問題:

1. 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?

A) 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'
B) The 'ACCOUNTADMIN' and roles need to have 'SELECT' privilege on the 'SNOWFLAKACCOUNT USAGE.MASKING POLICIES view
C) The masking policy is not properly activated. Run the ALTER TABLE EMPLOYEES MODIFY COLUMN SALARY SET MASKING POLICY salary_mask' command again.
D) The 'ACCOUNTADMIN' role does not have the 'OWNERSHIP' privilege on the table. Grant the 'OWNERSHIP' privilege to 'ACCOUNTADMIN' on the 'EMPLOYEES' table.
E) The 'ACCOUNTADMIW and 'SECURITYADMIIV roles do not have the 'APPLY MASKING POLICY privilege. Grant this privilege to the roles.

2. You are developing a JavaScript stored procedure in Snowflake using Snowpark to transform data'. The procedure needs to efficiently calculate the sum of 'amount' for each 'customer_id' in a large table named 'orders'. You want to avoid transferring large amounts of data to the client and utilize Snowpark's pushdown capabilities. Which of the following JavaScript code snippets is the MOST efficient and correct way to achieve this? Assume 'snowflake' binding is available.

A)

B)

C)

D)

E)

3. 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 an internal stage, copy all JSON Files, create and load the target table, and drop external table
B) Convert the JSON files to Parquet format and recreate the external table to point to the Parquet files.
C) Increase the size of the Snowflake virtual warehouse to provide more processing power.
D) Create a materialized view on top of the external table to pre-aggregate the data.
E) 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.

4. You're working on a data transformation pipeline in Snowflake. You need to create a SQL UDF called that accepts the following parameters: 'price' (NUMBER) (NUMBER) (BOOLEAN) The function should calculate the final price after applying the discount. If the customer , an additional 5% discount should be applied on top of the initial discount. Choose all the valid SQL UDF definitions that accurately implement the requirements. Only one Discount Percentage needs to apply to the base price.

A) Option E
B) Option A
C) Option C
D) Option D
E) Option B

5. You have a Snowflake view that joins three large tables: ORDERS, CUSTOMERS, and PRODUCTS. The query accessing this view is frequently used but performs poorly. You suspect inefficient join processing and potential skew in the data'. Which of the following strategies can be used to optimize the view's performance? (Select all that apply)

A) Replace the view with a materialized view to precompute and store the results.
B) Use JOIN hints, such as BROADCAST or MERGE, to guide the query optimizer on join strategies.
C) Analyze the query profile to identify bottlenecks and potential data skew issues, and then re-cluster the underlying tables based on the most frequently used join keys.
D) Partition the underlying tables based on the join keys to improve data locality.
E) Increase the virtual warehouse size to provide more resources for query processing. Convert the view into a table using CREATE TABLE AS SELECT (CTAS).

質問と回答：