Databricks-Certified-Professional-Data-Engineer Databricks Certified Data Engineer Professional Exam Questions and Answers

Explanation

here is the additional documentation for your review.

https://docs.databricks.com/spark/latest/spark-sql/language-manual/delta-merge-into.html

1.MERGE INTO target_table_name [target_alias]

2. USING source_table_reference [source_alias]

3. ON merge_condition

4. [ WHEN MATCHED [ AND condition ] THEN matched_action ] [...]

5. [ WHEN NOT MATCHED [ AND condition ] THEN not_matched_action ] [...]

6.

7.matched_action

8. { DELETE |

9. UPDATE SET * |

10. UPDATE SET { column1 = value1 } [, ...] }

11.

12.not_matched_action

13. { INSERT * |

14. INSERT (column1 [, ...] ) VALUES (value1 [, ...])

Explanation

The answer is,

Select Workflows UI and Delta live tables tab, under task type select Delta live tables pipeline and select the notebook.

Create a pipeline

To create a new pipeline using the Delta Live Tables notebook:

1.Click Workflows in the sidebar, click the Delta Live Tables tab, and click Create Pipeline.

2.Give the pipeline a name and click to select a notebook.

3.Optionally enter a storage location for output data from the pipeline. The system uses a de-fault location if you leave Storage Location empty.

4.Select Triggered for Pipeline Mode.

5.Click Create.

The system displays the Pipeline Details page after you click Create. You can also access your pipeline by clicking the pipeline name in the Delta Live Tables tab.

The answer is 2,

Count(DISTINCT *) removes rows with any column with a NULL value

The answer is, The Job cluster is best suited for this purpose.

Since you don't need to interact with the notebook during the execution especially when it's a scheduled job, job cluster makes sense. Using an all-purpose cluster can be twice as expensive as a job cluster.

FYI,

When you run a job scheduler with option of creating a new cluster when the job is complete it terminates the cluster. You cannot restart a job cluster.

Explanation

The answer is SHALLOW CLONE

SHALLOW CLONE If you wish to create a copy of a table quickly to test out applying changes without the risk of modifying the current table, SHALLOW CLONE can be a good option. Shallow clones just copy the Delta transaction logs, meaning that the data doesn't move so it can be very quick.

1.CREATE OR REPLACE TABLE {new_table_name} SHALLOW CLONE {source_table_name}|[LOCATION path]

DEEP CLONE fully copies data and metadata from a source table to a target. This copy occurs incre-mentally, so executing this command again can sync changes from the source to the target location. It copies all of the data and transaction logs this can take a long time based on the size of the table.

1.CREATE OR REPLACE TABLE {new_table_name} DEEP CLONE {source_table_name}|[LOCATION path]

Explanation

The answer is, Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.

Delta live tables support three types of expectations to fix bad data in DLT pipelines

Review below example code to examine these expectations,

Diagram Description automatically generated with medium confidence

Explanation

Alerts supports multiple destinations, email is the default destination.

Alert destinations | Databricks on AWS

Graphical user interface, application Description automatically generated

Explanation

Warning message:

Graphical user interface, text, application, email Description automatically generated

Pricing for All-purpose clusters are more expensive than the job clusters

AWS pricing(Aug 15th 2022)Graphical user interface Description automatically generated

Bottom of Form

Top of Form

Explanation

The answer is, copy of raw data, easy to query and ingest data for downstream processes,

Medallion Architecture – Databricks

Here are the typical role of Bronze Layer in a medallion architecture.

Bronze Layer:

1. Raw copy of ingested data

2. Replaces traditional data lake

3. Provides efficient storage and querying of full, unprocessed history of data

4. No schema is applied at this layer

Exam focus: Please review the below image and understand the role of each layer(bronze, silver, gold) in medallion architecture, you will see varying questions targeting each layer and its purpose.

Sorry I had to add the watermark some people in Udemy are copying my content.

Explanation

What Is a Lakehouse? - The Databricks Blog

Text Description automatically generated

Explanation

The Answer is dbfs:/user/hive/warehouse this is the default location where spark stores user data-bases, the default can be changed using spark.sql.warehouse.dir a parameter. You can also provide a custom location using the LOCATION keyword.

Here is how this works,

Graphical user interface, text, application, email Description automatically generated

Default location

FYI, This can be changed used using cluster spark config or session config.

Modify spark.sql.warehouse.dir location to change the default location

Graphical user interface, text, application Description automatically generated

Explanation

Medallion Architecture – Databricks

Silver Layer:

1. Reduces data storage complexity, latency, and redundency

2. Optimizes ETL throughput and analytic query performance

3. Preserves grain of original data (without aggregation)

4. Eliminates duplicate records

5. production schema enforced

6. Data quality checks, quarantine corrupt data

Exam focus: Please review the below image and understand the role of each layer(bronze, silver, gold) in medallion architecture, you will see varying questions targeting each layer and its purpose.

Sorry I had to add the watermark some people in Udemy are copying my content.

A diagram of a house Description automatically generated with low confidence

The answer is Checkpointing and write-ahead logging.

Structured Streaming uses checkpointing and write-ahead logs to record the offset range of data being processed during each trigger interval.

The answer is

1.CREATE OR REPLACE TEMPORARY VIEW view_name

2.AS SELECT * FROM table_name

The default temporary view is session scoped, as soon as the session ends or if a notebook is de-tached session scoped temporary view is dropped.

Explanation

The answer is A Directory where parquet data files are stored, a sub directory _delta_log where meta data, and the transaction log is stored as JSON files.

Timeline Description automatically generated

Explanation

The answer is Databricks Repos allow you to add comments and select the changes you want to commit.

Explanation

The answer is, Modify the task, to include time out to kill the job if it runs more than 15 mins.

https://docs.microsoft.com/en-us/azure/databricks/data-engineering/jobs/jobs#timeout

Answer is SELECT * FROM orders UNION SELECT * FROM orders_archive

UNION and UNION ALL are set operators,

UNION combines the output from both queries but also eliminates the duplicates.

UNION ALL combines the output from both queries.

Explanation

The answer is Setup the dashboard refresh schedule to end in two weeks

Explanation

The answer is

1.spark\

2..readStream\

3..format("cloudFiles") \# use Auto loader

4..option("cloudFiles.format","csv") \ # csv format files

5..option("cloudFiles.schemaLocation", checkpoint_directory)\

6..load('landing')\

7..writeStream.option("checkpointLocation", checkpoint_directory)\

8..table(raw)

Note: if you chose the below option which is incorrect because it does not have readStream

1.spark.read.format("cloudFiles")

2..option("cloudFiles.format",”csv”)

3....

4...

5...

Exam focus: Please review the below image and understand the role of each layer(bronze, silver, gold) in medallion architecture, you will see varying questions targeting each layer and its purpose.

Sorry I had to add the watermark some people in Udemy are copying my content.

A diagram of a house Description automatically generated with low confidence

Explanation

The answer is, Provide schema hints.

1.spark.readStream \

2. .format("cloudFiles") \

3. .option("cloudFiles.format", "csv") \

4. .option("header", "true") \

5. .option("cloudFiles.schemaLocation", schema_location) \

6. .option("cloudFiles.schemaHints", "id int, description string")

7. .load(raw_data_location)

8. .writeStream \

9. .option("checkpointLocation", checkpoint_location) \

10. .start(target_delta_table_location)

option("cloudFiles.schemaHints", "id int, description string")

# Here we are providing a hint that id column is int and the description is a string

When cloudfiles.schemalocation is used to store the output of the schema inference during the load process, with schema hints you can enforce data types for known columns ahead of time.

Explanation

The answer is readStream, writeStream, once = True.

spark.readStream

format("delta")

table("events_log")

groupBy("customerId")

count()

writeStream

format("delta")

outputMode("complete")

option("checkpointLocation", "/tmp/delta/eventsByCustomer/_checkpoints/")

trigger(once = True)

table("target_table")

The answer is A, Directory listing, File notifications

Directory listing: Auto Loader identifies new files by listing the input directory.

File notification: Auto Loader can automatically set up a notification service and queue service that subscribe to file events from the input directory.

Choosing between file notification and directory listing modes | Databricks on AWS

Explanation

The answer is

1.select aggregate(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y)

2.as total_sales from sales

Nested Struct can be queried using the . notation performance.sales will give you access to all the sales values in the performance column.

Note: option D is wrong because it uses performance:sales not performance.sales. ":" this is only used when referring to JSON data but here we are dealing with a struct data type. for the exam please make sure to understand if you are dealing with JSON data or Struct data.

Other solutions:

we can also use reduce instead of aggregate

select reduce(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y) as total_sales from sales

we can also use explode and sum instead of using any higher-order funtions.

1.with cte as (

2. select

3. explode(flatten(collect_list(performance.sales))) sales from sales

4.)

5.select

6. sum(sales) from cte

Sample data with create table syntax for the data:

1.create or replace table sales as

2.select 1 as batchId ,

3.from_json('[{ "employeeId":1234,"sales" : 10000 },{ "employeeId":3232,"sales" : 30000 }]',

4. 'ARRAY>') as performance,

5. current_timestamp() as insertDate

6.union all

7.select 2 as batchId ,

8. from_json('[{ "employeeId":1235,"sales" : 10500 },{ "employeeId":3233,"sales" : 32000 }]',

9. 'ARRAY>') as performance,

10. current_timestamp() as insertDate

Explanation

Cluster pools allow us to reserve VM's ahead of time, when a new job cluster is created VM are grabbed from the pool. Note: when the VM's are waiting to be used by the cluster only cost incurred is Azure. Databricks run time cost is only billed once VM is allocated to a cluster.

Here is a demo of how to setup a pool and follow some best practices,

Graphical user interface, text Description automatically generated

Explanation

The answer isSELECT * FROM streadming_view order by id Please Note: Sorting with Group by will work without any issues

see below explanation for each option of the options,

Graphical user interface, text, application Description automatically generated

Certain operations are not allowed on streaming data, please see highlighted in bold.

https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html#unsupported-operations

• Multiple streaming aggregations (i.e. a chain of aggregations on a streaming DF) are not yet supported on streaming Datasets.
• Limit and take the first N rows are not supported on streaming Datasets.
• Distinct operations on streaming Datasets are not supported.
• Deduplication operation is not supported after aggregation on a streaming Datasets.
• Sorting operations are supported on streaming Datasets only after an aggregation and in Complete Output Mode.

        Note: Sorting without aggregation function is not supported.

Here is the sample code to prove this,

Setup test stream

Graphical user interface, text, application, email Description automatically generated

Sum aggregation function has no issues on stream

Graphical user interface, application Description automatically generated

Max aggregation function has no issues on stream

Graphical user interface, application Description automatically generated

Group by with Order by  has no issues on stream

Group by has no issues on stream

Table Description automatically generated

Order by without group by fails.

Graphical user interface, text, application Description automatically generated