Data Engineer Associate · 22% of the exam

Data Transformation and Modeling: free practice questions

5 sample questions from our 83-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Databricks exam.

1. A data engineer writes the following PySpark code to join two DataFrames: ```python result = orders.join(customers, on='customer_id', how='left') ``` Which statement best describes the result?

  • A. All rows from `customers` are returned; rows from `orders` without a matching `customer_id` are filled with NULLs.
  • B. Only rows where `customer_id` exists in both `orders` and `customers` are returned.
  • C. All rows from `orders` are returned; rows from `orders` without a matching `customer_id` in `customers` have NULL values for `customers` columns.✓ Correct
  • D. All rows from both DataFrames are returned, with NULLs where there is no match on either side.
Explanation

Option C is correct: a LEFT join returns every row from the left DataFrame (`orders`), and for rows where `customer_id` has no match in `customers`, the columns sourced from `customers` are filled with NULLs. Option A describes a RIGHT join (all rows from the right table, `customers`). Option B describes an INNER join (only matching rows). Option D describes a FULL OUTER join (all rows from both sides with NULLs where there is no match).

2. A data engineer executes the following code in a Databricks notebook: ```python df = spark.read.format("csv")\ .option("header", "true")\ .option("inferSchema", "true")\ .load("/mnt/landing/customers/") df.createOrReplaceTempView("vw_customers") ``` Later, a colleague opens a **new notebook** attached to the **same cluster** and runs: ```sql SELECT * FROM vw_customers LIMIT 10; ``` What is the result?

  • A. The query succeeds because temp views are shared across all notebooks on the same cluster
  • B. The query fails because `vw_customers` is a local temp view scoped to the SparkSession of the original notebook✓ Correct
  • C. The query succeeds but returns zero rows because the view definition is shared without the underlying data
  • D. The query fails because CSV files cannot be used as the basis for a temp view
Explanation

Option B is correct. In Databricks, `createOrReplaceTempView` creates a session-scoped temporary view tied to the SparkSession of the notebook that created it. Each notebook on a cluster has its own SparkSession (unless session sharing is explicitly configured), so the view is not visible in the colleague's notebook. Option A is wrong — temp views are NOT shared across notebooks/sessions; only global temp views (created with `createOrReplaceGlobalTempView`) are shared at the cluster level and must be accessed via the `global_temp` database. Option C is incorrect because temp view visibility is a scoping issue, not a data issue. Option D is wrong — CSV files are perfectly valid sources for creating temp views.

3. A data engineer is analyzing an `orders` table and an `order_items` table. They want a result that includes ALL orders, even those with no matching items, along with item details where available. Which SQL join type should they use?

  • A. INNER JOIN
  • B. LEFT JOIN (orders LEFT JOIN order_items)✓ Correct
  • C. RIGHT JOIN (orders RIGHT JOIN order_items)
  • D. CROSS JOIN
Explanation

**Correct: B.** A `LEFT JOIN` (with `orders` as the left/driving table) returns all rows from `orders` and the matching rows from `order_items`. Where there is no match, NULL values are returned for the `order_items` columns — this correctly represents orders with no items. **A is wrong** because `INNER JOIN` only returns rows where a match exists in both tables; orders with no items would be excluded. **C is wrong** because `RIGHT JOIN (orders RIGHT JOIN order_items)` would return all rows from `order_items` and matching orders; orders with no items could still be lost. **D is wrong** because `CROSS JOIN` produces a Cartesian product of every row in both tables, which is not the desired behavior.

4. A data engineer has the following PySpark code: ```python df = spark.read.format("json").load("/mnt/events/") df2 = df.withColumn("event_ts", col("event_ts").cast("timestamp")) df3 = df2.withColumn("amount", col("amount").cast("double")) df3.write.format("delta").mode("overwrite").saveAsTable("events_silver") ``` Which Spark SQL statement is the closest equivalent to the combined transformation and write in lines 2-4?

  • A. `CREATE OR REPLACE TABLE events_silver AS SELECT CAST(event_ts AS TIMESTAMP), CAST(amount AS DOUBLE), * FROM json.\`/mnt/events/\``
  • B. `CREATE TABLE events_silver AS SELECT CAST(event_ts AS TIMESTAMP) AS event_ts, CAST(amount AS DOUBLE) AS amount, * FROM json.\`/mnt/events/\``
  • C. `CREATE OR REPLACE TABLE events_silver AS SELECT * EXCEPT(event_ts, amount), CAST(event_ts AS TIMESTAMP) AS event_ts, CAST(amount AS DOUBLE) AS amount FROM json.\`/mnt/events/\``✓ Correct
  • D. `INSERT OVERWRITE events_silver SELECT CAST(event_ts AS TIMESTAMP) AS event_ts, CAST(amount AS DOUBLE) AS amount FROM json.\`/mnt/events/\``
Explanation

**Correct: C.** The PySpark code overwrites all columns but replaces `event_ts` and `amount` with cast versions while keeping all other columns unchanged. Option C correctly uses `* EXCEPT(event_ts, amount)` to include all other columns and then explicitly adds the two cast columns. **A is wrong** because selecting `CAST(event_ts AS TIMESTAMP)` alongside `*` would include the original `event_ts` column TWICE (under the original name and as an unnamed cast), causing ambiguity or an error. **B is wrong** for the same reason — selecting `*` after the casts includes duplicate columns for `event_ts` and `amount`. **D is wrong** because `INSERT OVERWRITE` requires the table to already exist, and it also omits all other columns from the source.

5. A data engineer has a Delta table `raw_logs` with a column `event_payload` of type STRING containing JSON. A sample value looks like: `'{"user_id": "u123", "action": "click", "metadata": {"page": "home"}}'`. The engineer needs to extract the `user_id` and the nested `page` value as separate columns using Spark SQL. Which query correctly extracts both fields?

  • A. ```sql SELECT get_json_object(event_payload, '$.user_id') AS user_id, get_json_object(event_payload, '$.metadata.page') AS page FROM raw_logs ```✓ Correct
  • B. ```sql SELECT event_payload:user_id AS user_id, event_payload:metadata:page AS page FROM raw_logs ```✓ Correct
  • C. ```sql SELECT from_json(event_payload).user_id AS user_id, from_json(event_payload).metadata.page AS page FROM raw_logs ```
  • D. ```sql SELECT json_extract(event_payload, 'user_id') AS user_id, json_extract(event_payload, 'metadata.page') AS page FROM raw_logs ```
Explanation

Options A and B are both correct. Option A uses `GET_JSON_OBJECT()` with JSONPath syntax (`$.field` and `$.nested.field`), which is fully supported in Spark SQL for extracting values from JSON strings. Option B uses the Databricks-specific colon (`:`) notation for navigating JSON string columns, including nested paths like `event_payload:metadata:page`, which is also valid in Databricks SQL. Option C is wrong because `FROM_JSON()` requires a schema argument — you cannot call it as `from_json(col)` without specifying a schema, and the dot notation would not work directly on the STRING return without schema parsing. Option D is wrong because `JSON_EXTRACT()` is a MySQL/SQLite function syntax; Spark SQL does not support it in that form.

78 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free