[2024年10月17日]Fast2test Databricks-Certified-Professional-Data-Engineer試験練習テスト問題（更新された122問あります)
合格させるDatabricks Databricks-Certified-Professional-Data-Engineer試験情報と無料練習テスト
質問 # 56
identifies if its needs to be converted to Fahrenheit or Celcius with a one-word letter F or C?
select udf_convert(60,'C') will result in 15.5
select udf_convert(10,'F') will result in 50
- A. 1.CREATE USER FUNCTION udf_convert(temp DOUBLE, measure STRING)
2.RETURNS DOUBLE
3.RETURN CASE WHEN measure == 'F' then (temp * 9/5) + 32
4. ELSE (temp - 33 ) * 5/9
5.END - B. 1.CREATE UDF FUNCTION udf_convert(temp DOUBLE, measure STRING)
2. RETURN CASE WHEN measure == 'F' then (temp * 9/5) + 32
3. ELSE (temp - 33 ) * 5/9
4. END - C. 1. CREATE UDF FUNCTION udf_convert(temp DOUBLE, measure STRING)
2. RETURNS DOUBLE
3. RETURN CASE WHEN measure == 'F' then (temp * 9/5) + 32
4. ELSE (temp - 33 ) * 5/9
5. END - D. 1.CREATE FUNCTION udf_convert(temp DOUBLE, measure STRING)
2.RETURNS DOUBLE
3.RETURN CASE WHEN measure == 'F' then (temp * 9/5) + 32
4. ELSE (temp - 33 ) * 5/9
5. END
(Correct) - E. 1.CREATE FUNCTION udf_convert(temp DOUBLE, measure STRING)
2.RETURN CASE WHEN measure == 'F' then (temp * 9/5) + 32
3. ELSE (temp - 33 ) * 5/9
4. END
正解：D
解説：
Explanation
The answer is
1.CREATE FUNCTION udf_convert(temp DOUBLE, measure STRING)
2.RETURNS DOUBLE
3.RETURN CASE WHEN measure == 'F' then (temp * 9/5) + 32
4. ELSE (temp - 33 ) * 5/9
5. END
質問 # 57
Which statement regarding stream-static joins and static Delta tables is correct?
- A. The checkpoint directory will be used to track updates to the static Delta table.
- B. Stream-static joins cannot use static Delta tables because of consistency issues.
- C. Each microbatch of a stream-static join will use the most recent version of the static Delta table as of each microbatch.
- D. The checkpoint directory will be used to track state information for the unique keys present in the join.
- E. Each microbatch of a stream-static join will use the most recent version of the static Delta table as of the job's initialization.
正解：C
解説：
This is the correct answer because stream-static joins are supported by Structured Streaming when one of the tables is a static Delta table. A static Delta table is a Delta table that is not updated by any concurrent writes, such as appends or merges, during the execution of a streaming query. In this case, each microbatch of a stream-static join will use the most recent version of the static Delta table as of each microbatch, which means it will reflect any changes made to the static Delta table before the start of each microbatch. Verified Reference: [Databricks Certified Data Engineer Professional], under "Structured Streaming" section; Databricks Documentation, under "Stream and static joins" section.
質問 # 58
A user new to Databricks is trying to troubleshoot long execution times for some pipeline logic they are working on. Presently, the user is executing code cell-by-cell, usingdisplay()calls to confirm code is producing the logically correct results as new transformations are added to an operation. To get a measure of average time to execute, the user is running each cell multiple times interactively.
Which of the following adjustments will get a more accurate measure of how code is likely to perform in production?
- A. Scala is the only language that can be accurately tested using interactive notebooks; because the best performance is achieved by using Scala code compiled to JARs. all PySpark and Spark SQL logic should be refactored.
- B. The Jobs Ul should be leveraged to occasionally run the notebook as a job and track execution time during incremental code development because Photon can only be enabled on clusters launched for scheduled jobs.
- C. The only way to meaningfully troubleshoot code execution times in development notebooks Is to use production-sized data and production-sized clusters with Run All execution.
- D. Calling display () forces a job to trigger, while many transformations will only add to the logical query plan; because of caching, repeated execution of the same logic does not provide meaningful results.
- E. Production code development should only be done using an IDE; executing code against a local build of open source Spark and Delta Lake will provide the most accurate benchmarks for how code will perform in production.
正解：D
解説：
In Databricks notebooks, using thedisplay()function triggers an action that forces Spark to execute the code and produce a result. However, Spark operations are generally divided into transformations and actions.
Transformations create a new dataset from an existing one and are lazy, meaning they are not computed immediately but added to a logical plan. Actions, likedisplay(), trigger the execution of this logical plan.
Repeatedly running the same code cell can lead to misleading performance measurements due to caching.
When a dataset is used multiple times, Spark's optimization mechanism caches it in memory, making subsequent executions faster. This behavior does not accurately represent the first-time execution performance in a production environment where data might not be cached yet.
To get a more realistic measure of performance, it is recommended to:
* Clear the cache or restart the cluster to avoid the effects of caching.
* Test the entire workflow end-to-end rather than cell-by-cell to understand the cumulative performance.
* Consider using a representative sample of the production data, ensuring it includes various cases the code will encounter in production.
References:
* Databricks Documentation on Performance Optimization: Databricks Performance Tuning
* Apache Spark Documentation: RDD Programming Guide - Understanding transformations and actions
質問 # 59
A junior member of the data engineering team is exploring the language interoperability of Databricks notebooks. The intended outcome of the below code is to register a view of all sales that occurred in countries on the continent of Africa that appear in the geo_lookup table.
Before executing the code, running SHOW TABLES on the current database indicates the database contains only two tables: geo_lookup and sales.
Which statement correctly describes the outcome of executing these command cells in order in an interactive notebook?
- A. Cmd 1 will succeed and Cmd 2 will fail, countries at will be a Python variable containing a list of strings.
- B. Cmd 1 will succeed. Cmd 2 will search all accessible databases for a table or view named countries af: if this entity exists, Cmd 2 will succeed.
- C. Cmd 1 will succeed and Cmd 2 will fail, countries at will be a Python variable representing a PySpark DataFrame.
- D. Both commands will succeed. Executing show tables will show that countries at and sales at have been registered as views.
- E. Both commands will fail. No new variables, tables, or views will be created.
正解：A
解説：
This is the correct answer because Cmd 1 is written in Python and uses a list comprehension to extract the country names from the geo_lookup table and store them in a Python variable named countries af. This variable will contain a list of strings, not a PySpark DataFrame or a SQL view. Cmd 2 is written in SQL and tries to create a view named sales af by selecting from the sales table where city is in countries af. However, this command will fail because countries af is not a valid SQL entity and cannot be used in a SQL query. To fix this, a better approach would be to use spark.sql() to execute a SQL query in Python and pass the countries af variable as a parameter. Verified Reference: [Databricks Certified Data Engineer Professional], under "Language Interoperability" section; Databricks Documentation, under "Mix languages" section.
質問 # 60
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?
- A. Drops table but keeps meta data, history and data in storage
- B. Drops table from meta store, drops metadata, history, and data in storage.
- C. Drops table and history but keeps meta data and data in storage
- D. Drops table from meta store, meta data and history but keeps the data in storage
- E. Drops table from meta store and data from storage but keeps metadata and history in storage
正解：B
解説：
Explanation
For a managed table, a drop command will drop everything from metastore and storage.
See the below image to understand the differences between dropping an external table.
Diagram Description automatically generated
質問 # 61
A table in the Lakehouse named customer_churn_params is used in churn prediction by the machine learning team. The table contains information about customers derived from a number of upstream sources. Currently, the data engineering team populates this table nightly by overwriting the table with the current valid values derived from upstream data sources.
The churn prediction model used by the ML team is fairly stable in production. The team is only interested in making predictions on records that have changed in the past 24 hours.
Which approach would simplify the identification of these changed records?
- A. Convert the batch job to a Structured Streaming job using the complete output mode; configure a Structured Streaming job to read from the customer_churn_params table and incrementally predict against the churn model.
- B. Apply the churn model to all rows in the customer_churn_params table, but implement logic to perform an upsert into the predictions table that ignores rows where predictions have not changed.
- C. Modify the overwrite logic to include a field populated by calling spark.sql.functions.current_timestamp() as data are being written; use this field to identify records written on a particular date.
- D. Calculate the difference between the previous model predictions and the current customer_churn_params on a key identifying unique customers before making new predictions; only make predictions on those customers not in the previous predictions.
- E. Replace the current overwrite logic with a merge statement to modify only those records that have changed; write logic to make predictions on the changed records identified by the change data feed.
正解：E
解説：
The approach that would simplify the identification of the changed records is to replace the current overwrite logic with a merge statement to modify only those records that have changed, and write logic to make predictions on the changed records identified by the change data feed. This approach leverages the Delta Lake features of merge and change data feed, which are designed to handle upserts and track row-level changes in a Delta table12. By using merge, the data engineering team can avoid overwriting the entire table every night, and only update or insert the records that have changed in the source data. By using change data feed, the ML team can easily access the change events that have occurred in the customer_churn_params table, and filter them by operation type (update or insert) and timestamp. This way, they can only make predictions on the records that have changed in the past 24 hours, and avoid re-processing the unchanged records.
The other options are not as simple or efficient as the proposed approach, because:
Option A would require applying the churn model to all rows in the customer_churn_params table, which would be wasteful and redundant. It would also require implementing logic to perform an upsert into the predictions table, which would be more complex than using the merge statement.
Option B would require converting the batch job to a Structured Streaming job, which would involve changing the data ingestion and processing logic. It would also require using the complete output mode, which would output the entire result table every time there is a change in the source data, which would be inefficient and costly.
Option C would require calculating the difference between the previous model predictions and the current customer_churn_params on a key identifying unique customers, which would be computationally expensive and prone to errors. It would also require storing and accessing the previous predictions, which would add extra storage and I/O costs.
Option D would require modifying the overwrite logic to include a field populated by calling spark.sql.functions.current_timestamp() as data are being written, which would add extra complexity and overhead to the data engineering job. It would also require using this field to identify records written on a particular date, which would be less accurate and reliable than using the change data feed.
質問 # 62
The data engineering team noticed that one of the job normally finishes in 15 mins but gets stuck randomly when reading remote databases due to a network packet drop, which of the following steps can be used to improve the stability of the job?
- A. Use Cluster timeout setting in the Job cluster UI
- B. Modify the task, to include a timeout to kill the job if it runs more than 15 mins.
- C. Use Jobs runs, active runs UI section to monitor and kill long running job
- D. Use Spark job time out setting in the Spark UI
- E. Use Databrick REST API to monitor long running jobs and issue a kill command
正解：B
解説：
Explanation
The answer is, Modify the task, to include time out to kill the job if it runs more than 15 mins.
https://docs.microsoft.com/en-us/azure/databricks/data-engineering/jobs/jobs#timeout
質問 # 63
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 a bronze table created with the property delta.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, newly updated records will be merged into the target table, overwriting previous values with the same primary keys.
- B. 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.
- 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, the entire available history of inserted or updated records will be appended to the target table, resulting in many duplicate entries.
- E. 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.
正解：D
解説：
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.
質問 # 64
The data governance team is reviewing code used for deleting records for compliance with GDPR. They note the following logic is used to delete records from the Delta Lake table namedusers.
Assuming thatuser_idis a unique identifying key and thatdelete_requestscontains all users that have requested deletion, which statement describes whether successfully executing the above logic guarantees that the records to be deleted are no longer accessible and why?
- A. Yes; the Delta cache immediately updates to reflect the latest data files recorded to disk.
- B. Yes; Delta Lake ACID guarantees provide assurance that the delete command succeeded fully and permanently purged these records.
- C. No; files containing deleted records may still be accessible with time travel until a vacuum command is used to remove invalidated data files.
- D. No; the Delta cache may return records from previous versions of the table until the cluster is restarted.
- E. No; the Delta Lake delete command only provides ACID guarantees when combined with the merge into command.
正解：C
解説：
Explanation
The code uses the DELETE FROM command to delete records from the users table that match a condition based on a join with another table called delete_requests, which contains all users that have requested deletion.
The DELETE FROM command deletes records from a Delta Lake table by creating a new version of the table that does not contain the deleted records. However, this does not guarantee that the records to be deleted are no longer accessible, because Delta Lake supports time travel, which allows querying previous versions of the table using a timestamp or version number. Therefore, files containing deleted records may still be accessible with time travel until a vacuum command is used to remove invalidated data files from physical storage.
Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Delete from a table" section; Databricks Documentation, under "Remove files no longer referenced by a Delta table" section.
質問 # 65
Which statement characterizes the general programming model used by Spark Structured Streaming?
- A. Structured Streaming is implemented as a messaging bus and is derived from Apache Kafka.
- B. Structured Streaming models new data arriving in a data stream as new rows appended to an unbounded table.
- C. Structured Streaming leverages the parallel processing of GPUs to achieve highly parallel data throughput.
- D. Structured Streaming uses specialized hardware and I/O streams to achieve sub-second latency for data transfer.
- E. Structured Streaming relies on a distributed network of nodes that hold incremental state values for cached stages.
正解：B
質問 # 66
Which of the following commands can be used to run one notebook from another notebook?
- A. only job clusters can run notebook
- B. execute.utils.run("full notebook path")
- C. dbutils.notebook.run("full notebook path")
- D. spark.notebook.run("full notebook path")
- E. notebook.utils.run("full notebook path")
正解：C
解説：
Explanation
The answer is dbutils.notebook.run(" full notebook path ")
Here is the full command with additional options.
run(path: String, timeout_seconds: int, arguments: Map): String
1.dbutils.notebook.run("ful-notebook-name", 60, {"argument": "data", "argument2": "data2", ...})
質問 # 67
Which statement characterizes the general programming model used by Spark Structured Streaming?
- A. Structured Streaming leverages the parallel processing of GPUs to achieve highly parallel data throughput.
- B. Structured Streaming uses specialized hardware and I/O streams to achieve sub-second latency for data transfer.
- C. Structured Streaming relies on a distributed network of nodes that hold incremental state values for cached stages.
- D. Structured Streaming models new data arriving in a data stream as new rows appended to an unbounded table.
- E. Structured Streaming is implemented as a messaging bus and is derived from Apache Kafka.
正解：E
解説：
This is the correct answer because it characterizes the general programming model used by Spark Structured Streaming, which is to treat a live data stream as a table that is being continuously appended. This leads to a new stream processing model that is very similar to a batch processing model, where users can express their streaming computation using the same Dataset/DataFrame API as they would use for static data. The Spark SQL engine will take care of running the streaming query incrementally and continuously and updating the final result as streaming data continues to arrive. Verified References: [Databricks Certified Data Engineer Professional], under "Structured Streaming" section; Databricks Documentation, under "Overview" section.
質問 # 68
Define an external SQL table by connecting to a local instance of an SQLite database using JDBC
- A. 1.CREATE TABLE users_jdbc
2.USING SQL
3.URL = {server:"jdbc:/sqmple_db",dbtable: "users"} - B. 1.CREATE TABLE users_jdbc
2.USING org.apache.spark.sql.jdbc
3.OPTIONS (
4. url = "jdbc:sqlite:/sqmple_db",
5. dbtable = "users"
6.) - C. 1.CREATE TABLE users_jdbc
2.USING SQLITE
3.OPTIONS (
4. url = "jdbc:/sqmple_db",
5. dbtable = "users"
6.) - D. 1.CREATE TABLE users_jdbc
2.USING SQL
3.OPTIONS (
4. url = "jdbc:sqlite:/sqmple_db",
5. dbtable = "users"
6.) - E. 1.CREATE TABLE users_jdbc
2.USING org.apache.spark.sql.jdbc.sqlite
3.OPTIONS (
4. url = "jdbc:/sqmple_db",
5. dbtable = "users"
6.)
正解：E
解説：
Explanation
The answer is,
1.CREATE TABLE users_jdbc
2.USING org.apache.spark.sql.jdbc
3.OPTIONS (
4. url = "jdbc:sqlite:/sqmple_db",
5. dbtable = "users"
6.)
Databricks runtime currently supports connecting to a few flavors of SQL Database including SQL Server, My SQL, SQL Lite and Snowflake using JDBC.
1.CREATE TABLE <jdbcTable>
2.USING org.apache.spark.sql.jdbc or JDBC
3.OPTIONS (
4. url = "jdbc:<databaseServerType>://<jdbcHostname>:<jdbcPort>",
5. dbtable " = <jdbcDatabase>.atable",
6. user = "<jdbcUsername>",
7. password = "<jdbcPassword>"
8.)
For more detailed documentation
SQL databases using JDBC - Azure Databricks | Microsoft Docs
質問 # 69
The security team is exploring whether or not the Databricks secrets module can be leveraged for connecting to an external database.
After testing the code with all Python variables being defined with strings, they upload the password to the secrets module and configure the correct permissions for the currently active user. They then modify their code to the following (leaving all other variables unchanged).
Which statement describes what will happen when the above code is executed?
- A. The connection to the external table will succeed; the string "redacted" will be printed.
- B. The connection to the external table will succeed; the string value of password will be printed in plain text.
- C. An interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the encoded password will be saved to DBFS.
- D. An interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the password will be printed in plain text.
- E. The connection to the external table will fail; the string "redacted" will be printed.
正解：A
解説：
This is the correct answer because the code is using the dbutils.secrets.get method to retrieve the password from the secrets module and store it in a variable. The secrets module allows users to securely store and access sensitive information such as passwords, tokens, or API keys. The connection to the external table will succeed because the password variable will contain the actual password value. However, when printing the password variable, the string "redacted" will be displayed instead of the plain text password, as a security measure to prevent exposing sensitive information in notebooks. Verified References: [Databricks Certified Data Engineer Professional], under "Security & Governance" section; Databricks Documentation, under
"Secrets" section.
質問 # 70
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. 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.
- B. 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.
- C. 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.
- D. An incremental job will leverage running information in the state store to update aggregate values in the gold_customer_lifetime_sales_summary table.
- E. 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.
正解：C
解説：
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
質問 # 71
Which statement describes integration testing?
- A. Validates interactions between subsystems of your application
- B. Validates an application use case
- C. Requires manual intervention
- D. Requires an automated testing framework
- E. Validates behavior of individual elements of your application
正解：A
解説：
This is the correct answer because it describes integration testing. Integration testing is a type of testing that validates interactions between subsystems of your application, such as modules, components, or services.
Integration testing ensures that the subsystems work together as expected and produce the correct outputs or results. Integration testing can be done at different levels of granularity, such as component integration testing, system integration testing, or end-to-end testing. Integration testing can help detect errors or bugs that may not be found by unit testing, which only validates behavior of individual elements of your application. Verified References: [Databricks Certified Data Engineer Professional], under "Testing" section; Databricks Documentation, under "Integration testing" section.
質問 # 72
The data science team has created and logged a production using MLFlow. The model accepts a list of column names and returns a new column of type DOUBLE.
The following code correctly imports the production model, load the customer table containing the customer_id key column into a Dataframe, and defines the feature columns needed for the model.
Which code block will output DataFrame with the schema'' customer_id LONG, predictions DOUBLE''?
- A. Df, map (lambda k:midel (x [columns]) ,select (''customer_id predictions'')
- B. Df.apply(model, columns). Select (''customer_id, prediction''
- C. Df. Select (''customer_id''.
Model (''columns) alias (''predictions'') - D. Model, predict (df, columns)
正解：D
解説：
Given the information that the model is registered with MLflow and assuming predict is the method used to apply the model to a set of columns, we use the model.predict() function to apply the model to the DataFrame df using the specified columns. The model.predict() function is designed to take in a DataFrame and a list of column names as arguments, applying the trained model to these features to produce a predictions column.
When working with PySpark, this predictions column needs to be selected alongside the customer_id to create a new DataFrame with the schema customer_id LONG, predictions DOUBLE.
References:
* MLflow documentation on using Python function models:
https://www.mlflow.org/docs/latest/models.html#python-function-python
* PySpark MLlib documentation on model prediction:
https://spark.apache.org/docs/latest/ml-pipeline.html#pipeline
質問 # 73
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?
- A. JOBS and TASKS
- B. SQL Endpoints
- C. DELTA LIVE TABLES
- D. AUTO LOADER
- E. STRUCTURED STREAMING with MULTI HOP
正解：C
解説：
Explanation
The answer is, DELTA LIVE TABLES
DLT simplifies data dependencies by building DAG-based joins between live tables. Here is a view of how the dag looks with data dependencies without additional meta data,
1.create or replace live view customers
2.select * from customers;
3.
4.create or replace live view sales_orders_raw
5.select * from sales_orders;
6.
7.create or replace live view sales_orders_cleaned
8.as
9.select sales.* from
10.live.sales_orders_raw s
11. join live.customers c
12.on c.customer_id = s.customer_id
13.where c.city = 'LA';
14.
15.create or replace live table sales_orders_in_la
16.selects from sales_orders_cleaned;
Above code creates below dag
Documentation on DELTA LIVE TABLES,
https://databricks.com/product/delta-live-tables
https://databricks.com/blog/2022/04/05/announcing-generally-availability-of-databricks-delta-live-tables-dlt.htm DELTA LIVE TABLES, addresses below challenges when building ETL processes
1.Complexities of large scale ETL
a.Hard to build and maintain dependencies
b.Difficult to switch between batch and stream
2.Data quality and governance
a.Difficult to monitor and enforce data quality
b.Impossible to trace data lineage
3.Difficult pipeline operations
a.Poor observability at granular data level
b.Error handling and recovery is laborious
質問 # 74
The viewupdatesrepresents an incremental batch of all newly ingested data to be inserted or updated in the customerstable.
The following logic is used to process these records.
Which statement describes this implementation?
- A. The customers table is implemented as a Type 3 table; old values are maintained as a new column alongside the current value.
- B. The customers table is implemented as a Type 2 table; old values are overwritten and new customers are appended.
- C. The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.
- D. The customers table is implemented as a Type 0 table; all writes are append only with no changes to existing values.
- E. The customers table is implemented as a Type 1 table; old values are overwritten by new values and no history is maintained.
正解：C
解説：
Explanation
The logic uses the MERGE INTO command to merge new records from the view updates into the table customers. The MERGE INTO command takes two arguments: a target table and a source table or view. The command also specifies a condition to match records between the target and the source, and a set of actions to perform when there is a match or not. In this case, the condition is to match records by customer_id, which is the primary key of the customers table. The actions are to update the existing record in the target with the new values from the source, and set the current_flag to false to indicate that the record is no longer current; and to insert a new record in the target with the new values from the source, and set the current_flag to true to indicate that the record is current. This means that old values are maintained but marked as no longer current and new values are inserted, which is the definition of a Type 2 table. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Merge Into (Delta Lake on Databricks)" section.
質問 # 75
You have noticed that Databricks SQL queries are running slow, you are asked to look reason why queries are running slow and identify steps to improve the performance, when you looked at the issue you noticed all the queries are running in parallel and using a SQL endpoint(SQL Warehouse) with a single cluster. Which of the following steps can be taken to improve the performance/response times of the queries?
*Please note Databricks recently renamed SQL endpoint to SQL warehouse.
- A. They can turn on the Serverless feature for the SQL endpoint(SQL warehouse) and change the Spot Instance Policy to "Reliability Optimized."
- B. They can increase the maximum bound of the SQL endpoint(SQL warehouse)'s scaling range
- C. They can turn on the Auto Stop feature for the SQL endpoint(SQL warehouse).
- D. They can increase the warehouse size from 2X-Smal to 4XLarge of the SQL end-point(SQL warehouse).
- E. They can turn on the Serverless feature for the SQL endpoint(SQL warehouse).
正解：B
解説：
Explanation
The answer is, They can increase the maximum bound of the SQL endpoint's scaling range when you increase the max scaling range more clusters are added so queries instead of waiting in the queue can start running using available clusters, see below for more explanation.
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, for 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 having 1 worker node and X-Small having 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.
質問 # 76
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. 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.
- B. 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.
- C. 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.
- D. An incremental job will leverage running information in the state store to update aggregate values in the gold_customer_lifetime_sales_summary table.
- E. 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.
正解：C
解説：
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. Reference:
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
質問 # 77
A user new to Databricks is trying to troubleshoot long execution times for some pipeline logic they are working on. Presently, the user is executing code cell-by-cell, using display() calls to confirm code is producing the logically correct results as new transformations are added to an operation. To get a measure of average time to execute, the user is running each cell multiple times interactively.
Which of the following adjustments will get a more accurate measure of how code is likely to perform in production?
- A. Scala is the only language that can be accurately tested using interactive notebooks; because the best performance is achieved by using Scala code compiled to JARs. all PySpark and Spark SQL logic should be refactored.
- B. The Jobs Ul should be leveraged to occasionally run the notebook as a job and track execution time during incremental code development because Photon can only be enabled on clusters launched for scheduled jobs.
- C. The only way to meaningfully troubleshoot code execution times in development notebooks Is to use production-sized data and production-sized clusters with Run All execution.
- D. Calling display () forces a job to trigger, while many transformations will only add to the logical query plan; because of caching, repeated execution of the same logic does not provide meaningful results.
- E. Production code development should only be done using an IDE; executing code against a local build of open source Spark and Delta Lake will provide the most accurate benchmarks for how code will perform in production.
正解：D
解説：
This is the correct answer because it explains which of the following adjustments will get a more accurate measure of how code is likely to perform in production. The adjustment is that calling display() forces a job to trigger, while many transformations will only add to the logical query plan; because of caching, repeated execution of the same logic does not provide meaningful results. When developing code in Databricks notebooks, one should be aware of how Spark handles transformations and actions. Transformations are operations that create a new DataFrame or Dataset from an existing one, such as filter, select, or join. Actions are operations that trigger a computation on a DataFrame or Dataset and return a result to the driver program or write it to storage, such as count, show, or save. Calling display() on a DataFrame or Dataset is also an action that triggers a computation and displays the result in a notebook cell. Spark uses lazy evaluation for transformations, which means that they are not executed until an action is called. Spark also uses caching to store intermediate results in memory or disk for faster access in subsequent actions. Therefore, calling display() forces a job to trigger, while many transformations will only add to the logical query plan; because of caching, repeated execution of the same logic does not provide meaningful results. To get a more accurate measure of how code is likely to perform in production, one should avoid calling display() too often or clear the cache before running each cell. Verified Reference: [Databricks Certified Data Engineer Professional], under "Spark Core" section; Databricks Documentation, under "Lazy evaluation" section; Databricks Documentation, under "Caching" section.
質問 # 78
The following table consists of items found in user carts within an e-commerce website.
The following MERGE statement is used to update this table using an updates view, with schema evaluation enabled on this table.
How would the following update be handled?
- A. The new restored field is added to the target schema, and dynamically read as NULL for existing unmatched records.
- B. The update is moved to separate ''restored'' column because it is missing a column expected in the target schema.
- C. The new nested field is added to the target schema, and files underlying existing records are updated to include NULL values for the new field.
- D. The update throws an error because changes to existing columns in the target schema are not supported.
正解：C
解説：
With schema evolution enabled in Databricks Delta tables, when a new field is added to a record through a MERGE operation, Databricks automatically modifies the table schema to include the new field. In existing records where this new field is not present, Databricks will insert NULL values for that field. This ensures that the schema remains consistent across all records in the table, with the new field being present in every record, even if it is NULL for records that did not originally include it.
References:
* Databricks documentation on schema evolution in Delta Lake:
https://docs.databricks.com/delta/delta-batch.html#schema-evolution
質問 # 79
......
認定試験は、複数選択問題から構成されるコンピューターベースのテストです。受験者は2時間以内に試験を完了し、合格するために最低70％のスコアを取得する必要があります。試験は監督され、受験者は信頼できるインターネット接続とウェブカメラ、マイクを備えたコンピューターを持っている必要があります。
あなたを合格させるDatabricks試験にはDatabricks-Certified-Professional-Data-Engineer試験問題集：https://jp.fast2test.com/Databricks-Certified-Professional-Data-Engineer-premium-file.html
Databricks-Certified-Professional-Data-Engineer試験問題集PDF更新された問題集にはFast2test試験合格保証付き：https://drive.google.com/open?id=1K-ir8jIkdO4Q1vswSqr1ju1kDcB6BgvA