What you will be able to do
- Explain what query federation does and where a federated query runs
- Decide when query federation fits better than ingesting with Lakeflow Connect
- Create a connection and a foreign catalog in SQL, keeping credentials in Databricks secrets
- State the compute and privilege requirements to set up and query federated data
Key concept
Foreign catalog — A Unity Catalog object that mirrors a database in an external system. Its tables appear under a normal catalog name and are governed like any other catalog, so you can query them next to your Delta tables without copying the data.
1.Querying data where it lives
Most analytics on Databricks reads Delta tables governed by Unity Catalog. The data you need to join against often lives somewhere else, though: an operational PostgreSQL database, a Snowflake account, or an Amazon Redshift cluster. Lakehouse Federation is the Databricks platform for reaching that data without copying it in first. It comes in two types. This page covers query federation, which connects to external relational databases over JDBC. Catalog federation, the other type, is covered later as a contrast.
That split matters. Databricks pushes the parts of the query that the foreign database can handle down to it over JDBC, and runs the rest itself. Because of this, a query can use the external system's compute, and you don't have to build an ETL job just to read a table once. The documentation names the situations where this pays off: on-demand reporting, proof-of-concept work, the exploratory phase of new ETL pipelines or reports, and supporting workloads during an incremental migration.
It also gives three conditions that point to query federation. You don't want to ingest the data into Databricks. You want your queries to use compute in the external database. And you want Unity Catalog governance, including fine-grained access control, lineage, and search, over data that stays where it is.
Federation is not the default for every external source. Databricks recommends ingesting with Lakeflow Connect managed connectors when volume and latency matter, because those connectors scale to high data volumes and give lower query latency. Federation is the right choice when you specifically want to query in place.
Checkpoint 1 of 5· Check yourself
A team needs a recurring, high-volume feed of orders from MySQL with low query latency. Separately, an analyst wants to check this week's numbers once against a PostgreSQL table. Which pairing matches Databricks guidance?
Managed ingestion connectors are recommended for high volumes and low latency. Query federation is the documented choice for ad hoc reporting and proof-of-concept work.
“choose query federation for ad hoc reporting or proof-of-concept work on your ETL pipelines”Source: docs.databricks.com
2.Two objects: a connection and a foreign catalog
Before you can query an external database, Unity Catalog needs two securable objects. A connection holds the path and credentials Databricks uses to reach the external system. A foreign catalog uses that connection to mirror one database from the external system as a catalog, so its schemas and tables appear under a normal catalog name. You can create both in Catalog Explorer, or with SQL in a notebook or the SQL query editor.
Here is the connection for a PostgreSQL server. The options differ by connection type. This version reads the user name and password from a Databricks secret scope instead of plaintext, which is what Databricks recommends.
CREATE CONNECTION postgresql_connection
TYPE POSTGRESQL
OPTIONS (
host 'qf-postgresql-demo.xxxxxx.us-west-2.rds.amazonaws.com',
port '5432',
user secret('secrets.r.us', 'postgresUser'),
password secret('secrets.r.us', 'postgresPassword'));Next, the foreign catalog names the connection and the remote database to mirror. You don't put credentials here, because the foreign catalog takes them from the named connection. Databricks keeps the mirrored schemas and relations in sync with the foreign source. Metadata is synced into Unity Catalog every time someone interacts with the catalog, so new remote tables show up without any extra step.
Checkpoint 2 of 5· Fill the gap
Which keyword completes this statement, which exposes a PostgreSQL database as a foreign catalog?
CREATE FOREIGN CATALOG postgresql_catalog
USING ? postgresql_connection
OPTIONS (database 'postgresdb');A foreign catalog is always built on an existing Unity Catalog connection, which it references with USING CONNECTION.
Source: docs.databricks.comCheckpoint 3 of 5· Put it in order
Put the steps for making an external PostgreSQL table queryable in Databricks SQL in order
- 1.Run queries, which are pushed down to the external database
- 2.Grant users privileges on tables in the foreign catalog
- 3.Create a connection in Unity Catalog with the access credentials
- 4.Create a foreign catalog that uses the connection
The foreign catalog depends on the connection. Grants apply to objects in the foreign catalog. Only then can users query.
“Create a connection in Unity Catalog with your access credentials and JDBC URL. Create a foreign catalog using the connection.”Source: docs.databricks.com
Checkpoint 4 of 5· Exam question
A data analyst wants to run a single query that joins a Databricks-managed Delta table with a table that lives in an external Snowflake database, without first copying the Snowflake data into Databricks. Which Databricks capability enables this?
Correct answer: A — Lakehouse Federation, which registers the external database as a foreign catalog in Unity Catalog and pushes query predicates down to the source for execution.
- A. Lakehouse Federation is correct because it mirrors the external database as a foreign catalog inside Unity Catalog, letting a single query join it against a Delta table while pushing filters and joins toward the source system where possible.
- B. Delta Sharing is incorrect because it is designed for sharing Delta table data out to other organizations, not for querying a live external database like Snowflake from inside Databricks.
- C. Auto Loader is incorrect because it ingests new files arriving in cloud object storage into Delta tables; it has no mechanism for reaching into a running external database at query time.
- D. Lakeflow Connect is incorrect here because it copies source data into Databricks on a schedule or continuously, which is a data-movement pattern rather than the live, no-copy cross-system join the scenario asks for.
3.Compute and privileges you need
Federation has requirements for both the compute you use and the privileges you hold. These details show up in scenario questions, where a query fails because the warehouse is the wrong type or the user lacks a privilege.
| Requirement | What the documentation states |
|---|---|
| Workspace | Enabled for Unity Catalog |
| Databricks compute | Databricks Runtime 13.3 LTS or above, Standard or Dedicated access mode |
| SQL warehouse | Pro or serverless, version 2023.40 or above |
| Network | Connectivity from your compute resource to the target database systems |
| Create a connection | Metastore admin, or the CREATE CONNECTION privilege on the metastore |
| Create a foreign catalog | CREATE CATALOG on the metastore, plus ownership of the connection or CREATE FOREIGN CATALOG on it |
In workspaces that were enabled for Unity Catalog automatically, workspace admins have CREATE CONNECTION and CREATE CATALOG by default. Elsewhere, a metastore admin has to grant them. Once the foreign catalog exists, you give access to its tables the same way as for any other catalog: at the catalog, schema, or table level.
Checkpoint 5 of 5· Check yourself
An analyst has SELECT on a foreign catalog's tables, but a federated query fails on the SQL warehouse attached to their dashboard. Which warehouse would explain the failure?
Query federation requires a pro or serverless SQL warehouse on 2023.40 or above. A classic warehouse doesn't qualify.
“SQL warehouses must be pro or serverless and must use 2023.40 or above.”Source: docs.databricks.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Any SQL warehouse can run a federated query as long as the user has privileges on the foreign catalog.Why is that wrong?
The warehouse type and version matter too. Only pro or serverless warehouses on version 2023.40 or above support query federation.
Covered in Compute and privileges you need
2.You supply the database user name and password when you run CREATE FOREIGN CATALOG.Why is that wrong?
Credentials belong to the connection. The foreign catalog only names the connection and the database to mirror.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The query is executed both in Databricks and using remote compute.”
↩︎ Querying data where it lives“Databricks recommends ingestion using Lakeflow Connect managed connectors because they scale to accommodate high data volumes and lower query latency.”
↩︎ Querying data where it lives“A connection, a securable object in Unity Catalog that specifies a path and credentials for accessing an external database system.”
↩︎ Two objects: a connection and a foreign catalog“We recommend that you use Databricks secrets instead of plaintext strings for sensitive values like credentials.”
↩︎ Two objects: a connection and a foreign catalog“If you use the UI to create a connection to the data source, foreign catalog creation is included and you can skip this step.”
↩︎ Two objects: a connection and a foreign catalog“Foreign catalog metadata is synced into Unity Catalog on each interaction with the catalog.”
↩︎ Two objects: a connection and a foreign catalog“Databricks compute must use Databricks Runtime 13.3 LTS or above and Standard or Dedicated access mode.”
↩︎ Compute and privileges you need“must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege”
↩︎ Compute and privileges you need“SQL warehouses must be pro or serverless and must use 2023.40 or above.”
↩︎ Exam trap 1“Access credentials for the foreign catalog come from the named connection, so you don't specify them here.”
↩︎ Exam trap 2“choose query federation for ad hoc reporting or proof-of-concept work on your ETL pipelines”
↩︎ Checkpoint“SQL warehouses must be pro or serverless and must use 2023.40 or above.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/query-federationOfficial docs
“Lakehouse Federation is the Databricks query federation platform.”
↩︎ Querying data where it lives“Create a connection in Unity Catalog with your access credentials and JDBC URL. Create a foreign catalog using the connection.”
↩︎ Checkpoint - 3.
“Databricks keeps the definition of the catalog's schemas and their relations in sync with the foreign source.”
↩︎ Two objects: a connection and a foreign catalog
Also cited
“A foreign catalog is a securable object in Unity Catalog that mirrors a database in an external data system”
↩︎ Key concept