Databricks Certified Data Engineer Professional Study Guide

Issuing body: Databricks Jurisdiction: Global (vendor certification — not a legal license to practice) Level: Professional Prerequisites: None formal; the Data Engineer Associate is the recommended foundation. (Source: catalogue record data-ai-ml-cert-014.) Renewal: 2 years (retake the current exam).

1. What this exam is

The Databricks Data Engineer Professional exam is the advanced lakehouse data-engineering credential: Delta Lake internals, performance optimization, production pipeline design, and Unity Catalog governance — at a depth the Associate exam doesn't reach. Where the Associate tests "can you build a pipeline," Professional tests "can you build one that survives 10 TB/day, 50 concurrent users, schema chaos, and an auditor." It's the Databricks-ecosystem counterpart to DEA-C01 and GCP Data Engineer, with Delta Lake as the center of gravity instead of Redshift/BigQuery.

2. Pathway and exam format

Typical path: Databricks Data Engineer Associate (Spark SQL, Delta basics, Workflows) → 1–2 years running production Databricks pipelines → this exam. Pairs with the ML Professional (the same lakehouse underneath).
Format: $200 USD, 120 minutes, ~60 questions (per 2026 guides in the catalogue), proctored online. 2-year validity. (Source: catalogue record data-ai-ml-cert-014, verified against 2026 community guides.)
The exam's mental model: you're the senior engineer owning the lakehouse. Every question is a design, debugging, or optimization scenario over Delta tables, Spark jobs, and Unity Catalog — with cost, reliability, and governance as first-class constraints.

3. Knowledge domains (with theory)

Public blueprint (approximate): Data modeling & Delta Lake (~30%), Performance optimization (~25%), Production pipelines (~25%), Governance/security (~20%). Approximate; verify against the official exam guide.

Delta Lake internals (the deepest domain)

The transaction log (the theory): Delta's _delta_log is a JSON/checkpointed ordered log of every transaction (add/remove files, schema changes, metadata). Readers get snapshot isolation: they read the log up to version N — concurrent writers don't corrupt readers. ACID on a data lake comes from this log, not from locking. Time travel (VERSION AS OF / TIMESTAMP AS OF) reads historical log versions — free for audit, debugging, and ML reproducibility.

Optimistic concurrency: writers proceed without locks; on commit, check for conflicts (concurrent writes to the same files/partitions). Conflicts → retry or fail depending on the operation (append-only rarely conflicts; overlapping updates conflict). Know the isolation levels (Serializable default vs WriteSerializable — the performance/correctness trade-off).

Schema enforcement vs evolution: enforcement (write rejected if schema doesn't match — the default, the production answer) vs evolution (mergeSchema — additive changes allowed). The exam's trap: mergeSchema in a streaming pipeline with drifting sources = schema chaos over time. Production pattern: enforcement + explicit schema-migration process.

Deletes/updates/merges: DML on Delta (DELETE/UPDATE/MERGE) rewrites affected files — partition pruning makes DML fast (only touched partitions rewritten). Z-ordering / liquid clustering co-locates related data so DML and filtered reads skip files (file-skipping via min/max stats in the log).

VACUUM: removes files no longer referenced by the log (older than the retention threshold, default 7 days). The exam's critical warning: VACUUM with too-short retention breaks time travel and concurrent readers (a long-running query reading version N while VACUUM deletes its files). Never set retention below your longest query + time-travel needs.

OPTIMIZE: bin-packs small files into ~128 MB–1 GB targets; Z-ORDER by columns for file skipping. Auto-optimize (auto-compaction + optimized writes) for streaming/continuous ingestion.

Change Data Feed (CDF): row-level change log (insert/update/delete + pre/post images) — the CDC-out answer (downstream consumers read changes, not full snapshots). Clones: shallow (metadata-only, instant — dev/test environments) vs deep (full copy — physical isolation).

Spark performance (the optimization domain)

The physical plan (the theory): Spark SQL → logical plan → optimized logical plan (Catalyst: predicate pushdown, projection pruning, join reordering) → physical plan (Tungsten: whole-stage codegen). The exam tests reading EXPLAIN: identify full scans (missing pruning), shuffles (exchanges), broadcast hints, skewed joins.

Partitioning (the #1 lever): partition by the filter column (usually date) — partition pruning skips entire directories. The small-files corollary: too-fine partitioning (by hour + region + ...) = metadata meltdown; too-coarse = no pruning. Target ~128 MB–1 GB per file, hundreds–thousands of partitions max.

Joins: broadcast hash join (small table <~10 MB auto-broadcast — no shuffle, the fastest), sort-merge join (large/large — expensive shuffle), shuffled hash join. Skewed joins: one hot key = one straggler task = the whole job waits. Fixes: salting (add random suffix to the hot key, explode the small side), AQE skew handling (adaptive query execution splits skewed partitions — enable it), broadcast the small side.

AQE (Adaptive Query Execution): runtime plan adaptation — coalesces shuffle partitions, handles skew, converts sort-merge to broadcast at runtime. Enable it always (it's the modern default for a reason).

Caching: CACHE TABLE / persist for reused DataFrames (iterative ML, repeated queries) — MEMORY_AND_DISK default; unpersist when done (cached data holds cluster memory). Delta cache (SSD caching on workers) for repeated reads.

Photon: Databricks' vectorized engine — 2–4× on scan-heavy SQL. Know when it helps (analytical scans) and that it's priced differently.

Cluster sizing: jobs clusters (ephemeral, per-job — the production answer) vs all-purpose (interactive); autoscaling for variable load; spot instances for fault-tolerant batch (with the retry discipline); right-size by monitoring (Ganglia/Spark UI: are executors idle? is shuffle the bottleneck?).

Production pipelines

Databricks Workflows: multi-task jobs (notebooks, jars, SQL, dbt, Delta Live Tables pipelines) with dependencies, retries, timeouts, notifications. The production patterns: idempotent tasks (rerunnable), task values for handoffs, repair-and-rerun for partial failures.

Delta Live Tables (DLT): declarative pipelines (@dlt.table, expectations) — expectations (data quality: EXPECT valid_id ON id IS NOT NULL with fail/drop/retain actions) as code. Medallion in DLT: bronze (raw, append) → silver (cleaned, expectations enforced) → gold (aggregates). The exam loves DLT expectations: data quality as pipeline code, not wiki documentation.

Structured Streaming: micro-batch engine; watermarks (event-time lateness bound), output modes (append/update/complete), triggers (processing-time, once, available-now — AvailableNow for incremental batch, the cost-efficient middle ground), checkpoints (WAL + state in cloud storage — exactly-once across restarts), Auto Loader (file arrival detection via cloud file notifications — the ingestion answer for landing-zone files).

Exactly-once (the theory): checkpointed offsets + idempotent Delta writes (Delta's transaction log makes replayed batches idempotent — the same batch committed twice = one version). The exam's guarantee question: exactly-once = checkpointing + transactional sink + replayable source. Miss any one and it's at-least-once.

Schema drift handling: Auto Loader schema hints + rescue column (_rescued_data captures unexpected fields — the production answer for messy sources), explicit evolution process for real changes.

Governance: Unity Catalog

The object model: catalog → schema → table/volume/function/model. Grants (SELECT, MODIFY, CREATE...) on objects; data access via clusters with credential passthrough or service principals. Lineage (automatic: table → table, table → notebook → model). Audit logs (who accessed what, when).

Volumes: managed file storage in UC (replacing DBFS mounts — DBFS mounts with long-lived credentials are the legacy anti-pattern; UC volumes + service principals are the answer).

The exam's governance scenarios: PII masking (column masks, row filters), cross-workspace sharing (Delta Sharing — the open protocol for sharing Delta tables externally, no Databricks account needed on the consumer side), and access reviews.

4. Key numbers and formulas

File sizing: 128 MB–1 GB per file; partitions in the hundreds–thousands, not millions.
Broadcast threshold: ~10 MB default (spark.sql.autoBroadcastJoinThreshold) — raise cautiously (driver memory is the limit).
Shuffle partitions: default 200; AQE coalesces automatically — with AQE on, stop hand-tuning this.
VACUUM retention: default 7 days / 168 hours; never below max(longest query, time-travel SLA, concurrent-reader window).
Delta log checkpointing: every 10 commits (parquet checkpoints) — readers don't replay thousands of JSONs.
Streaming trigger math: micro-batch interval vs latency vs cost — 1-min triggers ≈ 1-min latency + steady cluster cost; AvailableNow = process new files then stop (batch economics, near-streaming freshness).
Cluster cost: DBU × instance cost; jobs clusters cheaper than all-purpose; spot 50–90% off with retry-tolerant workloads.

5. Worked examples

Example 1 — Skewed join rescue. Scenario: 2 TB fact join 50 GB dim on customer_id; job takes 6 hours, one task runs 5.5 hours (hot key: a "default" customer with 30% of rows). Solution: (1) Enable AQE skew-split (handles moderate skew automatically). (2) For the pathological key: salt — split the hot key (customer_id + random(0..N)) on the fact side, explode the dim side N× for that key (or filter the hot key into a separate broadcast join and union). (3) Long-term: fix the source (the "default" customer is a data-quality bug — route to quarantine). Verify: Spark UI stage skew (max/median task duration ratio → ~1). The exam's lesson: skew is a data problem wearing a performance costume.

Example 2 — DLT medallion with expectations. Scenario: IoT JSON landing hourly; need validated silver + gold aggregates; bad records must not halt the pipeline. Solution: DLT pipeline: bronze (@dlt.table reading Auto Loader with _rescued_data), silver (expectations: EXPECT valid_ts ON ts IS NOT NULL, EXPECT positive_temp ON temp > -100 with drop action for violators — quarantine table for the dropped), gold (hourly aggregates). AvailableNow trigger (hourly freshness, batch cost). Monitoring: expectation metrics in the DLT UI; alert on quarantine growth rate. The exam's pattern: expectations-as-code > post-hoc data-quality reports.

Example 3 — Schema drift in production. Scenario: Upstream adds/removes JSON fields weekly; pipeline breaks monthly. Solution: Auto Loader with schema hints (known fields typed) + _rescued_data column (unknown fields captured, not dropped); enforcement on the silver table (known schema, explicit migration process for real changes); alert on rescued-data growth (drift signal). Never mergeSchema on autopilot in production — additive drift compounds into schema soup. The process answer: drift detected → reviewed → explicit migration.

Example 4 — Time-travel forensics. Scenario: "Yesterday's report was wrong; the table looks fine now." Diagnose. Solution: SELECT ... VERSION AS OF / TIMESTAMP AS OF yesterday's version → diff vs current (find the bad batch); check the Delta history (DESCRIBE HISTORY — which job wrote version N, when); root-cause the writing job (late data? bad join?); fix forward (don't rewrite history — append the correction, or restore the table to the good version if the bad writes are fully identified). VACUUM retention must exceed the forensics window — this is why 7-day default exists.

Example 5 — Cost-optimized continuous ingestion. Scenario: 500 GB/day landing files; need <15 min freshness; minimize cost. Solution: Auto Loader (incremental file detection — no LIST storms) + AvailableNow trigger (process new files every 10 min, cluster terminates between runs — batch economics) + auto-optimize (compaction) + Photon for the transform. Jobs cluster (ephemeral) with spot workers. Estimated: ~5–10× cheaper than 24/7 streaming cluster for the same freshness. The exam's lesson: match the compute pattern to the freshness requirement, not the hype.

6. Standards and regulations cross-reference

GDPR — erasure in Delta (DML DELETE + VACUUM with retention discipline; CDF for propagating deletes downstream), lineage for DSARs (Unity Catalog), data minimization at ingestion.
DAMA-DMBOK — the data-management vocabulary (UC implements much of it: cataloging, lineage, quality, security).
ISO/IEC 27001 — access control (UC grants), audit logging, backup (Delta clones/snapshots as the recovery story).
PCI DSS v4.0.1 — tokenize PAN before bronze; scope cardholder tables; column masking for analysts; no PAN in _rescued_data/logs (scrub the rescue column too — the forgotten leak).
SOC 2 — pipeline change control (bundles/versioned jobs), access reviews (UC grants audited), evidence from audit logs.
EU AI Act / SR 11-7 — when tables feed regulated models: data quality gates (DLT expectations as the evidence), lineage, documented provenance.

7. Pitfalls, traps, and exam strategy

Trap patterns:

The small-files trap: streaming/partitioning without compaction = the #1 Databricks perf killer. OPTIMIZE/auto-compact is always part of the answer.
The VACUUM trap: retention shorter than time-travel/concurrent-reader needs = corruption. 7 days default exists for a reason.
The mergeSchema trap: auto-evolution in production = schema soup. Enforcement + explicit migration.
The broadcast trap: raising the broadcast threshold until the driver OOMs. AQE + default threshold first.
The DBFS-mount trap: legacy mounts with long-lived tokens — the modern answer is UC volumes + service principals.
The trigger trap: continuous streaming cluster for 15-min freshness needs — AvailableNow gives batch economics. Match compute pattern to freshness.

Strategy: the exam is Delta Lake + Spark internals + production judgment. Memorize: the transaction-log mechanics (snapshot isolation, optimistic concurrency), VACUUM/OPTIMIZE semantics, the join strategies and skew fixes, DLT expectations, Structured Streaming guarantees (checkpoint + idempotent sink + replayable source), and the UC object/grant model. For scenario questions, identify the bottleneck (skew? small files? shuffle? freshness?) before choosing the fix.

8. Practice problems

P1. A Delta table has 1M files averaging 100 KB after a year of streaming. Queries are 50× slower than expected. Give the remediation and the prevention. Answer: Remediation: OPTIMIZE (bin-pack to ~1 GB; may need partition-by-partition for a huge table) + enable auto-compaction. Prevention: auto-optimize on write, AvailableNow trigger with larger batches, partition by date (bounds per-partition file counts). The 100 KB files mean the writer committed per-micro-batch without compaction — the classic streaming anti-pattern.

P2. VACUUM was run with 24-hour retention; a 3-day-long ML training job now fails reading its input table. Explain and fix. Answer: The training job's snapshot referenced files VACUUM deleted (retention < reader lifetime). Fix: restore from... the files are gone — re-materialize the training data (Delta time travel can't help; the log references deleted files). Prevention: retention ≥ longest reader + time-travel SLA (keep 7+ days; set per-table). The exam's lesson: VACUUM retention is a correctness parameter, not a storage-saving knob.

P3. Two concurrent jobs append to the same Delta table; occasionally one fails with a concurrent-modification error. When is this safe to retry, and when does it indicate a design problem? Answer: Append-only concurrent writes rarely conflict (different files) — retry is safe and usually succeeds. It indicates a design problem when: jobs UPDATE/MERGE overlapping partitions (true write-write conflicts — redesign to partition-aligned writes), or retries storm (add backoff + jitter, or serialize via Workflow dependencies). Know the isolation levels: WriteSerializable relaxes conflict detection for append-mostly workloads (performance), Serializable is strictest.

P4. Design exactly-once ingestion from Kafka to Delta for order events. Name the three mechanisms. Answer: (1) Replayable source: Kafka with committed offsets (or Auto Loader checkpoint for files). (2) Checkpointing: Structured Streaming checkpoint dir (offsets + state durably stored). (3) Idempotent sink: Delta's transaction log (replayed micro-batches commit once — same batch ID = same version). Miss any one → at-least-once. Verify: kill the stream mid-batch, restart, count rows (should equal exactly-once).

P5. A join between a 5 TB table and a 5 GB table is slow. EXPLAIN shows SortMergeJoin with a huge shuffle. Optimize in order. Answer: (1) Broadcast the 5 GB side (raise threshold or hint — 5 GB is large for broadcast; check driver memory first). (2) If broadcast is infeasible: ensure AQE is on (runtime broadcast conversion + skew handling), pre-partition both sides by the join key (bucketing/co-partitioning eliminates the shuffle), Z-order both by the join key (file skipping). (3) Check skew (hot keys → salting). The exam's order: broadcast > AQE > co-partitioning > skew handling.

P6. An auditor asks: "Who changed the customers table last quarter, what changed, and who can read the PII columns?" Give the UC-based answer. Answer: (1) Who/what changed: DESCRIBE HISTORY customers (every version: timestamp, user, operation, affected partitions) + Delta CDF for row-level changes. (2) PII access: UC grants on the table + column masks/row filters (SHOW GRANTS; the masking policies are securable objects too). (3) Audit logs: Databricks audit log (access events). The exam's point: Delta history + UC grants + audit logs = the complete answer, produced as a side effect of using the platform correctly.

Original ETS reference writing, verified against public sources on 2026-09-30. Thin sourcing is flagged inline. Not legal advice — confirm current editions with the issuing body.

Databricks Certified Data Engineer Professional Study Guide