Databricks-DE-Pro Practice Questions 2 — 50 Questions

Question 1

Which THREE features are provided by Delta Lake when compared to standard Parquet files? (Select THREE)

Accepted Answer

ACID transactions. Delta Lake adds a transaction log (the _delta_log) to standard Parquet files, enabling ACID transactions, time travel, and schema enforcement. These features solve common data engineering pain points such as partial writes, data corruption, and the inability to revert to previous versions of data. Mastering these capabilities is essential for building reliable data lakes that provide the same consistency and reliability as traditional data warehouses.

Answer

Automatic indexing for all columns

Answer

Built-in support for real-time web socket streaming

Question 2

What is the primary function of a 'Personal Access Token' (PAT) in Databricks, and why is it considered a security risk if not managed properly?

Accepted Answer

It provides long-lived programmatic access to the API.. Personal Access Tokens (PATs) are used for programmatic access to Databricks REST APIs. They authenticate the user and inherit their permissions. The risk lies in their longevity; if an unexpired token is leaked, an attacker can impersonate the user without needing to re-authenticate via SSO. Therefore, limiting token duration and using service principals for automated tasks are crucial security measures to protect the platform.

Answer

It allows users to bypass multi-factor authentication.

Answer

It is intended for end-user dashboard access.

Answer

It encrypts data stored in the workspace.

Question 3

Refer to the exhibit. A Databricks administrator wants to restrict access to a specific SQL Alert. Based on the JSON policy, which statement accurately describes the current permission model for this alert?

Accepted Answer

The user can see the alert, but cannot trigger a manual refresh or modify the alert settings.. The exhibit displays an Access Control List (ACL) entry granting 'CAN_VIEW' permission to a specific user for a single alert resource. In Databricks, SQL Alerts are secured via the workspace-level permission model. Understanding these JSON-based policy definitions is essential for data engineers tasked with auditing governance and ensuring that sensitive monitoring data is only accessible to authorized personnel during security reviews.

Answer

The user is allowed to modify the alert's query criteria and notification frequency.

Answer

The user has sufficient permissions to delete the alert from the Databricks SQL workspace.

Answer

This JSON policy provides the user with global access to all alerts within the SQL warehouse.

Question 4

Your organization is ingesting sensitive PII data. You need to ensure that personal identifiers are masked during the ingestion process before they are stored in the Bronze layer of your Medallion architecture. What is the best practice for this?

Accepted Answer

Apply masking logic within the initial streaming ingestion transformation.. Implementing masking at the ingestion layer using Delta Live Tables (DLT) expectations or standard Spark transformations ensures that sensitive data is never written in plaintext to the Bronze layer. Applying transformations during the 'Acquisition' phase is a critical security practice, ensuring that governance requirements are met before data becomes available to analysts, thereby reducing the risk of accidental exposure and maintaining data privacy compliance across the pipeline.

Answer

Mask the data using Delta Lake column masking after the data reaches the Silver layer.

Answer

Configure Unity Catalog to mask columns only for specific users.

Answer

Use a post-ingestion job to delete PII columns.

Question 5

What is the primary benefit of the Medallion architecture in a Databricks Lakehouse?

Accepted Answer

It provides a clear progression of data quality and structure.. The Medallion architecture provides a clear, structured progression of data quality. By segregating data into Bronze (raw), Silver (cleaned), and Gold (refined) layers, organizations can maintain an immutable audit trail while enabling both technical teams and business analysts to consume the data at the appropriate level of abstraction. This structure simplifies data governance, incremental processing, and data quality management, which are fundamental to building a reliable Lakehouse.

Answer

It eliminates the need for data partitioning and Z-Ordering.

Answer

It forces all data to be stored in a star schema at all layers.

Answer

It automatically converts all incoming data to a structured format.

Question 6

Which metric should a data engineer prioritize when investigating a slow-running query in the Databricks SQL query history?

Accepted Answer

The total execution time and breakdown of the query stages.. The 'Total Time' metric is decomposed into wait time, compilation time, and execution time. Identifying the execution time bottleneck helps determine if the issue is compute-bound, I/O-bound, or due to slow data retrieval. This information is vital for deciding whether to optimize the query code, adjust partitioning, or scale the warehouse, ensuring that engineering efforts are targeted at the right performance issues to maximize impact.

Answer

The number of users logged into the workspace at the time.

Answer

The color of the query status light in the UI.

Answer

The name of the user who submitted the query.

Question 7

Refer to the exhibit. A Data Engineer is attempting to merge data into a table with these constraints defined. If the incoming batch contains rows that violate these rules, what is the default behavior of the Delta Lake engine during the merge operation?

Accepted Answer

The transaction fails entirely, and no changes are committed to the Delta table.. Delta Lake maintains strict ACID compliance. When a transaction violates defined CHECK or NOT NULL constraints, the engine throws an error, and the entire transaction is rolled back. This behavior prevents data corruption and ensures that only valid state transitions occur. This is essential for maintaining data integrity in complex ETL pipelines where maintaining a 'known good' state is preferred over partial updates or silent failures.

Answer

The engine silently ignores the invalid rows and completes the merge for valid records only.

Answer

The invalid rows are automatically routed to a hidden sidecar table for later review.

Answer

The engine automatically updates the invalid rows to the default column value.

Question 8

A Data Engineer is implementing a medallion architecture. Which THREE steps are critical for effectively implementing a high-quality 'Silver' layer from 'Bronze' data?

Accepted Answer

Enforce strict schema validation on incoming Bronze files.. The Silver layer is the 'single source of truth' for refined data. By applying strict schema enforcement, deduplication, and standardized data types, you ensure that analytical tools receive reliable, high-quality information. Proper implementation here prevents the 'garbage in, garbage out' scenario, allowing data scientists and analysts to focus on modeling and reporting rather than constant, redundant data cleaning tasks, significantly increasing organizational productivity and trust in data.

Answer

Apply business logic and complex transformations to create aggregate summary tables.

Answer

Convert all column names to uppercase to ensure case-insensitive consistency.

Question 9

You need to perform a deduplication task on a streaming source that includes late-arriving data. Which Delta Lake feature is best suited to manage this while ensuring efficient state cleanup?

Accepted Answer

Use the dropDuplicates() method with watermark settings to manage state.. The `withWatermark` and `dropDuplicates` combination is specifically designed for streaming deduplication. Watermarks tell the engine how long to wait for late-arriving data, allowing it to clear old state from memory. This is critical for high-volume streaming jobs where state growth would otherwise lead to out-of-memory errors or significant performance degradation, ensuring the job remains stable over long periods of execution.

Answer

Use a standard SQL DELETE query with a subquery to identify and remove duplicates.

Answer

Set the table property 'delta.enableChangeDataFeed' to true and filter on the change log.

Answer

Increase the 'spark.sql.shuffle.partitions' setting to ensure all duplicates land on the same node.

Question 10

Which of the following describes the correct behavior of Unity Catalog's 'Data Lineage' when used for security compliance?

Accepted Answer

It captures table-to-table data dependencies.. Data Lineage in Unity Catalog automatically captures the flow of data from source to target. This is invaluable for security compliance, as it allows administrators to trace sensitive data across the environment, identify which tables are derived from PII sources, and understand the impact of modifying or deleting specific tables. By visualizing these dependencies, teams can ensure that security policies are consistently applied throughout the data lifecycle.

Answer

It shows which users accessed the data.

Answer

It manually triggers an alert when PII is detected.

Answer

It permanently stores the raw data content.

Question 11

Refer to the exhibit. The engineer wants to replace only one specific partition in the 'orders' table. What is the best method in Databricks?

Accepted Answer

Enable dynamic partition overwrite mode and perform an INSERT OVERWRITE.. Using 'partitionOverwriteMode=dynamic' with a standard INSERT OVERWRITE operation is the correct way to replace a single partition without affecting the entire table. The default behavior is 'static', which overwrites the whole table. By setting the dynamic mode, the system identifies the partitions present in the incoming data and replaces only those, ensuring minimal impact and preventing accidental data loss across the entire dataset during the overwrite process.

Answer

Run a DELETE statement for the partition and then an APPEND.

Answer

Use the MERGE command to overwrite only the records in the specific partition.

Answer

Set 'spark.databricks.delta.retentionDurationCheck.enabled' to false.

Question 12

You are migrating a legacy ETL process to Delta Live Tables (DLT). You have an existing table defined with a complex transformation that involves a custom Python function using a third-party library. How should you structure this in DLT to ensure the function is available and correctly applied?

Accepted Answer

Define the function inside the same notebook but outside the 'dlt.table' function.. DLT pipelines require explicit management of dependencies. Using the 'dlt.table' decorator alongside proper library installation via cluster configuration or pip requirements ensures that Python functions are serialized and distributed to worker nodes. Understanding how DLT handles dependencies and decorators is critical for building reliable, production-grade pipelines that maintain parity with legacy logic while leveraging the automatic orchestration and quality management features inherent in the DLT framework.

Answer

Hardcode the library path using 'sys.path.append'.

Answer

You cannot use custom Python functions in DLT.

Answer

Use the 'spark.udf.register' method globally.

Question 13

Refer to the exhibit. You are using Auto Loader to ingest data with evolving schemas. After running the job for a week, you realize that new columns added to the source JSON are not being captured in the destination table. What must you add to the configuration?

Accepted Answer

Add 'cloudFiles.schemaEvolutionMode': 'addCol'.. By default, Auto Loader schema inference only detects the schema during the initial load. To capture schema evolution, you must explicitly enable 'cloudFiles.schemaEvolutionMode'. Without this parameter, Auto Loader ignores new fields to protect downstream consumers from breaking changes. Enabling 'addCol' allows the schema to expand dynamically, ensuring the target Delta table reflects the structure of the incoming data files as they arrive.

Answer

Add 'cloudFiles.schemaEvolutionMode': 'rescue'.

Answer

Add 'cloudFiles.maxFiles': '1000'.

Answer

Add 'cloudFiles.allowOverwrites': 'true'.

Question 14

When ingesting data using Auto Loader, what is the purpose of the 'cloudFiles.schemaLocation' parameter?

Accepted Answer

It stores metadata about the inferred schema and tracks evolution.. The schema location is vital for Auto Loader because it stores the inferred schema and tracks schema evolution history. By persisting this information in a managed location, Auto Loader can resume ingestion after a job restart without re-inferring the schema from scratch. This ensures consistency and prevents potential ingestion failures caused by schema drift when processing new files in a long-running stream.

Answer

It specifies the target directory where the processed Delta table data is stored.

Answer

It defines the temporary directory used for shuffling large datasets during joins.

Answer

It is used to cache the raw JSON files before they are parsed.

Question 15

You are migrating a legacy CSV-based ETL process to Databricks. The source CSV files contain inconsistent date formats. Which approach provides the most scalable way to handle these inconsistencies during the bronze-to-silver transformation?

Accepted Answer

Use the 'to_timestamp' function with an array of acceptable formats to parse the column.. Using Spark's `to_timestamp` with multiple format strings or a custom UDF is the standard, scalable way to handle format drift. By applying this logic in the Silver layer, you preserve the raw data in the Bronze layer while ensuring the refined data is standardized. This strategy follows the Medallion architecture pattern, allowing for lineage tracking and the ability to reprocess data if requirements change or better parsing logic is developed.

Answer

Convert all columns to string type to avoid parsing errors, and handle formatting in the visualization tool.

Answer

Use the 'from_unixtime' function with a hardcoded string format for all records.

Answer

Delete all rows with invalid date formats using a 'drop' expectation in a DLT pipeline.

Question 16

A data engineer needs to restrict access to personally identifiable information (PII) columns in a Unity Catalog table for a group of analysts. Which Unity Catalog feature should be used to enforce this policy while ensuring data remains queryable?

Accepted Answer

Define a column mask using SQL functions to redact data for the analyst group.. Dynamic views or Row-level security (RLS) and Column-level security (CLS) are essential for compliance. By utilizing SQL functions like current_user() within a defined mask, you ensure that PII is obscured based on user identity. This approach centralizes security within the data platform, preventing the need to duplicate datasets for different access levels, which significantly reduces the administrative burden and minimizes the risk of unauthorized data exposure in production environments.

Answer

Apply a static mask using a custom UDF during data ingestion.

Answer

Grant the analyst group SELECT access on the table and instruct them to cast PII columns to null.

Answer

Create separate physical tables for analysts containing only non-sensitive columns.

Question 17

A Data Engineer needs to verify that the column 'user_id' is unique in a critical Gold table. What is the most efficient, non-blocking way to perform this check in a production environment?

Accepted Answer

Define a primary key constraint in the Delta table definition.. Using a Delta constraint or a DLT expectation is the most efficient way to enforce uniqueness at the storage layer. Unlike a full table scan query, which is reactive and performance-intensive, native constraints are checked during the commit process, providing proactive quality assurance. This ensures that the table never enters an invalid state, which is vital for the integrity of downstream machine learning models and reporting applications.

Answer

Run a daily query: SELECT COUNT(user_id) - COUNT(DISTINCT user_id).

Answer

Use a custom UDF to check for duplicates inside a Spark streaming loop.

Answer

Perform a left-anti join with the previous day's data every time the pipeline runs.

Question 18

A Data Engineer is building a Delta Live Tables (DLT) pipeline to ingest raw JSON data. They need to ensure that records missing the required 'user_id' field are dropped while simultaneously capturing these discarded records in a separate table for auditing purposes. Which approach achieves this in DLT?

Accepted Answer

Define two separate tables in the pipeline, one filtering for valid records and one for invalid records using the NOT condition.. To handle data quality in DLT, the 'expect_violation_or_drop' constraint is not a standard clause. Instead, the 'expect_or_drop' constraint removes invalid records, but does not preserve them. The correct architectural pattern involves using a separate pipeline or query that filters for the inverse condition (where 'user_id' is null) and writes those records to an 'expect_all_fail' target table, maintaining strict lineage and auditing for schema non-compliance.

Answer

Apply the 'expect_or_drop' constraint and use a trigger to log dropped records.

Answer

Use the 'expect_or_fail' constraint to halt the pipeline and flag the error.

Answer

Configure a DLT pipeline to use 'expect_all_drop' to automatically split the data stream.

Question 19

A Data Engineer needs to monitor the health of a Delta Live Tables (DLT) pipeline. Which metric should they monitor to track the number of data quality violations over time?

Accepted Answer

expectations_violated_records. The 'expectations_violated_records' metric is specifically designed to track data quality issues in DLT pipelines. By integrating these metrics with Databricks SQL alerts or external tools like Grafana, engineers can gain visibility into data lineage and quality degradation. Monitoring these metrics is essential for maintaining pipeline reliability and ensuring that downstream data consumers receive accurate and validated data, preventing the propagation of corrupted data throughout the analytical ecosystem.

Answer

pipeline_latency_seconds

Answer

cluster_cpu_utilization

Answer

task_retry_count

Question 20

A data engineer is setting up a new Unity Catalog metastore. What is the primary purpose of the 'Metastore Admin' role?

Accepted Answer

To define the root storage location and manage top-level catalogs.. The Metastore Admin is the highest-privilege role in Unity Catalog, responsible for the initial configuration and top-level governance settings. This role is essential for establishing the security foundation of the platform, including catalog creation and cross-workspace access control. Proper management of this role is critical to prevent privilege escalation and ensure that only authorized personnel can define the organization's overall data governance strategy.

Answer

To create and manage compute clusters for all users.

Answer

To monitor and respond to daily system health alerts.

Answer

To approve all requests for new user sign-ups.