Lakehouse Federation Performance — Parallel Reads for JDBC Connectors and More

This post organizes the Databricks documentation page Lakehouse Federation performance recommendations into a quick reference, with the focus on its section Enable parallel reads for JDBC connectors. Syntax and requirements below follow the documentation.

At a glance

1. Enable parallel reads for JDBC connectors

Connectors that use the JDBC transfer protocol can read in parallel by partitioning the query. This lets multiple executors fetch data concurrently, which significantly improves performance for large tables.

Supported connectors: Databricks, Microsoft SQL Server, Microsoft Azure Synapse, MySQL, Oracle, PostgreSQL, Redshift, Salesforce Data 360, Teradata.

Compute requirements: a Pro or Serverless SQL warehouse, or compute running Databricks Runtime 16.4 LTS or above.

Parameters

lowerBound and upperBound decide only the partition stride. They do not filter rows: all rows in the table are partitioned and returned.

Example

The query below is split into 4 parallel partitions on id, each covering about 250 IDs (assuming one record per id between 1 and 1000).

SELECT * FROM mySqlCatalog.schema.table WITH (
  'numPartitions' 4,
  'partitionColumn' 'id',
  'lowerBound' 1,
  'upperBound' 1000
)

Choosing the partition column

The partition column should be:

• a numeric column,
• evenly distributed across the range, and
• indexed, for better performance.

Views: create them in the source database

Parallel reads are not supported when querying a Databricks-created view that references a federated table. Create the view in the source database instead, then query it from Databricks with the parallel-read parameters.

-- In the source database
CREATE VIEW my_source_database_view AS SELECT * FROM source_database_schema.table;

-- From Databricks
SELECT * FROM myfederated_catalog.schema.my_source_database_view WITH (
  'numPartitions' 4,
  'partitionColumn' 'id',
  'lowerBound' 1,
  'upperBound' 1000
)

2. Set the batch size with fetchSize

The JDBC fetch size is the number of rows fetched per round trip. By default most JDBC connectors fetch data atomically, which can exceed available memory. Setting fetchSize to a non-zero value makes the connector read in batches of at most that many rows.

• Supported connectors: Databricks, Microsoft SQL Server, Microsoft Azure Synapse, MySQL, Oracle, PostgreSQL, Salesforce Data 360, Teradata.
• Recommendation: use a large value such as 100,000; very small batches can prolong total execution time.
• Note: this lets worker nodes read in batches, but not in parallel.
• Compute: Databricks Runtime 16.1 or above; SQL warehouses must be Pro or Serverless on 2024.50.

SELECT * FROM mySqlCatalog.schema.table WITH ('fetchSize' 100000)

3. Set the partition size (Snowflake)

Snowflake fetches data in multiple partitions, engaging multiple executors. partition_size_in_mb sets the recommended uncompressed size of each partition (default 100 MB); use a larger value for fewer partitions. Actual sizes may vary. Compute requirements are the same as for fetchSize.

SELECT * FROM snowflakeCatalog.schema.table WITH ('partition_size_in_mb' 1000)

df = spark.read.option("partition_size_in_mb", "1000").table("snowflakeCatalog.schema.table")

4. Combine predicates with AND

Databricks Runtime tries to push predicates down to the remote engine. If a predicate cannot be pushed down, the remote query excludes it and Databricks filters the rows. When predicates are joined with AND, the pushable part is still pushed down.

-- ILIKE has no MySQL translation, so the remote query returns all rows
SELECT * FROM foreign_catalog.schema.table WHERE name ILIKE 'john'
-- sent remotely: SELECT * FROM catalog.schema.table
 
-- The date comparison is pushed down, reducing the rows fetched
SELECT * FROM foreign_catalog.schema.table WHERE name ILIKE 'john' AND date > '2025-05-01'
-- sent remotely: SELECT * FROM catalog.schema.table WHERE date > '2025-05-01'

To see the query sent to the remote database, run EXPLAIN FORMATTED. The actual query may differ because of adaptive query execution.

5. Join pushdown (Public Preview)

Join pushdown sends join operations to the remote engine instead of fetching both sides and joining locally, reducing network traffic.

• Generally available and on by default: Redshift, Snowflake, BigQuery.
• Public Preview: Oracle, PostgreSQL, MySQL, SQL Server, Teradata. These need Databricks Runtime 17.2 or above (SQL warehouses Pro or Serverless on 2025.30) and the Join Pushdown for Federated Queries preview turned on.
• Limitations (preview connectors): only inner, left outer and right outer joins; aliases in a join's children require Databricks Runtime 17.3 or above.
• Plan shape: only joins, filters, sample and scan nodes may appear below a join. A limit, offset or aggregate below a join blocks pushdown; aggregate, limit and offset on top of a join can be pushed down.

-- Supported: aggregate on top of a join
SELECT COUNT(*) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id
 
-- Not supported: join on top of an aggregate
SELECT * FROM (SELECT id, COUNT(*) AS cnt FROM table1 GROUP BY id) t1
INNER JOIN table2 t2 ON t1.id = t2.id

Verify with EXPLAIN FORMATTED; a pushed-down join shows PushedJoins in the physical plan, with the join condition in the PushedFilters above it.

Reference

• Databricks documentation: Lakehouse Federation performance recommendations