[Q27-Q47] Latest Databricks Databricks-Certified-Professional-Data-Engineer First Attempt, Exam real Dumps Updated [Oct-2023]

QUESTION 27
A dataset has been defined using Delta Live Tables and includes an expectations clause: CON-STRAINT valid_timestamp EXPECT (timestamp > ‘2020-01-01’) ON VIOLATION FAIL What is the expected behavior when a batch of data containing data that violates these constraints is processed?

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

Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.

Records that violate the expectation cause the job to fail

Records that violate the expectation are added to the target dataset and flagged as in-valid in a field added to the target dataset.

Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.

QUESTION 28
When scheduling Structured Streaming jobs for production, which configuration automatically recovers from query failures and keeps costs low?

Cluster: New Job Cluster;
Retries: Unlimited;
Maximum Concurrent Runs: Unlimited

Cluster: New Job Cluster;
Retries: None;
Maximum Concurrent Runs: 1

Cluster: Existing All-Purpose Cluster;
Retries: Unlimited;
Maximum Concurrent Runs: 1

Cluster: Existing All-Purpose Cluster;
Retries: None;
Maximum Concurrent Runs: 1

QUESTION 29
The data engineering team has configured a Databricks SQL query and alert to monitor the values in a Delta Lake table. Therecent_sensor_recordingstable contains an identifyingsensor_idalongside thetimestampandtemperaturefor the most recent 5 minutes of recordings.
The below query is used to create the alert:

The query is set to refresh each minute and always completes in less than 10 seconds. The alert is set to trigger whenmean (temperature) > 120. Notifications are triggered to be sent at most every 1 minute.
If this alert raises notifications for 3 consecutive minutes and then stops, which statement must be true?

The total average temperature across all sensors exceeded 120 on three consecutive executions of the query

Therecent_sensor_recordingstable was unresponsive for three consecutive runs of the query

The source query failed to update properly for three consecutive minutes and then restarted

The maximum temperature recording for at least one sensor exceeded 120 on three consecutive executions of the query

The average temperature recordings for at least one sensor exceeded 120 on three consecutive executions of the query

QUESTION 30
You are using k-means clustering to classify heart patients for a hospital. You have chosen Patient Sex,
Height, Weight, Age and Income as measures and have used 3 clusters. When you create a pair-wise plot of
the clusters, you notice that there is significant overlap between the clusters. What should you do?

Identify additional measures to add to the analysis

Remove one of the measures

Decrease the number of clusters

Increase the number of clusters

QUESTION 31
You are working on a dashboard that takes a long time to load in the browser, due to the fact that each visualization contains a lot of data to populate, which of the following approaches can be taken to address this issue?

Increase size of the SQL endpoint cluster

Increase the scale of maximum range of SQL endpoint cluster

Use Databricks SQL Query filter to limit the amount of data in each visualization

Remove data from Delta Lake

Use Delta cache to store the intermediate results

QUESTION 32
What is the purpose of gold layer in Multi hop architecture?

Optimizes ETL throughput and analytic query performance

Eliminate duplicate records

Preserves grain of original data, without any aggregations

Data quality checks and schema enforcement

Optimized query performance for business-critical data

QUESTION 33
What is the type of table created when you issue SQL DDL command CREATE TABLE sales (id int, units int)

Query fails due to missing location

Query fails due to missing format

Managed Delta table

External Table

Managed Parquet table

QUESTION 34
What is the best way to query external csv files located on DBFS Storage to inspect the data using SQL?

SELECT * FROM ‘dbfs:/location/csv_files/’ FORMAT = ‘CSV’

SELECT CSV. * from ‘dbfs:/location/csv_files/’

SELECT * FROM CSV. ‘dbfs:/location/csv_files/’

You can not query external files directly, us COPY INTO to load the data into a table first

SELECT * FROM ‘dbfs:/location/csv_files/’ USING CSV

QUESTION 35
Suppose there are three events then which formula must always be equal to P(E1|E2,E3)?

P(E1,E2,E3)P(E1)/P(E2:E3)

P(E1,E2;E3)/P(E2,E3)

P(E1,E2|E3)P(E2|E3)P(E3)

P(E1,E2|E3)P(E3)

P(E1,E2,E3)P(E2)P(E3)

QUESTION 36
The team has decided to take advantage of table properties to identify a business owner for each table, which of the following table DDL syntax allows you to populate a table property identifying the business owner of a table CREATE TABLE inventory (id INT, units FLOAT)

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

TBLPROPERTIES (business_owner = ‘supply chain’)

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

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

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

QUESTION 37
Which of the following statements can successfully read the notebook widget and pass the python variable to a SQL statement in a Python notebook cell?

1.order_date = dbutils.widgets.get(“widget_order_date”)
2.
3.spark.sql(f”SELECT * FROM sales WHERE orderDate = ‘f{order_date }'”)

1.order_date = dbutils.widgets.get(“widget_order_date”)
2.
3.spark.sql(f”SELECT * FROM sales WHERE orderDate = ‘order_date’ “)

1.order_date = dbutils.widgets.get(“widget_order_date”)
2.
3.spark.sql(f”SELECT * FROM sales WHERE orderDate = ‘${order_date }’ “)

1.order_date = dbutils.widgets.get(“widget_order_date”)
2.
3.spark.sql(f”SELECT * FROM sales WHERE orderDate = ‘{order_date}’ “)
(Correct)

1.order_date = dbutils.widgets.get(“widget_order_date”)
2.
3.spark.sql(“SELECT * FROM sales WHERE orderDate = order_date”)

QUESTION 38
A data architect is designing a data model that works for both video-based machine learning work-loads and
highly audited batch ETL/ELT workloads.
Which of the following describes how using a data lakehouse can help the data architect meet the needs of
both workloads?

A data lakehouse requires very little data modeling

A data lakehouse combines compute and storage for simple governance

A data lakehouse fully exists in the cloud

A data lakehouse stores unstructured data and is ACID-compliant

A data lakehouse provides autoscaling for compute clusters

QUESTION 39
Data engineering team has a job currently setup to run a task load data into a reporting table every day at 8: 00 AM takes about 20 mins, Operations teams are planning to use that data to run a second job, so they access latest complete set of data. What is the best to way to orchestrate this job setup?

Add Operation reporting task in the same job and set the Data Engineering task to de-pend on Operations reporting task

Setup a second job to run at 8:20 AM in the same workspace

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

Use Auto Loader to run every 20 mins to read the initial table and set the trigger to once and create a second job

Setup a Delta live to table based on the first table, set the job to run in continuous mode

QUESTION 40
You would like to build a spark streaming process to read from a Kafka queue and write to a Delta table every
15 minutes, what is the correct trigger option

trigger(“15 minutes”)

trigger(process “15 minutes”)

trigger(processingTime = 15)

trigger(processingTime = “15 Minutes”)

trigger(15)

QUESTION 41
You are currently working on reloading customer_sales tables using the below query
1. INSERT OVERWRITE customer_sales
2. SELECT * FROM customers c
3. INNER JOIN sales_monthly s on s.customer_id = c.customer_id
After you ran the above command, the Marketing team quickly wanted to review the old data that was in the table. How does INSERT OVERWRITE impact the data in the customer_sales table if you want to see the previous version of the data prior to running the above statement?

Overwrites the data in the table, all historical versions of the data, you can not time travel to previous versions

Overwrites the data in the table but preserves all historical versions of the data, you can time travel to previous versions

Overwrites the current version of the data but clears all historical versions of the data, so you can not time travel to previous versions.

Appends the data to the current version, you can time travel to previous versions

By default, overwrites the data and schema, you cannot perform time travel

QUESTION 42
The marketing team is launching a new campaign to monitor the performance of the new campaign for the first two weeks, they would like to set up a dashboard with a refresh schedule to run every 5 minutes, which of the below steps can be taken to reduce of the cost of this refresh over time?

Reduce the size of the SQL Cluster size

Reduce the max size of auto scaling from 10 to 5

Setup the dashboard refresh schedule to end in two weeks

Change the spot instance policy from reliability optimized to cost optimized

Always use X-small cluster

QUESTION 43
You are tasked to set up a set notebook as a job for six departments and each department can run the task parallelly, the notebook takes an input parameter dept number to process the data by department, how do you go about to setup this up in job?

Use a single notebook as task in the job and use dbutils.notebook.run to run each note-book with parameter in a different cell

A task in the job cannot take an input parameter, create six notebooks with hardcoded dept number and setup six tasks with linear dependency in the job

A task accepts key-value pair parameters, creates six tasks pass department number as parameter foreach task with no dependency in the job as they can all run in parallel.
(Correct)

A parameter can only be passed at the job level, create six jobs pass department number to each job with linear job dependency

A parameter can only be passed at the job level, create six jobs pass department number to each job with no job dependency

QUESTION 44
What is the purpose of the bronze layer in a Multi-hop architecture?

Can be used to eliminate duplicate records

Used as a data source for Machine learning applications.

Perform data quality checks, corrupt data quarantined

Contains aggregated data that is to be consumed into Silver

Provides efficient storage and querying of full unprocessed history of data

QUESTION 45
Which statement describes Delta Lake Auto Compaction?

An asynchronous job runs after the write completes to detect if files could be further compacted; if yes, an optimize job is executed toward a default of 1 GB.

Before a Jobs cluster terminates, optimize is executed on all tables modified during the most recent job.

Optimized writes use logical partitions instead of directory partitions; because partition boundaries are only represented in metadata, fewer small files are written.

Data is queued in a messaging bus instead of committing data directly to memory; all data is committed from the messaging bus in one batch once the job is complete.

An asynchronous job runs after the write completes to detect if files could be further compacted; if yes, an optimize job is executed toward a default of 128 MB.

QUESTION 46
You were asked to create a table that can store the below data, orderTime is a timestamp but the finance team when they query this data normally prefer the orderTime in date format, you would like to create a calculated column that can convert the orderTime column timestamp datatype to date and store it, fill in the blank to complete the DDL.