Databricks Certified Data Engineer Associate

Question 1

A data engineering team runs a nightly production ETL pipeline that is triggered automatically by the Databricks Jobs scheduler. During code review, a teammate notices the pipeline is configured to run on an all-purpose cluster that stays running 24/7 so analysts can also attach notebooks to it interactively during the day. Management wants to reduce compute cost for the scheduled pipeline without affecting the analysts' interactive work. What is the most appropriate change?

Accepted Answer

Configure the scheduled job to use a job cluster that is created when the job starts and terminated when it finishes.

Answer

Keep using the shared all-purpose cluster but reduce its worker count so both workloads run more cheaply.

Answer

Move the analysts' notebooks to a job cluster and keep the ETL pipeline on the all-purpose cluster.

Answer

Enable autoscaling on the all-purpose cluster so it scales down when the ETL job is not running.

Question 2

A data engineering team uses Databricks Asset Bundles to deploy jobs and notebooks. When they run 'databricks bundle deploy -t dev', the engineer wants to confirm where the bundle's files and resources are placed so multiple developers on the same team do not overwrite each other's deployments in the 'dev' target. By default, how does a Databricks Asset Bundle isolate a developer's deployment in the 'dev' target?

Accepted Answer

It deploys files under a per-user path such as '/Workspace/Users//.bundle//dev' by default

Answer

It deploys all files to a shared '/Shared/bundles' folder that every developer accesses in common

Answer

It requires each developer to manually set a unique 'root_path' in databricks.yml or deployments will collide

Answer

It stores all deployed artifacts only in the local Git repository and never writes to the workspace

Question 3

A data engineering team's Databricks Asset Bundle project has grown large, with dozens of job and pipeline resource definitions all placed directly inside the top-level databricks.yml file. The lead engineer wants to split these resource definitions into separate YAML files under a resources/ directory so each team member can maintain their own jobs without causing merge conflicts in one giant file. Which databricks.yml mechanism allows these separate resource files to be combined into the bundle configuration?

Accepted Answer

Add an 'include' section in databricks.yml that lists the paths (or glob patterns) to the separate resource YAML files

Answer

Add an 'import' key under each target that references the external resource files

Answer

Reference the files using the 'artifacts' section so they are uploaded during deploy

Answer

Nothing is needed — the Databricks CLI automatically loads every .yml file in the project directory

Question 4

A data engineer has defined a Databricks Asset Bundle containing a job resource in databricks.yml. After committing changes to Git, they want to push the updated job definition and its associated files to the Databricks workspace for the 'dev' target, but they do NOT want to trigger execution of the job yet. Which Databricks CLI command accomplishes this?

Accepted Answer

databricks bundle deploy -t dev

Answer

databricks bundle run -t dev

Answer

databricks bundle validate -t dev

Answer

databricks bundle init -t dev

Question 5

A data engineering team stores their Databricks Asset Bundle project in a Git repository. A new engineer clones the repo and needs to understand which file defines the bundle's name, the resources (jobs and pipelines) to deploy, and the available deployment targets. Which file in the project contains this core configuration?

Accepted Answer

databricks.yml at the root of the bundle project

Answer

requirements.txt in the project root

Answer

The .gitignore file managed by Databricks Repos

Answer

A cluster-policy.json file in the resources folder

Question 6

A data engineering team manages a Databricks Asset Bundle that deploys a job to both a staging and a production workspace. When they promote a new release, they want the production job to always run the code from a specific, immutable Git tag rather than from files copied into the production workspace. Which approach best supports this requirement?

Accepted Answer

Configure the job task to use a Git source in databricks.yml, pinning it to the release tag, so the production deployment references the versioned Git commit directly.

Answer

Manually export the notebooks from staging and import them into the production workspace before each release.

Answer

Set bundle mode to 'development' for the production target so the workspace files are prefixed and isolated per release.

Answer

Store the code only in DBFS and reference the DBFS path in the job definition to guarantee immutability.

Question 7

A data engineering team wants every new Databricks Asset Bundle project to start with a consistent folder structure, a pre-configured databricks.yml, and standard job definitions that match company conventions. They want engineers to scaffold new projects quickly without copying files manually. Which Databricks CLI capability should they use to accomplish this?

Accepted Answer

Use 'databricks bundle init' with a custom bundle template so new projects are generated from a standardized template

Answer

Use 'databricks bundle deploy' repeatedly and instruct engineers to delete unneeded resources afterward

Answer

Use 'databricks bundle validate' to automatically create the folder structure from the schema

Answer

Use 'databricks repos create' to clone the production workspace files into each new project

Question 8

A data engineering team uses Databricks Asset Bundles. Each developer deploys the same bundle to a shared workspace from their own feature branch. They report that when two developers deploy simultaneously, their jobs collide — one developer's deployment overwrites the other's job and schedules start firing unexpectedly during testing. They want each developer's deployment to be isolated (uniquely named per user) and to have schedules automatically paused. Which configuration in the target should they use?

Accepted Answer

Set the target's `mode` to `development`

Answer

Set the target's `mode` to `production`

Answer

Add `run_as` with a shared service principal to the target

Answer

Set `default: true` on the target so all developers share one deployment

Question 9

A data engineering team uses Databricks Asset Bundles to deploy jobs. During development, jobs run under each developer's personal identity, which is fine for testing. However, when deploying to the production target, the team wants all jobs to run under a shared service principal rather than the identity of whoever last ran `databricks bundle deploy`. Which configuration approach in the bundle's databricks.yml achieves this for the production target?

Accepted Answer

Add a `run_as` setting with the service principal's `service_principal_name` under the production target's configuration.

Answer

Set the `DATABRICKS_TOKEN` environment variable to the service principal's personal access token before running deploy.

Answer

Add `git_source` referencing the service principal's repository under the production target.

Answer

Set `mode: production` on the target, which automatically makes all jobs run under the workspace owner.

Question 10

A data engineering team uses Databricks Asset Bundles (DABs) to deploy the same job to a development workspace and a production workspace. In development, jobs should run on a small single-node cluster and write to a dev catalog; in production, jobs should run on a larger cluster and write to a prod catalog. The team wants a single bundle definition that adapts these settings based on where it is deployed. What is the recommended way to achieve this?

Accepted Answer

Define multiple 'targets' in databricks.yml, each overriding variables and resource settings, and deploy with 'databricks bundle deploy -t <target>'.

Answer

Maintain two separate databricks.yml files in different Git branches and merge them manually before each deployment.

Answer

Hardcode the production values in the bundle and manually edit the YAML each time before deploying to development.

Answer

Use a single target and rely on the Databricks workspace to automatically detect the environment and swap cluster sizes.

Question 11

A data engineer has finished editing the databricks.yml configuration and several job definitions for a Databricks Asset Bundle. Before deploying to the development target, they want to confirm that the bundle configuration is syntactically correct and that all references resolve properly, without actually creating or updating any resources in the workspace. Which CLI command should they run?

Accepted Answer

databricks bundle validate

Answer

databricks bundle deploy --dry-run

Answer

databricks bundle run --check

Answer

databricks bundle init

Question 12

A data engineering team maintains a Databricks Asset Bundle whose databricks.yml defines several jobs. Each job needs to reference the same notification email address and the same Unity Catalog catalog name in multiple places. The team wants to define these values in ONE location and reuse them throughout the bundle configuration, so a single edit updates every reference. Which Asset Bundle feature should they use?

Accepted Answer

Define custom variables in a top-level 'variables:' block and reference them with ${var.name} syntax throughout the configuration

Answer

Hard-code the values in each job definition and rely on find-and-replace when they change

Answer

Create a separate target for every value that needs to be reused

Answer

Store the values as Databricks secrets and interpolate them at deploy time in databricks.yml

Question 13

A data engineering team ingests new JSON order files that land in a cloud storage location throughout the day. They want to run a scheduled job every hour that processes only files that have arrived since the last run, then shuts down the cluster to save cost. They want to reuse Auto Loader's file tracking so no file is processed twice across runs. Which approach meets these requirements?

Accepted Answer

Use an Auto Loader (cloudFiles) readStream with Trigger.AvailableNow and a checkpoint location, scheduled as an hourly job

Answer

Use an Auto Loader readStream with Trigger.ProcessingTime='1 hour' running as a continuous always-on stream

Answer

Use spark.read.json() in a batch job and manually filter files by modification timestamp each hour

Answer

Use COPY INTO with FORCE = true to reload the source directory on every scheduled run

Question 14

A data engineer builds an Auto Loader stream that ingests JSON files from a cloud storage path into a Bronze Delta table. The pipeline runs every hour using Trigger.AvailableNow. After a job restart, the engineer notices that previously ingested files are NOT being reprocessed, even though the source directory still contains all historical files. Which mechanism is responsible for ensuring each file is processed exactly once across restarts?

Accepted Answer

Auto Loader's checkpoint location, which persists metadata about which files have already been processed

Answer

The Delta transaction log of the target table, which deduplicates rows on write

Answer

The cloudFiles.schemaLocation option, which caches file contents between runs

Answer

The VACUUM command, which removes already-ingested files from the source directory

Question 15

A data engineer is configuring an Auto Loader stream to incrementally ingest newly arriving Parquet files from an S3 landing zone into a Delta table. They write the following incomplete code: spark.readStream.format("cloudFiles").option(???).load("s3://landing/events/"). Which option is REQUIRED for Auto Loader to correctly read the incoming files?

Accepted Answer

cloudFiles.format

Answer

cloudFiles.maxFilesPerTrigger

Answer

cloudFiles.inferColumnTypes

Answer

cloudFiles.schemaLocation

Question 16

A data engineer configures Auto Loader to ingest JSON files from cloud storage using schema inference. After the first run, they notice that numeric fields like 'order_total' and 'quantity' are all being inferred as STRING type in the target table, even though the JSON values are clearly numbers. The engineer wants Auto Loader to infer proper data types such as INTEGER and DOUBLE instead of treating everything as strings. Which option should they set to achieve this?

Accepted Answer

Set the option 'cloudFiles.inferColumnTypes' to 'true'

Answer

Set the option 'cloudFiles.schemaEvolutionMode' to 'rescue'

Answer

Set the option 'cloudFiles.format' to 'parquet' to preserve types

Answer

Set the option 'cloudFiles.allowOverwrites' to 'true'

Question 17

A data engineer configures an Auto Loader stream to ingest millions of new files that arrive daily into a large cloud storage bucket. The initial deployment uses the default directory listing mode, but as the number of files grows, the team notices that each micro-batch spends a long time simply discovering new files, increasing latency and cloud API costs. Which change should the engineer make to improve file-discovery efficiency at this scale?

Accepted Answer

Switch Auto Loader to file notification mode by setting cloudFiles.useNotifications to true so new files are detected via cloud queue events instead of listing the directory

Answer

Replace Auto Loader with a COPY INTO command that reloads the entire directory on each run

Answer

Increase the cluster size so directory listing completes faster during each micro-batch

Answer

Set cloudFiles.inferColumnTypes to false to reduce the overhead of scanning file contents

Question 18

A data engineering team receives daily drops of thousands of small JSON files into a cloud storage directory. They need a solution that automatically discovers and ingests only new files on each scheduled run, without manually tracking which files have already been loaded, and that requires minimal SQL or Python code for a Delta target. Given these requirements, which approach best fits a repeatable, incremental ingestion pipeline that scales as file counts grow over time?

Accepted Answer

Use Auto Loader (cloudFiles) reading from the directory into a Delta table, relying on its checkpoint to track processed files across runs

Answer

Write a Spark batch job that reads the entire directory with spark.read.json each run and overwrites the target table

Answer

Manually maintain a control table listing loaded filenames and filter them out with a custom join before each DataFrame write

Answer

Mount the storage and use dbutils.fs.ls to enumerate files, then loop and load each file individually with INSERT INTO

Question 19

A data engineer is configuring an Auto Loader stream to ingest millions of small JSON files that land continuously in a cloud storage bucket. The team notices that the default file discovery approach requires repeatedly listing the entire directory, which becomes slow and expensive as the number of files grows. Which Auto Loader configuration should the engineer use to improve file detection efficiency at scale?

Accepted Answer

Set cloudFiles.useNotifications (file notification mode) so Auto Loader uses cloud queue/event notifications instead of directory listing

Answer

Switch from Auto Loader to COPY INTO, which automatically scales directory listing

Answer

Increase the trigger interval so the directory is listed less frequently

Answer

Disable schema inference to reduce the overhead of scanning files

Question 20

A data engineer uses Auto Loader to ingest a very large backlog of JSON files that accumulated in a cloud storage location. When the stream starts, the cluster becomes overwhelmed because it tries to process an enormous number of files in a single micro-batch, causing memory pressure and slow performance. The engineer wants to limit how many files each micro-batch processes so the load is more manageable. Which option should be configured to control this behavior?

Accepted Answer

cloudFiles.maxFilesPerTrigger

Answer

cloudFiles.schemaEvolutionMode

Answer

cloudFiles.inferColumnTypes

Answer

cloudFiles.includeExistingFiles

Question 21

A data engineer configures Auto Loader to ingest JSON files from cloud storage into a Bronze Delta table. Occasionally, upstream systems send records where a field has a data type that conflicts with the inferred schema (e.g., a numeric field arriving as a non-numeric string). The engineer wants to ensure these mismatched values are not silently dropped and can be inspected later. Which Auto Loader feature captures data that does not match the expected schema?

Accepted Answer

The _rescued_data column, which stores fields that don't match the schema

Answer

The cloudFiles.maxFilesPerTrigger option, which limits ingestion rate

Answer

The mergeSchema option, which automatically alters column data types

Answer

The badRecordsPath option, which quarantines entire corrupt files

Question 22

A data engineer uses Auto Loader to ingest JSON files into a bronze Delta table. The stream is configured with a schema location, and the default schema evolution mode is in effect. One day the upstream team adds a new field to the JSON files. What happens to the Auto Loader stream when it encounters the new column?

Accepted Answer

The stream fails with an UnknownFieldException, updates the schema in the schema location, and succeeds automatically when restarted.

Answer

The stream silently drops the new column and continues running without interruption.

Answer

The stream places all values of the new column into the _rescued_data column permanently.

Answer

The stream ignores the change until OPTIMIZE is run on the target Delta table.

Question 23

A data engineer uses Auto Loader to incrementally ingest JSON files from a cloud storage location into a Bronze Delta table. The upstream system occasionally adds new fields to the JSON files. The engineer wants the pipeline to automatically detect and add these new columns to the target table without the stream failing, and to reprocess the affected data. Which configuration should the engineer use?

Accepted Answer

Set cloudFiles.schemaEvolutionMode to "addNewColumns" (the default) and provide a schemaLocation so Auto Loader tracks the evolving schema.

Answer

Set cloudFiles.schemaEvolutionMode to "none" so the stream ignores unexpected columns and continues running.

Answer

Use COPY INTO with the FORMAT_OPTIONS('mergeSchema' = 'false') setting to avoid schema conflicts.

Answer

Disable schema inference by hardcoding a fixed StructType schema so no new columns can ever appear.

Question 24

A data engineer is using Auto Loader to ingest JSON files into a bronze Delta table. Auto Loader correctly infers most columns, but the 'transaction_amount' field is being inferred as a STRING because some early files contained values wrapped in quotes. The engineer wants Auto Loader to treat this specific column as a DECIMAL while still inferring all other columns automatically. What is the most appropriate approach?

Accepted Answer

Set the 'cloudFiles.schemaHints' option to specify 'transaction_amount DECIMAL(10,2)' while leaving the rest of the schema to be inferred

Answer

Disable schema inference entirely and provide a full explicit schema for every column using the .schema() method

Answer

Set 'cloudFiles.inferColumnTypes' to false so all columns default to correct types automatically

Answer

Enable 'cloudFiles.schemaEvolutionMode' to 'rescue' so the incorrectly typed column is fixed on the next run

Question 25

A data engineer is setting up an Auto Loader stream to ingest JSON files that land continuously in an S3 bucket. The team wants Auto Loader to automatically detect the columns and their data types on the first run without manually specifying a schema, and to persist the inferred schema so subsequent runs remain consistent. Which configuration is required to enable this behavior?

Accepted Answer

Set the option 'cloudFiles.schemaLocation' to a durable storage path so Auto Loader can infer and store the schema.

Answer

Set 'cloudFiles.inferColumnTypes' to false and provide the schema inline with the .schema() method.

Answer

Enable 'cloudFiles.allowOverwrites' so that new files trigger a fresh schema calculation on every run.

Answer

Configure 'cloudFiles.maxFilesPerTrigger' to limit ingestion so the schema can be inferred incrementally.

Question 26

A data engineer configures an Auto Loader stream to ingest JSON files landing in a cloud storage directory. They want Auto Loader to infer the schema on the first run and then reuse that same inferred schema (tracking evolution over time) across subsequent job restarts, rather than re-inferring from scratch each time. Which option must be set to enable this behavior?

Accepted Answer

cloudFiles.schemaLocation

Answer

cloudFiles.format

Answer

cloudFiles.maxFilesPerTrigger

Answer

cloudFiles.inferColumnTypes

Question 27

A data engineering team receives new JSON files in a cloud storage location every few minutes, around 50,000 small files per day. They need a low-latency ingestion pipeline that automatically detects and processes newly arriving files without reprocessing old ones, and they want to avoid the overhead of listing the entire directory on each run. Which ingestion approach best meets these requirements?

Accepted Answer

Use Auto Loader with Structured Streaming and file notification mode to incrementally process new files as they arrive.

Answer

Run a scheduled COPY INTO command every few minutes that reads the entire storage directory each time.

Answer

Use a batch spark.read.json() job that overwrites the target table on every scheduled run.

Answer

Manually track processed file names in a control table and filter them out in each batch job.

Question 28

A data engineer is joining a large fact table (billions of rows) with a small dimension table (about 20 MB) in a Spark SQL query. The job is running slowly because of heavy shuffling during the join. Which technique should the engineer use to eliminate the shuffle and improve performance?

Accepted Answer

Broadcast the small dimension table so it is sent to all executors and joined locally without shuffling the fact table

Answer

Repartition both tables by the join key so the data is co-located before joining

Answer

Apply a window function partitioned by the join key to avoid the join altogether

Answer

Cache the fact table in memory before executing the join

Question 29

A data engineer is building a Silver-layer transformation from a Bronze customer table. The column `preferred_contact` frequently contains NULL values, and the business rule states that when it is NULL, the pipeline should fall back to the value in `secondary_contact`, and if that is also NULL, use the literal string 'unknown'. Which Spark SQL expression correctly implements this rule in a single column definition?

Accepted Answer

COALESCE(preferred_contact, secondary_contact, 'unknown') AS contact

Answer

NVL(preferred_contact, secondary_contact) AS contact

Answer

IFNULL(preferred_contact, 'unknown') AS contact

Answer

NULLIF(preferred_contact, secondary_contact, 'unknown') AS contact

Question 30

A data engineer has a Silver table 'order_items' with one row per line item, containing columns 'order_id', 'product_name', and 'quantity'. For a reporting requirement, they must produce a Gold table with exactly one row per 'order_id' where a new column 'products' holds an array of all 'product_name' values for that order. Which Spark SQL approach produces the correct result?

Accepted Answer

SELECT order_id, collect_list(product_name) AS products FROM order_items GROUP BY order_id

Answer

SELECT order_id, explode(product_name) AS products FROM order_items GROUP BY order_id

Answer

SELECT order_id, array(product_name) AS products FROM order_items

Answer

SELECT DISTINCT order_id, product_name AS products FROM order_items

Question 31

A data engineer needs to load a set of CSV files from a cloud storage location into an existing Delta table named sales_bronze. The files have a header row, and the engineer wants COPY INTO to infer column types automatically rather than treating everything as strings. Which addition to the COPY INTO command correctly enables header handling and automatic type inference for the CSV format?

Accepted Answer

FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')

Answer

COPY_OPTIONS ('header' = 'true', 'mergeSchema' = 'true')

Answer

PATTERN ('header=true, inferSchema=true')

Answer

FORMAT_OPTIONS ('force' = 'true', 'overwrite' = 'true')

Question 32

A data engineer runs COPY INTO to load a batch of CSV files from a cloud storage location into a newly created empty Delta table. The command completes successfully. Later that day, without any new files being added to the source location, the engineer runs the exact same COPY INTO command again. What happens on the second execution?

Accepted Answer

The command processes zero new files and the target table row count remains unchanged.

Answer

The command reloads all the same files, resulting in duplicate rows in the target table.

Answer

The command fails with an error because the files have already been ingested.

Answer

The command truncates the target table and reloads all files from scratch.

Question 33

A data engineer needs to load only the CSV files from a cloud storage directory that also contains JSON and Parquet files mixed together. The engineer wants to use COPY INTO to load these files into an existing Delta table, ensuring only files matching a specific naming pattern (e.g., files starting with 'sales_') are ingested. Which combination of COPY INTO options should be used?

Accepted Answer

Use FILEFORMAT = CSV with the PATTERN option set to 'sales_*.csv'

Answer

Use FORMAT_OPTIONS('fileType' = 'csv') with a WHERE filename LIKE clause

Answer

Use FILEFORMAT = CSV and rely on schema inference to skip non-CSV files automatically

Answer

Use the cloudFiles.format option set to 'csv' with a glob PATTERN filter

Question 34

A data engineer runs a nightly job that uses COPY INTO to load newly arrived CSV files from a cloud storage location into a Delta table. Due to a scheduling misconfiguration, the same job occasionally runs twice on the same night, pointing at the same source directory. The engineer notices that despite the duplicate runs, no duplicate rows appear in the target table. What is the primary reason COPY INTO avoids reloading the same data?

Accepted Answer

COPY INTO tracks which files have already been ingested and skips files it has previously loaded, making it idempotent on repeated runs.

Answer

COPY INTO automatically runs a MERGE against a primary key to deduplicate rows on every execution.

Answer

COPY INTO enables Delta's VACUUM process, which removes duplicate rows after each load.

Answer

COPY INTO overwrites the entire target table on every run, so duplicates cannot accumulate.

Question 35

A data engineer uses COPY INTO to incrementally load daily CSV files from cloud storage into an existing Delta table. This week, the upstream team added two new columns to the source files. The engineer wants COPY INTO to automatically add these new columns to the target Delta table instead of failing or ignoring them. Which approach accomplishes this?

Accepted Answer

Set the COPY_OPTIONS ('mergeSchema' = 'true') option in the COPY INTO command

Answer

Set the FORMAT_OPTIONS ('inferSchema' = 'true') option in the COPY INTO command

Answer

Add 'cloudFiles.schemaEvolutionMode' = 'addNewColumns' to the COPY INTO command

Answer

Drop and recreate the target Delta table before each COPY INTO run

Question 36

A data engineer wants to load a batch of new Parquet files from an S3 location into an existing table using the COPY INTO command. The team plans to run this command on a nightly schedule as files arrive. Which requirement must be satisfied for COPY INTO to work correctly in this scenario?

Accepted Answer

The target table must be a Delta table; COPY INTO requires a Delta Lake target.

Answer

The source files must be JSON or CSV, since COPY INTO does not support Parquet input.

Answer

The command must be run inside a Structured Streaming query to track file arrival.

Answer

A new external table must be created on each run because COPY INTO cannot append to an existing table.

Question 37

A data engineer receives a one-time delivery of exactly 12 large Parquet files in a cloud storage folder representing a historical backfill. The files will never change and no new files will arrive in this folder. The engineer needs to load them into an existing Delta table as a single batch operation and wants the simplest approach that guarantees each file is loaded only once even if the command is accidentally re-run. Which approach best fits this requirement?

Accepted Answer

Use COPY INTO to load the Parquet files into the Delta table, relying on its built-in tracking of already-ingested files.

Answer

Configure Auto Loader with cloudFiles and a checkpoint location running in continuous streaming mode.

Answer

Read the files with spark.read.parquet and append them using a plain INSERT INTO each time the job runs.

Answer

Use Auto Loader with directory listing mode and Trigger.AvailableNow, adding a new checkpoint each run.

Question 38

A data engineer needs to run a one-time bulk load of several thousand historical Parquet files that were just uploaded to an S3 path into an existing Delta table. The load must be re-runnable safely (if the job is retried, previously loaded files should not be re-ingested), but the team does NOT want to configure a streaming checkpoint or maintain a continuously running stream. Which approach best meets these requirements?

Accepted Answer

Use the COPY INTO command targeting the Delta table with the source S3 path and Parquet format

Answer

Use spark.read.parquet() to load the files and then append them with a standard INSERT INTO

Answer

Configure Auto Loader with cloudFiles format and a continuous trigger

Answer

Use CREATE TABLE AS SELECT to rebuild the Delta table from the Parquet files on each run

Question 39

A data engineer is building a Gold-layer table that reports the number of unique customers who placed at least one order on each calendar day. The Silver table `orders` has columns `order_date`, `customer_id`, and `order_amount`. Which Spark SQL query correctly produces one row per day with the count of distinct customers?

Accepted Answer

SELECT order_date, COUNT(DISTINCT customer_id) AS unique_customers FROM orders GROUP BY order_date

Answer

SELECT order_date, COUNT(customer_id) AS unique_customers FROM orders GROUP BY order_date

Answer

SELECT DISTINCT order_date, COUNT(customer_id) AS unique_customers FROM orders

Answer

SELECT order_date, COUNT(DISTINCT customer_id) AS unique_customers FROM orders

Question 40

A data engineer runs `CREATE TABLE sales_summary AS SELECT region, SUM(amount) AS total FROM silver.orders GROUP BY region` in a Databricks SQL notebook without specifying a LOCATION. The catalog and schema are configured with default settings. Where will the underlying data files for sales_summary be stored, and what happens if the table is later dropped?

Accepted Answer

Files are stored in the metastore's managed storage location, and dropping the table deletes both the metadata and the underlying data files.

Answer

Files are stored in the metastore's managed storage location, but dropping the table only removes the metadata while leaving data files intact.

Answer

No files are stored because CTAS creates only a view, so dropping it removes the query definition but no data.

Answer

Files are stored in a user-specified external path, and dropping the table removes only the metadata.

Question 41

A data engineer needs to build a complete availability matrix for a Gold-layer report. Each of the 500 store locations must appear once for every date in a 365-row date dimension table, regardless of whether any sales occurred. Both tables share no common join key. Which Spark SQL join produces all 182,500 store-date combinations?

Accepted Answer

A CROSS JOIN between the stores table and the date dimension table

Answer

An INNER JOIN on store_id = date_id

Answer

A LEFT OUTER JOIN with the date table on the left side

Answer

A FULL OUTER JOIN between the two tables

Question 42

A data engineering team uses Databricks Repos linked to a remote GitHub repository. One engineer edits a notebook inside their personal Repo in the workspace and wants their changes to be reviewed by teammates through the team's normal pull request process before merging to the main branch. What should the engineer do from the Databricks Repos Git interface?

Accepted Answer

Commit the changes to a feature branch and push it to the remote GitHub repository, then open a pull request in GitHub

Answer

Use the workspace 'Export' option to download the notebook as a .dbc file and email it to teammates for review

Answer

Commit the changes directly to the main branch in the Repo, since Databricks automatically creates a pull request on every commit

Answer

Clone the production job's cluster and attach the notebook so teammates can inspect the runtime output

Question 43

A data engineering team uses Databricks Repos to manage their notebooks under Git version control. A new engineer clones the remote repository into their own Databricks Repo folder, makes changes to a notebook on a feature branch, and wants their teammates to be able to review the changes before they are merged into the main branch. Which workflow correctly accomplishes this using Databricks Repos and Git?

Accepted Answer

Commit and push the feature branch from the Databricks Repo, then open a pull request in the Git provider for teammates to review before merging into main.

Answer

Export the notebook as a .dbc archive and email it to teammates for manual review, then import it into the shared workspace folder.

Answer

Directly commit the changes to the main branch from the Databricks Repo so teammates see them immediately in production.

Answer

Copy the notebook into a shared workspace folder outside of Repos so all teammates can edit it simultaneously.

Question 44

A data engineering team runs a streaming job that continuously appends records to a Delta table while an analyst simultaneously runs a large aggregation query against the same table. The analyst is concerned that they might read a mix of old and newly written data mid-transaction, producing inconsistent results. Which characteristic of Delta Lake ensures the analyst's query returns consistent results?

Accepted Answer

Delta Lake provides snapshot isolation via the transaction log, so the read query sees a consistent version of the table as of the moment the query started.

Answer

Delta Lake locks the entire table during writes, blocking all read queries until the append completes.

Answer

Delta Lake automatically pauses the streaming write whenever a read query begins to avoid conflicts.

Answer

Delta Lake caches the query result in memory so the analyst never reads directly from the underlying files.

Question 45

An analyst runs the same dashboard query dozens of times per hour against a large Delta table. Each execution rereads the same Parquet files from cloud object storage, and the Spark UI shows most of the stage time spent in remote reads rather than compute. The data changes only once per day after an overnight batch load. Which optimization will most directly reduce the repeated remote-read latency for these interactive queries?

Accepted Answer

Enable disk caching (Delta cache) on the cluster so frequently accessed Parquet files are stored on local SSDs of the worker nodes

Answer

Increase the number of shuffle partitions with spark.sql.shuffle.partitions to parallelize the reads

Answer

Run VACUUM on the table with a zero-hour retention to remove stale files and speed up scans

Answer

Repartition the DataFrame in memory before each query using repartition(200)

Question 46

A data engineer is designing a Silver Delta table for e-commerce events. Queries almost always filter on the event date, but the source only provides an event_timestamp column of type TIMESTAMP. The engineer wants the table to be physically partitioned by date without requiring downstream writers to manually compute and populate a separate date column on every insert. Which approach best satisfies this requirement?

Accepted Answer

Define a generated column, e.g. event_date DATE GENERATED ALWAYS AS (CAST(event_timestamp AS DATE)), and partition the table by event_date.

Answer

Partition the table directly by event_timestamp so Delta automatically buckets rows into daily folders.

Answer

Create a regular event_date column and require every writer to include a CAST(event_timestamp AS DATE) expression in each INSERT statement.

Answer

Run OPTIMIZE with ZORDER BY (event_timestamp) instead of partitioning to physically organize data by date.

Question 47

A data engineering team is evaluating whether to standardize their lakehouse tables on Delta Lake. A senior architect raises a concern about vendor lock-in and asks how Delta Lake tables physically store their underlying data in cloud object storage. Which statement correctly describes the storage format used by Delta Lake?

Accepted Answer

Delta Lake stores data in an open Parquet file format alongside a transaction log (the _delta_log directory) containing JSON and checkpoint files.

Answer

Delta Lake stores data in a proprietary binary format that can only be read by Databricks Runtime.

Answer

Delta Lake stores data as compressed CSV files with a separate metadata database managed by the metastore.

Answer

Delta Lake stores data exclusively in ORC files with row-level indexes embedded in the file footer.

Question 48

A data engineer runs a MERGE INTO command to upsert a batch of customer updates into a Delta target table. The source DataFrame contains multiple rows with the same customer_id (the merge key) because the daily extract captured several updates per customer. The MERGE fails with an error stating that a matched target row was matched by more than one source row. What is the correct approach to resolve this while keeping only the most recent update per customer?

Accepted Answer

Deduplicate the source before the MERGE by using a window function to keep only the latest record per customer_id, then run the MERGE against the deduplicated source.

Answer

Add the SKIP DUPLICATES clause to the MERGE statement so Delta automatically retains one row per key.

Answer

Change the target table to allow multiple matches by disabling ACID enforcement with a table property.

Answer

Repartition the source DataFrame by customer_id so each partition contains only one row per key before the MERGE.

Question 49

A data engineer maintains a Silver customer table in Delta Lake. Each night a source extract arrives that contains both existing customers (already loaded) and brand-new customers. The business rule is strict: existing customer rows must NEVER be modified, but any customer not yet in the table must be inserted. Which MERGE statement correctly implements this requirement?

Accepted Answer

MERGE INTO silver_customers t USING updates s ON t.customer_id = s.customer_id WHEN NOT MATCHED THEN INSERT *

Answer

MERGE INTO silver_customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *

Answer

MERGE INTO silver_customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT *

Answer

INSERT OVERWRITE silver_customers SELECT * FROM updates

Question 50

A data engineer is writing a MERGE statement to update a Delta table of customer accounts. Business rules require that when a matched record has a status of 'CLOSED' in the source, the target row should be deleted; for all other matched records, the target row should be updated with the new balance. Which MERGE clause construction correctly implements both behaviors?

Accepted Answer

Use two WHEN MATCHED clauses: the first with condition `s.status = 'CLOSED'` THEN DELETE, followed by a second WHEN MATCHED THEN UPDATE SET t.balance = s.balance.

Answer

Use a single WHEN MATCHED THEN UPDATE clause and rely on a separate DELETE statement run before the MERGE.

Answer

Use two WHEN MATCHED clauses ordered as UPDATE first, then the DELETE with condition `s.status = 'CLOSED'`, since order does not affect evaluation.

Answer

Use a single WHEN MATCHED clause with a CASE expression that returns DELETE or UPDATE based on `s.status`.

Question 51

A data engineer maintains a Silver dimension table `dim_customer` and receives a daily batch of updated customer records in a staging view `stg_customer`. Both tables share the key `customer_id`. The requirement is: update existing customers with new attribute values, and insert customers that do not yet exist. Which single Spark SQL statement correctly implements this upsert?

Accepted Answer

MERGE INTO dim_customer t USING stg_customer s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *

Answer

INSERT OVERWRITE dim_customer SELECT * FROM stg_customer

Answer

MERGE INTO dim_customer t USING stg_customer s ON t.customer_id = s.customer_id WHEN NOT MATCHED BY SOURCE THEN DELETE

Answer

INSERT INTO dim_customer SELECT * FROM stg_customer

Question 52

A data engineer maintains a Silver Delta table 'customers' that must be fully synchronized with a daily full snapshot loaded into a staging view 'stg_customers'. The requirement is: insert new customers, update changed customers, and delete customers that no longer exist in the daily snapshot. Which single MERGE statement clause correctly handles the deletion of records that are present in the target but absent from the source?

Accepted Answer

WHEN NOT MATCHED BY SOURCE THEN DELETE

Answer

WHEN NOT MATCHED THEN DELETE

Answer

WHEN MATCHED AND source.id IS NULL THEN DELETE

Answer

WHEN NOT MATCHED BY TARGET THEN DELETE

Question 53

A data engineer maintains a Silver Delta table `customers` and receives a daily source batch `updates` containing both new and existing customer records, keyed by `customer_id`. The requirement is: when a `customer_id` already exists in the target, update its `email` and `status` columns; when a `customer_id` does not exist in the target, insert the full new row. Which single Spark SQL statement correctly implements this in one pass?

Accepted Answer

MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET t.email = s.email, t.status = s.status WHEN NOT MATCHED THEN INSERT *

Answer

INSERT INTO customers SELECT * FROM updates; then run UPDATE customers SET email = ... to fix duplicates afterward

Answer

MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN NOT MATCHED THEN UPDATE SET t.email = s.email WHEN MATCHED THEN INSERT *

Answer

INSERT OVERWRITE customers SELECT * FROM updates

Question 54

A data engineer notices that a Delta table used for hourly streaming ingestion has accumulated thousands of tiny files over the past week, slowing down downstream read queries. The engineer wants to compact these small files into larger ones to improve read performance without changing the table's data or partitioning scheme. Which command should the engineer run?

Accepted Answer

OPTIMIZE table_name

Answer

VACUUM table_name

Answer

REFRESH TABLE table_name

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS

Question 55

A data engineer manages a Delta table that is continuously written to by a Structured Streaming job with a short micro-batch interval. Analysts complain that read queries against this table have become progressively slower over several days. In the Spark UI, they notice queries are launching thousands of tiny file-scan tasks. The table has no partitioning issues and query filters are appropriate. What is the most likely root cause and appropriate remediation?

Accepted Answer

The streaming job is producing many small files per micro-batch; schedule periodic OPTIMIZE to compact them into larger files.

Answer

The table is missing a Z-ORDER index on the join key; run ANALYZE TABLE to rebuild statistics.

Answer

Time travel history is accumulating; run VACUUM with a zero-hour retention to remove old versions and speed up reads.

Answer

The Delta transaction log is corrupted; recreate the table with CREATE TABLE AS SELECT to reset it.

Question 56

A data engineer is creating a Silver Delta table for web clickstream events. The table will hold billions of rows, and analysts almost always filter queries by event_date. A colleague suggests partitioning the table by user_id because most joins reference that column. What is the best partitioning strategy, and why?

Accepted Answer

Partition by event_date, because it is a low-cardinality column frequently used in filter predicates, enabling effective partition pruning.

Answer

Partition by user_id, because partitioning on the most-joined column always improves join performance the most.

Answer

Partition by both event_date and user_id together to satisfy both filtering and joining patterns.

Answer

Do not partition at all; Delta tables cannot benefit from partitioning on any column.

Question 57

A data engineer notices that a critical Delta table in the silver layer has produced unexpected row counts after a series of overnight jobs. Before deciding whether to roll back, they need to review a chronological log of every operation performed on the table, including the operation type (WRITE, MERGE, DELETE), the user, and the version number for each change. Which approach lets them retrieve this information directly?

Accepted Answer

Run DESCRIBE HISTORY on the Delta table to view the transaction log of versions and operations

Answer

Run DESCRIBE DETAIL on the Delta table to list the version-by-version operation log

Answer

Query the table using a VERSION AS OF clause for each possible version until the change is found

Answer

Run VACUUM on the table to expose the retained history of prior operations

Question 58

A data engineer inspects a Delta table's storage location in cloud object storage and finds a subfolder named _delta_log containing several JSON files and one Parquet checkpoint file, alongside the data Parquet files. A junior colleague asks what the _delta_log directory is responsible for. Which statement best describes its purpose?

Accepted Answer

It stores the ordered transaction log that records every commit, enabling ACID guarantees and time travel.

Answer

It caches query results so that repeated reads of the table return faster without scanning data files.

Answer

It holds backup copies of the data Parquet files in case the primary files become corrupted.

Answer

It contains the compiled physical execution plan generated by the Spark optimizer for the table.

Question 59

A data engineer accidentally ran a DELETE statement that removed thousands of rows from a Delta table named sales_transactions 20 minutes ago. No VACUUM has been run since the mistake, and the table retention settings are at their defaults. The engineer needs to restore the table to its exact state before the erroneous DELETE. Which approach will correctly recover the data?

Accepted Answer

Use RESTORE TABLE sales_transactions TO VERSION AS OF <version-before-delete>, identifying the version with DESCRIBE HISTORY.

Answer

Run OPTIMIZE sales_transactions to rebuild the data files and recover the deleted rows automatically.

Answer

Re-run the DELETE statement, which toggles the deleted rows back into the table via ACID rollback.

Answer

Immediately run VACUUM sales_transactions to reclaim the deleted rows from the transaction log.

Question 60

A data engineer needs to add a column to a Spark DataFrame of sales transactions that shows, for each row, the total number of transactions made by that customer — while still keeping every individual transaction row in the output. Which approach produces the correct result without collapsing rows?

Accepted Answer

Apply a window function: count('*').over(Window.partitionBy('customer_id')) as a new column

Answer

Use groupBy('customer_id').count() and use the resulting DataFrame as the final output

Answer

Use groupBy('customer_id').agg(count('*')) then drop duplicate customer rows

Answer

Apply select(countDistinct('transaction_id')) over the entire DataFrame

Question 61

A data engineer manages a Lakeflow Declarative (DLT) pipeline that applies several data quality expectations across bronze and silver tables. After a production run, stakeholders report that some downstream metrics look off, and the engineer needs to determine how many records failed each expectation over the last several pipeline updates without manually re-running the pipeline. Which approach provides this historical data quality information most directly?

Accepted Answer

Query the pipeline's event log, filtering on the 'flow_progress' events to read the data quality metrics recorded for each expectation.

Answer

Open the Spark UI for the most recent run and inspect the Stages tab for records dropped during each transformation.

Answer

Run DESCRIBE HISTORY on each target Delta table to view the count of rows rejected by each expectation.

Answer

Re-run the pipeline in development mode and watch the notebook cell output for expectation violation counts.

Question 62

A data engineer is building a Lakeflow Declarative Pipeline (formerly Delta Live Tables) for a silver customer table. Business rules require that any record with a NULL email be excluded from the resulting table, but the pipeline must continue processing all other valid records without failing. Which expectation clause should the engineer use?

Accepted Answer

@dlt.expect_or_drop("valid_email", "email IS NOT NULL")

Answer

@dlt.expect("valid_email", "email IS NOT NULL")

Answer

@dlt.expect_or_fail("valid_email", "email IS NOT NULL")

Answer

@dlt.expect_all("valid_email", "email IS NULL")

Question 63

A data engineer is building a Lakeflow Declarative Pipeline (formerly Delta Live Tables) for a regulated financial dataset. The compliance team requires that if any record violates the constraint that transaction_amount must be greater than zero, the entire pipeline update must immediately stop and fail so that no partial data is published. Which expectation configuration should the engineer use?

Accepted Answer

@dlt.expect_or_fail("valid_amount", "transaction_amount > 0")

Answer

@dlt.expect("valid_amount", "transaction_amount > 0")

Answer

@dlt.expect_or_drop("valid_amount", "transaction_amount > 0")

Answer

@dlt.expect_all("valid_amount", "transaction_amount > 0")

Question 64

A data engineer defines a Lakeflow Declarative Pipeline (Delta Live Tables) table with two expectations on a single dataset: one uses @dlt.expect_or_drop for a 'valid_amount' rule and another uses @dlt.expect (warn only) for a 'complete_customer' rule. During an update, a record fails the 'valid_amount' rule but passes 'complete_customer', while a second record passes 'valid_amount' but fails 'complete_customer'. What happens to these two records in the target table?

Accepted Answer

The first record is dropped; the second record is retained and its violation is only recorded in the metrics.

Answer

Both records are dropped because any expectation violation removes the row.

Answer

The pipeline update fails immediately because two conflicting expectations cannot be applied to the same dataset.

Answer

Both records are retained because expect_or_drop and expect only log violations without removing rows.

Exam domain	Exam weight	Practice
Databricks Intelligence Platform	6%	Practice this topic
Data Ingestion and Loading	21%	Practice this topic
Data Transformation and Modeling	22%	Practice this topic
Working with Lakeflow Jobs	16%	Practice this topic
Implementing CI/CD	10%	Practice this topic
Troubleshooting, Monitoring, and Optimization	10%	Practice this topic
Governance and Security	15%	Practice this topic

Databricks Certified Data Engineer Associate (Databricks Data Engineer Associate) practice exam & study guide

Start studying Databricks Data Engineer Associate

All Databricks Data Engineer Associate study resources

Databricks Data Engineer Associate exam domains

Sample Databricks Data Engineer Associate questions

Key Databricks Data Engineer Associate terms

Databricks Data Engineer Associate frequently asked questions

Databricks Intelligence Platform (6%)

Data Ingestion and Loading (21%)

Data Transformation and Modeling (22%)

Working with Lakeflow Jobs (16%)

Implementing CI/CD (10%)

Troubleshooting, Monitoring, and Optimization (10%)

Governance and Security (15%)