DEA-C02試験問題集、DEA-C02練習テスト問題 [Q196-Q217]

DEA-C02試験問題集、DEA-C02練習テスト問題

PDF問題（2025年最新）実際のSnowflake DEA-C02試験問題

質問 # 196
You are designing a data sharing solution where the consumer account needs real-time access to a secure view that aggregates data from several tables in your provider account. The consumer should not be able to see the underlying tables. Which of the following approaches offers the MOST secure and efficient way to implement this data sharing while minimizing the risk of data leakage and performance impact on your provider account?

• A. Create a UDF that encapsulates the data aggregation logic and share the UDF's result using a data share, calling the UDF on demand.
• B. Create a standard view that joins the tables and share the view using a data share. Implement row-level security policies on the underlying tables.
• C. Create a shared database and grant SELECT privilege on the underlying tables directly to the consumer's role.
• D. Create a secure view that joins the tables and share only the secure view using a data share.
• E. Create a materialized view on top of the tables, refresh it periodically, and share the materialized view.

正解：D

解説：
Secure views are specifically designed for data sharing while protecting the underlying data sources. Sharing the secure view ensures that the consumer only sees the aggregated data and cannot access the underlying tables directly. Options A and D expose the underlying tables, increasing the risk of data leakage. Option C introduces latency due to the materialized view refresh. Option E adds unnecessary complexity and potential performance overhead.

質問 # 197
A data engineer notices that a daily ETL job loading data into a Snowflake table 'TRANSACTIONS' is consistently taking longer than expected. The table is append-only and partitioned by 'TRANSACTION DATE. The engineer observes high 'Remote Spill' during the load process and suspect that micro-partition pruning isn't working effectively. Which of the following approaches would BEST address the performance issue, assuming you have already considered increasing warehouse size?

• A. Implement data skipping by creating a masking policy on the 'TRANSACTION_DATE column.
• B. Examine the data load process to ensure the data is loaded in 'TRANSACTION_DATE order. If not, sort the data by 'TRANSACTION_DATE before loading.
• C. Partition the data in the source system by 'TRANSACTION DATE' and load data in parallel corresponding to each partition.
• D. Re-create the 'TRANSACTIONS' table with a larger virtual warehouse and re-load the entire dataset.
• E. Enable automatic clustering on the 'TRANSACTION_DATE column of the 'TRANSACTIONS table.

正解：B、E

解説：
Options A and E are the most appropriate. Automatic clustering (A) will reorganize the data to improve micro-partition pruning on 'TRANSACTION DATE', reducing the amount of data scanned and therefore reducing spillover. Loading the data in 'TRANSACTION DATE' order (E) ensures that data is naturally clustered as it is loaded, minimizing fragmentation and maximizing micro-partition pruning efficiency. Re-creating the table (B) is an extreme and unnecessary measure. Masking policies (C) are for data security, not performance optimization. Partitioning in the source system (D) might improve the data extraction process but won't directly address the micro-partition pruning issue within Snowflake if data isn't loaded in a sorted manner.

質問 # 198
You are tasked with building a data pipeline to process image metadata stored in JSON format from a series of URLs. The JSON structure contains fields such as 'image_url', 'resolution', 'camera_model', and 'location' (latitude and longitude). Your goal is to create a Snowflake table that stores this metadata along with a thumbnail of each image. Given the constraints that you want to avoid downloading and storing the images directly in Snowflake, and that Snowflake's native functions for image processing are limited, which of the following approaches would be most efficient and scalable?

• A. Create a Snowflake stored procedure that iterates through each URL, downloads the JSON metadata using 'SYSTEM$URL_GET, extracts the image URL from the metadata, downloads the image using 'SYSTEM$URL_GET , generates a thumbnail using SQL scalar functions, and stores the metadata and thumbnail in a Snowflake table.
• B. Store just the 'image_url' in snowflake. Develop a separate application using any programming language to pre generate the thumbnails and host those at publicly accessible URLs. Within Snowflake, create a view to generate the links for image and thumbnail using 'CONCAT.
• C. Create a Python-based external function that fetches the JSON metadata and image from their respective URLs. The external function uses libraries like PIL (Pillow) to generate a thumbnail of the image and returns the metadata along with the thumbnail's Base64 encoded string within a JSON object.
• D. Create a Snowflake external table that points to an external stage which holds the JSON metadata files. Develop a spark process to fetch image URL, create thumbnails and store as base64 encoded strings in an external stage, create a view using the external table and generated thumbnails data
• E. Create a Snowflake view that selects from a table containing the metadata URLs, using 'SYSTEM$URL GET to fetch the metadata. For each image URL found in the metadata, use a JavaScript UDF to generate a thumbnail. Embed the thumbnail into a VARCHAR column as a Base64 encoded string.

正解：B、C

解説：
Option C is the most appropriate solution. By using an external function with Python and libraries like PIL, you can efficiently handle image processing tasks that are difficult or impossible to perform natively within Snowflake. The external function encapsulates the image processing logic, keeping the Snowflake SQL code cleaner. Option E is also a valid solution as it leverages external processing. Option A is not performant as it tries to download image in snowflake which is not the best way to process image. Option B is not recommended because using JavaScript UDFs for binary data (images) can be inefficient. External Tables as described in Option D require pre-processing of data and storage to an external stage. Option D doesn't use the 'SYSTEM$URL GET' function that this question is trying to assess.

質問 # 199
You are a data engineer responsible for data governance in a Snowflake environment. Your company has implemented data classification using tags to identify sensitive data'. The compliance team has requested a report detailing all tables and columns that contain PII data, specifically including the tag name, tag value, the fully qualified name of the table, and the column name. You have the necessary privileges to access the Snowflake metadata views. Which of the following queries would provide the MOST comprehensive and accurate report, considering performance and ease of understanding?

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

正解：B

解説：
Option D provides the MOST comprehensive and accurate report. It directly queries the view, filtering for 'TAG_NAME = 'PII" and 'object_domain = 'COLUMN" to specifically target tags applied to columns. It selects the database, schema, table name, column name, tag name, and tag value, providing all the necessary information. Option A requires a JOIN between "snowflake.account_usage.columns' and , which is unnecessary for this use case and less efficient. option B is missing the OBJECT_DATABASE and OBJECT_SCHEMA which is needed to fully qualify the table. option C attempts to use a table function, which is unnecessary complexity and potentially less performant. Option E does not filter for column-level tags, potentially including tags applied to other object types (e.g., tables, views), leading to inaccurate results. The fully qualified name can be easily constructed from OBJECT DATABASE, OBJECT SCHEMA and OBJECT NAME.

質問 # 200

• A. The data types in the Lambda function and Snowflake function definition do not match. Specifically, the Lambda function expects strings while Snowflake is sending numbers and vice versa. Modify the Lambda function to handle numeric inputs and ensure the Snowflake function definition aligns with the expected output data type (FLOAT).
• B. The Lambda function returns the discount within a nested JSON structure Tdata': [[discount]]}'. The Snowflake function is not designed to handle this. The lambda function should return '{'data':
• C. The Snowflake external function is not correctly parsing the JSON response from the Lambda function. Implement a wrapper function in Snowflake to parse the JSON and extract the discount value before returning it.
• D. The Lambda function is returning a string instead of a number. Modify the Lambda function to return the discount as a number (e.g., 'discount = 0.15' instead of 'discount = '0.15")
• E. The 'RETURNS NULL ON NULL INPUT clause in the external function definition is causing the function to return NULL even when valid inputs are provided. Remove this clause.

正解：C

解説：
The most likely cause is (B). Snowflake expects the external function to return a single value directly convertible to the declared return type. The Lambda function is returning a JSON object that needs to be parsed. Snowflake needs a wrapper function to extract the numerical result from the json response. All other issues have been taken care of in the question and is not the cause of the problem.

質問 # 201
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. 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.
• B. 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.
• C. Schedule a daily full refresh of the 'CUSTOMER DATA' table from the source system, overwriting any potentially corrupted data.
• D. 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.
• 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.

正解：A、D、E

解説：
Options A, B, and D are the most effective. Option A provides a programmatic approach within Snowflake. Option B leverages Snowflake's built-in data quality capabilities (if available, check documentation for supported features and editions). Option D integrates with external specialized tools. Option C relies on manual monitoring, which is less proactive. Option E does not address the root cause of data quality issues and could potentially overwrite valid data with erroneous data.

質問 # 202
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?

• A. 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.
• B. 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.
• C. 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.
• D. Use Snowpipe with auto-ingest, create a single VARIANT column in your target table, and rely solely on Snowflake's automatic schema detection.
• E. 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.

正解：A

解説：
Option C is the most effective. Snowpipe provides continuous loading. A raw VARIANT column captures all data, and projecting commonly accessed fields into relational columns optimizes query performance. Search optimization on the projected columns allows for faster filtering and lookups. Options A, B, D, and E have trade-offs. A lacks optimized querying and can lead to expensive computations on the variant column. B requires pre-processing and may lose data fidelity. D impacts query performance due to runtime flattening. E introduces complexities with materialized view maintenance.

質問 # 203
Consider the following Snowflake SQL API call to execute a stored procedure:

• A. Include the stored procedure's fully qualified name (database.schema.procedure_name) in the 'statement' parameter.
• B. The stored procedure should handle the error handling for network disruptions and automatically retry.
• C. Set the parameter to and retrieve the result set directly from the API response.
• D. Use the parameter to specify which external functions are allowed to be called by the procedure.
• E. Set the 'warehouse' parameter in the SQL API request to ensure the stored procedure uses a specific warehouse size.

正解：A、C、E

解説：
A, B, and C are the most relevant.Setting 'wait_for result' allows direct retrieval. Explicitly setting the 'warehouse' parameter in the SQLAPI request ensures the stored procedure uses a specific warehouse size. The fully qualified name is best practice. D is incorrect because external access integrations are specified when creating the stored procedure, not within the SQLAPI call, unless you're altering a procedure that accepts it. E is incorrect, while a procedure can include error handling, it does not impact the actual API call.

質問 # 204
A data engineer is tasked with creating a Snowpark Python UDF to perform sentiment analysis on customer reviews. The UDF, named 'analyze_sentiment' , takes a string as input and returns a string indicating the sentiment ('Positive', 'Negative', or 'Neutral'). The engineer wants to leverage a pre-trained machine learning model stored in a Snowflake stage called 'models'. Which of the following code snippets correctly registers and uses this UDF?

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

正解：D

解説：
The most concise and recommended way to define a Snowpark UDF in Python is using the @F.udf decorator. This decorator automatically handles registration with Snowflake and simplifies the code. It also correctly specifies the 'return_type' , 'input_types' , and required packages'. Options A, B, C and E are either missing the decorator or have issues with specifying input types or session usage. The session.add_packageS is not a proper way to define packages used by UDFs and 'StringType' is not imported from 'snowflake.snowpark.types , so the correct way is to set return_type and input_types within the decorator.

質問 # 205
You are designing a Snowflake data pipeline that continuously ingests clickstream dat a. You need to monitor the pipeline for latency and throughput, and trigger notifications if these metrics fall outside acceptable ranges. Which of the following combinations of Snowflake features and techniques would be MOST effective for achieving this goal?

• A. Create a custom dashboard using a Bl tool that connects to Snowflake via JDBC/ODBC and visualizes data ingestion and processing metrics. Manually monitor the dashboard for anomalies.
• B. Rely on Snowflake's default resource monitors to track warehouse usage. If warehouse usage exceeds a certain threshold, assume there are performance issues and send a notification.
• C. Implement a combination of Snowflake Streams, Tasks, and external functions. Streams capture changes, Tasks process the changes, and external functions send notifications to a monitoring service when latency or throughput issues are detected.
• D. Use Snowflake's 'QUERY_HISTORY view to track query execution times and implement a scheduled task that queries this view, calculates latency and throughput, and sends email notifications using Snowflake's built-in email integration if thresholds are exceeded.
• E. Use Snowflake's Event Tables and Event Notifications to capture events related to data ingestion and processing. Configure alerts based on event patterns that indicate latency or throughput issues.

正解：C、E

解説：
Options B and D offer the most effective solutions. Option B provides a granular approach using Streams, Tasks, and external functions for real-time monitoring and notification. Option D leverages Event Tables and Event Notifications, enabling a reactive approach based on specific event patterns. Option A is less precise as it relies on query history, which may not accurately reflect real-time latency. Option C is too general. Option E requires manual monitoring, which is not ideal for continuous pipelines.

質問 # 206
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?

• A. 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.
• B. Manually unload data from the US region and load it into the EU region using SnowSQL. Automate this process using a scheduled task.
• C. 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.
• D. Create a database replica in the EU region and manually refresh it periodically using 'CREATE DATABASE AS CLONE'
• E. 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 .

正解：E

解説：
Option B is the most suitable because it utilizes Snowflake's replication groups, which provide automated and continuous replication with minimal downtime during failover. Option A requires manual intervention. Option C doesn't truly replicate the data; it provides access to it. Options D and E are inefficient and introduce significant latency.

質問 # 207
You have a Snowflake table 'orders_raw' with a VARIANT column named 'order detailS that contains an array of order items represented as JSON objects. Each object has 'item id', 'quantity' , and 'price'. You need to calculate the total revenue for each order. Which SQL statement efficiently flattens the array and calculates the total revenue using LATERAL FLATTEN and appropriate casting?

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

正解：C

解説：
Option E is the most efficient and correct. It uses 'LATERAL FLATTEN' to unnest the 'order_details' array. It then casts both the quantity' and 'price' fields to FLOAT, ensuring accurate calculations for total revenue. A, B and D are incorrect due to incorrect join syntax or function usage with lateral flatten, or improper datatypes. C doesn't properly flatten the array so it only accesses the first element.

質問 # 208
You are troubleshooting a slow-running query that joins a large fact table 'SALES DATA' (100 billion rows) with a smaller dimension table 'CUSTOMER DIM' (1 million rows) on 'CUSTOMER ID. Initial analysis shows that the query is spending significant time in the join operation. You suspect the issue lies with the join strategy being used by Snowflake. Which of the following actions are MOST likely to improve query performance and optimize the join?

• A. Ensure both 'SALES DATA' and 'CUSTOMER DIM' are clustered on 'CUSTOMER ID.
• B. Increase the virtual warehouse size and monitor for spillover to local disk. If spilling occurs, further increase the warehouse size.
• C. Analyze the query profile in Snowflake's web UI and identify if a broadcast join is occurring. If so, consider increasing session parameter (within limits) or re-designing the query to avoid the broadcast join.
• D. Convert the query to use a LATERAL FLATTEN function to pre-process the 'CUSTOMER_DIW table before the join.
• E. Ensure that the 'CUSTOMER_ID column in both tables has compatible datatypes and that no implicit type conversions are happening during the join. Also check cardinality of 'CUSTOMER_ID in the SALES DATA table.

正解：B、C、E

解説：
Options C, D, and E are the most effective. Increasing the warehouse size (C) provides more resources for the join. Analyzing the query profile for broadcast joins and adjusting 'AUTO BROADCAST JOIN SIZE (D) can prevent inefficient join strategies where the smaller table is unnecessarily broadcast to all nodes. Ensuring data type compatibility (E) prevents performance-impacting implicit conversions. While clustering on the join key (A) can help, it may not be sufficient on its own, especially for very large tables. LATERAL FLATTEN (B) is generally used for semi- structured data and is not relevant to this scenario. Understanding data skewness in 'SALES_DATX is important too. A highly skewed distribution of 'CUSTOMER_ID' could lead to hot spots.

質問 # 209
You are tasked with creating a Snowpark Python UDF that calculates the exponential moving average (EMA) of a time series dataset stored in a Snowflake table named 'SALES DATA'. The table has columns 'TIMESTAMP' (TIMESTAMP_NTZ) and 'SALES' (NUMBER). The EMA should be calculated for each product, identified by the 'PRODUCT ID' column. You want to optimize the calculation by using a Pandas DataFrame within the UDF and leveraging vectorized operations. Which of the following code snippets would be the MOST efficient and correct way to achieve this? Assume 'alpha' is a predefined float variable representing the smoothing factor.

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

正解：C

解説：
Option E correctly defines a UDF that accepts a JSON string as input. The input JSON string represents a group of sales records which are converted to a Pandas Dataframe using 'pd.read_json'. The 'ewm' function is then used to calculate the EMA efficiently. The result is serialized back into JSON and returned. Other options fail because they incorrectly define the UDF either in terms of the types of parameters or not properly loading the dataframe. Options A uses Sprocs which is not the best fit for this scenario as it is meant for Stored procedures, and Option B and C have the wrong input and output tyypes.

質問 # 210
You are using Snowpark Python to perform a complex data transformation involving multiple tables and several intermediate dataframes. During the transformation, an error occurs within one of the Snowpark functions, causing the entire process to halt. To ensure data consistency, you need to implement transaction management. Which of the following Snowpark DataFrameWriter options or session configurations would be MOST appropriate for rolling back the entire transformation in case of an error during the write operation to the final target table?

• A. Set the session parameter to 'TRUE and wrap the entire transformation within a 'try...except block, explicitly calling in the 'excepts block.
• B. Set the session parameter to 'TRUE to ensure all DDL operations are atomic and can be rolled back.
• C. Use True)' to automatically rollback the write operation if an error occurs during the write process.
• D. Use and manually track intermediate dataframes to delete them in case of failure.
• E. Wrap the entire transformation in a stored procedure and call 'SYSTEM$QUERY within the stored procedure's exception handler.

正解：A

解説：
Setting 'TRANSACTION_ABORT ON ERROR to 'TRUE ensures that any error will abort the transaction. Wrapping the code in a 'try...except' block allows you to catch the exception and explicitly call 'session.rollback()' to undo any changes made within the transaction. Option A is relevant to DDL operations, not general data transformations. Option B involves manual tracking, which is error-prone. Option D is not a valid Snowpark DataFrameWriter option. Option E, while potentially useful for cancelling queries, does not directly manage transaction rollback from within the Snowpark session.

質問 # 211
Which of the following statements are TRUE regarding Snowflake's Fail-safe mechanism and its relation to Time Travel? (Select all that apply)

• A. The Fail-safe period starts immediately after the Time Travel retention period ends.
• B. Fail-safe is automatically enabled for all Snowflake accounts and requires no configuration.
• C. Users can query data directly from Fail-safe using SQL commands if Time Travel is insufficient.
• D. Fail-safe is exclusively used by Snowflake to recover data in the event of a catastrophic system failure, and users have no direct access.
• E. Fail-safe provides a historical data retention period of 7 days, similar to the default Time Travel setting.

正解：A、B、D

解説：
Fail-safe is automatically enabled and managed by Snowflake (B). It kicks in after Time Travel (C) and is not directly accessible to users (E). Users cannot query data from Fail-safe using SQL commands. Fail-safes duration depends on the Snowflake Edition but not for the same days as time travel.

質問 # 212
You are developing a data pipeline that uses Snowpipe Streaming to ingest JSON data into a Snowflake table. Some JSON documents contain nested arrays and complex structures. You need to flatten the JSON structure during ingestion to simplify querying. Consider the following JSON document: { "order id": 123, "customer": { "id": "cust123", "name": "John Doe", "address": { "street": "123 Main St", "city": "Anytown" } }, "items": [ {"product_id": "prodl", "quantity": 2}, {"product_id": "prod2", "quantity": 1} ] } Which approach would you use within the 'COPY INTO' statement of your Snowpipe to flatten this JSON structure during ingestion?

• A. Use the ' FLATTEN()' table function directly within the 'COPY INTO' statement to expand the 'items' array and extract nested fields. For nested objects, use dot notation directly in the SELECT list (e.g., 'customer.name').
• B. Create a separate transformation pipeline using Snowflake Tasks to flatten the data after it is ingested into the table.
• C. Use JavaScript UDFs within the 'COPY INTO' statement to recursively flatten the JSON structure.
• D. Snowpipe and the 'COPY INTO' command automatically flattens JSON data during ingestion; no additional steps are required.
• E. Pre-process the JSON documents before loading them into the stage using a custom script to flatten the structure.

正解：A

解説：
Snowflake's 'FLATTEN()' function, combined with dot notation for nested objects, provides the most efficient way to flatten JSON data during ingestion within the 'COPY INTO' statement. Options B, C, and D introduce unnecessary complexity and latency. Snowpipe does NOT automatically flatten JSON (E).

質問 # 213
A data engineering team is managing a Snowflake warehouse that supports a high volume of ad-hoc queries from data analysts exploring a large, semi-structured JSON dataset containing website clickstream data'. The query performance is frequently slow, and analysts are complaining about long wait times. The warehouse is already sized appropriately. You have identified that many of the queries filter on nested JSON attributes that are not explicitly indexed. Considering only query acceleration service features, what is the MOST effective approach to improve query performance for these ad-hoc queries without modifying the queries themselves or significantly increasing storage costs?

• A. Create a dedicated virtual warehouse specifically for ad-hoc queries, and enable query acceleration on this warehouse.
• B. Use a combination of materialized views and query acceleration for best performance.
• C. Enable Automatic Clustering on the table to improve data organization based on query patterns.
• D. Enable the Materialized View feature to create materialized views over the frequently queried nested JSON attributes.
• E. Enable Search Optimization Service for the table containing the JSON data. Selectively enable search optimization on the specific columns and nested paths that are frequently used in WHERE clause predicates.

正解：E

解説：
Search Optimization Service is designed specifically to improve query performance on semi-structured data and complex predicates, especially on JSON data. It automatically creates and maintains search access paths, including paths for nested JSON attributes, enabling faster filtering and retrieval of relevant data. Materialized Views are beneficial, but require creation and maintenance, and might not be ideal for ad-hoc queries. Automatic Clustering helps with data organization, but its impact on complex JSON queries might be limited. Using Query Acceleration alone requires a larger warehouse and may not address the underlying issue of unoptimized queries on semi-structured data. While a dedicated warehouse is a good practice, it does not address the underlying performance issue related to JSON queries.

質問 # 214
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?

• A. Bypass the external function completely and rely solely on Snowflake's built-in NLP functions for sentiment analysis.
• B. Set 'MAX BATCH_ROWS' to a very high value (e.g., 10000) to maximize the number of rows processed per API call, even if it increases latency for individual reviews.
• C. Implement rate limiting and error handling in the external service (e.g., AWS Lambda or Azure Function) to gracefully handle API usage limits and prevent excessive charges due to errors.
• D. Implement a caching mechanism (e.g., using a Snowflake table or an external cache) to store the sentiment analysis results for frequently occurring reviews or similar text patterns, avoiding redundant API calls.
• E. Pre-process the customer reviews in Snowflake to filter out irrelevant reviews (e.g., very short reviews or reviews with stop words) before sending them to the external function.

正解：C、D、E

解説：
The correct answers are B, C, and D. Option B avoids redundant API calls by caching results. Option C handles API rate limits to prevent excessive charges. Option D reduces the number of API calls by pre-filtering irrelevant reviews. Option A will increase latency and is not a cost optimization. Option E is not always feasible if Snowflake's built-in functions are insufficient.

質問 # 215
You are implementing row access policies on a 'SALES DATA table to restrict access based on the 'REGION' column. Different users are allowed to see data only for specific regions. You have a mapping table 'USER REGION MAP' with columns 'USERNAME' and 'REGION'. You want to create a row access policy that dynamically filters the 'SALES DATA' based on the user and their allowed region. Which of the following options represents a correct approach to create and apply this row access policy?

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

正解：D

解説：
Option B is the correct approach. It creates a row access policy that checks if a row exists in the where the username matches the current user and the region matches the 'REGION' column in the 'SALES_DATR table. 'ADD ROW ACCESS POLICY is the correct command to apply the policy. Options A is incorrect as it uses IN clause, which can become inefficient with large datasets. Option C uses 'SET' which is not a valid operation, and Option D uses 'MODIFY which is used for masking policy and not row access policy. Option E uses 'CURRENT ROLE instead of 'CURRENT USER' which is not the appropriate filter criteria.

質問 # 216
You have a table named 'sales_data' with columns 'region', 'product_category', and 'revenue'. You want to create an aggregation policy to prevent users without the 'FINANCE ADMIN' role from seeing revenue values aggregated across all regions. Instead, these users should only see revenue aggregated at the region level. The policy should return NULL for the 'revenue' column when aggregated across all regions by non-admin users. Which of the following SQL snippets correctly implements this aggregation policy?

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

正解：D

解説：
Option B correctly uses the GROUPING() function to identify when the aggregation is being performed across all regions (GROUPING(region) = 1). It then returns NULL for non-FINANCE_ADMIN users in this scenario. Options A is incorrect because grouping(region) = 0 means region-level aggregation. Options C does not consider regions in the policy, so non-admin users will always see null revenue. Option D's syntax is incorrect. must be combined with GROUPING as in E to work correctly. Option E can also work but the CASE statement is clearer and easier to understand.

質問 # 217
......

更新された2025年11月合格させるDEA-C02試験リアル練習テスト問題：https://www.passtest.jp/Snowflake/DEA-C02-shiken.html

問題集返金保証付きのDEA-C02問題集には90%オフ：https://drive.google.com/open?id=1zGCKaYqdPqb7RIVRJGuWiOTlII7SRtgj