Databricks Data Engineer: transformation and modelling
Data Transformation and Modeling is worth 22% of the Databricks Data Engineer Associate exam — around 10 of the 45 scored questions and the largest single section. It covers turning raw ingested data into something usable, and doing it in a way that survives being rerun.
Delta Lake operations
The mechanics you are expected to know:
| Operation | What it does |
|---|---|
| MERGE | Insert, update or delete in one statement based on a match condition — the upsert |
| UPDATE / DELETE | Modify rows in place, which plain file formats cannot do |
| Time travel | Read a previous version of a table |
| OPTIMIZE | Compact small files into larger ones |
| VACUUM | Remove files no longer referenced, subject to a retention window |
MERGE is the one to know cold. It is how you apply changes without duplicating, and it is the standard answer to “records are being double-counted on rerun”.
Idempotency
The most important concept in the section, and a recurring exam scenario.
A transformation is idempotent if running it twice produces the same result as running it once. Pipelines fail and get rerun, so non-idempotent transformations quietly corrupt data.
- Append is not idempotent. Rerun it and rows duplicate.
- MERGE on a unique key is idempotent. Rerun it and matching rows update rather than duplicate.
- Overwrite of a partition is idempotent for that partition.
Any scenario mentioning duplicates after a retry or a rerun is asking about this.
The medallion architecture
The layering convention the exam assumes:
- Bronze — raw ingested data, minimally transformed, retained so downstream layers can be rebuilt.
- Silver — cleansed, deduplicated, conformed. Joined and validated. The layer most engineering effort goes into.
- Gold — aggregated and shaped for consumption: business metrics, reporting tables, features.
Questions test whether you know what belongs where. Business aggregates in bronze, or raw unparsed data in gold, are both wrong.
Handling messy data
- Duplicates — deduplicating on a business key, and deciding which record wins.
- Nulls — dropping, defaulting or flagging, and knowing each is a decision with consequences.
- Late-arriving data — records that turn up after the period they belong to has been processed.
- Data quality checks — validating expectations rather than assuming.
Sample questions
Question 1. A silver-layer job appends records from bronze. After a transient failure the job was rerun, and the silver table now contains duplicated rows. What should the transformation use instead?
- A. A larger cluster so the job does not fail
- B. Manual deletion of duplicates after each failure
- C. A MERGE on a unique business key instead of an append
- D. Disabling automatic retries on the job
Show answer
Answer: C
A merge keyed on a unique identifier updates existing rows rather than adding them again, making the job safe to rerun. A larger cluster does not change correctness, deleting and rebuilding manually is not a fix in the pipeline, and disabling retries leaves the pipeline fragile.
Question 2. A table contains daily revenue totals by product category, used directly by a reporting dashboard. Which medallion layer does it belong to?
- A. Gold
- B. Bronze
- C. Silver
- D. A separate staging area outside the architecture
Show answer
Answer: A
Aggregated data shaped for business consumption belongs in the gold layer. Bronze holds raw ingested data, silver holds cleansed and conformed detail, and a staging area is not one of the medallion layers.
Question 3. An engineer must recover the state of a Delta table as it existed before an incorrect update was applied two hours ago. What capability makes this possible?
- A. Schema enforcement
- B. OPTIMIZE on the table
- C. Restarting the cluster
- D. Delta Lake time travel
Show answer
Answer: D
Delta Lake time travel lets you read or restore a previous version of a table, which is what recovering pre-update state requires. Schema enforcement blocks mismatched writes, OPTIMIZE compacts files, and a cluster restart does nothing to table data.
What to practise
Build bronze, silver and gold tables from one source. Write the silver load as an append, run it twice, and count the duplicates. Then rewrite it as a MERGE and run it three times. Finally, make a bad update and recover with time travel.
Three exercises, one afternoon, and they cover the most heavily weighted section on the exam.