Databricks-DE-Pro Practice Test 12 — 15 Questions

Question 1

A Data Engineer wants to monitor cluster health proactively. Which metric is most effective for identifying that a cluster needs to be scaled up to handle increasing workload demands?

Accepted Answer

Cluster CPU utilization. Monitoring 'cluster_memory_utilization' or 'node_cpu_load' is essential for identifying saturation. When these metrics consistently approach high thresholds, it signals that the current compute power is insufficient for the data volume. Proactive scaling based on these metrics prevents job failures and performance degradation, ensuring that data pipelines meet their processing deadlines and maintain efficiency without wasting costs on over-provisioned infrastructure.

Answer

Query result set size

Answer

Delta table file count

Answer

User login frequency

Question 2

You are writing a PySpark script to join two large tables. You want to ensure the join operation is optimized for performance by broadcasting the smaller table. Which configuration property should you adjust, or code construct should you use, to force this behavior?

Accepted Answer

df.join(broadcast(small_df), 'id'). Broadcasting small tables is a critical optimization technique in Databricks to prevent expensive shuffles across the cluster. By utilizing the broadcast hint, developers explicitly instruct the Spark Catalyst optimizer to send the smaller table to all worker nodes. This minimizes data movement and significantly reduces latency during join operations. Understanding this mechanism is essential for building scalable ETL pipelines and ensuring efficient cluster resource utilization within the Databricks unified analytics platform.

Answer

spark.conf.set('spark.sql.shuffle.partitions', '1')

Answer

spark.sql('SET spark.sql.autoBroadcastJoinThreshold = -1')

Answer

df.repartition(100)

Question 3

A data engineer is developing a PySpark job that reads a large Delta table, performs a groupBy on a high-cardinality column, and writes the result to another Delta table. The job is experiencing performance issues due to data skew. The engineer wants to optimize the shuffle by using salting. Which approach correctly implements salting to distribute the skewed keys evenly?

Accepted Answer

Add a random salt column to the DataFrame, group by the salted column, then remove the salt and re-aggregate.. Salting is a technique to distribute skewed keys by appending a random salt to the key before the shuffle, then grouping by the salted key, and finally removing the salt and re-aggregating to get the correct results. This spreads the load of a hot key across multiple partitions. The other options do not correctly implement salting or fail to address the skew.

Answer

Use repartition on the skewed column before the groupBy to increase parallelism.

Answer

Use broadcast join instead of groupBy to avoid the shuffle.

Answer

Set spark.sql.adaptive.enabled to true and rely on Adaptive Query Execution to handle skew automatically.

Question 4

A data engineer is building a Structured Streaming job that reads from a Kafka topic and writes to a Delta table. The pipeline must tolerate late-arriving data up to 10 minutes and update aggregations accordingly. The engineer wants to use a watermark on the event-time column. Which code snippet correctly applies the watermark and performs a 5-minute tumbling window aggregation?

Accepted Answer

df.withWatermark("event_time", "10 minutes").groupBy(window("event_time", "5 minutes")).count(). The correct approach is to apply the watermark on the event-time column before the aggregation, with a delay that matches the late-data tolerance (10 minutes). The aggregation must use a window of the desired size (5 minutes). Placing the watermark after the aggregation or using mismatched durations leads to incorrect results or errors.

Answer

df.withWatermark("event_time", "10 minutes").groupBy("event_time").count()

Answer

df.withWatermark("event_time", "5 minutes").groupBy(window("event_time", "10 minutes")).count()

Answer

df.groupBy(window("event_time", "5 minutes")).count().withWatermark("event_time", "10 minutes")

Question 5

A data engineer is building a streaming ingestion pipeline using Databricks Auto Loader to ingest JSON files from cloud storage into a Delta Bronze table. The pipeline must handle schema evolution without failing and must minimize the number of files that require reprocessing when the schema changes. The engineer wants to configure Auto Loader appropriately. Which two configuration settings should be used to achieve these requirements? (Choose two.)

Accepted Answer

Set cloudFiles.schemaLocation to a dedicated directory in cloud storage.. To handle schema evolution without failing and minimize reprocessing, Auto Loader must be configured to add new columns dynamically and to store schema metadata persistently. Setting the schema evolution mode to addNewColumns ensures new fields are incorporated, while specifying a schema location allows Auto Loader to track schema changes and avoid reprocessing already-ingested files. Together, these settings provide robust schema evolution with efficient incremental processing.

Answer

Set cloudFiles.inferColumnTypes to true.

Answer

Set cloudFiles.useNotifications to true.

Answer

Set cloudFiles.format to 'parquet'.

Question 6

A data engineer is using Delta Live Tables (DLT) to build a pipeline that ingests data from a streaming source. The pipeline must ensure that the target table is updated incrementally and that data quality constraints are enforced. The engineer wants to use expectations to drop invalid records while maintaining pipeline performance. Which TWO of the following are true regarding DLT expectations and their behavior? (Choose two.)

Accepted Answer

Expectations with the 'drop' action will remove records that violate the constraint from the target table.. DLT expectations support actions like warn, drop, and fail. The 'drop' action removes invalid records and continues, while 'fail' stops the pipeline. These actions apply to both streaming tables and materialized views, and are evaluated on every update, not just the initial load. Thus, the statements about 'drop' and 'fail' are correct.

Answer

When an expectation with 'drop' action is violated, the pipeline fails and must be restarted.

Answer

Expectations can only be applied to streaming tables, not to materialized views.

Answer

Expectations are evaluated only during the initial data load, not on subsequent incremental updates.

Question 7

A data engineer is ingesting data from an Azure SQL Database into a Delta Lake table using the JDBC connector in a Databricks notebook. The source table contains millions of rows, and the engineer wants to optimize the ingestion by reading the data in parallel. The source table has a numeric primary key column named 'id' that is evenly distributed. Which approach should the engineer use to achieve parallel reads?

Accepted Answer

Specify the 'partitionColumn', 'lowerBound', 'upperBound', and 'numPartitions' options in the JDBC read configuration.. To read a large JDBC table in parallel, you must configure the JDBC connector with partitioning options: partitionColumn, lowerBound, upperBound, and numPartitions. The partitionColumn must be numeric, and the bounds define the range for partitioning. Spark then creates multiple tasks to read the data concurrently. This is the standard method for parallelizing JDBC reads in Databricks.

Answer

Set the 'fetchsize' option to a large value to increase the number of rows retrieved per round trip.

Answer

Use the 'query' option with a custom SQL statement that includes a 'WHERE' clause with modulo arithmetic to split the data.

Answer

Enable 'spark.sql.adaptive.enabled' to automatically parallelize the JDBC read.

Question 8

A Data Engineer is building a Structured Streaming pipeline that reads from a Kafka topic and writes to a Delta table. The pipeline must handle late-arriving data up to 2 hours and produce correct aggregations per 10-minute window. The engineer wants the streaming query to automatically clean up old state so the job does not accumulate unbounded state. Which combination of Structured Streaming features should be used?

Accepted Answer

Use a tumbling window of 10 minutes with a watermark of 2 hours on the event-time column, and set the output mode to append.. To handle late data up to 2 hours while producing 10-minute aggregations and bounded state, the pipeline needs event-time tumbling windows of 10 minutes and a watermark of 2 hours. Append mode emits a window result only after the watermark exceeds the window end, and Spark automatically evicts state older than the watermark, keeping state size proportional to the lateness bound rather than the entire stream history.

Answer

Use a sliding window of 10 minutes with a watermark of 10 minutes on the event-time column, and set the output mode to update.

Answer

Use a tumbling window of 10 minutes with a watermark of 2 hours on the processing-time column, and set the output mode to append.

Answer

Use a tumbling window of 2 hours with a watermark of 10 minutes on the event-time column, and set the output mode to complete.

Question 9

A data engineer is ingesting data from an Apache Kafka topic into a Delta Lake table using Structured Streaming. The Kafka topic receives messages with a timestamp field in the value payload, but the ingestion must handle late-arriving data and produce correct aggregations. The engineer wants to ensure that watermarks are applied correctly. Which approach should be used?

Accepted Answer

Parse the timestamp field from the Kafka value payload, cast it to a timestamp, and apply a watermark on that column.. For correct event-time processing with Kafka and Delta Lake, the event timestamp must come from the message payload, not from Kafka metadata or processing time. Parsing that field and applying a watermark on it enables Structured Streaming to manage late-arriving data accurately. This ensures that aggregations and stateful operations reflect the true event time and that late data is handled according to the defined watermark.

Answer

Use the Kafka timestamp column provided by the Kafka source, and apply a watermark on that column.

Answer

Use the current_timestamp() function to generate a processing-time column and apply a watermark on that column.

Answer

Set the Kafka source option 'startingOffsets' to 'earliest' and rely on the default watermark.

Question 10

A data engineer is using Delta Live Tables (DLT) to create a pipeline that processes streaming data. They need to ensure that the pipeline only processes new data since the last run and that the pipeline can recover from failures without reprocessing all data. Which combination of features should they use to achieve this?

Accepted Answer

Use a streaming live table and rely on DLT's automatic checkpointing and state management.. DLT streaming live tables automatically manage checkpoints and state, ensuring that only new data is processed and that the pipeline can recover from failures without reprocessing all data. This is a core feature of DLT. The other options either incorrectly rely on manual checkpointing, use batch constructs that don't support incremental processing, or misunderstand the capabilities of materialized views.

Answer

Use a streaming live table with a checkpoint location specified in the pipeline configuration.

Answer

Use a materialized view with a trigger to process only new data based on a timestamp column.

Answer

Use a batch live table with a filter on the ingestion timestamp to process only new records.

Question 11

A data engineer is implementing a Structured Streaming job that reads from a Kafka topic and writes to a Delta table. The engineer needs to ensure that the job can recover from failures without data loss or duplication. The job uses foreachBatch to perform upserts into the Delta table. Which checkpointing configuration is required to achieve exactly-once semantics?

Accepted Answer

Set the checkpointLocation to a reliable distributed storage path and ensure the Delta table write is idempotent using merge.. Exactly-once semantics in Structured Streaming require a reliable checkpoint location to track progress and idempotent writes to handle reprocessing. Using foreachBatch with MERGE provides idempotent upserts, and a distributed checkpoint location ensures recovery. The other options either use unreliable storage, non-idempotent writes, or unsupported write modes, failing to guarantee exactly-once.

Answer

Use the option 'startingOffsets' set to 'latest' and enable trigger once for the streaming query.

Answer

Set the checkpointLocation to a local path on the driver and use append mode for the Delta table write.

Answer

Set the Spark configuration spark.sql.streaming.checkpointLocation to a DBFS path and use complete mode for the Delta table write.

Question 12

A data engineer is reviewing the event log of a Databricks job that has just failed. They need to determine the exact cause of the failure. Which event type in the event log indicates that a task failed due to an exception?

Accepted Answer

TASK_FAILED. The TASK_FAILED event is logged when an individual task within a job run encounters an exception. It captures the error message, stack trace, and other relevant metadata, making it the primary source for diagnosing task-level failures. Other event types like JOB_FAILED indicate a higher-level failure but lack the specific task exception details. Therefore, TASK_FAILED is the correct choice for determining the exact cause of a task failure.

Answer

RUN_FAILED

Answer

JOB_FAILED

Answer

EXECUTION_FAILED

Question 13

A Data Engineer is working on a Delta Live Tables (DLT) pipeline that ingests JSON files from cloud storage. The pipeline must drop rows where the 'email' column is null and also flag rows where 'age' is negative as invalid, but still process them. Which combination of DLT expectations should be used?

Accepted Answer

Use @dlt.expect_or_drop("valid_email", "email IS NOT NULL") and @dlt.expect("non_negative_age", "age >= 0"). The correct approach uses expect_or_drop for the email null rule to remove those rows, and expect for the age rule to record violations without dropping. This satisfies both the drop and flag requirements simultaneously. Other expectation types either fail the pipeline, only log metrics, or drop rows that should be retained.

Answer

Use @dlt.expect_all({"valid_email": "email IS NOT NULL", "non_negative_age": "age >= 0"})

Answer

Use @dlt.expect_or_fail("valid_email", "email IS NOT NULL") and @dlt.expect_or_drop("non_negative_age", "age >= 0")

Answer

Use @dlt.expect_all_or_drop({"valid_email": "email IS NOT NULL", "non_negative_age": "age >= 0"})

Question 14

A Data Engineer is using Delta Live Tables to process a stream of user events. The `user_id` column should be unique in the target table, but the source may contain duplicate events due to retries. The engineer wants to keep only the latest event for each `user_id` based on the `event_timestamp`. Which Delta Live Tables feature should be used?

Accepted Answer

Use `APPLY CHANGES INTO target` with `KEYS (user_id)` and `SEQUENCE BY event_timestamp`, and set `STORED AS SCD TYPE 1`.. To deduplicate and keep only the latest event per `user_id`, `APPLY CHANGES` with SCD Type 1 and a sequence column is the correct Delta Live Tables feature. It performs upserts based on the key and sequence, ensuring one row per key with the latest data. SCD Type 2 would keep history, and window functions or dropDuplicates are not suitable for streaming deduplication.

Answer

Use `APPLY CHANGES INTO target` with `KEYS (user_id)` and `SEQUENCE BY event_timestamp`, and set `STORED AS SCD TYPE 2`.

Answer

Use `@dp.expect_or_drop("unique_user", "user_id IS NOT NULL")` and then apply `dropDuplicates(["user_id"])` on the resulting DataFrame.

Answer

Use `@dp.expect_or_drop("unique_user", "row_number() OVER (PARTITION BY user_id ORDER BY event_timestamp DESC) = 1")` in a streaming table definition.

Question 15

A data engineer is configuring an Auto Loader stream to ingest JSON files from an S3 bucket into a Bronze Delta table. The source bucket contains both .json and .json.gz files, and the engineer wants to ensure that only .json files are processed. Which parameter should be set to achieve this?

Accepted Answer

cloudFiles.pathGlobFilter. Auto Loader provides the cloudFiles.pathGlobFilter option to filter files using glob patterns. Setting it to '*.json' ensures that only files with the .json extension are processed, while other files like .json.gz are ignored. This is the intended mechanism for file selection based on path patterns in Auto Loader, making it the correct choice for this scenario.

Answer

cloudFiles.schemaLocation

Answer

cloudFiles.includeExistingFiles

Answer

cloudFiles.format