Databricks-DE-Assoc Practice Exam 2 — 20 Questions

Question 1

A data engineer is implementing a Type 2 slowly changing dimension in Delta Lake for a customers table. The table has columns customer_id, name, address, effective_date, end_date, and is_current. When a customer's address changes, the engineer wants to expire the existing current row and insert a new current row in a single atomic operation. Which Delta Lake feature should the engineer use?

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

Accepted Answer

MERGE INTO the customers table using a source of changed records, with WHEN MATCHED UPDATE to set end_date and is_current on the existing row and WHEN NOT MATCHED INSERT to add the new version. A Type 2 dimension change requires atomically expiring the current row and inserting a new current row. Delta Lake MERGE INTO expresses both actions in one statement, and its ACID transaction guarantees that readers see either the old state or the new state, never a partial update. INSERT OVERWRITE, separate DELETE and INSERT, and OPTIMIZE do not provide the same incremental, atomic behavior.

Answer

Use OPTIMIZE with ZORDER BY customer_id to reorganize the table so that new versions are placed next to old versions

Answer

Use INSERT OVERWRITE to replace the entire customers table with a new snapshot that includes the expired and new rows

Answer

Use DELETE followed by INSERT in two separate statements to expire the old row and add the new one

Question 2

Which action is recommended to resolve a scenario where a Databricks Job is failing due to excessive metadata operations on a Delta table with millions of files?

Accepted Answer

Run OPTIMIZE to consolidate files.. When a Delta table contains millions of small files, the transaction log and metadata operations become a bottleneck. The 'list' operations required to build the state of the table consume significant time and driver memory. Implementing partition pruning or using Delta Lake's table property 'delta.enableChangeDataFeed' are not the primary solutions here. Instead, running OPTIMIZE to consolidate files is the standard way to reduce metadata overhead and improve table performance.

Answer

Increase the driver node instance size.

Answer

Disable the Delta transaction log.

Answer

Add more worker nodes to the cluster.

Question 3

A team is transitioning to the Databricks Intelligence Platform. Which TWO actions are required to successfully register a table in Unity Catalog using the three-level namespace?

Accepted Answer

Grant the 'USE CATALOG' privilege on the target catalog.. The three-level namespace (catalog.schema.table) is a fundamental feature of Unity Catalog. To register a table, an engineer must have sufficient privileges in the specific catalog and schema, and the table must be created using the correct fully qualified path. Understanding this structure is crucial because it enables unified data discovery and governance across different environments, preventing namespace collisions and allowing for standardized cross-database operations in the platform.

Answer

Configure a local Hive Metastore on the cluster.

Answer

Manually update the cluster's Spark configuration to point to the metastore URL.

Answer

Set the default database to 'default' before executing the CREATE statement.

Question 4

A team wants to ensure that code changes to their Databricks notebooks are reviewed before being deployed to production. They use a Git repository and Databricks Repos. Which practice should they implement in their CI/CD process?

Accepted Answer

Require pull requests with at least one approval before merging into the main branch, and trigger production deployment only after the merge.. Enforcing pull requests with approvals through branch protection rules ensures that changes are reviewed before merging. Deploying to production only after the merge ties deployment to reviewed code. This practice is essential for maintaining code quality and auditability in a CI/CD pipeline.

Answer

Allow developers to commit directly to the main branch and deploy automatically to production on every commit.

Answer

Use Databricks Repos to create a separate repository for each developer and merge changes manually in the production workspace.

Answer

Configure the production workspace to allow only workspace admins to edit notebooks, and have developers request admin assistance for each change.

Question 5

A data engineer maintains a Delta table where each row represents a customer record, and updates arrive continuously as change data capture events. The engineer needs to apply inserts, updates, and deletes from a staging table into the target table in a single atomic operation, matching records on customer_id. Which Delta Lake operation should be used?

Accepted Answer

MERGE INTO the target table using the staging table as the source, with matched update/delete clauses and a not-matched insert clause.. MERGE INTO is the Delta Lake primitive designed for upsert and delete semantics in one atomic transaction. By matching on customer_id, the engineer can update or delete matched rows and insert new ones from the staging table, applying CDC events idempotently. This avoids data gaps, prevents duplicate versions, and keeps the target table representing current state for downstream consumers.

Answer

Run a DELETE followed by an INSERT on the target table inside the same notebook cell.

Answer

Append the staging rows to the target table and deduplicate later with a window function during reads.

Answer

Use INSERT OVERWRITE to replace all partitions touched by the staging table with the staged rows.

Question 6

A data engineer needs to ingest data from a legacy SQL Server database into a Delta Lake bronze table. The ingestion must be performant and support parallel reads from the source table. What is the best practice for configuring the JDBC connection in this scenario?

Accepted Answer

Configure 'partitionColumn', 'lowerBound', 'upperBound', and 'numPartitions'.. Parallelizing JDBC reads is essential for moving large datasets from relational databases to Databricks. By providing partitioning columns and boundaries, Spark can spawn multiple executors to read different segments of the data simultaneously. This significantly reduces the time required for the initial load and improves the overall throughput of the ingestion pipeline.

Answer

Use a single executor to ensure data consistency during the read.

Answer

Rely on the default JDBC settings for automatic parallelization.

Answer

Use the COPY INTO command to read directly from the JDBC source.

Question 7

Which security feature in Databricks allows administrators to mask sensitive data, such as email addresses or social security numbers, in query results based on user-defined functions?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking allows you to define a masking function that hides or transforms data based on the user's role. By applying these functions to specific columns in a view or table, sensitive information is obscured in the results returned to the user, providing an effective way to maintain compliance with data privacy regulations like GDPR or HIPAA.

Answer

Row-Level Security

Answer

Table ACLs

Answer

Workspace-level isolation

Question 8

A data engineering team is building a medallion architecture in Databricks. In the Silver layer, streaming data from Kafka must be cleaned, deduplicated, and written into a Delta table. Which Structured Streaming output mode should the engineer select to ensure append-only storage of fully processed, stateful deduplicated records?

Accepted Answer

Append output mode. When performing stateful operations such as deduplication with dropDuplicates() on a streaming DataFrame, Structured Streaming requires the output mode to be set to append. This mode allows newly emitted unique rows to be written continuously to the target Delta table without requiring complete table rewrites, fitting the streaming append-only nature of the Silver medallion layer.

Answer

Complete output mode

Answer

Update output mode

Answer

Overwrite output mode

Question 9

A data engineering team needs to ingest millions of small JSON files from an S3 bucket into a Delta Lake table. The solution must provide incremental loading, support schema evolution, and automatically scale to handle increasing file volumes without manual tracking of processed files. Which tool is best suited for this requirement?

Accepted Answer

Auto Loader using the cloudFiles source in Structured Streaming.. Auto Loader is the recommended tool for incremental ingestion from cloud storage. It scales to millions of files using either directory listing or file notifications. Unlike standard Spark sources, it tracks processed files in a checkpoint, ensuring exactly-once semantics. This automation reduces operational overhead when managing unpredictable data volumes and evolving schema structures in production environments.

Answer

The standard Apache Spark DataFrame reader using the .load() method.

Answer

The COPY INTO SQL command with the mergeSchema option enabled.

Answer

A Python loop that iterates through filenames and uses INSERT INTO.

Question 10

Refer to the exhibit. A data engineer is attempting to ingest JSON data into a Delta table. Based on the error log, what is the most appropriate transformation step to implement before loading this data into the production table?

Accepted Answer

Apply a 'coalesce' function or a 'filter' transformation on the DataFrame before writing to the Delta table.. The exhibit shows a failure due to null values in a required field during ingestion. In production pipelines, failing at the ingestion layer stops data flow. Implementing a transformation step to handle nulls, either by providing default values or filtering, ensures the pipeline is resilient. This is a core Data Engineering principle: handle dirty source data before it reaches the target, preventing ingestion failures.

Answer

Use the 'DROP TABLE' command to clear the target and recreate it with nullable constraints.

Answer

Modify the target table schema using 'ALTER TABLE' to change the column to nullable.

Answer

Set the 'spark.sql.execution.nullValue' configuration to 'ignore' for the session.

Question 11

A junior data engineer writes a PySpark transformation that reads a Parquet dataset, filters out inactive users, and appends the resulting DataFrame to an existing Delta Lake table. However, the data engineer notices duplicate records appearing in the target table after multiple runs. Which technique should be implemented to ensure idempotency?

Accepted Answer

Implement a Delta Lake MERGE operation using a unique identifier to conditionally insert or update records.. Using Delta Lake's MERGE INTO operation allows data engineers to perform upserts by matching incoming records against existing table rows using a unique business key. This prevents duplicates during repeated pipeline executions, guaranteeing idempotency. Traditional append operations simply add records without checking for prior existence, which is a common root cause of duplicate data issues in incremental batch pipelines.

Answer

Use DataFrameWriter's mode("overwrite") with replaceWhere option matching the updated partition keys.

Answer

Increase the shuffle partition count using spark.sql("SET spark.sql.shuffle.partitions = 200").

Answer

Execute the VACUUM command immediately after every write operation completes.

Question 12

A data engineer is working on a Bronze-to-Silver transformation. Which THREE of the following practices are recommended to optimize performance and data quality during this stage?

Accepted Answer

Apply data quality checks to filter out or quarantine malformed records before writing to Silver.. Effective Bronze-to-Silver transformations are the foundation of a robust Lakehouse. By combining data validation, efficient partitioning, and incremental processing, engineers can minimize resource consumption while ensuring the data is reliable. These practices ensure the pipeline remains performant as volume scales and that the downstream Gold tables contain clean, query-optimized data, which is critical for business intelligence and data science use cases.

Answer

Always use 'SELECT *' to ensure the entire schema is captured in the Silver table.

Answer

Perform heavy joins and aggregations directly on the Bronze table.

Question 13

A data engineering team needs to ingest streaming data from Kafka into a Delta table while maintaining exactly-once processing guarantees and low latency. Which Databricks Intelligence Platform feature should they utilize to build this streaming pipeline declaratively?

Accepted Answer

Delta Live Tables. Delta Live Tables (DLT) is a declarative framework that simplifies ETL pipeline development. By defining data sources and target tables using SQL or Python, DLT manages infrastructure deployment, task orchestration, and error handling automatically, while leveraging structured streaming under the hood for low-latency, exactly-once ingestion.

Answer

Databricks Job clusters with manual spark-submit scripts

Answer

Databricks SQL query history

Answer

Unity Catalog lineage graphs

Question 14

A data engineer needs to share a subset of a table with an external partner who does not have access to the internal Databricks workspace. What is the most appropriate method to achieve this?

Accepted Answer

Use Delta Sharing to share a table.. Delta Sharing is the secure, open-standard solution for sharing data externally. It allows the recipient to access data directly from cloud storage without needing to provision an account in the provider's Databricks workspace. This process is secure because it uses short-lived tokens and does not require complex credential management, ensuring that data stays governed and that the provider retains control over what is being shared.

Answer

Create a service principal with restricted access.

Answer

Export data to CSV and email it.

Answer

Grant the partner access to a shared S3 bucket.

Question 15

A data engineer needs to automate the ingestion and transformation of data within Databricks with minimal manual intervention. Which feature is most appropriate for orchestrating these data workflows?

Accepted Answer

Databricks Workflows. Databricks Workflows is a fully managed orchestration service that allows users to create, schedule, and monitor data pipelines. It is essential because it integrates seamlessly with the platform, allowing engineers to chain together notebooks, SQL queries, and Delta Live Tables pipelines into complex, multi-step workflows. This automation reduces manual toil and improves the reliability of data production environments in the Lakehouse.

Answer

Delta Live Tables (DLT)

Answer

Unity Catalog UI

Answer

Databricks SQL Warehouse

Question 16

When a Data Engineer uses a 'Repair and Rerun' functionality on a failed Databricks Job, what happens?

Accepted Answer

It restarts from the failed task.. Repair and Rerun is a critical feature for managing complex DAG workflows. It allows engineers to restart a job from the specific failed task, inheriting the successful results of prior tasks. This saves significant time and compute costs by avoiding redundant processing of already completed data, which is essential for maintaining efficient pipelines and preventing unnecessary data re-computation in production environments.

Answer

It deletes all previous successful task logs.

Answer

It resets all task statuses to 'Pending'.

Answer

It requires a new cluster to be provisioned.

Question 17

Which identity management approach is mandatory for the implementation of Unity Catalog within a Databricks environment?

Accepted Answer

Account-level identities. Unity Catalog requires users to be managed at the account level, rather than at the individual workspace level. This synchronization ensures that permissions and identities are consistent across all workspaces in the account, which is a fundamental requirement for the unified governance model that Unity Catalog provides to the Databricks platform.

Answer

Workspace-local users

Answer

Local SQL-only credentials

Answer

Personal access tokens for every user

Question 18

A data engineer needs to perform an upsert on a target Delta table using a source DataFrame. Which operation provides the most robust mechanism to handle duplicates and updates in a single pass?

Accepted Answer

Using the 'MERGE INTO' command.. The MERGE operation is the standard industry practice for upsert patterns in Delta Lake. It allows for atomic updates, inserts, and deletions in a single transaction. This prevents partial writes and maintains the integrity of the data stream, which is crucial in production ELT workflows where data often arrives out of order or contains overlapping records that must be reconciled.

Answer

Using 'INSERT INTO' combined with a 'DELETE' operation.

Answer

Using 'overwrite' mode in the DataFrameWriter.

Answer

Using 'append' mode with custom filter logic.

Question 19

Which Databricks compute resource is specifically optimized for running BI dashboards and SQL queries?

Accepted Answer

SQL Warehouse. Databricks SQL Warehouses are compute environments designed to deliver high-performance SQL execution, making them ideal for BI tools and dashboarding. Understanding the difference between SQL Warehouses and traditional data engineering clusters is important, as it helps engineers choose the right compute resource to match the workload, ensuring better query performance and cost efficiency for end-users who need to interact with data via SQL.

Answer

All-Purpose Cluster

Answer

Job Cluster

Answer

Delta Live Tables Pipeline

Question 20

An organization is adopting the Databricks Intelligence Platform and wants to leverage Mosaic AI for building custom machine learning models. Which feature allows data engineers to track machine learning experiments, log parameters, and manage model artifacts reliably?

Accepted Answer

MLflow. MLflow is the open-source platform integrated natively into Databricks Mosaic AI for managing the end-to-end machine learning lifecycle. It provides comprehensive tracking of experiments, parameters, metrics, and registered model versions, ensuring reproducibility and smooth transition from development to production environments within the data lakehouse architecture.

Answer

Databricks SQL dashboards

Answer

Unity Catalog volumes

Answer

Delta Live Tables