Free DEA-C02 practice test: 30 questions with answers

Snowpipe with auto-ingest loads files as they arrive using event notifications, so it is continuous and serverless. Nightly jobs, manual PUTs and polling procedures add latency and operational work.

COPY INTO loads files in bulk using warehouse compute and file format options. Selecting from a stage does not load data, row inserts are slow for bulk loads, and views do not store data.

ON_ERROR CONTINUE loads valid rows and skips bad ones, and load history or validation shows the errors. Aborting on the first error stops the whole load, deleting files loses evidence, and turning off history removes visibility.

A stream records changes on a table, which downstream tasks use to process only what changed. Clustering keys improve pruning, search optimization speeds lookups, and zero-copy clones copy metadata rather than tracking changes.

A scheduled task with a WHEN condition on SYSTEM$STREAM_HAS_DATA runs only when there is new data, which avoids wasted compute. Running every second wastes credits, views do not schedule, and unscheduled procedures never run automatically.

COPY INTO a stage location unloads query results to files in the chosen format, including Parquet. Creating tables, showing tables and downloading to a laptop do not export to cloud storage in the required format.

A clustering key co-locates rows with similar values, which improves micro-partition pruning for filters on those columns. Larger warehouses do not change pruning, views do not store data, and CSV exports make queries slower.

Search optimization accelerates selective lookups such as equality on high-cardinality columns. A clustering key on a low-cardinality column does not help point lookups, and warehouse size and clones do not change the lookup strategy.

Query Profile shows operator-level timings, partition pruning and data volumes for a query. Billing dashboards, user lists and network policies do not show query execution detail.

A multi-cluster warehouse adds clusters when queries queue, which absorbs concurrency. A single-node suspended warehouse, more tasks and disabled auto-resume do not solve queueing.

Auto-suspend stops idle compute and auto-resume restarts it when needed, which saves credits without blocking jobs. Running at maximum size, permanently off warehouses and a new warehouse per query increase cost or break the workflow.

Result caching returns results for identical queries when data is unchanged, and materialized views precompute repeated expensive results. Full scans, disabled caching and oversized warehouses all increase cost.

Stored procedures can contain multi-step logic with conditions and loops in several supported languages. Materialized views store query results, file formats describe files, and stages hold files.

The colon path notation extracts a nested value and the double-colon cast converts it to a type. LOWER does not extract nested fields, GROUP BY groups rows, and SHOW lists objects.

Snowpark lets developers write DataFrame-style code in Python, Java or Scala that executes in Snowflake. Time Travel, Fail-safe and zero-copy cloning are data protection or storage features.

A dynamic table defines a query whose results are refreshed to meet a target lag. File formats, stages and resource monitors do not maintain query results.

A temporary table exists only for the session, which suits intermediate results. Permanent tables persist, external tables point at external files, and materialized views maintain results rather than session scratch space.

Renaming changes metadata only and keeps the data in place. Copying, exporting and cloning the whole database are unnecessary and slower.

A masking policy applied to the column returns masked or real values based on the role, which enforces the rule for every query. A view without conditions, a file format and warehouse sizing do not control column visibility.

A row access policy filters which rows each role can see based on conditions such as ownership. Clustering keys improve pruning, resource monitors track credits, and streams record changes.

ACCESS_HISTORY records which objects were accessed and by whom, which supports audit. Clustering keys, stages and resource monitors do not record object access.

A masking policy with a role condition enforces the restriction at query time for every user. Comments, naming conventions and disabled query history do not enforce access.

A secure view exposes only the aggregated result and hides the underlying definition and data, which limits access appropriately. A direct grant on the detailed table, a stage and an unrestricted temporary table expose more than needed.

Masking policies and row access policies limit what each role sees, which protects personal data. Shared logins, unrestricted copies and disabled monitoring all weaken protection.

Time Travel with UNDROP restores a dropped table within the Time Travel retention period. Fail-safe is for emergency recovery by Snowflake, clones after the drop cannot recover the original, and reloading from CSV may lose data.

Fail-safe provides a limited recovery period after Time Travel, and only Snowflake can use it for emergency recovery. Result caching, auto-suspend and search optimization are not recovery features.

A zero-copy clone shares storage initially, and changes to the clone are stored separately so the source is unaffected. Cloning does not copy data physically at creation, clones are not limited to external tables, and they can be created in the same schema.

A zero-copy clone creates an instant copy that shares storage until changes are made, which suits test environments. CSV exports, full restores and spreadsheets are slower and less practical.

DATA_RETENTION_TIME_IN_DAYS controls how long Time Travel history is kept, so setting it to 30 keeps 30 days of history. Warehouse suspension, task counts and stage file limits do not control history retention.

Time Travel allows querying past states within the retention period, and the retention can be set per object within the limits of the edition. It is not unlimited for all editions, users cannot restore on their own after the retention period, and dropped tables can be recovered within the period.