Lakeflow Connect, JDBC/ODBC, REST, and Choosing an Ingestion Method

Introduction

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 and explode.
• 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 is the part of Databricks that provides ingestion connectors. Think of three families.

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:

1. 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.
2. 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.
3. The destination is a Unity Catalog catalog and schema, so permissions, lineage and auditing apply automatically.
4. 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

1. Connection: a Unity Catalog connection object that stores the credentials for the source (so credentials are never in notebooks).
2. Source objects: the tables, objects or reports to ingest (for example the Account and Opportunity objects in Salesforce).
3. Destination: the target catalog and schema for the streaming tables created by the pipeline.
4. Schedule: how often the pipeline runs, usually as a scheduled Lakeflow Job or a pipeline update trigger.

The first run performs an initial snapshot of the selected objects; later runs read only changes, using the source's change tracking (for SaaS, incremental read APIs; for databases, change data capture or change tracking). The result is a set of bronze-level tables you can then clean in the silver layer.

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("fetchsize", "10000")
      .load())

df.write.mode("append").saveAsTable("prod.bronze.pg_orders")

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, upperBound and numPartitions on a numeric, date or timestamp column.
• query lets you push a filter to the database (for example only rows changed since the last run) instead of pulling the whole table.
• 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_at and a MERGE into 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 the equivalent standard used by client tools and drivers, for example a Python or BI client connecting to a SQL warehouse. In ingestion questions, remember: JDBC for Spark reading a database; ODBC/JDBC drivers for external tools connecting to Databricks.

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.

1. The long runtime comes from a single partition. Use partitionColumn = order_id, lowerBound = 1, upperBound = 180000000 and numPartitions = 16 to read in parallel (keep the number moderate to protect the source).
2. The full re-read is wasteful. Use a query that selects only rows with updated_at greater than the last stored watermark.
3. Land the delta in a staging table, then MERGE INTO the bronze table on order_id to apply updates.
4. If the database is on the supported list, consider a managed database connector instead, which uses change data capture and removes this code.

Why other choices are weaker. Increasing the cluster size does not help a single JDBC partition. Auto Loader reads files, not databases. Copying the database to files by hand adds a manual step and still needs incremental logic.

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.

import requests, json

def fetch_page(token, page):
    r = requests.get(
        "https://api.acme-crm.example/v2/contacts",
        headers={"Authorization": f"Bearer {token}"},
        params={"page": page, "page_size": 500},
        timeout=30,
    )
    r.raise_for_status()
    return r.json()

token = dbutils.secrets.get("acme", "crm_token")
rows, page = [], 1
while True:
    data = fetch_page(token, page)
    if not data["items"]:
        break
    rows.extend(json.dumps(i) for i in data["items"])
    page += 1

(spark.createDataFrame([(r,) for r in rows], "raw STRING")
      .write.mode("append").saveAsTable("prod.bronze.crm_contacts_raw"))

Good practice for REST ingestion:

• Store the raw payload in bronze as a string (or in a volume as JSON files) and parse it in silver, so a parsing bug never loses data.
• 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 MERGE on 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: write the JSON responses as files to a volume and let Auto Loader ingest them. This separates extraction from loading and gives checkpointed, exactly-once file handling.

4. Partner connectors and other methods

Databricks Partner Connect lets you set up integrations with ingestion partners (such as Fivetran or similar tools) from the UI. The partner extracts data from the source and writes Delta tables to Unity Catalog. It suits teams that already use such a tool or that need a source Databricks does not yet cover. 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:

-- 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:

• With Auto Loader, nested JSON is parsed into structs automatically when cloudFiles.inferColumnTypes is true. Use schema hints for deep fields, for example items ARRAY<STRUCT<sku: STRING, qty: INT>>. Auto Loader can also keep the whole payload as a variant column or a string if the structure is too unstable; this is an option when schemas vary between records.
• explode drops rows whose array is null or empty; explode_outer keeps 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 960 orders instead of 1,000. What happened, and what is the fix?

Reasoning.

1. explode produces one row per array element and removes rows whose array is empty or null, so the 40 orders vanish from the result.
2. If the report must count all orders, use explode_outer(items), which keeps those rows with null item columns.
3. Count orders with COUNT(DISTINCT order_id) rather than COUNT(*), 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.

1. Volume: how much data and how many files or rows?
2. Frequency and latency: once, hourly, continuous?
3. Data type and source: files, database, SaaS app, message bus, API?
4. Governance and effort: who owns credentials, schema changes, auditing, and how much code can the team maintain?

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: unpartitioned JDBC reads. One partition means one slow connection; set partition options.
• Pitfall: explode losing empty arrays. Use explode_outer when empty records matter.
• Pitfall: parsing in bronze. Keep raw payloads in bronze, transform 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.

1. Meter files: Auto Loader, because of the file count; availableNow hourly or continuous depending on latency, notifications if listing is slow.
2. Salesforce: Lakeflow Connect managed SaaS connector with a Unity Catalog connection, hourly schedule, destination in the bronze schema.
3. Billing database: JDBC read in a Lakeflow Jobs notebook task, parallelised on invoice_id with numPartitions, filtered by modified_at since the last watermark, then MERGE into bronze. Credentials in a secret scope.
4. 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_json and explode, using explode_outer if days with no readings must be kept.

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_json and explode/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-05/databricks-certified-data-engineer-associate-exam-guide-may-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)