Free Databricks Databricks-Certified-Data-Engineer-Associate Practice Exam with Questions & Answers | Set: 2

The MERGE INTO command is used to perform upserts, which are a combination of insertions and updates, based on a source table into a target Delta table1. The MERGE INTO command can handle scenarios where the target table cannot contain duplicate records, such as when there is a primary key or a unique constraint on the target table. The MERGE INTO command can match the source and target rows based on a merge condition and perform different actions depending on whether the rows are matched or not. For example, the MERGE INTO command can update the existing target rows with the new source values, insert the new source rows that do not exist in the target table, or delete the target rows that do not exist in the source table1.

The INSERT INTO command is used to append new rows to an existing table or create a new table from a query result2. The INSERT INTO command does not perform any updates or deletions on the existing target table rows. The INSERT INTO command can handle scenarios where the location of the data needs to be changed, such as when the data needs to be moved from one table to another, or when the data needs to be partitioned by a certain column2. The INSERT INTO command can also handle scenarios where the target table is an external table, such as when the data is stored in an external storage system like Amazon S3 or Azure Blob Storage3. The INSERT INTO command can also handle scenarios where the source table can be deleted, such as when the source table is a temporary table or a view4. The INSERT INTO command can also handle scenarios where the source is not a Delta table, such as when the source is a Parquet, CSV, JSON, or Avro file5.

1: MERGE INTO | Databricks on AWS

2: [INSERT INTO | Databricks on AWS]

3: [External tables | Databricks on AWS]

4: [Temporary views | Databricks on AWS]

5: [Data sources | Databricks on AWS]

The DELETE command in Delta Lake allows you to remove data that matches a predicate from a Delta table. This command will delete all the rows where the value in the column age is greater than 25 from the existing Delta table my_table and save the updated table. The other options are either incorrect or do not achieve the desired result. Option A will only select the rows that match the predicate, but not delete them. Option B will update the rows that match the predicate, but not delete them. Option D will update the rows that do not match the predicate, but not delete them. Option E will delete the rows that do not match the predicate, which is the opposite of what we want. References: Table deletes, updates, and merges — Delta Lake Documentation

The reason why all of the data files and metadata files were deleted from the file system after dropping the table is that the table was managed. A managed table is a table that is created and managed by Spark SQL. It stores both the data and the metadata in the default location specified by the spark.sql.warehouse.dir configuration property. When a managed table is dropped, both the data and the metadata are deleted from the file system.

Option B is not correct, as the size of the table’s data does not affect the behavior of dropping the table. Whether the table’s data is smaller or larger than 10 GB, the data files and metadata files will be deleted if the table is managed, and will be preserved if the table is external.

Option C is not correct, for the same reason as option B.

Option D is not correct, as an external table is a table that is created and managed by the user. It stores the data in a user-specified location, and only stores the metadata in the Spark SQL catalog. When an external table is dropped, only the metadata is deleted from the catalog, but the data files are preserved in the file system.

Option E is not correct, as a table must have a location to store the data. If the location is not specified by the user, it will use the default location for managed tables. Therefore, a table without a location is a managed table, and dropping it will delete both the data and the metadata.

Managing Tables

[Databricks Data Engineer Professional Exam Guide]

 To set up the new task to run a new notebook prior to the original task in a single-task Job, the data engineer can use the following approach: In the existing Job, create a new task that corresponds to the new notebook that needs to be run. Set up the new task with the appropriate configuration, specifying the notebook to be executed and any necessary parameters or dependencies. Once the new task is created, designate it as a dependency of the original task in the Job configuration. This ensures that the new task is executed before the original task.

 Delta Live Tables expectations are optional clauses that apply data quality checks on each record passing through a query. An expectation consists of a description, a boolean statement, and an action to take when a record fails the expectation. The ON VIOLATION clause specifies the action to take, which can be one of the following: warn, drop, or fail. The drop action means that invalid records are dropped from the target dataset before data is written to the target. The failure is reported as a metric for the dataset, which can be viewed by querying the Delta Live Tables event log. The event log contains information such as the number of records that violate an expectation, the number of records dropped, and the number of records written to the target dataset. References:

Manage data quality with Delta Live Tables

Monitor Delta Live Tables pipelines

Delta Live Tables SQL language reference

A webhook alert destination is a way to send notifications to external applications or services via HTTP requests. A data engineer can use a webhook alert destination to notify their entire team via a messaging webhook, such as Slack or Microsoft Teams, whenever the number of NULL values in the input data reaches 100. To set up a webhook alert destination, the data engineer needs to do the following steps:

In the Databricks SQL workspace, navigate to the Settings gear icon and select SQL Admin Console.

Click Alert Destinations and click Add New Alert Destination.

Select Webhook and enter the webhook URL and the optional custom template for the notification message.

Click Create to save the webhook alert destination.

In the Databricks SQL editor, create or open the query that returns the number of input records containing unexpected NULL values.

Click the Create Alert icon above the editor window and configure the alert criteria, such as the value column, the condition, and the threshold.

In the Notification section, select the webhook alert destination that was created earlier and click Create Alert. References: What are Databricks SQL alerts?, Monitor alerts, Monitoring Your Business with Alerts, Using Automation Runbook Webhooks To Alert on Databricks Status Updates.