Databricks-DE-Pro Developing Code (Python/SQL) Practice Test 2 — 15 Questions

Question 1

A data engineer is working with a Delta table that has a column 'id' as the primary key. The table is updated frequently with new records, and the engineer needs to ensure that only the latest record for each 'id' is kept. The engineer decides to use MERGE to upsert data from a staging table. Which MERGE condition will correctly update existing records and insert new ones?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *. The correct MERGE statement uses WHEN MATCHED THEN UPDATE SET * to update all columns for existing records and WHEN NOT MATCHED THEN INSERT * to insert new records. This is the standard upsert pattern. The other options either have reversed actions, invalid syntax, or delete instead of update.

Answer

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN INSERT * WHEN NOT MATCHED THEN UPDATE SET *

Answer

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT *

Answer

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN UPDATE SET target.* = staging.* WHEN NOT MATCHED THEN INSERT *

Question 2

A Data Engineer needs to implement a PySpark transformation that processes a large DataFrame partitioned by date. The transformation must apply a complex Python function to each row, but the function requires loading a large lookup table (about 2 GB) that should be reused across all executors. The engineer wants to minimize serialization overhead and avoid reloading the lookup table for every row. Which approach is most appropriate?

Accepted Answer

Use a mapPartitions transformation where the lookup table is loaded once per partition from a broadcast variable or a distributed cache.. The most efficient approach is to use mapPartitions, which allows the lookup table to be loaded once per partition (or executor) and reused across all rows in that partition. This avoids per-row loading and reduces serialization overhead. Combining with a broadcast variable ensures the lookup table is distributed once and available to all executors without repeated I/O. This pattern is ideal for large, static lookup data used in row-wise transformations.

Answer

Use a foreachPartition action that writes results to an external system, and load the lookup table inside the function for each partition.

Answer

Use a pandas UDF with the function decorated with @pandas_udf, and inside the function load the lookup table from a file each time.

Answer

Broadcast the lookup table as a Spark DataFrame and perform a join with the large DataFrame before applying the Python function.

Question 3

A data engineer needs to create a Delta table that enforces a constraint to ensure that the 'email' column contains a valid email format. The engineer wants to reject any rows that do not match the pattern. Which SQL statement should be used to add this constraint?

Accepted Answer

ALTER TABLE users ADD CONSTRAINT valid_email CHECK (email LIKE '%@%.%');. Delta Lake supports CHECK constraints to enforce data quality rules. The correct syntax is ALTER TABLE ... ADD CONSTRAINT CHECK (). This ensures that any new data written to the table must satisfy the condition, otherwise the write fails. The other options are either invalid syntax or do not enforce constraints.

Answer

ALTER TABLE users ADD CHECK (email LIKE '%@%.%');

Answer

CREATE CONSTRAINT valid_email ON users (email) CHECK (email LIKE '%@%.%');

Answer

ALTER TABLE users SET TBLPROPERTIES ('constraints' = 'email_format');

Question 4

A Data Engineer is developing a PySpark job that reads from a Delta table, performs several transformations, and writes the result to another Delta table. The engineer notices that the job is producing many small files in the target table, which degrades read performance. The engineer wants to reduce the number of files written without changing the partitioning of the target table. Which optimization technique should be applied?

Accepted Answer

Use the coalesce method to reduce the number of partitions before writing to the Delta table.. Using coalesce reduces the number of partitions without a full shuffle, directly decreasing the number of files written to the Delta table. This is an efficient way to consolidate small files. Unlike repartition, coalesce does not redistribute data across the cluster, so it is less expensive. It also does not alter the partitioning of the target table, satisfying the constraint.

Answer

Set spark.sql.shuffle.partitions to a higher value to increase parallelism.

Answer

Enable adaptive query execution (AQE) and set spark.sql.adaptive.coalescePartitions.enabled to true.

Answer

Repartition the DataFrame by the partition column of the target table before writing.

Question 5

A data engineer is using Databricks Repos to manage a Python project. The project contains multiple notebooks and a Python module in a separate file. The engineer wants to import the module into a notebook. Which approach should be used to ensure the module is importable within the notebook?

Accepted Answer

Add the path to the module's directory to sys.path using sys.path.append('/Workspace/Repos/...') and then import the module.. In Databricks Repos, the notebook's working directory is the repo root, but Python does not automatically include subdirectories in sys.path. To import a module from a subdirectory, you must add that directory to sys.path. This is the standard method for importing local modules in Repos. The other options either do not work for .py files or involve unnecessary packaging.

Answer

Install the module as a wheel package using %pip install and then import it.

Answer

Use dbutils.library.install to install the module from the repo.

Answer

Use the %run magic command to execute the Python module as a notebook.

Question 6

A data engineer is optimizing a PySpark job that processes a large Delta table. The job performs a groupBy on a high-cardinality column and then joins the result with another table. The engineer notices that the job is slow and suspects data skew. Which two actions should the engineer take to mitigate the skew? (Choose two.)

Accepted Answer

Repartition the DataFrame on the skewed key with a higher number of partitions before the groupBy.. Data skew in groupBy operations can be mitigated by salting the skewed key to distribute data evenly, and by repartitioning the DataFrame on the skewed key with a higher number of partitions to increase parallelism. These techniques directly address the uneven distribution of data. The other options either do not target skew in groupBy or are not as effective for this specific scenario.

Answer

Broadcast the smaller table in the join to avoid shuffling the large table.

Answer

Enable Adaptive Query Execution (AQE) and set spark.sql.adaptive.skewJoin.enabled to true.

Answer

Increase the number of shuffle partitions by setting spark.sql.shuffle.partitions to a higher value.

Question 7

A Data Engineer is writing a PySpark job that reads a large Parquet dataset, performs a join with a smaller dimension table, and writes the result to a Delta table. The engineer wants to optimize the job for performance and cost. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Set spark.sql.autoBroadcastJoinThreshold to a higher value to ensure the dimension table is broadcast.. The most effective optimizations are to broadcast the smaller dimension table and to increase the auto broadcast join threshold if needed. Broadcasting avoids shuffling the large fact table, which is the most expensive part of a join. Adjusting the threshold ensures that Spark automatically chooses a broadcast join when the dimension table is slightly larger than the default limit. These two actions together minimize data movement and improve performance.

Answer

Repartition the large fact table by the join key before the join to balance the data.

Answer

Use a salting technique on the large fact table to handle data skew before the join.

Answer

Cache the smaller dimension table in memory before the join.

Question 8

A data engineer is implementing a Delta Live Tables (DLT) pipeline that ingests data from a streaming source. The pipeline must ensure that data is processed exactly once and that the pipeline can recover from failures without data loss or duplication. Which two configurations or practices are required to achieve exactly-once processing in DLT? (Choose two.)

Accepted Answer

Use Delta Lake as the sink for all streaming tables, leveraging its transactional guarantees.. Exactly-once processing in DLT requires checkpointing to track stream progress and Delta Lake as the sink to provide atomic, idempotent writes. Checkpointing ensures that the pipeline resumes from the correct position after a failure, while Delta's transactional guarantees prevent partial or duplicate writes. Other options like foreachBatch, continuous mode, or schema management are not core requirements for exactly-once. Therefore, the two correct choices are checkpointing and using Delta Lake as the sink.

Answer

Ensure that the target Delta table is configured with the correct schema and is not manually modified outside the pipeline.

Answer

Set the pipeline mode to 'continuous' to ensure low-latency processing.

Answer

Use the 'foreachBatch' sink with an idempotent write operation to the target table.

Question 9

A data engineer is writing a PySpark script to read a JSON file from cloud storage. The file contains nested fields and the engineer wants to flatten the structure to access nested attributes easily. Which PySpark function should be used to extract a nested field from a DataFrame column?

Accepted Answer

col('nested').getField('field'). The correct way to extract a nested field from a struct column in PySpark is to use the getField method on the Column object. This method explicitly retrieves the specified field from a struct. The other options either misuse dot notation or use getItem, which is for arrays and maps. Therefore, col('nested').getField('field') is the appropriate function to flatten or access nested attributes.

Answer

col('nested').getItem('field')

Answer

col('nested').field

Answer

col('nested.field')

Question 10

You are optimizing a PySpark job that reads from a Delta table. You notice skewed data distribution on the 'customer_id' column, causing Task-level stragglers. Which transformation should you apply to the DataFrame to mitigate this skew during a join operation?

Accepted Answer

Salt the skewed column and perform the join.. Salting involves adding a random prefix to the join key of the skewed table and exploding the join key of the dimension table. This technique breaks down massive partitions caused by skewed keys into smaller, evenly distributed chunks. Understanding data skew is critical in Databricks for maintaining cluster stability and preventing OOM errors, ensuring that shuffle partitions are utilized efficiently across all available executors rather than overloading one core.

Answer

Increase the spark.sql.shuffle.partitions configuration dynamically.

Answer

Broadcast the skewed table to all executors.

Answer

Cache the skewed table in memory before the join.

Question 11

You are processing sensitive PII data in a Delta table. You need to ensure that specific columns containing PII are not readable by general data analysts while maintaining the ability to perform aggregate analysis on those rows. Which feature should you implement?

Accepted Answer

Apply Dynamic Views with functions like 'mask_hash' or 'case'.. Databricks Unity Catalog provides robust data governance, including column-level security through Dynamic Views. By defining a view that masks or filters data based on the user's role, you ensure PII protection while allowing data exploration. Managing access control via Unity Catalog is a standard professional requirement for maintaining compliance (GDPR/CCPA) and ensuring that sensitive information remains secure within the enterprise data architecture.

Answer

Use the 'ALTER TABLE DROP COLUMN' command.

Answer

Implement row-level security using Spark configurations.

Answer

Encrypt the entire storage bucket using cloud-native tools.

Question 12

A data engineer is tuning a Spark job and decides to use 'Z-Ordering' on a Delta table. Which THREE of the following are valid considerations when selecting columns for Z-Ordering?

Accepted Answer

Columns with high cardinality are generally better candidates.. Z-Ordering improves data skipping by clustering related information in the same set of files. Choosing the right columns is crucial; high-cardinality columns with frequent filter predicates are ideal. Excessive Z-Ordering can lead to high maintenance costs and metadata overhead. Mastering this technique is essential for performance tuning in Databricks, as it drastically reduces the amount of data read during query execution, directly impacting both latency and cloud infrastructure costs.

Answer

You should Z-Order on every column to maximize performance.

Answer

Z-Ordering is most effective on columns that are never filtered.

Question 13

You are migrating a legacy ETL process to Delta Live Tables (DLT). You have an existing table defined with a complex transformation that involves a custom Python function using a third-party library. How should you structure this in DLT to ensure the function is available and correctly applied?

Accepted Answer

Define the function inside the same notebook but outside the 'dlt.table' function.. DLT pipelines require explicit management of dependencies. Using the 'dlt.table' decorator alongside proper library installation via cluster configuration or pip requirements ensures that Python functions are serialized and distributed to worker nodes. Understanding how DLT handles dependencies and decorators is critical for building reliable, production-grade pipelines that maintain parity with legacy logic while leveraging the automatic orchestration and quality management features inherent in the DLT framework.

Answer

Hardcode the library path using 'sys.path.append'.

Answer

You cannot use custom Python functions in DLT.

Answer

Use the 'spark.udf.register' method globally.

Question 14

When designing a streaming pipeline using Structured Streaming, which THREE of the following are necessary to ensure 'exactly-once' processing semantics in Databricks?

Accepted Answer

Using a source system that supports replaying data (e.g., Kafka or Delta).. Exactly-once processing requires that the source system, the processing engine, and the sink system all support fault-tolerant checkpoints and idempotency. Databricks handles this through checkpointing and write-ahead logs. Understanding these components is critical for data engineers to ensure data consistency in critical financial or operational systems, preventing the common pitfalls of duplicate entries or missed data during cluster restarts or transient failures.

Answer

Setting 'spark.sql.shuffle.partitions' to 1.

Answer

Disabling the Delta Lake write-ahead log.

Question 15

You have a large Spark DataFrame that you need to filter and save as multiple smaller Parquet files based on the values in a 'region' column. Which method should you use to optimize the file layout for subsequent queries?

Accepted Answer

Use the 'partitionBy' option in the write command.. Partitioning by a low-cardinality column like 'region' physically organizes data into folders. This allows engines to perform 'partition pruning', skipping irrelevant folders during query execution. This is a standard optimization in Databricks for any dataset with clear categorical groupings, as it directly reduces the I/O cost of queries that filter by the partition key, improving performance significantly for analysts and dashboard tools.

Answer

Use the 'repartition' method on the DataFrame before writing.

Answer

Use the 'sortWithinPartitions' method.

Answer

Use the 'coalesce' method.