Databricks Certified Data Engineer Associate Exam Practice Test Instant Access

Question 1

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?

AReview the Permissions tab in the table's page in Data Explorer

BAll of these options can be used to identify the owner of the table

CReview the Owner field in the table's page in Data Explorer

DReview the Owner field in the table's page in the cloud storage solution

EThere is no way to identify the owner of the table

Answer : C

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.

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 2

A data analyst has a series of queries in a SQL program. The data analyst wants this program to run every day. They only want the final query in the program to run on Sundays. They ask for help from the data engineering team to complete this task.

Which of the following approaches could be used by the data engineering team to complete this task?

AThey could submit a feature request with Databricks to add this functionality.

BThey could wrap the queries using PySpark and use Python's control flow system to determine when to run the final query.

CThey could only run the entire program on Sundays.

DThey could automatically restrict access to the source table in the final query so that it is only accessible on Sundays.

EThey could redesign the data model to separate the data used in the final query into a new table.

Answer : B

This approach would allow the data engineering team to use the existing SQL program and add some logic to control the execution of the final query based on the day of the week. They could use thedatetimemodule in Python to get the current date and check if it is a Sunday. If so, they could run the final query, otherwise they could skip it. This way, they could schedule the program to run every day without changing the data model or the source table.Reference:PySpark SQL Module,Python datetime Module,Databricks Jobs

Question 3

Which of the following Git operations must be performed outside of Databricks Repos?

ACommit

BPull

CPush

DClone

EMerge

Answer : E

Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as commit, pull, push, branch management, and visual comparison of diffs when committing1.However, merge is not supported in the Git dialog2.You need to use the Repos UI or your Git provider to merge branches3.Merge is a way to combine the commit history from one branch into another branch1. During a merge, a merge conflict is encountered when Git cannot automatically combine code from one branch into another.Merge conflicts require manual resolution before a merge can be completed1.Reference:4: Run Git operations on Databricks Repos4,1: CI/CD techniques with Git and Databricks Repos1,3: Collaborate in Repos3,2: Databricks Repos - What it is and how we can use it2.

Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as commit, pull, push, merge, and branch management. However, to clone a remote Git repository to a Databricks repo, you must use the Databricks UI or API.You cannot clone a Git repo using the CLI through a cluster's web terminal, as the files won't display in the Databricks UI1.Reference:1: Run Git operations on Databricks Repos | Databricks on AWS2

Question 4

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?

Aif day_of_week = 1 and review_period:

Bif day_of_week = 1 and review_period = 'True':

Cif day_of_week == 1 and review_period == 'True':

Dif day_of_week == 1 and review_period:

Eif day_of_week = 1 & review_period: = 'True':

Answer : D

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 thereview_periodvariable to a string value'True', which is different from the boolean valueTrue. Option D is the correct answer, as it uses the==operator to compare theday_of_weekvariable to the integer value1, and theandoperator to check if both conditions are true. If both conditions are true, then the final block of the Python program will be executed.Reference: [Python Operators], [Python If ... Else]

Question 5

A data engineer wants to schedule their Databricks SQL dashboard to refresh every hour, but they only want the associated SQL endpoint to be running when it is necessary. The dashboard has multiple queries on multiple datasets associated with it. The data that feeds the dashboard is automatically processed using a Databricks Job.

Which of the following approaches can the data engineer use to minimize the total running time of the SQL endpoint used in the refresh schedule of their dashboard?

AThey can turn on the Auto Stop feature for the SQL endpoint.

BThey can ensure the dashboard's SQL endpoint is not one of the included query's SQL endpoint.

CThey can reduce the cluster size of the SQL endpoint.

DThey can ensure the dashboard's SQL endpoint matches each of the queries' SQL endpoints.

EThey can set up the dashboard's SQL endpoint to be serverless.

Answer : A

The Auto Stop feature allows the SQL endpoint to automatically stop after a specified period of inactivity. This can help reduce the cost and resource consumption of the SQL endpoint, as it will only run when it is needed to refresh the dashboard or execute queries. The data engineer can configure the Auto Stop setting for the SQL endpoint from the SQL Endpoints UI, by selecting the desired idle time from the Auto Stop dropdown menu. The default idle time is 120 minutes, but it can be set to as low as 15 minutes or as high as 240 minutes. Alternatively, the data engineer can also use the SQL Endpoints REST API to set the Auto Stop setting programmatically.Reference:SQL Endpoints UI,SQL Endpoints REST API,Refreshing SQL Dashboard

Question 6

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?

AThe STREAM function is not needed and will cause an error.

BThe table being created is a live table.

CThe customers table is a streaming live table.

DThe customers table is a reference to a Structured Streaming query on a PySpark DataFrame.

EThe data in the customers table has been updated since its last run.

Answer : C

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.Reference:Difference between LIVE TABLE and STREAMING LIVE TABLE,CREATE STREAMING TABLE,Load data using streaming tables in Databricks SQL.

Question 7

A new data engineering team has been assigned to work on a project. The team will need access to database customers in order to see what tables already exist. The team has its own group team.

Which of the following commands can be used to grant the necessary permission on the entire database to the new team?

AGRANT VIEW ON CATALOG customers TO team;

BGRANT CREATE ON DATABASE customers TO team;

CGRANT USAGE ON CATALOG team TO customers;

DGRANT CREATE ON DATABASE team TO customers;

EGRANT USAGE ON DATABASE customers TO team;

Answer : E

: The correct command to grant the necessary permission on the entire database to the new team is to use theGRANT USAGEcommand. The GRANT USAGE command grants the principal the ability to access the securable object, such as a database, schema, or table. In this case, the securable object is the database customers, and the principal is the group team. By granting usage on the database, the team will be able to see what tables already exist in the database. Option E is the only option that uses the correct syntax and the correct privilege type for this scenario. Option A uses the wrong privilege type (VIEW) and the wrong securable object (CATALOG). Option B uses the wrong privilege type (CREATE), which would allow the team to create new tables in the database, but not necessarily see the existing ones. Option C uses the wrong securable object (CATALOG) and the wrong principal (customers). Option D uses the wrong securable object (team) and the wrong principal (customers).Reference:GRANT,Privilege types,Securable objects,Principals