Ingest from a database over JDBC¶

Not every source is a file or a Delta table. Often the data you need still lives in an operational database — a customer master in PostgreSQL, a product catalog in MySQL — and your job is to pull it into the lakehouse over JDBC.

You could hand-write it: spark.read.format("jdbc"), then a chain of .option(...) calls for the URL, driver, user, password, and table; a dbutils.secrets.get(...) around each credential so the password never lands in source; a .load(); and then a second block to declare the target table. Or you declare a type: jdbc load, name the connection and the table, and let Lakehouse Plumber write that Lakeflow for you. That is the idea on every page: declare your ETL, don’t hand-write it.

Let’s pull a customer master table out of an operational PostgreSQL database and land it as a bronze table — over JDBC, with the password supplied by a secret, without touching PySpark.

Before you start¶

You need Lakehouse Plumber installed and a project to work in. A JDBC read also has two runtime prerequisites that live outside the flowgroup: the database driver (here org.postgresql.Driver) must be available on the pipeline’s compute, and the database credentials must already be stored in a Databricks secret scope. The flowgroup references the secret; it never holds the password.

Declare the JDBC load¶

A pipeline in Lakehouse Plumber is a flowgroup: a short YAML file describing a sequence of actions. This one has two — a load that reads the external table into a view, and a write that lands the view as a bronze table.

Create pipelines/customers_jdbc.yaml:

pipelines/customers_jdbc.yaml¶

# Ingest guide: pull the customer master table from an operational
# PostgreSQL database into the bronze layer over JDBC.
# Action chain: load (jdbc, batch) -> write (materialized_view).
pipeline: bronze_customers
flowgroup: customers_jdbc

actions:
  # 1. Load: read the customer master table from the operational sales
  #    database over JDBC. Batch read — a JDBC source has no stream.
  - name: load_customers_jdbc
    type: load
    readMode: batch
    source:
      type: jdbc
      url: "jdbc:postgresql://sales-db.internal:5432/sales"
      driver: "org.postgresql.Driver"
      user: "${secret:sales_db/username}"
      password: "${secret:sales_db/password}"
      table: "public.customers"
    target: v_customers_jdbc
    description: "Customer master table from the operational sales database"

  # 2. Write: land the pulled rows as a bronze materialized view that
  #    Databricks recomputes from the source view on each refresh.
  - name: write_customers_bronze
    type: write
    source: v_customers_jdbc
    write_target:
      type: materialized_view
      catalog: "${catalog}"
      schema: "${bronze_schema}"
      table: customers
      comment: "Bronze copy of the operational customer master, refreshed over JDBC"

The load action’s source names the connection: the JDBC url, the driver class, and the table to read. readMode: batch decides how the source is read — a JDBC source has no stream to tail, so a JDBC load is a batch read, and LHP generates spark.read rather than spark.readStream. Give either a table (read the whole table) or a query (push a SELECT down to the database), but not both.

Credentials are the part you never inline. user and password are written as ${secret:sales_db/username} and ${secret:sales_db/password} — secret references, not literal values. The write action lands the pulled rows as a materialized_view in the bronze layer: a JDBC pull has no incremental stream to append, so a materialized view — recomputed from the source on each refresh — is the natural landing for a full-table read.

Supply the tokens and the secret scope¶

The ${...} tokens and the secret scope both resolve from a per-environment substitutions file. Create substitutions/dev.yaml:

substitutions/dev.yaml¶

# Development environment tokens.
# Referenced from flowgroups with ${token} syntax.
dev:
  catalog: dev_catalog
  bronze_schema: bronze

# Logical secret scopes. A ${secret:sales_db/<key>} reference resolves its
# scope through this map, so the flowgroup names a scope, never a raw secret.
secrets:
  default_scope: dev_secrets
  scopes:
    sales_db: dev_sales_db_secrets

Two things resolve here. The catalog and bronze_schema tokens fill in the target table’s name. And the secrets block maps the logical scope name you wrote in the flowgroup — sales_db — onto the actual workspace secret scope dev_sales_db_secrets. So ${secret:sales_db/username} becomes a lookup against dev_sales_db_secrets. Point prod.yaml at a production scope later and the same flowgroup reads production credentials, unchanged.

Note

The secret reference is resolved to a dbutils.secrets.get(...) call in the generated code — the password is fetched at run time, never written into the Python. That is why the credential lives behind ${secret:scope/key} and not as a literal string in the flowgroup.

Generate the pipeline¶

Now compile the YAML into Lakeflow code. Validate first, then generate:

$ lhp validate --env dev
✓ discover (0.01s)
✓ preflight (0.00s)
✓ bronze_customers  0 files
✓ validate (0.44s)
1 validated · 0.5s

$ lhp generate --env dev
✓ discover (0.00s)
✓ preflight (0.00s)
✓ bronze_customers  1 file
✓ generate (0.38s)
✓ format (0.02s)
✓ monitoring (0.00s)
1 pipeline generated · 1 file · 0.4s

validate resolves every token and secret reference and checks the actions before you commit to generating; generate writes the Python.

Read what Lakehouse Plumber wrote¶

Open generated/dev/bronze_customers/customers_jdbc.py. This is the entire output — nothing is hidden behind a runtime:

generated/dev/bronze_customers/customers_jdbc.py¶

# Generated by LakehousePlumber
# Pipeline: bronze_customers
# FlowGroup: customers_jdbc

from pyspark.sql import DataFrame
from pyspark import pipelines as dp

# Pipeline Configuration
PIPELINE_ID = "bronze_customers"
FLOWGROUP_ID = "customers_jdbc"


# ============================================================================
# SOURCE VIEWS
# ============================================================================


@dp.temporary_view()
def v_customers_jdbc():
    """Customer master table from the operational sales database"""
    df = (
        spark.read.format("jdbc")
        .option("url", "jdbc:postgresql://sales-db.internal:5432/sales")
        .option(
            "user", dbutils.secrets.get(scope="dev_sales_db_secrets", key="username")
        )
        .option(
            "password",
            dbutils.secrets.get(scope="dev_sales_db_secrets", key="password"),
        )
        .option("driver", "org.postgresql.Driver")
        .option("dbtable", "public.customers")
        .load()
    )

    return df


# ============================================================================
# TARGET TABLES
# ============================================================================


@dp.materialized_view(
    name="dev_catalog.bronze.customers",
    comment="Bronze copy of the operational customer master, refreshed over JDBC",
    table_properties={},
)
def customers():
    """Write to dev_catalog.bronze.customers from multiple sources"""
    # Materialized views use batch processing
    df = spark.read.table("v_customers_jdbc")

    return df

It’s ordinary Lakeflow. Because you set readMode: batch, the source view is a @dp.temporary_view wrapping spark.read.format("jdbc") with the url, driver, and dbtable options set from your source. Each ${secret:sales_db/...} reference became a dbutils.secrets.get(scope="dev_sales_db_secrets", key=...) call — the password is fetched at run time, never stored. The target is a @dp.materialized_view whose three-part name resolved to dev_catalog.bronze.customers, defined by a batch spark.read.table of the source view. You described a connection and a target table; LHP wrote the reader, the secret lookups, and the view declaration that connect them.

What you just did¶

A two-action flowgroup — thirty-three lines of YAML, comments and all — compiled to fifty-four lines of Lakeflow Python. Zero lines of PySpark came from you: not the spark.read.format("jdbc") reader, not the two dbutils.secrets.get lookups that keep the password out of source, not the @dp.materialized_view declaration with its resolved three-part name. And the output is code you own: version it, diff it, and open it in the Databricks editor like anything else.

The payoff is less about line count than about the parts you did not have to get right by hand — the secret handling and the batch-read wiring are generated the same way every time, for every JDBC source you add.

What’s next¶

Push a query to the database. Swap table for a query field to read the result of a SELECT instead of a whole table — filter, project, or join at the source so the pipeline pulls only the rows it needs.
Stamp extraction metadata onto the rows. operational_metadata adds columns such as an extraction timestamp to the loaded view, so downstream tables can tell when each batch arrived.
See every JDBC option. Other drivers (MySQL, SQL Server, Oracle), the query form, and the credential rules are listed in the load action reference with their constraints.