Snowflake DEA-C02 Test Engine Dumps Training With 354 Questions [Q18-Q42]

NO.18 A data engineer needs to optimize the performance of a series of complex transformations performed using Snowflake stored procedures. These procedures involve multiple table joins, aggregations, and data filtering operations. The current execution time is unacceptably long. Which of the following optimization strategies are most likely to provide the greatest performance improvements, considering both code-level optimizations and Snowflake’s architecture? Select all that apply.

Rewrite the stored procedures to use procedural logic (e.g., loops and cursors) more extensively, as this allows for fine-grained control over the execution flow and can improve performance for certain operations.

Refactor the stored procedures to leverage set-based operations (e.g., using SQL statements with joins and aggregations) whenever possible, allowing Snowflake to parallelize the execution across multiple nodes.

Implement data caching within the stored procedures using temporary tables to store intermediate results, reducing the need to repeatedly query the same data.

Use external functions written in languages like Python or Java for computationally intensive tasks that are not efficiently handled by SQL, taking advantage of Snowflake’s support for external functions.

Ensure the underlying tables are appropriately clustered and consider using materialized views for frequently accessed aggregations or pre-computed results to improve query performance.

NO.19 You are developing a JavaScript UDF in Snowflake to perform complex data validation on incoming data’. The UDF needs to validate multiple fields against different criteria, including checking for null values, data type validation, and range checks. Furthermore, you need to return a JSON object containing the validation results for each field, indicating whether each field is valid or not and providing an error message if invalid. Which approach is the MOST efficient and maintainable way to structure your JavaScript UDF to achieve this?

Use a single, monolithic JavaScript function with nested if-else statements to handle all validation logic. Return a JSON string containing the validation results.

Create separate JavaScript functions for each validation check (e.g., ‘isNull’, ‘isValidType’, ‘isWithinRange’). Call these functions from the main UDF and aggregate the results into a JSON object.

Utilize a JavaScript library like Lodash or Underscore.js within the UDF to perform data manipulation and validation. Return a JSON string containing the validation results.

Define a JavaScript object containing validation rules and corresponding validation functions. Iterate through the object and apply the rules to the input data, collecting the validation results in a JSON object. This object is returned as a string.

Directly embed SQL queries within the JavaScript UDF to perform data validation checks using Snowflake’s built-in functions. Return a JSON string containing the validation results.

NO.20 You are designing a continuous data pipeline to load data from AWS S3 into Snowflake. The data arrives in near real-time, and you need to ensure low latency and minimal impact on your Snowflake warehouse. You plan to use Snowflake Tasks and Streams. Which of the following approaches would provide the most efficient and cost-effective solution for this scenario, considering data freshness and resource utilization?

Create a single, root Snowflake Task that triggers every 5 minutes, executing a COPY INTO command to load all new data from the S3 bucket into a staging table, followed by a MERGE statement to update the target table. Use ‘VALIDATE ( STAGE NAME ‘0’.////’ before COPY INTO.

Configure an AWS SQS queue to receive S3 event notifications whenever a new file is uploaded. Use a Lambda function triggered by the SQS queue to invoke a Snowflake stored procedure. This stored procedure executes a COPY INTO command to load the specific file into Snowflake. Use ‘ON ERROR = CONTINUE’ during COPY INTO.

Create a Stream on the target table and a Snowflake Task. The task executes a COPY INTO command into a staging table when the Stream has data and then a MERGE statement. Schedule the task to run continuously with ‘WHEN SYSTEM$STREAM HAS but limit the ‘WAREHOUSE SIZE’ to

Create a Pipe object in Snowflake using Snowpipe and configure the S3 bucket for event notifications to the Snowflake-provided SQS queue. Monitor the Snowpipe status using ‘SYSTEM$PIPE STATUS and address any errors by manually retrying failed loads with ‘ALTER PIPE REFRESH;’

Create a Stream on the target table and a Snowflake Task that runs every minute. The task executes a MERGE statement to apply changes from the Stream to the target table, filtering the Stream data using the ‘SYSTEM$STREAM GET TABLE TIMESTAMP function to process only newly arrived data since the last task execution. Use ‘WHEN SYSTEM$STREAM HAS to run the Task.

NO.21 You are tasked with building a data pipeline that ingests data from various sources into Snowflake, processes it, and then writes the final results back to a data lake in AWS S3, partitioned by date. The data in S3 should be queryable by other applications outside of Snowflake. You choose to use Snowflake Iceberg tables for this purpose. Which of the following is the correct SQL statement to create an Iceberg table ‘analytics.public.daily_summary’ in Snowflake, backed by an S3 bucket ‘s3://your-bucket/data/daily_summary/’, partitioned by the column, and specifying ‘parquet’ as the file format?

Option A

Option B

Option C

Option D

Option E

NO.22 You need to implement a data masking solution in Snowflake for a table ‘CUSTOMER DATA’ containing PII. The requirement is to mask the email address based on the user’s role: if the user is in ‘ANALYST ROLE , the email address should be partially masked (e.g., ‘a @example.com’), otherwise, it should be fully masked (e.g., @ .com’). Which of the following masking policy definitions and subsequent actions will correctly implement this?

Create a masking policy ’email_mask’ using ‘REGEXP_REPLACE to replace the first part of the email with asterisks if the current role is not ‘ANALYST_ROLE’ , otherwise use ‘LEFT and ‘ REGEXP_REPLACE to mask only part of the username. Apply this policy to the ‘EMAIL ‘ column of ‘CUSTOMER DATA’.

Create two separate masking policies, one for ‘ANALYST_ROLE’ and one for all other roles. Apply both policies to the ‘EMAIL’ column of ‘CUSTOMER DATA’. Grant the ‘APPLY MASKING POLICY privilege on the ‘CUSTOMER DATA’ table to the ‘ANALYST_ROLE.

Create a masking policy ’email_mask’ using a ‘CASE’ statement that checks ‘CURRENT_ROLE()’. If the role is ‘ANALYST_ROLE, partially mask using ‘LEFT and ‘REGEXP REPLACE; otherwise, fully mask using ‘REGEXP REPLACE. Apply this policy to the ‘EMAIL’ column of ‘CUSTOMER DATA’.

Create a masking policy ’email_mask’ that always fully masks the email address. Grant the ‘UNMASK’ privilege on the ‘EMAIL’ column to the ‘ANALYST ROLE

Create a masking policy ’email_mask’ using a ‘CASE’ statement that checks ‘CURRENT_ROLE()’. If the role is ‘ANALYST_ROLE, partially mask using ‘LEFT and ‘REGEXP REPLACE; otherwise, return original value. Apply this policy to the ‘EMAIL’ column of ‘CUSTOMER DATA’.

NO.23 You have a Snowflake view that joins three large tables: ORDERS, CUSTOMERS, and PRODUCTS. The query accessing this view is frequently used but performs poorly. You suspect inefficient join processing and potential skew in the data’. Which of the following strategies can be used to optimize the view’s performance? (Select all that apply)

Replace the view with a materialized view to precompute and store the results.

Use JOIN hints, such as BROADCAST or MERGE, to guide the query optimizer on join strategies.

Analyze the query profile to identify bottlenecks and potential data skew issues, and then re-cluster the underlying tables based on the most frequently used join keys.

Increase the virtual warehouse size to provide more resources for query processing. Convert the view into a table using CREATE TABLE AS SELECT (CTAS).

Partition the underlying tables based on the join keys to improve data locality.

NO.24 You’re designing a Snowpark data transformation pipeline that requires running a Python function on each row of a large DataFrame. The Python function is computationally intensive and needs access to external libraries. Which of the following approaches will provide the BEST combination of performance, scalability, and resource utilization within the Snowpark architecture?

Use ‘DataFrame.foreach(lambda row: my_python_function(row))’ to iterate through each row and apply the Python function.

Create a Snowpark UDF using input_types=[StringType()], return_type=StringType())’ and apply it to the DataFrame using

Load the DataFrame into a Pandas DataFrame using and then apply the Python function using Pandas DataFrame operations.

Create a Snowpark UDTF using gudtf(output_schema=StructType([StructField(‘result’, StringType())]), and apply it to the DataFrame using with a lateral flatten operation.

Define a stored procedure in Snowflake and use it to execute the Python code on each row by calling it in a loop.

NO.25 You are responsible for monitoring a critical data pipeline that loads data from an external Kafka topic into a Snowflake table ‘ORDERS’ Data anomalies have been frequently observed, impacting downstream reporting. You want to implement a solution that proactivelyidentifies and alerts on data quality issues such as missing values, invalid formats, and unexpected data distributions. Which combination of Snowflake features and approaches would be MOST effective for achieving this objective with minimal performance overhead on the pipeline itself?

Using Snowflake’s ‘VALIDATE’ table function after the data load to check for data corruption and then trigger alerts based on the validation results.

Implementing custom SQL-based data quality checks within a scheduled Snowflake task that runs after the data load and writing results to an audit table for monitoring.

Leveraging Snowflake’s Data Governance features along with Snowpark UDFs to define and enforce data quality rules at the time of ingestion using a Python- based library like Great Expectations, configured to trigger alerts through Snowflake Notifications.

Creating a separate Snowflake pipeline that reads from the same Kafka topic, performs data quality checks in real-time using Snowpipe and streams the results to an alert system.

Employing Snowflake’s built-in statistics and histogram features to analyze data distribution in the ‘ORDERS’ table and configure alerts based on deviations from historical patterns, combined with a Snowflake Native App for data quality reporting.

NO.26 Consider a table ‘EVENT DATA’ that stores events from various applications. The table has columns like ‘EVENT ID, ‘EVENT TIMESTAMP, ‘APPLICATION ID’, ‘USER ID’, and ‘EVENT _ TYPE. A significant portion of queries filter on ‘EVENT TIMESTAMP ranges AND ‘APPLICATION ID. The data volume is substantial, and query performance is crucial. You observe high clustering depth after initial loading. Which combination of actions will provide the MOST effective performance optimization, addressing both clustering depth and query performance?

Cluster the table on ‘EVENT TIMESTAMP’ and periodically run ‘OPTIMIZE TABLE EVENT DATA’ using a small warehouse. Also, create a separate table clustered on ‘APPLICATION

Cluster the table on ‘(EVENT TIMESTAMP, APPLICATION IDY and periodically run ‘OPTIMIZE TABLE EVENT DATA’ using a warehouse sized appropriately for the table size. Then, monitor clustering depth regularly.

Cluster the table on ‘USER ICY and rely solely on Snowflake’s automatic reclustering feature, without running ‘OPTIMIZE TABLES manually.

Create separate tables for each ‘ , each clustered on ‘EVENT_TIMESTAMP’. Then, create a view that UNION ALLs these tables.

Create multiple materialized views: one filtering on common ‘EVENT TIMESTAMP’ ranges, and another filtering on common ‘APPLICATION ID’ values.

NO.27 You are implementing Snowpipe using the REST API for a custom data ingestion process. Your application uploads data files to an internal stage named ‘@MY STAGE and then calls the Snowpipe REST API to trigger the data load. However, you are encountering ‘insufficient privileges’ errors when calling the API, even though the role used to authenticate the API requests has the ‘USAGE’ privilege on the stage and the ‘OPERATE’ privilege on the pipe. The Pipe name is ‘MY PIPE’. Which of the following is the MOST LIKELY cause of this error, and what can you do to resolve it?

The role used to authenticate the API requests does not have the ‘SELECT’ privilege on the underlying table that the Snowpipe is loading data into.

The role used to authenticate the API requests does not have the ‘OWNERSHIP’ privilege on the Snowpipe.

The role used to authenticate the API requests requires the ‘INSERT privilege on the target table in addition to ‘USAGE on the stage and ‘OPERATE on the pipe. Grant the ‘INSERT privilege to the role.

The Snowpipe’s ‘EXECUTE TASK’ privilege has not been granted to the role used for the REST API calls. Grant the ‘EXECUTE TASK’ privilege to the role.

The role doesn’t have the ‘USAGE’ privilege on the database and schema containing the stage and pipe. Grant ‘USAGE’ on the database and schema.

NO.28 You have a VARIANT column named ‘raw_data’ in a Snowflake table ‘eventS , containing nested JSON data’. You need to extract specific fields Cevent_id’, ‘timestamp’ , and ‘user.user_id’) and load them into a relational table ‘structured_events’ with columns ‘event_id’ , ‘timestamp’ , and ‘user_id’, respectively. However, some entries may be missing the ‘user’ object. Which of the following SQL statements will achieve this while handling missing ‘user’ objects gracefully and ensuring data integrity, and also efficiently handle potentially large JSON payloads?

Option A

Option B

Option C

Option D

Option E

NO.29 A data engineer is working with a Snowpark DataFrame ‘sales df containing sales data with columns ‘product id’, ‘sale_date’, and ‘sale amount’. The engineer needs to calculate the cumulative sales amount for each product over time. Which of the following code snippets using window functions correctly calculates the cumulative sales amount, ordered by ‘sale date’?

Option A

Option B

Option C

Option D

Option E

NO.30 A provider account is sharing a database named ‘SHARED DB’ through a share named ‘MY SHARE. The consumer account has created a database named ‘CONSUMER DB’ from the share. The provider account revokes access to a table named ‘SALES DATA within ‘SHARED DB’. What will happen when a user in the consumer account attempts to query ‘CONSUMER DB.SHARED SCHEMA.SALES DATA’?

The query will execute successfully, but the user will receive an empty result set.

The query will fail with an error message indicating that the table does not exist or the user does not have privileges.

The query will execute successfully, but only return data that existed before the access was revoked.

The query will be automatically re-routed to another available share containing ‘SALES DATA’.

The query will be cached based on the initial access, so users can continue query previous result based on same SQL

NO.31 You are tasked with loading a large dataset (50TB) of JSON files into Snowflake. The JSON files are complex, deeply nested, and irregularly structured. You want to maximize loading performance while minimizing storage costs and ensuring data integrity. You have a dedicated Snowflake virtual warehouse (X-Large).
Which combination of approaches would be MOST effective?

Use Snowpipe with auto-ingest, create a single VARIANT column in your target table, and rely solely on Snowflake’s automatic schema detection.

Pre-process the JSON data using a Python script with Pandas to flatten the structure and convert it into a relational format like CSV. Then, load the CSV files using the COPY INTO command with gzip compression.

Use Snowpipe with auto-ingest, create a raw VARIANT column alongside projected relational columns for frequently accessed fields, and use search optimization on those projected columns.

Load the JSON data using the COPY INTO command with no pre-processing. Create a VIEW on top of the raw VARIANT column to flatten the data for querying.

Load the JSON data using the COPY INTO command with gzip compression. Create a raw VARIANT column alongside projected relational columns for frequently accessed fields, and use materialized views to improve query performance.

NO.32 A data engineering team is using a Snowflake stream to capture changes made to a source table named ‘orders’. They want to only capture ‘INSERT and ‘UPDATE operations but exclude ‘DELETE operations from being captured in the stream. Which of the following configurations will achieve this requirement? Assume the stream has already been created and is named ‘orders_stream’.

It’s impossible to configure a stream to exclude specific DML operations. All changes are always tracked.

Create a view on top of the base table that filters out deleted rows, and then create a stream on the view.

Alter the stream using the ‘HIDE_DELETES parameter: ‘ALTER STREAM orders_stream SET HIDE_DELETES = TRUE;’

Use task and stream combination. In the task, create view using ‘select from orders where metadata$isDelete = false’ and create stream on that view.

Create a Snowflake task that periodically truncates the stream’s metadata table, removing DELETE records.

NO.33 You have a large Snowflake table ‘WEB EVENTS that stores website event data’. This table is clustered on the ‘EVENT TIMESTAMP column. You’ve noticed that certain queries filtering on a specific ‘USER ID’ are slow, even though ‘EVENT TIMESTAMP clustering should be helping. You decide to investigate further Which of the following actions would be MOST effective in diagnosing whether the clustering on ‘EVENT TIMESTAMP is actually benefiting these slow queries?

Run ‘ EXPLAIN’ on the slow query and examine the ‘partitionsTotal’ and ‘partitionsScanned’ values. A significant difference indicates effective clustering.

Use the SYSTEM$CLUSTERING_INFORMATIOW function to get the ‘average_overlaps’ for the table and ‘EVENT_TIMESTAMP’ column. A low value indicates good clustering.

Query the ‘QUERY_HISTORY view to see the execution time of the slow query and compare it to the average execution time of similar queries without a ‘USER filter.

Execute ‘SHOW TABLES’ and check the ‘clustering_key’ column to ensure that the table is indeed clustered on ‘EVENT _ TIMESTAMP’.

Run ‘SYSTEM$ESTIMATE QUERY COST to estimate the query cost to see if the clustering is impacting the cost.

NO.34 You have a Snowflake table ‘raw_data’ with columns ‘id’, ‘timestamp’, and ‘payload’. A stream is defined on this table. A data pipeline reads changes from the stream and applies transformations before loading the data into a target table. However, the pipeline needs to handle cases where updates to the same ‘id’ occur multiple times within a short period, and only the latest version of the ‘payload’ should be processed. How can you achieve this idempotent processing of stream data to ensure only the latest payload is applied to the target table, avoiding duplicates and inconsistencies, using Snowflake streams?

Use a regular Snowflake task to periodically merge the stream data into the target table, overwriting any existing records with the same Sid’.

Create a materialized view on the stream, grouping by ‘id’ and selecting the maximum ‘timestamp’ and corresponding ‘payload’. Then, consume the materialized view instead of the stream.

When processing data from the stream, use a MERGE statement with a staging table. Load all stream changes into the staging table, then merge from the staging table to the target table using ‘timestamp’ to identify the latest version.

Before loading data into target table, create a temporary table by grouping Sid’ and selecting the maximum ‘timestamp’ and corresponding ‘payload’ from stream. Finally, load this data into target table.

Configure the stream with a unique key constraint on the Sid’ column to prevent multiple updates for the same Sid’ from being captured.

NO.35 You are using Snowpark Python to perform data transformation on a large dataset stored in a Snowflake table named customer transactions’. This table contains columns such as ‘customer id’, ‘transaction date’, ‘transaction amount’, and product_category’. Your task is to identify customers who have made transactions in more than one product category within the last 30 days. Which of the following Snowpark Python snippets is the most efficient way to achieve this, minimizing data shuffling and maximizing query performance?

Option A

Option B

Option C

Option D

Option E

NO.36 You are designing a data pipeline that requires applying a complex scoring algorithm to customer data in Snowflake. This algorithm involves multiple steps, including feature engineering, model loading, and prediction. You want to encapsulate this logic within a reusable component and apply it to incoming data streams efficiently. Which of the following approaches is most suitable and scalable for implementing this scoring logic as a UDF/UDTF, considering real-time data processing and low latency requirements?

A SQL UDF containing a series of nested CASE statements to implement the entire scoring algorithm.

A Python UDF that loads a pre-trained machine learning model (e.g., using scikit-learn) and performs predictions on the input data.

A Java UDTF that leverages a custom Java library for feature engineering and model prediction, deployed as a JAR file to Snowflake’s internal stage.

A Python UDTF using Snowpark, leveraging external libraries like ‘torch’ for accelerated calculations and ML model inference by GPU.

A JavaScript UDF that uses basic JavaScript functions to perform the entire scoring algorithm without external dependencies.

NO.37 You need to implement both a row access policy and a dynamic data masking policy on the ‘EMPLOYEE table in Snowflake. The requirements are as follows: 1. Employees should only be able to see their own record in the ‘EMPLOYEE table. 2. The ‘SALARY’ column should be masked for all employees except those with the ‘HR ADMIN’ role. Unmasked values are required for compliance reasons, they need to be available for ‘HR ADMIN’ role. Given the following table structure: CREATE TABLE EMPLOYEE ( EMPLOYEE ID INT, EMPLOYEE NAME STRING, SALARY NUMBER, EMAIL STRING ) ; Which of the following sets of steps correctly implement the row access policy and dynamic data masking policy?

Option A

Option B

Option C

Option D

Option E

NO.38 You are designing a data governance strategy for a Snowflake data warehouse. You need to track data lineage for compliance purposes. Specifically, you need to identify all downstream tables that depend on a specific column in a source table. Which combination of Snowflake features and techniques would you use to achieve this goal effectively?

Use Snowflake’s INFORMATION SCHEMA views (TABLES, COLUMNS) and regularly audit user query history to manually reconstruct the data lineage.

Utilize Snowflake’s data lineage feature in conjunction with object tagging. Tag relevant columns and tables, then query the lineage views to trace dependencies.

Implement a custom data lineage tracking system by parsing all SQL queries executed in the Snowflake environment and storing the dependencies in a separate metadata database.

Rely solely on user documentation and training to ensure data lineage is properly documented and maintained. Implement strict naming conventions for tables and columns.

Use Snowflake’s ACCOUNT USAGE views related to query history and object dependencies, combined with a custom script to recursively trace data lineage based on SQL operations (e.g., INSERT INTO … SELECT).

NO.39 A global e-commerce company, ‘GlobalMart’, uses Snowflake for its data warehousing needs. They operate primarily in the US (us-east-1) and Europe (eu-west-l). They’re implementing cross-region replication for disaster recovery and business continuity. Their requirements are: 1) All data from the US region needs to be replicated to the EU region. 2) The failover to the EU region should have minimal downtime. 3) Replication should be automatic and continuous. Considering these requirements, which of the following Snowflake features and configurations would be the MOST suitable and efficient?

Create a database replica in the EU region and manually refresh it periodically using ‘CREATE DATABASE AS CLONE’

Enable database replication using replication groups, configure a primary database in us-east-I , and a secondary database in eu-west-l. Set the replication schedule with ‘ALTER REPLICATION GROUP ADD .

Use Snowflake’s Data Sharing feature to share data from the US region with an account in the EU region. This automatically replicates the data.

Export data from the US region to cloud storage (e.g., AWS S3 or Azure Blob Storage) and then load it into the EU region using Snowpipe.

Manually unload data from the US region and load it into the EU region using SnowSQL. Automate this process using a scheduled task.

NO.40 You are tasked with implementing a data recovery strategy for a critical table ‘SALES DATA’ in Snowflake. The table is frequently updated, and you need to ensure you can recover to a specific point in time in case of accidental data corruption. Which approach provides the most efficient and granular recovery option, minimizing downtime and data loss? Consider performance and storage implications of each method.

Regularly creating full clones of the ‘SALES_DATR table to a separate database.

Relying solely on Snowflake’s Time Travel feature with the default data retention period.

Creating a Snowflake Stream on ‘SALES_DATR and capturing all DML changes for point-in-time recovery.

Using the ‘UNDROP TABLE command in conjunction with the ‘AT’ clause to revert the table to a previous state.

Create a scheduled task that takes a snapshot of the sales data and store it to an external staging location.

NO.41 You are loading JSON data into a Snowflake table with a ‘VARIANT’ column. The JSON data contains nested arrays with varying depths. You need to extract specific values from the nested arrays and load them into separate columns in your Snowflake table. Which approach would provide the BEST performance and flexibility?

Use a stored procedure to parse the JSON data and insert values into the table row by row.

Load the entire JSON into a ‘VARIANT column and then use SQL with nested ‘FLATTEN’ functions to extract the desired values during query time.

Create a view with nested ‘FLATTEN’ functions to extract the values from the ‘VARIANT column. The view serves as the source for further transformations.

Use a ‘COPY’ command with a ‘TRANSFORM’ clause that uses JavaScript UDFs to parse the JSON and extract the values during the load process. Load the extracted values directly into the target columns.

Use Snowpipe with auto-ingest, loading directly into the table with the ‘VARIANT column. Define data quality checks with pre-load data transformation.

NO.42 You are performing a series of complex data transformations on a large table named ‘TRANSACTIONS’ in Snowflake. After running several DML statements, you realize that an earlier transformation step introduced incorrect data into the table. You want to rollback the table to a state before that specific transformation occurred. Which of the following methods could be used to achieve this rollback, assuming you know the exact timestamp or query ID of the state you want to revert to? Select all that apply.

Restore the entire Snowflake account to a point in time before the incorrect transformation.

Create a clone of the ‘ TRANSACTIONS’ table using Time Travel, specifying the ‘AT’ or ‘BEFORE clause with either the timestamp or query ID of the desired state. Then, replace the original table with the cloned table.

Use the UNDROP TABLE command if the table was dropped accidentally, then manually re-apply the correct transformations.

Create a new table with the correct data and load from the original table filtered by a range of transaction IDs excluding the incorrect range.

Use Time Travel to query the historical version of the ‘TRANSACTIONS’ table using the ‘AT’ or ‘BEFORE clause with either the timestamp or query ID. Then, use ‘INSERT OVERWRITES or ‘ REPLACE TABLES statement to replace the current content of the original table with the historical data.

Exam Study Guide & Braindumps

Snowflake DEA-C02 Test Engine Dumps Training With 354 Questions [Q18-Q42]

Leave a Reply Cancel reply