Q1.An online payment service operates an Azure Databricks workspace with Unity Catalog enabled, which contains a catalog named Catalog1.
Catalog1 stores a table named Transactions.
The Transactions table has the following columns.
transaction_id (transaction ID)
customer_name (customer name)
email_address (email address)
credit_card_number (credit card number)
transaction_amount (transaction amount)
You need to configure access so that an assigned business analyst can query all rows of the Transactions table.
This configuration must meet all of the following requirements.
The analyst must not see the full values of the email_address and credit_card_number columns.
For each email address, the analyst must be able to see only the portion after the “@”.
For each credit card number, the analyst must be able to see only the last four digits.
The analyst must be able to query the table without errors.
Follow the principle of least privilege.
What should you do?
Show answer
A column mask does not return the original column value to the user as-is; instead it replaces it with the result of a user-defined function registered in Unity Catalog.
For email_address you can set a function that returns the part after “@”, and for credit_card_number a function that returns only the last four digits.
Because SELECT permission is granted on the table, the analyst can query all rows and the non-sensitive columns without errors.
A row filter restricts the rows themselves, which is not suitable for hiding column values in this case.
Removing SELECT permission on the sensitive columns would cause query errors, so column masks are what satisfy the least-privilege requirement.
Row filters and column masks
Column mask clause
Q2.A data analytics team uses an Azure Databricks workspace that has Unity Catalog enabled and a catalog named catalog1.
The workspace contains a group named group1.
You plan to create a schema named schema1 inside catalog1.
You need to configure group1 to meet all of the following requirements.
It can create tables in schema1.
It can modify and query the tables.
It cannot grant permissions on the schema or its objects.
How should you complete the following SQL statement (the GRANT statement that follows CREATE SCHEMA catalog1.schema1;)?
To answer, select the appropriate options in the answer area.
Show answer
As posed in this question, USAGE allows the use of schema1, and CREATE TABLE allows creating tables within the schema.
Because MANAGE and ownership are not granted, group1 is not given the ability to manage permissions.
Note that current Unity Catalog uses USE CATALOG and USE SCHEMA instead of USAGE, and the grantee uses the TO principal form rather than TO ROLE.
To let group1 query and modify all existing tables, grant SELECT and MODIFY separately.
This question is based on the older syntax.
Unity Catalog privileges reference
GRANT
Q3.A team operates an Azure Databricks workspace that has Unity Catalog enabled.
You need to create an external volume named Volume1 in an existing schema.
Volume1 is responsible for exposing files inside an Azure Storage container.
This configuration must meet all of the following requirements.
For authentication, no credentials should have to be stored inside Databricks.
Users can access the files but cannot modify them.
Follow the principle of least privilege.
Which type of authentication should you configure, and which permission should you grant to users?
To answer, select the appropriate options in the answer area.
Show answer
An Azure Databricks Access Connector uses a managed identity to connect to Azure Storage, so there is no need to store or rotate credentials such as client secrets inside Databricks.
READ VOLUME allows reading the files and directories in the volume but does not allow adding, deleting, or modifying them.
BROWSE is a permission for discovering objects and cannot read file contents.
WRITE VOLUME allows modifying files, which is outside the requirements.
Therefore, the combination of a Databricks access connector and READ VOLUME satisfies least privilege.
Connect to an Azure Data Lake Storage Gen2 (ADLS Gen2) external location
Q4.Note: This section contains one or more sets of questions that address the same scenario and problem.
Each question presents a distinct solution to the problem.
Determine whether the presented solution meets the stated goals.
Within a set, more than one solution may solve the problem, or none of the solutions may solve it.
After you answer a question in this section, you cannot return to previous questions.
As a result, these questions do not appear on the review screen.
You have an Azure Databricks workspace named Workspace1 that has a lakehouse and Unity Catalog enabled.
A connection to a Microsoft SQL Server database named DB1 is also configured.
You need to expose the schemas and tables of DB1 and meet all of the following requirements.
The schemas and tables can be queried in Databricks.
The schemas and tables appear alongside other Unity Catalog objects.
The data is not copied to Databricks-managed storage.
Solution: You create a Lakeflow Connect pipeline and connect to DB1.
Does this solution meet the goal?
Show answer
Lakeflow Connect ingests data from SQL Server into Azure Databricks and stores it in destination Delta tables, so it does not meet the requirement of “not copying the data to Databricks-managed storage.”
To query the DB1 data without moving it, use Lakehouse Federation to create a foreign catalog from the connection.
A foreign catalog surfaces the schemas and tables of the external database inside Unity Catalog and provides read-only access to the data on the original SQL Server.
Therefore the answer is No.
Run federated queries on Microsoft SQL Server
Manage and work with foreign catalogs
Q5.Note: This section contains one or more sets of questions that address the same scenario and problem.
Each question presents a distinct solution to the problem.
Determine whether the presented solution meets the stated goals.
Within a set, more than one solution may solve the problem, or none of the solutions may solve it.
After you answer a question in this section, you cannot return to previous questions.
As a result, these questions do not appear on the review screen.
You have an Azure Databricks workspace named Workspace1 that has a lakehouse and Unity Catalog enabled.
A connection to a Microsoft SQL Server database named DB1 is also configured.
You need to expose the schemas and tables of DB1 and meet all of the following requirements.
The schemas and tables can be queried in Databricks.
The schemas and tables appear alongside other Unity Catalog objects.
The data is not copied to Databricks-managed storage.
Solution: You create a Databricks access connector.
Does this solution meet the goal?
Show answer
To expose the schemas and tables of DB1 to Unity Catalog without copying data, create a foreign catalog using the existing connection.
A foreign catalog mirrors the external database and lets you run read-only federated queries from Databricks.
An Azure Databricks access connector is a resource that associates a managed identity with Databricks and is used for authentication to Azure Storage and similar services; it does not register SQL Server schemas or tables in Unity Catalog.
Therefore this solution does not meet the requirements.
Run federated queries on Microsoft SQL Server
Manage and work with foreign catalogs
Q6.Note: This section contains one or more sets of questions that address the same scenario and problem.
Each question presents a distinct solution to the problem.
Determine whether the presented solution meets the stated goals.
Within a set, more than one solution may solve the problem, or none of the solutions may solve it.
After you answer a question in this section, you cannot return to previous questions.
As a result, these questions do not appear on the review screen.
You have an Azure Databricks workspace named Workspace1 that has a lakehouse and Unity Catalog enabled.
A connection to a Microsoft SQL Server database named DB1 is also configured.
You need to expose the schemas and tables of DB1 and meet all of the following requirements.
The schemas and tables can be queried in Databricks.
The schemas and tables appear alongside other Unity Catalog objects.
The data is not copied to Databricks-managed storage.
Solution: You create a foreign catalog in Catalog Explorer.
Does this solution meet the goal?
Show answer
A foreign catalog mirrors an external database inside Unity Catalog and lets its schemas and tables be displayed and queried just like other catalog objects.
With query federation through Lakehouse Federation, the data on SQL Server is queried read-only while remaining in place, so no copy is made to Databricks-managed storage.
Therefore, this solution of creating a foreign catalog in Catalog Explorer meets all requirements.
What is query federation?
Manage and work with foreign catalogs
Q7.Note: This section contains one or more sets of questions that address the same scenario and problem.
Each question presents a distinct solution to the problem.
Determine whether the presented solution meets the stated goals.
Within a set, more than one solution may solve the problem, or none of the solutions may solve it.
After you answer a question in this section, you cannot return to previous questions.
As a result, these questions do not appear on the review screen.
You have an Azure Databricks workspace named Workspace1 that has a lakehouse and Unity Catalog enabled.
A connection to a Microsoft SQL Server database named DB1 is also configured.
You need to expose the schemas and tables of DB1 and meet all of the following requirements.
The schemas and tables can be queried in Databricks.
The schemas and tables appear alongside other Unity Catalog objects.
The data is not copied to Databricks-managed storage.
Solution: You create a new native catalog in Unity Catalog.
Does this solution meet the goal?
Show answer
Creating a new ordinary native catalog does not automatically reflect the schemas and tables of DB1 on SQL Server.
To display and query them inside Unity Catalog without copying data, you must use the existing connection and create a Lakehouse Federation foreign catalog.
A foreign catalog mirrors the external database and can run read-only queries while keeping the data on the original SQL Server.
Therefore this solution does not meet the requirements.
What is query federation?
Manage and work with foreign catalogs
Q8.You have an Azure Databricks workspace that uses serverless compute.
You need to ingest data by using Lakeflow Jobs.
New records must be processed immediately as they become available.
Which type of job trigger should you use for this ingestion?
Show answer
A continuous trigger keeps the job always running and starts the next run as soon as a run completes or fails.
This suits the requirement to continuously process new records without waiting for a fixed schedule.
A file arrival trigger is limited to cases where a new file arrives in a monitored Unity Catalog storage location.
In this question the input is not specified to be files, so a continuous trigger is appropriate.
Note that the continuous trigger of Lakeflow Jobs is a different feature from Spark’s Continuous Processing trigger.
Automate jobs using schedules and triggers
Production considerations for Structured Streaming
Q9.You have an Azure Databricks workspace that has Unity Catalog enabled and a managed Delta table named Sales.
Sales stores transaction data and has the following columns.
transaction_id (string)
transaction_date (date)
amount (decimal)
You need to implement the following data quality requirements by using table-level data quality enforcement.
amount must be greater than 0.
transaction_id must not be NULL.
Invalid records must be rejected when written to the Sales table.
What should you do?
Show answer
A NOT NULL constraint prohibits storing NULL in transaction_id, and combined with a CHECK constraint that defines amount as greater than 0, writes that do not meet the conditions are rejected at the table level.
These are data quality constraints enforced on Delta tables.
A WHERE clause or a view only excludes invalid rows at query time and cannot prevent invalid data from being written to the underlying table.
RLS is also a feature that controls the range of rows visible and is not used for data quality validation.
Constraints on Azure Databricks
ADD CONSTRAINT clause
Q10.You have an Azure Databricks workspace that has Unity Catalog enabled and two Delta tables named Table1 and Table2 that use the same data types.
Table1 has a column named Column1, and Table2 has a column named Column2.
You run the following query.
SELECT Column1
FROM Table1
GROUP BY Column1
HAVING COUNT() > 1
INTERSECT
SELECT Column2
FROM Table2
GROUP BY Column2
HAVING COUNT() > 1;
What is the result of running this query?
Show answer
In each SELECT, GROUP BY groups rows by each column value, and HAVING COUNT(*) > 1 extracts only the values that appear more than once within that table.
HAVING is a clause that filters the aggregated results of GROUP BY by a condition.
INTERSECT returns only the values that exist in both the preceding and following SELECT results.
Because ALL is not specified, it is processed as the default DISTINCT and duplicates in the result are also removed.
Therefore, values that appear more than once in both Table1 and Table2 are displayed once each.
Set operators
HAVING clause