Learn with Snowflake : DEA-C02 training material for 100% pass

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

1. You're designing a data pipeline in Snowflake that utilizes an external function to perform sentiment analysis on customer reviews using a third-party NLP service. This service charges per request. You need to minimize costs while ensuring timely processing of the reviews.
Which of the following strategies would be most effective in optimizing the cost and performance of your external function?

A) Set 'MAX BATCH_ROWS' to a very high value (e.g., 10000) to maximize the number of rows processed per API call, even if it increases latency for individual reviews.
B) Implement rate limiting and error handling in the external service (e.g., AWS Lambda or Azure Function) to gracefully handle API usage limits and prevent excessive charges due to errors.
C) Bypass the external function completely and rely solely on Snowflake's built-in NLP functions for sentiment analysis.
D) Implement a caching mechanism (e.g., using a Snowflake table or an external cache) to store the sentiment analysis results for frequently occurring reviews or similar text patterns, avoiding redundant API calls.
E) Pre-process the customer reviews in Snowflake to filter out irrelevant reviews (e.g., very short reviews or reviews with stop words) before sending them to the external function.

2. A data engineering team uses Snowflake to analyze website clickstream data stored in AWS S3. The data is partitioned by year and month in the S3 bucket. They need to query the data frequently for reporting purposes but don't want to ingest the entire dataset into Snowflake due to storage costs and infrequent full dataset analysis. Which approach is the MOST efficient and cost-effective way to enable querying of this data in Snowflake?

A) Create a Snowflake internal stage, copy the necessary files into the stage, and then load the data into a Snowflake table.
B) Create a Snowflake external stage pointing to the S3 bucket, define an external table on the stage, and use partitioning metadata to optimize queries.
C) Create a Snowpipe pointing to the S3 bucket and ingest the data continuously into a Snowflake table.
D) Use Snowflake's COPY INTO command to ingest data directly from S3 into a Snowflake table on a scheduled basis.
E) Load all the data into a Snowflake table and create a materialized view on top of the table to pre-aggregate the data for reporting.

3. 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 standard Stream on the 'TRANSACTIONS table and then filter the results in downstream processing to only include the required columns.
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 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.
E) Create a Stream on the 'TRANSACTIONS' table. Periodically truncate stream and reload all data from TRANSACTION table by applying filter while loading.

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 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.
C) Grant the 'APPLY MASKING POLICY privilege to the user running the batch job, allowing them to bypass the masking policy.
D) Set the 'DISABLE MASKING' session parameter to 'TRUE' for the batch job session. This will temporarily disable all masking policies.
E) 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.

5. 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 B
B) Option E
C) Option A
D) Option D
E) Option C

Solutions: