Get real DEA-C02 exam questions for better preparation

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions:

1. You're building a data product on the Snowflake Marketplace that includes a view that aggregates data from a table containing Personally Identifiable Information (PII). You need to ensure that consumers of your data product CANNOT directly access the underlying PII data but can only see the aggregated results from the view. What is the MOST secure and recommended approach to achieve this?

A) Create a stored procedure that returns the aggregated data, and grant EXECUTE privilege on the stored procedure to the share. The stored procedure SELECTs from the PII table.
B) Grant USAGE privilege on the database containing the PII table and to the share.
C) Grant the 'SELECT privilege directly on the underlying PII table to the share used for the Marketplace listing, along with the 'SELECT privilege on 'sensitive data view'.
D) Grant 'READ privilege on the internal stage containing the data files backing the PII table.
E) Grant the 'SELECT privilege only on the to the share used for the Marketplace listing. Do not grant any privileges on the underlying PII table.

2. Consider a table with columns and 'customer _ region'. You want to implement both a Row Access Policy (RAP) and an Aggregation Policy on this table. The RAP should restrict access to orders based on the user's region, defined in a session variable 'CURRENT REGION'. Users should only see orders from their region. The Aggregation Policy should mask order totals for regions other than the user's region when aggregating data'. In other words if someone attempts to aggregate ALL region's totals, the aggregation will only include their region. Which statements about implementing this scenario are true?

A) You can use the function within both the RAP and Aggregation Policy to control access based on user roles in addition to region.
B) Using external functions in RAPs can introduce performance overhead, especially if the external function is complex or slow to execute.
C) The Aggregation Policy is evaluated before the RAP, ensuring that even if users try to bypass the RAP by aggregating across all regions, the results will be masked appropriately according to 'CURRENT REGION'.
D) You cannot use session variables directly in Row Access Policies; you must pass the session variable as an argument to a user-defined function (UDF) called by the policy.
E) The RAP should be applied first to filter the data, and then the Aggregation Policy will apply to the filtered data, only masking aggregated values within the user's region.

3. Which of the following statements are true regarding using Dynamic Data Masking and Column-Level Security in Snowflake? (Select all that apply)

A) Dynamic Data Masking policies can reference external tables directly without requiring special grants.
B) Dynamic Data Masking can be used to apply different masking rules based on the user's role, IP address, or other contextual factors.
C) Column-Level Security via views provides more fine-grained control over data access compared to Dynamic Data Masking.
D) Using both Dynamic Data Masking and Column-Level Security (e.g. views) on the same column is redundant and will result in an error.
E) Dynamic Data Masking is applied at query runtime, while Column-Level Security through views or roles is applied when the object is created.

4. You have implemented a masking policy on the 'SSN' column of the 'EMPLOYEES' table. You now need to suspend the masking policy temporarily for a specific batch job that requires access to the unmasked data'. What is the recommended way to achieve this without dropping the masking policy or altering the user's role?

A)

B) Create a new role with 'ACCOUNTADMIN' privileges, assign this role to the batch job process during the execution, and then revert back to the original role after the job is done.
C) Create a temporary view on the 'EMPLOYEES' table without the 'SSN' column, grant access to the view for the batch job, and drop the view after the job is complete.
D) Grant the 'APPLY MASKING POLICY privilege to the user running the batch job, allowing them to bypass the masking policy.
E) Set the 'DISABLE MASKING' session parameter to 'TRUE' for the batch job session. This will temporarily disable all masking policies.

5. You have a Snowflake Stream named 'ORDERS STREAM' on an 'ORDERS' table, which is used to incrementally load data into a historical orders table named 'HISTORICAL ORDERS'. The data pipeline involves a series of tasks: 1) Consume changes from the 'ORDERS STREAM', 2) Apply transformations and data quality checks, and 3) Merge the changes into 'HISTORICAL ORDERS' using a MERGE statement. After a recent data load, you notice that the 'HISTORICAL ORDERS' table contains duplicate records for certain 'ORDER values. The MERGE statement uses 'ORDER ID' as the matching key. You have confirmed that the transformation logic is correct and idempotent. Examine the MERGE statement below. What could be causing the duplicates, given the context of Streams and incremental loading?

A) The MERGE statement is not correctly handling updates and deletes from the stream. The 'WHEN NOT MATCHED' and 'WHEN MATCHED' clauses are not mutually exclusive, leading to potential insertions of duplicate rows.
B) The stream is not configured to capture DELETE operations from the ORDERS table, causing records that should have been removed in HISTORICAL ORDERS to remain.
C) The stream's or 'BEFORE clause is being used incorrectly, potentially rewinding the stream to an earlier point in time.
D) The 'ORDERS STREAM' is retaining historical data beyond the data retention period, causing older records to be re-processed.
E) Multiple tasks are concurrently consuming from the same 'ORDERS STREAM' without proper coordination, causing records to be processed multiple times.

Solutions: