Databricks-DE-Pro Practice Exam 2 — 20 Questions

Question 1

A data engineer is using PySpark to process a large DataFrame and needs to reduce the number of partitions before writing to a Delta table to avoid creating too many small files. The DataFrame currently has 2000 partitions, each about 10 MB. The engineer wants to reduce the number of partitions to approximately 200 while minimizing data shuffling. Which approach is most appropriate?

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

Accepted Answer

Use coalesce(200) to reduce the number of partitions without a full shuffle.. The correct answer is to use coalesce(200). coalesce reduces the number of partitions by merging existing partitions without a full shuffle, which is efficient for decreasing partition count. It is particularly useful when the partitions are already reasonably sized and you just want to reduce the number of output files. repartition would work but causes a full shuffle, which is unnecessary here. The other options involve shuffles or are not designed for simple partition reduction.

Answer

Use repartition(200) to evenly redistribute the data across 200 partitions.

Answer

Use repartitionByRange(200) on a key column to control data distribution.

Answer

Use bucketBy(200, 'id') and saveAsTable to create 200 buckets.

Question 2

An organization wants to monitor and limit the spend of their Databricks SQL warehouses. Which feature is most appropriate for setting alerts when costs exceed a certain threshold?

Accepted Answer

Implement Budget Policies in the Databricks account console to set alerts on usage.. Databricks provides budget policies and administrative controls to manage costs. Setting up budget alerts via the Databricks account console allows administrators to define spending limits and receive notifications when consumption approaches these levels. This proactive monitoring is essential for governance, ensuring that cost overruns are identified early and addressed by the relevant teams, preventing unexpected bills and promoting responsible use of compute resources across the organization.

Answer

Enable auto-termination on every individual query executed.

Answer

Use the Databricks Billing System to set a hard limit that automatically shuts down the account.

Answer

Manually check the Spark UI after every query to calculate the cost.

Question 3

Which THREE of the following are benefits of using Delta Lake over standard Parquet files for your data lake storage?

Accepted Answer

ACID transactions for concurrent reads and writes.. Delta Lake adds a transactional layer (the log) on top of Parquet files, enabling ACID transactions, time travel, and schema enforcement. These features solve the most common challenges with raw data lakes, such as partial writes and data corruption. As a Databricks professional, you must prioritize Delta Lake to build reliable, scalable architectures that support robust data governance and high-performance analytical queries without the risks of file-based data inconsistency.

Answer

Faster cold-start reads by caching data on the driver.

Answer

Allows the use of SQL as the only way to interact with data.

Question 4

Which of the following describes the purpose of the 'Gold' layer in a Lakehouse?

Accepted Answer

To store business-ready data for analytical use cases.. The Gold layer is designed for high-performance analytical queries and business intelligence. By storing data in a denormalized or star schema, and by applying business-level aggregations and business rules, this layer is the primary source of truth for downstream reporting tools. It is optimized for the needs of business users, ensuring that they can access consistent and performant data without having to perform complex joins or transformations themselves.

Answer

To store the raw data in an immutable format for historical auditing.

Answer

To serve as a staging area for data cleaning and filtering.

Answer

To store only metadata for the entire Lakehouse architecture.

Question 5

Which TWO of the following are true regarding Unity Catalog's ability to govern external locations?

Accepted Answer

External locations require a storage credential to function.. Unity Catalog can manage access to external cloud storage locations by creating 'External Locations'. This allows administrators to grant specific permissions to users to read from or write to these storage buckets without providing them with direct cloud provider credentials. This abstraction is vital for security, as it centralizes control and auditing of data access within the platform while keeping the underlying storage infrastructure shielded from the users.

Answer

Users can directly mount external locations as DBFS paths.

Answer

External locations are automatically created for every S3 bucket in the account.

Answer

External locations are only supported for Delta-formatted data.

Question 6

A data engineer wants to monitor the data quality of a Delta table over time. Which tool is most appropriate for this task?

Accepted Answer

Implement DLT Expectations within your pipeline code to validate data constraints.. Delta Live Tables (DLT) Expectations are the most appropriate and integrated way to enforce and monitor data quality. By defining constraints directly within the DLT pipeline code, data engineers can automatically validate data as it arrives, generate failure logs, and even quarantine invalid records. This observability is critical for maintaining high-quality datasets in a lakehouse architecture without requiring complex, separate validation workflows for every table.

Answer

Use Databricks SQL Alerts to count total rows in the table every hour.

Answer

Write a Spark job that manually scans the table for NULL values and sends an email.

Answer

Configure a cluster-level alert to trigger when the table metadata is updated.

Question 7

Which TWO of the following are valid ways to trigger a job in Databricks?

Accepted Answer

Using the 'Jobs' UI to trigger a run.. Databricks provides multiple interfaces for job orchestration. The 'Jobs' UI in the workspace allows for interactive configuration and scheduling. Alternatively, the Databricks REST API provides a programmatic way to trigger jobs, which is crucial for CI/CD pipelines and integrating Databricks with external orchestrators like Airflow or Azure Data Factory. Using these methods ensures that pipelines can be automated, monitored, and integrated into broader enterprise data workflows.

Answer

Executing the 'run_job' command inside a notebook cell.

Answer

Running the 'dbutils.jobs.run()' method.

Answer

Creating a file named 'trigger.job' in DBFS.

Question 8

What is the primary benefit of using a Job Cluster instead of an All-Purpose Cluster for production workloads?

Accepted Answer

Job clusters are more cost-effective and provide better workload isolation.. Job clusters are specifically designed for automated production workloads. They are cheaper because they are ephemeral, terminating as soon as the job finishes, and they provide better isolation, ensuring that production jobs do not interfere with other development tasks. This is a critical best practice for cost management and system reliability, ensuring that production pipelines run in a clean, predictable environment that scales according to actual need.

Answer

Job clusters support more advanced libraries than All-Purpose clusters.

Answer

Job clusters can be manually resized while the job is currently running.

Answer

Job clusters are required to access data stored in the Unity Catalog.

Question 9

What is the primary advantage of using Delta Lake as the sink for your data ingestion pipelines compared to raw Parquet files?

Accepted Answer

Delta Lake provides ACID transactions and schema enforcement.. The primary advantage of Delta Lake over raw Parquet is its support for ACID transactions and schema enforcement. These features prevent data corruption during concurrent writes and ensure that only clean, well-structured data enters the lake. This reliability is fundamental for enterprise data pipelines, as it eliminates common issues like partial writes, corrupted files, and downstream failures caused by unexpected schema changes, which are difficult to manage with raw Parquet.

Answer

Delta Lake offers faster read performance for all queries.

Answer

Delta Lake is required for all streaming sources.

Answer

Delta Lake automatically compresses data by 90% more than Parquet.

Question 10

A data engineer is optimizing a Spark job that reads from a large Delta table and performs a join with a smaller dimension table. The job is running slowly, and the engineer suspects data skew and shuffle overhead are the main issues. Which two techniques should the engineer apply to improve performance and reduce cost? (Choose two.)

Accepted Answer

Enable adaptive query execution (AQE) and set spark.sql.adaptive.skewJoin.enabled to true.. Broadcast join eliminates shuffle for the smaller table, and enabling AQE with skew join optimization dynamically handles skew in the larger table. Together, they reduce shuffle overhead and mitigate stragglers, improving performance and lowering cost. Other options either introduce unnecessary shuffles or do not directly address skew.

Answer

Increase the number of shuffle partitions to 2000.

Answer

Cache the larger table in memory before the join.

Answer

Repartition the larger table on the join key before the join.

Question 11

A data engineer is designing a Gold layer table for a retail company. The table must support efficient queries that filter on product_category (low cardinality) and sort by transaction_timestamp (high cardinality). The table is expected to grow to petabytes. Which Delta Lake table design should the engineer choose to optimize both filtering and sorting?

Accepted Answer

Partition by product_category and Z-ORDER BY transaction_timestamp.. Partitioning by the low-cardinality product_category enables efficient partition pruning for filters. Z-ORDER BY the high-cardinality transaction_timestamp clusters data within partitions, speeding up sorting and range queries on that column. This combined approach leverages both partitioning and Z-ORDER appropriately, avoiding the pitfalls of partitioning on high-cardinality columns.

Answer

Z-ORDER BY both product_category and transaction_timestamp.

Answer

Partition by product_category only, without Z-ORDER.

Answer

Partition by transaction_timestamp and Z-ORDER BY product_category.

Question 12

A data engineering team stores customer transaction data in a Unity Catalog managed table named prod.finance.transactions. The security team requires that any query referencing this table, whether through a view or directly, is recorded with the identity of the user who ran it, and that the audit logs are retained for 365 days. The workspace uses Unity Catalog and has audit logs delivered to a cloud storage location. Which configuration should the data engineer verify or set to meet the requirement that all access to the table is captured with the user identity?

Accepted Answer

Ensure that Unity Catalog audit logging is enabled for the account and that the audit log delivery is configured to the required cloud storage with appropriate retention.. Unity Catalog audit logs are generated at the account level and capture the identity of the user performing the action on Unity Catalog objects. To satisfy a retention requirement, the logs must be delivered to durable cloud storage with a retention policy. Cluster-level or view-level controls do not replace this native audit logging, and they do not provide the same structured, tamper-resistant record.

Answer

Configure a cluster policy that enforces the use of a specific Spark log4j appender to send query logs to the audit storage.

Answer

Enable table access control on the cluster and set the cluster's Spark configuration to log all queries.

Answer

Create a view over the table and grant SELECT on the view only, so that all access goes through the view and is logged.

Question 13

A data engineer is setting up a Delta Share to provide an external partner with access to a subset of data. The partner will use a non-Databricks client that supports the Delta Sharing protocol. Which two actions are required to enable the partner to access the shared data? (Choose two.)

Accepted Answer

Create a recipient in Unity Catalog and provide the partner with the activation link or credential file.. To enable an external partner to access a Delta Share, two key steps are required: creating a recipient and providing them with the credential file or activation link, and adding the table to a share while granting the recipient access to that share. These actions establish the secure sharing relationship and define the data scope. Other options are either not applicable to non-Databricks clients or not part of the Delta Sharing setup process.

Answer

Configure a Databricks SQL warehouse for the recipient to query the shared data.

Answer

Enable the Delta Sharing server on the Databricks workspace.

Answer

Grant the recipient SELECT privileges on the shared table.

Question 14

A Spark job is failing with an OutOfMemoryError (OOM) during a group-by operation on a skewed key. What is the most effective way to resolve this?

Accepted Answer

Use the 'salting' technique by adding a random prefix to the skewed key.. Data skew occurs when one key contains a disproportionate amount of data, causing one task to process significantly more than others. Salting (adding a random prefix to the key) breaks this concentration, distributing the data evenly across the cluster. This prevents individual tasks from crashing due to memory limits, allowing the job to complete successfully and efficiently by balancing the workload across all available workers.

Answer

Increase the executor memory size for all workers in the cluster.

Answer

Remove the group-by clause and process the data using a UDF.

Answer

Reduce the number of executors to force the job to run sequentially.

Question 15

Which Databricks feature should be used to gain observability into access patterns and security events across the entire workspace?

Accepted Answer

System tables (Audit logs). System tables (specifically the audit log system table) provide comprehensive logs of all activities within a Databricks account. These tables allow engineers and security teams to monitor access patterns, identify unauthorized actions, and perform compliance reporting. Using system tables is critical for maintaining an audit trail, detecting potential threats, and ensuring that workspace operations adhere to organizational security policies and data governance standards.

Answer

Delta Live Tables (DLT) logs

Answer

Job run history

Answer

Cluster event logs

Question 16

A data engineer is reviewing a Databricks job that runs a notebook to process a large Delta table. The job takes 45 minutes, and the engineer notices that the cluster spends a significant amount of time in the 'Pending' state before execution begins. The cluster is a job cluster with autoscaling enabled and no cluster pool. The engineer wants to reduce the overall job duration and cost. Which action should the data engineer take?

Accepted Answer

Enable a cluster pool and attach the job cluster to it so that instances are pre-provisioned and startup time is reduced.. The 'Pending' state indicates the cluster is waiting for cloud instances to be provisioned. A cluster pool pre-provisions instances so they are ready when the job starts, which reduces the time spent waiting and shortens the overall job duration. While pools have some idle cost, for a frequently running job with significant startup overhead, the reduction in runtime and the ability to use right-sized clusters often results in net savings. Increasing max workers or reducing min workers does not address provisioning delay.

Answer

Increase the maximum number of workers in the autoscaling configuration to 32 so the cluster scales up faster.

Answer

Switch the job to use a serverless job compute environment, which eliminates cluster startup time.

Answer

Reduce the cluster's autoscaling minimum workers to 1 to lower cost, and accept the longer startup.

Question 17

A data engineer is deploying a Databricks Asset Bundle (DAB) that defines a job with a notebook task. The bundle validates locally, but deployment fails with 'Error: cannot find notebook at path /Workspace/Users/dev@example.com/pipeline/ingest'. The engineer confirms the notebook exists in the workspace at that exact path. Which action should the engineer take to resolve the deployment failure?

Accepted Answer

Ensure the notebook path in the bundle configuration is relative to the bundle root and that the sync root includes the notebook. Databricks Asset Bundles synchronize files from the bundle root to the workspace. Notebook paths must be relative to the bundle root and included in the sync. An absolute or incorrectly rooted path causes deployment to fail even if the file exists in the workspace, because the bundle cannot map it to a synchronized file.

Answer

Add the spark.databricks.workspace.path Spark config to the job cluster

Answer

Convert the notebook task to a Python wheel task

Answer

Change the notebook task to use a Git source instead of a workspace path

Question 18

A Data Engineer is building a Databricks SQL pipeline that ingests clickstream events from a Delta table. The events table contains a nested column `payload` of type STRUCT with fields `page_id` (STRING), `duration` (INT), and `referrer` (STRING). The engineer needs to flatten the `payload` fields into top-level columns and drop any records where `page_id` is NULL. Which SQL expression accomplishes this transformation while preserving all other columns?

Accepted Answer

SELECT *, payload.page_id AS page_id, payload.duration AS duration, payload.referrer AS referrer FROM events WHERE payload.page_id IS NOT NULL. The correct solution uses dot notation to access nested STRUCT fields, renames them to top-level columns, and applies a filter on the nested field to drop NULL page_id records. This is the standard and supported way in Databricks SQL to flatten and cleanse nested data while retaining all other columns. The other options misuse functions or syntax not applicable to STRUCT types.

Answer

SELECT *, payload.* AS (page_id, duration, referrer) FROM events WHERE payload.page_id IS NOT NULL

Answer

SELECT *, explode(payload) AS (page_id, duration, referrer) FROM events WHERE page_id IS NOT NULL

Answer

SELECT *, payload[0] AS page_id, payload[1] AS duration, payload[2] AS referrer FROM events WHERE payload[0] IS NOT NULL

Question 19

You are using the Databricks CLI to automate workspace tasks. Which THREE of the following statements correctly identify capabilities of the Databricks CLI?

Accepted Answer

The CLI can manage and deploy Databricks Asset Bundles (DABs).. The Databricks CLI is a powerful tool for DevOps integration and workspace automation. It provides a programmatic interface for interacting with the Databricks control plane, enabling developers to manage notebooks, clusters, and jobs without relying on the GUI. Mastering the CLI is essential for implementing CI/CD pipelines, automating infrastructure deployment via Terraform, and managing large-scale workspace resources efficiently within a professional Databricks environment.

Answer

The CLI can be used to execute arbitrary SQL queries on a SQL Warehouse.

Answer

The CLI can directly modify the contents of a Delta table.

Question 20

A data engineer is designing a Unity Catalog governance model for a new data lakehouse. They need to ensure that data access is auditable and that sensitive data is protected. Which two actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Enable audit logging for the metastore to capture all access and permission changes.. Enabling audit logging captures all access and permission changes, providing the necessary audit trail. Applying tags to sensitive columns and using ABAC policies enforces fine-grained access control based on those tags, protecting sensitive data consistently. Together, these actions create a governance model that is both auditable and secure, leveraging Unity Catalog's native capabilities for centralized policy management.

Answer

Use dynamic views to mask PII columns for all users except administrators.

Answer

Create a separate metastore for each department to isolate data.

Answer

Store all data in a single catalog and grant `ALL PRIVILEGES` to the data engineering team.