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.
Gold objects: tables, views, materialized views and streaming tables
One objective asks you to tell apart, and build, the four kinds of Gold object you hand to BI and analytics teams in Unity Catalog. The guide puts them in this section; you build the last two with Lakeflow Spark Declarative Pipelines (formerly Delta Live Tables) or Databricks SQL.
| Object | What it stores | Choose it when |
|---|---|---|
| Table | Data you write yourself, for example with MERGE or an overwrite | You control the load logic in a notebook or job |
| View | Nothing; the query runs each time it is read | The logic is cheap, or results must reflect the source right now |
| Materialized view | The query result, kept current by refreshes that are incremental where possible | Aggregations and joins over sources that change, read often by many users |
| Streaming table | Rows from an append-only source, each processed once | Incremental ingestion and appends, such as a Bronze or Silver feed |
The decision rule from the Databricks documentation: append-only source and new rows only, streaming table; aggregations, joins or sources whose rows are updated or deleted, materialized view. Both are Unity Catalog managed tables, so any query, dashboard or job can read them, and both are defined with CREATE OR REFRESH:
CREATE OR REFRESH MATERIALIZED VIEW gold.daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM silver.orders GROUP BY order_date;
For a Gold layer this usually means materialized views for the aggregated tables that BI tools read, and streaming tables further upstream. A plain view is fine for a thin rename or filter on top, but every read pays for the full query.
Pitfall: a per-day revenue total built as a streaming table looks efficient, but a streaming table reads each source row once. If a late correction changes an earlier day, the stored total is not recomputed. The answer for an aggregate that must stay correct is a materialized view. The Lakeflow Declarative Pipelines guide covers the dataset types, expectations and refresh modes in full; packaging the pipeline for deployment is part of CI/CD.
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.
Combining DataFrames: joins and unions
One objective in this section names the ways to combine DataFrames: inner, left, broadcast and cross joins, joins on multiple keys, and UNION versus UNION ALL. Expect a short PySpark or SQL snippet and a question about how many rows or which columns come out.
| Operation | Result | Watch for |
|---|---|---|
| Inner join | Only rows with a match on both sides | Unmatched rows vanish silently |
| Left join | Every left row; right columns are null where nothing matched | A WHERE filter on a right-side column drops the null rows, effectively an inner join |
| Broadcast join | Same result as the join it replaces; the small side is copied to every executor so the large side is not shuffled | Only for a genuinely small side |
| Cross join | Every left row paired with every right row | 1,000 × 1,000 rows is a million |
| Multiple keys | Match on all listed columns | Join on a column list to keep one copy of each key |
Multiple keys. orders.join(customers, on=["country", "customer_id"], how="left") matches on both columns and keeps a single country and customer_id in the output. The same join written as an expression, such as orders.country == customers.country, keeps both copies, and a later select("country") fails as ambiguous.
Broadcast. Spark broadcasts automatically when it estimates one side below spark.sql.autoBroadcastJoinThreshold, one of the tuning parameters this section also lists. broadcast(small_df) in PySpark, or a BROADCAST hint in SQL, forces it when the estimate is wrong.
Unions. Both combine rows from inputs with matching columns. In SQL, UNION removes duplicates and UNION ALL keeps them, so UNION ALL is cheaper and right when duplicates are impossible or wanted. In PySpark, union() keeps duplicates, behaving like UNION ALL; add .distinct() for UNION semantics.
The pitfall: union() matches columns by position, not by name. Two DataFrames with the same columns in a different order union without an error and put values in the wrong columns. unionByName() matches by name, and with allowMissingColumns=True fills columns missing on one side with nulls.
The outline does not name window functions in this section, so expect them less here than on the Professional. Ranking, running totals and the latest row per group are explained with worked results in SQL joins and window functions, together with the row-multiplication bug.
Question 1. An engineer combines df_a (id, name, city) and df_b (id, city, name) with df_a.union(df_b). The job succeeds, but in some rows the name column holds city values. What explains this and what is the fix?
- A. union() removed duplicates; use UNION ALL instead
- B. The inputs need a cross join rather than a union
- C. union() matches columns by position; use unionByName()
- D. The smaller DataFrame must be broadcast first
Show answer
Answer: C
union() resolves columns by position, so df_b’s city lands in df_a’s name column. unionByName() matches on column names. union() does not deduplicate, a cross join pairs every row with every other, and a broadcast hint only changes how a join is executed.
Want more questions like this? Full Data Engineer Associate practice tests →
Sample questions
Question 2. 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.
Want more questions like this? Full Data Engineer Associate practice tests →
Question 3. 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.
Want more questions like this? Full Data Engineer Associate practice tests →
Question 4. 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.
Want more questions like this? Full Data Engineer Associate practice tests →
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.