Courseiva

DEA-C02 · domain

scenario questions

Practise SnowPro Advanced: Data Engineer scenario questions practice questions — original exam-style scenarios with answer choices, explanations, and analysis of common mistakes.

229 questions45 easy118 medium66 hard

Focused practice

Practice scenario questions questions

Scored sessions drawing only from this domain — pick a length below.

Start 20-question practice test →

What this domain covers

What to know about scenario questions

scenario questions questions test whether you can apply the concept in context, not just recognise a definition.

How the topic appears in realistic exam-style scenarios.

Which detail in the question changes the correct answer.

How to eliminate plausible but wrong options.

How to connect the question back to the wider exam objective.

Watch out for

Common scenario questions exam traps

  • ▸Answering from memory before reading the full scenario.
  • ▸Missing a constraint such as cost, availability, security, scope or command context.
  • ▸Choosing a broad answer when the question asks for the most specific fix.
  • ▸Ignoring why the wrong options are tempting.

Question index

All scenario questions questions (229)

Click any question to see the full explanation, or start a practice session above.

1

A data engineer is converting a column of strings into integers. Some rows contain non-numeric characters that would normally cause the query to fail. Which function should be used to return a NULL value instead of an error when a conversion is impossible?

Easy
2

A data engineer needs to unload a large table to an external stage in Parquet format. The table contains a column with sensitive data that must be masked in the unloaded files. The engineer wants to use a secure view that applies a masking policy, and then unload from that view. Which statement accurately describes the behavior when unloading from a secure view?

Medium
3

A data engineer needs to perform a complex transformation that involves reading from a source table and writing to multiple target tables in a single transaction. What is the recommended strategy?

Medium
4

A data engineer needs to protect a set of permanent tables in a Snowflake Enterprise Edition account. The requirement is to allow querying of historical versions for up to 30 days and to allow recovery of dropped tables within that same window. Which two actions should the engineer take to meet these requirements? (Choose two.)

Medium
5

A data engineer needs to create a development copy of a 5 TB production table named FACT_SALES for query testing. The team wants the copy to share the same underlying micro-partitions as the source so no additional storage is consumed at creation time. Which command should the engineer use?

Hard
6

A data engineer is designing a transformation that requires unpivoting a wide table with columns `q1_sales`, `q2_sales`, `q3_sales`, and `q4_sales` into a long format with columns `quarter` and `sales`. The table has millions of rows. Which Snowflake construct should be used to achieve this efficiently?

Hard
7

A data engineer is auditing storage consumption in a Snowflake account. A large permanent table named SALES_FACT has DATA_RETENTION_TIME_IN_DAYS set to 60. Over the past month, ETL jobs have performed several full-table UPDATE operations that rewrote most micro-partitions. The engineer notices that the table's Time Travel storage has grown significantly even though the visible row count is unchanged. What is the most likely cause of the increased Time Travel storage?

Hard
8

A warehouse is being used for both ETL processes and ad-hoc BI reporting. Users report that BI reports are slow during ETL runs. What is the best optimization?

Medium
9

A data engineer manages a transient table named STG_EVENTS in a Snowflake Enterprise Edition account. The table has a DATA_RETENTION_TIME_IN_DAYS setting of 1. The engineer wants Time Travel to retain data for 30 days for this table. What will happen if the engineer executes ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 30?

Medium
10

A data engineer is building a transformation pipeline that must parse semi-structured log data stored in a VARIANT column named log_data. The JSON structure contains a nested array under the key 'events', and each element in the array has a 'timestamp' field. The engineer needs to produce one row per event with the timestamp extracted as a TIMESTAMP_NTZ value. Which SQL construct should be used to achieve this transformation efficiently?

Medium
11

A user runs the same complex analytical query twice within 5 minutes and notices the second execution is nearly instantaneous. Which Snowflake feature is primarily responsible for this performance improvement?

Easy
12

A data engineer is loading data from an external stage into a Snowflake table using a COPY INTO command. The source files are compressed with gzip and contain a header row. The engineer wants to skip the first row of each file and load the remaining data. Which COPY INTO option should be used?

Hard
13

Which of the following is the best practice for using the query profile to identify performance bottlenecks?

Medium
14

A data engineer is implementing a data classification process using Snowflake's Data Classification feature. The engineer wants to automatically classify columns containing sensitive data and then use the results to apply masking policies. After running the classification, the engineer notices that some columns that should be classified as 'EMAIL' are not being tagged. The engineer has verified that the data contains valid email addresses. What is the most likely reason for the missing classification?

Hard
15

An analyst needs to unload a 4 TB table from Snowflake to an external Amazon S3 stage for archival. The team wants maximum write throughput and wants the output organized so downstream tools can read subsets without scanning everything. Which combination of COPY INTO options best achieves this?

Medium
16

In a data transformation pipeline, what is the primary benefit of using 'Zero-Copy Cloning' for creating a development environment from production data?

Medium
17

Which feature allows a Data Engineer to define a transformation pipeline where the target table automatically updates when the source table changes, without needing to manually define a schedule?

Easy
18

A data engineer is reviewing a query profile for a query that performs a large join. The profile shows a high percentage of time spent in the 'Join' node with significant 'Bytes spilled to local storage'. The engineer wants to reduce local spilling without changing the query. Which action is most appropriate?

Easy
19

A data engineer needs to move a large table from an on-premises Oracle database into Snowflake on a recurring nightly basis. The source system allows outbound connections to cloud endpoints but does not permit installing third-party agents on the database host. Which Snowflake-native approach best fits these constraints?

Medium
20

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?

Hard
21

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?

Hard
22

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?

Medium
23

A data engineer has a permanent table ORDERS_FACT in an Enterprise Edition account with DATA_RETENTION_TIME_IN_DAYS set to 14. The table was dropped 20 days ago. The engineer now runs UNDROP TABLE ORDERS_FACT. What is the outcome?

Hard
24

A data engineer needs to continuously ingest JSON event files landing in an Amazon S3 bucket. The ingestion must be automatic, near real-time, and must reuse the same transformation logic already validated in a COPY INTO statement. The team wants the least administrative overhead. Which Snowflake feature should they configure?

Medium
25

A data engineer needs to move data from a Snowflake table in the 'US-WEST-2' region to another Snowflake table in the 'EU-CENTRAL-1' region. What is the most resilient and automated way to keep these tables synchronized?

Medium
26

Which of the following is the most cost-effective way to handle massive concurrent read-only queries?

Medium
27

A data engineer is designing a pipeline that loads semi-structured data from an external stage into a Snowflake table with a VARIANT column. The files contain nested arrays and keys that vary between records. Which TWO configuration choices should the engineer make to handle the variability and preserve the structure? (Choose two.)

Hard
28

A data engineer is using Snowpark for Python to perform transformations on a Snowflake table. The engineer wants to leverage Snowpark's capabilities for data transformation. Which two statements accurately describe Snowpark's features for transformation? (Choose two.)

Medium
29

A user complains that a dashboard query is slow during peak hours. The warehouse is configured with auto-suspend and auto-resume. What is the most likely cause of the latency observed during the initial execution?

Medium
30

A data engineer needs to prevent any future column additions to a critical ORDERS table from containing unprotected PII. The goal is to automatically classify new columns and receive alerts when sensitive data is detected. Which Snowflake feature should be configured to achieve this?

Easy
31

What is the primary purpose of a 'Secure View' in Snowflake from a governance perspective?

Easy
32

A query is slow because it is scanning a massive table. The filters are on columns that are not currently clustered. What is the most immediate step to improve performance?

Easy
33

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

Medium
34

A data engineer is using Snowpipe to load data from an external stage. The pipe is configured with AUTO_INGEST = TRUE. Which two statements are true regarding the behavior of Snowpipe in this configuration? (Choose two.)

Hard
35

A data engineer needs to transform a large fact table by adding a column that contains the previous row's `sale_amount` partitioned by `customer_id` and ordered by `sale_date`. The table has billions of rows, and the transformation must run efficiently without shuffling data unnecessarily. Which Snowflake feature should be used to achieve this?

Hard
36

A data engineer is configuring Snowpipe to ingest files from an S3 bucket. The bucket contains files with different schemas. Which approach allows the engineer to handle these variations without creating separate pipes?

Medium
37

A data engineer notices that a query performing a large GROUP BY on a high-cardinality column is slow. The Query Profile shows that the aggregation step is spilling to local disk. The engineer wants to reduce local spilling without changing the query logic. Which action is most appropriate?

Medium
38

A data engineer is responsible for implementing data governance in Snowflake. The organization requires that all access to sensitive data be auditable and that data usage can be attributed to specific users and roles. Which two Snowflake features should the engineer use to meet these requirements? (Choose two.)

Medium
39

A data engineer is analyzing a query that filters on a column named 'status' which has only 5 distinct values. The table has 10 billion rows. The query is running slowly, and the query profile shows a full table scan. Which action is most likely to improve performance?

Easy
40

To comply with privacy regulations, a data engineer must ensure that certain columns in a table are masked for unauthorized users during transformation. Which Snowflake feature provides a way to define re-usable masking logic that is automatically applied at query time?

Medium
41

A data engineer is designing a data protection strategy for a set of permanent tables in a Snowflake Enterprise Edition account. The engineer needs to ensure that accidental data modifications can be reversed and that dropped tables can be restored by the team without opening a support ticket. Which two configuration choices support these goals? (Choose two.)

Hard
42

A data engineer is analyzing a Query Profile for a query that joins a large fact table to a small dimension table. The profile shows a significant amount of time spent in the 'Join' operator, and the 'Bytes spilled to remote storage' metric is high. The engineer has already confirmed that the small table is used as the build side. Which optimization should the engineer try next to reduce remote spilling?

Hard
43

A data engineer is troubleshooting a Snowpipe that is not loading new files from an external Amazon S3 stage. The pipe definition is valid and manual ALTER PIPE ... REFRESH loads the files successfully, but automatic ingestion never triggers. Which cause is most consistent with these symptoms?

Hard
44

Which feature should be used to automate the loading of files from an S3 bucket into Snowflake as soon as they are uploaded, without manual intervention?

Easy
45

A data engineer runs the following statement on a permanent table in a Snowflake account: ALTER TABLE SALES_ARCHIVE SET DATA_RETENTION_TIME_IN_DAYS = 14; The table previously had a retention of 1 day, and the account is on Enterprise Edition. Which statement accurately describes the effect?

Medium
46

What is the result of increasing the DATA_RETENTION_TIME_IN_DAYS from 1 to 5 days on an existing table?

Medium
47

Which Snowflake feature helps in managing data transformation pipelines by providing a visual and programmatic way to handle task dependencies and state management?

Medium
48

A data engineer is analyzing a slow-running query that performs a large aggregation over a table with many columns. The query profile shows that most time is spent in the 'Aggregate' operator, and there is significant data spilling to local disk. Which action is most likely to reduce the spilling and improve performance?

Medium
49

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?

Hard
50

A data engineer is configuring a new permanent table in a Snowflake Standard Edition account. The engineer needs to ensure that the table can be recovered if it is accidentally dropped, but wants to minimize storage costs. The table will be updated frequently. Which DATA_RETENTION_TIME_IN_DAYS setting should the engineer choose to meet these requirements?

Easy
51

A financial services firm stores account balances in a Snowflake table ACCOUNTS. A row access policy is defined so that analysts see only rows where REGION = CURRENT_REGION(). The firm also attaches a masking policy to the BALANCE column that returns NULL for users without the FINANCE role. An analyst with the ANALYST role queries SELECT REGION, BALANCE FROM ACCOUNTS. What will the analyst see?

Hard
52

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?

Hard
53

A data engineer is using Snowpark for Python to transform data. The engineer needs to perform a join between two DataFrames and then apply a filter based on a column from the joined result. Which TWO methods are appropriate for this transformation? (Choose two.)

Medium
54

A data engineer is tasked with migrating a 10TB historical dataset from an on-premise HDFS cluster to Snowflake. The data is currently stored in compressed CSV files. What is the most efficient strategy to ensure optimal performance during the initial bulk load into a Snowflake table?

Medium
55

Which Snowflake feature should be used to move data from a private cloud network to Snowflake without traversing the public internet?

Medium
56

An organization wants to classify their data to identify PII. Which feature should they use to automatically tag columns containing sensitive information?

Easy
57

Which governance tool allows an administrator to audit who accessed a specific table and when?

Medium
58

During a data loading process using the COPY command, the data engineer notices that the same files are being processed multiple times, leading to duplicate records. Which Snowflake feature is likely being bypassed or misconfigured?

Medium
59

A data engineer needs to periodically copy incremental data from an on-premises database into Snowflake. The source system can export CSV files to a network share, and the engineer wants to use a Snowflake-managed stage that does not require configuring an external cloud storage bucket. Which stage type should be used?

Easy
60

A data engineer needs to audit all tag assignments across the account to ensure that no sensitive columns are missing required tags. Which Snowflake view should the engineer query to retrieve a list of all tags applied to columns, including the tag name, value, and the object it is applied to?

Medium
61

An organization is unloading data from a Snowflake table to an external S3 bucket for use in a machine learning pipeline. Which TWO practices will optimize the performance and manageability of the exported files? (Select TWO)

Medium
62

When a large production table is cloned to a development environment using the CLONE keyword, how is the initial storage for the cloned table billed?

Easy
63

A data engineer notices that a daily aggregation query scans 4 TB of a 5 TB table even though it only needs the last seven days of data. The table has a DATE column but no clustering key, and the query filters with DATE >= CURRENT_DATE - 7. Query Profile shows almost no partition pruning. What is the most effective change to reduce bytes scanned?

Medium
64

Which Snowflake feature is best suited for transforming data that is already loaded and requires periodic, complex SQL transformations without managing manual scheduling or streams?

Easy
65

An engineer needs to ensure that a transformation pipeline handles 'late-arriving' data in a streaming context. Which feature is most effective?

Medium
66

A data engineer is tasked with migrating small, frequent batches of data into Snowflake. Which feature is most appropriate to keep costs low while ensuring the data is processed continuously?

Medium
67

A user is experiencing 'Data Spilling' in a query that performs a complex window function over a very large dataset. What is the most likely reason this is occurring and how should it be addressed?

Hard
68

A data engineer is setting up Snowflake Data Classification on a table containing customer feedback. The engineer wants to ensure that the classification process identifies columns with potentially sensitive information and tags them appropriately. Which two actions are required to enable Data Classification on the table? (Choose two.)

Medium
69

A Snowflake data engineer has been tasked with implementing dynamic data masking on a CUSTOMERS table so that the SSN column is fully redacted for all users except those with the role PII_ADMIN. The engineer wants the masking to apply automatically whenever the column is queried, without changing any application SQL. Which Snowflake object should the engineer create and attach to the SSN column?

Medium
70

A data engineering team needs to create a set of tables for an ETL process that involves massive intermediate data transformations. These tables should persist across multiple sessions for 24 hours but do not require long-term disaster recovery protection. Which table type should be used to minimize storage costs while meeting these requirements?

Medium
71

A data engineer accidentally drops a permanent table named CUSTOMER_DIM that had DATA_RETENTION_TIME_IN_DAYS set to 5. Six days after the drop, the engineer runs UNDROP TABLE CUSTOMER_DIM; and it fails. What is the most likely explanation?

Hard
72

A production team uses a permanent table with DATA_RETENTION_TIME_IN_DAYS set to 14. After an accidental DELETE, the team successfully runs a Time Travel query to recover the rows. Two days later, the same team needs to recover a different set of rows that were deleted 20 days ago. What will happen when they attempt Time Travel for the 20-day-old deletion?

Medium
73

A data engineer is optimizing a query that joins a very large fact table to a small dimension table. The Query Profile shows the small table being redistributed across all nodes before the join. Which action is most likely to improve performance?

Medium
74

When analyzing a Query Profile, which indicator most strongly suggests that 'Partition Pruning' is performing effectively?

Medium
75

A data engineer creates a table in a Snowflake database that resides on a standard (non-replicated) storage. The engineer wants to confirm that Fail-safe will protect the table after Time Travel expires. Which table type should the engineer use?

Easy
76

When using the COPY INTO command to load data from an S3 bucket, which of the following best describes how Snowflake handles file partitioning?

Medium
77

A data engineer has a permanent table named ORDERS with DATA_RETENTION_TIME_IN_DAYS set to 5. The table is dropped accidentally. The engineer executes UNDROP TABLE ORDERS 2 days later, successfully restoring the table. What is the DATA_RETENTION_TIME_IN_DAYS setting for the restored table?

Hard
78

A data engineer needs to restore a specific version of a permanent table named FINANCE_LEDGER as it existed 40 hours ago, but the table is currently 2 TB and has DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer wants to recover the historical data without affecting the current table. Which approach correctly recovers the data?

Hard
79

A data engineer is designing a storage strategy for a Snowflake account that uses Enterprise Edition. The engineer needs to ensure that critical tables can be recovered for up to 90 days, while non-critical staging tables should minimize storage costs. Which two statements accurately describe Snowflake Time Travel and Fail-safe behavior for permanent tables? (Choose two.)

Hard
80

Which transformation technique should be used when you need to pivot data from a long-form format (rows) to a wide-form format (columns) for reporting?

Medium
81

A data engineer wants to load data from an S3 bucket and perform a transformation during the load. Which method is the most appropriate for this task?

Medium
82

What is the primary purpose of the 'SNOWFLAKE.ACCOUNT_USAGE' schema in a governance context?

Easy
83

A data engineer is creating a new table to hold a large volume of raw clickstream events that are loaded continuously and only retained for reporting within the same day. The team wants to minimize storage costs while still allowing recovery of rows modified within the last 24 hours. Which table type best fits these requirements?

Easy
84

A data engineer wants to optimize a query that performs a point lookup on a value nested deep within a VARIANT column in a 50TB table. Which approach is the most effective for optimizing this lookup?

Hard
85

A data engineer is implementing a data governance strategy and needs to ensure that all tables containing sensitive data are automatically identified and tagged. The engineer wants to use Snowflake's native classification capabilities and then apply masking policies based on those tags. Which sequence of steps should the engineer follow?

Medium
86

A data engineer is building a transformation pipeline that processes semi-structured event logs stored in a VARIANT column named `event_data`. The logs contain a nested array under the key `items`. The engineer needs to produce one output row per element in the array, preserving all other columns from the source table. Which Snowflake construct should be used to achieve this transformation?

Medium
87

A data engineer is unloading a large fact table to an external stage pointing at an Amazon S3 bucket. The downstream consumer requires many small files for parallel processing, and each file must be no larger than 64 MB. Which COPY INTO location options should the engineer use?

Medium
88

A data engineer is investigating a query that reads a large table and returns only a few rows after filtering on a high-cardinality column. The Query Profile shows a high percentage of partitions scanned relative to partitions total. Which two actions should the engineer take to improve pruning? (Choose two.)

Hard
89

A financial institution uses Snowflake to store customer transactions. A data engineer needs to implement a policy that restricts access to rows in the TRANSACTIONS table based on the department of the user. The department information is stored in a lookup table named USER_DEPARTMENT. The policy must be applied dynamically without modifying the TRANSACTIONS table. Which Snowflake feature should the engineer use?

Hard
90

A data engineer maintains a transient table named STG_EVENTS in a Snowflake Enterprise Edition account. The table has a DATA_RETENTION_TIME_IN_DAYS setting of 1. The engineer accidentally truncates the table and immediately realizes the mistake. What is the most reliable way to recover the lost rows?

Medium
91

A table experiences performance degradation over time due to frequent DML operations (INSERT/UPDATE/DELETE). What is the most likely cause?

Hard
92

When performing a MERGE operation to update a large table, which factor most significantly impacts the performance of the transformation?

Hard
93

What is the primary benefit of using `CLONE` for data transformation and testing?

Easy
94

What is the primary benefit of using a 'Materialized View' over a standard view in Snowflake?

Medium
95

A data engineer has a permanent table ORDERS_ARCHIVE with DATA_RETENTION_TIME_IN_DAYS set to 30. An analyst accidentally runs DELETE FROM ORDERS_ARCHIVE WHERE order_date < '2022-01-01' and commits the transaction. Twenty minutes later, the engineer wants to recover only the deleted rows without disturbing the current state of the table. Which approach is correct?

Hard
96

A data engineer needs to unload a large table from Snowflake to an external stage. The unloading process must produce a single compressed file. Which COPY INTO <location> option should be used to ensure the output is a single file?

Hard
97

A data engineer needs to audit all grants of the 'SYSADMIN' role to users across the Snowflake account. The engineer has access to the ACCOUNTADMIN role and wants to retrieve this information efficiently. Which Snowflake view should be queried?

Hard
98

While reviewing a Query Profile, a data engineer notices a 'Join' operator where the number of output rows is significantly larger than the sum of the input rows. Which TWO steps should be taken to resolve this performance issue? (Select TWO)

Hard
99

A data engineer is using a Snowpipe to load data from an external stage. The pipe has been running successfully, but the engineer notices that some files are being loaded multiple times, resulting in duplicate records. Which action should be taken to prevent future duplicate loads?

Medium
100

A Data Engineer needs to ensure that data in a target table is updated with changes from a source table while handling potential duplicate records. Which command should be used?

Medium
101

A data engineer runs a query that joins a 5 TB fact table to a 200 GB dimension table. The query profile shows a broadcast operation for the dimension table and a local spilling node on the fact table. The engineer wants to reduce local spilling without changing the query results. The warehouse is a multi-cluster warehouse with sufficient memory. Which action is most likely to reduce local spilling?

Medium
102

A data engineer is transforming a table with a column `tags` stored as a VARIANT array of strings. The goal is to produce one row per tag for downstream aggregation. Which function should be used to expand the array into rows?

Easy
103

A data engineer manages a Snowpipe that ingests files from an external stage. The pipe uses a file format with SKIP_HEADER = 1, and the source files are regenerated daily with the same names in the same stage path. The engineer notices that only the first day's files are loaded and subsequent regenerated files are ignored. What is the most likely cause?

Hard
104

A data engineer is loading 1TB of data from an S3 bucket into Snowflake using the COPY command. The data consists of 10,000 small files (approx. 100KB each). How will this file size affect the loading performance?

Medium
105

During a bulk load from an S3 stage, a data engineer notices that the data is not being loaded despite the COPY INTO command executing successfully. What is the most likely cause?

Medium
106

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

Medium
107

Which TWO of the following are considered 'anti-patterns' for performance in Snowflake?

Hard
108

Which of the following describes the purpose of a Snowflake storage integration object?

Easy
109

A data engineer maintains a table that receives continuous small INSERT statements throughout the day. Query performance on this table has degraded even though total data volume is modest, and the Query Profile shows many very small micro-partitions being scanned. Which action best addresses the underlying cause?

Hard
110

A data engineer needs to retain a point-in-time copy of a large permanent table SALES_HISTORY for regulatory purposes. The table is 5 TB. The engineer runs CREATE TABLE SALES_HISTORY_BACKUP CLONE SALES_HISTORY. What is the initial storage cost impact of this operation?

Medium
111

A data engineer must validate a COPY INTO load from an external stage before promoting it to production. The team wants to confirm which files were loaded, how many rows each contained, and which rows were rejected, without leaving partial or duplicated data in the target table. (Choose two.)

Hard
112

A data engineer notices that a query against a large table returns results quickly when filtering on one column but scans the entire table when filtering on another column. Both columns are used in equality predicates. What is the most likely explanation?

Easy
113

An auditor requests proof of who has accessed a specific table containing sensitive data. Which Snowflake view in the ACCOUNT_USAGE schema provides this data?

Medium
114

A data engineer is implementing a Python User-Defined Function (UDF) to perform complex string manipulation. To optimize performance for a large-scale transformation, the engineer wants to ensure the UDF processes multiple rows in a single call. Which type of UDF should be implemented?

Medium
115

A data engineer is loading semi-structured JSON files from an external stage into a VARIANT column. Several files contain a field named 'event_time' formatted as an ISO-8601 string, but the ingestion team wants to automatically convert it to a TIMESTAMP_NTZ during the load without using a separate transformation step. Which COPY INTO feature should be used?

Medium
116

A data engineer needs to join a large fact table to a small dimension table that changes slowly. The dimension has a `valid_from` and `valid_to` timestamp, and the fact rows have an `event_ts`. The engineer wants to avoid scanning the entire dimension for every fact row and must ensure the join uses the correct validity window. Which approach is most appropriate?

Hard
117

If a permanent table in a Snowflake Enterprise edition account is dropped, and it had a 10-day Time Travel retention period, what is the total duration until the data is permanently unrecoverable by any means?

Medium
118

A data engineer is designing a recovery strategy for a critical permanent table FACT_SALES in an Enterprise Edition account. The team wants the ability to restore the table to any point within the last 60 days and also wants protection against a catastrophic failure that corrupts the data. Which statement correctly describes the available recovery windows?

Medium
119

A data engineer needs to ensure that all queries against a table containing sensitive data are logged for compliance purposes. Which Snowflake feature should the engineer use to capture the query text and the user who executed it?

Easy
120

Which of the following scenarios is most appropriate for using a Materialized View to optimize performance?

Medium
121

A company requires that data masking policies be applied automatically whenever a column is tagged with 'PII'. How can this be achieved?

Medium
122

A Snowflake account has a tag-based masking policy on column CUSTOMER.SSN. An analyst runs a query that applies the SYSTEM$GET_TAG function to that column. The analyst has been granted the APPLY MASKING POLICY privilege on the tag, but not the USAGE privilege on the tag. What does the analyst see for the SSN column value?

Medium
123

A financial services firm stores customer records in a table called TRANSACTIONS. The compliance team requires that a specific column, CREDIT_CARD_NUMBER, be transformed so that only the last four digits are visible to all users except members of the role PAYMENT_ADMIN. Additionally, the transformation must occur at query time without modifying the stored data. Which Snowflake feature should the data engineer use to meet this requirement?

Medium
124

A data engineer is unloading a large fact table to an external stage and wants to minimize the total volume of data transferred while keeping files readable by downstream tools. The table contains many repeated values in several columns. Which approach best reduces the unloaded data size?

Hard
125

A data engineer needs to continuously load JSON event files that arrive in an Amazon S3 bucket into a Snowflake table with near-zero latency. The files are small and arrive in bursts of hundreds per minute. Which Snowflake feature should the engineer configure?

Medium
126

A data engineer runs a batch job on a transient table named STG_EVENTS that is loaded from an external stage. The job truncates and reloads the table every night. After a faulty deployment, the engineer needs to restore the table contents from 30 hours ago. The engineer attempts Time Travel but the query returns no historical data. What is the most likely cause?

Hard
127

A data engineer is managing a Snowflake account with multiple databases. The engineer wants to ensure that critical data can be recovered in the event of accidental deletion or modification. The engineer is considering using Time Travel and Fail-safe. Which TWO statements accurately describe the capabilities and limitations of Fail-safe? (Choose two.)

Hard
128

A data engineer needs to ensure that only users with the role FINANCE_ANALYST can view the SALARY column in the EMPLOYEES table. All other users should see a masked value. Which Snowflake feature should the engineer use?

Easy
129

A data engineer is monitoring warehouse performance and notices a high 'Warehouse Overload' status. Which TWO metrics from the Query History or Warehouse Load monitoring views should be prioritized to confirm the warehouse is undersized? (Select TWO)

Medium
130

You are debugging a transformation pipeline where a task is failing with an 'Insufficient Privileges' error during a MERGE operation. The task is owned by a service account role. What is the most likely cause?

Hard
131

In Snowflake's object tagging hierarchy, if a tag is applied at the Schema level and a different value for the same tag is applied at the Table level, what is the resulting behavior for the Table?

Easy
132

A data engineer is designing a transformation pipeline that uses Snowpark to process data. The pipeline must perform complex data cleaning and feature engineering. Which TWO capabilities of Snowpark are specifically designed to support these transformations? (Choose two.)

Medium
133

A data engineer is configuring a Snowpipe to continuously load new files from an external stage backed by Google Cloud Storage. The pipeline must ingest files within seconds of their arrival. The engineer notices that files are not being loaded and that no errors appear in the pipe's copy history. The pipe was created with AUTO_INGEST = TRUE, and the notification channel is configured. Which Snowflake feature should the engineer verify to ensure that Snowpipe receives event notifications from GCS?

Medium
134

A data engineer notices that a dashboard query returns instantly on repeat executions but takes much longer the first time each morning. No data has changed overnight. Which Snowflake feature is most directly responsible for the fast repeat executions?

Easy
135

A financial services company stores transaction records in a Snowflake table that includes a column named 'SSN'. The data engineering team has been asked to implement a governance control that automatically detects and tags any column containing Social Security Numbers across the entire account, without manually inspecting every table. Which Snowflake feature should the team use to achieve this requirement?

Medium
136

A data engineer must load semi-structured JSON from an external stage where each file contains a top-level array of objects. The engineer wants each object in the array to become one row, with each object's keys exposed as columns. Which file format option should be configured?

Easy
137

A company requires that all data moved into Snowflake be encrypted at rest within the target table. How does Snowflake handle this requirement?

Medium
138

What is the primary function of a 'Tag' in Snowflake's governance framework?

Easy
139

A data engineer manages a Snowflake account where a transient table named STAGE_EVENTS is loaded hourly. To cut storage costs, the engineer plans to set DATA_RETENTION_TIME_IN_DAYS = 0 on this transient table. Which outcome will occur?

Medium
140

A data engineer creates a zero-copy clone of a 5 TB permanent table to provide a development team with a copy of production data. Immediately after the clone is created, how is storage billed for the clone?

Easy
141

A data engineer is setting up a Snowpipe to automatically ingest data from an external stage (Google Cloud Storage) into a Snowflake table. The engineer wants to minimize latency and ensure that files are loaded as soon as they are available. Which Snowpipe configuration should be used?

Easy
142

A data engineer is loading a large number of small JSON files into a Snowflake table using the COPY command. The process is taking much longer than expected. Which action is most likely to resolve the performance bottleneck?

Medium
143

Which of the following is true when considering the order of operations for policy application in Snowflake?

Medium
144

An account administrator is reviewing storage usage and notices that a table with 1 TB of active data is actually consuming 5 TB of total storage. The table has a clustering key and 90 days of Time Travel. What is the most likely reason for the 4 TB difference?

Hard
145

An organization wants to track PII data usage across the environment. Which Snowflake feature provides the most comprehensive audit trail of access to objects containing sensitive information?

Medium
146

A data engineer changes the DATA_RETENTION_TIME_IN_DAYS parameter for a schema from 1 to 30. How does this change affect the existing permanent tables within that schema?

Medium
147

Which of the following best describes the storage impact of zero-copy cloning a large table?

Medium
148

When migrating a large transformation from a legacy system to Snowflake, why is it recommended to prioritize ELT over ETL?

Medium
149

A data engineer is optimizing a Snowflake environment for a data warehouse that experiences high concurrency during business hours. The engineer observes that many queries are small and frequent, and the warehouse is often queued. The engineer wants to reduce queueing and improve throughput without increasing cost significantly. Which two actions should the engineer take? (Choose two.)

Hard
150

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?

Hard
151

A data engineer runs a long-running aggregation query and inspects the Query Profile. The profile shows a single operator with an output row count roughly 400 times larger than its input row count, and the downstream operator is bottlenecked. Which action should the engineer take to resolve this?

Medium
152

A company is using a Multi-cluster Warehouse (MCW) with a 'Standard' scaling policy. Users report that during peak morning hours, query queuing occurs briefly before a new cluster starts. What is the effect of changing the scaling policy to 'Economy' in this scenario?

Medium
153

A data engineer runs a query on a Snowflake virtual warehouse that scans a 5 TB table but returns only 100 rows. The Query Profile shows that the TableScan operator processed 5 TB of data even though a highly selective filter on a non-clustered column was applied. The warehouse is sized Medium. Which action will most effectively reduce the amount of data scanned by this query?

Medium
154

A data engineer is transforming a large fact table using a complex SQL query that includes multiple window functions and joins. The query is executed frequently and must return results with minimal latency. The engineer notices that the query spends significant time on repartitioning data for window functions. Which Snowflake feature should be used to improve performance by pre-organizing the data to avoid repartitioning?

Hard
155

A data engineer wants to share a subset of data with a third party while ensuring sensitive columns are masked. Which governance combination is best?

Hard
156

A company clones a 5 TB permanent table named RAW_CLICKS into a development schema using CREATE TABLE DEV.RAW_CLICKS CLONE PROD.RAW_CLICKS. Immediately after the clone, the development team loads 500 GB of new data into DEV.RAW_CLICKS and also updates 100 GB of existing rows. Which statement correctly describes the storage charges that result?

Hard
157

A Snowflake account uses Enterprise Edition. A permanent table FACT_ORDERS with DATA_RETENTION_TIME_IN_DAYS set to 14 is dropped at 10:00 on Monday. On Wednesday at 10:00, the engineer runs UNDROP TABLE FACT_ORDERS. Immediately after the UNDROP, what is the DATA_RETENTION_TIME_IN_DAYS value for the restored table?

Hard
158

Which Snowflake edition is required to support a 90-day Time Travel retention period?

Easy
159

Which approach is most effective for managing governance policies across a large, multi-schema data warehouse environment?

Medium
160

Which function is best suited for converting a string representation of a JSON object into a VARIANT type within a transformation query?

Medium
161

A data engineer is transforming semi-structured event logs stored in a VARIANT column named event_data. The column contains an array of objects under the key 'items', where each object has a 'product_id' and a 'quantity'. The engineer needs to produce one row per product per event, with the event's timestamp and user ID repeated. Which SQL construct should be used to achieve this transformation?

Medium
162

A data engineer is transforming a large table with a VARIANT column that contains nested arrays. The goal is to produce one row per element in the array, preserving all other columns. The engineer uses the FLATTEN function with the LATERAL keyword. Which behavior should the engineer expect when the VARIANT column contains an empty array?

Hard
163

A data engineer loads CSV files into a Snowflake table using COPY INTO from an internal stage. Some rows fail validation because a numeric column contains non-numeric text. The engineer wants the load to continue and capture the rejected rows for later analysis. Which approach should be used?

Medium
164

A data engineer is investigating a slow query that scans a large table. The Query Profile shows that the table scan is reading a very high number of micro-partitions compared to the total number of partitions in the table. The query filters on a column that is not the clustering key. What is the most likely explanation for the high number of partitions read?

Easy
165

A data engineer notices that a recurring daily query that joins a large fact table with a date dimension is taking longer than expected. The query filters on a date range and joins on a date key. The fact table is clustered by date_key, and the date dimension is small. The query profile shows a Cartesian join warning. Which action should the engineer take to resolve the Cartesian join and improve performance?

Medium
166

A data engineer is designing a storage strategy for a Snowflake account. The engineer wants to reduce Fail-safe storage costs and must understand which table types are exempt from Fail-safe. (Choose two.)

Hard
167

A data engineer runs a transformation that loads a 50 GB staged file into a target table using a COPY INTO statement with a single large file. The warehouse is a MEDIUM multi-cluster warehouse. The load takes far longer than expected because one node processes the entire file. Which change most directly improves load throughput?

Hard
168

What is the primary purpose of the 'VALIDATION_MODE' parameter in the COPY INTO <table_name> statement?

Easy
169

Which TWO of the following are true regarding the use of Snowflake Data Classification?

Medium
170

A data engineer is working with a table that contains a column of type VARCHAR storing JSON strings. The engineer needs to transform this column into a VARIANT type to leverage Snowflake's semi-structured data functions. Which function should be used to convert the VARCHAR column to VARIANT?

Easy
171

A data engineer must unload query results from a Snowflake table into a named internal stage so another team can download the files. The engineer wants the unloaded data to be encodable in a columnar format. Which command should be used?

Easy
172

What is the primary benefit of using Snowflake's Object Tagging for cost attribution?

Medium
173

An organization wants to track all data access in their Snowflake account for compliance. They need to know which columns were accessed by which queries, and they want to retain this information for at least one year. Which Snowflake feature should they use to meet this requirement?

Medium
174

A data engineer manages a transient table named STG_EVENTS with DATA_RETENTION_TIME_IN_DAYS set to 0 in a Snowflake Enterprise Edition account. A batch job accidentally truncates the table at 02:00. The engineer attempts to recover the lost rows using Time Travel queries against the table's historical versions but finds no historical data available. Which statement explains why the historical data is unavailable?

Medium
175

A data engineer is optimizing a query that aggregates a large sales table by product category and region. The query currently uses a GROUP BY on two high-cardinality columns and produces a large intermediate result set. The query profile shows significant network traffic and remote spilling. The engineer wants to reduce remote spilling without changing the aggregation logic. Which approach is most likely to achieve this?

Hard
176

Which of the following describes the purpose of the 'Result Cache' in Snowflake?

Medium
177

A data engineer is using Snowflake's Time Travel feature to recover a table that was dropped 2 days ago. The table was a permanent table with DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer attempts to use UNDROP TABLE but receives an error. What is the most likely reason for the failure?

Hard
178

A data engineer maintains a transient table named RAW_EVENTS in a Snowflake Enterprise Edition account. The table currently has DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer attempts to run ALTER TABLE RAW_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 14; and the statement fails. What is the reason for the failure?

Medium
179

A data engineer is building a transformation pipeline that must read semi-structured events from a stage, parse them, and write the output to a target table. The pipeline will run on a schedule and must be version-controlled and testable like other software artifacts. The team wants to avoid writing SQL scripts that are hard to unit test. Which Snowflake feature should the engineer use to implement this transformation?

Medium
180

A data engineer is using COPY INTO to load data from an external stage into a table. The source files contain a column with values like '00123' that must be stored as a VARCHAR to preserve leading zeros. The target column is defined as VARCHAR(10). During the load, the engineer notices that the leading zeros are being stripped and the values are stored as '123'. What is the most likely cause?

Hard
181

A data engineer is analyzing a Query Profile for a query that joins three large tables. The profile shows that the optimizer chose a broadcast join for one of the joins, but the broadcasted table is 50 GB. The query is spilling to remote disk. Which action is most likely to improve performance by changing the join strategy?

Hard
182

When unloading data from Snowflake to an external stage, which TWO of the following are supported file formats?

Hard
183

An analytics team wants to let external partners query a curated set of rows from a Snowflake table through a secure share. The partners use their own Snowflake accounts and must not be able to see any rows outside the curated set. Which approach should the data engineer use?

Easy
184

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?

Medium
185

A Data Engineer is using the COPY INTO <table_name> command to load Parquet files. The source files contain new columns that do not yet exist in the target Snowflake table. Which TWO features or settings should be used to handle this automatically? (Select TWO)

Hard
186

A data engineer notices that a dashboard query is running slowly. The query filters a large table on a column with high cardinality and returns a small number of rows. The engineer wants to improve performance for this specific query pattern. Which Snowflake feature is most appropriate?

Easy
187

A financial services firm must enforce a policy that only users with the role 'COMPLIANCE_OFFICER' can view rows where the 'ACCOUNT_STATUS' column equals 'DELINQUENT' in the 'LOANS' table. All other users should see only non-delinquent rows. Which Snowflake feature should the data engineer implement to meet this requirement?

Medium
188

A data engineer needs to filter the results of a complex analytical query based on the result of a window function. The query calculates a rolling average of sales per region and should only return rows where the current sale exceeds that average. Which SQL clause is most efficient for this transformation?

Medium
189

A data engineer is working with a table that stores customer orders in a VARIANT column named order_details. The order_details column contains an array of line items under the key 'items'. Each line item is an object with keys 'product_id', 'quantity', and 'price'. The engineer needs to produce a flattened result set where each row represents a single line item with its associated order ID. Which Snowflake function should be used to achieve this transformation?

Medium
190

A data engineer is reviewing a Query Profile for a query that filters a large table on a column with a very high cardinality, such as a UUID. The query applies an equality predicate on that column and returns a single row. The table is not clustered on that column. Which Snowflake feature is designed to accelerate this type of highly selective point lookup?

Easy
191

A data engineer is managing a Snowflake account with Enterprise Edition. A permanent table named FINANCE_RECORDS has DATA_RETENTION_TIME_IN_DAYS set to 10. The engineer wants to reduce storage costs by lowering the retention period to 1 day. What is the effect of executing ALTER TABLE FINANCE_RECORDS SET DATA_RETENTION_TIME_IN_DAYS = 1?

Medium
192

A data engineer is working with a table that has a column `order_date` of type DATE. The engineer needs to create a new column that contains the year and month in the format 'YYYY-MM' (e.g., '2023-01') for each order. Which Snowflake function should be used to produce this formatted string?

Easy
193

A healthcare company implements a Row Access Policy (RAP) on a PATIENTS table to restrict doctor access to only their assigned patients. The RAP references a mapping table. What is the most critical performance consideration when designing this policy for a table with billions of rows?

Hard
194

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

Hard
195

A data steward at a financial services company needs to automatically detect and tag columns containing Social Security numbers across all schemas in the PROD database. The steward wants the tagging to be applied without manually inspecting each table and to leverage Snowflake's built-in classifiers. Which approach should the steward use?

Medium
196

A company requires continuous ingestion of JSON logs from an S3 bucket into a Snowflake table with minimal latency. They decide to use Snowpipe with auto-ingest. How does Snowflake determine which new files need to be processed once the pipe is created?

Medium
197

What is the effect of the UNDROP command on a table that has already been recreated with the same name?

Medium
198

A data engineer needs to ensure that PII data in the 'SALES' table is obscured for non-admin users while maintaining original data types for downstream analytical models. Which approach provides the most scalable governance?

Medium
199

A query is filtering a table based on a 'TRANSACTION_DATE' column using a range (e.g., BETWEEN '2023-01-01' AND '2023-01-31'). The table is 500GB and not explicitly clustered. Why might this query still perform well and show good partition pruning?

Medium
200

A Data Engineer needs to transform semi-structured JSON data loaded into a VARIANT column named 'raw_data'. The goal is to flatten the 'items' array into individual rows while preserving the 'order_id' from the root level. Which function is the most efficient choice for this transformation?

Medium
201

Which TWO statements describe the benefits of using Snowflake's 'Change Tracking' for data transformation?

Medium
202

A data engineer is working with a table that has a VARIANT column containing JSON objects. Some objects have a key 'discount' with a numeric value, while others have it as a string, and some lack the key entirely. The engineer needs to produce a numeric column 'discount_amount' where missing keys are treated as 0 and string values are cast to numbers. Which expression correctly achieves this?

Hard
203

A data engineer runs a dashboard query that aggregates sales by region for the current month. The query scans a large fact table but returns only a few rows. The Query Profile shows that most time is spent scanning micro-partitions that do not contain the current month's data. Which feature should the engineer use to improve performance for this recurring query?

Easy
204

A user runs a query twice in succession with no data changes. The second query completes in near-zero time. What feature is responsible for this performance?

Easy
205

A data engineer needs to identify all columns across a multi-database Snowflake account that have been assigned the 'PII_Type' tag to ensure compliance with a new privacy regulation. Which approach provides the most comprehensive and efficient result for this account-level audit?

Medium
206

Which object allows a data engineer to assign a security policy based on a user's geographical location attribute?

Medium
207

A data engineer is configuring a Snowpipe to automatically load Parquet files from an external stage. The files are partitioned by date in the path (e.g., dt=2023-10-01/). The engineer wants to ensure that Snowpipe loads only new files and avoids reprocessing old ones, even if files are added to existing partition paths. The pipe definition includes a PATTERN option. Which approach best ensures that only new files are ingested and that previously loaded files are not reprocessed?

Hard
208

A data engineer needs to transform a string column containing dates in the format 'YYYY-MM-DD' into a DATE type. The column may contain NULL values and occasionally invalid date strings. The engineer wants the transformation to return NULL for invalid strings without causing the query to fail. Which function should the engineer use?

Easy
209

Which of the following describes the primary purpose of Snowflake's Fail-safe storage?

Easy
210

A data engineer needs to move a large volume of archived data from an on-premises system into a Snowflake internal stage and then load it into a permanent table. The data must be protected against accidental deletion for at least 30 days. The account is Snowflake Enterprise Edition. Which configuration best meets these requirements?

Medium
211

When designing a clustering key for a table that is frequently joined with other tables, which strategy generally provides the best performance for the join operation?

Medium
212

A data engineer needs to ingest files from an S3 bucket into Snowflake as soon as they are uploaded. The files arrive every few minutes and are generally smaller than 50MB. Which approach provides the most cost-effective and low-latency solution for this requirement?

Medium
213

A data engineer needs to categorize columns across multiple databases with custom business tags such as COST_CENTER and DATA_OWNER, and then enforce that only users with the TAG_ADMIN role can modify those tags. Which Snowflake feature should the engineer use to meet this requirement?

Medium
214

A retail company has a Snowflake account with many databases and schemas. The data governance team needs to discover all columns that contain personal data such as names, email addresses, and phone numbers, and automatically assign a system tag so that a masking policy can be applied later. They want to minimize manual effort and ensure the classification is consistent. Which Snowflake feature should they use to achieve this?

Medium
215

A data engineer is configuring continuous ingestion of new event files from an external Amazon S3 stage into a Snowflake table. The files arrive frequently and the engineer wants Snowflake to load them automatically without building an external orchestrator. The stage already has a storage integration attached. Which Snowflake object should the engineer create to accomplish this?

Easy
216

A data engineer needs to ensure that a column containing credit card numbers is masked for all users except those with the PAYMENT_ADMIN role. The masking should be applied consistently across all tables that use a specific tag. Which Snowflake feature should the engineer use?

Easy
217

A data engineer is reviewing storage costs in a Snowflake account. The engineer notices that a permanent table named CUSTOMER_DIM has a DATA_RETENTION_TIME_IN_DAYS value of 90, but the account is on Standard Edition. The engineer wants to confirm whether this is valid. What is the maximum Time Travel retention period for a permanent table in a Standard Edition account?

Easy
218

What is the primary benefit of using a file format object in Snowflake when dealing with multiple stages and load jobs?

Hard
219

A data engineer is tasked with implementing a data governance strategy that includes classifying sensitive data and applying tags. The engineer plans to use Snowflake's Data Classification and tag-based masking. Which two statements are true regarding the interaction between Data Classification and tags? (Choose two.)

Hard
220

A data engineer is implementing row access policies to enforce data segregation for a multi-tenant application. The table ORDERS contains a column TENANT_ID. The engineer creates a row access policy that uses a mapping table TENANT_MAPPING to associate users with their allowed TENANT_ID values. After applying the policy, the engineer notices that queries against ORDERS are returning no rows for some users who should have access. The mapping table is correctly populated. What is the most likely cause of the issue?

Hard
221

A data engineer manages a Snowflake account with a database named PROD_DB. The database contains a schema SALES with a table ORDERS. The engineer wants to clone the entire PROD_DB database to a new database named DEV_DB for testing. The PROD_DB database is large, but the engineer needs the clone to be available immediately. Which statement accurately describes the storage consumption and availability of the cloned database?

Hard
222

A data engineer needs to load data from an Azure Blob storage container into Snowflake. The organization requires a secure connection that does not use public endpoints. What should the engineer configure?

Medium
223

A data engineer is managing a Snowflake account where a transient table named STG_ORDERS is created in a schema. The table holds intermediate ETL results. The engineer wants to ensure that if the table is accidentally dropped, it can be recovered within 24 hours without incurring Fail-safe storage costs. What should the engineer do?

Medium
224

A data engineer is tuning a query that joins two large tables. The query is performing a 'Remote Disk Spilling' operation. Which optimization strategy is most effective to resolve this?

Medium
225

Which approach is most efficient for transforming a large volume of data in Snowflake when the logic requires complex window functions and stateful processing?

Medium
226

A data engineer runs a COPY INTO statement that loads 120 files from an external S3 stage into a target table. The LOAD_UNCERTAIN_FILES option was not specified, and 42 files were already loaded by an earlier run that completed successfully. The engineer expects all 120 files to be reprocessed because the target table was truncated before this run. What will Snowflake actually do, and why?

Hard
227

A data engineer maintains a very large fact table that is loaded nightly with the previous day's orders. Analysts run reports that almost always filter on ORDER_DATE and also aggregate by CUSTOMER_ID. The engineer wants search optimization to accelerate point lookups on ORDER_ID and CUSTOMER_ID without adding clustering maintenance overhead. Which configuration best meets these requirements?

Medium
228

A data engineer runs a nightly transformation that joins a 12 TB fact table to a 300 GB dimension table. The fact table is clustered by DATE_KEY, and the dimension is small enough to fit in memory. Query Profile shows the join operator building a hash table on the 12 TB side and spilling to remote disk. The engineer wants to eliminate the remote spill without increasing warehouse size. Which action should the engineer take?

Hard
229

A data engineer must transform semi-structured event data stored in a VARIANT column named 'event_payload'. The payload contains an array under the key 'tags', and each element of the array is an object with keys 'name' and 'score'. The engineer needs to produce one row per tag element, preserving the original event ID and extracting the 'name' and 'score' values. Which Snowflake construct should be used to achieve this transformation?

Medium

Frequently asked questions

What does the scenario questions domain cover on the DEA-C02 exam?
scenario questions questions test whether you can apply the concept in context, not just recognise a definition.
How many questions are in this domain?
This page lists all 229 scenario questions questions in the DEA-C02 question bank. The actual exam draws from this domain proportionally to its weighting in the official exam blueprint.
What is the best way to practise this domain?
Start with a short focused session (10 questions) to identify gaps, then work through explanations. Repeat with a longer session once the weak areas feel solid.
Can I practise only scenario questions questions?
Yes — the session launcher on this page filters questions to this domain only. Choose any session length for inline explanations and scoring.