Free DEA-C02 practice test

Correct (B - Standard stream): A standard stream on a table captures all DML changes: inserts, updates, and deletes. It is the default stream type for tables. | Correct (E - Append-only stream): An append-only stream on a table captures only INSERT operations, making it more efficient for use cases where only new rows matter (e.g., event log tables). | Wrong (D - Insert-only stream): Insert-only streams exist in Snowflake but are specifically for EXTERNAL TABLES, not standard internal tables. Confusing insert-only (external tables) with append-only (internal tables) is a common misconception. | Wrong (A - Delete-only stream): No such stream type exists in Snowflake. | Wrong (C - Update-only stream): No such stream type exists in Snowflake. All change capture types are bundled into the standard stream.

(A) is correct: append-only streams track only INSERT operations. UPDATEs and DELETEs are invisible to the stream, making them unsuitable for CDC on mutable tables. (D) is correct: because append-only streams skip change tracking overhead for updates/deletes, they are more performant and storage-efficient for immutable/append-only workloads like event logs. — (C) is wrong: this description fits a standard stream's behavior for pre/post-image rows, not an append-only stream; an append-only stream simply omits non-insert changes entirely. (B) is wrong: insert-only streams (not append-only streams) are created on external tables; the terminology is distinct in Snowflake documentation. (E) is wrong as a distinguishing fact: while offset advancement is true of all stream types, it is not specific to append-only streams and is therefore not a defining characteristic.

Correct (C): The Snowflake Kafka Connector setting enable.schema.evolution (set to true) enables automatic schema evolution, allowing the connector to issue ALTER TABLE statements to add new columns to the target Snowflake table when a new field appears in an incoming Avro/JSON schema from the Schema Registry. | Wrong (D): SCHEMA_EVOLUTION_MODE is not a valid Kafka Connector configuration key. It is a plausible-sounding distractor but does not exist in the official connector configuration. | Wrong (A): snowflake.schema.evolution follows the connector's typical property prefix naming convention but is not the actual property name—the correct one uses the simpler enable.schema.evolution. | Wrong (B): auto.evolve.schema is not a recognized Snowflake Kafka Connector property, though it sounds intuitive. It may be confused with Schema Registry client settings used in other systems.

Option E is correct: If the refresh compute is underpowered, each incremental refresh takes longer than the target lag interval, causing lag to accumulate. Increasing the warehouse size speeds up individual refresh executions. Option A is correct: If the source table is not clustered on the predicate columns used during incremental refresh, Snowflake must scan more micro-partitions, making each refresh slower. Adding a clustering key reduces data scanned and speeds up refresh. Option B is incorrect: Reducing TARGET_LAG makes Snowflake attempt refreshes more frequently, but if each refresh already takes longer than the current lag window, reducing the target further will not fix the underlying performance problem and may worsen resource contention. Option D is incorrect: Switching to FULL refresh mode reprocesses the entire dataset on every cycle, which would be slower and increase lag further rather than reduce it. Option C is incorrect: ALLOW_OVERLAPPING_EXECUTION is a Task option, not a Dynamic Table option; Dynamic Tables do not expose this setting.

Correct (C): When a Dynamic Table's refresh query takes longer to execute than the target lag interval, lag will accumulate and consistently exceed the target. With 10 million rows arriving per minute and a 1-minute target, an undersized warehouse that cannot process that volume within the window is the most operationally plausible root cause.

Why the others are wrong: A – Snowflake Dynamic Tables do support incremental refresh for GROUP BY aggregations under certain conditions, and even when a full refresh occurs, Snowflake chooses the most efficient approach. The blanket statement that GROUP BY disables incremental refresh is incorrect. B – Snowflake supports target lag values as low as 1 minute (and even sub-minute in some tiers). '1 minute' is a valid target lag. D – Dynamic Tables do not require the user to create a Stream on source tables; Snowflake manages the change tracking internally. The absence of a manually created Stream does not cause this behavior.

A standard view stores only the query definition, always reflects current base data, incurs no maintenance credits, and supports arbitrary SQL including multi-table joins and window functions. Materialized views in Snowflake cannot contain joins or window functions and consume serverless maintenance credits — so options 2 and 3 are invalid. A dynamic table supports joins but is refreshed on a lag (not always current) and consumes refresh compute, which contradicts the no-maintenance-credit requirement.

In Snowflake, most DDL statements cause an implicit commit of any open transaction, so the preceding DML is permanently committed and a later ROLLBACK has nothing to undo. DDL is therefore not transactional here and is not rolled back. Snowflake does not reject DDL inside a transaction — it silently commits, which is the trap. Nothing is deferred to COMMIT time.

Snowflake transactions read their own uncommitted writes, so the SELECT sees bal after the -100 update. Snapshot isolation applies to other transactions' concurrent changes, not to the transaction's own modifications. A transaction cannot block itself, so no NULL or lock wait occurs. SELECT statements are freely allowed within explicit transactions.

TRY_CAST returns NULL when the conversion fails instead of raising an error, which is exactly the requirement. CAST and TO_NUMBER both raise a conversion error on 'N/A' and abort the statement. PARSE_JSON on 'N/A' raises an invalid JSON error, so it does not provide safe conversion either.

SUM(amount) OVER (PARTITION BY order_id) with no ORDER BY computes the total across the whole order partition, giving the correct denominator. Adding ORDER BY line_id implicitly creates a RANGE UNBOUNDED PRECEDING frame, producing a running total, not the order total. SUM OVER () totals the entire table, not each order. RATIO_TO_REPORT would work but requires PARTITION BY order_id; with ORDER BY order_id and no partition it computes the ratio over all rows.

Option D is correct: a stream becomes STALE when the source table's data retention period has been exceeded; the change records for the period before the stale point are gone and cannot be recovered. Option B is correct: the only way to resume a stale stream is to DROP and recreate it; recreation resets the offset to NOW (current table version), meaning historical changes are lost. Option E is correct: because the gap in CDC records cannot be replayed, the target table may be out of sync, so a full reload (or reconciliation) from the source is necessary to ensure consistency. — Option F is wrong: there is no SYSTEM$STREAM_BACKFILL() function in Snowflake; stale stream data cannot be reconstructed. — Option C is wrong: tasks consuming a stale stream raise an error (stream is stale), but the phrasing 'silently skip' is what they do NOT do — they do raise errors, which is partially true, but Option C's full implication that tasks always fail cleanly rather than having partial issues is misleading; more importantly Option C is not a remediation step and the set of three correct answers is B, D, E. — Option A is wrong: increasing DATA_RETENTION_TIME_IN_DAYS only affects future retention; it cannot retroactively extend the retention window that already elapsed and caused the stream to become stale.

Snowflake's result cache is global and account-wide — it is not tied to a specific warehouse. When any user runs an identical query (same SQL text, same role, same session parameters that affect results) within 24 hours and the underlying data has not changed, Snowflake serves the result from the result cache regardless of which warehouse the second user is on. The 2-second response from a different warehouse, with unchanged data, is the hallmark of a result cache hit. — Option B is wrong: the warehouse (SSD) cache is local to each warehouse's compute nodes. A different warehouse has its own SSD cache, which would be cold for this query, so it could not benefit from another warehouse's SSD warm state. — Option C is wrong: Snowflake compiles query plans deterministically for identical SQL; two warehouses of the same size would produce the same plan and perform equivalently without caching. A different plan alone cannot explain a 45x speedup. — Option D is wrong: a 3 TB fact table join query cannot be resolved from metadata alone; it requires actual data scanning.

SYSTEM$PIPE_STATUS provides real-time operational state of the pipe including whether it is paused, its last ingested timestamp, and the count of pending files—directly revealing gaps in ingestion activity. Checking the notification integration queue (SQS for AWS) confirms whether file arrival events were ever delivered to Snowflake, which is a common silent failure mode. Option B is wrong because PIPE_USAGE_HISTORY tracks credit consumption, not file-level ingestion status, and zero credits could simply mean no files arrived. Option C is wrong because the scenario states no explicit errors appear in COPY_HISTORY, so filtering for failures there would yield no diagnostic information. Option A is wrong because INFORMATION_SCHEMA.LOAD_HISTORY only covers the most recent 14 days and is scoped to COPY INTO operations, not Snowpipe; for a recent weekend it might technically be accessible but it does not cover Snowpipe loads. Option E is wrong because truncating and reloading destroys audit trail, may skip already-loaded files due to Snowpipe's deduplication by file path+size+modified date, and is not a diagnostic step.

Option E is correct: a larger warehouse provides more total memory across nodes, directly reducing the likelihood that intermediate sort buffers overflow to local SSD. Option B is correct: pushing a selective filter upstream reduces the cardinality of the data set that flows into the Sort operator, lowering its memory footprint and eliminating spill. — Option A is wrong: the result cache only benefits identical repeated queries; it does not affect the memory used during the current query's sort phase. — Option C is wrong: a clustering key pre-orders micro-partitions on disk, which can help range scans, but an explicit ORDER BY or GROUP BY sort in the query plan still requires in-memory sorting of the result set; the Sort node would still appear and could still spill. — Option D is wrong: USE_CACHED_RESULT = FALSE bypasses result reuse but does not allocate additional memory; it has no effect on spill behavior.

Option D is correct: the AT clause with TIMESTAMP accepts a timestamp expression, and DATEADD('hour', -72, CURRENT_TIMESTAMP()) computes the point 72 hours ago. Option B uses BEFORE instead of AT — BEFORE is valid but means 'just before the given point', and more importantly its syntax uses the same sub-clause keywords (TIMESTAMP, OFFSET, STATEMENT), so it would technically work, but the real issue is that AT is the appropriate and precise choice for 'exactly at' a timestamp, and the BEFORE keyword retrieves the state just before the timestamp, not at it — making it subtly wrong for an exact-snapshot audit requirement. Option A uses OFFSET, which takes an integer number of seconds relative to now (negative = in the past); -259200 seconds is indeed 72 hours, but this option is provided with the wrong unit label 'hour', making it a distractor that conflates OFFSET (seconds) with a time expression. Option C uses STATEMENT, which requires a query ID string (UUID), not a timestamp expression, making it invalid in this context.

Replication lag is directly influenced by how frequently the refresh is scheduled — a longer refresh interval means the secondary will be further behind the primary. Cross-cloud replication (e.g., AWS to Azure) inherently has more latency than cross-region replication within the same cloud provider due to data traversing different cloud networks. Option B is incorrect — secondary databases are always read-only by design; this is not a throttling mechanism affecting replication lag. Option C describes why each refresh might take longer but is a secondary factor; the primary driver of 'several hours behind' is the refresh interval, not individual DML volume. Option A is incorrect — REPLICATION_OPTIMIZATION_LEVEL is not a real Snowflake parameter; incremental replication is always used when available.

A FAILOVER GROUP is the correct construct when the requirement includes replication of non-database objects (such as users, roles, warehouses, resource monitors, integrations, network policies) AND the need for automatic client redirect during failover. A FAILOVER GROUP supports both databases and account-level objects and enables the secondary to be promoted to primary with minimal reconfiguration. Option A is wrong — DATABASE REPLICATION (the legacy approach) replicates only a single database and does not support non-database objects. Option B is partially correct — REPLICATION GROUPs do support multiple databases and some account objects, but FAILOVER GROUPs are the superset that also enables failover/client redirect capabilities, making them more appropriate for DR. Option D is wrong — Shares are for data sharing, not a replication mechanism for object types.

To load data (COPY INTO a table from a stage), the role needs USAGE on the database and schema to navigate the namespace, INSERT on the target table to write rows, and READ on the stage to access staged files. SELECT is NOT required for a load-only operation (option A adds unnecessary SELECT). Option D grants OWNERSHIP on the entire database, which violates least privilege significantly—OWNERSHIP implies all privileges on all child objects. Option C inheriting SYSADMIN is the worst violation of least privilege, as SYSADMIN has account-wide administrative capabilities far beyond what a loader needs.

Option B is correct because IS_ROLE_IN_SESSION() checks the entire active role hierarchy (including secondary roles), which is the recommended Snowflake function for masking policy conditionals. It correctly handles three tiers: full email for SUPPORT_TIER2, domain-only for SUPPORT_TIER1 (using REGEXP_REPLACE to strip the local part before '@'), and masked for everyone else. Option A uses CURRENT_ROLE(), which only returns the single currently active primary role and misses cases where SUPPORT_TIER2 or SUPPORT_TIER1 is an inherited secondary role—this is a common misconception. Option C only has two branches and hashes the email for non-TIER2 users rather than showing a domain or mask, which does not meet the requirement. Option D returns the full email for SUPPORT_TIER1 and masked for everyone else—it inverts the logic and does not handle SUPPORT_TIER2 at all.

BASE_OBJECTS_ACCESSED in the ACCESS_HISTORY view is the correct field for column-level read lineage. It contains a nested ARRAY of objects (including tables and views) along with the specific columns accessed within each object during query execution, enabling fine-grained auditing of which columns were read. Option A is incorrect — OBJECTS_MODIFIED tracks write operations (DML), not column-level reads, and joining to QUERY_HISTORY does not yield column-level projection details. Option D describes DIRECT_OBJECTS_ACCESSED, which captures the objects directly referenced in the query (e.g., the view in a FROM clause) but may not drill down to the underlying base table columns when views are involved — that is the key distinction from BASE_OBJECTS_ACCESSED. Option C describes a real field (OBJECT_MODIFIED_BY_CHILD_QUERIES) but it pertains to objects written by child queries, not column-level reads.