How many Refer to the Exhibit Practice Questions questions are on this page?

This page has 10 Refer to the Exhibit Practice Questions scenario questions for the DEA-C02 exam, each with detailed explanations and wrong-answer analysis.

How should I approach DEA-C02 scenario questions?

Read the full scenario before looking at the answer options. Identify the constraint or requirement in the scenario, then eliminate options that are generally true but wrong for this specific case. Scenario questions reward careful reading over pattern matching.

← Back to SnowPro Advanced: Data Engineer questions

Scenario-based practice

Refer to the Exhibit Practice Questions

Practise SnowPro Advanced: Data Engineer practice questions — original exam-style scenarios covering every exam domain, with detailed explanations, wrong-answer analysis, and common exam traps.

Start full practice test Read exam guide

scenario questions

DEA-C02

exam code

Snowflake

vendor

Scenario guide

How to approach refer to the exhibit practice questions

Practise exhibit-style questions that ask you to read a topology, table, command output or diagram before choosing the best answer.

Quick answer

Exhibit-style questions test whether you can read a topology, command output, diagram or table before choosing the best answer.

How to extract the relevant detail from an exhibit.

How topology, command output or routing information affects the answer.

How to avoid answering from memory before reading the evidence.

How to map the exhibit back to the exam objective.

Practice scenarios

Question 1hardmultiple choice

Full question →

Refer to the exhibit. A security administrator executes this query to audit access to a sensitive table. What specific information is captured in the 'base_objects_accessed' column regarding the data lineage of this query?

Exhibit

SELECT 
    query_id, 
    user_name, 
    base_objects_accessed 
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE EXISTS (
    SELECT 1 
    FROM TABLE(FLATTEN(input => base_objects_accessed))
    WHERE value:"objectName"::string = 'PROD_DB.FINANCE.SALARY_DATA'
);

A
It lists only the immediate view name that the user referenced in their SELECT statement.
Why it fails: The column that captures the immediate object referenced in the query is 'objects_accessed', not 'base_objects_accessed'. Focusing only on the immediate object would fail to reveal the underlying sensitive tables if the user accessed them through an abstraction layer like a non-secure view or a stored procedure.
B
It displays the metadata of the warehouse used to execute the query for the table.
Why it fails: Warehouse metadata is not stored within the 'base_objects_accessed' field. That field is dedicated strictly to object-level lineage and data access patterns. Information about the compute resources used for the query is found in different columns of the ACCESS_HISTORY view or the QUERY_HISTORY view.
C
It identifies the specific columns and rows that were filtered out by the query optimizer.
Why it fails: ACCESS_HISTORY records which objects were accessed, but it does not provide a detailed log of which specific rows were returned or which were filtered out by the optimizer. Its purpose is to track the flow of sensitive data at the object and column level for governance purposes.
D
It identifies the underlying tables that provided the data, even if the user queried a view.
The 'base_objects_accessed' column performs a recursive look-through of views to identify the original source tables. This ensures that even if sensitive data is hidden behind multiple layers of views, the security administrator can still track exactly which underlying physical tables were accessed by the user.

Full breakdown with real-world context →

Question 2hardmultiple choice

Full question →

Refer to the exhibit. What is the DATA_RETENTION_TIME_IN_DAYS setting for the table after the UNDROP operation?

Exhibit

ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 5;
-- Later, the user executes:
DROP TABLE sales_data;
-- Then, the user executes:
UNDROP TABLE sales_data;

A
It reverts to the database default.
Why it fails: The UNDROP command restores the exact metadata state of the table at the time it was dropped. It does not revert to the parent database's default settings. If a specific retention value was explicitly set on the table, that value persists after the object is successfully restored to active status.
B
It remains 5.
The UNDROP operation restores the table to its exact state, including all defined parameters like DATA_RETENTION_TIME_IN_DAYS. Since it was explicitly set to 5 before the DROP command, that configuration is preserved in the metadata and reapplied upon restoration, ensuring no changes to the intended retention policy occur.
C
It reverts to the account default.
Why it fails: Restoration does not force the table to inherit account-level defaults. The object retains the configuration defined by the user prior to the drop. If the user had not set a specific value, it would have inherited the default, but since an explicit value was set, that value takes precedence.
D
It is reset to 1.
Why it fails: There is no mechanism in Snowflake that resets the retention period to 1 upon restoration. The system preserves the table's state exactly as it existed before the DROP command. Resetting to 1 would be incorrect behavior and would potentially violate the user's defined data protection strategy for the table.

Full breakdown with real-world context →

Question 3hardmultiple choice

Full question →

Refer to the exhibit. The 'sales' table is very large and not clustered. Which action will provide the most significant performance improvement for this query?

Exhibit

SELECT * FROM sales WHERE region = 'EMEA' AND sale_date >= '2023-01-01';

A
Enable the Search Optimization Service on the sales table.
Why it fails: Search optimization is designed for point-lookups on specific keys. While it might help with a very selective filter, clustering is a much more robust solution for range-based queries (like the date filter here) and complex filtering. Clustering reorganizes the data for efficiency, whereas search optimization adds an index.
B
Increase the warehouse size.
Why it fails: While a larger warehouse might brute-force the scan faster, it does not address the inefficiency of reading irrelevant data. Clustering is the superior long-term solution because it addresses the data organization, leading to more sustainable performance improvements without unnecessarily increasing compute-related credit costs.
C
Define a clustering key on (region, sale_date).
Clustering the table on these two columns will physically organize the micro-partitions. This enables excellent partition pruning, ensuring that the system only reads the partitions containing data for the specified region and date range, significantly reducing the I/O cost and improving the query execution speed.
D
Materialize the query result in a view.
Why it fails: A materialized view is static and would need to be refreshed as new sales data arrives. This creates a maintenance burden and additional costs. Clustering is an automated background process in Snowflake that maintains organization as data changes, making it a much more manageable and efficient approach.

Full breakdown with real-world context →

Question 4mediummultiple choice

Full question →

Refer to the exhibit. A data engineer runs this query to investigate clustering costs. The output shows high credit consumption but the 'Clustering Depth' of the table remains high. What is the most likely cause of this behavior?

Exhibit

SELECT * FROM TABLE(INFORMATION_SCHEMA.AUTOMATIC_CLUSTERING_HISTORY(
  TABLE_NAME => 'SALES_DATA',
  START_TIME => DATEADD(H, -12, CURRENT_TIMESTAMP())));

A
The table is being continuously updated with data that overlaps existing ranges.
When new data is inserted or existing data is updated in a way that creates overlapping ranges in the clustering key, the clustering service must work harder. If the rate of these changes is high, the service consumes many credits while struggling to keep the table well-organized.
B
The clustering key is defined on a column with a 'Date' data type.
Why it fails: Date columns are actually excellent clustering keys because they naturally provide a logical order for time-series data. Using a Date type would not inherently cause high costs or poor depth unless the data was being loaded in a completely random order across a very wide range of dates.
C
The warehouse used for clustering is too small for the table size.
Why it fails: Automatic Clustering does not use a user-defined virtual warehouse; it uses Snowflake-managed background resources. Therefore, the size of the user's warehouse has no impact on the clustering performance or credit consumption shown in the AUTOMATIC_CLUSTERING_HISTORY view, as these costs are billed separately as background services.
D
The Search Optimization Service is conflicting with the clustering service.
Why it fails: Search Optimization and Automatic Clustering are independent features that operate on different data structures. Search Optimization builds a separate persistent index-like structure, while clustering reorders the micro-partitions themselves. They do not conflict in a way that would cause high clustering costs or prevent the depth from decreasing.

Full breakdown with real-world context →

Question 5hardmultiple choice

Full question →

Refer to the exhibit. If the output shows TABLE_TYPE='BASE TABLE', IS_TRANSIENT='YES', and RETENTION_TIME='1', which statement correctly describes the storage behavior if this table is accidentally dropped?

Exhibit

SELECT 
  TABLE_NAME, 
  TABLE_TYPE, 
  IS_TRANSIENT, 
  RETENTION_TIME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'SENSITIVE_LOGS';

A
The table can be recovered by Snowflake Support for up to 7 days after the drop.
Why it fails: Because IS_TRANSIENT is 'YES', the table has no Fail-safe period. Snowflake Support relies on the Fail-safe layer to perform manual data recoveries. Since this layer does not exist for transient tables, neither the user nor Snowflake Support can recover the data once the Time Travel window expires.
B
The table will stay in Time Travel for 1 day, then move to Fail-safe for 7 days.
Why it fails: Transient tables are explicitly defined by their lack of a Fail-safe period. The data protection lifecycle for a transient table ends immediately when the Time Travel retention period (in this case, 1 day) concludes. There is no subsequent 7-day period for these table types in Snowflake.
C
The table can be recovered using UNDROP for exactly 24 hours after the drop.
The RETENTION_TIME of '1' indicates a 1-day Time Travel window. During this period, the metadata for the dropped table is preserved, allowing the UNDROP TABLE command to succeed. This is the only recovery mechanism available for transient tables before the data is permanently deleted.
D
The table is not recoverable because transient tables do not support the UNDROP command.
Why it fails: This is incorrect; transient tables fully support Time Travel features, including the UNDROP command and the AT/BEFORE clauses, provided the retention time is set to 1. The only Time Travel limitation for transient tables is that the retention period cannot exceed one day.

Full breakdown with real-world context →

Question 6hardmultiple choice

Full question →

Refer to the exhibit. A data engineer is analyzing a Query Profile for a long-running join operation. Based on the provided JSON statistics, what is the most effective action to improve the performance of this specific query?

Exhibit

{
  "Node": "Join",
  "Status": "Success",
  "Statistics": {
    "PartitionsScanned": 4500,
    "PartitionsTotal": 4500,
    "SpillageToLocalDisk": "150GB",
    "SpillageToRemoteDisk": "1.2TB"
  }
}

A
Enable the Search Optimization Service on the join columns.
Why it fails: Search Optimization Service is designed for point lookups on specific values, not for large-scale joins that are already scanning all partitions. Since the exhibit shows 4500 out of 4500 partitions being scanned, the bottleneck is the join processing and data volume, not the initial partition pruning or lookup efficiency.
B
Apply a clustering key to the tables involved in the join.
Why it fails: Clustering might help with initial partition pruning, but the exhibit indicates that the query is already performing a full scan of all partitions. Furthermore, clustering does not address the memory exhaustion issue evidenced by the remote disk spillage, which is the primary cause of the extreme latency in this specific operation.
C
Rewrite the query to use a Common Table Expression (CTE).
Why it fails: Rewriting a query to use CTEs is primarily for readability and does not change how the Snowflake optimizer processes the physical join. It will not reduce the amount of data being processed or the memory required for the join hash table, thus failing to resolve the disk spillage problem.
D
Scale up the virtual warehouse to a larger size.
Scaling up to a larger warehouse increases the available RAM and local SSD space for each compute node. This allows the join operation to be processed entirely in memory or reduces spillage to local disk, completely avoiding the highly latent remote disk spillage that is currently degrading the query performance.

Full breakdown with real-world context →

Question 7mediummultiple choice

Full question →

Refer to the exhibit. A data engineer runs SYSTEM$CLUSTERING_INFORMATION on a table. Based on the output, what is the most accurate interpretation of the table's current state?

Exhibit

{
  "cluster_by_keys" : "LINEAR(C1, C2)",
  "total_partition_count" : 1500,
  "total_constant_partition_count" : 100,
  "average_overlaps" : 12.5,
  "average_depth" : 8.4,
  "partition_depth_histogram" : { ... }
}

A
The table is perfectly clustered because the average depth is less than 10.
Why it fails: An average depth of 8.4 is not considered 'perfect.' In a perfectly clustered table, the depth would be closer to 1.0, meaning that for any given value of the clustering key, only one micro-partition would need to be scanned, greatly improving query performance and data pruning.
B
The clustering depth indicates that a significant number of micro-partitions overlap.
High average overlaps and a depth of 8.4 indicate that the values for C1 and C2 are scattered across many different micro-partitions. This overlap prevents efficient partition pruning during query execution, as the warehouse must open multiple partitions to find all relevant records for a specific key range.
C
The table does not have a clustering key defined, so the depth is irrelevant.
Why it fails: The exhibit explicitly shows 'cluster_by_keys' as 'LINEAR(C1, C2)', confirming that a clustering key has indeed been defined for this table. The depth and overlap metrics are directly calculated based on this definition to help engineers assess if the Automatic Clustering service is performing effectively.
D
The constant partition count of 100 means the table is mostly read-only.
Why it fails: Constant partitions are those where the clustering key values do not overlap with any other partitions. Having only 100 constant partitions out of 1500 total partitions (less than 7%) actually suggests that the table is poorly clustered and that most data is widely distributed across overlapping blocks.

Full breakdown with real-world context →

Question 8mediummultiple choice

Full question →

Refer to the exhibit. Based on the Query Profile, what is the most likely bottleneck for this query?

Exhibit

Query Profile shows: 
- Table Scan: 85%
- Local Disk Spilling: 5%
- Remote Disk Spilling: 0%
- Network: 10%

A
Insufficient warehouse memory.
Why it fails: The profile shows 0% remote disk spilling, which is the primary indicator of insufficient memory. If the warehouse lacked memory, the query would be forced to use remote storage for intermediate results. Since it is not, the performance bottleneck is likely in the data reading process.
B
Inefficient data filtering and large data scans.
Because the table scan dominates the query profile, the query is likely reading more data than necessary. Implementing clustering keys or using partition pruning can reduce the volume of data scanned, directly targeting the primary cause of the high execution time shown in the profile.
C
Network congestion between nodes.
Why it fails: Network time is only 10%, which is relatively low compared to the 85% spent on scanning. Focusing on network optimization would yield negligible improvements compared to addressing the table scan issue, which is clearly the primary driver of latency in the current execution plan.
D
Excessive joins causing data shuffling.
Why it fails: Data shuffling typically results in high network wait times or disk spilling if memory is exceeded. With only 10% network time and no spilling, the query is not performing heavy data movement. The evidence points squarely at the inefficiency of the initial data retrieval phase.

Full breakdown with real-world context →

Question 9hardmultiple choice

Full question →

Refer to the exhibit. A data engineer applies search optimization to a table to speed up point lookups. How does this transformation impact the storage and maintenance costs for the table?

Exhibit

ALTER TABLE sales_data ADD SEARCH OPTIMIZATION ON EQUALITY(customer_email);

A
It has no impact on storage as it uses the existing metadata of the micro-partitions.
Why it fails: Search optimization creates a separate, dedicated data structure to track the location of values. This structure requires additional storage space beyond the standard micro-partition metadata. For large tables with many unique values, the storage overhead can be substantial and must be factored into the total cost of ownership.
B
It increases storage costs and uses serverless compute for maintenance.
When search optimization is enabled, Snowflake builds a search access path. Maintaining this path as the table is updated requires serverless compute resources, which are billed to the account. Additionally, the search access path itself occupies storage, adding to the monthly storage bill for that specific database object.
C
It only increases compute costs during the initial build phase, with no ongoing costs.
Why it fails: The Search Optimization Service is dynamic. Every time DML operations (INSERT, UPDATE, DELETE) are performed on the base table, the search access path must be updated to remain accurate. This ongoing maintenance is handled by Snowflake in the background and results in recurring serverless compute charges whenever the data changes.
D
It reduces storage costs by compressing the base table micro-partitions more efficiently.
Why it fails: Search optimization does not modify the compression or structure of the base table's micro-partitions. Its purpose is to provide an auxiliary path for finding data, not to optimize the underlying storage of the table itself. In fact, it always increases the total storage footprint because of the auxiliary data generated.

Full breakdown with real-world context →

Question 10mediummultiple choice

Full question →

Refer to the exhibit. Why can the user not change the retention time of the table 'SENSITIVE_DATA' to 30 days?

Network Topology

A
The table is transient, which limits retention to one day.
Transient tables in Snowflake are designed for temporary data and support a maximum retention period of only one day. This architectural constraint prevents users from extending the Time Travel window beyond 24 hours, regardless of the Snowflake edition or account settings, ensuring efficient storage for short-lived, transient datasets.
B
The user lacks the OWNERSHIP privilege on the table.
Why it fails: While ownership is required to alter a table, the exhibit specifically highlights that the table type is TRANSIENT. In Snowflake, the restriction on retention for transient tables is a structural limit of the object type, not a permission-based restriction. Changing the ownership would not override this object-level constraint.
C
The table is currently locked by a running query.
Why it fails: Snowflake allows metadata changes to tables, such as altering retention time, even while active DML operations are occurring. There is no concept of a 'lock' that prevents modifying object parameters like retention time. The restriction is inherent to the transient status of the table object, not active query locks.
D
The table has change tracking enabled.
Why it fails: Change tracking is an independent feature that captures DML changes for streams. It does not affect the maximum retention time allowed for a table. The exhibit shows change tracking is false anyway, but even if it were enabled, it would not be the cause of the one-day retention limit.

Full breakdown with real-world context →

These DEA-C02 practice questions are part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style DEA-C02 questions with detailed explanations, topic-based practice, mock exams, readiness tracking, and study analytics.

Refer to the Exhibit Practice Questions

How to approach refer to the exhibit practice questions

Quick answer

Related DEA-C02 topic practice pages

Data Transformation practice questions

Performance Optimization practice questions

Storage and Data Protection practice questions

Data Governance practice questions

Data Movement practice questions

DEA-C02 fundamentals practice questions

DEA-C02 scenario practice questions

DEA-C02 troubleshooting practice questions

Practice scenarios

Refer to the exhibit. A security administrator executes this query to audit access to a sensitive table. What specific information is captured in the 'base_objects_accessed' column regarding the data lineage of this query?

Exhibit

Refer to the exhibit. What is the DATA_RETENTION_TIME_IN_DAYS setting for the table after the UNDROP operation?

Exhibit

Refer to the exhibit. The 'sales' table is very large and not clustered. Which action will provide the most significant performance improvement for this query?

Exhibit

Refer to the exhibit. A data engineer runs this query to investigate clustering costs. The output shows high credit consumption but the 'Clustering Depth' of the table remains high. What is the most likely cause of this behavior?

Exhibit

Refer to the exhibit. If the output shows TABLE_TYPE='BASE TABLE', IS_TRANSIENT='YES', and RETENTION_TIME='1', which statement correctly describes the storage behavior if this table is accidentally dropped?

Exhibit

Refer to the exhibit. A data engineer is analyzing a Query Profile for a long-running join operation. Based on the provided JSON statistics, what is the most effective action to improve the performance of this specific query?

Exhibit

Refer to the exhibit. A data engineer runs SYSTEM$CLUSTERING_INFORMATION on a table. Based on the output, what is the most accurate interpretation of the table's current state?

Exhibit

Refer to the exhibit. Based on the Query Profile, what is the most likely bottleneck for this query?

Exhibit

Refer to the exhibit. A data engineer applies search optimization to a table to speed up point lookups. How does this transformation impact the storage and maintenance costs for the table?

Exhibit

Refer to the exhibit. Why can the user not change the retention time of the table 'SENSITIVE_DATA' to 30 days?