Lakeflow Connect and JDBC/REST Ingestion in Databricks
Introduction
Lakeflow Connect ingestion, JDBC extracts and REST clients solve different source-integration problems in Databricks. This lesson completes the Data Ingestion and Loading section of the official exam outline (about 21% of the scored questions, roughly nine or ten of 45). Lessons 3 and 4 covered file-based ingestion with COPY INTO and Auto Loader. Here you move to enterprise sources: SaaS applications, operational databases, REST APIs and nested JSON, and you learn to choose the right ingestion method for a scenario based on volume, frequency, data type and governance.
After this lesson you will be able to explain what Lakeflow Connect managed connectors do, read from a database with JDBC, call a REST API from a notebook and schedule it, flatten semi-structured JSON, and justify an ingestion method in a scenario question.
Learning objectives
- Describe the families of connectors in Lakeflow Connect: managed connectors and standard (file and streaming) connectors.
- Explain how a managed connector ingests incrementally and what you configure (connection, source objects, destination catalog and schema).
- Read from a relational database with JDBC and write a Delta table, and know when to schedule it with Lakeflow Jobs.
- Call a REST API from a notebook and land the responses safely.
- Ingest semi-structured and nested JSON with schema hints,
from_json, the:path syntax andexplode. - Choose between Auto Loader,
COPY INTO, Lakeflow Connect, partner connectors and custom code using volume, frequency, data type and governance.
1. Lakeflow Connect: the connector families
Lakeflow Connect provides managed ingestion. The exam contrasts managed connectors with code-based standard connectors. The table below is a study grouping, not an exhaustive current product catalog: documentation also organizes sources into file, streaming, query-based, community and other connector categories.
| Family | Sources | You manage | Typical use |
|---|---|---|---|
| Managed SaaS connectors | Enterprise applications such as Salesforce, Workday, ServiceNow, Google Analytics | Configuration only (connection, objects, destination) | Business application data into Delta tables |
| Managed database connectors | Operational databases such as SQL Server, MySQL, PostgreSQL, Oracle (change data capture or snapshot) | Configuration and some source-side setup (CDC enablement, permissions) | Replicating operational tables with low impact on the source |
| Standard connectors | Cloud object storage and message buses | Pipeline code (Auto Loader, Structured Streaming, Kafka) | Files and event streams |
The exact list of supported sources grows regularly, and the official documentation keeps the current list. For the exam, remember the principles, not a memorised catalogue:
- Managed connectors are low-code or no-code. Databricks operates the extraction, handles incremental reads or change data capture, manages schema changes where supported, and writes to Delta tables governed by Unity Catalog.
- They are configured through the UI, the API, the Databricks CLI or declarative bundles, and run as a managed pipeline (the ingestion pipeline) plus, when needed, a gateway for database sources.
- Destinations are governed Unity Catalog objects; verify grants and the supported monitoring and lineage coverage for that connector.
- They are the right answer when the question says "ingest from Salesforce / SQL Server without writing code, incrementally, with Databricks managing the connector".
What you configure for a managed connector
- Connection: a Unity Catalog connection object that stores the credentials for the source (so credentials are never in notebooks).
- Source objects: the tables, objects or reports to ingest (for example the
AccountandOpportunityobjects in Salesforce). - Destination: the target catalog and schema for the streaming tables created by the pipeline.
- Schedule: how often the pipeline runs, usually as a scheduled Lakeflow Job or a pipeline update trigger.
Initial loads establish the source state. Later updates use incremental reads where the connector and source object support them; some objects still require full refreshes. Database CDC designs can keep a gateway running continuously even when destination updates are scheduled. Verify source permissions, networking, supported versions and log-retention requirements.
Example. A sales operations team wants the Salesforce Opportunity object in Databricks every hour. They have no engineer to build and maintain an API client. A managed SaaS connector solves it with a connection, the object name and a schedule. If instead they wrote a custom Python extractor, they would own pagination, rate limits, schema drift and error handling.
2. Reading from databases with JDBC and ODBC
When no managed connector exists, or when you need a one-off extract, you can read a relational database from a notebook through JDBC (Java Database Connectivity) with Spark.
df = (spark.read
.format("jdbc")
.option("url", "jdbc:postgresql://pg.acme.internal:5432/sales")
.option("dbtable", "public.orders")
.option("user", dbutils.secrets.get("acme", "pg_user"))
.option("password", dbutils.secrets.get("acme", "pg_password"))
.option("driver", "org.postgresql.Driver")
.option("fetchsize", "10000")
.load())
df.write.mode("overwrite").saveAsTable("prod.bronze.pg_orders_stage")This is a full-snapshot staging example for one serialized demonstration run, not a retry-safe append pipeline. It assumes the JDBC driver, network route, secret scope and target schema are configured. Incremental ingestion needs the additional design below.
Points to know:
- Credentials come from secrets (
dbutils.secrets.get), never from literals in the notebook. - By default a JDBC read uses one connection and one partition, so a big table loads slowly. To parallelise, set
partitionColumn,lowerBound,upperBoundandnumPartitionson a numeric, date or timestamp column. - Use
queryfor a filtered unpartitioned read. For partitioned reads, put the filter in an aliaseddbtablesubquery: Spark does not allowquerytogether withpartitionColumn. Partition bounds set stride, not a row filter. - A JDBC pull is a full read each time unless you build the incremental logic yourself, for example with a watermark column such as
updated_atand aMERGEinto the target. - Lakehouse Federation (foreign catalogs) lets you query an external database in place, which is different from ingestion: data is not copied into Delta.
ODBC is another connectivity interface. BI tools can query Databricks over ODBC/JDBC, and notebook clients can also extract from source databases over ODBC when compatible drivers and networking are available. Spark's native JDBC reader is the distributed API illustrated here; distinguish source extraction from querying a Databricks SQL warehouse.
Worked example: parallel incremental extract
Scenario. Every night a job reads the whole orders table (180 million rows) from a PostgreSQL database with a single JDBC connection. It takes five hours and loads the production database. The table has a numeric order_id (1 to 180,000,000) and an updated_at timestamp. How would you improve it?
Reasoning.
- Profile the source first. If one JDBC partition is the bottleneck and the database has capacity, partition by
order_idwith a measured connection limit, such as 16. More connections can also overload the source. - Push an explicit bounded change window into an aliased
dbtablesubquery, for example(SELECT * FROM public.orders WHERE updated_at >= TIMESTAMP '2026-11-03 00:00:00' AND updated_at < TIMESTAMP '2026-11-04 00:00:00') AS changed_orders, then apply the partition options to that read. Do not combinequerywithpartitionColumn. - Persist the bounded extract to staging and reconcile one source row per business key before
MERGE. Advance the durable watermark only after the target succeeds; use a deliberate overlap/tie-handling strategy for equal timestamps and late commits. A timestamp filter does not capture hard deletes or guarantee a consistent cross-partition snapshot. - If supported source versions, connectivity and permissions allow it, consider managed CDC to reduce custom change-tracking code.
Why other choices are weaker. More workers do not by themselves increase a single JDBC read partition. Auto Loader reads files, not databases. Copying the database to files by hand adds a manual step and still needs incremental logic.
For file-based feeds instead, compare Auto Loader discovery and checkpoints.
3. REST APIs
Many SaaS sources have an HTTP API. If no managed connector exists, write a notebook (or use a partner or marketplace connector) and schedule it with Lakeflow Jobs.
The following is a bounded full-snapshot demonstration against an illustrative API. Assume page/page_size, an items list and an empty final page are the provider's documented contract. Configure the endpoint, networking, secret and isolated dev.scratch schema first. It is not a production incremental extractor.
import json
import requests
from requests.adapters import HTTPAdapter
from urllib3.util import Retry
retry = Retry(total=4, backoff_factor=1,
status_forcelist=[429, 500, 502, 503, 504],
allowed_methods=["GET"], respect_retry_after_header=True)
rows = []
with requests.Session() as session:
session.mount("https://", HTTPAdapter(max_retries=retry))
token = dbutils.secrets.get("acme", "crm_token")
for page in range(1, 22):
response = session.get(
"https://api.acme-crm.example/v2/contacts",
headers={"Authorization": f"Bearer {token}"},
params={"page": page, "page_size": 500}, timeout=(5, 30))
response.raise_for_status()
items = response.json()["items"]
if not items:
break
if page > 20 or len(items) > 500:
raise RuntimeError("Snapshot exceeds this small-example limit")
rows.extend((json.dumps(item),) for item in items)
snapshot = spark.createDataFrame(rows, "raw STRING")
snapshot.write.mode("overwrite").saveAsTable("dev.scratch.crm_contacts_snapshot")The extra request checks for an empty page after at most 10,000 records. A page limit fails explicitly rather than silently truncating the snapshot. Do not collect a production feed on the driver: persist bounded pages to durable staging with a stable extraction-window identifier, verify pagination completion, then apply an idempotent target update. Offset pagination on a changing source can miss or repeat records; use the provider's stable cursor or snapshot contract where available.
Good practice for REST ingestion:
- Retain the raw payload in bronze or governed file storage for replay after parsing errors; set appropriate retention and access controls.
- Handle pagination, rate limits (HTTP 429) and retries with back-off; set timeouts.
- Keep the token in a secret scope.
- Make the run idempotent: record the extraction window, and use
MERGEon a business key so a retry does not duplicate rows. - Schedule with Lakeflow Jobs, with a retry policy on the task and an alert on failure.
- Alternative: land immutable, uniquely identified pages in a volume and ingest them with Auto Loader. Preserve the checkpoint and still reconcile business duplicates across source pages or extraction windows.
4. Partner connectors and other methods
Databricks Partner Connect helps set up supported partner integrations. Ingestion behavior, authentication, target formats and Unity Catalog support depend on the chosen partner; the integration alone does not guarantee every source writes governed Delta tables. Partner Connect is optimized for supported new partner setups; an existing partner account may be better connected manually using its connection guide. Other ways to ingest include:
- Kafka and other message buses with Structured Streaming (
readStream.format("kafka")), for event streams. - Cloud-native services (a database export to object storage) followed by Auto Loader.
- Upload UI for small manual files.
5. Ingesting semi-structured and nested JSON
Event and API data are often nested JSON: structs inside structs, arrays of objects. Delta tables can store these as STRUCT and ARRAY columns. The transformation toolkit you need:
| Need | Tool | Example |
|---|---|---|
| Read nested fields from a JSON string column | : path syntax |
raw:customer.address.city |
| Convert a JSON string to a struct | from_json(col, schema) |
from_json(raw, 'id BIGINT, items ARRAY<STRUCT<sku STRING, qty INT>>') |
| Read a field of a struct | dot notation | customer.address.city |
| Turn an array into rows | explode |
explode(items) |
| Keep rows with empty arrays | explode_outer |
|
| Infer a schema from a sample | schema_of_json |
-- raw is a STRING column holding the JSON payload
SELECT
raw:order_id::BIGINT AS order_id,
raw:customer.address.city::STRING AS city,
from_json(raw:items, 'ARRAY<STRUCT<sku: STRING, qty: INT, price: DECIMAL(10,2)>>') AS items
FROM prod.bronze.orders_raw;-- One row per item
SELECT order_id, item.sku, item.qty, item.price
FROM (
SELECT raw:order_id::BIGINT AS order_id,
explode(from_json(raw:items, 'ARRAY<STRUCT<sku: STRING, qty: INT, price: DECIMAL(10,2)>>')) AS item
FROM prod.bronze.orders_raw
);Notes:
- Auto Loader can infer nested structs and arrays while leaving primitive fields as strings by default.
cloudFiles.inferColumnTypesrequests typed inference; hints guide specific fields. On supported runtimes,singleVariantColumncan retain a JSON record as VARIANT. Reading raw text is a different choice; neither option should be confused with ordinary typed JSON inference. explodedrops rows whose array is null or empty;explode_outerkeeps them with a null value. That difference is a classic trap when you count orders after flattening.- Keep the nested raw data in bronze and flatten in silver, so you can reprocess.
Worked example: flattening orders and counting correctly
Scenario. A bronze table holds 1,000 orders; 40 orders have an empty items array (cancelled before picking). An analyst flattens with explode(items) and reports COUNT(DISTINCT order_id) = 960 instead of 1,000; the remaining arrays are nonempty and order IDs are unique. What happened, and what is the fix?
Reasoning.
explodeproduces one row per array element and removes rows whose array is empty or null, so the 40 orders vanish from the result.- If the report must count all orders, use
explode_outer(items), which keeps those rows with null item columns. - Count orders with
COUNT(DISTINCT order_id)rather thanCOUNT(*), because an order with three items produces three rows.
Why other choices are weaker. Adding a filter hides the issue. Switching to from_json alone does not flatten the array. Rewriting the bronze data would lose the raw record.
6. Choosing an ingestion method
The exam describes a source and constraints and asks for the best method. Use these four lenses.
- Volume: how much data and how many files or rows?
- Frequency and latency: once, hourly, continuous?
- Data type and source: files, database, SaaS app, message bus, API?
- Governance and effort: who owns credentials, schema changes, auditing, and how much code can the team maintain?
| Scenario | Best method | Why |
|---|---|---|
| Files arrive all day in cloud storage, millions in total, schema evolves | Auto Loader | Scalable discovery, schema evolution, rescued data |
| About 2,000 files per day, SQL team, nightly | COPY INTO |
Simple, idempotent, no checkpoint |
| Salesforce or Workday data, no code, hourly | Lakeflow Connect managed SaaS connector | Incremental, governed, no extraction code |
| SQL Server tables replicated with low source impact | Managed database connector (CDC) | Change data capture, managed gateway |
| Database not covered by a managed connector, weekly extract | JDBC read in a notebook with Lakeflow Jobs | Flexible, you own the logic |
| Proprietary REST API | Notebook (requests) or partner connector, scheduled | No native connector |
| Event stream from Kafka | Structured Streaming Kafka source | Low latency |
| One-time spreadsheet from a colleague | Upload UI | Manual and small |
| Supported new partner integration | Partner Connect | Guided setup; verify partner requirements |
Governance angle. Prefer methods that keep credentials in Unity Catalog connections or secret scopes, write to governed catalogs and schemas, and leave an audit trail (lineage). A homegrown script with a password in the notebook fails the governance test even if it works.
Example. A hospital group must load patient appointment data from an on-premises SQL Server daily, with minimal load on the source and full auditability. The team has two data engineers who cannot maintain extraction code. The managed database connector with CDC fits: it is incremental, low impact, governed by Unity Catalog and needs only configuration. A full JDBC read each night would load the source and need custom incremental logic.
Common pitfalls and exam tips
- Pitfall: choosing Auto Loader for a database or SaaS source. Auto Loader reads files; use a connector or JDBC.
- Pitfall: credentials in notebooks. Use secrets or Unity Catalog connections.
- Pitfall: unmeasured JDBC parallelism. One partition can bottleneck a large extract, but too many connections can overload the source.
- Pitfall:
explodelosing empty arrays. Useexplode_outerwhen empty records matter. - Pitfall: discarding the raw payload. Bronze may parse structure, but preserve enough source detail to replay and investigate. Apply business cleaning in silver.
- Pitfall: assuming JDBC is incremental. It is a full read unless you filter on a watermark.
- Exam tip. "Without writing code", "incremental" and a named enterprise application point to a managed connector.
- Exam tip. "Millions of files" or "schema evolution" points to Auto Loader; "thousands of files, simple SQL" points to
COPY INTO. - Exam tip. Scheduled notebooks that call APIs or JDBC run as Lakeflow Jobs tasks with retries.
Mini case study
Case. Helios Energy has four ingestion needs: (1) smart-meter readings arrive as Parquet files in S3, about 5 million per month; (2) customer records live in Salesforce and must refresh hourly without custom code; (3) a legacy billing database that no managed connector supports must be copied nightly (60 million rows, modified_at column, numeric invoice_id); (4) a weather partner exposes a REST API with daily forecasts as nested JSON containing an array of hourly readings.
Tasks. Choose a method for each need and explain the key design points.
Solution.
- Meter files: Auto Loader, because of the file count;
availableNowhourly or continuous depending on latency, notifications if listing is slow. - Salesforce: Lakeflow Connect managed SaaS connector with a Unity Catalog connection, hourly schedule, destination in the bronze schema.
- Billing database: JDBC read in a Lakeflow Jobs notebook task, parallelised on
invoice_idwithnumPartitions, filtered bymodified_atsince the last watermark, thenMERGEinto bronze. Credentials in a secret scope. - Weather API: a daily notebook task with retries and back-off writes raw JSON into a volume (or a raw string column). Silver flattens with
from_jsonandexplode, usingexplode_outerif days with no readings must be kept.
Continue with PySpark and SQL cleaning and joins for silver transformations, and review Unity Catalog table privileges before granting ingestion identities access.
Key terms
- Lakeflow Connect: Databricks ingestion connector family.
- Managed connector: connector operated by Databricks, configured rather than coded.
- Change data capture (CDC): capturing only inserted, updated and deleted rows from a source.
- JDBC: standard database connectivity used by Spark to read and write relational tables.
- Secret scope: protected store for credentials, read with
dbutils.secrets.get. explode/explode_outer: turn an array into rows, dropping or keeping empty arrays.- Partner Connect: UI to set up third-party ingestion integrations.
Summary checklist
- I can name the managed SaaS, managed database and standard connector families.
- I know what to configure for a managed connector: connection, objects, destination, schedule.
- I can write a JDBC read with secrets and partition options.
- I know a JDBC read is not incremental unless I filter on a watermark.
- I can design a REST ingestion with retries, raw landing and idempotent writes.
- I can flatten nested JSON with
:,from_jsonandexplode/explode_outer. - I can choose a method using volume, frequency, data type and governance.
Official sources
- Exam guide: https://www.databricks.com/sites/default/files/2026-10/Databricks-Certified-Data-Engineer-Associate-Exam-Guide-August-2026.pdf (opens in a new tab)
- Lakeflow Connect concepts: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/ (opens in a new tab)
- Managed SaaS connectors: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/saas-overview (opens in a new tab)
- Managed database connectors: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/cdc-overview (opens in a new tab)
- Spark JDBC reader: https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html (opens in a new tab)
- Secrets and redaction limits: https://docs.databricks.com/aws/en/security/secrets/ (opens in a new tab)
- JSON parsing: https://docs.databricks.com/aws/en/sql/language-manual/functions/from_json (opens in a new tab)
- Requests sessions and adapters: https://requests.readthedocs.io/en/latest/user/advanced/ (opens in a new tab)
- Retry configuration: https://urllib3.readthedocs.io/en/stable/reference/urllib3.util.html (opens in a new tab)
- Partner Connect: https://docs.databricks.com/aws/en/partner-connect (opens in a new tab)
