Free Snowflake DEA-C02 Practice Test & Real Exam Questions

You are using the Snowflake REST API to insert data into a table named 'RAW JSON DATA. The JSON data is complex and nested, and you want to efficiently parse and flatten it into a relational structure. You have the following JSON sample:

Which SQL statement, executed after loading the raw JSON using the REST API, is the MOST efficient way to flatten the JSON and extract relevant fields into a new table named 'PURCHASES' with columns like 'EVENT TYPE', 'USER D', 'EMAIL', 'STREET, 'CITY', 'ITEM ID', and 'PRICE'?

A Snowflake data pipeline utilizes Snowpipe to ingest JSON data from cloud storage into a raw staging table 'RAW DATA' Subsequently, a series of transformation tasks are executed to cleanse, transform, and load the data into fact and dimension tables. You've noticed significant performance degradation in the transformation tasks, especially when dealing with large JSON payloads and deeply nested structures. Which of the following optimization techniques, applied at different stages of the pipeline, would MOST likely improve the overall performance of the data transformation tasks?

You are working with a Snowflake table 'customer_data' which contains customer information stored in a VARIANT column named raw_info'. The 'raw_info' JSON structure includes nested addresses, and preferences. Your task is to extract the city from the first address in the 'addresses' array, and the customer's preferred communication method from the 'preferences' object. Some customers might not have addresses or preferences defined. Select the two SQL snippets that correctly and efficiently extract this data, handling missing fields gracefully and providing appropriate type casting. Address array is in the format 'addresses: [ { 'city': '...', 'state': ' '},

You are designing a data product for the Snowflake Marketplace that provides daily weather forecasts. You need to ensure that consumers of your data receive the latest forecast data every morning automatically with minimal latency. Which of the following strategies offers the MOST efficient and cost-effective solution for updating the shared data?

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'?

Consider a scenario where you have a Snowflake table named 'CUSTOMER DATA' containing customer IDs (INTEGER) and encrypted credit card numbers (VARCHAR). You need to create a secure JavaScript UDF to decrypt these credit card numbers using a custom encryption key stored securely within Snowflake's internal stage, and then mask all but the last four digits of the decrypted number for data protection. Which of the following actions are necessary to ensure both functionality and security while adhering to Snowflake's best practices for UDF development and security?

A data provider wants to share a large dataset (several TB) with multiple consumers. The dataset is updated daily. The provider wants to minimize the cost associated with data sharing and ensure that consumers receive consistent data'. Which of the following strategies would be the MOST cost-effective and maintainable?

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?

You are using the Snowflake Spark connector to update records in a Snowflake table based on data from a Spark DataFrame. The Snowflake table 'CUSTOMER' has columns 'CUSTOMER ID' (primary key), 'NAME, and 'ADDRESS'. You have a Spark DataFrame with updated 'NAME and 'ADDRESS' values for some customers. To optimize performance and minimize data transfer, which of the following strategies can you combine with a temporary staging table to perform an efficient update?

You have an external table named in Snowflake that points to a set of CSV files in an AWS S3 bucket. The CSV files have a header row, and the data is comma-separated. However, some of the files in the S3 bucket are gzipped. You need to define the external table to correctly read both compressed and uncompressed files. Which of the following SQL statements BEST achieves this?

You're loading data into a Snowflake table using 'COPY INTO'. You notice that some rows are being rejected due to data validation errors (e.g., data type mismatch, uniqueness constraint violations). You want to implement a strategy to capture these rejected rows for further analysis and correction. Which of the following approaches offers the MOST efficient and reliable method for capturing and storing the rejected rows, minimizing performance impact during the data loading process? Assume no staging table exists before loading data to production table.

You are working with a directory table named associated with an external stage containing a large number of small JSON files. You need to process only the files containing specific sensor readings based on a substring match within their filenames (e.g., files containing 'temperature' in the filename). You also want to load these files into a Snowflake table 'sensor_readings. Consider performance and cost-effectiveness. Which of the following approaches is the MOST efficient and cost-effective to achieve this? Choose TWO options.

You are designing a data pipeline in Snowflake to process IoT sensor data'. The data arrives in JSON format, and you need to extract specific nested fields using a Snowpark UDF for performance reasons. Which of the following statements are true regarding best practices and limitations when working with complex JSON data and Snowpark UDFs (Python or Scala)? (Select all that apply)

You are the provider of a data product on the Snowflake Marketplace. You need to grant a trial access to a potential consumer You want to provide limited access for 7 days to specific tables in your database. Which of the following steps are REQUIRED to accomplish this?
(Select all that apply)