[Apr 01, 2023] Reliable Databricks-Certified-Professional-Data-Engineer Exam Tips Test Pdf Exam Material
New 2023 Databricks-Certified-Professional-Data-Engineer Test Tutorial (Updated 220 Questions)
NEW QUESTION 15
When working with AUTO LOADER you noticed that most of the columns that were inferred as part of loading are string data types including columns that were supposed to be integers, how can we fix this?
- A. Update the checkpoint location
- B. Provide the schema of the target table in the cloudfiles.schemalocation
- C. Provide the schema of the source table in the cloudfiles.schemalocation
- D. Correct the incoming data by explicitly casting the data types
- E. Provide schema hints
Answer: E
Explanation:
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.
NEW QUESTION 16
You are asked to write a python function that can read data from a delta table and return the Data-Frame, which of the following is correct?
- A. Write SQL UDF to return a DataFrame
- B. Write SQL UDF that can return tabular data
- C. Python function will result in out of memory error due to data volume
- D. Python function cannot return a DataFrame
- E. Python function can return a DataFrame
Answer: C
Explanation:
Explanation
The answer is Python function can return a DataFrame
The function would something like this,
1.get_source_dataframe(tablename):
2. df = spark.read.table(tablename)
3.return df
df = get_source_dataframe('test_table')
since there is no action spark returns a Dataframe and assigns to df python variable
NEW QUESTION 17
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
- A. REPLACE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- B. UPDATE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- C. MERGE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- D. MODIFY transactions SET active_flag = 'N' WHERE active_flag = 'Y'
Answer: A
Explanation:
Explanation
The answer is
UPDATE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
Delta Lake supports UPDATE statements on the delta table, all of the changes as part of the update are ACID compliant.
NEW QUESTION 18
Why does AUTO LOADER require schema location?
- A. Schema location is used to identify the schema of target table
- B. Schema location is used to identify the schema of target table and source table
- C. Schema location is used to store schema inferred by AUTO LOADER
- D. AUTO LOADER does not require schema location, because its supports Schema evolution
- E. Schema location is used to store user provided schema
Answer: C
Explanation:
Explanation
The answer is, Schema location is used to store schema inferred by AUTO LOADER, so the next time AUTO LOADER runs faster as does not need to infer the schema every single time by trying to use the last known schema.
Auto Loader samples the first 50 GB or 1000 files that it discovers, whichever limit is crossed first. To avoid incurring this inference cost at every stream start up, and to be able to provide a stable schema across stream restarts, you must set the option cloudFiles.schemaLocation. Auto Loader creates a hidden directory _schemas at this location to track schema changes to the input data over time.
The below link contains detailed documentation on different options
Auto Loader options | Databricks on AWS
NEW QUESTION 19
What is the main difference between the bronze layer and silver layer in a medallion architecture?
- A. Bronze is raw copy of ingested data, silver contains data with production schema and optimized for ELT/ETL throughput
- B. Silver may contain aggregated data
- C. Duplicates are removed in bronze, schema is applied in silver
- D. Bad data is filtered in Bronze, silver is a copy of bronze data
Answer: A
Explanation:
Explanation
Medallion Architecture - Databricks
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
NEW QUESTION 20
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?
- A. Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.
- B. Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
- C. Records that violate the expectation are added to the target dataset and flagged as in-valid in a field added to the target dataset.
- D. Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.
- E. Records that violate the expectation cause the job to fail
Answer: E
Explanation:
Explanation
The answer is Records that violate the expectation cause the job to fail.
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
Invalid records:
Use the expect operator when you want to keep records that violate the expectation. Records that violate the expectation are added to the target dataset along with valid records:
SQL
CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01')
Drop invalid records:
Use the expect or drop operator to prevent the processing of invalid records. Records that violate the expectation are dropped from the target dataset:
SQL
CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION DROP ROW Fail on invalid records:
When invalid records are unacceptable, use the expect or fail operator to halt execution immediately when a record fails validation. If the operation is a table update, the system atomically rolls back the transaction:
SQL
CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION FAIL UP-DATE
NEW QUESTION 21
What is the best way to query external csv files located on DBFS Storage to inspect the data using SQL?
- A. SELECT * FROM CSV. 'dbfs:/location/csv_files/'
- B. SELECT * FROM 'dbfs:/location/csv_files/' USING CSV
- C. You can not query external files directly, us COPY INTO to load the data into a table first
- D. SELECT * FROM 'dbfs:/location/csv_files/' FORMAT = 'CSV'
- E. SELECT CSV. * from 'dbfs:/location/csv_files/'
Answer: A
Explanation:
Explanation
Answer is, SELECT * FROM CSV. 'dbfs:/location/csv_files/'
you can query external files stored on the storage using below syntax
SELECT * FROM format.`/Location`
format - CSV, JSON, PARQUET, TEXT
NEW QUESTION 22
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 DROP ROW What is the expected behavior when a batch of data containing data that violates these constraints is processed?
- A. Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.
- B. Records that violate the expectation cause the job to fail.
- C. Records that violate the expectation are added to the target dataset and flagged as in-valid in a field added to the target dataset.
- D. Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
- E. Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.
Answer: D
Explanation:
Explanation
The answer is Records that violate the expectation are dropped from 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
NEW QUESTION 23
Two junior data engineers are authoring separate parts of a single data pipeline notebook. They are working on
separate Git branches so they can pair program on the same notebook simultaneously. A senior data engineer
experienced in Databricks suggests there is a better alternative for this type of collaboration.
Which of the following supports the senior data engineer's claim?
- A. Databricks Notebooks support real-time co-authoring on a single notebook
- B. Databricks Notebooks support the creation of interactive data visualizations
- C. Databricks Notebooks support commenting and notification comments
- D. Databricks Notebooks support the use of multiple languages in the same notebook
- E. Databricks Notebooks support automatic change-tracking and versioning
Answer: A
NEW QUESTION 24
You were asked to create or overwrite an existing delta table to store the below transaction data.
- A. 1.CREATE OR REPLACE DELTA TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int) - B. 1.CREATE OR REPLACE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int) - C. 1.CREATE OR REPLACE TABLE IF EXISTS transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
5.FORMAT DELTA - D. 1.CREATE IF EXSITS REPLACE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
Answer: B
Explanation:
Explanation
The answer is
1.CREATE OR REPLACE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
When creating a table in Databricks by default the table is stored in DELTA format.
NEW QUESTION 25
One of the queries in the Databricks SQL Dashboard takes a long time to refresh, which of the be-low steps can be taken to identify the root cause of this issue?
- A. Select the SQL endpoint cluster, spark UI, SQL tab to see the execution plan and time spent in each step
- B. Run optimize and Z ordering
- C. Change the Spot Instance Policy from "Cost optimized" to "Reliability Optimized."
- D. Restart the SQL endpoint
- E. Use Query History, to view queries and select query, and check query profile to time spent in each step
Answer: E
Explanation:
Explanation
The answer is, Use Query History, to view queries and select query, and check the query profile to see time spent in each step.
Here is the view of the query profile, for more info use the link
https://docs.microsoft.com/en-us/azure/databricks/sql/admin/query-profile As you can see here Databricks SQL query profile is much different to Spark UI and provides much more clear information on how time is being spent on different queries and time it spent on each step.
Graphical user interface, application Description automatically generated
NEW QUESTION 26
Which of the following statements are correct on how Delta Lake implements a lake house?
- A. Delta lake always stores meta data in memory vs storage
- B. Delta lake uses open source, open format, optimized cloud storage and scalable meta data
- C. Delta lake uses a proprietary format to write data, optimized for cloud storage
- D. Delta lake stores data and meta data in computes memory
- E. Using Apache Hadoop on cloud object storage
Answer: B
Explanation:
Explanation
Delta lake is
* Open source
* Builds up on standard data format
* Optimized for cloud object storage
* Built for scalable metadata handling
Delta lake is not
* Proprietary technology
* Storage format
* Storage medium
* Database service or data warehouse
NEW QUESTION 27
The data analyst team had put together queries that identify items that are out of stock based on orders and replenishment but when they run all together for final output the team noticed it takes a really long time, you were asked to look at the reason why queries are running slow and identify steps to improve the performance and when you looked at it you noticed all the code queries are running sequentially and using a SQL endpoint cluster. Which of the following steps can be taken to resolve the issue?
Here is the example query
1.--- Get order summary
2.create or replace table orders_summary
3.as
4.select product_id, sum(order_count) order_count
5.from
6. (
7. select product_id,order_count from orders_instore
8. union all
9. select product_id,order_count from orders_online
10. )
11.group by product_id
12.-- get supply summary
13.create or repalce tabe supply_summary
14.as
15.select product_id, sum(supply_count) supply_count
16.from supply
17.group by product_id
18.
19.-- get on hand based on orders summary and supply summary
20.
21.with stock_cte
22.as (
23.select nvl(s.product_id,o.product_id) as product_id,
24. nvl(supply_count,0) - nvl(order_count,0) as on_hand
25.from supply_summary s
26.full outer join orders_summary o
27. on s.product_id = o.product_id
28.)
29.select *
30.from
31.stock_cte
32.where on_hand = 0
- A. Increase the cluster size of the SQL endpoint.
- B. Turn on the Serverless feature for the SQL endpoint and change the Spot Instance Pol-icy to "Reliability Optimized."
- C. Increase the maximum bound of the SQL endpoint's scaling range.
- D. Turn on the Auto Stop feature for the SQL endpoint.
- E. Turn on the Serverless feature for the SQL endpoint.
Answer: A
Explanation:
Explanation
The answer is to increase the cluster size of the SQL Endpoint, here queries are running sequentially and since the single query can not span more than one cluster adding more clusters won't improve the query but rather increasing the cluster size will improve performance so it can use additional compute in a warehouse.
In the exam please note that additional context will not be given instead you have to look for cue words or need to understand if the queries are running sequentially or concurrently. if the que-ries are running sequentially then scale up(more nodes) if the queries are running concurrently (more users) then scale out(more clusters).
Below is the snippet from Azure, as you can see by increasing the cluster size you are able to add more worker nodes.
SQL endpoint scales horizontally(scale-out) and vertically (scale-up), you have to understand when to use what.
Scale-up-> Increase the size of the cluster from x-small to small, to medium, X Large....
If you are trying to improve the performance of a single query having additional memory, additional nodes and cpu in the cluster will improve the performance.
Scale-out -> Add more clusters, change max number of clusters
If you are trying to improve the throughput, being able to run as many queries as possible then having an additional cluster(s) will improve the performance.
SQL endpoint
A picture containing diagram Description automatically generated
NEW QUESTION 28
Which of the following describes a scenario in which a data engineer will want to use a Job cluster instead of
an all-purpose cluster?
- A. A data team needs to collaborate on the development of a machine learning model
- B. An ad-hoc analytics report needs to be developed while minimizing compute costs
- C. A Databricks SQL query needs to be scheduled for upward reporting
- D. A data engineer needs to manually investigate a production error
- E. An automated workflow needs to be run every 30 minutes
Answer: E
NEW QUESTION 29
Question-3: In machine learning, feature hashing, also known as the hashing trick (by analogy to the kernel
trick), is a fast and space-efficient way of vectorizing features (such as the words in a language), i.e., turning
arbitrary features into indices in a vector or matrix. It works by applying a hash function to the features and
using their hash values modulo the number of features as indices directly, rather than looking the indices up in
an associative array. So what is the primary reason of the hashing trick for building classifiers?
- A. Noisy features are removed
- B. It reduces the non-significant features e.g. punctuations
- C. It creates the smaller models
- D. It requires the lesser memory to store the coefficients for the model
Answer: D
Explanation:
Explanation
This hashed feature approach has the distinct advantage of requiring less memory and one less pass through
the training data, but it can make it much harder to reverse engineer vectors to determine which original
feature mapped to a vector location. This is because multiple features may hash to the same location. With
large vectors or with multiple locations per feature, this isn't a problem for accuracy but it can make it hard to
understand what a classifier is doing.
Models always have a coefficient per feature, which are stored in memory during model building. The hashing
trick collapses a high number of features to a small number which reduces the number of coefficients and thus
memory requirements. Noisy features are not removed; they are combined with other features and so still have
an impact.
The validity of this approach depends a lot on the nature of the features and problem domain; knowledge of
the domain is important to understand whether it is applicable or will likely produce poor results. While
hashing features may produce a smaller model, it will be one built from odd combinations of real-world
features, and so will be harder to interpret.
An additional benefit of feature hashing is that the unknown and unbounded vocabularies typical of word-like
variables aren't a problem.
NEW QUESTION 30
......
Databricks-Certified-Professional-Data-Engineer Cert Guide PDF 100% Cover Real Exam Questions: https://www.torrentexam.com/Databricks-Certified-Professional-Data-Engineer-exam-latest-torrent.html
