Databricks-DE-Pro Developing Code (Python/SQL) Practice Test 1 — 15 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?

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

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()