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

A.

B.

C.

D.

E.

A. Create a standard view with a stored procedure to handle the joins across databases and use EXECUTE AS OWNER to avoid permission issues. This standard view should be shared.

B. Create a secure view that joins tables from different databases and implement row-level security using a row access policy based on the CURRENT ROLE() function. Masking policy cannot provide role based access control so will not work.

C. Create a view for each table and then build a final view using 'UNION ALL' to combine data from all the views and implement row-level security with a role based row access policy. Standard views should not be used in data sharing.

D. Create a secure view that joins tables from different databases using fully qualified names (e.g., 'DATABASEI .SCHEMAI . TABLET) and implement row-level security using a masking policy based on the CURRENT_ROLE() function.

E. Create a standard view that joins tables from different databases using aliases and implement row-level security using a UDF that checks the consumer's role and filters the data accordingly.

A. You cannot create alerts based on a rolling hourly window within Snowflake. Alerts can only be based on fixed time intervals.

B. Use Snowflake's Resource Monitor feature and adjust the credit quota to trigger an alert if the credit usage exceeds the threshold for the virtual warehouse processing data pipeline, indirectly indicating that performance is degraded or data volume has changed significantly.

C. Create a Snowflake Stream on the 'ORDERS' table. Then create an Alert that triggers based on the metadata column, comparing it to the threshold value. This allows for real-time monitoring of data changes.

D. Create a Snowflake task that runs every hour, executes a query to count the rows loaded in the past hour and triggers an alert using 'SYSTEM$SEND_EMAIC if the count is below the threshold. No need to create a Snowflake alert.

E. Create a Snowflake Alert that executes a SQL query to count the number of rows loaded into the 'ORDERS table within the last hour. Configure the alert to trigger when the count is below the defined threshold. Use a Notification Integration to send alerts to a monitoring system.

A. Utilize Snowflake's automatic clustering on the 'TRANSFORMED_SALES table by specifying 'CLUSTER BY when creating or altering the table to ensure future data is efficiently accessed.

B. Use the 'pandas' API within the Snowpark stored procedure to perform the transformation, as 'pandas' automatically optimizes for data skew.

C. Increase the warehouse size significantly to compensate for the data skew and improve overall processing speed without modifying the partitioning strategy.

D. Implement a custom partitioning strategy using before the transformation logic to redistribute data evenly across the cluster.

E. Combine salting with repartitioning by adding a random number to the 'product_id' before repartitioning, then removing the salt after the transformation to break up the skew. Then, enable automatic clustering on the 'TRANSFORMED SALES' table.

A. Implement a stored procedure that periodically executes a query on the external table to force a metadata refresh.

B. Create a Snowpipe that continuously loads data from the S3 bucket into a Snowflake table instead of using an external table.

C. Enable auto-refresh on the external table using the 'AUTO_REFRESH = TRUE parameter during creation.

D. Periodically execute the 'ALTER EXTERNAL TABLE ext_logs REFRESH' command to update the metadata about the files in S3.

E. Configure an event notification service (e.g., AWS SQS) to trigger an external table refresh whenever new files are added to S3.

A. Create a separate DataFrame with average salaries per department, then join it back to the original DataFrame.

B. Use the 'window' function with 'avg' to compute the average salary per department and include it as a new column in the original DataFrame.

C. Use 'groupBV to get a dataframe containing average salary by department and then use a Python UDF to iterate through the 'employee_df and add the value to each row

D. Create a temporary table with average salaries per department, then join it back to the original DataFrame.

E. Use a correlated subquery within the SELECT statement to calculate the average salary for each department for each employee.

A. Implement exponential backoff and retry logic in your client application to handle transient errors when calling the SQL API.

B. Increase the timeout value for the SQL API requests to accommodate potentially long-running queries.

C. Use the SQL API's asynchronous execution mode and poll for query status to handle long-running queries without blocking.

D. Bundle all the SQL queries into a single, large SQL statement to reduce the number of API calls.

E. Disable query result caching in Snowflake to ensure that the pipeline always retrieves the latest data.

A. Create an external function that Data Insights can call to execute pre-approved aggregate functions on the underlying data. Grant USAGE on the function to Data Insights' role and create a secure view that uses that function.

B. Create a share object and grant USAGE privilege on the database containing the secure view to the share. Then, grant SELECT privilege on the secure view to the share. Finally, share the share with Data Insights' Snowflake account using their account identifier.

C. Create a row access policy that restricts the rows returned based on the role used by Data Insights. Then, grant SELECT privilege on the secure view directly to the role used by Data Insights' Snowflake account.

D. Create a masking policy that only allows aggregate functions to be executed by Data Insights' role and apply it to the relevant columns in the underlying table. Then, grant SELECT privilege on the secure view directly to the role used by Data Insights' Snowflake account.

E. Grant SELECT privilege on the secure view directly to the role used by Data Insights' Snowflake account.

A. Construct a single, large 'UPDATE statement with multiple 'CASE WHEN' clauses to update all rows in a single operation.

B. Use 'SnowflakeCursor.executemany()' with a list of tuples containing the update values.

C. Use with the option to insert the updated data into a staging table, then use a 'MERGE' statement to update the target table from the staging table.

D. Iterate through the rows of the Pandas DataFrame and execute an 'UPDATE statement for each row using 'cursor.execute()'.

E. Create a temporary table in Snowflake, load the DataFrame into the temporary table using , and then use a single 'UPDATE' statement with a 'JOIN' to the temporary table.

A. Create a standard external table directly on the S3 bucket. Refresh the external table metadata using SALTER EXTERNAL TABLE ... REFRESH' on a daily schedule.

B. Create an Iceberg table backed by the S3 bucket. Snowflake will automatically manage the metadata and handle incremental updates efficiently.

C. Create a series of views on top of the S3 bucket using 'READ_PARQUET function, updating view definitions whenever underlying files change.

D. Create a standard external table with the 'AUTO REFRESH' parameter set to 'TRUE'. This will automatically refresh the metadata whenever changes are detected in S3.

E. Create an external table using a Snowflake-managed catalog. Configure a Snowpipe to automatically refresh the metadata as new files are added to the S3 bucket.

A. Use Snowpark's async actions (e.g., to offload data processing to separate threads or processes, allowing your main Snowpark application to continue receiving data.

B. Implement dynamic warehouse scaling. Utilize Snowflake's Resource Monitors and the ability to programmatically resize warehouses through Snowpark. Monitor the queue depth or latency of your Snowpark application, and dynamically scale up the warehouse size when thresholds are exceeded. Then, scale it back down when traffic subsides.

C. Use 'CACHE RESULT for all queries in snowpark that use Kafka

D. Implement a message queuing system (e.g., RabbitMQ, Kafka) between Kafka and your Snowpark application to buffer incoming data during traffic spikes.

E. Configure the Snowflake warehouse used by your Snowpark application to use auto-suspend and auto-resume with a short auto-suspend time to minimize costs during periods of low traffic.

A. Use stored procedures with parameters to encapsulate the queries. This will ensure that the query syntax is consistent, regardless of the specific parameters used.

B. Optimize the 'SALES_FACT table by clustering it on the most frequently used filter columns and enabling automatic clustering. This will improve data locality and reduce the amount of data that needs to be scanned.

C. Implement query tagging to standardize query syntax. By applying consistent tags to queries, you can ensure that similar queries are recognized as identical and reuse cached results.

D. Create a separate virtual warehouse specifically for running these queries. This will isolate the cache and prevent it from being invalidated by other queries.

E. Implement a data masking policy on the 'SALES_FACT table. Data masking will reduce the size of the data that needs to be cached, improving cache utilization.

A. Use a larger virtual warehouse for the transformation job and partition the 'RAW CLICKS table on the ingestion timestamp to improve data pruning and reduce the amount of data processed during the transformation.

B. Replace the transformation job with a series of smaller, more specialized jobs, each running on a separate virtual warehouse optimized for the specific task, and orchestrate these jobs using a data pipeline tool.

C. Implement a change data capture (CDC) mechanism on the source systems to only ingest changed data into 'RAW CLICKS, reducing the overall data volume and the amount of data processed by the transformation job.

D. Create a materialized view on top of 'RAW CLICKS' that pre-computes the necessary transformations and aggregations, allowing the 'CLICK EVENTS' table to be populated directly from the materialized view.

E. Optimize the transformation queries by identifying and rewriting inefficient SQL patterns, ensuring appropriate use of joins, filtering conditions, and data type conversions.