2024 Databricks-Certified-Professional-Data-Engineer exam torrent Databricks-Certified-Professional-Data-Engineer Study Guide
Easily pass Databricks-Certified-Professional-Data-Engineer Exam with our Dumps & PDF Test Engine
Databricks Certified Professional Data Engineer exam is a comprehensive assessment that covers a wide range of topics related to data engineering using Databricks. Databricks-Certified-Professional-Data-Engineer exam consists of multiple-choice questions and performance-based tasks that require candidates to demonstrate their ability to design, build, and optimize data pipelines using Databricks. Databricks-Certified-Professional-Data-Engineer exam is available online and can be taken from anywhere in the world, making it a convenient option for data professionals who want to validate their expertise in Databricks. Upon successful completion of the exam, candidates will receive a Databricks Certified Professional Data Engineer certification, which will demonstrate their proficiency in data engineering using Databricks.
NEW QUESTION # 49
What is the main difference between AUTO LOADER and COPY INTO?
- A. AUTO LOADER supports schema evolution.
- B. AUTO LOADER supports reading data from Apache Kafka
- C. AUTO LOADER Supports file notification when performing incremental loads.
- D. COPY INTO supports file notification when performing incremental loads.
- E. COPY INTO supports schema evolution.
Answer: C
Explanation:
Explanation
Auto loader supports both directory listing and file notification but COPY INTO only supports di-rectory listing.
Auto loader file notification will automatically set up a notification service and queue service that subscribe to file events from the input directory in cloud object storage like Azure blob storage or S3. File notification mode is more performant and scalable for large input directories or a high volume of files.
Auto Loader and Cloud Storage Integration
Auto Loader supports a couple of ways to ingest data incrementally
1.Directory listing - List Directory and maintain the state in RocksDB, supports incremental file listing
2.File notification - Uses a trigger+queue to store the file notification which can be later used to retrieve the file, unlike Directory listing File notification can scale up to millions of files per day.
[OPTIONAL]
Auto Loader vs COPY INTO?
Auto Loader
Auto Loader incrementally and efficiently processes new data files as they arrive in cloud storage without any additional setup. Auto Loader provides a new Structured Streaming source called cloudFiles. Given an input directory path on the cloud file storage, the cloudFiles source automatically processes new files as they arrive, with the option of also processing existing files in that directory.
When to use Auto Loader instead of the COPY INTO?
*You want to load data from a file location that contains files in the order of millions or higher. Auto Loader can discover files more efficiently than the COPY INTO SQL command and can split file processing into multiple batches.
*You do not plan to load subsets of previously uploaded files. With Auto Loader, it can be more difficult to reprocess subsets of files. However, you can use the COPY INTO SQL command to reload subsets of files while an Auto Loader stream is simultaneously running.
Auto loader file notification will automatically set up a notification service and queue service that subscribe to file events from the input directory in cloud object storage like Azure blob storage or S3. File notification mode is more performant and scalable for large input directories or a high volume of files.
Here are some additional notes on when to use COPY INTO vs Auto Loader
When to use COPY INTO
https://docs.databricks.com/delta/delta-ingest.html#copy-into-sql-command When to use Auto Loader
https://docs.databricks.com/delta/delta-ingest.html#auto-loader
NEW QUESTION # 50
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/' FORMAT = 'CSV'
- C. SELECT CSV. * from 'dbfs:/location/csv_files/'
- D. You can not query external files directly, us COPY INTO to load the data into a table first
- E. SELECT * FROM 'dbfs:/location/csv_files/' USING CSV
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 # 51
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 maximum bound of the SQL endpoint's scaling range.
- B. Turn on the Auto Stop feature for the SQL endpoint.
- C. Increase the cluster size of the SQL endpoint.
- D. Turn on the Serverless feature for the SQL endpoint and change the Spot Instance Pol-icy to "Reliability Optimized."
- E. Turn on the Serverless feature for the SQL endpoint.
Answer: C
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 # 52
You had worked with the Data analysts team to set up a SQL Endpoint(SQL warehouse) point so they can easily query and analyze data in the gold layer, but once they started consuming the SQL Endpoint(SQL warehouse) you noticed that during the peak hours as the number of users increase you are seeing queries taking longer to finish, which of the following steps can be taken to resolve the issue?
*Please note Databricks recently renamed SQL endpoint to SQL warehouse.
- A. They can increase the maximum bound of the SQL endpoint(SQL warehouse) 's scaling range.
- B. They can turn on the Serverless feature for the SQL endpoint(SQL warehouse).
- C. They can turn on the Serverless feature for the SQL endpoint(SQL warehouse) and change the Spot Instance Policy from "Cost optimized" to "Reliability Optimized."
- D. They can turn on the Auto Stop feature for the SQL endpoint(SQL warehouse) .
- E. They can increase the cluster size from 2X-Small to 4X-Large of the SQL end-point(SQL warehouse) .
Answer: A
Explanation:
Explanation
the answer is,
They can increase the maximum bound of the SQL endpoint's scaling range, when you increase the maximum bound you can add more clusters to the warehouse which can then run additional queries that are waiting in the queue to run, focus on the below explanation that talks about Scale-out.
The question is looking to test your ability to know how to scale a SQL Endpoint(SQL Warehouse) and you have to look for cue words or need to understand if the queries are running sequentially or concurrently. if the queries are running sequentially then scale up(Size of the cluster from 2X-Small to 4X-Large) if the queries are running concurrently or with more users then scale out(add more clusters).
SQL Endpoint(SQL Warehouse) Overview: (Please read all of the below points and the below diagram to understand )
1.A SQL Warehouse should have at least one cluster
2.A cluster comprises one driver node and one or many worker nodes
3.No of worker nodes in a cluster is determined by the size of the cluster (2X -Small ->1 worker, X-Small ->2 workers.... up to 4X-Large -> 128 workers) this is called Scale up
4.A single cluster irrespective of cluster size(2X-Smal.. to ...4XLarge) can only run 10 queries at any given time if a user submits 20 queries all at once to a warehouse with 3X-Large cluster size and cluster scaling (min
1, max1) while 10 queries will start running the remaining 10 queries wait in a queue for these 10 to finish.
5.Increasing the Warehouse cluster size can improve the performance of a query, example if a query runs for 1 minute in a 2X-Small warehouse size, it may run in 30 Seconds if we change the warehouse size to X-Small.
this is due to 2X-Small has 1 worker node and X-Small has 2 worker nodes so the query has more tasks and runs faster (note: this is an ideal case example, the scalability of a query performance depends on many factors, it can not always be linear)
6.A warehouse can have more than one cluster this is called Scale out. If a warehouse is con-figured with X-Small cluster size with cluster scaling(Min1, Max 2) Databricks spins up an additional cluster if it detects queries are waiting in the queue, If a warehouse is configured to run 2 clusters(Min1, Max 2), and let's say a user submits 20 queries, 10 queriers will start running and holds the remaining in the queue and databricks will automatically start the second cluster and starts redirecting the 10 queries waiting in the queue to the second cluster.
7.A single query will not span more than one cluster, once a query is submitted to a cluster it will remain in that cluster until the query execution finishes irrespective of how many clusters are available to scale.
Please review the below diagram to understand the above concepts:
SQL endpoint(SQL Warehouse) scales horizontally(scale-out) and vertical (scale-up), you have to understand when to use what.
Scale-out -> to add more clusters for a SQL endpoint, 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.
Databricks SQL automatically scales as soon as it detects queries are in queuing state, in this example scaling is set for min 1 and max 3 which means the warehouse can add three clusters if it detects queries are waiting.
During the warehouse creation or after you have the ability to change the warehouse size (2X-Small....to
...4XLarge) to improve query performance and the maximize scaling range to add more clusters on a SQL Endpoint(SQL Warehouse) scale-out, if you are changing an existing warehouse you may have to restart the warehouse to make the changes effective.
How do you know how many clusters you need(How to set Max cluster size)?
When you click on an existing warehouse and select the monitoring tab, you can see warehouse utilization information(see below), there are two graphs that provide important information on how the warehouse is being utilized, if you see queries are being queued that means your warehouse can benefit from additional clusters. Please review the additional DBU cost associated with adding clusters so you can take a well balanced decision between cost and performance.
NEW QUESTION # 53
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?
- A. 1.order_date = dbutils.widgets.get("widget_order_date")
2.
3.spark.sql(f"SELECT * FROM sales WHERE orderDate = '{order_date}' ")
(Correct) - B. 1.order_date = dbutils.widgets.get("widget_order_date")
2.
3.spark.sql(f"SELECT * FROM sales WHERE orderDate = 'f{order_date }'") - C. 1.order_date = dbutils.widgets.get("widget_order_date")
2.
3.spark.sql(f"SELECT * FROM sales WHERE orderDate = '${order_date }' ") - D. 1.order_date = dbutils.widgets.get("widget_order_date")
2.
3.spark.sql(f"SELECT * FROM sales WHERE orderDate = 'order_date' ") - E. 1.order_date = dbutils.widgets.get("widget_order_date")
2.
3.spark.sql("SELECT * FROM sales WHERE orderDate = order_date")
Answer: A
NEW QUESTION # 54
The data engineering team maintains the following code:
Assuming that this code produces logically correct results and the data in the source table has been de-duplicated and validated, which statement describes what will occur when this code is executed?
- A. An incremental job will detect if new rows have been written to the silver_customer_sales table; if new rows are detected, all aggregates will be recalculated and used to overwrite the gold_customer_lifetime_sales_summary table.
- B. An incremental job will leverage running information in the state store to update aggregate values in the gold_customer_lifetime_sales_summary table.
- C. The silver_customer_sales table will be overwritten by aggregated values calculated from all records in the gold_customer_lifetime_sales_summary table as a batch job.
- D. The gold_customer_lifetime_sales_summary table will be overwritten by aggregated values calculated from all records in the silver_customer_sales table as a batch job.
- E. A batch job will update the gold_customer_lifetime_sales_summary table, replacing only those rows that have different values than the current version of the table, using customer_id as the primary key.
Answer: D
Explanation:
This code is using the pyspark.sql.functions library to group the silver_customer_sales table by customer_id and then aggregate the data using the minimum sale date, maximum sale total, and sum of distinct order ids.
The resulting aggregated data is then written to the gold_customer_lifetime_sales_summary table, overwriting any existing data in that table. This is a batch job that does not use any incremental or streaming logic, and does not perform any merge or update operations. Therefore, the code will overwrite the gold table with the aggregated values from the silver table every time it is executed. References:
* https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-dataframes-python.html
* https://docs.databricks.com/spark/latest/dataframes-datasets/transforming-data-with-dataframes.html
* https://docs.databricks.com/spark/latest/dataframes-datasets/aggregating-data-with-dataframes.html
NEW QUESTION # 55
Which of the following data workloads will utilize a gold table as its source?
- A. A job that enriches data by parsing its timestamps into a human-readable format
- B. A job that ingests raw data from a streaming source into the Lakehouse
- C. A job that aggregates cleaned data to create standard summary statistics
- D. A job that queries aggregated data that already feeds into a dashboard
- E. A job that cleans data by removing malformatted records
Answer: D
Explanation:
Explanation
The answer is, A job that queries aggregated data that already feeds into a dashboard The gold layer is used to store aggregated data, which are typically used for dashboards and reporting.
Review the below link for more info,
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.
Purpose of each layer in medallion architecture
NEW QUESTION # 56
All records from an Apache Kafka producer are being ingested into a single Delta Lake table with the following schema:
key BINARY, value BINARY, topic STRING, partition LONG, offset LONG, timestamp LONG There are 5 unique topics being ingested. Only the "registration" topic contains Personal Identifiable Information (PII). The company wishes to restrict access to PII. The company also wishes to only retain records containing PII in this table for 14 days after initial ingestion. However, for non-PII information, it would like to retain these records indefinitely.
Which of the following solutions meets the requirements?
- A. Data should be partitioned by the registration field, allowing ACLs and delete statements to be set for the PII directory.
- B. Because the value field is stored as binary data, this information is not considered PII and no special precautions should be taken.
- C. Data should be partitioned by the topic field, allowing ACLs and delete statements to leverage partition boundaries.
- D. All data should be deleted biweekly; Delta Lake's time travel functionality should be leveraged to maintain a history of non-PII information.
- E. Separate object storage containers should be specified based on the partition field, allowing isolation at the storage level.
Answer: C
Explanation:
Partitioning the data by the topic field allows the company to apply different access control policies and retention policies for different topics. For example, the company can use the Table Access Control feature to grant or revoke permissions to the registration topic based on user roles or groups. The company can also use the DELETE command to remove records from the registration topic that are older than 14 days, while keeping the records from other topics indefinitely. Partitioning by the topic field also improves the performance of queries that filter by the topic field, as they can skip reading irrelevant partitions. References:
* Table Access Control: https://docs.databricks.com/security/access-control/table-acls/index.html
* DELETE: https://docs.databricks.com/delta/delta-update.html#delete-from-a-table
NEW QUESTION # 57
Which of the following results in the creation of an external table?
- A. CREATE TABLE transactions (id int, desc string) LOCATION '/mnt/delta/transactions'
- B. CREATE TABLE transactions (id int, desc string)
- C. CREATE TABLE transactions (id int, desc string) TYPE EXTERNAL
- D. CREATE EXTERNAL TABLE transactions (id int, desc string)
- E. CREATE TABLE transactions (id int, desc string) USING DELTA LOCATION EX-TERNAL
Answer: A
Explanation:
Explanation
Answer is CREATE TABLE transactions (id int, desc string) USING DELTA LOCATION
'/mnt/delta/transactions'
Anytime a table is created using Location it is considered an external table, below is the current syntax.
Syntax
CREATE TABLE table_name ( column column_data_type...) USING format LOCATION "dbfs:/"
NEW QUESTION # 58
A Delta Lake table representing metadata about content posts from users has the following schema:
user_id LONG, post_text STRING, post_id STRING, longitude FLOAT, latitude FLOAT, post_time TIMESTAMP, date DATE This table is partitioned by the date column. A query is run with the following filter:
longitude < 20 & longitude > -20
Which statement describes how data will be filtered?
- A. Statistics in the Delta Log will be used to identify partitions that might Include files in the filtered range.
- B. The Delta Engine will use row-level statistics in the transaction log to identify the flies that meet the filter criteria.
- C. The Delta Engine will scan the parquet file footers to identify each row that meets the filter criteria.
- D. No file skipping will occur because the optimizer does not know the relationship between the partition column and the longitude.
- E. Statistics in the Delta Log will be used to identify data files that might include records in the filtered range.
Answer: E
Explanation:
This is the correct answer because it describes how data will be filtered when a query is run with the following filter: longitude < 20 & longitude > -20. The query is run on a Delta Lake table that has the following schema:
user_id LONG, post_text STRING, post_id STRING, longitude FLOAT, latitude FLOAT, post_time TIMESTAMP, date DATE. This table is partitioned by the date column. When a query is run on a partitioned Delta Lake table, Delta Lake uses statistics in the Delta Log to identify data files that might include records in the filtered range. The statistics include information such as min and max values for each column in each data file. By using these statistics, Delta Lake can skip reading data files that do not match the filter condition, which can improve query performance and reduce I/O costs. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Data skipping" section.
NEW QUESTION # 59
Which of the following Structured Streaming queries is performing a hop from a Bronze table to a Silver
table?
- A. 1. (spark.table("sales")
2. .groupBy("store")
3. .agg(sum("sales"))
4. .writeStream
5. .option("checkpointLocation", checkpointPath)
6. .outputMode("complete")
7. .table("aggregatedSales")
8.) - B. 1. (spark.table("sales")
2. .withColumn("avgPrice", col("sales") / col("units"))
3. .writeStream
4. .option("checkpointLocation", checkpointPath)
5. .outputMode("append")
6. .table("cleanedSales")
7.) - C. 1. (spark.readStream.load(rawSalesLocation)
2. .writeStream
3. .option("checkpointLocation", checkpointPath)
4. .outputMode("append")
5. .table("uncleanedSales")
6. ) - D. 1. (spark.table("sales")
2. .agg(sum("sales"),
3. sum("units"))
4. .writeStream
5. .option("checkpointLocation", checkpointPath)
6. .outputMode("complete")
7. .table("aggregatedSales")
8. ) - E. 1. (spark.read.load(rawSalesLocation)
2. .writeStream
3. .option("checkpointLocation", checkpointPath)
4. .outputMode("append")
5. .table("uncleanedSales")
6. )
Answer: B
NEW QUESTION # 60
A data ingestion task requires a one-TB JSON dataset to be written out to Parquet with a target part-file size of
512 MB. Because Parquet is being used instead of Delta Lake, built-in file-sizing features such as Auto-Optimize & Auto-Compaction cannot be used.
Which strategy will yield the best performance without shuffling data?
- A. Set spark.sql.shuffle.partitions to 512, ingest the data, execute the narrow transformations, and then write to parquet.
- B. Set spark.sql.shuffle.partitions to 2,048 partitions (1TB*1024*1024/512), ingest the data, execute the narrow transformations, optimize the data by sorting it (which automatically repartitions the data), and then write to parquet.
- C. Set spark.sql.files.maxPartitionBytes to 512 MB, ingest the data, execute the narrow transformations, and then write to parquet.
- D. Set spark.sql.adaptive.advisoryPartitionSizeInBytes to 512 MB bytes, ingest the data, execute the narrow transformations, coalesce to 2,048 partitions (1TB*1024*1024/512), and then write to parquet.
- E. Ingest the data, execute the narrow transformations, repartition to 2,048 partitions (1TB*
1024*1024/512), and then write to parquet.
Answer: B
Explanation:
The key to efficiently converting a large JSON dataset to Parquet files of a specific size without shuffling data lies in controlling the size of the output files directly.
* Setting spark.sql.files.maxPartitionBytes to 512 MB configures Spark to process data in chunks of
512 MB. This setting directly influences the size of the part-files in the output, aligning with the target file size.
* Narrow transformations (which do not involve shuffling data across partitions) can then be applied to this data.
* Writing the data out to Parquet will result in files that are approximately the size specified by spark.sql.files.maxPartitionBytes, in this case, 512 MB.
* The other options involve unnecessary shuffles or repartitions (B, C, D) or an incorrect setting for this specific requirement (E).
References:
* Apache Spark Documentation: Configuration - spark.sql.files.maxPartitionBytes
* Databricks Documentation on Data Sources: Databricks Data Sources Guide
NEW QUESTION # 61
You are asked to setup two tasks in a databricks job, the first task runs a notebook to download the data from a remote system, and the second task is a DLT pipeline that can process this data, how do you plan to configure this in Jobs UI
- A. Add first step in the DLT pipeline and run the DLT pipeline as triggered mode in JOBS UI
- B. Single job cannot have a notebook task and DLT Pipeline task, use two different jobs with linear dependency.
- C. Single job can be used to setup both notebook and DLT pipeline, use two different tasks with linear dependency.
- D. Jobs UI does not support DTL pipeline, setup the first task using jobs UI and setup the DLT to run in trigger mode.
- E. Jobs UI does not support DTL pipeline, setup the first task using jobs UI and setup the DLT to run in continuous mode.
Answer: C
Explanation:
Explanation
The answer is Single job can be used to set up both notebook and DLT pipeline, use two different tasks with linear dependency, Here is the JOB UI
1.Create a notebook task
2.Create DLT task
a.add notebook task as dependency
3.Final view
Create the notebook task
Graphical user interface, text, application, email Description automatically generated
DLT task
Graphical user interface, text, application, email Description automatically generated
Final view
Graphical user interface, text, application, PowerPoint Description automatically generated
Bottom of Form
Top of Form
NEW QUESTION # 62
Which statement describes the correct use of pyspark.sql.functions.broadcast?
- A. It marks a column as small enough to store in memory on all executors, allowing a broadcast join.
- B. It caches a copy of the indicated table on attached storage volumes for all active clusters within a Databricks workspace.
- C. It marks a column as having low enough cardinality to properly map distinct values to available partitions, allowing a broadcast join.
- D. It caches a copy of the indicated table on all nodes in the cluster for use in all future queries during the cluster lifetime.
- E. It marks a DataFrame as small enough to store in memory on all executors, allowing a broadcast join.
Answer: E
Explanation:
Explanation
https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.broadcast.html
NEW QUESTION # 63
A table nameduser_ltvis being used to create a view that will be used by data analysts on various teams. Users in the workspace are configured into groups, which are used for setting up data access using ACLs.
Theuser_ltvtable has the following schema:
email STRING, age INT, ltv INT
The following view definition is executed:
An analyst who is not a member of the marketing group executes the following query:
SELECT * FROM email_ltv
Which statement describes the results returned by this query?
- A. Three columns will be returned, but one column will be named "redacted" and contain only null values.
- B. The email, age. and ltv columns will be returned with the values in user ltv.
- C. The email and ltv columns will be returned with the values in user itv.
- D. Only the email and ltv columns will be returned; the email column will contain the string
"REDACTED" in each row. - E. Only the email and itv columns will be returned; the email column will contain all null values.
Answer: D
Explanation:
The code creates a view called email_ltv that selects the email and ltv columns from a table called user_ltv, which has the following schema: email STRING, age INT, ltv INT. The code also uses the CASE WHEN expression to replace the email values with the string "REDACTED" if the user is not a member of the marketing group. The user who executes the query is not a member of the marketing group, so they will only see the email and ltv columns, and the email column will contain the string "REDACTED" in each row.
Verified References: [Databricks Certified Data Engineer Professional], under "Lakehouse" section; Databricks Documentation, under "CASE expression" section.
NEW QUESTION # 64
Which of the following techniques structured streaming uses to create an end-to-end fault toler-ance?
- A. Write ahead logging and water marking
- B. Checkpointing and idempotent sinks
- C. Checkpointing and Water marking
- D. Write ahead logging and idempotent sinks
- E. Stream will failover to available nodes in the cluste
Answer: B
Explanation:
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.
NEW QUESTION # 65
Which of the following SQL keywords can be used to append new rows to an existing Delta table?
- A. DELETE
- B. COPY
- C. INSERT INTO
- D. UNION
- E. UPDATE
Answer: C
NEW QUESTION # 66
A data engineering team needs to query a Delta table to extract rows that all meet the same condi-tion.
However, the team has noticed that the query is running slowly. The team has already tuned the size of the
data files. Upon investigating, the team has concluded that the rows meeting the condition are sparsely located
throughout each of the data files.
Based on the scenario, which of the following optimization techniques could speed up the query?
- A. Tuning the file size
- B. Z-Ordering
- C. Data skipping
- D. Write as a Parquet file
- E. Bin-packing
Answer: B
NEW QUESTION # 67
A junior data engineer seeks to leverage Delta Lake's Change Data Feed functionality to create a Type 1 table representing all of the values that have ever been valid for all rows in abronzetable created with the propertydelta.enableChangeDataFeed = true. They plan to execute the following code as a daily job:
Which statement describes the execution and results of running the above query multiple times?
- A. Each time the job is executed, only those records that have been inserted or updated since the last execution will be appended to the target table giving the desired result.
- B. Each time the job is executed, the entire available history of inserted or updated records will be appended to the target table, resulting in many duplicate entries.
- C. Each time the job is executed, the differences between the original and current versions are calculated; this may result in duplicate entries for some records.
- D. Each time the job is executed, newly updated records will be merged into the target table, overwriting previous values with the same primary keys.
- E. Each time the job is executed, the target table will be overwritten using the entire history of inserted or updated records, giving the desired result.
Answer: B
Explanation:
Explanation
Reading table's changes, captured by CDF, using spark.read means that you are reading them as a static source. So, each time you run the query, all table's changes (starting from the specified startingVersion) will be read.
NEW QUESTION # 68
A junior data engineer has ingested a JSON file into a table raw_table with the following schema:
1. cart_id STRING,
2. items ARRAY<item_id:STRING>
The junior data engineer would like to unnest the items column in raw_table to result in a new table with the
following schema:
1.cart_id STRING,
2.item_id STRING
Which of the following commands should the junior data engineer run to complete this task?
- A. 1. SELECT cart_id, filter(items) AS item_id
2. FROM raw_table; - B. 1. SELECT cart_id, flatten(items) AS item_id
2. FROM raw_table; - C. 1. SELECT cart_id, slice(items) AS item_id
2. FROM raw_table; - D. 1. SELECT cart_id, reduce(items) AS item_id
2. FROM raw_table; - E. 1. SELECT cart_id, explode(items) AS item_id
2. FROM raw_table;
Answer: E
NEW QUESTION # 69
A junior data engineer has been asked to develop a streaming data pipeline with a grouped aggregation using DataFrame df. The pipeline needs to calculate the average humidity and average temperature for each non-overlapping five-minute interval. Events are recorded once per minute per device.
Streaming DataFrame df has the following schema:
"device_id INT, event_time TIMESTAMP, temp FLOAT, humidity FLOAT"
Code block:
Choose the response that correctly fills in the blank within the code block to complete this task.
- A. window("event_time", "10 minutes").alias("time")
- B. to_interval("event_time", "5 minutes").alias("time")
- C. lag("event_time", "10 minutes").alias("time")
- D. "event_time"
- E. window("event_time", "5 minutes").alias("time")
Answer: E
Explanation:
This is the correct answer because the window function is used to group streaming data by time intervals. The window function takes two arguments: a time column and a window duration. The window duration specifies how long each window is, and must be a multiple of 1 second. In this case, the window duration is "5 minutes", which means each window will cover a non-overlapping five-minute interval. The window function also returns a struct column with two fields: start and end, which represent the start and end time of each window. The alias function is used to rename the struct column as "time". Verified References: [Databricks Certified Data Engineer Professional], under "Structured Streaming" section; Databricks Documentation, under "WINDOW" section.
https://www.databricks.com/blog/2017/05/08/event-time-aggregation-watermarking-apache-sparks-structured-str
NEW QUESTION # 70
A data ingestion task requires a one-TB JSON dataset to be written out to Parquet with a target part-file size of
512 MB. Because Parquet is being used instead of Delta Lake, built-in file-sizing features such as Auto-Optimize & Auto-Compaction cannot be used.
Which strategy will yield the best performance without shuffling data?
- A. Set spark.sql.shuffle.partitions to 512, ingest the data, execute the narrow transformations, and then write to parquet.
- B. Set spark.sql.shuffle.partitions to 2,048 partitions (1TB*1024*1024/512), ingest the data, execute the narrow transformations, optimize the data by sorting it (which automatically repartitions the data), and then write to parquet.
- C. Set spark.sql.files.maxPartitionBytes to 512 MB, ingest the data, execute the narrow transformations, and then write to parquet.
- D. Set spark.sql.adaptive.advisoryPartitionSizeInBytes to 512 MB bytes, ingest the data, execute the narrow transformations, coalesce to 2,048 partitions (1TB*1024*1024/512), and then write to parquet.
- E. Ingest the data, execute the narrow transformations, repartition to 2,048 partitions (1TB*
1024*1024/512), and then write to parquet.
Answer: B
Explanation:
The key to efficiently converting a large JSON dataset to Parquet files of a specific size without shuffling data lies in controlling the size of the output files directly.
* Settingspark.sql.files.maxPartitionBytesto 512 MB configures Spark to process data in chunks of 512 MB. This setting directly influences the size of the part-files in the output, aligning with the target file size.
* Narrow transformations (which do not involve shuffling data across partitions) can then be applied to this data.
* Writing the data out to Parquet will result in files that are approximately the size specified by spark.sql.files.maxPartitionBytes, in this case, 512 MB.
* The other options involve unnecessary shuffles or repartitions (B, C, D) or an incorrect setting for this specific requirement (E).
References:
* Apache Spark Documentation: Configuration - spark.sql.files.maxPartitionBytes
* Databricks Documentation on Data Sources: Databricks Data Sources Guide
NEW QUESTION # 71
A table customerLocations exists with the following schema:
1. id STRING,
2. date STRING,
3. city STRING,
4. country STRING
A senior data engineer wants to create a new table from this table using the following command:
1. CREATE TABLE customersPerCountry AS
2. SELECT country,
3. COUNT(*) AS customers
4. FROM customerLocations
5. GROUP BY country;
A junior data engineer asks why the schema is not being declared for the new table. Which of the following
responses explains why declaring the schema is not necessary?
- A. CREATE TABLE AS SELECT statements result in tables where schemas are optional
- B. CREATE TABLE AS SELECT statements infer the schema by scanning the data
- C. CREATE TABLE AS SELECT statements adopt schema details from the source table and query
- D. CREATE TABLE AS SELECT statements assign all columns the type STRING
- E. CREATE TABLE AS SELECT statements result in tables that do not support schemas
Answer: C
NEW QUESTION # 72
A user wants to use DLT expectations to validate that a derived table report contains all records from the source, included in the table validation_copy.
The user attempts and fails to accomplish this by adding an expectation to the report table definition.
Which approach would allow using DLT expectations to validate all expected records are present in this table?
- A. Define a temporary table that perform a left outer join on validation_copy and report, and define an expectation that no report key values are null
- B. Define a view that performs a left outer join on validation_copy and report, and reference this view in DLT expectations for the report table
- C. Define a SQL UDF that performs a left outer join on two tables, and check if this returns null values for report key values in a DLT expectation for the report table.
- D. Define a function that performs a left outer join on validation_copy and report and report, and check against the result in a DLT expectation for the report table
Answer: B
Explanation:
To validate that all records from the source are included in the derived table, creating a view that performs a left outer join between the validation_copy table and the report table is effective. The view can highlight any discrepancies, such as null values in the report table's key columns, indicating missing records. This view can then be referenced in DLT (Delta Live Tables) expectations for the report table to ensure data integrity. This approach allows for a comprehensive comparison between the source and the derived table.
References:
* Databricks Documentation on Delta Live Tables and Expectations: Delta Live Tables Expectations
NEW QUESTION # 73
A Delta Lake table representing metadata about content from user has the following schema:
Based on the above schema, which column is a good candidate for partitioning the Delta Table?
- A. Post_id
- B. Post_time
- C. Date
- D. User_id
Answer: C
Explanation:
Partitioning a Delta Lake table improves query performance by organizing data into partitions based on the values of a column. In the given schema, the date column is a good candidate for partitioning for several reasons:
* Time-Based Queries: If queries frequently filter or group by date, partitioning by the date column can significantly improve performance by limiting the amount of data scanned.
* Granularity: The date column likely has a granularity that leads to a reasonable number of partitions (not too many and not too few). This balance is important for optimizing both read and write performance.
* Data Skew: Other columns like post_id or user_id might lead to uneven partition sizes (data skew), which can negatively impact performance.
Partitioning by post_time could also be considered, but typically date is preferred due to its more manageable granularity.
References:
* Delta Lake Documentation on Table Partitioning: Optimizing Layout with Partitioning
NEW QUESTION # 74
......
Databricks Certified Professional Data Engineer certification is designed for data engineers who work with the Databricks platform and have a deep understanding of data engineering concepts. Databricks Certified Professional Data Engineer Exam certification exam tests the candidate’s ability to design, build, and maintain data pipelines using Databricks, as well as their knowledge of data modeling, data warehousing, and data governance. Databricks Certified Professional Data Engineer Exam certification is recognized globally and indicates that the candidate has the skills and expertise needed to work with Databricks.
Databricks-Certified-Professional-Data-Engineer PDF Pass Leader, Databricks-Certified-Professional-Data-Engineer Latest Real Test: https://www.freecram.com/Databricks-certification/Databricks-Certified-Professional-Data-Engineer-exam-dumps.html
Valid Databricks-Certified-Professional-Data-Engineer Test Answers & Databricks-Certified-Professional-Data-Engineer Exam PDF: https://drive.google.com/open?id=1inlhtwx-7fP-0O7anpOxDKBRytKF2BP6