Lakehouse Federation: Connections, Foreign Catalogs and UC Permissions

1.Two securables: the connection and the foreign catalog

Lakehouse Federation lets you query data that lives in another system without first copying it into Databricks. The docs describe it as the Databricks query federation platform, which gives governed, read-only access to external data through Unity Catalog foreign catalogs. Governance is the reason to use it instead of a quick JDBC read. Because the external data shows up as Unity Catalog objects, you get the same privilege model, lineage and search that you have for your own tables.

To make an external dataset queryable you create two objects. Both are securables, so both can be owned and granted on. The connection stores the path to the external system and the credentials for it. The foreign catalog is a mirror of one database in that system. It appears in the three-level namespace next to your normal catalogs.

This split puts credentials at the connection level. The secret is held in one place, the connection, and every foreign catalog that uses the connection reaches the external system with that identity. Users are never handed the database password. Instead you give them Unity Catalog privileges on the foreign catalog's tables. The foreign catalog is the securable that mirrors the external database so you can run read-only queries and control access with Unity Catalog.

A connection is also a different kind of object from the other credential-like objects in Unity Catalog. Storage credentials cover cloud object storage, and service credentials cover non-storage cloud services. A connection covers neither. It is the object for external systems such as databases.

Sources123

2.Query federation vs catalog federation, and the supported sources

Lakehouse Federation comes in two types, and the exam expects you to pick the right one for a scenario. Both are read-only, and both govern access through a foreign catalog with table-level controls. The difference is where the query runs.

With query federation, Unity Catalog pushes the query down to the external database over JDBC. The work is split between Databricks and the remote system's compute. With catalog federation, Databricks connects to an external catalog service and reads the table files directly from object storage. The query runs only on Databricks compute, so the docs call catalog federation more cost-effective and performance-optimized than query federation. Because catalog federation reads storage directly, it needs two more objects: a storage credential and an external location for the table paths.

Snowflake and Salesforce Data 360 appear in both lists, so the source system alone doesn't decide the type. Look at the pattern instead. Live JDBC pushdown means query federation. Reading the source's storage directly means catalog federation.

Federation also has limits. If you need to write to the external system, or your source isn't on these lists, the docs point you to the Spark Data Source API. When a source supports both federation and Lakeflow Connect, and performance on high data volumes or low latency matters, Databricks recommends Lakeflow Connect ingestion. Keep query federation for ad hoc reporting or proof-of-concept work.

Sources21

3.Creating a connection with connection-level credentials

Several prerequisites apply before you create a connection.

- Workspace: it must be enabled for Unity Catalog. Workspaces created after November 8, 2023 are enabled automatically. - Cluster compute: Databricks Runtime 13.3 LTS or above, in Standard or Dedicated access mode. - SQL warehouses: pro or serverless, version 2023.40 or above. - Network: the compute must be able to reach the target database. - Privilege: you must be a metastore admin or hold CREATE CONNECTION on the metastore. In workspaces that were enabled for Unity Catalog automatically, workspace admins have CREATE CONNECTION by default.

You can create a connection in Catalog Explorer, with the CREATE CONNECTION SQL command, or through the REST API or CLI. In SQL, CREATE CONNECTION accepts these TYPE values: DATABRICKS, HTTP, MYSQL, POSTGRESQL, REDSHIFT, SNOWFLAKE, SQLDW, SQLSERVER. SaaS ingestion types can only be created in the UI or through the APIs. The options you supply depend on the connection type. PostgreSQL needs a host, port, user and password. Databricks recommends storing the sensitive values as Databricks secrets, not as plaintext strings.

CREATE CONNECTION <connection-name> TYPE postgresql
OPTIONS (
  host '<hostname>',
  port '<port>',
  user secret ('<secret-scope>','<secret-key-user>'),
  password secret ('<secret-scope>','<secret-key-password>')
)

Some connection options control trust, not identity. On PostgreSQL the connection is always encrypted with SSL. If the server presents a certificate from a private certificate authority, you can pass its PEM-encoded certificate in userProvidedServerCertificate. This option is an alternative to trustServerCertificate, which skips verification, and it takes precedence when both are set.

Network access is a separate matter. The docs describe outbound security in two layers. Unity Catalog connections govern credentials and connection configuration such as the URL, host, port and options. Network policies govern which endpoints traffic can reach. A connection does not restrict network access at all.

Sources134

4.Creating the foreign catalog

If you create the connection in the Catalog Explorer UI, the wizard creates the foreign catalog in the same flow. It then asks which workspaces can access the catalog, who owns it, and which privilege presets to grant. All account users get BROWSE by default. Data Reader grants read privileges, and Data Editor grants read and modify privileges.

In SQL, creating the foreign catalog is a separate step and needs two privileges:

1. CREATE CATALOG on the metastore. 2. Either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

The statement names the connection with USING CONNECTION and names the external database in OPTIONS. MySQL is the exception: it uses a two-layer namespace, so you don't supply a database. The statement never includes credentials, because they come from the connection. Foreign catalog metadata is synced into Unity Catalog on each interaction with the catalog, so the mirror stays current without a scheduled refresh.

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING  ?  <connection-name>
OPTIONS (database '<database-name>');

Sources1

5.Governing access: connection privileges and table-level grants

Governance works at two levels. Connection-level privileges decide who can build on the connection or use it. Table-level privileges on the foreign catalog decide who can read the mirrored data.

The connection has two privileges you can grant:

- CREATE FOREIGN CATALOG lets a user create a foreign catalog that is a read-only mirror of a database in the connection's data source. - USE CONNECTION lets a user use the connection in Lakeflow pipelines to ingest data from the source into Databricks.

USE CONNECTION behaves differently at the metastore level. Granted on the metastore, it only lets the user view the details of connections. To actually use a connection, the user still needs USE CONNECTION on that specific connection.

Only a metastore admin or the connection owner can grant or revoke these privileges. Only the connection owner can drop the connection.

GRANT CREATE FOREIGN CATALOG ON CONNECTION <connection-name> TO <user-name>;

You can inspect connections with SHOW CONNECTIONS [LIKE <pattern>]; and DESCRIBE CONNECTION <connection-name>;. Which connections you see depends on your privileges. Users with USE CONNECTION on the metastore see all of them. Everyone else sees only the connections they own or hold some privilege on. To revoke a privilege, run REVOKE <privilege> ON CONNECTION <connection-name> FROM <user-name>;. To remove a connection, run DROP CONNECTION [IF EXISTS] <connection-name>;.

Once the foreign catalog exists, you treat it like any other catalog. You grant read-only access to foreign tables with standard Unity Catalog privileges, and Databricks tracks lineage for them the same way it does for standard tables. Queries must use Apache Spark data types.

The access mode of the compute changes who can query. On standard access mode and serverless compute, the standard catalog privileges apply. On dedicated access mode compute, the user must own the underlying connection to query the foreign catalog. Holding CREATE FOREIGN CATALOG on the connection is not enough.

Sources56

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Granting USE CONNECTION on the metastore lets a user use every connection in it.Why is that wrong?

   At the metastore level, USE CONNECTION only allows viewing connection details. The user still needs USE CONNECTION on each individual connection to use it.

   Covered in Governing access: connection privileges and table-level grants

2. 2.Anyone with CREATE FOREIGN CATALOG on a connection can query the resulting foreign catalog on any compute.Why is that wrong?

   On dedicated access mode compute, only the connection owner can query the foreign catalog. Standard catalog privileges are enough only on standard access mode and serverless compute.

   Covered in Governing access: connection privileges and table-level grants

3. 3.Lakehouse Federation can write back to the external database through the foreign catalog.Why is that wrong?

   Both query federation and catalog federation are read-only. For write access, the docs point to the Spark Data Source API.

   Covered in Query federation vs catalog federation, and the supported sources

4. 4.Defining a Unity Catalog connection also restricts which external hosts compute can reach.Why is that wrong?

   Connections govern credentials and configuration only. Network policies control allowed traffic.

   Covered in Creating a connection with connection-level credentials