What you will be able to do
- Write one query that combines a local Delta table with a table in a foreign catalog using three-level names
- Apply the rules for foreign tables: read-only access, Spark data types, and connection ownership on dedicated compute
- Predict which operations Databricks pushes down to a PostgreSQL source
- Choose between query federation, the remote_query function, and catalog federation for a cross-system workload
1.One query, local and foreign tables
Once a foreign catalog exists, Unity Catalog treats its tables as one more set of three-level names: catalog.schema.table. You work with a foreign catalog the same way you work with any other Unity Catalog catalog. The result is a single SQL namespace that includes your Delta tables and the mirrored tables from PostgreSQL, Snowflake, or another supported source.
An ordinary JOIN works. Query federation lets you write queries in Databricks SQL syntax, and Databricks translates the compatible operations for the remote database. Databricks supports standard ANSI joins: inner, outer, semi, anti, and cross. So a cross-system join is a normal join where one side's three-level name points at a foreign catalog, for example postgresql_catalog.a_schema.table1, and the other side points at a Delta table in a regular catalog. The documentation lists this as a reason to choose query federation: combining data from multiple sources in a single query with consistent syntax.
A join like this is a batch join, so it is stateless. Every run calculates fresh results from the data as it exists at that moment, on both sides, with no copy of the remote table kept in between.
The Databricks SQL reference shows the same idea with a set operation instead of a join. One UNION ALL statement stacks rows from a foreign catalog table, two other tables, and a local table.
SELECT * FROM postgresql_catalog.a_schema.table1
UNION ALL
SELECT * FROM default.postgresql_schema.table2
UNION ALL
SELECT * FROM default.postgresql.mytable
UNION ALL
SELECT local_table;Checkpoint 1 of 6· Check yourself
An analyst joins main.sales.orders (a Delta table) to postgresql_catalog.crm.customers (a foreign table) in the SQL editor. What do they need to write?
Foreign catalog tables take part in normal Databricks SQL queries alongside local tables. Federation exists so that you can combine sources in one query with consistent syntax.
“You want to combine data from multiple sources in a single query using consistent syntax.”Source: docs.databricks.com
Checkpoint 2 of 6· Exam question
An analyst at a retail company needs to set up federated access to an on-premises PostgreSQL database that stores daily inventory counts, so analysts can join it against a Delta table of sales transactions in Databricks SQL. What is the first object they must create in Unity Catalog before they can query the PostgreSQL tables?
Correct answer: A — A connection object that stores the PostgreSQL host, port, and authentication secrets, created with `CREATE CONNECTION ... TYPE postgresql`.
- A. Creating a connection first is correct because Lakehouse Federation requires a securable connection object holding the host, port, and credentials before any foreign catalog can be built on top of it.
- B. This is incorrect because `CREATE FOREIGN CATALOG` always references an existing connection by name via `USING CONNECTION`; it cannot embed host and credential details inline instead of a connection.
- C. An external location and storage credential secure access to cloud object storage such as S3 or ADLS; they have no role in connecting to a running PostgreSQL database engine.
- D. A Delta Sharing recipient profile governs outbound sharing of Databricks data to external parties, which is the opposite direction of pulling data from PostgreSQL into a federated query.
2.Rules that apply to the foreign side
The foreign side of a join behaves like a Unity Catalog table, with three differences that matter when you write cross-system queries.
It is read-only. Query federation doesn't support writes. You can read foreign tables in a SELECT or join, but you can't INSERT, UPDATE, or MERGE into them through the foreign catalog. If the result of a join needs to be kept, the target has to be a Databricks table.
Use Spark data types. Your queries have to use Apache Spark data types, not the remote database's native types. A join key or filter literal is compared using the Spark type that the remote column maps to, and each connector's page documents that mapping.
Compute access mode changes the privilege check. On standard access mode and serverless compute, normal catalog privileges apply. On dedicated access mode compute, you must own the underlying connection. Having CREATE FOREIGN CATALOG on the connection is not enough.
Governance still applies across the join. You grant read access to foreign tables with standard Unity Catalog privileges, and Databricks tracks lineage for foreign tables the same way it does for standard tables. A report built on a Delta-plus-PostgreSQL join therefore shows lineage back to both sources.
Checkpoint 3 of 6· Check yourself
A user runs a join between a Delta table and a foreign table on a dedicated access mode cluster. They have CREATE FOREIGN CATALOG on the connection and SELECT on the foreign table, but the query is denied. What is missing?
On dedicated access mode, querying a foreign catalog requires owning the connection. The CREATE FOREIGN CATALOG privilege on the connection isn't sufficient.
“On dedicated access mode compute, you must own the underlying connection to query data in the foreign catalog.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
An analyst suspects a federated query joining an Oracle foreign catalog table with a Delta table is not pushing filter predicates down to Oracle, causing it to scan far more rows than necessary. Which action lets them confirm whether pushdown actually occurred for this specific query?
Correct answer: A — Run `EXPLAIN FORMATTED` on the query or inspect its Query Profile to see the actual SQL statement Databricks generated and sent to the remote Oracle system.
- A. `EXPLAIN FORMATTED` and Query Profile are correct because they expose the generated remote SQL statement that Lakehouse Federation actually sends to Oracle, letting the analyst confirm whether the filter was translated into that pushed-down statement.
- B. The authentication method used by a connection has no bearing on whether Databricks decides to push a given predicate down; pushdown is a query-planning decision, not a credential-type feature.
- C. Pushdown behavior for Lakehouse Federation is not restricted to serverless warehouses; switching warehouse type changes compute for local processing but does not by itself change whether predicates reach the remote system.
- D. Photon accelerates local vectorized execution inside the Databricks compute layer; it does not decide which parts of a federated query get translated and sent to the remote database engine.
3.What gets pushed down to the source
A federated query is split: some operations go to the remote database over JDBC, and the rest runs in Databricks. Each connector page lists which operations it pushes down. Here is the PostgreSQL list.
| Operation | Pushdown support |
|---|---|
| Filters | All compute |
| Projections | All compute |
| Aggregates | All compute |
| Limit | All compute |
| Sorting, when used with limit | All compute |
| Joins | Databricks Runtime 17.2 and above and SQL warehouse compute; Public Preview toggle required |
| Windows functions | Not supported |
In a Delta-to-PostgreSQL join, WHERE filters, column projections, and aggregates on the foreign table can be sent to PostgreSQL, so less data crosses the network. Window functions are not pushed down for this source. The sources don't describe exactly how Databricks plans a join where one side is local and the other is remote. What they do say is that the query runs on both Databricks and the remote compute.
Checkpoint 5 of 6· Check yourself
Which operation in a query against a PostgreSQL foreign table is NOT pushed down to PostgreSQL?
The PostgreSQL pushdown list shows filters, aggregates, and limit for all compute, and window functions as not supported.
“Windows functions | Not supported”Source: docs.databricks.com
4.Query federation versus its neighbours
Two other features are easy to confuse with query federation. The remote_query table-valued function reuses the same Unity Catalog connection, but you write the query in the remote system's own dialect, and it doesn't need a foreign catalog. Catalog federation also creates a foreign catalog, but it targets external catalog services such as Hive metastores, AWS Glue, and Snowflake, and it reads the tables directly in object storage on Databricks compute only. Query federation and catalog federation are both read-only.
| Approach | Query syntax and execution | Access control |
|---|---|---|
| Query federation | Databricks SQL; compatible operations pushed down to the remote database over JDBC | Table-level privileges on foreign catalog objects |
| remote_query function | Native SQL dialect of the remote database | USE CONNECTION on the connection, or SELECT on a view wrapping the function |
| Catalog federation | Runs directly against object storage on Databricks compute only | Unity Catalog foreign catalog with table-level access controls |
Checkpoint 6 of 6· Match them up
Match each need to the approach the documentation points to
Tap a term, then the definition that fits it.
remote_query keeps the remote dialect. Query federation keeps Databricks SQL with foreign-catalog grants. Catalog federation reads object storage for incremental migration or hybrid setups.
“Write queries using the native SQL dialect of the remote database”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Once a foreign catalog is set up, you can MERGE the results of a cross-system join back into the external table.Why is that wrong?
Query federation is read-only. Foreign tables can be read and joined, but not written through the foreign catalog.
Covered in Rules that apply to the foreign side
2.Having CREATE FOREIGN CATALOG on the connection is enough to query the foreign catalog from any compute.Why is that wrong?
On dedicated access mode compute, you must own the connection. Normal catalog privileges are enough only on standard access mode and serverless compute.
Covered in Rules that apply to the foreign side
3.Every part of a federated query, joins included, is always pushed down to the remote database.Why is that wrong?
Pushdown depends on the connector and the operation. For PostgreSQL, join pushdown is a Public Preview feature with compute requirements, and window functions are not pushed down.
Covered in What gets pushed down to the source
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“You work with a foreign catalog the same way you work with any catalog managed by Unity Catalog”
↩︎ One query, local and foreign tables“You can run read-only queries on tables in a foreign catalog. You must use Apache Spark data types in your queries.”
↩︎ Rules that apply to the foreign side“Databricks tracks data lineage for tables in foreign catalogs the same way it tracks lineage for standard Unity Catalog tables.”
↩︎ Rules that apply to the foreign side“You can run read-only queries on tables in a foreign catalog.”
↩︎ Exam trap 1“The CREATE FOREIGN CATALOG privilege on the connection isn't sufficient.”
↩︎ Exam trap 2“On dedicated access mode compute, you must own the underlying connection to query data in the foreign catalog.”
↩︎ Checkpoint - 2.
“You can now issue queries across the various local and foreign relations.”
↩︎ One query, local and foreign tables - 3.https://docs.databricks.com/aws/en/transform/joinOfficial docs
“Databricks supports standard SQL join syntax, including inner, outer, semi, anti, and cross joins.”
↩︎ One query, local and foreign tables“All batch joins are stateless joins. Results process immediately and reflect data at the time the query runs.”
↩︎ One query, local and foreign tables - 4.
“Write queries using Databricks SQL syntax. Databricks translates and pushes down compatible operations to the remote database.”
↩︎ One query, local and foreign tables“Users need USE CONNECTION privilege on the connection.”
↩︎ Query federation versus its neighbours“You want to combine data from multiple sources in a single query using consistent syntax.”
↩︎ Checkpoint“Write queries using the native SQL dialect of the remote database”
↩︎ Checkpoint - 5.https://docs.databricks.com/aws/en/query-federationOfficial docs
“Not supported (read-only).”
↩︎ Rules that apply to the foreign side“The query is only run on Databricks compute, meaning that catalog federation is more cost-effective and performance-optimized than query federation.”
↩︎ Query federation versus its neighbours - 6.
“This pushdown is in Public Preview; enable the Join Pushdown for Federated Queries toggle on the Previews page.”
↩︎ What gets pushed down to the source“This pushdown is in Public Preview; enable the Join Pushdown for Federated Queries toggle on the Previews page.”
↩︎ Exam trap 3“Windows functions | Not supported”
↩︎ Checkpoint - 7.
“The query is executed both in Databricks and using remote compute.”
↩︎ What gets pushed down to the source