DEA-C02試験無料問題集（354題）「Snowflake SnowPro Advanced: Data Engineer (DEA-C02) 認定」

出題：1

You are tasked with implementing row-level filtering on a 'customers' table in Snowflake. You need to restrict access to customer data based on the user's region. The 'customers' table has a 'region' column, and you have a mapping table 'user regions' that associates users with the regions they are allowed to access. The 'user_regions' table has columns 'username' and 'region'. Which of the following SQL statements correctly creates and applies a row access policy to achieve this, minimizing complexity and maximizing performance? Select all that apply:

A. Option E

B. Option D

C. Option C

D. Option A

E. Option B

正解：A,C 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：2

A Snowflake table 'CUSTOMER ORDERS is clustered by 'ORDER DATE. You have observed the clustering depth increasing over time, impacting query performance. To improve performance, you decide to recluster the table. However, you need to minimize the impact on concurrent DML operations and cost. Which of the following strategies would be MOST effective in managing this reclustering process?

A. Create a new table clustered by 'ORDER_DATE, copy data in parallel, and then swap tables.

B. Implement a continuous reclustering process using Snowpipe to automatically recluster new data as it arrives.

C. Use 'CREATE OR REPLACE TABLE with SELECT FROM CUSTOMER ORDERS to rebuild the table with optimized clustering.

D. Leverage Snowflake's automatic reclustering feature, monitor its performance, and adjust warehouse size as needed.

E. Recluster the entire table in a single transaction during off-peak hours.

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：3

A data engineer is investigating high credit consumption on a Snowflake warehouse due to frequent re-clustering operations on a large table named 'WEB EVENTS. This table is clustered on 'EVENT TIMESTAMP' and 'USER ID. The engineer suspects that the high frequency of data ingestion, especially out-of-order 'EVENT TIMESTAMP' values, contributes to the poor clustering. Choose the options that can lead to optimizing clustering and reducing credit consumption, assuming you have limited control over the ingestion process and data quality.

A. Implement a maintenance task to periodically re-cluster the table less frequently, but at more strategically chosen times (e.g., during off-peak hours).

B. Drop the clustering key altogether to avoid re-clustering costs.

C. Implement a pre-processing stage to sort the incoming data by 'EVENT TIMESTAMP before loading it into the 'WEB EVENTS table, using a temporary table and then inserting into the final table.

D. Partition the table based on "EVENT _ TIMESTAMP' instead of clustering.

E. Increase the warehouse size to accelerate the re-clustering process.

正解：A,C 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：4

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?

A. Configure the SQS queue with a longer retention period and implement an event bridge rule with a retry policy to resend notifications.

B. Implement a Lambda function that triggers the 'SYSTEM$PIPE FORCE RESUME procedure after a certain delay.

C. Set 'MAX FILE_AGE to 'DEFAULT' and utilize the 'SYSTEM$PIPE FORCE RESUME' procedure in conjunction with a separate process that lists the S3 bucket and compares it to the files already loaded in Snowflake, loading any missing files.

D. Increase the 'MAX FILE AGE parameter in the Snowpipe definition and implement a periodic 'ALTER PIPE ... REFRESH' command.

E. Use 'VALIDATE()' function periodically to identify files that have not been loaded and trigger manual data loads for missing data.

正解：C 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：5

You are tasked with optimizing a Snowpipe Streaming pipeline that ingests data from Kafka into a Snowflake table named 'ORDERS' You notice that while the Kafka topic has high throughput, the data ingestion into Snowflake is lagging. The pipe definition is as follows: "sql CREATE OR REPLACE PIPE ORDERS_PIPEAS COPY INTO ORDERS FROM @KAFKA STAGE FILE_FORMAT = (TYPE = JSON); Which of the following actions, taken individually, would be MOST effective in improving the ingestion rate, assuming sufficient compute resources are available in your Snowflake virtual warehouse?

A. Implement batching within the Kafka producer to send larger messages.

B. Increase the size of the virtual warehouse associated with the Snowflake account.

C. Tune the parameter in the file format definition to a smaller value.

D. Increase the number of Kafka partitions and ensure Snowflake has enough compute to consume them in parallel using Snowpipe Streaming.

E. Enable auto-ingest for the Snowpipe.

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：6

You have implemented a masking policy on the 'SSN' column of the 'EMPLOYEES' table. You now need to suspend the masking policy temporarily for a specific batch job that requires access to the unmasked data'. What is the recommended way to achieve this without dropping the masking policy or altering the user's role?

A. Create a temporary view on the 'EMPLOYEES' table without the 'SSN' column, grant access to the view for the batch job, and drop the view after the job is complete.

B. Grant the 'APPLY MASKING POLICY privilege to the user running the batch job, allowing them to bypass the masking policy.

C. Create a new role with 'ACCOUNTADMIN' privileges, assign this role to the batch job process during the execution, and then revert back to the original role after the job is done.

D.

E. Set the 'DISABLE MASKING' session parameter to 'TRUE' for the batch job session. This will temporarily disable all masking policies.

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：7

You are managing a Snowflake environment where data retention is set to the default 1 day for all databases and tables. You need to clone a production table, 'CUSTOMER DATA, to a development environment to test some complex transformations. However, after cloning, you realize that the original 'CUSTOMER DATA' table in production was accidentally dropped 2 days ago. Which of the following statements accurately describe the situation and what can be done, if anything?

A. The cloned 'CUSTOMER DATA' table in the development environment will remain accessible because the clone operation created a point-in-time snapshot.

B. The cloned 'CUSTOMER DATA' table in the development environment will no longer be accessible because the source table has been dropped and the data retention period has expired. The clone is effectively useless.

C. The cloned 'CUSTOMER DATA' table will only remain accessible if the source database was also cloned at the same time. If only the table was cloned, the cloned table is unusable after 1 day.

D. While the source 'CUSTOMER DATA' is dropped and outside the retention period, the clone created will remain available and fully functional for querying. It is an independent copy. The clone is usable indefinitely, within the limits of your Snowflake account storage.

E. The cloned 'CUSTOMER DATA' table in the development environment will inherit the data retention period from the original 'CUSTOMER DATA' table, so it will also be inaccessible after 1 day if dropped in development.

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：8

You have an external table in Snowflake pointing to data in Azure Blob Storage. The data consists of customer transactions, and new files are added to the Blob Storage daily You want to ensure that Snowflake automatically picks up these new files and reflects them in the external table without manual intervention. However, you are observing delays in Snowflake detecting the new files. What are the potential reasons for this delay and how can you troubleshoot them? (Choose two)

A. Snowflake's internal cache is not properly configured; increasing the cache size will solve the problem.

B. The Azure Event Grid notification integration is not properly configured to notify Snowflake about new file arrivals in the Blob Storage.

C. The file format used for the external table is incompatible with the data files in Blob Storage.

D. The storage integration associated with the external table does not have sufficient permissions to access the Blob Storage.

E. The external table's 'AUTO_REFRESH' parameter is set to 'FALSE', which disables automatic metadata refresh.

正解：B,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：9

You are developing a data pipeline in Snowflake that uses SQL UDFs for data transformation. You need to define a UDF that calculates the Haversine distance between two geographical points (latitude and longitude). Performance is critical. Which of the following approaches would result in the most efficient UDF implementation, considering Snowflake's execution model?

A. Create an External Function (using AWS Lambda or Azure Functions) to calculate the Haversine distance. This allows for offloading the computation to a separate compute environment.

B. Create a SQL UDF that pre-calculates the RADIANS for latitude and longitude only once and stores them in a temporary table, using those values for subsequent distance calculations within the same session.

C. Create a SQL UDF leveraging Snowflake's VECTORIZED keyword, hoping to automatically leverage SIMD instructions, without any code changes to mathematical calculation inside the UDF

D. Create a Java UDF that calculates the Haversine distance, leveraging optimized mathematical libraries. This allows for potentially faster execution due to lower- level optimizations.

E. Create a SQL UDF that directly calculates the Haversine distance using Snowflake's built-in mathematical functions (SIN, COS, ACOS, RADIANS). This is straightforward and easy to implement.

正解：E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：10

You need to implement both a row access policy and a dynamic data masking policy on the 'EMPLOYEE table in Snowflake. The requirements are as follows: 1. Employees should only be able to see their own record in the 'EMPLOYEE table. 2. The 'SALARY' column should be masked for all employees except those with the 'HR ADMIN' role. Unmasked values are required for compliance reasons, they need to be available for 'HR ADMIN' role. Given the following table structure: CREATE TABLE EMPLOYEE ( EMPLOYEE ID INT, EMPLOYEE NAME STRING, SALARY NUMBER, EMAIL STRING ) ; Which of the following sets of steps correctly implement the row access policy and dynamic data masking policy?

A. Option E

B. Option D

C. Option C

D. Option A

E. Option B

正解：E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：11

Given the following scenario: You have an external table 'EXT SALES in Snowflake pointing to a data lake in Azure Blob Storage. The storage account network rules are configured to only allow specific IP addresses and virtual network subnets, enhancing security. You are getting intermittent errors when querying 'EXT SALES. Which of the following could be the cause(s) and the corresponding solution(s)? Select all that apply.

A. The network connectivity between Snowflake and Azure Blob Storage is unstable. Solution: Implement retry logic in your queries to handle transient network errors.

B. The Snowflake IP addresses used to access the Azure Blob Storage are not whitelisted in the storage account's firewall settings. Solution: Obtain the Snowflake IP address ranges for your region and add them to the storage account's allowed IP addresses.

C. The table function cache is stale, causing access to non-existent files. Solution: Run 'ALTER EXTERNAL TABLE EXT_SALES REFRESH'.

D. The file format specified in the external table definition does not match the actual format of the files in Azure Blob Storage. Solution: Update the 'FILE_FORMAT parameter in the external table definition to match the correct file format.

E. The Snowflake service principal does not have the correct permissions on the Azure Blob Storage account. Solution: Ensure the Snowflake service principal has the 'Storage Blob Data Reader' role assigned to it.

正解：B,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：12

You're tasked with building a data pipeline using Snowpark Python to incrementally load data into a target table 'SALES SUMMARY from a source table 'RAW SALES. The pipeline needs to ensure that only new or updated records from 'RAW SALES are merged into 'SALES SUMMARY' based on a 'TRANSACTION ID'. You want to use Snowpark's 'MERGE' operation for this, but you also need to handle potential conflicts and log any rejected records to an error table 'SALES SUMMARY ERRORS'. Which of the following approaches offers the MOST robust and efficient solution for handling errors and ensuring data integrity within the MERGE statement?

A. Use the 'WHEN MATCHED THEN UPDATE' clause to update existing records and the 'WHEN NOT MATCHED THEN INSERT clause to insert new records. Implement a separate process to periodically compare 'SALES_SUMMARY with 'RAW_SALES' to identify and log any inconsistencies.

B. Employ the 'MERGE statement with 'WHEN MATCHED THEN UPDATE' and 'WHEN NOT MATCHED THEN INSERT clauses, and use a stored procedure that executes the 'MERGE statement and then conditionally inserts rejected records into the 'SALES SUMMARY ERRORS' table based on criteria defined within the stored procedure. This will use the table function on the output.

C. Utilize the 'WHEN MATCHED THEN UPDATE and 'WHEN NOT MATCHED THEN INSERT clauses with a 'WHERE' condition in each clause to filter out potentially problematic records. Log these filtered records to using a separate 'INSERT statement after the 'MERGE operation.

D. Use a single 'MERGE statement with 'WHEN MATCHED THEN UPDATE and 'WHEN NOT MATCHED THEN INSERT clauses. Capture rejected records by leveraging the ' SYSTEM$PIPE STATUS function after the 'MERGE operation to identify rows that failed during the merge.

E. Incorporate an 'ELSE clause in the 'MERGE' statement to capture records that do not satisfy the update or insert conditions due to data quality issues. Use this 'ELSE clause to insert rejected records into 'SALES SUMMARY ERRORS'

正解：B 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：13

You are planning to monetize a dataset on the Snowflake Marketplace. You want to provide potential customers with sample data to evaluate before they purchase a full subscription. Which of the following strategies are valid and recommended for offering a free sample of your data within the Snowflake Marketplace? (Select all that apply)

A. Offer a 'free trial' subscription on the primary listing that automatically expires after a set period (e.g., 7 days), allowing customers to access the full dataset during the trial period. You will need to write custom code to manage trial expiration and data access restrictions based on the trial status.

B. Create a separate share containing a subset (e.g., a smaller number of rows or columns) of the full dataset and offer this share as a free trial listing on the Marketplace.

C. Provide the consumer with the script to create a database link to your data, allowing them read-only access to a pre-defined sample table, and then revoke the access after a set period.

D. Create a view that filters the dataset based on a sampling algorithm (e.g., 'SAMPLE ROW' clause) and share the view through the Marketplace.

E. Upload a sample CSV file to a publicly accessible S3 bucket and provide the link in the Marketplace listing description. Consumers can download and load this data into their own Snowflake account for evaluation.

正解：B,D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：14

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

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

正解：A,C,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：15

You are tasked with sharing a subset of a customer table (CUSTOMER DATA') residing in your organization's Snowflake account with a partner organization. You need to mask personally identifiable information (PII) while providing near real-time updates. You decide to use a secure view. Which of the following SQL statements is the MOST efficient and secure way to accomplish this, assuming the partner only needs 'customer id', 'masked_email', 'city', and 'state'? The email should be masked using SHA256.

A. Option E

B. Option D

C. Option C

D. Option A

E. Option B

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

DEA-C02試験無料問題集「Snowflake SnowPro Advanced: Data Engineer (DEA-C02) 認定」