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

Explanation

Answer is

USING CSV

OPTIONS ( header =“true”, delimiter = ”|”)

Here is the syntax to create an external table with additional options

CREATE TABLE table_name (col_name1 col_typ1,..)

USING data_source

OPTIONS (key=’value’, key2=vla2)

LOCATION = “/location“

Explanation

The answer is Data Explorer

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

The answer is department = dbutils.widget.get("department")

Refer to additional documentation here

https://docs.databricks.com/notebooks/widgets.html

Explanation

Answer is View, A view can be used to join multiple tables but also persist into meta stores so others can accesses it

Explanation

The answer is, Add a retry policy to the task

Tasks in Jobs support Retry Policy, which can be used to retry a failed tasks, especially when using spot instance it is common to have failed executors or driver.

Explanation

Medallion Architecture – Databricks

Gold Layer:

1. Powers Ml applications, reporting, dashboards, ad hoc analytics

2. Refined views of data, typically with aggregations

3. Reduces strain on production systems

4. Optimizes query performance for business-critical 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 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

Cluster event logs are very useful, to identify issues pertaining to cluster availability. Cluster may not start due to resource limitations or issues with the cloud providers.

Some of the common issues include a subnet for compute VM reaching its limits or exceeding the subscription or cloud account CPU quota limit.

Here is an example where the cluster did not start due to subscription reaching the quota limit on a certain type of cpu cores for a VM type.

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

Click on event logs

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

Click on the message to see the detailed error message on why the cluster did not start.

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

Explanation

The answer is,

The entire dashboard with 10 queries can be refreshed at once, single schedule needs to be set up to refresh at 8 AM.

Automatically refresh a dashboard

A dashboard’s owner and users with the Can Edit permission can configure a dashboard to auto-matically refresh on a schedule. To automatically refresh a dashboard:

• uk.co.certification.simulator.questionpool.PList@1a219590

Explanation

The answer is GRANT MODIFY ON TABLE table_name TO john.smith@marketing.com , please note INSERT, UPDATE, and DELETE are combined into one role called MODIFY.

Below are the list of privileges that can be granted to a user or a group,

SELECT: gives read access to an object.

CREATE: gives the ability to create an object (for example, a table in a schema).

MODIFY: gives the ability to add, delete, and modify data to or from an object.

USAGE: does not give any abilities, but is an additional requirement to perform any action on a schema object.

READ_METADATA: gives the ability to view an object and its metadata.

CREATE_NAMED_FUNCTION: gives the ability to create a named UDF in an existing catalog or schema.

MODIFY_CLASSPATH: gives the ability to add files to the Spark classpath.

ALL PRIVILEGES: gives all privileges (is translated into all the above privileges

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 Run SQL command DESCRIBE EXTENDED table_name and check type

Example of External table

Graphical user interface, text, application Description automatically generated

Example of managed table

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

Explanation

The answer is SELECT DISTINCT * FROM table_name

Explanation

The answer is, INSERT OVERWRITE Overwrites the current version of the data but preserves all historical versions of the data, you can time travel to previous versions.

1.INSERT OVERWRITE customer_sales

2.SELECT * FROM customers c

3.INNER JOIN sales s on s.customer_id = c.customer_id

Let's just assume that this is the second time you are running the above statement, you can still query the prior version of the data using time travel, and any DML/DDL except DROP TABLE creates new PARQUET files so you can still access the previous versions of data.

SQL Syntax for Time travel

SELECT * FROM table_name as of [version number]

with customer_sales example

SELECT * FROM customer_sales as of 1 -- previous version

SELECT * FROM customer_sales as of 2 -- current version

You see all historical changes on the table using DESCRIBE HISTORY table_name

Note: the main difference between INSERT OVERWRITE and CREATE OR REPLACE TABLE(CRAS) is that CRAS can modify the schema of the table, i.e it can add new columns or change data types of existing columns. By default INSERT OVERWRITE only overwrites the data.

INSERT OVERWRITE can also be used to update the schema when spark.databricks.delta.schema.autoMerge.enabled is set true if this option is not enabled and if there is a schema mismatch command INSERT OVERWRITEwill fail.

Any DML/DDL operation(except DROP TABLE) on the Delta table preserves the historical ver-sion of the data.

Explanation

Here is how you setup

Create a single job and six tasks with the same notebook and assign a different parameter for each task ,

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

All tasks are added in a single job and can run parallel either using single shared cluster or with individual clusters.

Graphical user interface, application, Teams Description automatically generated

Explanation

The answer is, Cron, On-Demand runs

Supports running job immediately or using can be scheduled using CRON syntax

Explanation

Answer is Databricks Notebooks support real-time coauthoring on a single notebook

Every change is saved, and a notebook can be changed my multiple users.

Explanation

The answer is the Unity catalog.

Diagram Description automatically generated

Unity Catalog works at the Account level, it has the ability to create a meta store and attach that meta store to many workspaces

see the below diagram to understand how Unity Catalog Works, as you can see a metastore can now be shared with both workspaces using Unity Catalog, prior to Unity Catalog the options was to use single cloud object storage manually mount in the second databricks workspace, and you can see here Unity Catalog really simplifies that.

Diagram Description automatically generated with medium confidence

sorry for the inconvenience watermark was added because other people on Udemy are copying my questions and images.

duct features

https://databricks.com/product/unity-catalog

Explanation

The answer is, A single notebook can support multiple languages, use the magic command to switch between the two.

Use %sql and %python magic commands within the same notebook

Explanation

The answer is Add Operation reporting task in the same job and set the operations reporting task to depend on Data Engineering task.

Diagram Description automatically generated with medium confidence

Explanation

CREATE TABLE inventory (id INT, units FLOAT) TBLPROPERTIES (business_owner = ‘supply chain’)

Table properties and table options (Databricks SQL) | Databricks on AWS

Alter table command can used to update the TBLPROPERTIES

ALTER TABLE inventory SET TBLPROPERTIES(business_owner , 'operations')

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 Checkpointing and idempotent sinks

How does structured streaming achieves end to end fault tolerance:

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

•Next, the streaming sinks are designed to be _idempotent_—that is, multiple writes of the same data (as identified by the offset) do not result in duplicates being written to the sink.

Taken together, replayable data sources and idempotent sinks allow Structured Streaming to en-sure end-to-end, exactly-once semantics under any failure condition.

Explanation

The answer is, Data lakehouse can store structured and unstructured data and can enforce schema

What Is a Lakehouse? - The Databricks Blog

Graphical user interface, text, application Description automatically generated

Explanation

The answer is,

1.if department is not None:

2. #Execute code

3.else:

4. pass

Explanation

Upgrade tables and views to Unity Catalog - Azure Databricks | Microsoft Docs

Managed table: Upgrade a managed to Unity Catalog

External table: Upgrade an external table to Unity Catalog

Explanation

Answer is All-purpose cluster/ interactive cluster is the recommended way to run commands and view the data.

A job cluster can not provide a way for a user to interact with a notebook once the job is submitted, but an Interactive cluster allows to you display data, view visualizations write or edit quries, which makes it a perfect fit to investigate and analyze the data.