Databricks-DE-Pro Practice Test 4 — 10 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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.