Databricks-DE-Pro Practice Test 21 — 15 Questions

Question 1

A healthcare company uses a Databricks Lakehouse. The Silver layer contains a table patient_visits that is updated with late-arriving data. The table is partitioned by visit_date. The data engineering team needs to efficiently merge new data that may include updates to existing records and inserts of new records. They want to minimize the impact on existing data and ensure ACID compliance. Which Delta Lake operation should they use?

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

Accepted Answer

MERGE INTO patient_visits USING new_data ON patient_visits.visit_id = new_data.visit_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *. The MERGE statement is designed for upserts, allowing updates to existing records and inserts of new records in one atomic operation. It minimizes data rewriting by only touching affected files and maintains ACID compliance. Other options either overwrite data, are non-atomic, or do not apply changes.

Answer

Use Delta Lake change data feed to apply changes.

Answer

INSERT OVERWRITE patient_visits SELECT * FROM new_data

Answer

DELETE FROM patient_visits WHERE visit_id IN (SELECT visit_id FROM new_data); INSERT INTO patient_visits SELECT * FROM new_data

Question 2

A data engineer needs to receive an email notification when a Databricks job fails. The job is scheduled to run every hour. The engineer wants to configure this notification with minimal effort and without writing additional code. Which approach should the engineer use?

Accepted Answer

Configure the job's email notifications in the job settings to send alerts on failure to the engineer's email address.. Databricks jobs include built-in email notification settings that can be configured to send alerts on failure. This is the most straightforward and code-free method to receive email notifications for job failures, meeting the engineer's requirements for minimal effort.

Answer

Create a Databricks SQL alert that queries the job's run history and triggers when a failure is detected.

Answer

Use the Databricks REST API to poll the job status every hour and send an email if the status is failed.

Answer

Set up a webhook in the job configuration to call an external service that sends an email.

Question 3

Refer to the exhibit. A data engineer executes this command in a Unity Catalog-enabled workspace. What is the immediate effect on the 'analyst_group'?

Accepted Answer

The group gains permission to read data but not to modify table metadata.. The command grants 'SELECT' privileges on the 'orders' table to the 'analyst_group' within the 'sales' schema of the 'main' catalog. This is a standard Unity Catalog operation that enables users within the group to query the table. Understanding these permissions is vital for auditors and engineers managing data access lifecycle, ensuring only authorized groups can read sensitive business records.

Answer

The group gains ownership of the table, allowing them to drop it.

Answer

The command fails because 'main.sales.orders' is not a fully qualified name.

Answer

The group gains access to both the data and the underlying S3 files directly.

Question 4

A data engineer is designing a pipeline and notices that the cost of processing is unexpectedly high during development. Which action provides the most immediate cost reduction when using Databricks?

Accepted Answer

Utilizing Spot instances for non-critical development and test clusters.. Choosing the right instance type and using Spot/Preemptible instances is a foundational cost-optimization strategy. Spot instances leverage unused cloud capacity at significantly lower prices compared to On-Demand instances. While they can be reclaimed by the cloud provider, using them for development, testing, or fault-tolerant batch jobs is a highly effective way to slash cloud infrastructure spending without altering the underlying code or business logic of the data pipeline.

Answer

Switching to a more expensive instance type to complete the job faster.

Answer

Enabling Photon acceleration for all development workloads.

Answer

Increasing the number of workers in the cluster to handle more data in parallel.

Question 5

Refer to the exhibit. Which configuration change is required to enable multiple instances of this job to run simultaneously?

Accepted Answer

Set max_concurrent_runs to a value greater than 1 in the job settings.. The error message explicitly states that the job does not allow concurrent runs. By default, many jobs are configured for single-instance execution to prevent data contention or race conditions. To allow overlapping executions, the 'max_concurrent_runs' parameter must be adjusted in the job settings. This is a critical configuration for scenarios where independent data partitions need to be processed in parallel to meet aggressive latency requirements in a production system.

Answer

Increase the timeout threshold for the job.

Answer

Add more workers to the cluster assigned to the job.

Answer

Enable 'Retries' in the job configuration.

Question 6

Which property should be configured to allow Databricks to automatically optimize the size of files during write operations in Delta Lake?

Accepted Answer

spark.databricks.delta.optimizeWrite.enabled. The `spark.databricks.delta.optimizeWrite.enabled` property is essential for write-time optimization. It dynamically groups data before writing to storage, ensuring that the created files are of optimal size. This prevents the small-file problem from occurring in the first place, reducing the need for post-write maintenance and improving read query performance significantly. It is a proactive performance optimization that is highly recommended for high-frequency write workloads.

Answer

spark.sql.shuffle.partitions

Answer

spark.databricks.io.cache.enabled

Answer

spark.sql.autoBroadcastJoinThreshold

Question 7

A Data Engineer is using Delta Live Tables (DLT) to build a pipeline that ingests JSON files from cloud storage. The engineer defines a streaming table with expectations to enforce data quality. The expectation `@dlt.expect_or_drop("valid_timestamp", "timestamp IS NOT NULL")` is applied. During a pipeline run, 5% of records have a NULL timestamp. What is the outcome for those records, and how does it affect the pipeline?

Accepted Answer

The records with NULL timestamp are dropped from the target table, and the pipeline continues processing without failure.. The `expect_or_drop` expectation drops records that violate the condition and allows the pipeline to continue. It does not quarantine records, retain them, or fail the pipeline. This behavior is designed to handle data quality issues without interrupting processing, while still providing metrics on dropped records.

Answer

The records with NULL timestamp are dropped, and the pipeline fails after processing the batch due to the drop threshold being exceeded.

Answer

The records with NULL timestamp are quarantined in a separate table, and the pipeline fails with an error.

Answer

The records with NULL timestamp are retained in the target table, but the pipeline fails with a data quality error.

Question 8

When migrating to Unity Catalog, what is the best practice for managing existing data access permissions?

Accepted Answer

Implement a role-based access control (RBAC) model in Unity Catalog.. Adopting the principle of least privilege during migration is essential. By reviewing and re-granting permissions in the new Unity Catalog environment, you can eliminate legacy security debt and ensure that only necessary access is provided. This is the perfect opportunity to implement a robust, role-based access control (RBAC) model, ensuring the new environment is more secure than the legacy Hive Metastore it replaces, and facilitating long-term compliance.

Answer

Automatically import all Hive Metastore permissions during migration.

Answer

Grant every user 'ADMIN' access to simplify the migration process.

Answer

Use the metastore owner's credentials for all data access.

Question 9

Which TWO of the following are primary benefits of using Unity Catalog for managing data lineage in Databricks?

Accepted Answer

It provides automated, column-level lineage tracking for SQL and Python workloads.. Unity Catalog automatically captures lineage at the table and column level for queries executed on the platform. This metadata is crucial for impact analysis and compliance auditing. By providing a unified view of how data moves from source to destination, organizations can maintain transparency, satisfy regulatory requirements for data tracking, and easily debug complex ETL pipelines that span across multiple workspaces and various cloud storage locations.

Answer

It forces all data to be stored in a single, centrally managed S3 bucket.

Answer

It automatically encrypts all data at rest using customer-managed keys.

Answer

It allows users to manually edit the lineage graph to include external data sources.

Question 10

A data engineer manages a Databricks SQL warehouse that serves a dashboard used by the finance team. The dashboard queries have become slow during peak hours, and the engineer suspects that some queries are scanning excessive data. Which system table should the engineer query to analyze query performance and identify expensive queries?

Accepted Answer

system.query.history. The system.query.history table is designed for query observability in Databricks SQL. It captures execution details, including query duration, rows produced, and bytes read, enabling engineers to pinpoint inefficient queries. Other system tables focus on compute metrics, audit events, or billing, and lack the query-level performance data needed to troubleshoot slow dashboards.

Answer

system.billing.usage

Answer

system.access.audit

Answer

system.compute.node_timeline

Question 11

A data engineer is responsible for a Delta Live Tables pipeline that ingests streaming data from multiple sources. The pipeline occasionally experiences delays, and the engineer needs to monitor the pipeline's health. Which two metrics should the engineer monitor to detect ingestion backlog and processing latency? (Choose two.)

Accepted Answer

The 'processingTime' metric in the streaming query progress. numInputRows and processingTime are key metrics in the streaming query progress that directly reflect ingestion rate and processing duration. Monitoring both allows the engineer to detect when the pipeline is not keeping up with the source, causing backlog and increased latency. Other metrics like error counts, DBU usage, or storage size do not provide the necessary real-time performance insight.

Answer

The number of records in the event log with severity 'ERROR'

Answer

The number of DBUs consumed by the pipeline cluster

Answer

The total size of the Delta table in storage

Question 12

Which approach is most appropriate for ingesting data from a JDBC source into Delta Lake where the source table has no 'updated_at' or 'version' column for incremental loading?

Accepted Answer

Perform a full overwrite of the Delta table for every load.. When a source table lacks a watermark column, you cannot use standard incremental loading techniques. The most robust approach is a full overwrite of the destination table, ensuring that the target remains a faithful copy of the source. While this can be resource-intensive for large tables, it is the only way to ensure data integrity without primary keys or timestamps to track changes.

Answer

Use the 'partitionColumn' parameter with a random UUID.

Answer

Enable streaming ingestion using the JDBC source readStream API.

Answer

Use the 'fetchSize' parameter to optimize the load.

Question 13

Which of the following describes the correct use of a UDF (User Defined Function) in PySpark for production pipelines?

Accepted Answer

Use Pandas UDFs to leverage vectorized operations and improve performance.. UDFs in PySpark move data from the JVM (Java Virtual Machine) to a Python process, which incurs significant serialization overhead. When possible, it is always best to use native Spark SQL functions, which operate directly on the JVM, providing better performance and native optimization by the Catalyst engine. If a UDF is unavoidable, using Pandas UDFs (Vectorized UDFs) with Apache Arrow is the recommended approach to minimize serialization costs.

Answer

Always write row-based Python UDFs for maximum flexibility.

Answer

UDFs automatically optimize data distribution across the cluster.

Answer

UDFs are the most efficient way to perform simple string manipulation.

Question 14

A data engineer wants to monitor the performance of a Databricks cluster by tracking the average CPU utilization over time. Which Databricks feature should they use to visualize this metric?

Accepted Answer

Cluster metrics dashboard in the Databricks UI. The cluster metrics dashboard in the Databricks UI is designed to display cluster performance metrics such as CPU utilization, memory usage, and network activity. It provides both real-time and historical views, allowing engineers to monitor trends and identify performance issues. Other tools like Spark UI focus on job execution, while Databricks SQL dashboards are for data visualization, not infrastructure monitoring. Therefore, the cluster metrics dashboard is the correct choice.

Answer

Spark UI

Answer

Databricks SQL dashboards

Answer

Ganglia

Question 15

A data engineering team experiences massive compute waste because interactive development notebooks are frequently left running overnight by engineers. As a Databricks administrator, which configuration should you implement at the cluster policy level to automatically mitigate this financial exposure without disrupting ongoing development work?

Accepted Answer

Set a mandatory auto_termination_minutes rule in the cluster policy enforcing a maximum idle timeout threshold for all user-created clusters.. Enabling automatic cluster termination based on an idle timeout is the most direct policy enforcement mechanism to eliminate compute waste from forgotten interactive sessions. This setting continuously monitors the execution state of the driver and worker nodes, shutting down the cluster safely once the specified threshold is crossed, which drastically reduces cloud infrastructure spending while preserving user autonomy during active hours.

Answer

Configure a maximum worker node limit constraint within the compute policy that prevents scaling beyond two nodes.

Answer

Enforce a strict maximum instance lifetime limit that terminates the cluster regardless of whether interactive queries or jobs are running.

Answer

Disable the ability for standard users to attach notebooks to interactive clusters, forcing all execution through scheduled jobs.