How to access a delta table in UC from lakebase postgres ?

[gokkul]

Hi DB Community ,

Is there any way to access/write to delta table in UC from lakebase postgres ? There's a way using "Sync Table" - but it is recommended only to read data from Sync Table . Databricks recommends against writing to sync table . Or else is there any way I could move/copy the delta table data into postgres database inside lakebase instance ? 

Another doubt :

- is it possible to bring postgresql database as foreign catalog into UC ?

[pradeep_singh]

Postgres -> Delta sync exists but is in private preview right now .Both query federation and unity catalog registration are allowed . 
Looks for Data integrations  section in in following link - 
https://docs.databricks.com/aws/en/oltp

[Louis_Frolio]

Greetings @gokkul , I did some digging and here is what I found. 

Writing between Lakebase Postgres and UC Delta

You can’t write from Lakebase Postgres into a UC Delta table through a synced table. Synced tables are intentionally read-only on the Postgres side. They’re managed by a Databricks pipeline and exist to provide a low-latency mirror of a UC table for reads and joins in Postgres. If data needs correction, the guidance is to fix it in the source UC Delta table and let the sync pipeline propagate the change.

If the goal is to move or copy data from UC Delta into Lakebase Postgres, the supported approach is to create a synced table. You can choose Snapshot, Triggered, or Continuous mode depending on freshness requirements, with Continuous syncing at a minimum 15-second interval.

If you need a writable copy inside Lakebase, the common pattern is to treat the synced table as a source and then create your own Postgres table from it, for example using a CREATE TABLE AS SELECT. This preserves the managed sync as a clean, authoritative mirror while giving you a mutable table for application writes. In practice, this usually becomes a two-table pattern: a read-only synced table plus a writable application table, with merge or reconciliation logic handled in views or downstream queries.

If you’d rather avoid managed sync altogether, you can always fall back to a custom ETL approach. Read UC Delta in Databricks and write into Lakebase Postgres using standard Postgres connectivity, such as Spark JDBC, with your own job orchestration.

For the reverse direction, pushing operational changes from Postgres into UC Delta, the recommended product path is Lakeflow Connect for Postgres to Delta. This is currently in Private Preview as a forward ETL capability and is the intended, supported way to continuously replicate Postgres tables into Delta when available.

Using synced tables and writing considerations

A synced table is created and maintained by a managed pipeline. It’s fully queryable using standard Postgres tools and works well for joins with other Postgres tables at query time. While superuser roles could technically write to these tables, Databricks strongly recommends against it. Writing directly to synced tables risks divergence and interference with the pipeline. The clean model is to treat the UC source as authoritative and let the sync do its job.

Bringing PostgreSQL into Unity Catalog as a foreign catalog

Yes, this is supported through Lakehouse Federation. You can create a connection to an external PostgreSQL database using JDBC credentials and then define a foreign catalog in Unity Catalog. This mirrors the external database into UC, applies UC governance, and pushes queries down to Postgres. By design, these federated databases are read-only.

Lakehouse Federation is read-only for external sources like PostgreSQL. The only writable exception is an internal federated Hive metastore scenario. JDBC-based external databases remain read-only.

If you’re specifically working with Lakebase Postgres, you can also register a Lakebase database in Unity Catalog as a read-only catalog. This allows browsing in Catalog Explorer and running governed, read-only queries via SQL warehouses, while all data modification continues to happen directly in Lakebase/Postgres.

Practical patterns that tend to work well

For UC Delta to Lakebase Postgres, typically for application serving:

Create a synced table, choosing Snapshot, Triggered, or Continuous based on freshness needs.

If writes are required in Lakebase, copy from the synced table into your own Postgres table and write there, keeping the synced table as the authoritative mirror from UC.

For Lakebase Postgres to UC Delta:

Use Lakeflow Connect for Postgres to Delta when it’s available to you. That’s the productized path.

Until then, a Databricks job that reads from Postgres and writes to Delta using Spark connectors is the practical alternative.

For external PostgreSQL as a foreign catalog in UC:

Create a connection, define a foreign catalog, grant SELECT privileges, and run read-only federated queries under Unity Catalog governance.

Hope this helps clarify the boundaries and the “happy paths” across these options. Cheers, Louis

[SteveOstrowski]

Hi @gokkul,

There are a few different patterns here depending on which direction you need data to flow. Let me break them down.

READING DELTA/UC DATA FROM LAKEBASE (UC to Postgres)

Synced tables are the primary mechanism for this. They automatically synchronize data from a Unity Catalog table into your Lakebase Postgres database, giving you low-latency reads via standard Postgres queries.

You can create a synced table using the Python SDK, the Databricks CLI, or the REST API. Here is a Python SDK example:

from databricks.sdk import WorkspaceClient
from databricks.sdk.service.database import SyncedDatabaseTable, SyncedTableSpec

w = WorkspaceClient()
synced_table = w.database.create_synced_database_table(
  SyncedDatabaseTable(
      name="database_catalog.schema.synced_table",
      spec=SyncedTableSpec(
          source_table_full_name="source_catalog.source_schema.source_table",
          primary_key_columns=["id"],
          scheduling_policy=SyncedTableSchedulingPolicy.TRIGGERED
      )
  )
)

Three sync modes are available:
- Snapshot: one-time full copy, can be refreshed manually or on a schedule. Best when more than 10% of the source table changes between refreshes.
- Triggered: incremental changes synced on demand. Good balance of freshness and cost.
- Continuous: real-time incremental updates for lowest latency.

For Triggered or Continuous mode, the source Unity Catalog table must have Change Data Feed enabled.

As you noted, synced tables are intended to be read-only on the Postgres side. While it is technically possible to write to them, Databricks strongly recommends running only read queries on synced tables to protect data integrity with the source. Modifications can interfere with the synchronization pipeline.

Documentation: https://docs.databricks.com/aws/en/oltp/instances/sync-data/sync-table

WRITING DATA IN LAKEBASE THAT ORIGINATES FROM DELTA

If you need a writable copy of the data in Lakebase Postgres, the recommended approach is to create a regular Postgres table from the synced table:

CREATE TABLE my_writable_copy AS SELECT * FROM synced_table;

This gives you a standard Postgres table that you can read from and write to freely, independent of the sync pipeline. The trade-off is that this table will not automatically stay in sync with the UC source, so you would need to manage refreshes yourself if the source data changes.

QUERYING LAKEBASE DATA FROM DATABRICKS (Postgres to Delta)

You have two options here:

1. Register your Lakebase database in Unity Catalog. This creates a read-only catalog representation of your Postgres database, letting you run federated queries from Databricks notebooks or SQL warehouses against your Lakebase data. This is built into Lakebase, no separate connection setup needed.

 Documentation: https://docs.databricks.com/aws/en/oltp/instances/register-uc

2. Lakehouse Federation for PostgreSQL. You can also set up any external PostgreSQL database (including Lakebase) as a foreign catalog in Unity Catalog. This enables federated queries from Databricks, though it is read-only and does not support joins or window functions in the pushdown.

 Documentation: https://docs.databricks.com/aws/en/query-federation/postgresql

CAN YOU BRING A POSTGRESQL DATABASE INTO UC AS A FOREIGN CATALOG?

Yes. Both approaches above accomplish this. If your Lakebase database is already provisioned, registering it in Unity Catalog (option 1 above) is the simplest path. For external PostgreSQL databases outside of Lakebase, Lakehouse Federation (option 2) is the way to go.

SUMMARY

- UC Delta to Lakebase Postgres: use synced tables (read-only on Postgres side) or CREATE TABLE AS SELECT for a writable copy.
- Lakebase Postgres to UC/Delta: register your Lakebase DB in Unity Catalog for federated queries, or use Lakehouse Federation.
- Foreign catalog: yes, both registration and federation create a catalog in Unity Catalog that represents your Postgres database.

The main Lakebase documentation hub is here: https://docs.databricks.com/aws/en/oltp

Hope this helps clarify the options available.

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.