最新的SnowPro Advanced認證 DEA-C02考試題庫

問題1

You have a Snowpark DataFrame 'df_products' with columns 'product id', 'category', and 'price'. You need to perform the following transformations in a single, optimized query using Snowpark Python: 1. Filter for products in the 'Electronics' or 'Clothing' categories. 2. Group the filtered data by category. 3. Calculate the average price for each category. 4. Rename the aggregated column to 'average_price'. Which of the following code snippets demonstrates the most efficient way to achieve this?

A. Option B

B. Option D

C. Option C

D. Option A

E. Option E

正確答案: A

說明：（僅 VCESoft 成員可見）

問題2

A Snowflake table, contains product information in a VARIANT column named This column holds JSON structures. You need to create a view, , that exposes specific fields , and 'category') as structured columns, and should gracefully handle scenarios where may contain characters incompatible with VARCHAR, 'category' is nested inside an array called 'tags'. What is the BEST and the MOST robust approach?

A. Option B

B. Option D

C. Option C

D. Option A

E. Option E

正確答案: E

說明：（僅 VCESoft 成員可見）

問題3

A Snowflake table 'ORDERS' is clustered on the 'ORDER DATE column. After several months, you notice that many micro-partitions contain data from a wide range of 'ORDER DATE values, and query performance on date range filters is degrading. Which of the following actions could improve performance and reduce the overlap in micro-partitions?

A. Increase the virtual warehouse size used for loading data into the "ORDERS' table.

B. Run 'OPTIMIZE TABLE ORDERS' to recluster the table.

C. Drop and recreate the table with a different clustering key.

D. Change the ORDER DATE column to VARCHAR and then cluster.

E. Change the clustering key to a composite key including ORDER DATE and another relevant column.

正確答案: B,E

說明：（僅 VCESoft 成員可見）

問題4

You have a table 'EVENTS' containing application event data with columns 'EVENT ID, 'USER ID, 'EVENT TYPE, and EVENT DETAILS (VARCHAR). The 'EVENT DETAILS column contains comma-separated key-value pairs (e.g., 'location=USA,device=mobile,os=iOS'). Your objective is to transform this structured data into a VARIANT column named EVENT JSON' in a new table 'EVENTS JSON'. The data in EVENT DETAILS has inconsistent key-value pairs across different rows. Which of the following methods are the most efficient and scalable to parse the key-value pairs in 'EVENT DETAILS' and construct the JSON objects?

A. Use 'SPLIT to split the key-value pairs into an array, then use a LATERAL FLATTEN to create rows from array, then use 'SPLIT again to split each row by '='. Finally, construct the JSON using 'OBJECT CONSTRUCT.

B. Utilize to split the key-value pairs into rows, then use 'REGEXP_EXTRACT to extract the key and value. Finally, use 'OBJECT_CONSTRUCT and to construct the JSON object.

C. Use a Java UDF that iterates through the string, splitting it based on commas and equals signs, and then constructs a JSON object using a JSON library.

D. Use a combination of 'SPLIT, 'REGEXP_REPLACE and 'OBJECT_CONSTRUCT within a user-defined function (UDF) to parse the string and build the JSON object.

E. Use only REGEXP EXTRACT ALL' with appropriate regular expressions to extract all keys and values into arrays, then use a JavaScript UDF to combine them into a JSON object.

正確答案: A,B

說明：（僅 VCESoft 成員可見）

問題5

You are tasked with designing a solution to load semi-structured data (JSON) from an AWS S3 bucket into a Snowflake table using Snowpipe and the REST API. The data in S3 is constantly being updated, and you need to ensure that only new or modified files are loaded into Snowflake. Which of the following steps are essential for implementing an efficient and cost-effective solution?

A. Create a Snowflake external function that polls the S3 bucket every minute, checks for new files using the LIST command, and then calls the Snowpipe REST API endpoint for each new file.

B. Use the 'VALIDATION MODES copy option with 'RETURN_ALL RESULTS = TRUE to validate all data being loaded into the Snowflake table.

C. Configure an S3 event notification to trigger a REST API call to the Snowpipe endpoint whenever a new or modified file is added to the S3 bucket. The API call should include the file name in the request.

D. Configure Snowpipe to automatically detect new files in the S3 bucket using event notifications, but manually refresh the pipe using SYSTEM $PIPE STATUS periodically to ensure that all files are processed.

E. Configure auto-ingest using SQS queue and SNOWPIPE object. No need to manually call the REST API endpoint for data loading.

正確答案: C,E

說明：（僅 VCESoft 成員可見）

問題6

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?

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

B. 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).

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

D. 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.

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

正確答案: A

說明：（僅 VCESoft 成員可見）

問題7

You are tasked with building a data pipeline using Snowpark to process sensor data from IoT devices. The data arrives in near real-time as JSON payloads, and you need to transform and load it into a Snowflake table named 'SENSOR DATA'. The transformation logic involves extracting specific fields, converting data types, and filtering out records based on a timestamp. Consider performance optimization for large data volumes. Which of the following approaches, in combination, would be MOST efficient for this scenario?

A. Creating an external table pointing to the JSON data in cloud storage and using Snowpark DataFrames to read the external table, apply transformations, and load the result into 'SENSOR DATA'.

B. Using a Snowpark Python UDF to parse JSON and perform transformations, loading the result into a temporary table, and then merging into 'SENSOR DATA'.

C. Employing Snowpipe to ingest the raw JSON data into a VARIANT column in a staging table, followed by a Snowpark DataFrame operation using 'functions.get' to extract and transform the data, and finally loading into 'SENSOR DATA'

D. Leveraging Snowflake's native JSON parsing functions within a SQL transformation step implemented as a Snowpark DataFrame operation, combined with a Snowpipe for initial data ingestion into a staging table.

E. Using a stored procedure written in Java to parse the JSON data and insert directly into the "SENSOR DATA' table.

正確答案: C,D

說明：（僅 VCESoft 成員可見）

問題8

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?

A. Write the Spark DataFrame to a temporary table in Snowflake using MERGE. Use the WHEN MATCHED clause for Update the target table based on updates from staging table and finally drop the staging table

B. Write the Spark DataFrame to a temporary table in Snowflake. Then, execute an 'UPDATE statement in Snowflake joining the temporary table with the 'CUSTOMER table using the 'CUSTOMER_ID to update the 'NAME and 'ADDRESS' columns. Finally, drop the temporary table.

C. Use Spark's foreachPartition to batch update statements and execute on each partition. This will help with efficient data transfer and avoid single row based updates.

D. Iterate through each row in the Spark DataFrame and execute an individual 'UPDATE statement against the 'CUSTOMER table in Snowflake. Use the 'CUSTOMER_ID in the 'WHERE clause.

E. Broadcast the Spark DataFrame to all executor nodes, then use a UDF to execute the 'UPDATE' statement for each row directly from Spark.

正確答案: A,B

說明：（僅 VCESoft 成員可見）

問題9

You are tasked with optimizing a data pipeline that loads data from an external cloud storage location into Snowflake, transforms it, and then loads it into reporting tables. The pipeline is experiencing intermittent performance issues. You want to proactively identify and address these issues. Which of the following monitoring techniques and Snowflake features would be MOST effective for continuous monitoring and performance optimization?

A. Enable Snowflake's Auto-Suspend and Auto-Resume features on the warehouse. This is the most efficient way to manage resources and optimize costs, indirectly addressing performance concerns.

B. Utilize Snowflake's System Functions to periodically query performance views (e.g., 'QUERY_HISTORY, ' and write aggregated metrics to a dedicated monitoring table. Configure a scheduled task to generate alerts based on predefined thresholds.

C. Rely solely on Snowflake's default query history and resource monitors. These automatically track performance and usage, providing sufficient insight without additional configuration.

D. Implement custom logging and monitoring using Snowflake Scripting and User-Defined Functions (UDFs) to capture granular performance metrics at each stage of the pipeline and push notifications via external functions to a monitoring service.

E. Focus exclusively on optimizing SQL queries and data transformations. Monitoring is unnecessary since Snowflake automatically handles performance optimization.

正確答案: B,D

說明：（僅 VCESoft 成員可見）

問題10

You are tasked with designing a data pipeline to load data from an Azure Blob Storage container into Snowflake using an external stage. The data is in CSV format, compressed using GZIP. The container contains millions of small CSV files. To optimize the data loading process and minimize cost, which of the following strategies would you implement, considering both stage configuration and COPY INTO options? Choose TWO that apply.

A. Create a pipe object with 'AUTO INGEST = TRUE to automatically ingest new files as they are added to the Azure Blob Storage container. This ensures near real-time data ingestion.

B. Use the 'MATCH BY COLUMN NAME = CASE INSENSITIVE option with a copy transformation in the 'COPY INTO' statement to ensure that the column order in the CSV files doesn't affect the data load.

C. Consolidate the small CSV files in the Azure Blob Storage container into larger files before loading them into Snowflake. This reduces the overhead of processing numerous small files.

D. Leverage Snowflake's Snowpipe with a REST API endpoint to trigger data loads whenever new files are available in the Azure Blob Storage container.

E. Use the 'VALIDATION MODE = RETURN ERRORS option in the 'COPY INTO' statement to identify and correct any data quality issues during the load. This ensures that only clean data is loaded into Snowflake.

正確答案: C,D

說明：（僅 VCESoft 成員可見）

問題11

You are responsible for monitoring data quality in a Snowflake data warehouse. Your team has identified a critical table, 'CUSTOMER DATA, where the 'EMAIL' column is frequently missing or contains invalid entries. You need to implement a solution that automatically detects and flags these anomalies. Which of the following approaches, or combination of approaches, would be MOST effective in proactively monitoring the data quality of the 'EMAIL' column?

A. Implement a Streamlit application connected to Snowflake that visualizes the percentage of NULL and invalid 'EMAIL' values over time, allowing the team to manually monitor trends.

B. Utilize an external data quality tool (e.g., Great Expectations, Deequ) to define and run data quality checks on the 'CUSTOMER DATA' table, integrating the results back into Snowflake for reporting and alerting.

C. Create a Snowflake Task that executes a SQL query to count NULL 'EMAIL' values and invalid 'EMAIL' formats (using regular expressions). The task logs the results to a separate monitoring table and alerts the team if the count exceeds a predefined threshold.

D. Schedule a daily full refresh of the 'CUSTOMER DATA' table from the source system, overwriting any potentially corrupted data.

E. Use Snowflake's Data Quality features (if available) to define data quality rules for the 'EMAILS column, specifying acceptable formats and thresholds for missing values. Configure alerts to be triggered when these rules are violated.

正確答案: B,C,E

說明：（僅 VCESoft 成員可見）

問題12

You are designing a data sharing solution in Snowflake where a provider account shares a view with a consumer account. The view is based on a table that undergoes frequent DML operations (inserts, updates, deletes). The consumer account needs to see a consistent snapshot of the data, even during these DML operations. Which of the following strategies, or combination of strategies, would be MOST effective in ensuring data consistency from the consumer's perspective, and what considerations should be made?

A. Creating a standard view in the provider account and relying on Snowflake's inherent transactional consistency. The consumer account will always see a consistent snapshot of the data as it existed at the beginning of their query execution. No additional configurations are necessary.

B. Creating a stream on the base table in the provider account and building a view on top of the stream. This way, changes are only reflected when the stream is consumed, allowing for batch processing and controlled updates in the consumer account.

C. Creating a materialized view in the provider account and sharing that materialized view. This adds compute costs to the provider but ensures a consistent snapshot for the consumer account. The materialized view needs to be refreshed periodically, based on the rate of DML changes.

D. A and B

E. Using Snowflake's Time Travel feature by querying the view with a specific 'AT' or 'BEFORE' clause in the consumer account. The provider account needs to inform the consumer account of a specific timestamp that guarantees consistency, adding administrative overhead.

正確答案: A

說明：（僅 VCESoft 成員可見）

問題13

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.

A. Option B

B. Option D

C. Option C

D. Option A

E. Option E

正確答案: E

說明：（僅 VCESoft 成員可見）

問題14

You have a Snowflake table named 'ORDERS clustered on 'ORDER DATE. After a significant data load, you want to evaluate the effectiveness of the clustering. Which of the following SQL queries, using Snowflake system functions, will provide insights into the clustering depth and overlap of micro-partitions in the 'ORDERS' table, specifically helping you identify whether re-clustering is necessary? Assume that the table

A.

B.

C.

D.

E.

正確答案: E

說明：（僅 VCESoft 成員可見）

最新的Snowflake SnowPro Advanced: Data Engineer (DEA-C02) - DEA-C02免費考試真題

最新更新

站內鏈接

聯系我們