Free DATABRICKS-DATA-ENGINEER-ASSOCIATE Practice Test

The MERGE INTO command is specifically designed for "upsert" (update or insert) scenarios. It atomically modifies a target Delta table based on a join with a source dataset. Its core strength lies in its conditional logic: it can UPDATE rows that already exist (WHEN MATCHED) and INSERT new rows that do not (WHEN NOT MATCHED). This functionality is essential for maintaining data integrity by preventing the creation of duplicate records based on a specified key, a task that a simple INSERT INTO command cannot perform as it only appends new rows.

A. Changing a table's physical data location is a metadata operation handled by commands like ALTER TABLE ... SET LOCATION, not a DML operation like MERGE INTO.
B. Both MERGE INTO and INSERT INTO can operate on a target external Delta table. The table being external does not dictate the choice between these commands.
C. The lifecycle of the source table (i.e., whether it is deleted after the operation) is a separate workflow step and is irrelevant to the choice between MERGE and INSERT.
E. The source for a MERGE INTO operation can be any table, view, or subquery that produces a set of rows, not exclusively a Delta table.

1. Databricks SQL Language Reference

MERGE INTO: "Merges a set of updates

insertions

and deletions based on a source table into a target Delta table. ... The MERGE operation allows you to combine INSERT

UPDATE

and DELETE operations into a single atomic statement." This highlights the conditional logic used to manage records

which is the basis for preventing duplicates.

2. Databricks Documentation

"Upsert into a Delta table using merge": "You can upsert data from a source table

view

or DataFrame into a target Delta table using the MERGE SQL operation. Delta Lake supports inserts

updates

and deletes in MERGE... The most common use case is to upsert a set of new records into an existing Delta table." This directly addresses the use case of inserting new records while handling existing ones to avoid duplication.

3. Databricks Documentation

"Table deletes

updates

and merges": This document explains that MERGE is an atomic operation that simplifies mixed insert

update

and delete patterns. The example provided shows a typical SCD Type 1 implementation

which inherently relies on matching keys to prevent duplicate entities and update existing ones.

The Medallion Architecture organizes data into Bronze (raw), Silver (cleansed/enriched), and Gold (aggregated/business-level) tables. Gold tables contain highly refined and aggregated data, specifically prepared for business intelligence (BI), reporting, and analytics. A job that queries this aggregated data to populate a dashboard is a primary consumer of Gold tables, thus using them as its source. This aligns with the purpose of the Gold layer, which is to provide clean, business-centric data products for final consumption.

A. A job that enriches data by parsing its timestamps into a human-readable format: This is a typical enrichment task performed when moving data from the Bronze to the Silver layer, not consuming from the Gold layer.
B. A job that aggregates uncleaned data to create standard summary statistics: This job uses uncleaned (Bronze) data as its source. Gold tables are built from cleaned, validated Silver data, not the other way around.
C. A job that cleans data by removing malformatted records: This is a data cleaning process, which is characteristic of the workload that creates Silver tables from Bronze tables.
E. A job that ingests raw data from a streaming source into the Lakehouse: This describes the initial ingestion step that populates Bronze tables, the first layer of the Medallion Architecture.

1. Databricks Documentation

"What is the medallion lakehouse architecture?":

Reference for Correct Answer (D): In the section "Gold tables (curated business-level tables)

" the documentation states

"This layer provides data for analytics

machine learning

and production applications." Dashboards are a primary form of analytics application.

Reference for Incorrect Options (A

C

E): The documentation describes the Bronze layer as the target for raw data ingestion (E) and the Silver layer as the result of cleaning and enrichment (A

C) from the Bronze layer. The document states

"Data from the bronze layer is matched

merged

conformed

and cleansed to create the silver layer."

Auto Loader infers the schema by sampling the source data. JSON is a text-based format, which can lead to ambiguity in data types. For instance, a numeric value 123 can be represented as a number (123) or a string ("123") within different JSON records for the same field. When the schema inference process encounters such mixed types or quoted values (e.g., "true", "42.0") in its sample, it promotes the column's data type to string. This is the most flexible type to prevent data loss or parsing errors when inconsistencies exist in a text-based source file.

A. A type mismatch is a symptom or result of the schema inference process, not the underlying cause for why string was chosen.
C. This is factually incorrect. Auto Loader is designed to work with and infer a variety of complex data types, not just strings.
D. Null values are handled by schema inference and do not cause a column to be inferred as a string type. A column with integers and nulls would be inferred as an integer type.
E. This is factually incorrect. Schema inference and evolution are core, defining features of Auto Loader.

1. Databricks Documentation, "Configure schema inference and evolution in Auto Loader": This document explains the mechanism Auto Loader uses for schema inference: "To infer the schema, Auto Loader samples the first 50 GB or 1000 files that it discovers...". The behavior described in the question is a direct consequence of this sampling process acting on a potentially inconsistent text-based format like JSON.

2. Databricks Documentation, "Read and write JSON files": This page details how Apache Spark (the engine behind Auto Loader) handles JSON files. It notes that Spark infers the schema from the data. The documentation on data types implies that when different data types are present for the same field, Spark will choose a compatible type. For a mix of numbers and strings, the compatible type is string. The root cause is the text-based nature of JSON allowing this ambiguity.

3. Apache Spark Documentation, "Data Sources - JSON Files": The official Spark documentation (which Databricks is built upon) states, "Spark SQL can automatically infer the schema of a JSON dataset...". The inference logic will try to find a common type. If a field contains 1 in one record and "abc" in another, the inferred type for that field will be StringType. This behavior stems directly from JSON being a text format where such inconsistencies are possible.

The Databricks platform architecture is divided into a control plane and a data plane. The control plane contains the backend services that Databricks manages, including the web application, notebook commands, and management of jobs and clusters. Compute Orchestration is the core service within the control plane responsible for managing the lifecycle of compute resources. Unity Catalog is a centralized governance solution whose metastore and services also reside in the control plane, managing metadata and access control for data assets across the platform.

A. Virtual Machines: These are the fundamental compute resources that run in the data plane within the customer's cloud account, not the control plane.
C. Serverless Compute: The actual compute resources for serverless workloads run in a serverless data plane, separate from the control plane that orchestrates them.
D. Compute: This is a general term for the clusters and virtual machines where data processing occurs, which is the primary function of the data plane.

1. Databricks Documentation, "Databricks architecture overview": This document explicitly separates the architecture into two planes. It states, "The control plane includes the backend services that Databricks manages... Notebook commands and many other workspace configurations are stored in the control plane... The data plane is where your data is processed." This supports that orchestration logic (B) is in the control plane, while the actual compute resources (A, C, D) are in a data plane.

2. Databricks Documentation, "What is Unity Catalog?": This source clarifies the location of Unity Catalog. Under the section "The Unity Catalog object model," it states, "The Unity Catalog metastore... is a service managed by Databricks that resides in the control plane." This directly confirms that Unity Catalog (E) is a control plane component.

The DELETE FROM statement is the correct Data Manipulation Language (DML) command in SQL to remove rows from a Delta table. The WHERE clause is used to specify the condition for deletion. In this case, WHERE age > 25 correctly filters for the rows that need to be removed. This operation is atomic; it removes the specified rows and commits the change as a new version to the Delta table's transaction log, thus saving the updated table state.

A. The SELECT statement only queries and returns data; it does not perform any modification or deletion on the source table.
B. The UPDATE statement is used to modify the values within existing rows, not to remove them. The syntax is also incomplete without a SET clause.
D. The UPDATE statement modifies rows rather than deleting them, and it targets the incorrect set of rows (age <= 25).
E. This DELETE statement uses the incorrect condition (age <= 25), which would remove the rows that should be kept, not the ones specified. ---

1. Databricks SQL Language Reference

DELETE FROM: "Removes the rows that match a predicate." The documentation provides the syntax DELETE FROM tablename [WHERE predicate]

which directly matches the correct answer.

Source: Databricks Documentation > SQL reference > Data manipulation language > DELETE FROM.

2. Databricks Delta Lake Guide

"Table deletes

updates

and merges": "You can remove data that matches a predicate from a Delta table using the DELETE operation. DELETE removes the entire row and does not drop columns." This confirms DELETE is the appropriate operation for removing entire rows based on a condition.

Source: Databricks Documentation > Delta Lake > Delta Lake feature guide > Table deletes

updates

and merges.

The fundamental difference between a batch query and a streaming query in Apache Spark and Databricks is the entry point used to create the DataFrame. For batch processing on static data, spark.read is used. To process data incrementally from a continuously updating source like a Delta table, you must use the spark.readStream interface. This change signals to Spark to create a streaming DataFrame and execute the query as a continuous streaming job, rather than a one-time batch job. The format remains "delta" as Delta Lake natively supports being a streaming source.

A. Replace predict with a stream-friendly prediction function: The predict function is a transformation applied after reading the data. The primary issue is how the data is read (batch vs. stream), not how it is processed.
B. Replace schema(schema) with option("maxFilesPerTrigger", 1): maxFilesPerTrigger is a valid streaming option to control micro-batch size, but it does not initiate the stream itself. The core change from batch to stream is required first.
C. Replace "transactions" with the path to the location of the Delta table: Reading a Delta table by its registered name using .table("transactions") is a valid and supported method for streaming queries, just as using .load("/path/...") is. This change is not necessary.
D. Replace format("delta") with format("stream"): The format specifies the data source type. The source is a Delta Lake table, so the format must be "delta". There is no format("stream").

1. Databricks Documentation

"Table streaming reads and writes": This official guide explicitly states the method for reading a Delta table as a stream.

Quote: "To read a Delta table as a stream source

use spark.readStream." The documentation provides examples such as spark.readStream.format("delta").load("") and spark.readStream.table("").

Reference: Databricks Documentation > Delta Lake guide > Table streaming reads and writes.

2. Apache Spark Documentation

"Structured Streaming Programming Guide": This is the foundational documentation for Spark's streaming engine.

Quote: "Streaming DataFrames can be created through the DataStreamReader interface returned by spark.readStream."

Reference: Apache Spark Documentation > Structured Streaming Programming Guide > Quick Example > Creating streaming DataFrames and streaming Datasets.

The Medallion architecture is a data design pattern used to logically organize data in a lakehouse. Data progresses through Bronze, Silver, and Gold layers, with each layer representing a higher quality of data. The Bronze layer contains raw, unvalidated data ingested directly from source systems. The Silver layer's primary purpose is to take this raw data and apply cleaning, filtering, and enrichment rules. This creates a validated, conformed, and more reliable dataset that serves as an enterprise view of key business entities. Therefore, Silver tables always represent a more refined and cleaner version of the data found in Bronze tables.

A. This statement is the inverse of the Medallion architecture's principle. Data quality and refinement increase as data moves from Bronze to Silver.
B. Business-level aggregates are characteristic of the Gold layer, which is prepared for reporting and analytics, not the Silver layer, which focuses on cleaning and conforming data.
C. Silver tables do not necessarily contain more data. Operations like filtering out bad records or duplicates can reduce the number of rows compared to the Bronze layer.
E. Silver tables do not necessarily contain less data. Enrichment processes, such as joining with other tables, can add columns and potentially increase the overall data volume.

1. Databricks Documentation

"What is the medallion lakehouse architecture?":

Section: Bronze tables (raw data): Describes this layer as containing "raw data ingested from various sources" and being the "first stop for data."

Section: Silver tables (filtered

cleaned

augmented data): Explicitly states

"The data in the silver layer is more refined than the bronze layer... Common operations in this layer include... data quality and cleaning operations." This directly supports the correct answer (D) and refutes (A). It also distinguishes its role from aggregation

which is a primary function of the Gold layer

refuting (B).

2. Databricks Blog

"A Gentle Introduction to the Medallion Architecture":

Section: The Silver Layer – Cleansed and Conformed Data: "The goal of this layer is to provide a more reliable

enriched

and queryable source of data for a variety of use cases... Data in the Silver layer is considered a reliable source of truth." This reinforces that Silver tables are a cleaner

more refined version of Bronze tables.

3. Databricks eBook

"The Big Book of Data Engineering":

Chapter 2: Data Engineering Architectures

Section: The Medallion Architecture: This section details the flow from raw (Bronze) to validated (Silver) to aggregated (Gold). It clarifies that Silver tables are the result of applying "data quality rules

data cleansing

and enrichment" to the raw Bronze data. This confirms the refinement process and distinguishes the Silver layer's purpose from the Gold layer's aggregation focus.

The core problem described is data inconsistency between teams due to siloed data architectures. A data lakehouse directly addresses this by unifying the data lake (typically used by data engineers for raw data and ETL) and the data warehouse (used by analysts for BI and reporting) into a single platform. This consolidation eliminates redundant data copies and synchronization issues. By having both the data engineering and data analysis teams operate on the same, governed data repository, the lakehouse establishes a single source of truth, ensuring that reports are consistent and reliable across the organization.

A. Autoscaling is a compute performance and cost-optimization feature; it does not address the root cause of data inconsistency between separate architectural silos.
C. A data lakehouse is a technical architecture. It does not mandate or cause organizational changes like departmental restructuring.
D. While a unified platform enhances collaboration, the primary solution to report discrepancies is the unified data source, not the collaborative tooling itself.
E. Faster response to ad-hoc requests is a potential secondary benefit of a simplified architecture, but the direct solution to inconsistent reports is establishing a single source of truth. ---

1. Databricks Documentation

"What is a data lakehouse?": "A data lakehouse is a data management architecture that combines the flexibility

cost-efficiency

and scale of data lakes with the data management and ACID transactions of data warehouses... By building a single system

a data lakehouse eliminates the data silos that traditionally separate analytics

BI

data science

and machine learning." This directly supports the idea of eliminating silos to create a single system.

2. Armbrust

M.

Ghodsi

A.

Xin

R.

& Zaharia

M. et al. (2021). "Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics." CIDR 2021.: The paper states

"We argue that the solution is to implement a data warehouse's functionality directly on the data lake... This new design

the lakehouse

eliminates the main problems with the two-tier architecture [data lake + data warehouse]: the data is not split into two different systems

reducing staleness and management overhead

and there is direct access to the data for advanced analytics." (Section 1: Introduction). This highlights the elimination of the two-system problem

which is the root of the inconsistency.

3. Databricks Blog

"What Is a Data Lakehouse?": "The data lakehouse is what you get when you implement data warehouse structures and data management features on the low-cost storage used for data lakes. It’s a single platform to house all of your data... This eliminates the need to move or copy data for different workloads and breaks down data silos

providing a single source of truth." (Section: "What is a data lakehouse?"). This explicitly links breaking down silos with providing a single source of truth.

Spark SQL provides a rich set of array functions specifically designed to handle complex and nested data structures. Data ingested from semi-structured sources like JSON files often contains arrays within the schema. Functions such as explode, posexplode, size, arraycontains, and transform enable data engineers to effectively access, flatten, and manipulate the elements within these arrays. This capability is crucial for transforming hierarchical, nested data into a relational format suitable for analysis and further processing.

A. Array functions operate on elements within a single array column, and these elements must share the same data type.
B. This describes the functionality of window functions (e.g., PARTITION BY), which operate over a group of rows, not array columns.
C. This is the purpose of date and time functions (e.g., window, datetrunc), which are distinct from array manipulation functions.
E. This describes a procedural programming concept, not a feature of Spark SQL's built-in functions for manipulating array-type data within a DataFrame. ---

1. Databricks Documentation

"JSON files": This document explains how Apache Spark can automatically infer the schema of a JSON dataset

including nested structures that are parsed into ArrayType and StructType columns. It states

"Spark can infer the schema from a JSON dataset and load it as a DataFrame. ... This capability is particularly useful with semi-structured data that contains complex

nested fields." This directly supports the use case of handling complex data from JSON.

Source: Databricks Documentation > Data management > Data sources > JSON files.

2. Databricks Documentation

"Array functions": The official SQL language reference lists and describes the functions available for manipulating array columns. Functions like explode

which creates a new row for each element in the given array

are fundamental for working with nested data.

Source: Databricks Documentation > SQL reference > Built-in functions > Array functions.

3. Databricks Documentation

"explode" table-valued generator function: The documentation for explode explicitly states its purpose: "Returns a set of rows by un-nesting collection." This is a core operation for handling nested array data ingested from sources like JSON.

Source: Databricks Documentation > SQL reference > Language manual > Table-valued generator functions (TVGF) > explode.

Cluster pools are designed to reduce cluster start-up and auto-scaling times by maintaining a set of idle, ready-to-use instances. When a cluster is created from a pool, it uses these pre-warmed instances instead of waiting for the cloud provider to provision new ones. This mechanism is ideal for workloads that are time-sensitive and need to start executing with minimal delay. Refreshing an automated report "as quickly as possible" directly aligns with this primary benefit, as it minimizes the latency associated with acquiring compute resources for the job.

B: Reproducibility is ensured by managing code, library dependencies, and specific Databricks Runtime versions, not by using cluster pools. C: Testing for errors is a development workflow; while a pool can speed up the start of a test cluster, it does not inherently aid in error identification. D: Version control for collaborative projects is managed through tools like Git, integrated via Databricks Repos, which is unrelated to cluster pools. E: Making a report runnable for stakeholders is managed through job permissions, access control lists (ACLs), and sharing mechanisms, not infrastructure optimization.

1. Databricks Documentation - Cluster pools:

Section: "What are cluster pools?"

Content: "A cluster pool is a set of idle

ready-to-use instances. When a cluster attached to a pool is created

it uses drivers and worker nodes from the pool. ... The primary benefit of using a cluster pool is to reduce cluster start and auto-scaling times." This directly supports the rationale for selecting option A

as minimizing start-up time is crucial for quick report refreshes.

2. Databricks Documentation - Best practices: Cluster pools:

Section: "When to use pools"

Content: "Pools reduce cluster start and auto-scaling times. Use pools with workloads that have tight performance requirements or workloads that are slow to start up." This reinforces that pools are intended for performance-critical scenarios where speed is a primary concern.

3. Databricks Documentation - Manage cluster configuration:

Section: "Cluster configuration best practices"

Content: This section details how to ensure consistency and reproducibility through cluster policies and configurations

highlighting that these are the correct tools for reproducibility (Option B)

not cluster pools.