Free Snowflake DEA-C02 Practice Test & Real Exam Questions

You have a data pipeline that aggregates web server logs hourly. The pipeline loads data into a Snowflake table 'WEB LOGS' which is partitioned by 'event_time'. You notice that queries against this table are slow, especially those that filter on specific time ranges. Analyze the following Snowflake table definition and query pattern and select the options to diagnose and fix the performance issue: Table Definition:

A data engineering team is tasked with optimizing a complex query that joins three tables: 'ORDERS' , 'CUSTOMERS' , and 'PRODUCTS. The 'ORDERS' table contains millions of records and is frequently joined with 'CUSTOMERS' (containing customer demographics) and 'PRODUCTS' (containing product details). The initial query uses standard JOIN syntax, but performance is slow. The query retrieves order details along with customer and product information, filtering by a specific date range in the 'ORDERS' table and a customer segment in the 'CUSTOMERS table. Which optimization strategy would be MOST effective for significantly improving query performance?

A data engineering team is building a real-time fraud detection system. They have a large 'TRANSACTIONS table that grows rapidly. They need to calculate the average transaction amount per merchant daily. The following query is used:

This query is run every hour and is performance-critical. Which of the following materialized view definitions would provide the BEST performance improvement, considering the need for near real-time data and minimal latency?

You have a Snowpark Python application that performs complex calculations on a large dataset stored in Snowflake. The application is currently running slowly. After profiling, you've identified that the UDFs you're using are the bottleneck. These UDFs perform custom data transformations using a third-party Python library which has a significant initialization overhead. Which of the following strategies would be MOST effective to optimize performance, minimizing both runtime and resource consumption?

You have a large dataset of JSON documents stored in AWS S3, each document representing a customer order. You want to ingest these documents into Snowflake using Snowpipe and transform the nested 'address' field into separate columns in your target table. Considering data volume, complexity, and cost efficiency, which approach is MOST suitable?

You are developing a data pipeline that extracts data from an on-premise PostgreSQL database, transforms it, and loads it into Snowflake. You want to use the Snowflake Python connector in conjunction with a secure method for accessing the PostgreSQL database. Which of the following approaches provides the MOST secure and manageable way to handle the PostgreSQL connection credentials in your Python script when deploying to a production environment?

A company is using Snowflake's web app interface to manage its data'. A data engineer needs to create a new table, load data into it from a CSV file stored in an internal stage, and then grant SELECT privileges on the table to a specific role using the web app. Which sequence of actions within the Snowflake web app represents the most efficient and secure way to accomplish this task?

You are designing a data pipeline in Snowflake that involves several tasks chained together. One of the tasks, 'task B' , depends on the successful completion of 'task A'. 'task_B' occasionally fails due to transient network issues. To ensure the pipeline's robustness, you need to implement a retry mechanism for 'task_B' without using external orchestration tools. What is the MOST efficient way to achieve this using native Snowflake features, while also limiting the number of retries to prevent infinite loops and excessive resource consumption? Assume the task definition for 'task_B' is as follows:

A healthcare provider wants to share patient data with a research organization, but must ensure that researchers only have access to records from a specific region ('REGION A') and only see anonymized data'. You have a 'patients' table with columns 'patient_id' , 'region', 'dob', 'medical history', and 'ssn'. Which of the following steps would be MOST effective and secure for implementing row- level filtering and data masking for this data sharing scenario, minimizing administrative overhead and maximizing query performance?

A data engineer is tasked with migrating data from a large on-premise Hadoop cluster to Snowflake using Spark. The Hadoop cluster contains nested JSON dat a. To optimize performance and minimize data transformation in Spark, what is the most efficient approach to read the JSON data into a Spark DataFrame and write it directly to a Snowflake table?

You are creating a Snowflake Listing to share data with multiple consumers. One consumer requires access to the complete dataset while other consumers need access to a subset of the data based on geographical region (e.g., only data related to the 'US'). You want to minimize data duplication and management overhead. Select all the valid ways to implement this using Snowflake Data Sharing features.

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?

You are designing a Snowpipe pipeline to ingest data from an AWS SQS queue. The queue contains notifications about new files arriving in an S3 bucket. However, due to network issues, some notifications are delayed, causing Snowpipe to potentially miss files. Which of the following strategies, when combined, will BEST address the problem of delayed notifications and ensure data completeness?

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?