CertLakeDatabricks Cert Prep

Databricks Data Engineer Associate practice questions: worked examples

Five Databricks Data Engineer Associate practice questions as worked examples, each showing a different exam technique, from spotting qualifiers to splitting a tie between two true answers.

By , Databricks MVP and Solutions Architect Champion · · Facts checked against the official docs on

These five questions are in the style of the Databricks Certified Data Engineer Associate exam. Each one is a worked example of a different exam technique, so you come away with five ways to crack questions you haven't seen, not just five answers. The questions were written for an earlier version of the exam, cover topics the current exam (May 2026) still tests, and were checked against the Databricks documentation on 2 November 2026. CertLake's practice exams use different questions.

Try each question before you open the answer.

Five techniques for exam questions

Each question below shows one of these in action:

  1. Find the qualifiers (question 1). Words like single, atomic, persist and only often rule out options before you need to know the answer.
  2. Read the syntax literally, and watch for flipped pairs (question 2). A command's name usually says what it does, and one distractor is often the right answer reversed.
  3. Remove the distractors by naming them (question 3). Wrong options are rarely random. Say what each one really describes, often a sibling feature, and it stops being tempting.
  4. Judge multiple-answer options one at a time (question 4). Mark each option true or false on its own, then check the count matches what the question asks for.
  5. Split a tie by answering exactly what's asked (question 5). When several options are real features, pick the one that fits the precise ask, and treat "true, but not what was asked" as wrong.

Two habits help on every question: read the last sentence first, so you know what's being asked before you read the scenario, and know both the old and new product names. The exam uses current names, such as Lakeflow pipelines (formerly Delta Live Tables), but a lot of practice material still uses the old ones.

1. Updating and inserting rows in one statement

Which SQL command should be used to update existing records and insert new records in a single atomic operation?

  • A. UPDATE ... INSERT
  • B. UPSERT INTO
  • C. MERGE INTO
  • D. INSERT OR UPDATE
Show answer

Answer: C. MERGE INTO

Technique: find the qualifiers. Two phrases in the question do most of the work.

How to work it out

  1. "Update existing records and insert new records" is an upsert.
  2. "Single atomic operation" means one statement, so A, which is two statements, is out.
  3. B and D aren't Databricks SQL statements. They sound like upserts, which is what makes them good distractors.

Remember: on Delta tables, an upsert is MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT .... It's atomic because every Delta write is a transaction in the table's log.

Why the others are wrong

  • A. Not one statement, so it can't be a single atomic operation.
  • B. UPSERT INTO isn't a Databricks SQL statement. Databricks performs upserts with MERGE INTO.
  • D. INSERT OR UPDATE isn't valid Databricks SQL syntax.

Docs: MERGE INTO · Upsert into a Delta Lake table using merge

2. CREATE OR REPLACE TABLE vs CREATE TABLE IF NOT EXISTS

What is the difference between CREATE OR REPLACE TABLE and CREATE TABLE IF NOT EXISTS?

  • A. CREATE OR REPLACE TABLE fails if the table exists, while CREATE TABLE IF NOT EXISTS overwrites the existing table with the new one
  • B. CREATE OR REPLACE TABLE atomically replaces the existing table's definition and data; CREATE TABLE IF NOT EXISTS does nothing if the table already exists
  • C. They behave the same when the table exists: both keep the current data and update the schema to match
  • D. CREATE OR REPLACE TABLE works with external tables, while CREATE TABLE IF NOT EXISTS works with managed tables
Show answer

Answer: B.

Technique: read the syntax literally, and watch for flipped pairs. You can answer this from the clause names alone.

How to work it out

  1. Take the clauses literally. Or replace replaces what's there. If not exists only acts when nothing is there.
  2. A is the right answer reversed. When two options mirror each other, one of them is often correct, so check which way round matches the names.
  3. C contradicts both clause names, and D invents a managed-versus-external split that neither statement has.

Remember: CREATE OR REPLACE TABLE is not a drop and re-create. The table keeps its identity, its history (so RESTORE still works) and its granted privileges, and the replace is a single transaction, so readers never see the table missing. Databricks recommends it over dropping and re-creating. It's supported for Delta and Apache Iceberg tables.

Why the others are wrong

  • A. Backwards: CREATE OR REPLACE replaces an existing table, and IF NOT EXISTS leaves it untouched.
  • C. Only IF NOT EXISTS leaves an existing table alone, and it doesn't change the schema either.
  • D. Neither statement is tied to managed or external tables.

Docs: CREATE TABLE [USING] · Drop or replace a Delta table

3. Data quality expectations in a pipeline

In Lakeflow pipelines (formerly Delta Live Tables), what happens when a record violates this expectation?

@dp.expect_or_fail("valid_amount", "amount > 0")
  • A. The record is written to the target, and the violation is counted in the pipeline's data quality metrics
  • B. The record is dropped, and processing continues with the valid records
  • C. The pipeline update fails, and manual intervention is needed before reprocessing
  • D. The record is moved to a quarantine table
Show answer

Answer: C.

Technique: remove the distractors by naming them. Two of the wrong options are real behaviours, just not this one's.

How to work it out

  1. The decorator's name tells you the action: expect, expect_or_drop or expect_or_fail.
  2. Name each option: A is plain expect (warn), B is expect_or_drop, and C is expect_or_fail.
  3. D sounds sensible, but it's not an expectation action at all: nothing routes rows to another table. Once every option has a name, only C matches the decorator.

Remember: the three actions.

ActionPythonSQLInvalid records
warn (default)dp.expectCONSTRAINT name EXPECT (...)Written to the target, and counted in the metrics
dropdp.expect_or_drop... ON VIOLATION DROP ROWDropped before writing, and counted
faildp.expect_or_fail... ON VIOLATION FAIL UPDATEThe update fails, and needs fixing before reprocessing

In a triggered pipeline, a failed flow doesn't fail the other flows running alongside it. dp is the current Python module (from pyspark import pipelines as dp), and import dlt still works in older code.

Why the others are wrong

  • A. Writing invalid records while recording metrics is the default warn action, expect.
  • B. Dropping invalid records is expect_or_drop.
  • D. Quarantining needs your own logic, such as a flag column or a separate table. No expectation action does it.

Docs: Manage data quality with pipeline expectations

4. What a pipeline stores as tables

Which TWO dataset types does a Lakeflow pipeline (formerly Delta Live Tables) persist as Unity Catalog tables? (Select TWO.)

  • A. Streaming tables
  • B. Materialized views
  • C. External tables
  • D. Global temporary views
  • E. Virtual tables
Show answer

Answer: A and B.

Technique: judge multiple-answer options one at a time. Don't hunt for a pair. Test each option against the question on its own, then count.

How to work it out

  1. The test for each option is: "is this a pipeline dataset type, and is it stored?"
  2. A streaming tables: a pipeline dataset type, stored. True.
  3. B materialized views: a pipeline dataset type, stored. True.
  4. C external tables: a real Unity Catalog table type, but not something a pipeline creates. False.
  5. D global temporary views: a Spark session object, not a pipeline dataset type. False.
  6. E virtual tables: not a Databricks concept. False.
  7. Two trues for "Select TWO", so the count checks out. If you get one or three, re-read the question, because you've misjudged an option.

Remember: streaming tables process each record once from an append-only or streaming source. Materialized views are recomputed, incrementally where possible, to match their source. Both are Unity Catalog managed tables.

Why the others are wrong

  • C. External tables are a Unity Catalog table type, not a pipeline dataset type. Pipeline tables are managed tables.
  • D. Global temporary views belong to a Spark session, not to a pipeline.
  • E. "Virtual tables" isn't a pipeline concept.

Docs: Lakeflow pipeline concepts

5. Tracing data from source to downstream tables

Which Unity Catalog feature lets you trace how data flows from source tables through transformations to downstream tables?

  • A. Table history (DESCRIBE HISTORY)
  • B. Data lineage
  • C. Audit logs
  • D. Access controls
Show answer

Answer: B. Data lineage

Technique: split a tie by answering exactly what's asked. All four options are real Unity Catalog features, so knowing what exists isn't enough. The deciding detail is what each one tracks.

How to work it out

  1. Pin down the ask: "flows from source tables ... to downstream tables" is about relationships between objects.
  2. A looks close, because history is about how data changed. But it tracks one table over time, not data moving between tables.
  3. C tracks who did what, and D controls who's allowed to. Neither follows data.
  4. Only B is about the path data takes, so B is the best answer, even though A, C and D are all true statements about Unity Catalog.

Remember: Unity Catalog captures lineage automatically for queries run on Databricks, down to the column level. It shows upstream sources and downstream consumers (tables, views, notebooks, jobs, pipelines and dashboards). You can see it on the Lineage tab in Catalog Explorer.

Why the others are wrong

  • A. DESCRIBE HISTORY lists the writes to a single table (operation, user and time). It doesn't link tables to each other.
  • C. Audit logs record actions and who performed them, not how data moves between tables.
  • D. Access controls decide who can use an object. They don't track where its data came from.

Docs: Data lineage in Unity Catalog

What to do next

If any of these caught you out, that's the domain to study first. The exam overview has the full list of domains and weights, and the cheat sheet covers the rules and defaults these questions lean on.

See where you stand on Data Engineer Associate

The free diagnostic shows which exam domains you'd lose marks on, in about 10 minutes and without signing up.

Take the free diagnosticEverything for Data Engineer Associate

Spotted something out of date? Email support@certlake.com.