What you will be able to do
- Decide when to use a JDBC Unity Catalog connection and when Lakehouse Federation is the better fit
- Set up a JDBC connection from a driver JAR stored in a Unity Catalog volume, with credentials kept out of the URL
- Read from an external database in a notebook through the connection, control read parallelism, and save the result as a Unity Catalog table
Key concept
JDBC Unity Catalog connection — A Unity Catalog object that holds the JDBC driver, the database URL and the credentials for an external database. Notebooks use it by name, so the credentials stay hidden and access is controlled by Unity Catalog.
1.When a notebook should use JDBC
Sometimes the source is an operational database rather than files in cloud storage. A notebook can then pull its rows over JDBC and land them in a governed table. Databricks recommends doing this through a JDBC connection, which is a Unity Catalog object. You create it once and can reuse it on serverless, standard and dedicated compute and in Databricks SQL. Users who query through the connection never see the credentials stored on it.
The two options govern at different levels. Federation applies table-level access control through a foreign catalog. A JDBC connection is governed only at the connection level. JDBC is still the right choice in five situations: federation doesn't support the database; you need a specific driver; you need to write back to the source; you want Spark's parallelism options; or you want to push down a source SQL statement with the query option. The write case matters most for exam questions, because federation does not support writes. A third option is a PySpark custom data source. Use it when you need to build your own reader or writer in Python.
| Need | JDBC connection | PySpark data source |
|---|---|---|
| Driver | Built-in Spark JDBC support with an existing JDBC driver | You write the source or sink in Python |
| Governance | Unity Catalog governance at the connection level | Not stated |
| Where it runs | Any Unity Catalog compute: serverless, standard, dedicated, SQL API | Notebooks or PySpark workloads only |
| Partitioning | Spark Data Source API options | Custom partitioning logic |
Checkpoint 1 of 5· Match them up
Match each requirement to the option the documentation points you to
Tap a term, then the definition that fits it.
Federation can't write and JDBC is governed only per connection. Custom Python partitioning is the reason to build a PySpark data source.
“You need to write to the data source using Spark (query federation doesn't support writes).”Source: docs.databricks.com
Sources1
2.Setting up the connection
The connection installs the driver in an isolated sandbox. It takes the driver JAR from a Unity Catalog volume, so the setup begins with storage. Create a volume, or use one you can write to, and upload the JAR. Then give read access on the volume to everyone who will query through the connection. On serverless and standard clusters this needs Databricks Runtime 17.3 LTS or above in standard or dedicated access mode, or serverless compute. The person who creates the connection needs the CREATE CONNECTION privilege on the metastore.
GRANT READ VOLUME ON VOLUME my_catalog.my_schema.my_volume_JARs TO `account users`Checkpoint 2 of 5· Put it in order
Put the steps for setting up a JDBC connection in order
- 1.Grant READ VOLUME to the users who will query the connection
- 2.Create the connection with the JDBC URL, the JAR dependency and credentials
- 3.Create a Unity Catalog volume for the driver JARs
- 4.Upload the JDBC driver JAR to the volume
The connection loads its driver from the volume, so the JAR and read access must be in place before the connection is created and used.
“The JDBC connection reads and installs the JDBC driver JAR from a Unity Catalog volume.”Source: docs.databricks.com
You can create the connection in Catalog Explorer by choosing the JDBC connection type. You can also run CREATE CONNECTION from a notebook or the SQL editor, or use the REST API or CLI. Only the URL and the credentials are required. Keep credentials out of the URL, because logs and error messages can expose it. Put them in the credential fields instead: a static username and password, or OAuth machine-to-machine, which is in Beta. With OAuth M2M, Unity Catalog exchanges the client credentials for a token. Only that short-lived token reaches the driver. The externalOptionsAllowList setting controls which Spark options a querying user may set at query time. By default these are dbtable,query,partitionColumn,lowerBound,upperBound,numPartitions. Users can never override url or host.
Checkpoint 3 of 5· Check yourself
Users of a SQL Server JDBC connection get an error when they pass a database name at query time. The URL doesn't fix the database. What is the fix?
database is not one of the default allowlisted options, so the connection has to list it. Users can never set url, and an empty allowlist would restrict them even further.
“database is not in the default allowlist.”Source: docs.databricks.com
Sources1
3.Reading in the notebook and landing a Unity Catalog table
In the notebook, you name the connection in the Spark reader options. You don't write the URL or credentials there. The query option sends a SQL statement to run in the source database.
Checkpoint 4 of 5· Fill the gap
Which option tells Spark to use the Unity Catalog JDBC connection?
df = (spark.read
.format("jdbc")
.option(" ? ", "<connection-name>")
.option("query", "SELECT * FROM external_table")
.load())The connection is referenced by name through databricks.connection. The URL lives on the connection and users can't override it.
Source: docs.databricks.comjdbc(url, table, column=None, lowerBound=None, upperBound=None,
numPartitions=None, predicates=None, properties=None)There are two ways to read in parallel. With column, the reader splits a numeric range on that column; you must also give lowerBound, upperBound and numPartitions. With predicates, you pass a list of WHERE clauses and each clause becomes one partition. If you set both, column takes precedence. Once the data is in a DataFrame, write it to a Unity Catalog table. You need CREATE TABLE privileges on the target catalog and schema.
df.write.mode("overwrite").saveAsTable(f"{path_table}" + "." + f"{table_name}")Checkpoint 5 of 5· Exam question
A data engineer runs a nightly notebook task in a Lakeflow Job that reads a 200 million-row `orders` table from an on-premises PostgreSQL database using `spark.read.format("jdbc")`. Without any partitioning options set, the job opens a single connection and the read consistently takes over three hours before timing out. The `order_id` column is a monotonically increasing integer primary key with values evenly distributed across the full range. Which change is most likely to reduce the read time while keeping the load on the source database controlled?
Correct answer: A — Set `partitionColumn` to `order_id`, provide `lowerBound` and `upperBound` matching the observed key range, and set `numPartitions` to a modest value such as 8 so Spark splits the scan into parallel range queries.
- A. Setting `partitionColumn`, `lowerBound`, `upperBound`, and a modest `numPartitions` lets Spark split the scan into several range-bounded queries that run in parallel, which is the documented way to speed up a large JDBC read without opening an unbounded number of connections.
- B. A very large `fetchSize` only changes how many rows are buffered per round trip on a single connection; it does not add parallelism, so it will not meaningfully shrink a three-hour single-connection scan of 200 million rows.
- C. A bare `SELECT *` subquery does not aggregate or filter anything, so it still pulls the full table across a single connection; it does not reduce data volume or add parallelism.
- D. ODBC is a different client protocol, not an automatic parallelism mechanism, and it still requires explicit partitioning configuration to split a scan across multiple connections.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Lakehouse Federation can write data back to an external database, so you never need JDBC for supported databases.Why is that wrong?
Federation is read-only. If Spark has to write to the source, you need a JDBC Unity Catalog connection.
Covered in When a notebook should use JDBC
2.Putting user and password in the JDBC URL is fine, because the connection hides the URL anyway.Why is that wrong?
Credentials embedded in the URL can leak through logs or error messages. Use the connection's dedicated credential options.
Covered in Setting up the connection
3.When reading over JDBC, more partitions always means a faster and safer load.Why is that wrong?
Too many parallel partitions on a large cluster can crash the source database. Size numPartitions for what the source can handle.
Covered in Reading in the notebook and landing a Unity Catalog table
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“JDBC Unity Catalog connection provides governance only at the connection level.”
↩︎ When a notebook should use JDBC“Connection credentials are hidden from the querying user.”
↩︎ When a notebook should use JDBC“If you want to implement custom partitioning logic.”
↩︎ When a notebook should use JDBC“To create a connection, you must have the CREATE CONNECTION privilege on the metastore attached to the workspace.”
↩︎ Setting up the connection“passes only the resulting short-lived access token to the JDBC driver using the driver's token parameter.”
↩︎ Setting up the connection“Users can never specify url or host.”
↩︎ Setting up the connection“The JDBC connection is a securable object in Unity Catalog that specifies the JDBC driver, the URL path, and credentials”
↩︎ Key concept“You need to write to the data source using Spark (query federation doesn't support writes).”
↩︎ Exam trap 1“Don't embed credentials in the URL, because logs or errors can expose them.”
↩︎ Exam trap 2“If your database is supported, Databricks recommends using query federation instead of a JDBC connection.”
↩︎ Prediction“You need to write to the data source using Spark (query federation doesn't support writes).”
↩︎ Checkpoint“The JDBC connection reads and installs the JDBC driver JAR from a Unity Catalog volume.”
↩︎ Checkpoint“database is not in the default allowlist.”
↩︎ Checkpoint - 2.
“To use a JDBC Unity Catalog connection, specify databricks.connection in your Spark options:”
↩︎ Reading in the notebook and landing a Unity Catalog table - 3.
“If both column and predicates are specified, column takes precedence.”
↩︎ Reading in the notebook and landing a Unity Catalog table“Avoid creating too many partitions in parallel on a large cluster, as this can crash external database systems.”
↩︎ Exam trap 3“Avoid creating too many partitions in parallel on a large cluster, as this can crash external database systems.”
↩︎ Prediction - 4.
“To save your DataFrame in Unity Catalog, you must have CREATE table privileges on the catalog and schema.”
↩︎ Reading in the notebook and landing a Unity Catalog table - 5.https://docs.databricks.com/aws/en/integrations/odbcOfficial docs
“enables you to connect participating apps, tools, clients, SDKs, and APIs to Databricks through Open Database Connectivity (ODBC)”
↩︎ Reading in the notebook and landing a Unity Catalog table