Databricks Data Engineer Associate · 22% practice allocation
5 free sample questions from the Data Transformation and Modeling domain, with answers and explanations. The full Data Engineer Associate bank has 300 questions across 7 domains. Questions are scenario-based and written from the official Databricks exam guide.
Which function combines rows from two tables based on a matching key column?
A JOIN combines rows using matching key values. UNION stacks compatible result sets; OPTIMIZE rewrites files for performance, and VACUUM removes eligible unreferenced files. These maintenance operations do not join tables.
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:
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'?
'N/A'txn_amount value for that row becomes null in the resulting DataFrame✓ Correcttxn_amount value remains as the string 'N/A' because the cast fails gracefullyOption 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.
A data engineer wants to create a SQL UDF that categorizes an integer score into 'Low', 'Medium', or 'High' and then use it in a query. They write:
CREATE OR REPLACE FUNCTION categorize_score(score INT)
RETURNS STRING
LANGUAGE SQL
RETURN CASE
WHEN score < 40 THEN 'Low'
WHEN score BETWEEN 40 AND 79 THEN 'Medium'
ELSE 'High'
END;
SELECT customer_id, categorize_score(test_score) AS category
FROM customer_scores;
Which of the following statements about this SQL UDF are true? Select TWO.
Options B and E are correct. SQL UDFs created with CREATE OR REPLACE FUNCTION are stored in the Databricks metastore (Unity Catalog or Hive metastore) and persist across sessions, notebooks, and cluster restarts — they are reusable objects. They can also be used anywhere a scalar expression is valid in SQL, including SELECT, WHERE, HAVING, and ORDER BY clauses. Option D is wrong — unlike temp views or Python-defined UDFs registered with spark.udf.register, SQL UDFs are metastore-persisted. Option C is wrong — CASE expressions are fully supported inside SQL UDFs; branching logic is not restricted to Python UDFs. Option A is wrong — because the UDF is stored in the metastore, it survives cluster restarts.
A data engineer needs to implement a Python UDF in a Databricks SQL context. They write the following:
from pyspark.sql.functions import udf
from pyspark.sql.types import StringType
@udf(returnType=StringType())
def categorize_spend(amount):
if amount is None:
return 'unknown'
elif amount < 100:
return 'low'
elif amount < 1000:
return 'medium'
else:
return 'high'
df = spark.table("transactions")
result = df.withColumn("spend_category", categorize_spend(df["amount"]))
Which TWO statements are true about this Python UDF compared to an equivalent SQL UDF or native Spark function?
Correct: A and D. A is correct because Python UDFs require row-by-row serialization between the JVM (Spark's execution engine) and the Python interpreter, which adds significant overhead compared to native Spark SQL functions or even SQL UDFs that run inside the JVM. D is correct because Catalyst, Spark's query optimizer, treats a Python UDF as a black box — it cannot look inside the function body to apply optimizations like predicate pushdown or constant folding, unlike native Spark expressions. B is wrong because a Python UDF registered with @udf (or spark.udf.register) in PySpark is session-scoped; it is NOT automatically persisted in the metastore. To be called from SQL, it must be explicitly registered via spark.udf.register(...), and even then it only lasts for the session. C is wrong because the code explicitly handles None (Python's representation of SQL NULL) in the first branch of the function, returning 'unknown' safely. E is wrong because Python UDFs ARE distributed — each Spark executor runs a Python worker process, and the UDF executes in parallel across partitions on worker nodes.
A data engineer needs to query a self-describing JSON file at /mnt/configs/settings.json (a single JSON object, not JSON Lines) directly from Spark SQL without first creating a table. Which statement correctly reads this file?
SELECT * FROM json.\/mnt/configs/settings.json``✓ CorrectSELECT * FROM '/mnt/configs/settings.json'SELECT * FROM delta.\/mnt/configs/settings.json``SELECT * FROM read_files('/mnt/configs/settings.json', format='json')Option A is correct. Spark SQL supports the format.\path`syntax for querying files directly. Usingjson.`path`reads JSON files at the specified location without needing to create a table first. Option D is incorrect syntax —read_filesis not a standard Spark SQL function (it's part of Databricks SQL'sread_filestable-valued function available in newer runtimes, but even then the syntax uses named parameters differently; more importantly, it's not the standard tested pattern). Option B is wrong — a bare path string in a FROM clause is not valid Spark SQL. Option C is wrong —delta.`path`` is used to query Delta tables at a path; using it on a JSON file would cause a format mismatch error.
The free Data Engineer Associate practice test mixes about 20 questions weighted like the exam. The full 300-question bank adds instant grading, flashcards and a timed mock exam.