LevelUpWith

Explore / Databricks Certified Data Engineer Associate Exam Prep / Practice questions

Databricks Certified Data Engineer Associate practice questions

10 questions from the bank behind this course's timed mock. Free to read and to answer, no account needed, and each has its answer and an explanation. They belong to Databricks Certified Data Engineer Associate Exam Prep.

Answer them one at a time →Read lesson 1 free

  1. Question 1

    A data engineer taking the Databricks Certified Data Engineer Associate exam counts more than 45 questions on screen. A colleague had told them some questions are unscored items that Databricks uses to gather statistics. The engineer wants to get the highest possible score in the time they have. What is the best approach?

    1. Spend less time on every question so the extra questions fit inside the standard 90 minutes
    2. Answer only the first 45 questions carefully, because the scored items always come first
    3. Treat every question as scored and answer each one with the same care, because you can't tell which items are unscored
    4. Skip questions that look unusual or experimental, because they are probably the unscored items
    Show the answer

    Answer: C. Treat every question as scored and answer each one with the same care, because you can't tell which items are unscored

    Unscored items look the same as scored ones, so the only safe approach is to treat every question as if it counts. Skipping questions that look unusual is a guess that can cost real marks, and Databricks already adds extra time for the unscored items, so there's no need to rush.

  2. Question 2

    A data engineer parses a vendor feed with `from_json(raw, 'order_id STRING, total DOUBLE')`. The vendor's JSON now also contains a `discount` field, and some older records have no `total` key at all. The engineer wants to know how `from_json` handles these mismatches before adding data-quality checks. Which TWO statements are true? (Select TWO.)

    More than one answer is right.

    1. Records with no `total` key return NULL for `total`.
    2. Records with no `total` key are left out of the result.
    3. The `discount` field is added automatically as an extra STRING field in the struct.
    4. The query fails with a schema-mismatch error when `discount` appears.
    5. The `discount` field is silently dropped because it is not in the schema.
    Show the answer

    Answers: A. Records with no `total` key return NULL for `total`.; E. The `discount` field is silently dropped because it is not in the schema.

    `from_json` keeps only the fields in the schema you give it, and a schema field that's missing from the JSON comes back NULL. No error is raised, so you need to check row counts and null rates yourself. It doesn't add unknown fields and it doesn't drop rows, so the 'extra STRING field' and 'left out of the result' options are wrong.

  3. Question 3

    A data engineer is creating a streaming table over /Volumes/shop/raw/landing/orders/. The folder already holds two years of historical JSON files, and a separate team has already loaded them into another table. The new streaming table must capture only files that land after it is created, and it must keep ingesting incrementally after that. What should the engineer do?

    1. Create the streaming table normally, then immediately run REFRESH STREAMING TABLE ... FULL so that only new files are kept
    2. Add includeExistingFiles => false to the STREAM read_files(...) call in the CREATE OR REFRESH STREAMING TABLE statement
    3. Remove the SCHEDULE EVERY 1 HOUR clause so that the first refresh does not run when the table is created
    4. Remove the STREAM keyword so that read_files skips the files already in the folder
    Show the answer

    Answer: B. Add includeExistingFiles => false to the STREAM read_files(...) call in the CREATE OR REFRESH STREAMING TABLE statement

    By default, read_files ingests every file already in the folder on the first refresh, and includeExistingFiles => false limits the table to files that arrive later. A FULL refresh does the opposite: it truncates the table and rereads all the source data still available, historical files included.

  4. Question 4

    A data engineer reviews a Lakeflow Spark Declarative Pipeline. The bronze streaming table orders_bronze uses read_files on a Unity Catalog volume and adds _metadata.file_path and current_timestamp() columns. A colleague notices that the source sometimes delivers the same order twice and that both copies appear in orders_bronze. The colleague wants every downstream table to show each order only once. What is the best action?

    1. Deduplicate only in the gold materialized views, because aggregation will absorb the duplicates anyway
    2. Drop the _metadata.file_path and ingestion timestamp columns so the duplicate rows become identical and collapse
    3. Add a DISTINCT to the bronze query so duplicates never reach the lakehouse
    4. Leave orders_bronze as it is and deduplicate in the silver streaming table that reads from it
    Show the answer

    Answer: D. Leave orders_bronze as it is and deduplicate in the silver streaming table that reads from it

    Bronze is append-only and keeps data exactly as received, including duplicates and lineage metadata. Deduplication is a silver task. Running DISTINCT in bronze would change the raw replay log and break its role as a complete record of what the source sent.

  5. Question 5

    A data engineer maintains silver.customers with a MERGE that uses ON t.customer_id = s.customer_id and has these clauses, in this order: WHEN MATCHED AND s.status = 'closed' THEN DELETE WHEN MATCHED AND s.ingest_ts > t.updated_at THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT * The deduplicated source has one row for customer 42, which already exists in the target. That row has status = 'closed' and an ingest_ts newer than the target's updated_at. What happens to customer 42?

    1. The MERGE fails, because two WHEN MATCHED clauses are true for the same target row
    2. The row is updated first and then deleted, because both MATCHED conditions are true
    3. The row is deleted, because MATCHED clauses are checked in order and only the first one whose condition is true runs
    4. The row is updated, because the timestamp condition takes priority over the status condition
    Show the answer

    Answer: C. The row is deleted, because MATCHED clauses are checked in order and only the first one whose condition is true runs

    When there are several clauses of the same type, they are checked in order and the first one whose condition is true is the only one that runs, so the DELETE fires. The MERGE does not fail: that error comes from several source rows matching one target row, not from several clauses being true for one row.

  6. Question 6

    A data engineer is adding a SQL task to a Lakeflow Job. The task runs a saved query that refreshes a reporting table every hour. The team wants the compute that Databricks recommends for this task type. What should the task run on?

    1. The compute of a Lakeflow Declarative Pipeline in the same workspace
    2. A serverless SQL warehouse
    3. The team's existing all-purpose cluster, referenced with existing_cluster_id
    4. A job cluster defined under job_clusters and referenced with job_cluster_key
    Show the answer

    Answer: B. A serverless SQL warehouse

    SQL tasks run on a SQL warehouse, and Databricks recommends a serverless SQL warehouse. A job cluster is real jobs compute for notebook, Python and JAR tasks, but it is not what a SQL task runs on.

  7. Question 7

    A data engineer runs a Lakeflow Job whose `ingest_orders` task has `max_retries = 3`. An `on_failure` email notification is configured at the job level. The SRE team says they want an email every time an `ingest_orders` attempt fails, even when a later retry succeeds, so they can spot storage throttling patterns. What should the engineer do?

    1. Set retry_on_timeout = true on the ingest_orders task so each failed attempt is reported to the job-level notification
    2. Add an on_failure notification on the ingest_orders task itself and leave alert_on_last_attempt disabled
    3. Add a RUN_DURATION_SECONDS health rule to the job and notify on_duration_warning_threshold_exceeded
    4. Set alert_on_last_attempt = true in the job's notification_settings
    Show the answer

    Answer: B. Add an on_failure notification on the ingest_orders task itself and leave alert_on_last_attempt disabled

    Job-level notifications are not sent when failed tasks are retried, so you get an alert for every failed attempt only by putting the notification on the task. Setting alert_on_last_attempt = true does the opposite: it holds alerts back until the final retry.

  8. Question 8

    A CI runner deploys the `prod` target of a bundle. The variable `catalog` is declared with `default: dev`. The `prod` target maps `catalog: prod`. The file `.databricks/bundle/prod/variable-overrides.json` sets `catalog` to `uat`. A step earlier in the pipeline left `BUNDLE_VAR_catalog=sandbox` exported in the runner's environment. The deploy command has no `--var` flag. What does `${var.catalog}` resolve to?

    1. uat
    2. sandbox
    3. prod
    4. dev
    Show the answer

    Answer: B. sandbox

    With no `--var` flag, the `BUNDLE_VAR_catalog` environment variable has the highest precedence. It beats the overrides file, the target mapping and the default. `uat` is the nearest distractor, but the overrides file ranks below environment variables, so a leftover `BUNDLE_VAR_` silently wins.

  9. Question 9

    A data engineer owns a Unity Catalog external Delta table that a batch job appends to every few minutes. Each append leaves several small files. The engineer wants these files compacted on the writing cluster right after each successful write, without creating a separate maintenance job. What should the engineer do?

    1. Run OPTIMIZE with ZORDER BY on the append column after every write
    2. Enable auto compaction on the table by setting the delta.autoOptimize.autoCompact table property
    3. Enable predictive optimization on the table's schema so Databricks compacts it on serverless compute
    4. Add a VACUUM step after each append to remove the small files
    Show the answer

    Answer: B. Enable auto compaction on the table by setting the delta.autoOptimize.autoCompact table property

    Auto compaction runs a small compaction on the writing cluster right after each successful write. It targets 128 MB and only touches files that haven't been compacted yet. Predictive optimization is the closest distractor, but it doesn't run on external tables, and in any case it runs on serverless compute rather than the writing cluster.

  10. Question 10

    A platform admin has created the external location `finance_curated` at `s3://acme-finance/curated`. A lead data engineer needs to run `CREATE SCHEMA main.finance_gold MANAGED LOCATION 's3://acme-finance/curated/gold';` so that every managed table in the schema is stored under that path. The engineer already has USE CATALOG and CREATE SCHEMA on `main`. Which additional grant lets the statement succeed while following least privilege?

    1. GRANT CREATE EXTERNAL LOCATION ON the storage credential behind finance_curated
    2. GRANT WRITE FILES ON EXTERNAL LOCATION finance_curated
    3. GRANT CREATE EXTERNAL TABLE ON EXTERNAL LOCATION finance_curated
    4. GRANT CREATE MANAGED STORAGE ON EXTERNAL LOCATION finance_curated
    Show the answer

    Answer: D. GRANT CREATE MANAGED STORAGE ON EXTERNAL LOCATION finance_curated

    To use a path as the managed storage location of a catalog or schema, you need CREATE MANAGED STORAGE on the external location that covers that path. CREATE EXTERNAL TABLE is the closest distractor, but it only lets you register external tables with LOCATION. It doesn't let you set a schema's managed root.

The full timed mock

45 questions in 90 minutes, with a pass mark of 70%. Each paper is drawn from a bank of questions written for the mock alone, and these 10 are left out of it. Full access opens it, with every level of the course.

See the course →See the plans

LevelUpWith is independent of the organisation that sets this exam. These questions were written for this course; they are not taken from the exam.