Databricks-DE-Assoc Practice Test 3 — 10 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.