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

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. 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.
• B. 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.
• C. 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.
• D. 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.
• E. 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.

You need to implement a data masking solution in Snowflake for a table 'CUSTOMER DATA' containing PII. The requirement is to mask the email address based on the user's role: if the user is in 'ANALYST ROLE , the email address should be partially masked (e.g., 'a @example.com'), otherwise, it should be fully masked (e.g., @ .com'). Which of the following masking policy definitions and subsequent actions will correctly implement this?

• A. Create two separate masking policies, one for 'ANALYST_ROLE' and one for all other roles. Apply both policies to the 'EMAIL' column of 'CUSTOMER DATA'. Grant the 'APPLY MASKING POLICY privilege on the 'CUSTOMER DATA' table to the 'ANALYST_ROLE.
• B. Create a masking policy 'email_mask' that always fully masks the email address. Grant the 'UNMASK' privilege on the 'EMAIL' column to the 'ANALYST ROLE
• C. Create a masking policy 'email_mask' using a 'CASE' statement that checks 'CURRENT_ROLE()'. If the role is 'ANALYST_ROLE, partially mask using 'LEFT and 'REGEXP REPLACE; otherwise, return original value. Apply this policy to the 'EMAIL' column of 'CUSTOMER DATA'.
• D. Create a masking policy 'email_mask' using 'REGEXP_REPLACE to replace the first part of the email with asterisks if the current role is not 'ANALYST_ROLE' , otherwise use 'LEFT and ' REGEXP_REPLACE to mask only part of the username. Apply this policy to the 'EMAIL ' column of 'CUSTOMER DATA'.
• E. Create a masking policy 'email_mask' using a 'CASE' statement that checks 'CURRENT_ROLE()'. If the role is 'ANALYST_ROLE, partially mask using 'LEFT and 'REGEXP REPLACE; otherwise, fully mask using 'REGEXP REPLACE. Apply this policy to the 'EMAIL' column of 'CUSTOMER DATA'.

You are tasked with building a data pipeline using Snowpark to process sensor data from IoT devices. The data arrives in near real-time as JSON payloads, and you need to transform and load it into a Snowflake table named 'SENSOR DATA'. The transformation logic involves extracting specific fields, converting data types, and filtering out records based on a timestamp. Consider performance optimization for large data volumes. Which of the following approaches, in combination, would be MOST efficient for this scenario?

• A. Using a Snowpark Python UDF to parse JSON and perform transformations, loading the result into a temporary table, and then merging into 'SENSOR DATA'.
• B. Leveraging Snowflake's native JSON parsing functions within a SQL transformation step implemented as a Snowpark DataFrame operation, combined with a Snowpipe for initial data ingestion into a staging table.
• C. Creating an external table pointing to the JSON data in cloud storage and using Snowpark DataFrames to read the external table, apply transformations, and load the result into 'SENSOR DATA'.
• D. Employing Snowpipe to ingest the raw JSON data into a VARIANT column in a staging table, followed by a Snowpark DataFrame operation using 'functions.get' to extract and transform the data, and finally loading into 'SENSOR DATA'
• E. Using a stored procedure written in Java to parse the JSON data and insert directly into the "SENSOR DATA' table.

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 containing external tables pointing to data stored in cloud storage (e.g., AWS S3), updated daily using a pipeline.
• B. Create a data share and grant access to all consumers directly on the base tables.
• 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 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.
• E. Create a data share and create a separate database for each consumer, cloning the data into each consumer's database daily.

A Snowflake data pipeline utilizes Snowpipe to ingest JSON data from cloud storage into a raw staging table 'RAW DATA' Subsequently, a series of transformation tasks are executed to cleanse, transform, and load the data into fact and dimension tables. You've noticed significant performance degradation in the transformation tasks, especially when dealing with large JSON payloads and deeply nested structures. Which of the following optimization techniques, applied at different stages of the pipeline, would MOST likely improve the overall performance of the data transformation tasks?

• A. Increasing the virtual warehouse size used by the transformation tasks to provide more compute resources.
• B. Partitioning the 'RAW DATA' staging table based on the ingestion timestamp to reduce the amount of data scanned during transformation.
• C. Using the file format option when defining the Snowpipe integration to remove the outer array from the JSON data before ingestion.
• D. Employing Snowflake's 'LATERAL FLATTEN' function with appropriate 'PATH' expressions to efficiently extract the required attributes from the JSON data during transformation.
• E. Replacing the transformation tasks with external functions implemented in Python using Snowpark, leveraging the power of Pandas DataFrames for JSON processing.