Online Access Free DEA-C02 Practice Test

You are designing a data pipeline that uses the Snowflake SQLAPI to execute a series of complex SQL queries. These queries involve multiple joins, aggregations, and user-defined functions (UDFs). You need to ensure that the pipeline is resilient to transient network errors and can handle a large volume of concurrent requests. Which of the following strategies would you implement to enhance the reliability and performance of your pipeline?

• A.Bundle all the SQL queries into a single, large SQL statement to reduce the number of API calls.
• B.Use the SQL API's asynchronous execution mode and poll for query status to handle long-running queries without blocking.
• C.Disable query result caching in Snowflake to ensure that the pipeline always retrieves the latest data.
• D.Implement exponential backoff and retry logic in your client application to handle transient errors when calling the SQL API.
• E.Increase the timeout value for the SQL API requests to accommodate potentially long-running queries.

You have a requirement to continuously load data from a cloud storage location into a Snowflake table. The source data is in Avro format and is being appended to the cloud storage location frequently. You want to automate this process using Snowpipe. You've already created the Snowpipe and the associated stage and file format. However, you notice that some files are being skipped during the ingestion process, and data is missing in your Snowflake table. What is the MOST likely reason for this issue, assuming all necessary permissions and configurations (stage, file format, pipe definition) are correctly set up?

• A.The cloud storage event notifications are not properly configured to trigger Snowpipe.
• B.The file format definition in Snowflake is incompatible with the Avro schema.
• C.Snowflake does not support Avro format for Snowpipe.
• D.The data files in cloud storage are not being automatically detected by Snowpipe.
• E.The Snowpipe is paused due to exceeding the daily quota.

A financial services company is using Snowflake Streams on a table 'TRANSACTIONS' to capture changes for auditing purposes. The 'TRANSACTIONS' table contains sensitive data, and the auditing team requires the stream to only capture changes to specific columns: 'ACCOUNT ID', 'TRANSACTION DATE', and 'TRANSACTION AMOUNT'. Which of the following approaches is the MOST efficient and secure way to achieve this requirement, ensuring minimal performance impact and data exposure?

• A.Create a task that clones the TRANSACTIONS table and a stream on that cloned table, limiting what changes are captured using a WHERE clause on the cloning command.
• B.Create a View that selects only the 'ACCOUNT ID, 'TRANSACTION DATE, and 'TRANSACTION AMOUNT columns and create a Stream on the View.
• C.Create a Stream on the 'TRANSACTIONS' table and use a masking policy on the stream's output to redact the unnecessary columns.
• D.Create a standard Stream on the 'TRANSACTIONS table and then filter the results in downstream processing to only include the required columns.
• E.Create a Stream on the 'TRANSACTIONS' table. Periodically truncate stream and reload all data from TRANSACTION table by applying filter while loading.

You have a Python UDF in Snowflake designed to enrich customer data by calling an external API to retrieve additional information based on the customer ID. Due to API rate limits, you need to implement a mechanism to cache API responses within the UDF to avoid exceeding the limits. The UDF is defined as follows:

Which caching mechanism can be implemented MOST effectively WITHIN the Python UDF to minimize API calls while adhering to Snowflake's UDF limitations?

• A.Use the 'functools.lru_cache' decorator to cache the results of the 'get_customer details' function within the UDF's scope. This will automatically cache the most recently used API responses.
• B.Create a global dictionary within the UDF to store the API responses, using the customer ID as the key. Before calling the API, check if the customer ID exists in the dictionary; if it does, return the cached response. This approach will keep cached values during the session.
• C.Leverage external caching services like Redis by making API calls to Redis from the UDF to store and retrieve cached API responses. This would require configuring Snowflake to connect with external systems.
• D.Persist the API responses in a temporary table within Snowflake. The UDF will first query the temporary table for the customer ID; if found, return the cached data. Otherwise, call the API and store the response in the temporary table for future use.
• E.Utilize Snowflake's built-in caching mechanisms (result caching) by ensuring the UDF is deterministic and only depends on its input parameters. Snowflake will automatically cache the results of the UDF for subsequent calls with the same input.

A healthcare provider stores patient data in Snowflake, including 'PATIENT ID', 'NAME, 'MEDICAL HISTORY , and 'INSURANCE ID. They need to comply with HIPAA regulations. As a data engineer, you need to ensure that PHI (Protected Health Information) is masked appropriately based on user roles. Which of the following steps are NECESSARY to achieve this using Snowflake's data masking features and RBAC? (Select all that apply)

• A.Identify the columns containing PHI and create appropriate masking policies for each column (e.g., masking 'NAME, 'MEDICAL HISTORY, INSURANCE_ID).
• B.Enforce multi-factor authentication (MFA) for all users accessing the Snowflake environment to enhance security and prevent unauthorized access to sensitive data.
• C.Grant the 'OWNERSHIP privilege on the 'PATIENT table to the 'ACCOUNTADMIN' role, ensuring complete control and management of the data by the administrator.
• D.Create custom roles representing different user groups within the organization (e.g., 'DOCTOR, 'NURSE, 'ADMIN') and grant them the necessary privileges to access the data, including 'SELECT on the tables and views containing patient data.
• E.Apply the created masking policies to the corresponding columns in the patient data tables, ensuring that the masking policies are designed to reveal only the necessary information based on the user's role (e.g., doctors see full medical history, nurses see limited medical history, admins see de-identified data).