Snowflake DEA-C02 시험 개요:

Snowflake DEA-C02 시험 요강 주제:

최신 SnowPro Advanced DEA-C02 무료샘플문제

1. A data engineer is tasked with processing a large dataset of customer orders using Snowpark Python. The dataset contains a column stored as a string in 'YYYY-MM-DD HH:MI:SS' format. They need to create a new DataFrame with only the orders placed in the month of January 2023. Which of the following code snippets achieves this most efficiently, considering potential data volume and query performance?

A)

B)

C)

D)

E)

2. 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) Convert the query to use a LATERAL FLATTEN function to pre-process the 'CUSTOMER_DIW table before the join.
B) Ensure both 'SALES DATA' and 'CUSTOMER DIM' are clustered on 'CUSTOMER ID.
C) 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.
D) 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.
E) Increase the virtual warehouse size and monitor for spillover to local disk. If spilling occurs, further increase the warehouse size.

3. You have implemented a Snowpipe using auto-ingest to load data from an AWS S3 bucket. The pipe is configured to load data into a table with a 'DATE column ('TRANSACTION DATE'). The data files in S3 contain a date field in the format 'YYYYMMDD'. Occasionally, you observe data loading failures in Snowpipe with the error message indicating an issue converting the string to a date. The 'FILE FORMAT' definition includes 'DATE FORMAT = 'YYYYMMDD''. Furthermore, you are also noticing that after a while, some files are not being ingested even though they are present in the S3 bucket. How to effectively diagnose and resolve these issues?

A) Snowflake's auto-ingest feature has limitations and may not be suitable for inconsistent data formats. Consider using the Snowpipe REST API to implement custom error handling and data validation logic. Monitor the Snowflake event queue to ensure events are being received.
B) Verify that the 'DATE FORMAT is correct and that all files consistently adhere to this format. Check for corrupted files in S3 that may be preventing Snowpipe from processing subsequent files. Additionally, review the Snowpipe error notifications in Snowflake to identify the root cause of ingestion failures. Use 'SYSTEM$PIPE to troubleshoot the files not ingested
C) The 'DATE FORMAT parameter is case-sensitive. Ensure it matches the case of the incoming data. Also, check the 'VALIDATION MODE and ERROR parameters to ensure error handling is appropriately configured for files with date format errors. For the files that are not ingested use 'SYSTEM$PIPE to find the cause of the issue.
D) The error could be due to invalid characters in the source data files. Implement data cleansing steps to remove invalid characters from the date fields before uploading to S3. For files not being ingested, check S3 event notifications for missing or failed events.
E) The issue may arise if the time zone of the Snowflake account does not match the time zone of your data in AWS S3. Try setting the 'TIMEZONE parameter in the FILE FORMAT definition. For files that are not being ingested, manually refresh the Snowpipe with 'ALTER PIPE ... REFRESH'.

4. You are tasked with creating an external function in Snowflake that calls a REST API. The API requires a bearer token for authentication, and the function needs to handle potential network errors and API rate limiting. Which of the following code snippets demonstrates the BEST practices for defining and securing this external function, including error handling?

A) Option C
B) Option B
C) Option A
D) Option D
E) Option E

5. You are designing a continuous data pipeline to load data from AWS S3 into Snowflake. The data arrives in near real-time, and you need to ensure low latency and minimal impact on your Snowflake warehouse. You plan to use Snowflake Tasks and Streams. Which of the following approaches would provide the most efficient and cost-effective solution for this scenario, considering data freshness and resource utilization?

A) Create a single, root Snowflake Task that triggers every 5 minutes, executing a COPY INTO command to load all new data from the S3 bucket into a staging table, followed by a MERGE statement to update the target table. Use 'VALIDATE ( STAGE NAME '0'.////' before COPY INTO.
B) Create a Pipe object in Snowflake using Snowpipe and configure the S3 bucket for event notifications to the Snowflake-provided SQS queue. Monitor the Snowpipe status using 'SYSTEM$PIPE STATUS and address any errors by manually retrying failed loads with 'ALTER PIPE REFRESH;'
C) Create a Stream on the target table and a Snowflake Task. The task executes a COPY INTO command into a staging table when the Stream has data and then a MERGE statement. Schedule the task to run continuously with 'WHEN SYSTEM$STREAM HAS but limit the 'WAREHOUSE SIZE' to
D) Configure an AWS SQS queue to receive S3 event notifications whenever a new file is uploaded. Use a Lambda function triggered by the SQS queue to invoke a Snowflake stored procedure. This stored procedure executes a COPY INTO command to load the specific file into Snowflake. Use 'ON ERROR = CONTINUE' during COPY INTO.
E) Create a Stream on the target table and a Snowflake Task that runs every minute. The task executes a MERGE statement to apply changes from the Stream to the target table, filtering the Stream data using the 'SYSTEM$STREAM GET TABLE TIMESTAMP function to process only newly arrived data since the last task execution. Use 'WHEN SYSTEM$STREAM HAS to run the Task.

질문과 대답: