Data Engineer Professional · Domain 2: Data Ingestion & Acquisition · 12% of the exam
Task 2.5: Configure Lakehouse Federation with governance across supported source systems, applying UC permissions and connection-level credentials.
Connections and the credentials they hold, foreign catalogs, privileges on connections and foreign objects, pushdown, and catalog federation.
Study it
Lakehouse Federation: connections, foreign catalogs and permissions
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
A platform team created a PostgreSQL connection and a foreign catalog pg_erp for it. Analysts must query only the sales schema of the database, through Unity Catalog, and must never see the database credentials or reach other schemas. Which grants follow least privilege?
AUSE CONNECTION on the connection, plus USE CATALOG on pg_erp
BMANAGE on the foreign catalog pg_erp for the analysts' group
CUSE CATALOG on pg_erp; USE SCHEMA and SELECT on pg_erp.sales
DUSE CATALOG, USE SCHEMA and SELECT on the whole pg_erp catalog
Show the answer and why
AUSE CONNECTION on the connection, plus USE CATALOG on pg_erp
Incorrect
USE CONNECTION shows the connection details and allows remote_query, which could reach schemas outside sales.
BMANAGE on the foreign catalog pg_erp for the analysts' group
Incorrect
MANAGE is an administrative privilege on the catalog, far beyond querying one schema.
CUSE CATALOG on pg_erp; USE SCHEMA and SELECT on pg_erp.sales
Correct
Foreign catalogs are governed like other catalogs; the connection's credentials are used on the analysts' behalf and are never exposed.
DUSE CATALOG, USE SCHEMA and SELECT on the whole pg_erp catalog
Incorrect
SELECT on the catalog is inherited by every schema in it, so analysts would reach all schemas.
A connection stores the path and credentials for the external database, and a foreign catalog mirrors a database for read-only queries governed by Unity Catalog privileges. USE CONNECTION is needed only to view the connection or to run remote_query.
A data engineer must create a Unity Catalog connection to a MySQL database for Lakehouse Federation. Which privilege do they need if they are not a metastore admin?
ACREATE CATALOG on the metastore
BUSE CONNECTION on the metastore
CCREATE CONNECTION on the metastore
DCREATE EXTERNAL LOCATION on a storage credential
Show the answer and why
ACREATE CATALOG on the metastore
Incorrect
That is needed later for the foreign catalog, not for the connection.
BUSE CONNECTION on the metastore
Incorrect
USE CONNECTION applies to an existing connection.
CCREATE CONNECTION on the metastore
Correct
Creating a connection requires metastore admin or CREATE CONNECTION on the metastore.
DCREATE EXTERNAL LOCATION on a storage credential
Incorrect
That is for cloud storage, not database connections.
A connection specifies a path and credentials for an external database. Creating one needs metastore admin or CREATE CONNECTION on the metastore.
A connection to PostgreSQL is owned by the platform team. A data engineer who already has CREATE CATALOG on the metastore must create a foreign catalog from it. What else do they need?
AUSE CONNECTION on the PostgreSQL connection
BMANAGE on the PostgreSQL database user
CCREATE CATALOG on the connection
DCREATE FOREIGN CATALOG on the connection
Show the answer and why
AUSE CONNECTION on the PostgreSQL connection
Incorrect
USE CONNECTION lets a user view the connection and run remote_query.
BMANAGE on the PostgreSQL database user
Incorrect
Database users are not Unity Catalog securables.
CCREATE CATALOG on the connection
Incorrect
CREATE CATALOG is a metastore privilege, which they already hold; it is not granted on connections.
DCREATE FOREIGN CATALOG on the connection
Correct
Creating a foreign catalog requires CREATE CATALOG on the metastore and either ownership of the connection or CREATE FOREIGN CATALOG on it.
To create a foreign catalog, a user needs CREATE CATALOG on the metastore and must own the connection or hold CREATE FOREIGN CATALOG on it.
A federated query against PostgreSQL filters rows, then ranks them with a window function. Which part does Lakehouse Federation not push down to PostgreSQL?
AThe window function
BThe WHERE filter
CA LIMIT clause
DComparison operators in the filter
Show the answer and why
AThe window function
Correct
The PostgreSQL pushdown table lists window functions as not supported.
BThe WHERE filter
Incorrect
Filters are pushed down on all compute.
CA LIMIT clause
Incorrect
Limits are pushed down on all compute.
DComparison operators in the filter
Incorrect
Comparison operators are pushed down.
Federation pushes supported operations, such as filters, limits and sorting with a limit, to PostgreSQL and runs the rest in Databricks. Window functions are not pushed down.