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

An app that shows each user their own row needs lookups by key in about a millisecond, for many users at once, which analytical engines are not built for. Databricks serves such data from Lakebase, its managed Postgres: a synced table copies a Unity Catalog table into Postgres and keeps it updated, as a snapshot, on a trigger or continuously, and the app reads it with any Postgres driver. The lakehouse stays the source; the Postgres copy is read-only for the app.

- Page: https://tecyfy.com/data-engineering/course/serving-data/lakebase
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 8: Serving data, Lakebase, lookups for an app
- Parts of this module: Serving tables and the join that counts twice (https://tecyfy.com/data-engineering/course/serving-data); Metric views, revenue defined once (https://tecyfy.com/data-engineering/course/serving-data/metric-views); Dashboards and Genie on a SQL warehouse (https://tecyfy.com/data-engineering/course/serving-data/dashboards-and-genie); Sharing tables with partners (https://tecyfy.com/data-engineering/course/serving-data/sharing); Lakebase, lookups for an app (https://tecyfy.com/data-engineering/course/serving-data/lakebase); Build it: the shop's serving layer, checked (https://tecyfy.com/data-engineering/course/serving-data/build-it)

At the end of this part you can:

- Build a lookup table with 1 row per key for an app, and say why it is served from Postgres rather than a warehouse
- Copy it into Postgres and look rows up by key from Python with psycopg
- Create a Lakebase synced table, choose its sync mode and connect an app with an OAuth token

[Part 1](/data-engineering/course/serving-data) 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`:

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

```text
+-----------+-------+------+-----+--------------+
|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:

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

```python
"""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`:

```python
"""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:

```text
('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:

| Mode | What each sync does | When |
| --- | --- | --- |
| Snapshot | Copies every row again | More than about 10% of rows change each time |
| Triggered | Applies the inserts, updates and deletes since the last sync | Rows change on a known cadence, such as after a daily pipeline |
| Continuous | Streams changes within seconds | The app must see changes almost at once; it costs the most |

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:

```bash
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](/data-engineering/course/serving-data/build-it) builds the whole serving layer in 1 run.

## Check yourself

1. customer_lookup changes for about 2% of its rows each day, and the app can show numbers up to a day old. Which sync mode fits?

   A. Continuous, so the app is always current
   B. Triggered, run once a day after the pipeline, which applies only the changed rows from the change data feed
   C. Snapshot every minute
   D. No sync: the app queries the SQL warehouse

2. Why does app.py read its connection from PGHOST, PGUSER and PGPASSWORD rather than from the code?

   A. psycopg cannot take a host in the code
   B. Lakebase needs environment variables
   C. The same script then runs against local Postgres and Lakebase, and the password or token never sits in the code
   D. It makes the lookups faster

### Answers

1. B. Triggered applies inserts, updates and deletes since the last sync, which suits a known cadence and a small share of changed rows. Snapshot rewrites everything and pays off when more than about 10% of rows change; continuous costs the most and is for changes that must appear within seconds. Triggered needs the change data feed on the source table.
2. C. psycopg.connect() with no arguments uses the standard Postgres settings. Locally they name the Docker container; on Lakebase they name the endpoint, your Postgres role and an OAuth token, which expires after an hour and must be fetched fresh, never stored in the code.

## Questions

### What is Databricks Lakebase?

Lakebase is a managed Postgres database inside the Databricks platform, for apps that need fast lookups and transactions next to the lakehouse. It scales automatically, can scale to zero, and has branches for development. Synced tables copy Unity Catalog tables into it so apps can read lakehouse data with any Postgres driver.

### What is reverse ETL?

Moving data from the analytical store back into the operational systems that apps use, such as copying a gold table of customer scores into the database an app reads. Databricks' synced tables, which copy Unity Catalog tables into Lakebase Postgres and keep them updated, are reverse ETL.

## Sources

- [Lakebase Postgres](https://docs.databricks.com/aws/en/oltp/projects)
- [Serve lakehouse data with synced tables](https://docs.databricks.com/aws/en/oltp/projects/sync-tables)
- [About authentication](https://docs.databricks.com/aws/en/oltp/projects/authentication)
- [SQL warehouse types](https://docs.databricks.com/aws/en/compute/sql-warehouse/warehouse-types)
- [Using Lakebase with Databricks Apps](https://docs.databricks.com/aws/en/oltp/projects/databricks-apps)
- [Databricks Free Edition limitations](https://docs.databricks.com/aws/en/getting-started/free-edition-limitations)
- [Connection strings and environment variables](https://www.psycopg.org/psycopg3/docs/api/connections.html)