Lakeflow Connect, JDBC and REST Sources

What it is

Enterprise data lives in databases, SaaS applications and APIs, not only in files. Lakeflow Connect managed connectors ingest from supported sources with no ingestion code. For anything else, a notebook uses JDBC or a REST client and a Lakeflow Job runs it on a schedule.

Much of this data is semi structured, such as JSON with nested objects and arrays. The exam expects you to land it in Delta tables and read its nested fields.

How it works

Configuring a managed connector

Setting up a managed connector creates a few objects, all governed by Unity Catalog.

You pick the source objects (tables or SaaS objects), the destination catalog and schema, and a schedule. The schedule runs the ingestion pipeline, which can also be a task in a Lakeflow Job. The database gateway runs continuously, apart from that schedule.

The first run loads all selected data; later runs load only changes where the source supports it. This is what makes the connector reliable: Databricks maintains the API logic, retries and schema evolution, not your team.

JDBC from a notebook

When no managed connector fits, read the database with the Spark JDBC source. Keep credentials in a secret scope, never in the notebook.

df = (spark.read.format("jdbc")
  .option("url", "jdbc:postgresql://dbhost:5432/sales")
  .option("dbtable", "public.orders")
  .option("user", dbutils.secrets.get("sales_db", "user"))
  .option("password", dbutils.secrets.get("sales_db", "password"))
  .load())
df.write.mode("append").saveAsTable("main.bronze.orders")

For large tables, partitionColumn, lowerBound, upperBound and numPartitions split the read across parallel connections. ODBC follows the same idea from tools outside Spark.

REST APIs from a notebook

A REST client such as Python requests calls the API and either lands raw responses as files or writes rows directly to a table.

import requests
resp = requests.get(api_url, timeout=30)
resp.raise_for_status()
dbutils.fs.put(f"/Volumes/main/raw/landing/api/{run_id}.json", resp.text)

Landing raw JSON in a volume first lets Auto Loader load it incrementally and keeps the original response for replay. Writing straight to a table is simpler for small responses.

Orchestrating with Lakeflow Jobs

A custom notebook is not incremental on its own. Schedule it as a notebook task in a Lakeflow Job, add retries, and track a watermark, such as the last modified timestamp, so each run reads only new rows.

Semi structured and unstructured data

JSON objects become struct columns and JSON arrays become array columns. Read nested fields with dot notation and turn arrays into rows with explode.

SELECT o.order_id, o.customer.address.city, item.sku
FROM main.bronze.orders o
LATERAL VIEW explode(o.items) AS item;

When JSON is stored as a string column, the colon syntax reads into it, as in raw:customer.id. Managed file connectors, such as SharePoint and Google Drive, and Auto Loader with the binaryFile format bring in unstructured files such as PDFs.

How the exam asks about this

• "Ingest SQL Server tables with change data capture into Unity Catalog with minimal code." A managed connector with an ingestion gateway.
• "An internal API has no connector; load it daily." A notebook with a REST client scheduled by a Lakeflow Job.
• "Each order row holds an array of items; produce one row per item." Use explode on the array column.