Free Databricks Certified Data Engineer Associate practice test: 30 questions with answers

MERGE INTO applies updates to matched keys and inserts unmatched ones in a single atomic transaction, which is the upsert the requirement describes. INSERT OVERWRITE drops the existing customers that are not in the batch, and a plain INSERT adds duplicates of rows already present. CREATE OR REPLACE rebuilds the table from the batch alone and loses the rest.

Delta Lake adds ACID transactions over Parquet files by recording each commit in the _delta_log transaction log. VACUUM removes files no longer referenced beyond the retention threshold rather than immediately, so recent versions remain available. OPTIMIZE compacts files without deleting history, and Delta tables can be governed through Unity Catalog as well as the Hive metastore.

A window function ranks each customer's rows by updated_at, and keeping rank 1 returns the full latest row for each key. SELECT DISTINCT keeps every version because the timestamps differ. A GROUP BY with MAX returns only the timestamp and loses the other columns, and a single LIMIT returns one row for the entire table.

Filter and column projection or derivation act on each partition independently, so they need no shuffle. Grouping, joining two large tables and global ordering all redistribute rows between partitions, which makes them wide transformations.

A broadcast join sends the small table to every executor, so the large fact table does not have to be shuffled. A single partition for 500 GB would overload one task, and collecting to the driver and looping in Python removes the parallelism Spark provides. Caching the fact table does not avoid the shuffle.

Reading a table in Unity Catalog needs USE CATALOG and USE SCHEMA on its parents plus SELECT on the table, and nothing more. ALL PRIVILEGES on the catalog gives create and modify rights the group should not have. MODIFY allows writes, which the requirement excludes.

A column mask function applies to every query on the column, so the masking holds whichever tool reads the table. A view is bypassed when analysts keep SELECT on the base table, shared encryption keys give analysts the means to decrypt, and renaming provides no protection.

VACUUM deletes unreferenced data files older than the retention threshold, and once those files are gone, the older versions can no longer be read. Deleting the transaction log would destroy the table history, which VACUUM does not do. Merging small files is the job of OPTIMIZE, and recalculating statistics is a separate operation.

Unity Catalog captures lineage automatically for supported queries and shows upstream and downstream relationships in Catalog Explorer. The other options describe mechanisms that are not required or that restrict lineage to one pipeline type, which is not how it works.

Delta Sharing gives recipients read access to shared tables without copying the data, and access can be revoked centrally. Email exports create copies that drift, sharing a personal token exposes the engineer's identity, and a public link exposes the data to anyone who finds it.

Auto Loader uses the cloudFiles source to discover new files incrementally, stores the inferred schema in the schema location, and records which files are already processed. A nightly full read rescans everything, and listing folders by hand rebuilds bookkeeping that Auto Loader already does. A MERGE on a manually maintained list is fragile and pushes that bookkeeping onto the engineer.

A checkpoint location stores the offsets and state of the query, so after a restart it resumes exactly where it stopped. A trigger with no checkpoint loses the position on restart, and caching does not persist anything across failures. Overwrite mode replaces data each batch rather than recording progress.

COPY INTO tracks the files it has loaded, so reruns skip them and the load is idempotent without a streaming job. Overwriting or truncating rebuilds the table on each run, and a plain INSERT re-adds files already loaded, creating duplicates.

The rescued data column keeps records intact and places the unexpected values in a separate column, so nothing is lost and the fields can be reviewed. Dropping records loses data, and failing the job stops the pipeline when it does not need to. A single string column makes the data unusable for normal queries.

Schema location, file format and schema evolution mode are real Auto Loader options that control how the schema is stored, read and changed. There is no cloudFiles.mode option for overwriting, and partitioning the source files by a hash of the name is not a setting of the cloudFiles source.

Task dependencies enforce the order, and a job cluster is created for the run and terminated when it finishes, which matches the cost requirement. Fixed-time separate schedules cannot guarantee that transform waits for ingest. An all-purpose cluster is meant for interactive work and is more expensive for scheduled runs.

Setting retries and a retry interval on the task handles transient failures automatically, which is what the team needs. A second schedule runs the whole job again without targeting the failed step, removing the task drops required work, and a bigger cluster does not help when the API itself is unavailable.

A job parameter supplies the value at run time, and dbutils.widgets.get reads it inside the notebook, so one notebook serves every date. Separate copies multiply maintenance, and cluster-level Spark configuration is shared across runs and jobs. Reading previous output couples runs together and is fragile.

A job cluster exists only for the duration of the scheduled run, so the team pays for the 40 minutes it is used. An all-purpose cluster running all day is paid for continuously, and manually managed clusters that are left running waste compute.

Adaptive query execution's skew join handling splits oversized partitions at runtime, which spreads the heavy key across several tasks. Driver memory does not change how rows are distributed among executors. A single shuffle partition makes the skew worse by putting all rows in one task.

OPTIMIZE compacts small files into larger ones, which reduces the per-file overhead that slows reads. Very high shuffle partitions add task overhead without touching the file layout. CSV does not fix tiny files, and more executors cannot make up for fragmented storage.

A partition predicate lets Delta skip the partitions outside the three-day window, which removes most of the scan. A full overwrite rewrites the entire table on every run, and more merge columns do not reduce the scan. Disabling the transaction log removes the guarantees that make the merge safe.

Many small files, a skewed stage and memory spill to disk are common causes of slow Spark jobs, and each has a known remedy such as compaction, skew handling or better memory use and join strategies. Reading a widget parameter and printing a message are normal behaviour that does not slow the job.

Git folders connect a workspace folder to a Git repository so engineers can branch, commit and pull from the workspace. HTML exports lose the editable source, and copying workspaces for each feature creates drift. Storing notebooks in a Delta table is not how source control works.

Functions in a module can be imported and tested with pytest, and running those tests in CI blocks bad code before it reaches production. Testing after deployment exposes bad data to users first, and eyeballing a notebook is not repeatable. Waiting for dashboards to reveal problems is the slowest way to find a fault.

A pull request with review forces conflicts to be seen and resolved in source control before deployment, so one change cannot silently replace the other. Direct deploys and shared in-place editing both bypass that check, and renaming the job hides the collision without resolving it.

A bundle declares resources, per-environment targets and the source files they run, which lets one definition deploy to several environments. Personal tokens must not be stored in plain text, and run history is runtime data that does not belong in the bundle.

Delta Lake provides ACID transactions and time travel on top of open Parquet files in the customer's storage. CSV and JSON files have no transaction log, and bare Parquet files lack the commit history that enables atomic writes and time travel. Partitioning JSON by date does not add transactions.

Databricks SQL is the component for BI and analytical SQL against lakehouse tables. Lakeflow pipelines build tables rather than serve queries for reporting, MLflow handles machine learning tracking, and Unity Catalog governs data and does not host the query engine.

TIMESTAMP AS OF reads the table as it was at that time without modifying it, which is time travel for a read. RESTORE changes the table to the earlier state, which the analyst should not do, and the other two options are not valid time travel syntax.