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

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

• Question 191:

  Which method is used in PySpark to execute SQL statements?

  A. spark.read.sql()
  B. spark.query()
  C. spark.sql()
  D. spark.run_sql()
  E. sql.spark()
  C. spark.sql()

  ---

  Explanation

  spark.sql() executes a SQL query and returns a DataFrame.

• Question 192:

  A data engineer has joined an existing project and they see the following query in the project repository:

  CREATE STREAMING LIVE TABLE loyal_customers AS

  SELECT customer_id

  FROM STREAM(LIVE.customers)

  WHERE loyalty_level = 'high';

  Which of the following describes why the STREAM function is included in the query?

  A. The STREAM function is not needed and will cause an error.
  B. The table being created is a live table.
  C. The customers table is a streaming live table.
  D. The customers table is a reference to a Structured Streaming query on a PySpark DataFrame.
  E. The data in the customers table has been updated since its last run.
  C. The customers table is a streaming live table.

  ---

  Explanation

  The STREAM function is used to process data from a streaming live table or view, which is a table or view that contains data that has been added only since the last pipeline update. Streaming live tables and views are stateful, meaning that they retain the state of the previous pipeline run and only process new data based on the current query. This is useful for incremental processing of streaming or batch data sources. The customers table in the query is a streaming live table, which means that it contains the latest data from the source. The STREAM function enables the query to read the data from the customers table incrementally and create another streaming live table named loyal_customers, which contains the customer IDs of the customers with high loyalty level.

  References: Difference between LIVE TABLE and STREAMING LIVE TABLE, CREATE STREAMING TABLE, Load data using streaming tables in Databricks SQL.

• Question 193:

  A data engineer has been using a Databricks SQL dashboard to monitor the cleanliness of the input data to an ELT job. The ELT job has its Databricks SQL query that returns the number of input records containing unexpected NULL values. The data engineer wants their entire team to be notified via a messaging webhook whenever this value reaches 100.

  Which of the following approaches can the data engineer use to notify their entire team via a messaging webhook whenever the number of NULL values reaches 100?

  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 a new webhook alert destination.
  D. They can set up an Alert with one-time notifications.
  E. They can set up an Alert without notifications.
  C. They can set up an Alert with a new webhook alert destination.

  ---

  Explanation

  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.

• Question 194:

  Which of the following describes the relationship between Bronze tables and raw data?

  A. Bronze tables contain less data than raw data files.
  B. Bronze tables contain more truthful data than raw data.
  C. Bronze tables contain aggregates while raw data is unaggregated.
  D. Bronze tables contain a less refined view of data than raw data.
  E. Bronze tables contain raw data with a schema applied.
  E. Bronze tables contain raw data with a schema applied.

  ---

  Explanation

  Bronze tables are the first layer of a medallion architecture, which is a data design pattern used to organize data in a lakehouse. Bronze tables contain raw data ingested from various sources, such as RDBMS data, JSON files, IoT data, etc. The table structures in this layer correspond to the source system table structures "as-is", along with any additional metadata columns that capture the load date/time, process ID, etc. The only transformation applied to the raw data in this layer is to apply a schema, which defines the column names and data types of the table. The schema can be inferred from the data source or specified explicitly. Applying a schema to the raw data enables the use of SQL and other structured query languages to access and analyze the data. Therefore, option E is the correct answer.

  References: What is a Medallion Architecture?, Raw Data Ingestion into Delta Lake Bronze tables using Azure Synapse Mapping Data Flow, Apache Spark + Delta Lake concepts, Delta Lake Architecture and Azure Databricks Workspace.

• Question 195:

  A data engineer only wants to execute the final block of a Python program if the Python variable day_of_week is equal to 1 and the Python variable review_period is True. Which of the following control flow statements should the data engineer use to begin this conditionally executed code block?

  A. if day_of_week = 1 and review_period:
  B. if day_of_week = 1 and review_period = "True":
  C. if day_of_week == 1 and review_period == "True":
  D. if day_of_week == 1 and review_period:
  E. if day_of_week = 1 and review_period: = "True":
  D. if day_of_week == 1 and review_period:

  ---

  Explanation

  In Python, the == operator is used to compare the values of two variables, while the = operator is used to assign a value to a variable. Therefore, option A and E are incorrect, as they use the = operator for comparison. Option B and C are also incorrect, as they compare the review_period variable to a string value "True", which is different from the boolean value True. Option D is the correct answer, as it uses the == operator to compare the day_of_week variable to the integer value 1, and the and operator to check if both conditions are true. If both conditions are true, then the final block of the Python program will be executed.

  References: [Python Operators], [Python If ... Else]

• Question 196:

  A Databricks single-task workflow fails at the last task due to an error in a notebook. The data engineer fixes the mistake in the notebook. What should the data engineer do to rerun the workflow?

  A. Repair the task
  B. Rerun the pipeline
  C. Restart the cluster
  D. Switch the cluster
  A. Repair the task

  ---

  Explanation

  For a single-task workflow, the correct action is to repair the task, which reruns only the failed task after fixing the error, avoiding unnecessary re-execution of the whole workflow.