DEA-C02 Snowflake SnowPro Advanced - Data Engineer PDF Questions

1. Snowflake DEA-C02 Exam Snowflake SnowPro Advanced - Data Engineer https://www.passquestion.com/dea-c02.html 35% OFF on All, Including DEA-C02 Questions and Answers Pass Snowflake DEA-C02 Exam with PassQuestion DEA-C02 questions and answers in the first attempt. https://www.passquestion.com/ 1 / 4

2. 1.A Snowflake Administrator is reviewing the credit consumption of a new virtual warehouse. They notice the warehouse frequently suspends and resumes, adding latency to the first query of each new analytics session. To balance cost and performance for this ad-hoc query workload, which warehouse parameter is most appropriate to adjust? A. `STATEMENT_TIMEOUT_IN_SECONDS` B. `MAX_CLUSTER_COUNT` C. `SCALING_POLICY` D. `AUTO_SUSPEND` Answer: D 2.A data architect needs to create a table for staging ephemeral data that will be processed and deleted within a few hours. The highest priority is to minimize storage costs, specifically by avoiding any expense related to Time Travel and Fail-safe. Which table type should be chosen? A. Permanent B. Temporary C. Transient D. External Answer: B 3.An analytics engineer is querying a `VARIANT` column containing JSON. To improve performance of a query that repeatedly extracts the same nested fields, a colleague suggests creating a standard (non-materialized) view. How will creating this view affect the performance of the query? A. It will have no direct impact on performance. B. It will significantly improve performance by storing the extracted fields. C. It will degrade performance due to the overhead of the view. D. It will improve performance by creating a micro-partition index on the nested fields. Answer: A 4.A data engineering team frequently joins a large fact table (`FACT_SALES`) with a small dimension table (`DIM_COUNTRY`). The `DIM_COUNTRY` table has only 200 rows. Queries are exhibiting suboptimal join performance. Which of the following strategies could help optimize the join performance in this specific scenario? (Select all that apply.) A. Define a clustering key on the foreign key column in the `FACT_SALES` table. B. Use the `SEARCH OPTIMIZATION` service on the `FACT_SALES` table. C. Ensure the virtual warehouse is large enough to hold the smaller dimension table in memory. D. Create a materialized view of the `DIM_COUNTRY` table. E. Convert the warehouse to a multi-cluster warehouse. Answer: A, C 5.When loading data using `COPY INTO`, a data engineer wants to transform the raw data from staged 2 / 4

3. files before it is loaded into the target table. The transformations involve reordering columns from the source file and casting data types. Which clause of the `COPY INTO` command enables this functionality? A. A `SELECT` statement with transformations B. `ON_ERROR = CONTINUE` C. `MATCH_BY_COLUMN_NAME` D. `FILE_FORMAT = ( ... )` Answer: A 6.A data engineer needs to provide read-only access to a `SALES_DATA` table to an external partner. The partner has their own Snowflake account. The engineer wants to ensure that the partner cannot see the underlying definition of the views being shared and cannot execute `DESCRIBE` commands on the shared tables. Which steps should the engineer take to achieve this? (Select all that apply.) A. Create a share object. B. Grant `SELECT` on the `SALES_DATA` table to the share. C. Grant `USAGE` on the database and schema to the share. D. Create the share using the `CLONE` command. E. When creating the views to be shared, use the `SECURE` keyword (e.g., `CREATE SECURE VIEW`). Answer: A, B, C, E 7.A data engineer needs to troubleshoot a `COPY INTO` command that failed. To find the specific error message and details about the failed load, which information source should be queried? A. The `SYSTEM$LAST_QUERY_ID()` function. B. The `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` view, filtering by the session ID. C. The `information_schema.tables` view to check the table's schema. D. The `information_schema.load_history` view for the specific table. Answer: D 8.A data engineer has applied both a row access policy and a masking policy to a table named `EMPLOYEE_FINANCIALS`. When a user queries this table, in which order does Snowflake evaluate these policies? A. The policies are evaluated in parallel, and the results are merged. B. The order is determined by which policy was created first. C. The masking policy is evaluated first, and then the row access policy is evaluated on the masked data. D. The row access policy is evaluated first to determine which rows are visible, and then the masking policy is evaluated on the columns of those visible rows. Answer: D 9.An analyst's query is taking a long time to execute. A Snowflake Administrator checks the query profile and sees a "Remote Disk I/O" operator taking up 90% of the execution time. What does this indicate? A. The virtual warehouse is spilling data from memory to its local SSD. B. The query made effective use of the warehouse's local disk cache. 3 / 4

4. C. The query result is too large to send back to the client application. D. The query is reading a large amount of data from Snowflake's remote storage layer (e.g., S3, Azure Blob). Answer: D 10.Which of the following statements accurately describe Snowflake Stream objects? (Choose 2.) A. A stream incurs significant storage costs by creating a full physical copy of the changed data. B. A standard stream can only track `INSERT` operations on its source table. C. Querying a stream returns the change data for its source table since the stream's offset was last advanced. D. The `STALE_AFTER` parameter defines the time at which a stream becomes stale if it has not been consumed. Answer: C, D 11.An analytics engineer needs to reference the value from the preceding row in a window of data to calculate a difference. The data is ordered by `event_timestamp`. Which window function is used for this purpose? A. `NTH_VALUE()` B. `LEAD()` C. `ROW_NUMBER()` D. `LAG()` Answer: D 12.A data architect is deciding between a standard view and a secure view. What is a key characteristic of a `SECURE` view? A. It can only be queried by users with the `ACCOUNTADMIN` role. B. It automatically masks any columns that are tagged as PII. C. It prevents users from seeing the view's underlying definition and query text. D. It encrypts the data it exposes, providing an extra layer of security. Answer: C 13.A data engineering team is deciding on an ingestion method for two different sources: 1. Source A: A high-volume stream of IoT sensor data (many small files, ~100KB each) that needs to be available in Snowflake with very low latency (seconds). 2. Source B: A large, 200 GB Parquet file that is generated once per day by a batch ETL job and needs to be loaded efficiently. Which Snowflake data loading methods are best suited for Source A and Source B, respectively? A. Source A: `COPY INTO` command, Source B: Snowpipe B. Source A: Snowpipe Streaming, Source B: `COPY INTO` command C. Source A: External Functions, Source B: `COPY INTO` command D. Source A: Snowpipe, Source B: Snowpipe Streaming Answer: B 4 / 4