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

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

• Question 131:

  A data engineer wants to create a SQL UDF that converts text to lowercase.

  Which of the following code blocks correctly creates this SQL UDF in Databricks SQL?

  A. CREATE FUNCTION to_lowercase(text STRING) AS RETURN lower(text);
  B. CREATE OR REPLACE FUNCTION to_lowercase(text STRING) RETURNS STRING RETURN lower(text);
  C. CREATE FUNCTION to_lowercase(text STRING) RETURNS STRING BEGIN RETURN lower(text); END;
  D. CREATE FUNCTION to_lowercase(text STRING) RETURNS STRING RETURN upper(text);
  E. CREATE TEMP FUNCTION to_lowercase AS 'lower(text)';
  B. CREATE OR REPLACE FUNCTION to_lowercase(text STRING) RETURNS STRING RETURN lower(text);

  ---

  Explanation

• Question 132:

  Which of the following describes the relationship between Gold tables and Silver tables?

  A. Gold tables are more likely to contain aggregations than Silver tables.
  B. Gold tables are more likely to contain valuable data than Silver tables.
  C. Gold tables are more likely to contain a less refined view of data than Silver tables.
  D. Gold tables are more likely to contain more data than Silver tables.
  E. Gold tables are more likely to contain truthful data than Silver tables.
  A. Gold tables are more likely to contain aggregations than Silver tables.

  ---

  Explanation

  According to the medallion lakehouse architecture, gold tables are the final layer of data that powers analytics, machine learning, and production applications. They are often highly refined and aggregated, containing data that has been

  transformed into knowledge, rather than just information. Silver tables, on the other hand, are the intermediate layer of data that represents a validated, enriched version of the raw data from the bronze layer. They provide an enterprise view

  of all its key business entities, concepts and transactions, but they may not have all the aggregations and calculations that are required for specific use cases. Therefore, gold tables are more likely to contain aggregations than silver tables.

  References:

  What is the medallion lakehouse architecture?

  What is a Medallion Architecture?

• Question 133:

  Differentiate between all-purpose clusters and jobs clusters.

  A data engineering team has created a python notebook to load data from cloud storage, this job has been tested and now needs to be scheduled in production.

  Which would be the best cluster to be used in this case?

  A. All purpose cluster
  B. Any Unity Catalog-enabled cluster
  C. Jobs Cluster
  D. Serverless SQL warehouse
  C. Jobs Cluster

  ---

  Explanation

• Question 134:

  A single Job runs two notebooks as two separate tasks. A data engineer has noticed that one of the notebooks is running slowly in the Job's current run. The data engineer asks a tech lead for help in identifying why this might be the case. Which of the following approaches can the tech lead use to identify why the notebook is running slowly as part of the Job?

  A. They can navigate to the Runs tab in the Jobs UI to immediately review the processing notebook.
  B. They can navigate to the Tasks tab in the Jobs UI and click on the active run to review the processing notebook.
  C. They can navigate to the Runs tab in the Jobs UI and click on the active run to review the processing notebook.
  D. There is no way to determine why a Job task is running slowly.
  E. They can navigate to the Tasks tab in the Jobs UI to immediately review the processing notebook.
  C. They can navigate to the Runs tab in the Jobs UI and click on the active run to review the processing notebook.

  ---

  Explanation

• Question 135:

  Identify how the count_if function and the count where x is null can be used Consider a table random_values with below data.

  What would be the output of below query?

  select count_if(col > 1) as count_a. count(*) as count_b.count(col1) as count_c from random_values col1

  NULL

  A. 3 6 5
  B. 4 6 5
  C. 3 6 6
  D. 4 6 6
  A. 3 6 5

  ---

  Explanation

• Question 136:

  A data engineer has three tables in a Delta Live Tables (DLT) pipeline. They have configured the pipeline to drop invalid records at each table. They notice that some data is being dropped due to quality concerns at some point in the DLT pipeline. They would like to determine at which table in their pipeline the data is being dropped.

  Which of the following approaches can the data engineer take to identify the table that is dropping the records?

  A. They can set up separate expectations for each table when developing their DLT pipeline.
  B. They cannot determine which table is dropping the records.
  C. They can set up DLT to notify them via email when records are dropped.
  D. They can navigate to the DLT pipeline page, click on each table, and view the data quality statistics.
  E. They can navigate to the DLT pipeline page, click on the "Error" button, and review the present errors.
  D. They can navigate to the DLT pipeline page, click on each table, and view the data quality statistics.

  ---

  Explanation

  One of the features of DLT is that it provides data quality metrics for each dataset in the pipeline, such as the number of records that pass or fail expectations, the number of records that are dropped, and the number of records that are written to the target. These metrics can be accessed from the DLT pipeline page, where the data engineer can click on each table and view the data quality statistics for the latest update or any previous update. This way, they can identify which table

  is dropping the records and why.

  References:

  Monitor Delta Live Tables pipelines

  Manage data quality with Delta Live Tables

• Question 137:

  A data engineering team is using Kafka to capture event data and then ingest it into Databricks. The team wants to be able to see these historical events. Medallion architecture is already in place. The team wants to be mindful of costs. Where should this historical event data be stored?

  A. Gold
  B. Silver
  C. Bronze
  D. Raw layer
  C. Bronze

  ---

  Explanation

  In the Medallion architecture, the Bronze layer stores raw ingested data, including historical event data from Kafka. This approach preserves the full history at lower cost while allowing further refinement into Silver and Gold layers.

• Question 138:

  A data engineer needs access to a table new_table, but they do not have the correct permissions. They can ask the table owner for permission, but they do not know who the table owner is.

  Which of the following approaches can be used to identify the owner of new_table?

  A. Review the Permissions tab in the table's page in Data Explorer
  B. All of these options can be used to identify the owner of the table
  C. Review the Owner field in the table's page in Data Explorer
  D. Review the Owner field in the table's page in the cloud storage solution
  E. There is no way to identify the owner of the table
  C. Review the Owner field in the table's page in Data Explorer

  ---

  Explanation

  he approach that can be used to identify the owner of new_table is to review the Owner field in the table's page in Data Explorer. Data Explorer is a web-based interface that allows users to browse, create, and manage data objects such as tables, views, and functions in Databricks1. The table's page in Data Explorer provides various information about the table, such as its schema, partitions, statistics, history, and permissions2. The Owner field shows the name and email address of the user who created or owns the table3. The data engineer can use this information to contact the table owner and request for permission to access the table. The other options are not correct or reliable for identifying the owner of new_table. Reviewing the Permissions tab in the table's page in Data Explorer can show the users and groups who have access to the table, but not necessarily the owner4. Reviewing the Owner field in the table's page in the cloud storage solution can be misleading, as the owner of the data files may not be the same as the owner of the table5. There is a way to identify the owner of the table, as explained above, so option E is false.

  References:

  1: Data Explorer | Databricks on AWS

  2: Table details | Databricks on AWS

  3: Set owner when creating a view in databricks sql - Databricks - 9978

  4: Table access control | Databricks on AWS

  5: External tables | Databricks on AWS

• Question 139:

  A data engineer runs a statement every day to copy the previous day's sales into the table transactions. Each day's sales are in their own file in the location "/transactions/raw".

  Today, the data engineer runs the following command to complete this task:

  

  After running the command today, the data engineer notices that the number of records in table transactions has not changed. Which of the following describes why the statement might not have copied any new records into the table?

  A. The format of the files to be copied were not included with the FORMAT_OPTIONS keyword.
  B. The names of the files to be copied were not included with the FILES keyword.
  C. The previous day's file has already been copied into the table.
  D. The PARQUET file format does not support COPY INTO.
  E. The COPY INTO statement requires the table to be refreshed to view the copied rows.
  C. The previous day's file has already been copied into the table.

  ---

  Explanation

  The COPY INTO statement is an idempotent operation, which means that it will skip any files that have already been loaded into the target table. This ensures that the data is not duplicated or corrupted by multiple attempts to load the same file. Therefore, if the data engineer runs the same command every day without specifying the names of the files to be copied with the FILES keyword or a glob pattern with the PATTERN keyword, the statement will only copy the first file that matches the source location and ignore the rest. To avoid this problem, the data engineer should either use the FILES or PATTERN keywords to filter the files to be copied based on the date or some other criteria, or delete the files from the source location after they are copied into the table.

  References:

  1: COPY INTO | Databricks on AWS

  2: Get started using COPY INTO to load data | Databricks on AWS

• Question 140:

  A data engineer is working in a Databricks notebook to design and manage a batch ETL pipeline. The engineer is writing SQL and Python code to clean data, transform it, and join large datasets from different sources. The engineer wants to

  organize these steps into a structured process that can be run regularly and scheduled as part of a data pipeline.

  Which Databricks notebook feature is applicable in the use case?

  A. Real-time streaming support
  B. Collaborative editing
  C. Task workflows and job scheduling
  D. Notebook version control
  C. Task workflows and job scheduling

  ---

  Explanation

  Task workflows and job scheduling in Databricks allow engineers to structure ETL steps, run them in sequence, and schedule them for regular execution, making them suitable for production pipelines.