Databricks Data Engineer Associate Data Transformation and Modeling: 180 practice questions

12 of the 180 Data Transformation and Modeling questions in the Certsqill Databricks Data Engineer Associate bank, shown in full below. Each one carries an explanation for every option, not just the correct one — the wrong answers are where the marks go.

Preparing for Databricks Data Engineer Associate? Take the free 5-min readiness check →

1. 2: readings (integer columns, nullable): amount 2 NULL 2 7 NULL SQL: SELECT COUNT(*) FROM readings WHERE amoun

1. 5

   Five is the total row count, but the WHERE predicate retains only rows with NULL amounts.
2. 1

   The fixture contains two NULL amounts, and COUNT(*) counts every row satisfying the predicate.
3. 2 ✓

   `IS NULL` matches both NULL rows, and COUNT(*) counts each qualifying row.
4. 0

   The predicate explicitly selects rows whose amount is SQL NULL, so the two NULL rows are not excluded.

The query returns 2 because `IS NULL` matches both NULL amount rows.

2. Increase `spark.sql.shuffle.partitions`: What is the most appropriate first remediation?

1. Collect the joined DataFrame to the driver before performing the aggregation.

   Collecting a large join result can exhaust driver memory rather than improve executor parallelism.
2. Increase `spark.sql.shuffle.partitions`; compare representative runs and AQE. ✓

   Increasing the initial SQL shuffle parallelism addresses the stated low count, while comparison shows whether AQE and the change improve execution.
3. Set `spark.default.parallelism` very high while leaving SQL shuffle settings unchanged.

   This setting primarily affects RDD defaults and does not directly correct SQL shuffle partitioning.
4. Reduce the SQL shuffle partition count further.

   Fewer shuffle partitions increase work per partition and do not address the stated oversized tasks and spill caused by an already low count.

Increase SQL shuffle partitions, then validate representative runs and AQE behavior.

3. `items.withColumn > 0)`: Which PySpark expression produces the required result?

1. `items.withColumn("in_stock", F.col("quantity") > 0)` ✓

   withColumn returns a new DataFrame containing the original columns plus the Boolean expression as `in_stock`.
2. `items.drop("quantity").withColumn("in_stock", F.col("quantity") > 0)`

   Dropping quantity first removes the input required by the subsequent expression, so the requested result cannot be derived.
3. `items.select("sku", F.col("quantity") > 0)`

   The expression lacks the required `in_stock` alias and does not preserve the original quantity column.
4. `items.withColumnRenamed("quantity", "in_stock")`

   Renaming changes the existing column name and loses the requested quantity column instead of deriving a Boolean result.

Use `withColumn` to derive `in_stock` while retaining the existing inventory columns.

4. Attach the Boolean rule as a dataset expectation: What prerequisite is missing?

1. Use schema validation to generate metrics for the business rule.

   Schema validation does not define a business predicate or automatically create expectation metrics for it.
2. Configure `ON VIOLATION DROP ROW` so metrics cover the retained records.

   `DROP ROW` discards violating records, contradicting the requirement to retain them for inspection.
3. Attach the Boolean rule as a dataset expectation. ✓

   The expectation must be attached to the dataset so the pipeline evaluates violations, records metrics, and retains violating rows under the default behavior.
4. Configure `ON VIOLATION FAIL UPDATE` to retain every invalid row.

   `FAIL UPDATE` fails the affected flow or update rather than providing the requested retained-row monitoring behavior.

Attach the Boolean rule as a dataset expectation.

5. A materialized view that stores computed results: Which object best matches this requirement?

1. A streaming table, even though the aggregate has no streaming source or incremental processing requirement.

   Streaming tables are intended for incremental pipeline processing, not merely storing a refreshable batch aggregate.
2. A standard view whose query definition is reused for every supplier dashboard request.

   A standard view stores the query definition, so querying it uses underlying data rather than stored computed results.
3. A Gold-named Delta table that automatically refreshes whenever source supplier data changes.

   A Delta table stores data, but its results change only when an explicit writer or job updates it.
4. A materialized view that stores computed results and refreshes through supported mechanisms. ✓

   Materialized views store computed results for reuse and refresh them according to supported refresh behavior.

Choose a materialized view when stored, reusable aggregate results may refresh rather than remain continuously current.

6. 3: left_rows (integer columns, nullable): k 1 2 3 right_rows (integer columns, nullable): k 1 1 2 SQL: SELECT

1. 3 ✓

   The duplicate right key 1 creates two pairs, and key 2 creates one pair; key 3 has none.
2. 2

   Key 1 produces two matching pairs and key 2 produces one, so the result exceeds two rows.
3. 5

   An inner join retains matching pairs only; unmatched key 3 and any standalone rows are excluded.
4. 1

   The two right-side rows with key 1 each match the left key 1, so the join produces more than one row.

The join returns 3 rows because duplicate matching keys create multiple matching pairs.

7. 5: readings (integer columns, nullable): amount 2 NULL 2 7 NULL SQL: SELECT COUNT(*) FROM readings What single

1. 2

   Two is the number of NULL amounts, but COUNT(*) counts all rows regardless of column nullability.
2. 3

   Three is the number of non-NULL amounts, which would relate to COUNT(amount), not COUNT(*).
3. 4

   The fixture contains five rows; neither duplicate values nor NULL values reduce COUNT(*) row counting.
4. 5 ✓

   COUNT(*) counts all five rows, including both rows where amount is NULL.

COUNT(*) returns 5 because it counts every fixture row, including rows with NULL amounts.

8. 4: readings (integer columns, nullable): amount 3 NULL 3 8 NULL 4 SQL: SELECT COUNT(*) FROM readings WHERE amo

1. 3

   Three counts distinct non-NULL values, but COUNT(*) counts rows and preserves both occurrences of 3.
2. 6

   Six is the total row count before filtering, including the two rows containing NULL.
3. 2

   Two is the number of NULL rows, but `IS NOT NULL` retains the four non-NULL rows.
4. 4 ✓

   Four rows contain non-NULL amounts, and `IS NOT NULL` retains each of them.

The query returns 4 because four rows contain non-NULL amounts.

9. Increase `spark.default.parallelism` and decrease: Which configuration change addresses both requirements prec

1. Increase `spark.default.parallelism` and decrease `spark.sql.shuffle.partitions`. ✓

   These settings independently address default RDD parallelism and initial SQL shuffle partitioning. AQE may later adjust SQL execution.
2. Lower only `spark.default.parallelism`; the SQL exchange will follow it.

   The setting primarily affects default RDD parallelism and does not directly control initial SQL shuffle partitioning.
3. Repartition the DataFrame and RDD together with one shared partition count.

   A shared explicit partition count does not independently configure the RDD default and SQL shuffle setting as required.
4. Increase `spark.sql.shuffle.partitions` and leave the RDD default unchanged.

   This does not increase the RDD's default parallelism and also conflicts with the requirement for fewer SQL shuffle partitions.

Tune RDD defaults and SQL shuffle partitions independently.

10. Cast `qty` to an integer in `quantity`: Which change addresses the failure while retaining the quantity column

1. Drop `qty` and recreate `quantity` with the original string schema.

   Dropping the source removes data, and recreating the column with a string schema does not fix the numeric calculation.
2. Alias `qty` as `quantity` because a SQL alias performs an implicit numeric cast.

   An alias changes the projected name but preserves the original string data type unless an explicit cast is applied.
3. Rename `qty` to `quantity` again and rely on Spark to infer a numeric type.

   Renaming changes only the column label; it does not convert string values to a numeric type.
4. Cast `qty` to an integer in `quantity`, then check malformed results. ✓

   `withColumn` can create the numeric `quantity` column from `qty`; conversion results should be validated for malformed values.

Explicit casting changes the value type; renaming changes only the column name.

11. Declare a drop-row expectation for non-null: Select TWO implementations that satisfy the requirement.

Select two. More than one option is correct — every correct one is ticked below.

1. Use `fillna(0, ["amount"])` to convert missing amounts to valid values.

   Filling nulls changes missing data into zero and does not remove negative amounts.
2. Use `dropDuplicates(["invoice_id"])` to remove invalid amount rows.

   Deduplication addresses repeated keys, not null or negative amounts, and may retain invalid records.
3. Declare a drop-row expectation for non-null, nonnegative amounts. ✓

   A pipeline expectation with `ON VIOLATION DROP ROW` removes records violating the Boolean validity rule during processing.
4. Filter with `F.col("amount").isNotNull()` and `F.col("amount") >= 0`. ✓

   The filter retains rows satisfying both validity conditions and preserves the DataFrame columns.
5. Use `drop("amount")` to prevent invalid amounts downstream.

   Dropping the column removes the required measure instead of removing invalid records.

A row filter and a drop-row expectation both remove records using the explicit validity predicate.

12. Run it in a supported Lakeflow Spark Declarative Pipelines: Which missing prerequisite should the engineer add

1. Run it in a supported Lakeflow Spark Declarative Pipelines context. ✓

   Materialized-view creation and refresh require a supported pipeline context rather than an arbitrary ordinary SQL session.
2. Add ORDER BY to the definition so the computed results can be persisted during refresh.

   Ordering does not provide the execution context required to create or refresh a materialized view.
3. Rename the materialized view as a standard view because standard views refresh automatically.

   A standard view stores a query definition and does not provide materialized results or automatic refresh behavior.
4. Convert every source table to a streaming table before creating any materialized view.

   Streaming tables and materialized views serve different purposes; converting every source is not required.

Create and refresh the materialized view through a supported pipeline context.

Other Databricks Data Engineer Associate domains

• Data Ingestion and Loading — 172 questions →
• Working with Lakeflow Jobs — 129 questions →
• Governance and Security — 123 questions →
• Implementing CI/CD — 82 questions →
• Troubleshooting, Monitoring, and Optimization — 82 questions →
• All 817 Databricks Data Engineer Associate questions →
• Databricks Data Engineer Associate certification: requirements, cost and exam format →