Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

Question #1

Consider a scenario where you have a large dataset of sensor readings stored in a Snowflake table called 'SENSOR DATA'. You need to build an external function to perform complex calculations on these readings using a custom Python library hosted on AWS Lambda'. The calculation requires significant computational resources, and you want to optimize the data transfer between Snowflake and the Lambda function. The following SQL is provided: CREATE OR REPLACE EXTERNAL FUNCTION ARRAY) RETURNS ARRAY VOLATILE MAX BATCH ROWS = 2000 RETURNS NULL ON NULL INPUT API INTEGRATION = aws_lambda_integration AS 'arn:aws:lambda:us-east-1:123456789012:function:sensorProcessor'; Which of the following options would further optimize the performance and reduce data transfer costs, assuming the underlying Lambda function is correctly configured and functional?

A. Convert the input data to a binary format (e.g., using 'TO_BINARY and FROM_BINARY' functions in Snowflake) before sending it to the Lambda function, and decode it in Lambda to reduce the size of the data being transmitted.
B. Increase the 'MAX BATCH ROWS' parameter to the maximum allowed value to send larger batches of data to the external function. Ensure Lambda function memory is increased appropriately.
C. Rewrite the custom Python library in Java and create a Snowflake User-Defined Function (UDF) instead of using an external function.
D. Compress the data before sending it to the external function and decompress it within the Lambda function. Update the Lambda function to compress the array of results before sending it back to Snowflake and use Snowflake+s functions to decompress it.
E. Reduce the number of columns passed to the external function by performing pre-aggregation or filtering on the data within Snowflake before calling the function.

Question #2

A company stores raw clickstream data in AWS S3. They need to query this data occasionally (less than once per day) for ad-hoc analysis and auditing purposes without ingesting it into Snowflake. Which of the following approaches is MOST suitable and cost- effective, and which considerations regarding data freshness are crucial?

A. Create an internal stage in Snowflake and regularly copy the data from S3 into the internal stage using a Snowpipe. Query the data in the internal stage. Data freshness depends on the Snowpipe configuration.
B. Create an external table pointing directly to the S3 bucket. The data freshness depends on the automatic refresh enabled using the object. Ensure a metadata notification service (e.g., SQS) is configured to trigger the metadata refresh whenever new files are added to S3.
C. Create an external table pointing directly to the S3 bucket. The data freshness depends on manually refreshing the metadata using 'ALTER EXTERNAL TABLE ... REFRESH'. Automate this refresh with a scheduled task.
D. Load the data into a Snowflake table using COPY INTO and schedule a daily task to refresh the table. Data freshness is guaranteed by the scheduled task.
E. Create an external table pointing directly to the S3 bucket. Data freshness is automatically maintained by Snowflake whenever the external table is queried. No additional configuration is required.

Question #3

A critical database, 'PRODUCTION DB', in your Snowflake account was accidentally dropped. You need to restore it as quickly as possible, but you're unsure if Time Travel retention is sufficient. Which method guarantees restoration of the database even if it falls outside the Time Travel window?

A. Fail-safe cannot be directly accessed by the user for restoration purposes; it is only used by Snowflake Support in extreme disaster recovery scenarios.
B. Utilize the data cloning feature: 'CREATE DATABASE CLONE PRODUCTION_DB BEFORE (STATEMENT 'DROP DATABASE PRODUCTION_DB');'
C. Contact Snowflake Support and request restoration from Fail-safe.
D. Restore from a Snowflake-managed backup using the 'CREATE DATABASE ... FROM BACKUP' command. Specify the timestamp before the drop occurred.
E. Use the 'UNDROP DATABASE PRODUCTION command.

Question #4

You are developing a Snowpark Python application that processes data from a large table. You want to optimize the performance by leveraging Snowpark's data skipping capabilities. The table 'CUSTOMER ORDERS is partitioned by 'ORDER DATE. Which of the following Snowpark operations will MOST effectively utilize data skipping during data transformation?

A. Applying a filter '2023-01-01') & '2023-03-31'))' before performing any join or aggregation operations.
B. Executing 'df.collect()' to load the entire table into the client's memory before filtering.
C. Creating a new DataFrame with only the columns needed using 'ORDER_DATE', 'ORDER_AMOUNT')' before any filtering operations.
D. Using the 'cache()' method on the DataFrame before filtering by 'ORDER DATE
E. Applying a filter >= '2023-01-01') & (col('ORDER_DATE') <= '2023-03-31'))' after performing a complex join operation.

Question #5

Consider the following Snowflake Javascript UDF designed to convert temperatures between Celsius and Fahrenheit:

Which statement regarding the UDF's security and behavior is MOST accurate?

A. While the ' SECURE keyword hides the UDF's source code from users without sufficient privileges, users with the ACCOUNTADMIN role can still view the source code.
B. The UDF is not vulnerable to SQL injection because Javascript UDFs in Snowflake are sandboxed and prevent direct SQL execution.
C. The UDF is vulnerable to SQL injection because the 'unit' parameter is not validated against a predefined list of acceptable values.
D. The UDF will always return a value, even if the input 'unit is invalid, due to the implicit type conversion in JavaScript.
E. The 'SECURE' keyword ensures that the UDF's code is completely hidden from all users, including those with the ACCOUNTADMIN role.

Solutions: