DATABRICKS-DATA-ENGINEER-ASSOCIATE Practice Questions

The MERGE INTO command is used to perform upserts, which are a combination of insertions and

updates, based on a source table into a target Delta table1. The MERGE INTO command can handle

scenarios where the target table cannot contain duplicate records, such as when there is a primary

key or a unique constraint on the target table. The MERGE INTO command can match the source and

target rows based on a merge condition and perform different actions depending on whether the

rows are matched or not. For example, the MERGE INTO command can update the existing target

rows with the new source values, insert the new source rows that do not exist in the target table, or

delete the target rows that do not exist in the source table1.

The INSERT INTO command is used to append new rows to an existing table or create a new table

from a query result2. The INSERT INTO command does not perform any updates or deletions on the

existing target table rows. The INSERT INTO command can handle scenarios where the location of the

data needs to be changed, such as when the data needs to be moved from one table to another, or

when the data needs to be partitioned by a certain column2. The INSERT INTO command can also

handle scenarios where the target table is an external table, such as when the data is stored in an

external storage system like Amazon S3 or Azure Blob Storage3. The INSERT INTO command can also

handle scenarios where the source table can be deleted, such as when the source table is a

temporary table or a view4. The INSERT INTO command can also handle scenarios where the source

is not a Delta table, such as when the source is a Parquet, CSV, JSON, or Avro file5.

Reference:

1: MERGE INTO | Databricks on AWS

2: [INSERT INTO | Databricks on AWS]

3: [External tables | Databricks on AWS]

4: [Temporary views | Databricks on AWS]

5: [Data sources | Databricks on AWS]

A Gold table is a table that contains highly refined and aggregated data that powers analytics,

machine learning, and production applications. It represents data that has been transformed into

knowledge, rather than just information. A Gold table is typically the final output of a medallion

lakehouse architecture, where data flows from Bronze to Silver to Gold tables, with each layer

improving the structure and quality of data. A job that queries aggregated data designed to feed into

a dashboard is an example of a data workload that will utilize a Gold table as its source, as it requires

data that is ready for consumption and analysis. The other options are either data workloads that will

use a Bronze or Silver table as their source, or data workloads that will produce a Gold table as their

output. Reference: Databricks Documentation - What is the medallion lakehouse

architecture?, Databricks Documentation - What is a Medallion Architecture?, K21Academy - Delta

Lake Architecture & Azure Databricks Workspace.

JSON data is a text-based format that represents data as a collection of name-value pairs. By default,

when Auto Loader infers the schema of JSON data, it treats all columns as strings. This is because

JSON data can have varying data types for the same column across different files or records, and Auto

Loader does not attempt to reconcile these differences. For example, a column named “age” may

have integer values in some files, but string values in others. To avoid data loss or errors, Auto Loader

infers the column as a string type. However, Auto Loader also provides an option to infer more

precise column types based on the sample data. This option is called cloudFiles.inferColumnTypes

and it can be set to true or false. When set to true, Auto Loader tries to infer the exact data types of

the columns, such as integers, floats, booleans, or nested structures. When set to false, Auto Loader

infers all columns as strings. The default value of this option is false. Reference: Configure schema

inference and evolution in Auto Loader, Schema inference with auto loader (non-DLT and DLT), Using

and Abusing Auto Loader’s Inferred Schema, Explicit path to data or a defined schema required for

Auto loader.

:

The DELETE command in Delta Lake allows you to remove data that matches a predicate from a

Delta table. This command will delete all the rows where the value in the column age is greater than

25 from the existing Delta table my_table and save the updated table. The other options are either

incorrect or do not achieve the desired result. Option A will only select the rows that match the

predicate, but not delete them. Option B will update the rows that match the predicate, but not

delete them. Option D will update the rows that do not match the predicate, but not delete them.

Option E will delete the rows that do not match the predicate, which is the opposite of what we

want. Reference: Table deletes, updates, and merges — Delta Lake Documentation

To read from a stream source, the data engineer needs to use the spark.readStream method instead

of the spark.read method. The spark.readStream method returns a DataStreamReader object that

can be used to specify the details of the input source, such as the format, the schema, the path, and

the options. The spark.read method is only suitable for batch processing, not streaming processing.

The other changes are not necessary or correct for reading from a stream

source. Reference: Structured Streaming Programming Guide, Read a stream, Databricks Data

Sources

In a medallion architecture, a common data design pattern for lakehouses, data flows from Bronze

to Silver to Gold layer tables, with each layer progressively improving the structure and quality of

data. Bronze tables store raw data ingested from various sources, while Silver tables apply minimal

transformations and cleansing to create an enterprise view of the data. Silver tables can also join and

enrich data from different Bronze tables to provide a more complete and consistent view of the data.

Therefore, option D is the correct answer, as Silver tables contain a more refined and cleaner view of

data than Bronze tables. Option A is incorrect, as it is the opposite of the correct answer. Option B is

incorrect, as Silver tables do not necessarily contain aggregates, but can also store detailed records.

Option C is incorrect, as Silver tables may contain less data than Bronze tables, depending on the

transformations and cleansing applied. Option E is incorrect, as Silver tables may contain more data

than Bronze tables, depending on the joins and enrichments applied. Reference: What is a Medallion

Architecture?, Transforming Bronze Tables in Silver Tables, What is the medallion lakehouse

architecture?

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,

enabling business intelligence (BI) and machine learning (ML) on all data12. By using a data

lakehouse, both the data analysis and data engineering teams can access the same data sources and

formats, ensuring data consistency and quality across their reports. A data lakehouse also supports

schema enforcement and evolution, data validation, and time travel to old table versions, which can

help resolve data conflicts and errors1. Reference: 1: What is a Data Lakehouse? - Databricks 2: What

is a data lakehouse? | IBM

The array functions from Spark SQL are a subset of the collection functions that operate on array

columns1. They provide an ability to work with complex, nested data ingested from JSON files or

other sources2. For example, the explode function can be used to transform an array column into

multiple rows, one for each element in the array3. The array_contains function can be used to check

if a value is present in an array column4. The array_join function can be used to concatenate all

elements of an array column with a delimiter. These functions can be useful for processing JSON data

that may have nested arrays or objects. Reference: 1: Spark SQL, Built-in Functions - Apache

Spark 2: Spark SQL Array Functions Complete List - Spark By Examples 3: Spark SQL Array Functions -

Syntax and Examples - DWgeek.com 4: Spark SQL, Built-in Functions - Apache Spark : Spark SQL,

Built-in Functions - Apache Spark : [Working with Nested Data Using Higher Order Functions in SQL

on Databricks - The Databricks Blog]

Databricks cluster pools are a set of idle, ready-to-use instances that can reduce cluster start and

auto-scaling times. This is useful for scenarios where a data team needs to run an automated report

as quickly as possible, without waiting for the cluster to launch or scale up. Cluster pools can also

help save costs by reusing idle instances across different clusters and avoiding DBU charges for idle

instances in the pool. Reference: Best practices: pools | Databricks on AWS, Best practices: pools -

Azure Databricks | Microsoft Learn, Best practices: pools | Databricks on Google Cloud