Databricks DEA sample questions — 25 answered and explained

RESTORE returns a Delta table to an earlier version.

Delta keeps prior versions in the transaction log. DESCRIBE HISTORY lists them, VERSION AS OF and TIMESTAMP AS OF read them, and RESTORE TABLE ... TO VERSION AS OF (or TO TIMESTAMP AS OF) makes the current table match one of them by adding a new commit. Reading an old version or cloning it into a new table leaves sales.orders unchanged, and VACUUM only removes files.

1. Note the goal: change the current table, not just read history or copy it.
2. Recall the commands that read history (SELECT ... VERSION AS OF) or copy it (CLONE ... VERSION AS OF) vs change it (RESTORE).
3. Reject VACUUM, which only removes files.
4. Choose RESTORE TABLE ... TO VERSION AS OF 41.

Exam tip: Read the past with VERSION AS OF; go back to it with RESTORE.

VACUUM limits how far back time travel can reach.

Time travel needs both the commit in the transaction log and the data files that version referenced. VACUUM deletes data files that are no longer referenced by the current table and are older than the retention threshold, 7 days by default (delta.deletedFileRetentionDuration). A weekly VACUUM therefore removes the files for versions older than about a week, even though their log entries (kept 30 days by default) remain.

1. Note the maintenance job: weekly VACUUM at default retention.
2. Recall the default VACUUM retention threshold: 7 days.
3. Recall that time travel needs the old data files, not only the log entry.
4. Conclude that VACUUM removed the files for the 20-day-old version.

Exam tip: Time travel reaches only as far back as VACUUM has left the data files.

Streaming tables give SQL users incremental, exactly-once file ingestion.

read_files is a table-valued function. Used in a batch statement, it reads every file in the path each time, so CREATE OR REPLACE TABLE reloads everything and INSERT INTO duplicates. Wrapped in STREAM inside CREATE OR REFRESH STREAMING TABLE, it becomes an incremental source with its own checkpoint, so each refresh processes only files it has not seen. Databricks positions streaming tables as the scalable SQL replacement for COPY INTO.

1. Note the requirements: SQL only, each file exactly once, no rereads.
2. Recognize that batch reads of read_files reprocess every file.
3. Choose a streaming table with STREAM read_files, which tracks processed files.

Exam tip: SQL incremental file loads: CREATE OR REFRESH STREAMING TABLE ... FROM STREAM read_files().

Small, one-off local files are loaded with the file upload UI, which creates a managed Unity Catalog table.

The create-or-modify-table page in the workspace accepts small local files (CSV, TSV, JSON, Avro, Parquet, text), previews the schema and writes a managed Delta table into a chosen catalog and schema. It needs CREATE TABLE on the schema and a compute resource. Copying to a volume with the CLI and running COPY INTO also works, but it is the route for files too large for the upload page; Auto Loader and partner tools are designed for recurring or large-scale ingestion, not a one-off small file on a laptop.

1. Note the source: a small file on a local machine.
2. Note the frequency: once.
3. Choose the built-in file upload page that creates a managed table.

Exam tip: One small local file, loaded once: the file upload page.

Large local files go into a Unity Catalog volume (via CLI or SDK) and are loaded from there.

The file upload page is meant for small files, and browser uploads into a volume also have a size limit. Volumes themselves hold files up to the cloud storage maximum, and the Databricks CLI (databricks fs cp to a /Volumes path) or SDK can upload large files. The raw file then remains in a governed volume for audit, and read_files or COPY INTO loads it into a table. The DBFS root is not governed by Unity Catalog.

1. Recognize that every browser upload path is ruled out by the file's size.
2. Choose a governed landing place for raw files: a Unity Catalog volume.
3. Use the CLI or SDK, which are not bound by the browser upload limit.
4. Load the table from the volume path.

Exam tip: Too big for the upload page: copy to a Unity Catalog volume with the CLI/SDK, then load.

Standard connectors cover cloud storage and message buses; managed connectors cover SaaS applications, databases and file services.

Lakeflow Connect splits sources into two families. Managed connectors cover popular SaaS applications such as Workday and Salesforce, databases through CDC, and file services such as SharePoint, with Databricks handling authentication, incremental logic and retries. Standard connectors are used through Auto Loader for files in cloud object storage and through Structured Streaming or Lakeflow Spark Declarative Pipelines for message buses such as Kafka, Kinesis and Event Hubs.

1. Classify each source: SaaS app, cloud storage files, message bus.
2. Map SaaS to a managed connector.
3. Map cloud storage files to Auto Loader.
4. Map the message bus to a streaming read.

Exam tip: SaaS/database = managed connector; cloud files = Auto Loader; message bus = streaming read.

Start with the most managed ingestion layer and put business rules in downstream transformations, not in ingestion.

Databricks recommends starting with the most managed layer and moving down only when that layer cannot meet a requirement, for example when the source is unsupported. Cleansing rules are not an ingestion requirement: the connector lands raw data, and a downstream pipeline turns those bronze tables into a cleaned silver table. Replacing the connector with custom or community code gives up managed authentication, incremental reads and retries for no benefit.

1. Check whether the managed connector still meets the ingestion need: it does.
2. Classify the new requirement: a transformation, not ingestion.
3. Apply transformations downstream of the raw landed tables.
4. Move down a layer only when the managed layer cannot ingest the source.

Exam tip: Keep the most managed ingestion layer; do cleansing downstream in bronze-to-silver transformations.

Inner joins keep only matches; left joins keep every row of the left side and add nulls when the right side has no match.

Every order must appear, so orders must be the preserved side: orders.join(customers, ..., "left"). Inner and left semi joins drop unmatched orders (and semi returns no customer columns), and a right join preserves customers instead of orders.

1. Decide which table must keep all its rows: orders.
2. Put that table on the left and use a left join.
3. Expect nulls in the customer columns for unmatched orders.

Exam tip: Keep every row of one side: put it on the left and use a left join.

Joins multiply rows when the join key is not unique on one side.

Each output row is a matching pair. If customer_addresses has more than one row per customer_id (billing and shipping, or history), every order for that customer is repeated once per address. Inner joins never add unmatched rows, null keys do not match, and nothing turns an equality join into a cross join.

1. Compare the row count before and after the join.
2. Check whether the join key is unique on the other table.
3. Fix it by filtering or deduplicating that table to one row per key before joining.

Exam tip: More rows after a join = a non-unique key on the other side.

A WHERE filter on the right table's columns runs after a left join and removes the null-filled rows, turning it into an inner join.

The left join first keeps every order, then WHERE p.region = 'EU' evaluates to NULL for unmatched orders and to false for non-EU promotions, so both are filtered out. Putting the condition in ON restricts which promotions can match while the left join still preserves every order. Adding OR p.region IS NULL rescues orders with no promotion but still drops orders tied to a US or APAC promotion; inner and right joins preserve the wrong side.

1. Note when each clause runs: ON during the join, WHERE after it.
2. Unmatched rows have null right-side columns, which fail a WHERE equality test.
3. Check orders with a non-EU promotion too, not only orders with none.
4. Move right-side filters into ON to keep the left join's guarantee.

Exam tip: Filter the right table of a left join in ON, not in WHERE.

A broadcast join copies a small table to every executor so the large table does not need to be shuffled.

Wrapping the small side in broadcast() (imported from pyspark.sql.functions) is a join hint that works without touching configuration such as spark.sql.autoBroadcastJoinThreshold. Each executor joins its sales partitions against a local copy of the 30 MB table. Broadcasting the large side is backwards, repartitioning shuffles anyway, and a cross join followed by an equality filter is planned as the same sort-merge equi-join.

1. Identify the small side of the join.
2. Avoid configuration changes, so use a hint in code.
3. Wrap only the small DataFrame in broadcast().

Exam tip: Large-to-small join: broadcast the small side to skip the shuffle.

A join on a list of column names is an equi-join on every listed column that returns one copy of each key.

When keys share names on both sides, passing them as a list, ["store_id", "region"], combines them with AND and removes the duplicate key columns, so selecting store_id is no longer ambiguous. The Python and operator returns one string, chaining two joins re-adds the stores table, and OR matches on either key.

1. Both keys must match together, which is an AND of equalities.
2. Key columns have the same names on both sides.
3. Use the list-of-names form to get one copy of each key.

Exam tip: Same-named multi-column keys: join on a list of names for one copy of each key.

Standard equality never matches null keys; null-safe equality (eqNullSafe in PySpark, <=> in SQL) does.

In an equi-join, null == null evaluates to null, not true, so rows with a null part of the key never match. eqNullSafe returns true for two nulls and false for null versus a value, which is exactly the required behaviour, but only on the column that can be null. The OR isNull() version fans each top-level row out across all sub-accounts, null-safe equality on account_id leaves the null sub_account_id compared with ==, and a left join only keeps the unmatched rows with empty attributes.

1. Spot that part of the composite key can be null on both sides.
2. Recall that standard equality never matches nulls.
3. Rule out conditions that match a null to non-null keys.
4. Use null-safe equality for that key column.

Exam tip: Null keys never match with ==; use eqNullSafe (SQL <=>) when null is a real key value.

The If/else condition task adds boolean branching to a job: a single comparison decides whether the true or the false branch runs.

Choosing one of two paths from a parameter value is branching, and the If/else condition task provides it. Its operand can be a job parameter such as {{job.parameters.mode}}, and downstream tasks attach to its true or false outcome. For each loops over inputs, Run Job calls another job, and a SQL alert sends notifications.

1. Recognise the need: pick one of two paths from a value.
2. Recall that branching is done with an If/else condition task.
3. Attach the backfill notebook to the true outcome and the incremental one to the false outcome.
4. Reject loop, child-job and alert task types.

Exam tip: Branching in Lakeflow Jobs = If/else condition task; looping = For each.

Data-driven branching combines a task value (set upstream) with an If/else condition task that compares it.

The decision depends on a value computed during the run, so the validate notebook sets it with dbutils.jobs.taskValues.set, and an If/else condition task evaluates {{tasks.validate.values.bad_rows}} > 0. Quarantine depends on the true outcome and publish on the false outcome. Raising an exception would work only by failing the task, Run if cannot inspect values, and job parameters are fixed at trigger time.

1. Note the decision uses a value computed during the run.
2. Carry it downstream as a task value.
3. Compare it in an If/else condition task and attach each notebook to an outcome.
4. Reject designs that fail a task or rely on values fixed at trigger time.

Exam tip: Branch on a computed value: task value + If/else condition task, not exceptions plus Run if.

In an If/else condition task, == and != compare operands as strings, while >, >=, < and <= compare them numerically.

The value 2.0 is serialised as the string 2.0, and == compares it with the string 2, which differs, so the condition is false every time. Databricks documents this exact case: 12.0 == 12 is false. Fixes are to set the value as the integer 2 or to use a numeric comparison. Task values are available within the run, keep their JSON form, and are valid condition operands.

1. Note the value was set as a float, 2.0.
2. Recall that == and != compare as strings in condition tasks.
3. Compare the strings 2.0 and 2: not equal, so the false branch runs.
4. Fix by setting an integer or using a numeric operator.

Exam tip: Condition tasks: == and != are string comparisons; >, >=, <, <= are numeric.

The For each task loops a nested task over an array of inputs, one iteration per value.

Looping is the For each task's job. Its inputs are a JSON array such as ["US", "DE", ...], and the nested notebook task receives the current value through {{input}}. Each iteration is tracked on its own, which meets the per-country visibility requirement. A loop inside one notebook runs as a single task, and If/else or a Run Job task does not repeat work per value.

1. Recognise a loop: the same task once per value.
2. Recall the For each task and its JSON array of inputs.
3. Pass the current value to the nested task with {{input}}.
4. Reject single-run designs that hide per-country failures.

Exam tip: Repeat a task per value with For each; reference the current value with {{input}}.

Reset in a Git folder is git reset --hard followed by git push --force.

The requirement is to discard committed and pushed work. Reset is the only operation that does that: it hard-resets the branch to the chosen branch and force-pushes, discarding commits locally and remotely. Merge and rebase keep the commits, and pull only adds remote changes. Because it is destructive, reset suits a branch nobody else depends on.

1. Note the requirement: drop pushed commits on both sides.
2. Recall that merge, pull and rebase all keep commits.
3. Recall that reset is a hard reset plus force push.
4. Use reset on a branch nobody else depends on.

Exam tip: Git folder Reset = hard reset + force push: it discards local and remote commits.

Declarative Automation Bundles (formerly Databricks Asset Bundles) define jobs, pipelines and other assets as source files for repeatable deployment.

Databricks recommends bundles for CI/CD: resources live in YAML next to the code, under source control, and the CLI deploys them to each target. Git folders and workspace export move code, but not the job and pipeline definitions, and copying JSON by hand reproduces the drift.

1. Note the need: definitions as files, deployed identically.
2. Recall what bundles package: resources plus code.
3. Recall that the CLI deploys a bundle to each target.
4. Reject approaches that only move code or rely on manual copies.

Exam tip: Jobs and pipelines as code, promoted across environments: use bundles.

The workspace-wide Runs tab under Jobs & Pipelines lists runs across jobs, filters by status and error code, and shows the Top 5 error types.

The engineer wants a cross-job summary with no SQL. The Runs tab under Jobs & Pipelines covers all jobs in the workspace, filters by status, time range and error code, and shows a Top 5 error types table. The job_run_timeline system table holds similar data but needs a query, which the constraint excludes; per-job matrix views and cluster event logs do not summarize across jobs.

1. Note the scope: many jobs, summarized, no SQL.
2. Recall the workspace-wide Runs tab and its filters.
3. Use Top 5 error types and the error-code filter.
4. Reject per-job views and the SQL option.

Exam tip: Many jobs failing? Start at the workspace Runs tab: Top 5 error types and filters.

Skew shows as a few tasks in a stage taking far longer, and reading far more data, than the rest.

Tasks in a stage do the same work on different partitions, so their times should be close. When one task reads far more shuffle data and runs far longer, its partition is oversized, typically because one join key holds a large share of the rows. Too many partitions, general spill and executor loss produce different patterns.

1. Compare the outlier task with its peers in the same stage.
2. Note it reads much more data, not just runs longer.
3. Conclude one partition is oversized: data skew.
4. Reject causes that affect tasks evenly.

Exam tip: One task far slower and far bigger than its stage-mates = skew.

ALTER TABLE ... SET MANAGED converts an external Delta table in place; it needs the owner and a recent runtime.

Two conditions failed at once: the engineer is not the owner (MANAGE is not sufficient for SET MANAGED) and Runtime 15.4 LTS is too old (serverless or 17.3 LTS and above is required). Running as the owning service principal on serverless meets both and keeps the name, history and grants. Moving to a newer runtime without ownership, or taking ownership while staying on 15.4 LTS, each fixes only one condition, and CTAS loses history and grants.

1. Recall that SET MANAGED requires table ownership.
2. Recall that it needs serverless or Runtime 17.3 LTS+.
3. Check each option against both conditions.
4. Reject CTAS because history and grants must be kept.

Exam tip: SET MANAGED: run it as the owner, on serverless or 17.3 LTS+.

SET MANAGED converts an external table to managed in place, keeping name, history and permissions.

SET MANAGED is built for this: it copies the files and log into managed storage, keeps the name, settings, permissions, views and history, and only briefly blocks writers. A CTAS copy and a deep clone both create new tables that do not carry the grants, and changing table properties leaves the table external.

1. Note what must be kept: name, history, grants.
2. Recall that SET MANAGED converts in place and keeps them.
3. Reject copies that create new tables.
4. Reject property changes that leave the table external.

Exam tip: External to managed, keeping everything: ALTER TABLE ... SET MANAGED.

UNSET MANAGED reverses a SET MANAGED conversion within 14 days.

A table converted with SET MANAGED can be rolled back with UNSET MANAGED within 14 days, on serverless or Runtime 17.3 LTS or above. The rollback keeps writes made after the conversion. RESTORE would discard those writes and keep the table managed, the old path is missing the new data, and UNDROP is for dropped tables.

1. Note it was converted with SET MANAGED five days ago.
2. Recall the 14-day UNSET MANAGED rollback.
3. Check that post-conversion writes are kept.
4. Reject options that lose writes or target dropped tables.

Exam tip: Undo a SET MANAGED within 14 days with UNSET MANAGED.

SET MANAGED keeps a table's identity, but streams and Databricks-to-Databricks shares need follow-up.

The conversion keeps the table's name, history, permissions and dependent views, so re-granting SELECT or recreating views is unnecessary work. Two documented limitations do need follow-up: existing streaming jobs fail and must be restarted, and Databricks-to-Databricks sharing keeps pointing at the old location until the table is reshared. Only the option that restarts the stream and reshares the table covers both, without busywork on things the conversion preserves.

1. List the consumers: a stream and a Databricks-to-Databricks share.
2. Recall what SET MANAGED keeps: name, history, grants and views.
3. Recall what it does not handle: running streams and Databricks-to-Databricks shares.
4. Choose the option that restarts the stream and reshares the table.

Exam tip: After SET MANAGED: restart streams and reshare Databricks-to-Databricks shares.