[2023年07月] 試験Databricks-Certified-Professional-Data-Engineer最新ブレーン専門問題集はここ
無料で使えるDatabricks-Certified-Professional-Data-Engineer試験問題集試験点数を伸ばそう
Databricks Certified Professional Data Engineer になるには、データエンジニアリングに関連する幅広いトピックをカバーする厳しい試験に合格する必要があります。この試験は、Databricks ツールやテクノロジーを使用してデータパイプラインを設計、構築、維持する能力をテストするとともに、データモデリング、データウェアハウジング、データ統合の理解もテストします。
質問 # 60
Where are Interactive notebook results stored in Databricks product architecture?
- A. Control plane
- B. Data plane
- C. Databricks web application
- D. JDBC data source
- E. Data and Control plane
正解：E
解説：
Explanation
The answer is Data and Control plane,
Only Job results are stored in Data Plane(your storage), Interactive notebook results are stored in a combination of the control plane (partial results for presentation in the UI) and customer storage.
https://docs.microsoft.com/en-us/azure/databricks/getting-started/overview#--high-level-architecture Snippet from the above documentation, Graphical user interface, application Description automatically generated
How to change this behavior?
You can change this behavior using Workspace/Admin Console settings for that workspace, once enabled all of the interactive results are stored in the customer account(data plane) except the new notebook visualization feature Databricks has recently introduced, this still stores some metadata in the control pane irrespective of the below settings. please refer to the documentation for more details.
Graphical user interface, text, application, email Description automatically generated
Why is this important to know?
I recently worked on a project where we had to deal with sensitive information of customers and we had a security requirement that all of the data need to be stored in the data plane including notebook results.
質問 # 61
You are looking to process the data based on two variables, one to check if the department is supply chain and second to check if process flag is set to True
- A. if department == "supply chain" and process:
- B. if department == "supply chain" && process:
- C. if department == "supply chain" & process == TRUE:
- D. if department == "supply chain" & if process == TRUE:
- E. if department = "supply chain" & process:
正解：A
質問 # 62
How do you check the location of an existing schema in Delta Lake?
- A. Check unity catalog UI
- B. Run SQL command SHOW LOCATION schema_name
- C. Run SQL command DESCRIBE SCHEMA EXTENDED schema_name
E Schemas are internally in-store external hive meta stores like MySQL or SQL Server - D. Use Data explorer
正解：C
解説：
Explanation
Here is an example of how it looks
Graphical user interface, text, application, email Description automatically generated
質問 # 63
Which of the following developer operations in the CI/CD can only be implemented through a GIT provider when using Databricks Repos.
- A. Commit and push code
- B. Pull request and review process
- C. Create a new branch
- D. Create and edit code
- E. Trigger Databricks Repos pull API to update the latest version
正解：B
解説：
Explanation
The answer is Pull request and review process, please note: the question is asking for steps that are being implemented in GIT provider not Databricks Repos.
See below diagram to understand the role of Databricks Repos and Git provider plays when building a CI/CD workdlow.
All the steps highlighted in yellow can be done Databricks Repo, all the steps highlighted in Gray are done in a git provider like Github or Azure Devops.
Diagram Description automatically generated
Bottom of Form
Top of Form
質問 # 64
You are currently working to ingest millions of files that get uploaded to the cloud object storage for consumption, and you are asked to build a process to ingest this data, the schema of the file is expected to change over time, and the ingestion process should be able to handle these changes automatically. Which of the following method can be used to ingest the data incrementally?
- A. Structured Streaming
- B. AUTO APPEND
- C. COPY INTO
- D. AUTO LOADER
- E. Checkpoint
正解：D
解説：
Explanation
The answer is AUTO LOADER,
Use Auto Loader instead of the COPY INTO SQL command when:
*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.
*COPY INTO only directory listing but AUTO LOADER supports File notification method where the Auto Loader continues to ingest files as they arrive in cloud object storage lever-aging cloud provider(Queues and triggers) and Spark's structured streaming.
*Your data schema evolves frequently. Auto Loader provides better support for schema in-ference and evolution. See Configuring schema inference and evolution in Auto Loader.
質問 # 65
Which of the below commands can be used to drop a DELTA table?
- A. DROP table_name
- B. DROP TABLE table_name
- C. DROP TABLE table_name FORMAT DELTA
- D. DROP DELTA table_name
正解：B
質問 # 66
What is the purpose of a gold layer in Multi-hop architecture?
- A. Powers ML applications, reporting, dashboards and adhoc reports.
- B. Preserves grain of original data, without any aggregations
- C. Eliminate duplicate records
- D. Optimizes ETL throughput and analytic query performance
- E. Data quality checks and schema enforcement
正解：A
解説：
Explanation
The answer is Powers ML applications, reporting, dashboards and adhoc reports.
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.
質問 # 67
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. Increase the maximum bound of the SQL endpoint's scaling range.
- C. Turn on the Auto Stop feature for the SQL endpoint.
- D. Turn on the Serverless feature for the SQL endpoint.
- E. Turn on the Serverless feature for the SQL endpoint and change the Spot Instance Pol-icy to "Reliability Optimized."
正解：A
解説：
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
質問 # 68
You are still noticing slowness in query after performing optimize which helped you to resolve the small files problem, the column(transactionId) you are using to filter the data has high cardinality and auto incrementing number. Which delta optimization can you enable to filter data effectively based on this column?
- A. transactionId has high cardinality, you cannot enable any optimization.
- B. Create BLOOM FLTER index on the transactionId
- C. Perform Optimize with Zorder on transactionId
(Correct) - D. Increase the driver size and enable delta optimization
- E. Increase the cluster size and enable delta optimization
正解：C
解説：
Explanation
The answer is, perform Optimize with Z-order by transactionid
Here is a simple explanation of how Z-order works, once the data is naturally ordered, when a flle is scanned it only brings the data it needs into spark's memory Based on the column min and max it knows which data files needs to be scanned.
Table Description automatically generated
Graphical user interface, diagram, application Description automatically generated
質問 # 69
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. UPDATE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- B. MERGE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- C. REPLACE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- D. MODIFY transactions SET active_flag = 'N' WHERE active_flag = 'Y'
正解：C
解説：
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.
質問 # 70
What could be the expected output of query SELECT COUNT (DISTINCT *) FROM user on this table
- A. 0
- B. 2
(Correct) - C. NULL
- D. 1
- E. 2
正解：B
解説：
Explanation
The answer is 2,
Count(DISTINCT *) removes rows with any column with a NULL value
質問 # 71
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).
- B. They can turn on the Auto Stop 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 to "Reliability Optimized."
- D. They can increase the warehouse size from 2X-Smal to 4XLarge of the SQL end-point(SQL warehouse).
- E. They can increase the maximum bound of the SQL endpoint(SQL warehouse)'s scaling range
正解：E
解説：
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.
質問 # 72
Which of the following technique can be used to implement fine-grained access control to rows and columns of the Delta table based on the user's access?
- A. Data access control lists
- B. Use dynamic view functions
- C. Use Unity catalog to grant access to rows and columns
- D. Dynamic Access control lists with Unity Catalog
- E. Row and column access control lists
正解：B
解説：
Explanation
The answer is, Use dynamic view functions.
Here is an example that limits access to rows based on the user being part managers group, in the below view if a user is not a part of the manager's group you can only see rows where the total amount is <= 1000000 Dynamic view function to filter rows
1.CREATE VIEW sales_redacted AS
2.SELECT user_id, country, product, total
3.FROM sales_raw
4.WHERE CASE WHEN is_member('managers') THEN TRUE ELSE total <= 1000000 END; Dynamic view function to hide a column data based on user's access,
1.CREATE VIEW sales_redacted AS
2.SELECT user_id,
3. CASE WHEN is_member('auditors') THEN email ELSE 'REDACTED' END AS email,
4. country,
5. product,
6. total
7.FROM sales_raw
Please review below for more details
https://docs.microsoft.com/en-us/azure/databricks/security/access-control/table-acls/object-privileges#dynamic-v
質問 # 73
When using the complete mode to write stream data, how does it impact the target table?
- A. Stream must complete to write the data
- B. Target table cannot be updated while stream is pending
- C. Delta commits transaction once the stream is stopped
- D. Entire stream waits for complete data to write
- E. Target table is overwritten for each batch
正解：E
解説：
Explanation
The answer is Target table is overwritten for each batch
Complete mode - The whole Result Table will be outputted to the sink after every trigger. This is supported for aggregation queries
質問 # 74
Which of the following techniques structured streaming uses to create an end-to-end fault toler-ance?
- A. Write ahead logging and idempotent sinks
- B. Write ahead logging and water marking
- C. Checkpointing and idempotent sinks
- D. Stream will failover to available nodes in the cluste
- E. Checkpointing and Water marking
正解：C
解説：
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.
質問 # 75
You have written a notebook to generate a summary data set for reporting, Notebook was scheduled using the job cluster, but you realized it takes 8 minutes to start the cluster, what feature can be used to start the cluster in a timely fashion so your job can run immediatley?
- A. Use the Databricks cluster pools feature to reduce the startup time
- B. Pin the cluster in the cluster UI page so it is always available to the jobs
- C. Disable auto termination so the cluster is always running
- D. Setup an additional job to run ahead of the actual job so the cluster is running second job starts
- E. Use Databricks Premium edition instead of Databricks standard edition
正解：A
解説：
Explanation
Cluster pools allow us to reserve VM's ahead of time, when a new job cluster is created VM are grabbed from the pool. Note: when the VM's are waiting to be used by the cluster only cost incurred is Azure. Databricks run time cost is only billed once VM is allocated to a cluster.
Here is a demo of how to setup a pool and follow some best practices,
Graphical user interface, text Description automatically generated
質問 # 76
What steps need to be taken to set up a DELTA LIVE PIPELINE as a job using the workspace UI?
- A. DELTA LIVE TABLES do not support job cluster
- B. Select Workflows UI and Delta live tables tab, under task type select Delta live tables pipeline and select the pipeline JSON file
- C. Use Pipeline creation UI, select a new pipeline and job cluster
- D. Select Workflows UI and Delta live tables tab, under task type select Delta live tables pipeline and select the notebook
正解：D
解説：
Explanation
The answer is,
Select Workflows UI and Delta live tables tab, under task type select Delta live tables pipeline and select the notebook.
Create a pipeline
To create a new pipeline using the Delta Live Tables notebook:
1.Click Workflows in the sidebar, click the Delta Live Tables tab, and click Create Pipeline.
2.Give the pipeline a name and click to select a notebook.
3.Optionally enter a storage location for output data from the pipeline. The system uses a de-fault location if you leave Storage Location empty.
4.Select Triggered for Pipeline Mode.
5.Click Create.
The system displays the Pipeline Details page after you click Create. You can also access your pipeline by clicking the pipeline name in the Delta Live Tables tab.
質問 # 77
A data analyst has provided a data engineering team with the following Spark SQL query:
1.SELECT district,
2.avg(sales)
3.FROM store_sales_20220101
4.GROUP BY district;
The data analyst would like the data engineering team to run this query every day. The date at the end of the
table name (20220101) should automatically be replaced with the current date each time the query is run.
Which of the following approaches could be used by the data engineering team to efficiently auto-mate this
process?
- A. They could pass the table into PySpark and develop a robustly tested module on the existing query
- B. They could replace the string-formatted date in the table with a timestamp-formatted date
- C. They could wrap the query using PySpark and use Python's string variable system to automatically
update the table name - D. They could request that the data analyst rewrites the query to be run less frequently
- E. They could manually replace the date within the table name with the current day's date
正解：C
質問 # 78
You were asked to create or overwrite an existing delta table to store the below transaction data.
- A. 1.CREATE OR REPLACE TABLE IF EXISTS transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
5.FORMAT DELTA - B. 1.CREATE IF EXSITS REPLACE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int) - C. 1.CREATE OR REPLACE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int) - D. 1.CREATE OR REPLACE DELTA TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
正解：C
解説：
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.
質問 # 79
While investigating a data issue, you wanted to review yesterday's version of the table using below command, while querying the previous version of the table using time travel you realized that you are no longer able to view the historical data in the table and you could see it the table was updated yesterday based on the table history(DESCRIBE HISTORY table_name) command what could be the reason why you can not access this data?
SELECT * FROM table_name TIMESTAMP AS OF date_sub(current_date(), 1)
- A. A command VACUUM table_name RETAIN 0 was ran on the table
- B. Time travel must be enabled before you query previous data
- C. By default, historical data is cleaned every 180 days in DELTA
- D. Time travel is disabled
- E. You currently do not have access to view historical data
正解：A
解説：
Explanation
The answer is, VACUUM table_name RETAIN 0 was ran
The VACUUM command recursively vacuums directories associated with the Delta table and re-moves data files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold. The default is 7 Days.
When VACUUM table_name RETAIN 0 is ran all of the historical versions of data are lost time travel can only provide the current state.
質問 # 80
A data engineer wants to create a relational object by pulling data from two tables. The relational object must
be used by other data engineers in other sessions. In order to save on storage costs, the data engineer wants to
avoid copying and storing physical data.
Which of the following relational objects should the data engineer create?
- A. Spark SQL Table
- B. Delta Table
- C. Temporary view
- D. Database
- E. View
正解：E
質問 # 81
A data engineering manager has noticed that each of the queries in a Databricks SQL dashboard takes a few
minutes to update when they manually click the "Refresh" button. They are curious why this might be
occurring, so a team member provides a variety of reasons on why the delay might be occurring.
Which of the following reasons fails to explain why the dashboard might be taking a few minutes to update?
- A. The queries attached to the dashboard might all be connected to their own, unstarted Databricks clusters
- B. The queries attached to the dashboard might first be checking to determine if new data is available
- C. The SQL endpoint being used by each of the queries might need a few minutes to start up
- D. The queries attached to the dashboard might take a few minutes to run under normal circumstances
- E. The Job associated with updating the dashboard might be using a non-pooled endpoint
正解：E
質問 # 82
......
心強いDatabricks-Certified-Professional-Data-EngineerのPDF問題集はDatabricks-Certified-Professional-Data-Engineer問題：https://www.jpntest.com/shiken/Databricks-Certified-Professional-Data-Engineer-mondaishu
2023年最新の実際に出るDatabricks-Certified-Professional-Data-Engineer問題集には試験のコツがあるPDF試験材料：https://drive.google.com/open?id=1SX_aFrIG-IfyQDyZzsaroBqUXKorPMZd