Databricks-DE-Pro Developing Code (Python/SQL) — Timed 20 Questions

Question 1

A data engineer is implementing a Delta Lake table with streaming ingestion. To ensure high-concurrency writes while maintaining data integrity, what configuration must be enabled for the table to avoid 'Optimistic Concurrency Control' conflicts?

20 minute time limit — choose an answer to begin.

Accepted Answer

Set 'delta.isolationLevel' to WriteSerializable.. Enabling change data feed and utilizing multi-cluster writes or optimizing the commit protocol is essential for high-frequency streaming. Delta Lake uses Optimistic Concurrency Control, which fails if two writers modify the same metadata file. By configuring appropriate isolation levels and using table properties for conflict management, engineers reduce failure rates, which is vital for maintaining low-latency pipelines that require continuous updates from multiple streaming sources simultaneously.

Answer

Enable delta.enableChangeDataFeed.

Answer

Set 'delta.autoOptimize.optimizeWrite' to false.

Answer

Use the 'APPEND' mode exclusively for all writers.

Question 2

Which TWO of the following statements accurately describe the behavior of the 'MERGE' operation in Delta Lake regarding schema evolution and concurrency?

Accepted Answer

MERGE operations support automatic schema evolution only if 'mergeSchema' is set to true in the write options.. MERGE operations in Delta Lake support row-level updates by rewriting only the files that contain matching rows (Copy-on-Write), which is the foundation for its optimistic concurrency control. Automatic schema evolution for MERGE is not enabled by default and specifically requires setting the Spark configuration 'spark.databricks.delta.schema.autoMerge.enabled' to true. The 'mergeSchema' write option is used for standard DataFrame append or overwrite operations, not for the MERGE DML command.

Answer

Delta Lake allows concurrent MERGE operations on the same table without restriction.

Answer

MERGE operations will automatically fail if the source table has more rows than the target table.

Answer

Schema evolution during MERGE is only supported for dropping columns.

Question 3

Refer to the exhibit. A data engineer encounters this error while running a SQL query on a Delta table. Based on the error message, what is the most likely cause?

Accepted Answer

The column reference 'user_id' does not match the actual column name in the table.. The error indicates a case-sensitivity or naming mismatch in the column identifier. Databricks SQL is generally case-insensitive, but when specific aliases or object names are referenced, strict matching is required. Resolving this requires verifying the table schema via DESCRIBE or ensuring the query matches the underlying Parquet metadata. This is fundamental for debugging production pipelines where schema changes can break downstream SQL scripts unexpectedly.

Answer

The table does not exist in the metastore.

Answer

The user lacks permissions to read the specific column.

Answer

The Delta table files are corrupted.

Question 4

You are performing a 'Vacuum' operation on a large Delta table to remove expired files. You notice that the operation is running for an extended period and consuming significant cluster resources. What is the most likely reason for this performance overhead?

Accepted Answer

Vacuum is forced to rewrite the entire table's data.. Vacuuming scans the storage directory to find files not tracked by the current transaction log. When a Delta table accumulates millions of small files (often due to frequent streaming writes or small batch inserts), the file listing and deletion API calls become a major bottleneck, leading to extended execution times and high resource usage.

Answer

The retention period is set to 0, requiring immediate deletion of all history.

Answer

The table is currently being read by another process.

Answer

Vacuum requires that the cluster has enough memory to load the entire table.

Question 5

You have a large Delta table and you need to perform an update on a single row based on a complex condition. You have noticed that 'UPDATE' statements are slow. What is the most effective way to optimize this operation in a production pipeline?

Accepted Answer

Use the 'MERGE' command instead of 'UPDATE'.. In Delta Lake, both UPDATE and MERGE operations perform the same underlying task: identifying the files that contain the target rows, reading them, applying the changes, and rewriting the files. MERGE is not inherently faster than UPDATE for a single row update; both are subject to the same file-level rewrite overhead. The most effective way to optimize a single row update in a large Delta table is to ensure the table is Z-Ordered or partitioned on the column used in the WHERE clause to enable data skipping, thereby minimizing the number of files scanned.

Answer

Increase the number of shuffle partitions to 1000.

Answer

Drop and recreate the table with a new schema.

Answer

Perform the update using a Python UDF.

Question 6

A Data Engineer is developing a Delta Live Tables (DLT) pipeline. They need to ensure that rows containing null values in the 'customer_id' column are dropped during the ingestion process. Which constraint syntax is correct?

Accepted Answer

CONSTRAINT drop_null EXPECT customer_id IS NOT NULL. In Delta Live Tables (DLT) SQL pipelines, data quality constraints are defined using the `CONSTRAINT` keyword, followed by a name and the `EXPECT` clause (e.g., `CONSTRAINT drop_null EXPECT (customer_id IS NOT NULL)`). Option A is the correct SQL syntax for a DLT table definition, whereas option B is the Python decorator equivalent. Assuming standard SQL DLT syntax or clarifying the language is necessary, but since option A uses the explicit `CONSTRAINT` keyword requested by the stem, Option A is the correct constraint syntax.

Answer

@dlt.expect_or_drop('drop_null', 'customer_id IS NOT NULL')

Answer

ALTER TABLE table_name ADD CONSTRAINT customer_id IS NOT NULL

Answer

dlt.validate(customer_id, 'NOT NULL')

Question 7

A Data Engineer is optimizing a PySpark application. They notice significant data skew on the 'region_id' column during a join operation. Which TWO techniques can the engineer use to mitigate this skew?

Accepted Answer

Enable AQE and set spark.sql.adaptive.skewJoin.enabled to true.. Skewed joins occur when a few keys have disproportionately more data, causing specific executors to hang. Enabling Adaptive Query Execution (AQE) with spark.sql.adaptive.skewJoin.enabled automatically detects and handles skew. Alternatively, manually salting the join key by appending a random integer distributes the skewed keys across multiple partitions. Broadcasting the *smaller* table (rather than the larger one) can also avoid shuffles entirely, though broadcasting a large table is impossible or inefficient.

Answer

Increase the number of partitions using repartition() on the skewed column.

Answer

Use a broadcast hint for the larger table in the join.

Answer

Set spark.sql.shuffle.partitions to a much lower value.

Question 8

An engineer wants to capture data lineage and metrics for a custom Python transformation function that processes a Spark DataFrame outside of DLT. What is the most effective way to track this?

Accepted Answer

Use Unity Catalog lineage capabilities by ensuring the code uses standard SQL or DataFrame sink paths.. While DLT has built-in observability, custom PySpark code requires explicit instrumentation. Using the 'dlt.read' or 'dlt.write' approach is limited to DLT pipelines. For external code, standard logging or custom table metadata updates are insufficient for automated lineage. Databricks Unity Catalog, combined with standard Spark event listeners or custom callbacks, allows capturing data lineage information, ensuring compliance and observability across complex, multi-stage data engineering workflows in the Lakehouse.

Answer

Use the dlt.write() method for all DataFrames.

Answer

Manually insert metadata into a separate SQL table.

Answer

Use the Spark UI 'Stages' tab to view lineage.

Question 9

When using Auto Loader to ingest files from cloud storage, what is the primary purpose of the 'cloudFiles.schemaLocation' option?

Accepted Answer

To store the schema definition of the incoming data.. The 'cloudFiles.schemaLocation' option is specifically used to store the inferred schema of the data and evolve it over time. The checkpoint location (defined by 'checkpointLocation') is what manages the state and file metadata. The explanation provided in the question describes the function of the checkpoint location, not the schema location.

Answer

To manage the checkpoint state and file metadata.

Answer

To move the raw files into a Delta table.

Answer

To specify the destination path for the output table.

Question 10

A Data Engineer needs to perform a full refresh of a Delta table while maintaining the table's existing permissions and history. Which approach is best?

Accepted Answer

INSERT OVERWRITE TABLE. INSERT OVERWRITE is the correct approach to replace data while maintaining the table's schema, history, and permissions. The explanation should focus solely on why INSERT OVERWRITE is the standard Delta Lake pattern for this requirement, rather than introducing other commands that weren't options.

Answer

DROP TABLE and CREATE TABLE

Answer

Use the vacuum command to delete all data.

Answer

Change the table location to a new path.

Question 11

An engineer needs to ensure that a Delta table is only updated if a record with the same ID does not exist in the target table. Which MERGE clause should be used?

Accepted Answer

WHEN NOT MATCHED THEN INSERT. The MERGE INTO statement in Delta Lake allows for fine-grained control over how data is synchronized. The 'WHEN NOT MATCHED THEN INSERT' clause is used to specify that a record should be added to the target table only when no match is found for the join key. This is a common pattern for ensuring idempotency during incremental loads, as it prevents the creation of duplicate records for IDs that are already present.

Answer

WHEN MATCHED THEN UPDATE

Answer

WHEN MATCHED AND target.id IS NULL THEN INSERT

Answer

INSERT INTO ... WHERE NOT EXISTS

Question 12

Refer to the exhibit. An engineer is trying to run a VACUUM command on a Delta table but receives an error stating the retention threshold is too low. What is the implication of setting the configuration shown in the exhibit?

Accepted Answer

It permits the VACUUM command to delete files with a retention period less than seven days.. The configuration 'spark.databricks.delta.retentionDurationCheck.enabled' set to 'false' disables the safety check that prevents VACUUM from deleting files younger than 168 hours (7 days). This allows the user to specify a retention period shorter than 7 days in the VACUUM command, which is otherwise blocked by default to prevent data corruption for active readers or Time Travel operations.

Answer

It enables automatic deletion of all files older than the retention period.

Answer

It prevents the Delta table from being accessed by concurrent readers.

Answer

It automatically upgrades the table to the latest Delta Lake protocol version.

Question 13

You have a Delta table that is frequently queried by ID. To optimize performance, you decide to implement Z-Ordering. Which column is the best candidate for Z-Ordering?

Answer

A low-cardinality boolean flag column.

Answer

A timestamp column used for range-based filtering.

Answer

A column used strictly for joining with other tables.

Answer

A column that contains large, unstructured text blobs.

Question 14

A data engineer is writing a PySpark job that reads from a Delta table, applies several transformations, and writes the result to another Delta table. The engineer notices that the job occasionally fails with a FileNotFoundException during the write phase. The source table is frequently updated by another job. Which action should the engineer take to resolve this issue?

Accepted Answer

Enable Delta Lake transaction isolation by setting the table property delta.isolationLevel to Serializable.. The FileNotFoundException occurs because the source Delta table is being modified concurrently while the job reads it. By setting the isolation level to Serializable, the read operation uses a consistent snapshot and avoids conflicts with concurrent writes. This prevents the error and ensures data consistency. The other options do not address the root cause of concurrent updates.

Answer

Enable Auto Compaction on the source Delta table by setting delta.autoOptimize.autoCompact to true.

Answer

Use the option 'versionAsOf' to read a specific version of the source table.

Answer

Set the Spark configuration spark.databricks.delta.snapshotPartitions to a higher value.

Question 15

A data engineer is writing a PySpark script in a Databricks notebook to process a DataFrame. They need to cache the DataFrame to avoid recomputation across multiple actions. Which method should they use to cache the DataFrame in memory and on disk?

Accepted Answer

df.persist(StorageLevel.MEMORY_AND_DISK). To cache a DataFrame in memory and on disk, the engineer should use persist with StorageLevel.MEMORY_AND_DISK. This explicitly sets the storage level to use both memory and disk. The cache method uses a default level that may be MEMORY_AND_DISK but is not explicit. Checkpointing and repartitioning do not provide caching.

Answer

df.repartition(100)

Answer

df.cache()

Answer

df.checkpoint()

Question 16

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?

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 17

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 18

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 19

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 20

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.