Free Databricks Data Engineer Associate practice test

Option A is correct: in Databricks architecture, the data plane is the customer's own cloud account where compute resources (clusters) run and where actual data files are stored in the customer's cloud object storage. The data never leaves the customer's account. Option D is wrong: the control plane refers to the Databricks-managed infrastructure (web application, job scheduler, cluster manager, etc.) that lives in Databricks' own cloud account, not where data resides. Option C is wrong: autoscaling boundaries govern how cluster node counts scale up or down and are unrelated to where data is stored. Option B is wrong: metastore federation is a Unity Catalog concept related to cross-workspace metadata sharing, not data residency.

DLT automatically manages checkpoints for all streaming sources in the pipeline. Checkpoint data — including Kafka partition offsets and any aggregation state — is stored in the pipeline's configured storage location. When the pipeline restarts, it reads the checkpoint to resume exactly where it left off. Option B is partially true (Kafka does retain consumer group offsets), but Spark Structured Streaming does NOT rely on Kafka's consumer group offset mechanism for resumption; it uses its own checkpoint files, making B insufficient and misleading. Option C is incorrect: the Delta transaction log tracks table-level write transactions, not Kafka read offsets; the checkpoint directory is separate from the transaction log. Option D is the opposite of what is needed — startingOffsets=latest would skip all messages that arrived during the pause, causing data loss.

When a watermark is defined with a delay threshold of 10 minutes, Spark drops any event whose event_time is earlier than (max observed event_time) - 10 minutes. An event arriving 15 minutes behind the current watermark exceeds this threshold and is silently dropped — it will not contribute to any window aggregate. Option A is incorrect: once a window is closed by the watermark, its aggregate is finalized in append mode and emitted; the engine will not update and re-emit a finalized window result for a late arrival. Option D describes a fabricated 'late-data buffer' mechanism that does not exist in Structured Streaming — the watermark IS the mechanism for handling late data, and data beyond the threshold is dropped, not buffered. Option C is incorrect: append mode IS compatible with windowed aggregations that use a watermark; the watermark is actually required to use append mode with aggregations, as it allows Spark to determine when a window result is final.

@dlt.expect_or_fail enforces the constraint as a hard stop: if any record violates the expectation, the entire pipeline run fails immediately. This is by design for scenarios where invalid data must never reach downstream tables. Option C describes the behavior of @dlt.expect_or_drop, which silently drops violating records and records their count in pipeline metrics. Option B describes a quarantine pattern that must be implemented manually by the engineer; DLT does not create quarantine tables automatically. Option A describes behavior similar to @dlt.expect, which allows violating records to pass through while logging the violation, but does not add any warning flag to the rows.

Option A is correct: OPTIMIZE compacts small files into larger, more efficiently sized Parquet files, which directly addresses the small-file problem caused by streaming micro-batches. Option C is also correct and closely related: Z-ORDER co-locates data within compacted files so that records with similar values of the Z-ORDER columns are physically near each other, enabling the Delta engine to skip more files during queries filtered on those columns. Option B is incorrect — OPTIMIZE does not delete old file versions; that requires a separate VACUUM command. Option D is incorrect — Z-ORDER is a multi-dimensional clustering technique, not a global sort; it co-locates similar values within files but does not produce a globally sorted table. Option E is incorrect — OPTIMIZE/Z-ORDER is a one-time operation on existing data; new files written by future streaming jobs are not automatically Z-ORDERed. Option F is partially correct in describing the benefit but is essentially a restatement of C — the mechanism (co-location enabling file skipping via statistics) is what C describes, so C captures the correct underlying outcome.

Correct: C. A LEFT JOIN (with orders as the left/driving table) returns all rows from orders and the matching rows from order_items. Where there is no match, NULL values are returned for the order_items columns — this correctly represents orders with no items. B is wrong because INNER JOIN only returns rows where a match exists in both tables; orders with no items would be excluded. D is wrong because RIGHT JOIN (orders RIGHT JOIN order_items) would return all rows from order_items and matching orders; orders with no items could still be lost. A is wrong because CROSS JOIN produces a Cartesian product of every row in both tables, which is not the desired behavior.

Option B is correct: WHEN MATCHED updates price and stock_qty, WHEN NOT MATCHED BY TARGET inserts new products, and WHEN NOT MATCHED BY SOURCE THEN DELETE removes products missing from the source, all in one atomic MERGE. Option A handles inserts correctly (WHEN NOT MATCHED is the same as BY TARGET) but zeroes stock for missing products instead of deleting them. Option C can never delete: in a MATCHED row the source key isn't NULL. Option D splits the delete into a separate statement, so the operation is no longer atomic, and UPDATE SET * overwrites more than the two required columns.

Correct: C. A Common Table Expression (CTE) defined with a WITH clause exists only for the duration of the single query in which it is defined — it has no persistence and no catalog entry. A is wrong because a global temporary view persists for the lifetime of the entire Spark session and is accessible across notebooks in the same cluster. B is wrong because a temporary view persists for the lifetime of the SparkSession (the entire notebook session), not just a single query. D is wrong because a permanent view is stored in the metastore and persists across sessions.

Correct: B. EXPLODE(answers) is a table-generating function that produces one output row per element in the array, exactly as required. A is wrong because FLATTEN merges an array of arrays into a single flat array — it does not generate new rows. C is wrong because TRANSFORM applies a lambda function to each array element and returns a new array of the same size — it does not explode the array into multiple rows. D is wrong because ARRAY_DISTINCT removes duplicate elements from an array and returns a new (possibly shorter) array — it also does not generate new rows.

The FILTER higher-order function takes an array and a lambda predicate and returns a new array containing only the elements for which the predicate returns true. Option B correctly filters the weights array to retain only values greater than 10.0. Option C uses TRANSFORM, which applies a function to each element and returns a new array of the same length — here it would return an ARRAY<BOOLEAN> of true/false flags, not the filtered weights. Option D uses AGGREGATE, which performs a fold/reduce operation and returns a scalar (in this case a sum of heavy weights), not an array. Option A uses EXISTS, which returns a single BOOLEAN indicating whether any element satisfies the predicate — not an array of matching elements.

Actions I and III represent best practices; II and IV do not. Action I is correct: using 'FAIL UPDATE' expectations stops the pipeline when critical data quality is violated, and pairing it with a job failure notification ensures the on-call team is immediately alerted—this is the recommended DLT observability pattern. Action III is correct: a SQL Alert monitoring row counts after each run catches 'silent failures' where the pipeline succeeds technically but produces empty or drastically reduced output, which expectations alone may not catch. Action II is wrong: using Development mode in production is a significant anti-pattern. Development mode does not automatically retry on failures and keeps the cluster alive between runs in a way not suited for production reliability; Production mode should always be used in production. Action IV is wrong: relying solely on driver logs requires someone to proactively check logs, which is not a scalable observability strategy; automated notifications are essential for production pipelines. Therefore, only Option B (I and III only) is correct.

Options A, B, and C are correct: Publishing a new version of the pipeline code causes DLT to restart the pipeline cluster to apply the changes (A). Spot instance preemption by the cloud provider is a common cause of unexpected cluster restarts in any Databricks job, including DLT pipelines (B). If a streaming query encounters a fatal unhandled exception and retries are exhausted, DLT will restart the cluster as part of its recovery mechanism (C). Option D is incorrect because DLT Continuous mode pipelines do not scale to zero when idle — the cluster remains running and polls for new data; autoscaling to zero is not a feature of DLT continuous pipelines. Option E is incorrect because Databricks Runtime updates on running clusters do not happen automatically mid-run; runtime version changes require the pipeline to be stopped and restarted manually by the user. Option F is incorrect because switching pipeline modes is a configuration change that requires the pipeline to be stopped first; you cannot toggle between modes on an actively running pipeline.

Option C is correct: Databricks Workflows provides a built-in 'Email notifications' section in the job settings where you can configure email addresses to be notified on job start, success, or failure — this is the standard mechanism for alerting on workflow failures. Option A is incorrect because cluster configuration panels do not contain notification settings; notifications are a job-level construct. Option B is incorrect because Databricks SQL Alerts are for query-result-based alerting in the SQL analytics context, not for Workflow job status notifications. Option D is incorrect because Unity Catalog audit logs capture governance events and are not a mechanism for operational alerting on job failures.

Git-backed Repos give version history, pull-request review, and branching so changes are reviewed before release. It doesn't train models (C), remove testing (B), or store output data (D).

Databricks Asset Bundles define resources (jobs, pipelines, notebooks) as code in YAML for consistent, repeatable deployment across environments. Hand-copying (B) isn't CI/CD, and VACUUM (C) and Delta Sharing (D) are unrelated.

VACUUM deletes files no longer referenced by the table beyond the retention threshold, reclaiming storage. OPTIMIZE (B) compacts, MERGE (D) upserts, and CREATE TABLE (C) makes a table.

OPTIMIZE compacts many small files into fewer larger ones, improving read performance. VACUUM (D) removes old unreferenced files, DESCRIBE HISTORY (B) shows the commit log, and REFRESH (A) reloads metadata.

A column mask dynamically transforms/obscures a column's values based on the querying principal. Dropping the column (B) removes it for everyone, and cluster policies (C) and time travel (D) don't mask data.

Unity Catalog automatically captures column-level and table-level data lineage for queries run on Databricks clusters and SQL warehouses that use Unity Catalog. No extra configuration or code is needed — lineage is tracked passively whenever data is read or written. Option A (system tables) is where lineage and audit data is stored and queryable, but system tables are the storage mechanism, not the feature that captures lineage. Option C (Delta Lake transaction log) records operations on a specific Delta table but does not track cross-table lineage. Option D (CONSTRAINT clauses) is a data quality feature and has no relationship to lineage tracking.

In Unity Catalog, privileges are not implicitly inherited down the hierarchy — a principal must have USE CATALOG on the parent catalog, USE SCHEMA on the parent schema, and SELECT on the tables (or on the schema to cover all tables). Option A correctly grants all three necessary privilege levels, and granting SELECT ON SCHEMA covers all current and future tables without needing per-table grants. Option B fails because USE CATALOG and USE SCHEMA are required to traverse the namespace — SELECT alone is insufficient. Option C is missing USE SCHEMA, so the bi_team cannot enter the schema. Option D is unnecessarily granular (per-table grants) and still covers the required hierarchy, but granting SELECT on the schema (as in A) is simpler and equally valid; the real issue with D is that it omits USE SCHEMA, making it incomplete as written.