Data Engineer Associate · 29% of the exam

ELT with Spark SQL and Python: free practice questions

5 sample questions from our 73-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 has the following PySpark code: ```python from pyspark.sql.functions import col, when df_clean = df.withColumn( "priority", when(col("severity") == "critical", 1) .when(col("severity") == "high", 2) .when(col("severity") == "medium", 3) .otherwise(4) ) ``` Which Spark SQL expression is the closest equivalent to this PySpark transformation?

A. ```sql SELECT *, IIF(severity = 'critical', 1, IIF(severity = 'high', 2, IIF(severity = 'medium', 3, 4))) AS priority FROM df ```
B. ```sql SELECT *, CASE severity WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END AS priority FROM df ```✓ Correct
C. ```sql SELECT *, IF(severity IN ('critical','high','medium'), severity, 4) AS priority FROM df ```
D. ```sql SELECT *, COALESCE(severity = 'critical', 1, severity = 'high', 2, 4) AS priority FROM df ```

Explanation

The PySpark `when().when().otherwise()` chain is a multi-branch conditional equivalent to a SQL `CASE` expression. Option B correctly uses a simple CASE expression that maps each severity string to the corresponding integer, with ELSE 4 for all other values — exactly matching the PySpark logic. Option A uses `IIF()`, which is not a standard Spark SQL function (it exists in some other SQL dialects but not Spark). Option C uses `IF()` with an `IN` check, which returns the string `severity` value (not an integer) and doesn't differentiate between critical/high/medium. Option D misuses `COALESCE`, which returns the first non-null argument from a list of values, not a conditional mapping.

2. A data engineer has the following PySpark DataFrame `employees` with columns `emp_id`, `name`, `dept_id`, and a DataFrame `departments` with columns `dept_id` and `dept_name`. They need ALL employees, including those who do not belong to any department, along with department names where available. Which PySpark join is correct?

A. `employees.join(departments, "dept_id", "inner")`
B. `employees.join(departments, "dept_id", "left")`✓ Correct
C. `employees.join(departments, "dept_id", "right")`
D. `employees.join(departments, "dept_id", "full")`

Explanation

Option B is correct. A LEFT (OUTER) join returns all rows from the left DataFrame (`employees`) including those with no matching `dept_id` in `departments`, with `null` for `dept_name` where there is no match. This satisfies the requirement to include all employees. Option A (inner join) only returns employees who have a matching department, dropping employees with no department. Option C (right join) returns all rows from `departments` plus matching employees — this could exclude employees who don't belong to any department if they have no matching dept. Option D (full outer join) returns all rows from both sides, which would also include departments with no employees — more than what is needed and not what was asked.

3. A data engineer needs to read all JSON files from `/mnt/landing/events/` into a PySpark DataFrame without specifying a schema. Which of the following code snippets correctly reads the files and infers the schema automatically? ```python # Option A df = spark.read.format("json").load("/mnt/landing/events/") # Option B df = spark.read.json("/mnt/landing/events/", inferSchema=False) # Option C df = spark.read.format("json").option("header", "true").load("/mnt/landing/events/") # Option D df = spark.read.format("json").schema("id LONG, ts STRING").load("/mnt/landing/events/") ```

A. Option A — `spark.read.format("json").load("/mnt/landing/events/")`✓ Correct
B. Option B — `spark.read.json("/mnt/landing/events/", inferSchema=False)`
C. Option C — `spark.read.format("json").option("header", "true").load("/mnt/landing/events/")`
D. Option D — `spark.read.format("json").schema("id LONG, ts STRING").load("/mnt/landing/events/")`

Explanation

Option A is correct: `spark.read.format("json").load(path)` automatically infers the schema from the JSON files by default (inferSchema is true for JSON). Option B is wrong because passing `inferSchema=False` to the shorthand `spark.read.json()` is not a valid keyword argument for that method — and even conceptually it disables inference. Option C is wrong because the `header` option applies to CSV, not JSON; JSON files do not have a header row, so this option is irrelevant and would be ignored, but it's still misleading practice. Option D is wrong because supplying an explicit `.schema(...)` overrides automatic inference, meaning Spark uses the provided schema instead of inferring it.

4. A data engineer has a Delta table `transactions` with a column `txn_amount` stored as `STRING` that should be `DOUBLE`, and a column `txn_date` stored as `STRING` that should be `DATE`. They write the following PySpark code: ```python from pyspark.sql.functions import col, to_date cleaned = transactions_df \ .withColumn("txn_amount", col("txn_amount").cast("double")) \ .withColumn("txn_date", to_date(col("txn_date"), "yyyy-MM-dd")) ``` What happens to rows where `txn_amount` contains a non-numeric string such as `'N/A'`?

A. The job throws an AnalysisException and stops immediately when it encounters `'N/A'`
B. The row is silently dropped from the resulting DataFrame
C. The `txn_amount` value for that row becomes `null` in the resulting DataFrame✓ Correct
D. The `txn_amount` value remains as the string `'N/A'` because the cast fails gracefully

Explanation

Option C is correct. In Spark, when a `cast()` operation cannot convert a value to the target type (e.g., casting `'N/A'` to `double`), it returns `null` for that value rather than throwing an exception. This is Spark's default 'permissive' behavior for type casting. Option A is wrong — Spark does not throw an AnalysisException at runtime for bad cast values; that exception is for schema/plan issues detected at the logical plan phase. Option B is wrong — the row is not dropped; only the value in that column becomes null. Option D is wrong — because the column type is being changed to `double`, the resulting column is of type `double` and cannot hold the string `'N/A'`; the result is `null`, not the original string.

5. A data engineer creates the following SQL UDF: ```sql CREATE OR REPLACE FUNCTION calculate_tax(price DOUBLE, rate DOUBLE) RETURNS DOUBLE RETURN price * rate; ``` They then run: ```sql SELECT product_id, calculate_tax(unit_price, 0.08) AS tax FROM products; ``` Which of the following statements is TRUE about this SQL UDF? Select TWO.

A. The UDF is registered in the metastore and is accessible to other users and sessions with appropriate permissions.✓ Correct
B. The UDF can only be used within the notebook session in which it was created.
C. SQL UDFs in Databricks are optimized by the Catalyst query optimizer and can be inlined.✓ Correct
D. SQL UDFs cannot accept literal values like `0.08` as arguments; only column references are allowed.
E. The UDF will raise an error if `unit_price` contains NULL values because NULL * 0.08 is undefined.

Explanation

Options A and C are correct. A: SQL UDFs created without the TEMPORARY keyword are persisted in the metastore (Unity Catalog or Hive metastore) and are accessible to other users with the appropriate privileges — unlike temp views or temp functions. C: Unlike Python UDFs, SQL UDFs are understood natively by the Catalyst optimizer and can be inlined into the query plan, enabling optimization. Option B is incorrect — that describes a TEMPORARY function, not a standard SQL UDF. Option D is incorrect — SQL UDF arguments can accept literal values, column references, or expressions freely. Option E is incorrect — in SQL, any arithmetic involving NULL returns NULL without raising an error; NULL * 0.08 = NULL, which is valid behavior.

68 more questions in this domain

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

Start practicing free