Passing exam with DEA-C02 latest training materials

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. The table is already partitioned by 'event_time' , so there is no need for further optimization.
• B. Add a search optimization strategy to the table on the 'event_time' column.
• C. Change the table to use clustering on 'event_time' instead of partitioning to improve query performance for range filters.
• D. Increase the warehouse size to improve query performance.
• E. Create a materialized view that pre-aggregates the 'status_code' by hour to speed up the aggregation query.

A data pipeline ingests clickstream data from various sources into a raw Snowflake table CRAW CLICKS). A transformation job then processes this data and loads it into a more structured 'CLICK EVENTS table, performing filtering, cleaning, and data enrichment. The data engineering team notices significant performance bottlenecks during this transformation process, leading to data freshness issues.
The team wants to optimize this process, considering the following:

• 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. Optimize the transformation queries by identifying and rewriting inefficient SQL patterns, ensuring appropriate use of joins, filtering conditions, and data type conversions.
• C. 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.
• 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. 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.

You are tasked with loading Parquet files into Snowflake from an AWS S3 bucket. The Parquet files are compressed using Snappy compression and contain a complex nested schem a. Some of the columns contain timestamps with nanosecond precision. You want to create a Snowflake table that preserves the timestamp precision. Which COPY INTO statement options and table definition are MOST appropriate?

• A. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE - - pARQUET COMPRESSION = AUTO) ON_ERROR = 'SKIP_FILE';
• B. Table Definition: CREATE TABLE my_table (ts VARCHAR, other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE' = PARSE TIMESTAMP(ts));
• C. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE' VALIDATION_MODE = RETURN_ERRORS;
• D. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE';
• E. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ, other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE';

You are setting up a Kafka connector to load data from a Kafka topic into a Snowflake table. You want to use Snowflake's automatic schema evolution feature to handle potential schema changes in the Kafka topic. Which of the following is the correct approach to enable and configure automatic schema evolution using the Kafka Connector for Snowflake?

• A. Set the property to 'true' and the 'snowflake.ingest.stage' to an existing stage.
• B. Set the 'snowflake.data.field.name' property to the name of the column in the Snowflake table where the JSON data will be stored as a VARIANT, and set 'snowflake.enable.schematization' to 'true'.
• C. Set the 'value.converter.schemas.enable' to 'true' and provide Avro schemas and also, configure the Snowflake table with appropriate data types for each field. Schema Evolution is not supported by the Kafka Connector for Snowflake.
• D. Set 'snowflake.ingest.file.name' to an existing file in a stage.
• E. Automatic schema evolution is not directly supported by the Kafka Connector for Snowflake. You must manually manage schema changes in Snowflake.

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?

• A. Option E
• B. Option B
• C. Option C
• D. Option A
• E. Option D