Lakebase synced tables: serve lakehouse data to an app with Postgres

Part 1 built sales, 1 row per sold order, from the 2 tables setup_serving.py writes. To start here, run python setup_serving.py, then python run_sql.py sales.sql, with the files from part 1.

An app reads differently

The shop wants a page in its app where each customer sees their own numbers: how many orders, what they spent, when they last ordered. That reader is unlike the others in this module. It asks for 1 row by its key, it asks thousands of times a minute, for many users at once, and each answer should come back in a few milliseconds.

A lakehouse engine is built for the opposite: few queries, each reading many rows. So the app gets its own table, with 1 row per customer, in a database built for lookups by key. Save this as lookup.sql:

-- 1 row per customer, for an app that shows a customer their own numbers.
CREATE OR REPLACE TABLE customer_lookup USING delta AS
SELECT c.customer_id,
       c.country,
       count(s.order_id) AS orders,
       coalesce(sum(s.amount), 0) AS spend,
       max(s.day) AS last_order_day
FROM customer_versions c
LEFT JOIN sales s ON s.customer_id = c.customer_id
WHERE c.valid_to = TIMESTAMP'9999-12-31 00:00:00'
GROUP BY c.customer_id, c.country;

SELECT * FROM customer_lookup ORDER BY customer_id;

python run_sql.py lookup.sql prints:

+-----------+-------+------+-----+--------------+
|customer_id|country|orders|spend|last_order_day|
+-----------+-------+------+-----+--------------+
|C01        |ES     |2     |65.50|2026-10-02    |
|C02        |IE     |1     |18.25|2026-10-03    |
|C03        |DE     |0     |0.00 |NULL          |
+-----------+-------+------+-----+--------------+

It starts from the customers' current versions, so C03, who has bought nothing, still gets a row, and the country is where each customer lives now. The app needs both: a page for every customer, about today.

Postgres on your computer

On Databricks, this table goes into Lakebase, Databricks' Postgres. To run the same steps locally, start a Postgres in Docker, with a password of your own:

pip install "psycopg[binary]"
export PGHOST=127.0.0.1 PGPORT=55432 PGDATABASE=shop PGUSER=postgres PGPASSWORD=<a password you choose>
docker run -d --name shop-postgres -e POSTGRES_PASSWORD="$PGPASSWORD" -e POSTGRES_DB=shop -p 127.0.0.1:55432:5432 postgres:17-alpine

The PG variables are Postgres's standard settings, and psycopg reads them when it is given no others, so the scripts below hold no address and no password. Save this as copy_to_postgres.py:

"""Copy customer_lookup into Postgres, all rows at once, as a snapshot sync does.

The connection comes from the standard Postgres settings, PGHOST, PGPORT, PGDATABASE,
PGUSER and PGPASSWORD, so the same script works with any Postgres database.
"""
import psycopg
from delta_session import delta_spark

spark = delta_spark("copy")
rows = [tuple(row) for row in spark.table("customer_lookup").collect()]

with psycopg.connect() as conn:
    conn.execute("CREATE SCHEMA IF NOT EXISTS shop")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS shop.customer_lookup_synced (
            customer_id text PRIMARY KEY,
            country text,
            orders bigint,
            spend numeric(20, 2),
            last_order_day date
        )""")
    # Replace every row in 1 transaction, so the app never sees half a copy.
    conn.execute("DELETE FROM shop.customer_lookup_synced")
    with conn.cursor() as cur:
        cur.executemany("INSERT INTO shop.customer_lookup_synced VALUES (%s, %s, %s, %s, %s)", rows)
print("copied", len(rows), "rows")

The schema and name are what a Lakebase synced table of shop.customer_lookup would get, so the app below runs unchanged against either. collect() is fine here because the table has 1 row per customer and goes to 1 place; a real sync streams the rows. customer_id is the primary key, so Postgres keeps an index on it and finds a row without reading the others. Run python copy_to_postgres.py; it prints copied 3 rows.

The app's lookup

Save this as app.py:

"""What an app does with the copy: look up 1 customer by key, many times a second."""
import sys
import time
import psycopg

with psycopg.connect() as conn:
    customer = conn.execute(
        "SELECT customer_id, country, orders, spend, last_order_day FROM shop.customer_lookup_synced WHERE customer_id = %s",
        (sys.argv[1],),
    ).fetchone()
    print(customer)

    start = time.perf_counter()
    for _ in range(1000):
        conn.execute("SELECT spend FROM shop.customer_lookup_synced WHERE customer_id = %s", (sys.argv[1],)).fetchone()
    print(f"1,000 lookups took {time.perf_counter() - start:.2f} s")

python app.py C01 printed, on the laptop this course was run on:

('C01', 'ES', 2, Decimal('65.50'), datetime.date(2026, 10, 2))
1,000 lookups took 0.25 s

About a quarter of a millisecond a lookup. python app.py C09, a customer who does not exist, prints None, which the app turns into "no orders yet". The %s is a parameter, so a customer id typed into the app cannot change the SQL.

For comparison, the same lookup in Spark, spark.table("customer_lookup").where(F.col("customer_id") == "C01").select("spend").collect(), 20 times in a row, took 0.24 seconds in a classic local session, about 12 milliseconds each, and 2.84 seconds through Spark Connect, the way Databricks' serverless compute is reached, about 140 milliseconds each. Your timings will differ; the gap is the point. Spark plans and runs a job for every query, which pays off when the query reads millions of rows and is waste when it reads 1.

Lakebase on Databricks

On Databricks, Lakebase replaces the Docker container, and a synced table replaces copy_to_postgres.py. Free Edition has 1 Lakebase project, with compute that scales to zero when idle. This part of the module runs only there, so it follows Databricks' docs.

1. Make a project. Create a Lakebase project. It starts with a branch named production and a database named databricks_postgres.
2. Turn on the change data feed on the source, so later syncs send only what changed: ALTER TABLE <catalog>.shop.customer_lookup SET TBLPROPERTIES (delta.enableChangeDataFeed = true).
3. Create the synced table. In Catalog, open customer_lookup, then Create, then Synced table. Name it customer_lookup_synced in the shop schema, choose the project, branch and database, check that the primary key is customer_id, and choose the sync mode.

The sync mode decides how fresh the copy is and what it costs:

The shop's lookup changes once a day for a few customers, so Triggered, run as a task after the pipeline, fits. The first sync copies everything; after that, Snapshot and Triggered syncs run only when something starts them, such as the Database Table Sync pipeline task of a job, which module 10 schedules. Keys cannot be NULL in a synced table, and rows with a NULL key are left out of it, so check the key in the source as part 1 checked sales. The Postgres copy is for reading: Databricks recommends only read queries on it, so that it stays the same as the source.

Connect the app

Lakebase's Connect dialog gives the endpoint's host. Your Databricks identity logs in with an OAuth token as its password, made with the Databricks CLI:

databricks postgres generate-database-credential projects/<project>/branches/production/endpoints/<compute> --output json

Then set PGHOST to the endpoint, PGPORT=5432, PGDATABASE=databricks_postgres, PGUSER to your Databricks user name, PGPASSWORD to the token and PGSSLMODE=require, since Lakebase needs SSL, and app.py runs as it did locally. A token lasts 1 hour, and its expiry matters only when a connection opens, so a long-running app fetches a fresh one before it reconnects; Databricks' Python SDK has generate_database_credential for that. An app that cannot refresh its password every hour can log in with a native Postgres role and password instead.

Lakehouse Real-Time, a serverless SQL warehouse type in Beta, is built for sub-second SQL reads on Unity Catalog tables by many users at once, such as an app's analytical screens. For lookups by key with transactions, and for an app's own tables beside the copy, Postgres is the tool. Part 6 builds the whole serving layer in 1 run.