Snowflake DEA-C02 Practice Test (V8.02) – Your Path to SnowPro Advanced: Data Engineer Certification with the Latest Dumps

1. An analytics engineer wants to improve the performance of queries that frequently filter a 10-billion-row table on the `USER_ID` column. The `USER_ID` column has very high cardinality.

Which feature is specifically designed to accelerate these types of highly selective, point-lookup queries?

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

3. A data engineer needs to implement a policy that prevents users in the `JUNIOR_ANALYST` role from seeing any rows in the `EMPLOYEES` table where the `SALARY` column is greater than 100,000. Users in the `SENIOR_ANALYST` role should be able to see all rows.

Which type of data governance policy is designed to achieve this row-level filtering?

4. An analytics engineer runs `UNDROP TABLE my_table;` one hour after it was accidentally dropped. The command is successful.

What feature made this recovery possible?

5. A data architect is deciding between a standard view and a secure view.

What is a key characteristic of a `SECURE` view?

6. When using the `COPY INTO` command to load Parquet files in Snowflake, which of the following `FILE_FORMAT` options can directly influence the data loading process? (Choose 2.)

7. What is the primary purpose of micro-partitions in the Snowflake architecture?

8. An analytics engineer is responsible for a dashboard that runs the same complex aggregation query every hour on a very large, but slowly changing, base table. The query is slow and consumes significant warehouse credits. The business can tolerate the data in the dashboard being up to an hour stale.

To improve performance and reduce cost, the engineer decides to create a materialized view.

How does a materialized view address this problem?

9. A data architect is choosing a file format for landing Zone-Read-Decimal data from a legacy system into an external S3 stage. The downstream analytics in Snowflake require schema evolution (the ability to add or remove columns easily) and will perform aggregations on numeric fields.

Which file format would be the most suitable for this use case, considering both storage efficiency and compatibility with Snowflake's features?

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

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

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

13. Which of the following statements accurately describe Snowflake Stream objects? (Choose 2.)

14. A data engineer has two streams, `stream_A` and `stream_B`, on two different source tables. A single downstream task, `task_C`, needs to process records from both streams in a single transaction.

Which of the following statements is true about this scenario?

15. When loading data using `COPY INTO`, a data engineer wants to transform the raw data from staged 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?

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

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

18. A data engineer is designing a change data capture (CDC) pipeline for a `TRANSACTIONS` table. The requirement is to capture every change, including inserts, updates, and deletes. However, the downstream process needs to see both the "before" and "after" image of any updated row to calculate the delta.

Which type of stream should be created to meet this requirement?

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

20. A data architect is designing an ingestion solution.

What is a primary difference between the Snowpipe REST API and the Snowpipe Streaming API?

21. An analytics engineer is using the `COPY INTO` command to load a CSV file where text fields are enclosed in double quotes.

Which file format option should be used to correctly parse these fields?

22. A data architect is designing a data governance framework where masking policies need to reference enterprise-wide metadata that is managed outside of Snowflake. For example, a column's data sensitivity level (`PII`, `CONFIDENTIAL`, `PUBLIC`) is stored in an external metadata catalog.

The requirement is to have a single masking policy that can be applied to any column. This policy must call an external API, passing the column name and table name as arguments, to fetch the sensitivity level. Based on the returned level, it will then apply the appropriate masking.

Which combination of Snowflake features is required to implement this? (Choose 2.)

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

24. A data engineer is working with two large tables, `ORDERS` and `SHIPMENTS`, both containing a `REGION` column. A common query joins these two tables on their primary keys and filters for a specific region. To improve performance, the data engineer decides to create a multi-cluster warehouse.

How does a multi-cluster warehouse help optimize the performance of these queries, especially when multiple analysts are running them concurrently?

25. A data architect is comparing data sharing methods.

What is a key benefit of using Snowflake Secure Data Sharing compared to traditional methods like sharing files via SFTP or S3?

26. A data scientist has written a Python UDF to process data using a third-party library that is not included in the default Anaconda package channel provided by Snowflake.

What are the necessary steps the engineer must take to make this UDF work in Snowflake? (Choose 2.)

27. A Snowflake Administrator creates a new role `SALES_ANALYST` but does not grant it to any other role or user.

Which role can manage (e.g., grant, revoke, drop) this new role?

28. For a Snowpipe configured for automatic data ingestion, which of the following are necessary or recommended configurations? (Choose 2.)

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

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