DEA-C02
Free DEA-C02 practice questions
10 questions from our DEA-C02 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.
Question 1 of 10, Data Movement
Analysts must query Apache Iceberg tables whose metadata is maintained by an AWS Glue Data Catalog, while Spark jobs keep writing to them. Snowflake should not take over table management. What should the engineer configure?
- AAn external volume and a catalog integration for Glue, then Iceberg tables that use that catalog Correct answer
- BA Snowflake-managed Iceberg table with CATALOG = 'SNOWFLAKE' and a task that copies from Glue
- CA Snowpipe that loads the Iceberg data files into standard Snowflake tables as soon as each Spark job commits a snapshot
- DA storage integration and an external table with TABLE_FORMAT = DELTA on the warehouse folder
Answer and explanation: Externally managed Iceberg tables use an external volume for storage access and a catalog integration (here AWS Glue) so Snowflake reads metadata that another engine maintains. TABLE_FORMAT = DELTA is for Delta Lake. A Snowflake-managed Iceberg table hands management to Snowflake, which conflicts with Spark writing through Glue. Loading data files into standard tables duplicates data and ignores deletes recorded in Iceberg metadata.
Question 2 of 10, Data Movement
A team receives Parquet files whose schema changes often as new fields appear. They want the target table created automatically from the files and new columns added automatically on later loads. Which combination meets this requirement?
- ACREATE TABLE ... USING TEMPLATE over INFER_SCHEMA output, then ENABLE_SCHEMA_EVOLUTION = TRUE with MATCH_BY_COLUMN_NAME on COPY Correct answer
- BAn external table with AUTO_REFRESH = TRUE and a view that selects each new field with $1:field_name syntax
- CCREATE TABLE ... AS SELECT from the stage, then a stream on the table to detect new fields and ALTER it
- DA VARIANT landing table loaded with STRIP_OUTER_ARRAY, followed by a scheduled task that runs ALTER TABLE ADD COLUMN for new fields each week
Answer and explanation: INFER_SCHEMA with USING TEMPLATE builds the table definition from the files, and table schema evolution adds new columns during COPY when MATCH_BY_COLUMN_NAME is used. An external table plus a view needs manual view edits for each field. A stream on the table cannot see fields that were never loaded. A VARIANT landing table avoids the problem but does not produce typed columns automatically and STRIP_OUTER_ARRAY applies to JSON.
Question 3 of 10, Data Movement
A Snowpark Python procedure needs to call a partner's REST API to enrich rows during a load. Outbound traffic must be limited to that one host. What must be configured?
- AA storage integration whose STORAGE_ALLOWED_LOCATIONS lists the partner's API URL as an allowed location
- BA network policy with ALLOWED_IP_LIST set to the partner's IP addresses
- CAn API integration with API_ALLOWED_PREFIXES and an external function proxy service
- DAn egress network rule for the host plus an external access integration referenced by the procedure Correct answer
Answer and explanation: External network access from UDFs and procedures requires an egress network rule naming allowed hosts and an external access integration referenced in the function definition. Storage integrations govern stage access. Network policies restrict inbound client connections to Snowflake. External functions with API integrations go through a cloud proxy service rather than letting procedure code call the host directly.
Question 4 of 10, Performance Optimization
A warehouse runs mostly small queries, but a few ad hoc queries scan huge tables with selective filters and take 20 minutes. The team wants those outliers faster without resizing the warehouse for everyone. Which feature fits BEST?
- AA Snowpark-optimized warehouse of the same size so that each node has far more memory available
- BA multi-cluster warehouse with the economy scaling policy and two extra clusters
- CSearch optimization ON SUBSTRING on all string columns of the scanned tables
- DQuery acceleration service on the warehouse with an appropriate maximum scale factor Correct answer
Answer and explanation: The query acceleration service offloads eligible scan and filter work of outlier queries to serverless compute, scaled up to the configured factor, so the warehouse size can stay small. Extra clusters handle concurrency, not single long queries. Snowpark-optimized warehouses add memory per node for ML-type workloads at higher cost. Substring search optimization targets LIKE lookups and costs storage and maintenance on every column.
Question 5 of 10, Performance Optimization
Support engineers run frequent lookups such as WHERE order_uuid = '...' against a 2-billion-row table that is clustered by order_date. Each lookup scans thousands of partitions. Which feature should be enabled?
- AQuery acceleration with a high QUERY_ACCELERATION_MAX_SCALE_FACTOR value
- BSearch optimization on the order_uuid column for equality predicates Correct answer
- CA second clustering key on order_uuid alongside the order_date key
- DA materialized view on the table that selects every column ordered by UUID
Answer and explanation: Search optimization builds a search access path that prunes micro-partitions for highly selective equality lookups on high-cardinality columns without changing the clustering. A table supports only one clustering key, and adding a UUID to it would hurt date pruning. Materialized views cannot be ordered and would duplicate the whole table. Query acceleration adds compute but does not avoid scanning thousands of partitions.
Question 6 of 10, Storage and Data Protection
A staging table is rebuilt every hour and its data can be regenerated from source files. The team wants to minimize storage cost from historical data. Which table type is BEST?
- AA transient table with DATA_RETENTION_TIME_IN_DAYS = 0 Correct answer
- BA permanent table with DATA_RETENTION_TIME_IN_DAYS = 0
- CA temporary table created at the start of every session
- DA permanent table with a 1-day retention and no clustering
Answer and explanation: Transient tables have no Fail-safe, and with zero Time Travel they keep no historical data at all, which suits reproducible staging data shared across sessions. A permanent table still incurs 7 days of Fail-safe even with zero retention. Temporary tables vanish when the session ends, which breaks hourly jobs that use other sessions. A 1-day permanent table keeps both Time Travel and Fail-safe history.
Question 7 of 10, Data Governance
A data quality team wants Snowflake to compute the null count of customer_id and the row count of the orders table every hour and alert when nulls appear. Which approach uses built-in features?
- ACreate a masking policy that raises an error whenever customer_id is NULL in a query
- BAssociate system data metric functions with the table on a DATA_METRIC_SCHEDULE, then alert Correct answer
- CDefine a row access policy that hides rows with NULL customer_id from reporting roles
- DAdd a NOT NULL constraint and rely on Snowflake to log every violation to a quality table
Answer and explanation: Data metric functions such as SNOWFLAKE.CORE.NULL_COUNT and ROW_COUNT run on the table's DATA_METRIC_SCHEDULE and record results in the data quality monitoring results view, where an alert can act on them. Masking policies cannot raise errors on data quality. NOT NULL is enforced on insert but does not log or alert. Row access policies hide problems rather than measure them.
Question 8 of 10, Data Transformation
A raw_orders table receives inserts, updates and deletes. A downstream job must apply only the net changes since its last run to a curated table, then advance its position so the same changes are not processed twice. What should the engineer use?
- AAn append-only stream on raw_orders read by a SELECT in a task, which advances the offset as soon as the query finishes
- BA standard stream on raw_orders consumed by a MERGE inside a task, which advances the stream offset when the DML commits Correct answer
- CA CHANGES clause query on raw_orders that records the last processed timestamp in a control table between task runs
- DA dynamic table over raw_orders with TARGET_LAG = DOWNSTREAM that the job reads after each scheduled refresh completes successfully
Answer and explanation: A standard stream records inserts, updates and deletes, and consuming it in a committed DML statement such as MERGE advances its offset. An append-only stream ignores updates and deletes, and a plain SELECT does not advance any offset. The CHANGES clause works but requires custom offset bookkeeping. A dynamic table holds a full result, not a consumable change set.
Question 9 of 10, Data Transformation
A MERGE from a standard stream into a target table processes deletes incorrectly: rows deleted in the source remain in the target. The MERGE has WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT. What is missing?
- AA WHEN MATCHED AND METADATA$ACTION = 'DELETE' AND NOT METADATA$ISUPDATE THEN DELETE clause Correct answer
- BAn append-only stream instead of a standard stream so that delete rows reach the MERGE
- CSHOW_INITIAL_ROWS = TRUE on the stream so that deleted rows are replayed into the target
- DA WHEN NOT MATCHED BY SOURCE THEN DELETE clause, which removes rows that are missing from the stream
Answer and explanation: Deletes appear in the stream as DELETE rows with ISUPDATE false, and the MERGE needs a matched branch that deletes on that condition (placed before the update branch). A NOT MATCHED BY SOURCE approach would delete every target row that did not change. Append-only streams drop deletes entirely. SHOW_INITIAL_ROWS returns the initial table contents on first read.
Question 10 of 10, Data Transformation
A type 2 slowly changing dimension must close the current row and insert a new version when an attribute changes, driven by a standard stream on the source. What is a common Snowflake pattern?
- AA dynamic table with REFRESH_MODE = FULL that recomputes history from the current source table
- BA MERGE fed by the stream's changes twice: one copy expires the current row, one inserts the new version Correct answer
- CA materialized view over the source that keeps all versions of each key automatically as rows change over time
- DAn append-only stream with INSERT only, relying on the latest row being chosen by queries later
Answer and explanation: Because a MERGE row can either update or insert for a key, SCD2 typically feeds the MERGE with the changes twice (one set matching to expire the current row, one set not matching to insert the new version). Insert-only appends do not expire old rows. Materialized views cannot keep history. Recomputing from the current source loses prior versions.