PostgreSQL to Databricks: How to Replicate Your Data

One question determines the shape of this pipeline, and it is worth answering before you touch a configuration screen: what should happen in Databricks when a row is deleted in PostgreSQL?

Answer it three different ways and you get three different pipelines. If the row should vanish, you want CDC with hard deletes. If the deletion is itself a fact worth keeping, you want CDC with soft deletes. If deletions do not happen or do not matter, you can skip CDC and use a cursor. Most teams have not thought about it, discover the answer six weeks in when a compliance question arrives, and rebuild.

Setting up CDC, and the risk that will page you

CDC reads the write-ahead log, which is the only mechanism that captures deletes and updates that do not touch a timestamp column.

On the Postgres side: set wal_level to logical, create a replication slot using the pgoutput plugin, create a publication covering the tables you want, and give the Airbyte user the REPLICATION attribute plus SELECT on those tables. Changing the WAL level requires a restart, so plan a window.

Now the operational reality. A replication slot guarantees the consumer will not miss anything, and it delivers that guarantee by retaining WAL segments until they are read. If Airbyte stops reading, because a connection is paused, a sync is failing repeatedly, or a credential expired, the slot keeps holding WAL and disk on your primary grows. If it fills, the database stops accepting writes.

This is the single most important operational fact about Postgres CDC and it has nothing to do with Airbyte specifically. Alert on replication slot lag on day one, not after the incident, and drop slots you are no longer consuming.

For a database with no public address, the Postgres source supports SSH tunnelling through a bastion host. If you are on Airbyte Cloud with IP restrictions, add Airbyte's IP addresses to your allow list.

Hard deletes or soft deletes?

The Databricks destination exposes a CDC deletion mode with two options. Hard delete removes the row, which is the default. Soft delete keeps a tombstone record carrying an _ab_cdc_deleted_at timestamp.

Hard delete gives you a mirror of production, which is what most analytics wants and what most privacy obligations require. Soft delete gives you a record that a deletion happened and when, which matters for audit trails, for churn analysis where the deletion is the event you care about, and for any downstream model that would otherwise silently lose rows.

Pick this at setup. Changing it later leaves you with a table whose history reflects two different policies, and no way to tell from the data which rows fell under which.

What does Databricks require?

Unity Catalog must be enabled on the workspace. This is not negotiable and it is the most common first-attempt failure.

You need a SQL warehouse or all-purpose compute cluster, and permission to create schemas, tables, and Unity Catalog Volumes in the target catalog. Volumes are where the connector stages Avro files before loading them, so that permission is essential rather than incidental.

Use OAuth2 with a service principal for credentials: create the principal, generate a client ID and secret, and grant access to the target catalog and schema. A personal access token works but ties the pipeline to one person's account, which is a poor property for something that runs at three in the morning.

Collect Server Hostname, HTTP Path, and Port from the SQL warehouse Connection Details tab, with 443 as the default. The Unity Catalog name is the top-level catalog in the sidebar. Accept the JDBC driver terms in the configuration, set the Default Schema, and leave Purge Staging Files enabled unless you are actively debugging a load.

How does Postgres data land?

Since version 4.0.0 the destination uses direct load. Data goes straight to final tables and the underscore-prefixed raw tables are no longer produced. If you are migrating from 3.x with models reading raw tables, rewrite them first; the connector's migration guide covers the upgrade.

Each table carries your columns plus Airbyte's metadata: a raw ID, an extraction timestamp, a metadata object, and a generation ID used to track refreshes.

Most Postgres types map sensibly. Integers become LONG, booleans stay booleans, dates stay DATE, timestamps with timezone become TIMESTAMP and those without become TIMESTAMP_NTZ, both at microsecond precision, and numerics become DECIMAL(38, 10).

The mapping to watch is JSON and JSONB. Both land as STRING containing serialised JSON, as do arrays. Postgres schemas lean on JSONB heavily, and analysts will need to parse those columns in Databricks. If a JSONB column holds fields your BI layer treats as first-class, extract them into typed columns in a downstream model rather than leaving every query to parse JSON. Schema and table names are lowercased; column names keep their source casing.

Frequently asked questions

Do deletes propagate?

Yes, with CDC. The deletion mode decides how: hard delete removes the row, soft delete leaves a tombstone with a deletion timestamp. Cursor-based sync does not capture deletes at all.

What is the biggest operational risk?

An unread replication slot retaining WAL and filling disk on the Postgres primary. Alert on slot lag and remove slots you are not consuming.

How do JSONB columns arrive?

As STRING columns holding serialised JSON. Parse or extract them in a downstream model if they carry fields your analysts query directly.

Can I skip CDC?

If you never delete rows and every update touches a timestamp column, cursor-based incremental works and is simpler to operate. Otherwise CDC is the only option that will not drift from the source.

Get your PostgreSQL data into Databricks

Decide the deletion policy first, alert on replication slot lag before anything else, and plan a model for JSONB columns. For the same source landing elsewhere, see our guide to PostgreSQL to ClickHouse. For a different source into the same lakehouse, see Microsoft Dataverse to Databricks.

Start syncing now →