Overview
Section 3 is where raw data becomes useful. Its seven objectives follow the medallion flow. You read bronze tables with PySpark or SQL, handle nulls, cast types and write silver tables. You combine DataFrames with inner, left, broadcast and cross joins, joins on several keys, and union or union all. You reshape data by adding, dropping, splitting and renaming columns, filtering rows and exploding arrays, then remove duplicates and compute aggregates such as count, approximate distinct count and mean.
The section also covers basic performance settings, including shuffle partitions, default parallelism, executor and driver memory and the broadcast join threshold, with the expectation that you measure again after each change. It closes with the Gold layer, meaning views, materialized views, streaming tables and tables built for BI users, and with data quality checks that keep silver and gold data trustworthy.
How it's tested
Expect code-reading items in both PySpark and SQL, plus scenario items that ask which Gold object or quality rule fits a requirement. Code items often hinge on one function argument, such as a join type, a subset in dropDuplicates, or the difference between explode and explode_outer.
-
Predict the output
A short DataFrame snippet with a join, union or aggregation is shown and you choose the resulting row count or values.
-
Pick the Gold object
A BI or freshness requirement is described and you choose a view, materialized view, streaming table or managed table.
-
Choose the quality action
Bad records must be kept, dropped or must stop the update, and you choose the expectation or constraint that does it.
Silver orders must reject rows with a null order_id but keep loading the rest, and the team wants counts of rejected rows. A CHECK constraint would fail the entire write. An expectation with ON VIOLATION DROP ROW in a Lakeflow pipeline drops only the bad rows and records the failure counts in pipeline metrics, so it matches the requirement.
Objectives 7
Official objectives from the Databricks outline, in outline order.
3.1 Implement data cleaning by reading bronze tables with PySpark/SQL, cleaning nulls, standardizing data types, and writing to new silver tablesTurning raw bronze records into typed, cleaned silver data.
- What it means
- Bronze tables hold data close to its raw form, often with string columns, nulls and inconsistent formats. Cleaning reads bronze with spark.read.table or a SQL SELECT, fixes nulls and types, and writes the result to a silver table that downstream users can trust.
- What to understand
- Common tools are dropna and fillna (or SQL COALESCE) for nulls, cast or CAST for types, to_date and to_timestamp with a format string for dates, and trim, lower or upper to standardise text. Use try_cast in SQL when invalid values should become null rather than fail the query. Writes use saveAsTable, CREATE TABLE AS SELECT, or INSERT and MERGE into an existing table. For incremental silver tables, read bronze as a stream and write with a checkpoint, or define a streaming table in a Lakeflow pipeline.
- How it shows up
- Questions show a cleaning snippet and ask which function fixes a type or null problem.
- Common confusion
- Using fillna on a column of the wrong type; fill values only apply to columns of a matching type.
3.2 Combine DataFrames with operations such as Inner join, left join, broadcast join, multiple keys, cross join, union, and union allJoining and stacking DataFrames correctly.
- What it means
- Joins bring columns from two DataFrames together on matching keys, while unions stack rows from DataFrames with the same columns. The join type decides which unmatched rows survive, and the union type decides whether duplicate rows are removed.
- What to understand
- An inner join keeps only matching keys; a left join keeps every left row with nulls where nothing matches. Join on several keys with a list of column names or a combined condition. A cross join returns every pairing, so its row count is the product of both sizes. A broadcast join, requested with broadcast(df) or a hint, copies a small table to every executor and avoids a shuffle. In the DataFrame API, union and unionAll both keep duplicates and match columns by position; unionByName matches by name. In SQL, UNION removes duplicates while UNION ALL keeps them.
- How it shows up
- Expect row-count predictions after joins and unions, and questions on when to broadcast.
- Common confusion
- Assuming DataFrame union removes duplicates the way SQL UNION does.
3.3 Manipulate columns, rows, and table structures by adding, dropping, splitting, renaming column names, applying filters, and exploding arraysReshaping DataFrames and tables column by column and row by row.
- What it means
- Most transformations change the shape of data: adding a derived column, removing one that is not needed, splitting a combined field, renaming for clarity, filtering rows, or turning an array into one row per element.
- What to understand
- In PySpark, use withColumn or select with expressions to add columns, drop to remove them, split to turn a string into an array (then getItem to pick parts), withColumnRenamed to rename, and filter or where for rows. explode creates one row per array element and drops rows whose array is null or empty; explode_outer keeps them with a null. At table level, ALTER TABLE can add, rename or drop columns, and renaming or dropping requires column mapping on Delta tables.
- How it shows up
- Code items ask which function produces a stated output shape.
- Common confusion
- Expecting explode to keep rows with empty arrays.
3.4 Perform data deduplication operations and aggregate operations on DataFrames, such as count, approximate count distinct, and mean, summaryRemoving duplicates and computing grouped statistics.
- What it means
- Deduplication removes repeated rows, either across all columns or based on a set of key columns. Aggregation groups rows and computes statistics such as counts, averages or approximate distinct counts for each group.
- What to understand
- distinct removes rows that are identical in every column, while dropDuplicates(["id"]) keeps one row per key, with no promise about which one unless you rank rows with a window function first. groupBy with agg computes count, avg or mean, sum, min and max. approx_count_distinct trades a small error for speed on large data. summary and describe return count, mean, standard deviation, min and max, and summary adds quartiles. Note that count(*) counts rows, while count(column) skips nulls.
- How it shows up
- Questions test which dedup call matches a requirement and the difference between exact and approximate distinct counts.
- Common confusion
- Thinking dropDuplicates on a key keeps the latest record automatically.
3.5 Understand the basic tuning parameters (spark.sql.shuffle.partitions, spark.default.parallelism, spark.executor/driver.memory, spark.sql.autoBroadcastJoinThreshold) and re-measure the performanceWhat the core Spark settings control and how to test a change.
- What it means
- Spark exposes settings that control how work is split and how much memory processes get. The objective covers four of them and expects you to change one thing at a time and measure the result rather than tune by guesswork.
- What to understand
- spark.sql.shuffle.partitions sets how many partitions a shuffle produces for DataFrame and SQL work, with a default of 200, and adaptive query execution can coalesce them at run time. spark.default.parallelism sets default partitions for RDD operations. spark.executor.memory and spark.driver.memory size the JVM heap and are fixed when the cluster starts. spark.sql.autoBroadcastJoinThreshold, 10 MB by default, is the size below which a table is broadcast automatically; -1 disables it. Serverless compute manages most of these for you and allows only a few to be set.
- How it shows up
- Questions describe a symptom, such as too many tiny tasks or a driver error, and ask which setting to adjust.
- Common confusion
- Trying to change executor memory with spark.conf.set inside a running notebook session.
3.6 Understand the difference between, and how to build, Gold layer objects such as materialized views, views, streaming tables, and tables for BI and analytics teams in Unity CatalogChoosing and creating the right object for analytics consumers.
- What it means
- Gold objects present curated data to analysts and dashboards. A view stores only a query. A materialized view stores precomputed results and is refreshed. A streaming table processes new data incrementally. A regular managed table is written by your own jobs.
- What to understand
- A view is cheap to create and always current, but its query runs every time someone reads it. A materialized view, created with CREATE MATERIALIZED VIEW or in a Lakeflow pipeline, caches results and refreshes on a schedule or trigger, often incrementally on serverless, which makes it good for heavy aggregations behind dashboards. A streaming table, created with CREATE STREAMING TABLE, reads append-only sources once per record and suits incremental ingestion and simple transformations. Tables written with CTAS, INSERT or MERGE give full control. All live in Unity Catalog and are shared through grants.
- How it shows up
- Scenario items give freshness, cost and query complexity, then ask which object to build.
- Common confusion
- Using a streaming table for an aggregation that must recompute when source rows are updated.
3.7 Apply data quality checks and validation rules to ensure reliable Silver and Gold datasetsEnforcing rules so bad records do not reach curated layers.
- What it means
- Quality checks state what valid data looks like, such as non-null keys, positive amounts or dates in range, and decide what happens to records that break the rule.
- What to understand
- In Lakeflow Declarative Pipelines, expectations attach a rule to a dataset. A plain expectation keeps invalid rows but records the violation, ON VIOLATION DROP ROW removes them, and ON VIOLATION FAIL UPDATE stops the update. Python uses expect, expect_or_drop and expect_or_fail. Results appear in the pipeline event log and UI. Outside pipelines, Delta tables support NOT NULL and CHECK constraints that reject an entire write if any row fails. A quarantine pattern routes failing rows to a separate table for review.
- How it shows up
- Questions describe the required reaction to bad data and ask which expectation action or constraint achieves it.
- Common confusion
- Expecting a Delta CHECK constraint to drop only the bad rows; it rejects the whole transaction.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
union vs UNION
DataFrame union keeps duplicates; SQL UNION removes them.
- Remember
- SQL UNION ALL keeps duplicates.
- Tested as
- Row-count questions.
- Trap
- Assuming both APIs behave the same.
-
unionByName
Stacks DataFrames by column name.
- Remember
- union matches by position.
- Tested as
- Mismatched column order items.
- Trap
- Silent column swaps with union.
-
Broadcast join
Small table copied to every executor.
- Remember
- Avoids shuffling the large side.
- Tested as
- Join performance items.
- Trap
- Broadcasting a very large table.
-
explode_outer
Keeps rows whose array is null or empty.
- Remember
- explode drops them.
- Tested as
- Output-shape questions.
- Trap
- Losing parent rows with explode.
-
dropDuplicates(subset)
Keeps one row per key combination.
- Remember
- Which row survives is arbitrary.
- Tested as
- Dedup questions.
- Trap
- Assuming it keeps the latest record.
-
approx_count_distinct
Fast approximate distinct count.
- Remember
- Small, bounded error.
- Tested as
- Large data aggregation items.
- Trap
- Using it where an exact audit count is required.
-
spark.sql.shuffle.partitions
Partitions produced by shuffles; default 200.
- Remember
- AQE can coalesce at run time.
- Tested as
- Tuning symptom questions.
- Trap
- Confusing it with spark.default.parallelism.
-
autoBroadcastJoinThreshold
Auto-broadcast size limit; 10 MB default.
- Remember
- Set -1 to disable.
- Tested as
- Join strategy questions.
- Trap
- Raising it far beyond executor memory.
-
Materialized view
Stored, refreshed query results.
- Remember
- Good for heavy dashboard aggregations.
- Tested as
- Gold object choice.
- Trap
- Expecting always-live results between refreshes.
-
Expectation actions
Warn, drop row or fail update.
- Remember
- Metrics go to the pipeline event log.
- Tested as
- Data quality items.
- Trap
- Mixing up drop and fail behaviour.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Gold-layer objects
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| View | Saved query with no stored data. | Always current; recomputed on every read. | Using it for expensive aggregations hit by many users. |
| Materialized view | Precomputed results refreshed on schedule or trigger. | Fast reads, data as of last refresh. | Assuming it updates the moment the source changes. |
| Streaming table | Table fed incrementally from append-only sources. | Processes each new record once. | Using it where source rows are updated or deleted. |
| Managed table | Delta table written by your own jobs. | Full control over write logic. | Rebuilding logic a materialized view would handle. |
Quality enforcement options
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Expectation (warn) | Rule that logs violations and keeps rows. | Visibility without blocking. | Expecting bad rows to be removed. |
| Expect or drop | Rule that removes failing rows. | Load continues with valid rows. | Losing rows without reviewing counts. |
| Expect or fail / CHECK constraint | Rule that stops the update or write. | All-or-nothing protection. | Using it when partial loads are acceptable. |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
SQL semantics for DataFrame union
- What candidates pick
- Expect duplicates removed after df1.union(df2)
- Why it looks right
- SQL UNION removes duplicates.
- Why it's wrong
- The DataFrame union method behaves like UNION ALL and keeps every row; call distinct afterwards if needed.
Clue to watch for PySpark union in the snippet.
-
Cross join row count
- What candidates pick
- Rows equal the larger table's count
- Why it looks right
- Joins usually return about as many rows as the inputs.
- Why it's wrong
- A cross join pairs every row with every row, so the result is the product of both counts.
Clue to watch for crossJoin or CROSS JOIN keyword.
-
Runtime memory change
- What candidates pick
- spark.conf.set for executor memory
- Why it looks right
- Other settings change this way.
- Why it's wrong
- Executor and driver memory are fixed when the cluster starts and must be set in the compute configuration.
Clue to watch for Memory settings in a notebook.
-
Constraint drops rows
- What candidates pick
- A CHECK constraint to remove invalid rows
- Why it looks right
- Constraints are a familiar way to enforce rules.
- Why it's wrong
- A CHECK constraint rejects the whole write if any row fails; dropping rows needs an expectation or filter.
Clue to watch for Keep loading valid rows.
-
Streaming table for changing aggregates
- What candidates pick
- A streaming table for totals that must reflect updates
- Why it looks right
- Streaming tables are incremental and efficient.
- Why it's wrong
- Streaming tables expect append-only input; aggregates that must reflect changed rows suit a materialized view.
Clue to watch for Source rows can be updated or deleted.
Answer framework
Apply these steps to transformation and Gold-layer questions in Section 3.
- Read the code or requirement and name the operation: cleaning, join, union, reshape, dedup, aggregation, tuning or quality rule.
- For joins and unions, confirm which rows survive and whether duplicates are kept, then estimate the row count before reading the options.
- For reshaping, check null and empty handling, especially explode versus explode_outer and count of a column versus count of rows.
- For Gold objects, match freshness, read cost and whether sources are append-only to a view, materialized view, streaming table or table.
- For quality and tuning, choose the action that matches the stated reaction to bad data or the symptom, and plan to measure again.
Practice question
Want more like this? The Databricks DE Assoc practice test has questions for every domain.
Checklist
0 of 12
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Plan twelve to sixteen hours here, the largest single block of study. Work in a notebook with two small DataFrames you build by hand so you can predict results before running code. Try every join type in the guide, union versus unionByName, and SQL UNION versus UNION ALL. Write down the row count you expect, run the cell, and investigate any surprise. Do the same for explode, explode_outer, dropDuplicates and the aggregate functions.
Next, build a tiny medallion flow: a bronze table, a silver table created by a cleaning query, and a Gold materialized view. Then rebuild the silver step in a Lakeflow Declarative Pipeline with one expectation of each type, insert some bad rows and read the data quality metrics.
For tuning, run a join with the broadcast threshold enabled and disabled, and compare the plans in the Spark UI. Change shuffle partitions on a larger aggregation and record the duration each time. Finish with timed code-reading questions, since speed at reading PySpark and SQL is the skill this section rewards most.
FAQ
What share of the exam is Data Transformation and Modeling?
Data Transformation and Modeling is Section 3 and carries 22% of the exam, the largest share, which is roughly 10 of the 45 scored questions across seven official objectives.
Is the code on the exam in Python or SQL?
Both. The objectives say PySpark/SQL, so be ready to read DataFrame code and Spark SQL statements, and to translate between them for common operations.
Do I need to know adaptive query execution?
Know that it is on by default and can coalesce shuffle partitions and handle skewed joins at run time. Deep internals are beyond this objective.
Are window functions tested?
They are not named in the guide, but they are the standard way to keep the latest record per key when deduplicating, so understanding row_number over a partition is useful.
What is the difference between a materialized view and a streaming table?
A materialized view stores query results and recomputes them on refresh, incrementally where possible. A streaming table processes each new source record once and suits append-only data.
Can I set tuning parameters on serverless compute?
Only a small set of Spark properties can be changed on serverless, because Databricks manages resources. Memory settings apply to classic compute configured at cluster creation.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 3 knowledge
Test your speed at reading PySpark and SQL transformations and picking the right Gold object under exam timing.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the Databricks DE Assoc AI tutor Get any concept explained your way