Great Databricks Certified Professional Data Engineer Exam Dumps

dumps2023-02-13T05:48:02+08:00

Databricks Certified Professional Data Engineer exam information is available for you to study. There are 60 multiple-choice questions in the real Databricks Certified Professional Data Engineer exam, and you have 120 minutes to answer all the questions. The exam cost is $200. We have cracked the latest Databricks Certified Professional Data Engineer exam dumps, which are valid. Free Databricks Certified Professional Data Engineer exam dumps are available below.

Page 1 of 7

1. You were asked to setup a new all-purpose cluster, but the cluster is unable to start which of the following steps do you need to take to identify the root cause of the issue and the reason why the cluster was unable to start?

Check the cluster driver logs

Check the cluster event logs (Correct)

Workspace logs

Storage account

Data plane

2. A SQL Dashboard was built for the supply chain team to monitor the inventory and product orders, but all of the timestamps displayed on the dashboards are showing in UTC format, so they requested to change the time zone to the location of New York.

How would you approach resolving this issue?

Move the workspace from Central US zone to East US Zone

Change the timestamp on the delta tables to America/New_York format

Change the spark configuration of SQL endpoint to format the timestamp to Ameri-ca/New_York

Under SQL Admin Console, set the SQL configuration parameter time zone to Ameri-ca/New_York

Add SET Timezone = America/New_York on every of the SQL queries in the dashboard.

3. You are currently asked to work on building a data pipeline, you have noticed that you are currently working on a very large scale ETL many data dependencies, which of the following tools can be used to address this problem?

AUTO LOADER

JOBS and TASKS

SQL Endpoints

DELTA LIVE TABLES

STRUCTURED STREAMING with MULTI HOP

4. When you drop a managed table using SQL syntax DROP TABLE table_name how does it impact metadata, history, and data stored in the table?

Drops table from meta store, drops metadata, history, and data in storage.

Drops table from meta store and data from storage but keeps metadata and history in storage

Drops table from meta store, meta data and history but keeps the data in storage

Drops table but keeps meta data, history and data in storage

Drops table and history but keeps meta data and data in storage

5. Which of the following approaches can the data engineer use to obtain a version-controllable con-figuration of the Job’s schedule and configuration?

They can link the Job to notebooks that are a part of a Databricks Repo.

They can submit the Job once on a Job cluster.

They can download the JSON equivalent of the job from the Job’s page.

They can submit the Job once on an all-purpose cluster.

They can download the XML description of the Job from the Job’s page

6. What is the underlying technology that makes the Auto Loader work?

Loader

Delta Live Tables

Structured Streaming

DataFrames

Live DataFames

7. You are currently looking at a table that contains data from an e-commerce platform, each row contains a list of items(Item number) that were present in the cart, when the customer makes a change to the cart the entire information is saved as a separate list and appended to an existing list for the duration of the customer session, to identify all the items customer bought you have to make a unique list of items, you were asked to create a unique item’s list that was added to the cart by the user, fill in the blanks of below query by choosing the appropriate higher-order function?

Note: See below sample data and expected output.

Schema: cartId INT, items Array<INT>

Fill in the blanks:

Fill in the blanks:

SELECT cartId, _(_(items)) FROM carts

ARRAY_UNION, ARRAY_DISCINT

ARRAY_DISTINCT, ARRAY_UNION

ARRAY_DISTINCT, FLATTEN

FLATTEN, ARRAY_DISTINCT

ARRAY_DISTINCT, ARRAY_FLATTEN

8. When building a DLT s pipeline you have two options to create a live tables, what is the main difference between CREATE STREAMING LIVE TABLE vs CREATE LIVE TABLE?

CREATE STREAMING LIVE table is used in MULTI HOP Architecture

CREATE LIVE TABLE is used when working with Streaming data sources and Incremental data

CREATE STREAMING LIVE TABLE is used when working with Streaming data sources and Incremental data

There is no difference both are the same, CREATE STRAMING LIVE will be deprecated soon

CREATE LIVE TABLE is used in DELTA LIVE TABLES, CREATE STREAMING LIVE can only used in Structured Streaming applications

9. 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

10. Which of the following commands can be used to run one notebook from another notebook?

notebook.utils.run("full notebook path")

execute.utils.run("full notebook path")

dbutils.notebook.run("full notebook path")

only job clusters can run notebook

spark.notebook.run("full notebook path")

Page 2 of 7

11. You have configured AUTO LOADER to process incoming IOT data from cloud object storage every 15 mins, recently a change was made to the notebook code to update the processing logic but the team later realized that the notebook was failing for the last 24 hours, what steps team needs to take to reprocess the data that was not loaded after the notebook was corrected?

Move the files that were not processed to another location and manually copy the files into the ingestion path to reprocess them

Enable back_fill = TRUE to reprocess the data

Delete the checkpoint folder and run the autoloader again

Autoloader automatically re-processes data that was not loaded

Manually re-load the data

12. John Smith is a newly joined team member in the Marketing team who currently has access read access to sales tables but does not have access to delete rows from the table, which of the following commands help you accomplish this?

GRANT USAGE ON TABLE table_name TO [email protected]

GRANT DELETE ON TABLE table_name TO [email protected]

GRANT DELETE TO TABLE table_name ON [email protected]

GRANT MODIFY TO TABLE table_name ON [email protected]

GRANT MODIFY ON TABLE table_name TO [email protected]

13. Which of the following SQL command can be used to insert or update or delete rows based on a condition to check if a row(s) exists?

MERGE INTO table_name

COPY INTO table_name

UPDATE table_name

INSERT INTO OVERWRITE table_name

INSERT IF EXISTS table_name

14. return x

check_input(1,3)

3 (Correct)

15. Which of the following is true, when building a Databricks SQL dashboard?

A dashboard can only use results from one query

Only one visualization can be developed with one query result

A dashboard can only connect to one schema/Database

More than one visualization can be developed using a single query result

A dashboard can only have one refresh schedule

16. What is the main difference between the bronze layer and silver layer in a medallion architecture?

Duplicates are removed in bronze, schema is applied in silver

Silver may contain aggregated data

Bronze is raw copy of ingested data, silver contains data with production schema and optimized for ELT/ETL throughput

Bad data is filtered in Bronze, silver is a copy of bronze data

17. You noticed that a team member started using an all-purpose cluster to develop a notebook and used the same all-purpose cluster to set up a job that can run every 30 mins so they can update un-derlying tables which are used in a dashboard.

What would you recommend for reducing the overall cost of this approach?

Reduce the size of the cluster

Reduce the number of nodes and enable auto scale

Enable auto termination after 30 mins

Change the cluster all-purpose to job cluster when scheduling the job

Change the cluster mode from all-purpose to single-mode

18. Which of the following locations hosts the driver and worker nodes of a Databricks-managed cluster?

Data plane

Control plane

Databricks Filesystem

JDBC data source

Databricks web application

19. Which of the following data workloads will utilize a Bronze table as its destination?

A job that aggregates cleaned data to create standard summary statistics

A job that queries aggregated data to publish key insights into a dashboard

A job that ingests raw data from a streaming source into the Lakehouse

A job that develops a feature set for a machine learning application

A job that enriches data by parsing its timestamps into a human-readable format

20. What could be the expected output of query SELECT COUNT (DISTINCT *) FROM user on this table

2 (Correct)

NULL

Page 3 of 7

21. A DELTA LIVE TABLE pipelines can be scheduled to run in two different modes, what are these two different modes?

Triggered, Incremental

Once, Continuous

Triggered, Continuous

Once, Incremental

Continuous, Incremental

22. Which of the following developer operations in CI/CD flow can be implemented in Databricks Re-pos?

Merge when code is committed

Pull request and review process

Trigger Databricks Repos API to pull the latest version of code into production folder

Resolve merge conflicts

Delete a branch

23. Identify one of the below statements that can query a delta table in PySpark Dataframe API

Spark.read.mode("delta").table("table_name")

Spark.read.table.delta("table_name")

Spark.read.table("table_name")

Spark.read.format("delta").LoadTableAs("table_name")

Spark.read.format("delta").TableAs("table_name")

24. How VACCUM and OPTIMIZE commands can be used to manage the DELTA lake?

VACCUM command can be used to compact small parquet files, and the OP-TIMZE command can be used to delete parquet files that are marked for dele-tion/unused.

VACCUM command can be used to delete empty/blank parquet files in a delta table. OPTIMIZE command can be used to update stale statistics on a delta table.

VACCUM command can be used to compress the parquet files to reduce the size of the table, OPTIMIZE command can be used to cache frequently delta tables for better performance.

VACCUM command can be used to delete empty/blank parquet files in a delta table, OPTIMIZE command can be used to cache frequently delta tables for better performance.

OPTIMIZE command can be used to compact small parquet files, and the VAC-CUM command can be used to delete parquet files that are marked for deletion/unused. (Correct)

25. Which of the following statements are correct on how Delta Lake implements a lake house?

Delta lake uses a proprietary format to write data, optimized for cloud storage

Using Apache Hadoop on cloud object storage

Delta lake always stores meta data in memory vs storage

Delta lake uses open source, open format, optimized cloud storage and scalable meta data

Delta lake stores data and meta data in computes memory

26. What are the different ways you can schedule a job in Databricks workspace?

Continuous, Incremental

On-Demand runs, File notification from Cloud object storage

Cron, On Demand runs

Cron, File notification from Cloud object storage

Once, Continuous

27. Which of the following type of tasks cannot setup through a job?

Notebook

DELTA LIVE PIPELINE

Spark Submit

Python

Databricks SQL Dashboard refresh

28. Which of the following describes how Databricks Repos can help facilitate CI/CD workflows on the Databricks Lakehouse Platform?

Databricks Repos can facilitate the pull request, review, and approval process before merging branches

Databricks Repos can merge changes from a secondary Git branch into a main Git branch

Databricks Repos can be used to design, develop, and trigger Git automation pipelines

Databricks Repos can store the single-source-of-truth Git repository

Databricks Repos can commit or push code changes to trigger a CI/CD process

29. table("uncleanedSales")

30. table(table_name))

format, checkpointlocation, schemalocation, overwrite

cloudfiles.format, checkpointlocation, cloudfiles.schemalocation, overwrite

cloudfiles.format, cloudfiles.schemalocation, checkpointlocation, mergeSchema

cloudfiles.format, cloudfiles.schemalocation, checkpointlocation, append

cloudfiles.format, cloudfiles.schemalocation, checkpointlocation, overwrite

Page 4 of 7

31. Which of the following scenarios is the best fit for AUTO LOADER?

Efficiently process new data incrementally from cloud object storage

Efficiently move data incrementally from one delta table to another delta table

Incrementally process new data from streaming data sources like Kafka into delta lake

Incrementally process new data from relational databases like MySQL

Efficiently copy data from one data lake location to another data lake location

32. 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’)

33. While investigating a data issue, you wanted to review yesterday's version of the table using below command, while querying the previous version of the table using time travel you realized that you are no longer able to view the historical data in the table and you could see it the table was updated yesterday based on the table history(DESCRIBE HISTORY table_name) command what could be the reason why you can not access this data?

SELECT * FROM table_name TIMESTAMP AS OF date_sub(current_date(), 1)

You currently do not have access to view historical data

By default, historical data is cleaned every 180 days in DELTA

A command VACUUM table_name RETAIN 0 was ran on the table

Time travel is disabled

Time travel must be enabled before you query previous data

34. Which of the following table constraints that can be enforced on Delta lake tables are supported?

Primary key, foreign key, Not Null, Check Constraints

Primary key, Not Null, Check Constraints

Default, Not Null, Check Constraints

Not Null, Check Constraints

Unique, Not Null, Check Constraints

35. Which of the following commands results in the successful creation of a view on top of the delta stream (stream on delta table)?

Spark.read.format("delta").table("sales").createOrReplaceTempView("streaming_vw")

Spark.readStream.format("delta").table("sales").createOrReplaceTempView("streaming_vw ")

Spark.read.format("delta").table("sales").mode("stream").createOrReplaceTempView("strea ming_vw")

Spark.read.format("delta").table("sales").trigger("stream").createOrReplaceTempView("stre aming_vw")

Spark.read.format("delta").stream("sales").createOrReplaceTempView("streaming_vw")

You can not create a view on streaming data source.

36. What is the purpose of a 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

Powers ML applications, reporting, dashboards and adhoc reports.

37. Which of the following programming languages can be used to build a Databricks SQL dashboard?

Python

Scala

SQL

All of the above

38. You are working on a table called orders which contains data for 2021 and you have the second table called orders_archive which contains data for 2020, you need to combine the data from two tables and there could be a possibility of the same rows between both the tables, you are looking to combine the results from both the tables and eliminate the duplicate rows, which of the following SQL statements helps you accomplish this?

SELECT * FROM orders UNION SELECT * FROM orders_archive (Correct)

SELECT * FROM orders INTERSECT SELECT * FROM orders_archive

SELECT * FROM orders UNION ALL SELECT * FROM orders_archive

SELECT * FROM orders_archive MINUS SELECT * FROM orders

SELECT distinct * FROM orders JOIN orders_archive on order.id = or-ders_archive.id

39. Once a cluster is deleted, below additional actions need to performed by the administrator

Remove virtual machines but storage and networking are automatically dropped

Drop storage disks but Virtual machines and networking are automatically dropped

Remove networking but Virtual machines and storage disks are automatically dropped

Remove logs

No action needs to be performed. All resources are automatically removed.

40. Which of the statements are correct about lakehouse?

Lakehouse only supports Machine learning workloads and Data warehouses support BI workloads

Lakehouse only supports end-to-end streaming workloads and Data warehouses support Batch workloads

Lakehouse does not support ACID

In Lakehouse Storage and compute are coupled

Lakehouse supports schema enforcement and evolution

Page 5 of 7

41. You are working on IOT data where each device has 5 reading in an array collected in Celsius, you were asked to covert each individual reading from Celsius to Fahrenheit, fill in the blank with an appropriate function that can be used in this scenario.

Schema: deviceId INT, deviceTemp ARRAY<double>

SELECT deviceId, __(deviceTempC,i-> (i * 9/5) + 32) as deviceTempF

FROM sensors

APPLY

MULTIPLY

ARRAYEXPR

TRANSFORM

FORALL

42. 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

43. You are currently working on a production job failure with a job set up in job clusters due to a data issue, what cluster do you need to start to investigate and analyze the data?

A Job cluster can be used to analyze the problem

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

Existing job cluster can be used to investigate the issue

Databricks SQL Endpoint can be used to investigate the issue

44. Data engineering team has provided 10 queries and asked Data Analyst team to build a dashboard and refresh the data every day at 8 AM, identify the best approach to set up data refresh for this dashaboard?

Each query requires a separate task and setup 10 tasks under a single job to run at 8 AM to refresh the dashboard

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

Setup JOB with linear dependency to all load all 10 queries into a table so the dashboard can be refreshed at once.

A dashboard can only refresh one query at a time, 10 schedules to set up the refresh.

Use Incremental refresh to run at 8 AM every day.

45. 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 CS

* from 'dbfs:/location/csv_files/'

SELECT * FROM CS

'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

46. You have written a notebook to generate a summary data set for reporting, Notebook was scheduled using the job cluster, but you realized it takes 8 minutes to start the cluster, what feature can be used to start the cluster in a timely fashion so your job can run immediatley?

Setup an additional job to run ahead of the actual job so the cluster is running second job starts

Use the Databricks cluster pools feature to reduce the startup time

Use Databricks Premium edition instead of Databricks standard edition

Pin the cluster in the cluster UI page so it is always available to the jobs

Disable auto termination so the cluster is always running

47. Which of the following developer operations in CI/CD flow can be implemented in Databricks Re-pos?

Delete branch

Trigger Databricks CICD pipeline

Commit and push code

Create a pull request

Approve the pull request

48. You noticed that colleague is manually copying the notebook with _bkp to store the previous versions, which of the following feature would you recommend instead.

Databricks notebooks support change tracking and versioning

Databricks notebooks should be copied to a local machine and setup source control locally to version the notebooks

Databricks notebooks can be exported into dbc archive files and stored in data lake

Databricks notebook can be exported as HTML and imported at a later time

49. pass

50. You are asked to setup an AUTO LOADER to process the incoming data, this data arrives in JSON format and get dropped into cloud object storage and you are required to process the data as soon as it arrives in cloud storage, which of the following statements is correct

AUTO LOADER is native to DELTA lake it cannot support external cloud object storage

AUTO LOADER has to be triggered from an external process when the file arrives in the cloud storage

AUTO LOADER needs to be converted to a Structured stream process

AUTO LOADER can only process continuous data when stored in DELTA lake

AUTO LOADER can support file notification method so it can process data as it arrives

Page 6 of 7

51. Which of the following techniques structured streaming uses to ensure recovery of failures during stream processing?

Checkpointing and Watermarking

Write ahead logging and watermarking

Checkpointing and write-ahead logging

Delta time travel

The stream will failover to available nodes in the cluster

Checkpointing and Idempotent sinks

52. Kevin is the owner of both the sales table and regional_sales_vw view which uses the sales table as the underlying source for the data, and Kevin is looking to grant select privilege on the view regional_sales_vw to one of newly joined team members Steven.

Which of the following is a true statement?

Kevin can not grant access to Steven since he does not have security admin privilege

Kevin although is the owner but does not have ALL PRIVILEGES permission

Kevin can grant access to the view, because he is the owner of the view and the under-lying table

Kevin can not grant access to Steven since he does have workspace admin privilege

Steve will also require SELECT access on the underlying table

53. Which of the following SQL statements can be used to update a transactions table, to set a flag on the table from Y to N

MODIFY transactions SET active_flag = 'N' WHERE active_flag = 'Y'

MERGE transactions SET active_flag = 'N' WHERE active_flag = 'Y'

UPDATE transactions SET active_flag = 'N' WHERE active_flag = 'Y'

REPLACE transactions SET active_flag = 'N' WHERE active_flag = 'Y'

54. unitsSold int)

55. If you create a database sample_db with the statement CREATE DATABASE sample_db what will be the default location of the database in DBFS?

Default location, DBFS:/user/

Default location, /user/db/

Default Storage account

Statement fails “Unable to create database without location”

Default Location, dbfs:/user/hive/warehouse

56. AS SELECT * FROM table_name

57. The default threshold of VACUUM is 7 days, internal audit team asked to certain tables to maintain at least 365 days as part of compliance requirement, which of the below setting is needed to implement.

ALTER TABLE table_name set TBLPROPERTIES (del-ta.deletedFileRetentionDuration= ‘interval 365 days’)

MODIFY TABLE table_name set TBLPROPERTY (delta.maxRetentionDays = ‘inter-val 365 days’)

ALTER TABLE table_name set EXENDED TBLPROPERTIES (del-ta.deletedFileRetentionDuration= ‘interval 365 days’)

ALTER TABLE table_name set EXENDED TBLPROPERTIES (delta.vaccum.duration= ‘interval 365 days’)

58. You are currently working to ingest millions of files that get uploaded to the cloud object storage for consumption, and you are asked to build a process to ingest this data, the schema of the file is expected to change over time, and the ingestion process should be able to handle these changes automatically.

Which of the following method can be used to ingest the data incrementally?

AUTO APPEND

AUTO LOADER

COPY INTO

Structured Streaming

Checkpoint

59. You are noticing job cluster is taking 6 to 8 mins to start which is delaying your job to finish on time, what steps you can take to reduce the amount of time cluster startup time

Setup a second job ahead of first job to start the cluster, so the cluster is ready with re-sources when the job starts

Use All purpose cluster instead to reduce cluster start up time

Reduce the size of the cluster, smaller the cluster size shorter it takes to start the cluster

Use cluster pools to reduce the startup time of the jobs

Use SQL endpoints to reduce the startup time

60. as total_sales from sales

Page 7 of 7

61. You are currently working with the application team to setup a SQL Endpoint point, once the team started consuming the SQL Endpoint you noticed that during peak hours as the number of concur-rent users increases you are seeing degradation in the query performance and the same queries are taking longer to run, which of the following steps can be taken to resolve the issue?

They can turn on the Serverless feature for the SQL endpoint.

They can increase the maximum bound of the SQL endpoint’s scaling range.

They can increase the cluster size(2X-Small to 4X-Large) of the SQL endpoint.

They can turn on the Auto Stop feature for the SQL endpoint.

They can turn on the Serverless feature for the SQL endpoint and change the Spot In-stance Policy from “Cost optimized” to “Reliability Optimized.”

62. Which of the following locations in the Databricks product architecture hosts the notebooks and jobs?

Data plane

Control plane

Databricks Filesystem

JDBC data source

Databricks web application

63. A data engineer is using a Databricks SQL query to monitor the performance of an ELT job. The ELT job is triggered by a specific number of input records being ready to process. The Databricks SQL query returns the number of minutes since the job’s most recent runtime.

Which of the following approaches can enable the data engineering team to be notified if the ELT job has not been run in an hour?

They can set up an Alert for the accompanying dashboard to notify them if the returned value is greater than 60.

They can set up an Alert for the query to notify when the ELT job fails.

They can set up an Alert for the accompanying dashboard to notify when it has not refreshed in 60 minutes.

They can set up an Alert for the query to notify them if the returned value is greater than 60.

This type of alert is not possible in Databricks

Great Databricks Certified Professional Data Engineer Exam Dumps

Great Databricks Certified Professional Data Engineer Exam Dumps

Share this post

Leave a Reply Cancel reply

Related Posts