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

1. Your team is developing a set of complex analytical queries in Snowflake that involve multiple joins, window functions, and aggregations on a large table called 'TRANSACTIONS. These queries are used to generate daily reports. The query execution times are unacceptably high, and you need to optimize them using caching techniques. You have identified that the intermediate results of certain subqueries are repeatedly used across different reports, but they are not explicitly cached. Given the following options, which combination of strategies would MOST effectively utilize Snowflake's caching capabilities to optimize these analytical queries and improve report generation time?

A) Create common table expressions (CTEs) for the subqueries and reference them in the main query. CTEs will force Snowflake to cache the results of the subqueries, improving performance.
B) Use temporary tables to store the intermediate results of the subqueries. These tables will be automatically cached by Snowflake and can be reused by subsequent queries within the same session.
C) Utilize the "RESULT_SCAN' function in conjunction with the query ID of the initial subquery execution to explicitly cache and reuse the results in subsequent queries. This approach requires careful management of query IDs.
D) Consider using 'CACHE RESULT for particularly expensive subqueries or views. This is a hint to snowflake to prioritize caching the result set for future calls.
E) Create materialized views that pre-compute the intermediate results of the subqueries. This will allow Snowflake to automatically refresh the materialized views when the underlying data changes and serve the results directly from the cache.

2. A data engineering team is building a real-time dashboard in Snowflake to monitor website traffic. The dashboard relies on a complex query that joins several large tables. The query execution time is consistently exceeding the acceptable threshold, impacting dashboard responsiveness. Historical data is stored in a separate table and rarely changes. You suspect caching is not being utilized effectively. Which of the following actions would BEST improve the performance of this dashboard and leverage Snowflake's caching features?

A) Increase the size of the virtual warehouse. A larger warehouse will have more resources to execute the query, and the results will be cached for a longer period.
B) Use 'RESULT_SCAN' to cache the query result in the user session for subsequent queries. This is especially effective for large datasets that don't change frequently.
C) Replace the complex query with a series of simpler queries. This will reduce the amount of data that needs to be processed at any one time.
D) Materialize the historical data into a separate table that utilizes clustering and indexing for faster query performance. Refresh this table periodically.
E) Create a materialized view that pre-computes the results of the complex query. Snowflake will automatically refresh the materialized view when the underlying data changes.

3. You are tasked with designing a data sharing solution where data from multiple tables residing in different databases within the same Snowflake account needs to be combined into a single view that is then shared with a consumer account. The view must also implement row-level security based on the consumer's role. Which of the following options represent valid approaches for implementing this solution? Select all that apply.

A) Create a standard view that joins tables from different databases using aliases and implement row-level security using a UDF that checks the consumer's role and filters the data accordingly.
B) Create a standard view with a stored procedure to handle the joins across databases and use EXECUTE AS OWNER to avoid permission issues. This standard view should be shared.
C) Create a secure view that joins tables from different databases and implement row-level security using a row access policy based on the CURRENT ROLE() function. Masking policy cannot provide role based access control so will not work.
D) Create a secure view that joins tables from different databases using fully qualified names (e.g., 'DATABASEI .SCHEMAI . TABLET) and implement row-level security using a masking policy based on the CURRENT_ROLE() function.
E) Create a view for each table and then build a final view using 'UNION ALL' to combine data from all the views and implement row-level security with a role based row access policy. Standard views should not be used in data sharing.

4. You've created a JavaScript stored procedure using Snowpark to transform data'. The stored procedure is failing, and you suspect an issue with how Snowpark is handling null values during a join operation. Given two Snowpark DataFrames, and 'df2 , what is the expected behavior when performing an inner join on a column containing null values in both DataFrames, and how can you mitigate potential issues?

A) The inner join will treat null values as equal, resulting in rows where the join column is null in both DataFrames being included in the result. To avoid this, you should filter out null values before the join.
B) The behavior of the inner join with null values is undefined and may vary depending on the data types and the specific version of Snowpark. Explicit null handling is always required.
C) The inner join will exclude rows where the join column is null in either DataFrame. To include these rows, you must use a full outer join instead.
D) The inner join will automatically exclude rows where the join column is null in either DataFrame. There is no need for explicit null handling.
E) Inner Join will not throw an error, and will exclude the rows where join column is null. If you need to join records with null values, pre-processing dataframes using to replace null with a valid sentinel value before performing the join is one way to handle this.

5. A data engineering team is implementing Row Access Policies (RAP) on a table 'employee_data' containing sensitive salary information. They need to ensure that only managers can see the salary information of their direct reports. A user-defined function (UDF) 'GET returns a comma-separated string of manager usernames for a given username. Which of the following SQL statements correctly creates and applies a RAP to achieve this?

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

質問と回答：