Databricks DATABRICKS-CERTIFIED-DATA-ENGINEER-ASSOCIATE Online Practice Questions and Exam Preparation

Databricks DATABRICKS-CERTIFIED-DATA-ENGINEER-ASSOCIATE Online Questions & Answers

• Question 111:

  A data engineer needs to provide access to a group named manufacturing-team. The team needs privileges to create tables in the quality schema.

  Which set of SQL commands will grant a group named manufacturing-team to create tables in a schema named production with the parent catalog named manufacturing with the least privileges?

  A. GRANT CREATE TABLE ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE SCHEMA ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE CATALOG ON CATALOG manufacturing TO manufacturing-team;
  B. GRANT USE TABLE ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE SCHEMA ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE CATALOG ON CATALOG manufacturing TO manufacturing-team;
  C. GRANT CREATE TABLE ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT CREATE SCHEMA ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT CREATE CATALOG ON CATALOG manufacturing TO manufacturing-team;
  D. GRANT CREATE TABLE ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT CREATE SCHEMA ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE CATALOG ON CATALOG manufacturing TO manufacturing-team;
  A. GRANT CREATE TABLE ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE SCHEMA ON SCHEMA manufacturing.quality TO manufacturing-team; GRANT USE CATALOG ON CATALOG manufacturing TO manufacturing-team;

  ---

  Explanation

  To allow a group to create tables in a schema with least privileges, they need:

  GRANT CREATE TABLE on the schema GRANT USE SCHEMA on the schema GRANT USE CATALOG on the parent catalog This ensures proper access without granting unnecessary privileges like creating schemas or catalogs.

• Question 112:

  What is a benefit of using Delta Live Tables?

  A. Manual cluster provisioning
  B. Real-time access control
  C. Declarative pipeline management
  D. Raw file editing
  E. Built-in job reruns
  C. Declarative pipeline management

  ---

  Explanation

  Delta Live Tables enables declarative pipeline definitions with built-in reliability.

• Question 113:

  A data engineer team has decided to implement a new data platform on Databricks and is currently deciding how to store each kind of data on each data layer.

  What is the appropriate layer and data pairing for medallion architecture?

  A. Silver Layer - Raw data from deposit account application
  B. Bronze Layer - Summary of cash deposit amount for each country and city
  C. Silver Layer - Cleansed master customer data
  D. Gold Layer - Deduplicated money transfer transaction
  C. Silver Layer - Cleansed master customer data

  ---

  Explanation

  In the Medallion architecture, the Silver layer holds cleansed and conformed data (such as master customer data), which has been cleaned, validated, and standardized after ingestion from the Bronze layer.

• Question 114:

  An engineering manager wants to monitor the performance of a recent project using a Databricks SQL query. For the first week following the project's release, the manager wants the query results to be updated every minute. However, the manager is concerned that the compute resources used for the query will be left running and cost the organization a lot of money beyond the first week of the project's release.

  Which of the following approaches can the engineering team use to ensure the query does not cost the organization any money beyond the first week of the project's release?

  A. They can set a limit to the number of DBUs that are consumed by the SQL Endpoint.
  B. They can set the query's refresh schedule to end after a certain number of refreshes.
  C. They cannot ensure the query does not cost the organization money beyond the first week of the project's release.
  D. They can set a limit to the number of individuals that are able to manage the query's refresh schedule.
  E. They can set the query's refresh schedule to end on a certain date in the query scheduler.
  E. They can set the query's refresh schedule to end on a certain date in the query scheduler.

  ---

  Explanation

  In Databricks SQL, you can use scheduled query executions to update your dashboards or enable routine alerts. By default, your queries do not have a schedule. To set the schedule, you can use the dropdown pickers to specify the frequency, period, starting time, and time zone. You can also choose to end the schedule on a certain date by selecting the End date checkbox and picking a date from the calendar. This way, you can ensure that the query does not run beyond the first week of the project's release and does not incur any additional cost. Option A is incorrect, as setting a limit to the number of DBUs does not stop the query from running. Option B is incorrect, as there is no option to end the schedule after a certain number of refreshes. Option C is incorrect, as there is a way to ensure the query does not cost the organization money beyond the first week of the project's release. Option D is incorrect, as setting a limit to the number of individuals who can manage the query's refresh schedule does not affect the query's execution or cost.

  References: Schedule a query, Schedule a query - Azure Databricks - Databricks SQL

• Question 115:

  A data engineer at a company that uses Databricks with Unity Catalog needs to share a collection of tables with an external partner who also uses a Databricks workspace enabled for Unity Catalog. The data engineer decides to use Delta Sharing to accomplish this.

  What is the first piece of information the data engineer should request from the external partner to set up Delta Sharing?

  A. The IP address of their Databricks workspace
  B. The name of their Databricks cluster
  C. The sharing identifier of their Unity Catalog metastore
  D. Their Databricks account password
  C. The sharing identifier of their Unity Catalog metastore

  ---

  Explanation

  To configure Delta Sharing between two Databricks workspaces with Unity Catalog, the first requirement is the sharing identifier of the partner's Unity Catalog metastore, which enables secure setup of the share and recipient relationship.

• Question 116:

  A data engineer is developing a small proof of concept in a notebook. When running the entire notebook, the Cluster usage spikes. The data engineer wants to keep the development requirements and get real- time results. Which Cluster meets these requirements?

  A. All Purpose Cluster with autoscaling
  B. Job Cluster with Photon enabled and autoscaling
  C. Job Cluster with autoscaling enabled
  D. All-Purpose Cluster with a large fixed memory size
  A. All Purpose Cluster with autoscaling

  ---

  Explanation

  An All-Purpose Cluster with autoscaling is best for interactive development and proof of concept work in notebooks, since it provides real-time results and can dynamically scale resources as usage spikes.

• Question 117:

  What are the transformations typically included in building the Bronze layer?

  A. Include columns Load date/time, process ID
  B. Business rules and transformations
  C. Perform extensive data cleansing
  D. Aggregate data from multiple sources
  A. Include columns Load date/time, process ID

  ---

  Explanation

  The Bronze layer stores raw ingested data with minimal transformations, typically adding metadata columns such as load date/time and process ID for traceability and auditing.

• Question 118:

  A data engineer is attempting to write Python and SQL in the same command cell and is running into an error. The engineer thought that it was possible to use a Python variable in a select statement. Why does the command fail?

  A. Databricks supports language interoperability in the same cell but only between Scala and SQL.
  B. Databricks supports multiple languages but only one per notebook.
  C. Databricks supports one language per cell.
  D. Databricks supports language interoperability but only if a special character is used.
  C. Databricks supports one language per cell.

  ---

  Explanation

  In Databricks, each notebook cell supports only one language. While a notebook can contain multiple languages (Python, SQL, Scala, R), you must define the language per cell, so mixing Python and SQL directly in the same cell causes the error.

• Question 119:

  Which of the following commands can be used to write data into a Delta table while avoiding the writing of duplicate records?

  A. DROP
  B. IGNORE
  C. MERGE
  D. APPEND
  E. INSERT
  C. MERGE

  ---

  Explanation

  The MERGE command can be used to upsert data from a source table, view, or DataFrame into a target Delta table. It allows you to specify conditions for matching and updating existing records, and inserting new records when no match is

  found. This way, you can avoid writing duplicate records into a Delta table. The other commands (DROP, IGNORE, APPEND, INSERT) do not have this functionality and may result in duplicate records or data loss.

  References:

  1: Upsert into a Delta Lake table using merge | Databricks on AWS

  2: SQL DELETE | Databricks on AWS

  3: SQL INSERT INTO | Databricks on AWS

  4: SQL UPDATE | Databricks on AWS

• Question 120:

  A data engineer has been using a Databricks SQL dashboard to monitor the cleanliness of the input data to a data analytics dashboard for a retail use case. The job has a Databricks SQL query that returns the number of store-level records where sales is equal to zero. The data engineer wants their entire team to be notified via a messaging webhook whenever this value is greater than 0.

  Which of the following approaches can the data engineer use to notify their entire team via a messaging webhook whenever the number of stores with $0 in sales is greater than zero?

  A. They can set up an Alert with a custom template.
  B. They can set up an Alert with a new email alert destination.
  C. They can set up an Alert with one-time notifications.
  D. They can set up an Alert with a new webhook alert destination.
  E. They can set up an Alert without notifications.
  D. They can set up an Alert with a new webhook alert destination.

  ---

  Explanation

  A webhook alert destination is a notification destination that allows Databricks to send HTTP POST requests to a third-party endpoint when an alert is triggered. This enables the data engineer to integrate Databricks alerts with their preferred messaging or collaboration platform, such as Slack, Microsoft Teams, or PagerDuty. To set up a webhook alert destination, the data engineer needs to create and configure a webhook connector in their messaging platform, and then add the webhook URL to the Databricks notification destination. After that, the data engineer can create an alert for their Databricks SQL query, and select the webhook alert destination as the notification destination. The alert can be configured with a custom condition, such as when the number of stores with $0 in sales is greater than zero, and a custom message template, such as "Alert: {number_of_stores} stores have $0 in sales". The alert can also be configured with a recurrence interval, such as every hour, to check the query result periodically. When the alert condition is met, the data engineer and their team will receive a notification via the messaging webhook, with the custom message and a link to the Databricks SQL query. The other options are either not suitable for sending notifications via a messaging webhook (A, B, E), or not suitable for sending recurring notifications?

  References: Databricks Documentation - Manage notification destinations, Databricks Documentation - Create alerts for Databricks SQL queries, Databricks Documentation - Configure alert conditions and messages.