PostgreSQL to Databricks: CDC, Delta Table Replication & Backfill Guide

PostgreSQL data reaches Databricks four ways: a one-time export loaded with COPY INTO, scheduled query-based batch loads, Lakehouse Federation queries that read PostgreSQL in place, or change data capture (CDC) into Delta tables. For a busy production database, CDC is the method that scales: it reads committed inserts, updates, and deletes from the write-ahead log (WAL) via a logical replication slot, so each table is copied once and then kept current without re-scanning.

On PostgreSQL, a stalled slot holds WAL until the disk fills, and a failover can lose the slot. On Databricks, changes are merged on a SQL warehouse that bills while awake, so the sync interval doubles as a cost setting. Estuary is one managed option for this route: its PostgreSQL capture reads the slot into a durable collection, and its Databricks connector merges that collection into Unity Catalog Delta tables through your SQL warehouse on a schedule you set.

How to move PostgreSQL data to Databricks: methods compared

This guide focuses on CDC: batch misses hard deletes without extra work, and federation puts analyst queries on production.

How PostgreSQL to Databricks CDC works

CDC turns committed WAL records into row changes and lands them in Delta tables in batches. In PostgreSQL, wal_level = logical makes changes decodable, a publication picks the tables, a logical replication slot pins the oldest WAL the consumer hasn't confirmed, and pgoutput, the built-in decoder that Debezium and Lakeflow Connect use, streams committed changes in order.

Pipelines first copy existing rows; the slot holds changes made during the copy, which are applied afterward or interleaved with it.

Databricks has two write patterns. Merge stages a batch (in a volume, Kafka topic or staging table) and runs MERGE INTO on the primary key, keeping current state; Lakeflow pipelines' AUTO CDC does this declaratively, ordered by a SEQUENCE BY column. Append writes every change as a new row with COPY INTO or a streaming write: cheap to load, but queries must pick the latest row per key.

PostgreSQL requirements

Switching on logical replication, by host:

Minimal setup, PostgreSQL 14+:

sql
-- Run as a superuser or the tables' owner: adding a table to a publication requires owning it
CREATE USER cdc_user WITH PASSWORD '...' REPLICATION;  -- RDS/Aurora: drop REPLICATION, then GRANT rds_replication TO cdc_user;
GRANT pg_read_all_data TO cdc_user;                     -- PostgreSQL 13 and older: GRANT SELECT per schema
CREATE PUBLICATION cdc_publication;
ALTER PUBLICATION cdc_publication SET (publish_via_partition_root = true);
ALTER PUBLICATION cdc_publication ADD TABLE public.orders, public.customers;
ALTER SYSTEM SET max_slot_wal_keep_size = '50GB';       -- self-hosted; managed hosts: a parameter or flag, where offered

Before any tool connects:

• Name the tables you publish. With an all-tables publication, PostgreSQL refuses UPDATE and DELETE on any keyless table lacking a replica identity.
• Key every replicated table, or a Delta MERGE can't match changes to rows.
• Bound slot WAL with max_slot_wal_keep_size (13+); at the default, -1, a stuck consumer grows WAL until the volume fills.

Databricks requirements

• Unity Catalog: a catalog and schema. Loaders that stage files use a volume, which only exists in a Unity Catalog schema.
• A SQL warehouse. Serverless starts in about 2–6 seconds, with a 10-minute default Auto Stop; pro and classic take about 4 minutes, with a 45-minute default. Idle warehouses bill until they stop.
• A service principal with OAuth machine-to-machine (M2M) credentials. Databricks labels personal access tokens (PATs) legacy and revokes them after 90 days unused.
• Grants:USE CATALOG; USE SCHEMA, CREATE TABLE and CREATE VOLUME on the schema; Can use on the warehouse, to start it and run queries.

Setting up PostgreSQL to Databricks replication

1. Switch the server to logical decoding (host table above) and check SHOW wal_level; reports logical.
2. Run the SQL above and open a network path for the pipeline: IP allowlist, SSH bastion or private networking.
3. Prepare Databricks: catalog, schema, a warehouse with a short Auto Stop, and a service principal with grants.
4. Choose per table: current state (merge) or every change (append, the basis for SCD type 2 history).
5. Backfill, then keep syncing. Create the slot before the backfill so no change falls between copy and stream. Load the most-queried tables first; a tool that doesn't read the slot while copying holds WAL for the whole backfill.
6. Set the merge interval to the freshness people use; every run wakes the warehouse.
7. Prove it end to end. Check counts per table. Change a row, delete one and add a column, then find all three in Databricks after the next sync; DESCRIBE HISTORY should show a write per sync. Pause the pipeline for a few minutes; it should resume from the slot without a reload.

How updates, deletes and schema changes reach Delta tables

• Updates rewrite the row in a merged table or add a row to an append table.
• Deletes: most tools keep the row with a deleted flag; hard deletes remove it in the MERGE.
• Tables added later produce nothing until the publication and the pipeline include them.
• Added columns: logical replication carries no DDL, so the tool must spot the column and run ALTER TABLE … ADD COLUMN; older rows hold NULL.
• Type changes: Delta type widening (Runtime 15.4 LTS+) applies widening such as INT → BIGINT without rewriting files; narrowing means a rebuild.
• Removed columns stay; new rows leave them empty.
• TRUNCATE is replicated by default; tools differ on applying it.
• TOAST: an unchanged large out-of-line value is left out of an update's WAL record; a merge keeps the stored value, an appended row lacks it unless the table has REPLICA IDENTITY FULL.

PostgreSQL to Databricks data type mapping

NUMERIC columns that land as DOUBLE

A DOUBLE stores base-2 fractions, so most decimal amounts become approximations; Databricks says to use DECIMAL when base-10 values must be exact. After a pass through DOUBLE, sums stop reconciling with PostgreSQL and large integer-valued numerics lose low digits. Set DECIMAL(p,s) (up to 38 digits) or STRING before the first load: rows already written as DOUBLE must be reloaded from the source.

Performance, latency and Databricks cost

The WAL can be read continuously, but a Delta table is only as fresh as its last merge, and the merge interval also decides how long the SQL warehouse stays awake.

SQL warehouse uptime

Each load wakes the warehouse, and Auto Stop bills the idle time after it. With a pro warehouse's 45-minute default, any interval under 45 minutes keeps it running around the clock; a serverless warehouse with a short Auto Stop bills minutes per load.

Making MERGE INTO cheaper

• Merge less often. Longer intervals mean fewer wake-ups and more changes collapsed into each MERGE.
• Keep key ranges narrow. Databricks skips files by per-file min/max statistics, so a key-bounded MERGE reads few files when keys follow insert order, but most of the table with random UUIDs.
• Cluster on the merge key with liquid clustering (CLUSTER BY), which Databricks recommends for new tables.
• Enable deletion vectors (delta.enableDeletionVectors, unless the workspace turns them on for new tables) so MERGE marks changed rows instead of rewriting whole Parquet files.
• Let predictive optimization run OPTIMIZE and VACUUM on the small files frequent merges leave.
• Append event tables instead.COPY INTO skips files already loaded and never matches existing rows; updated keys gain rows that a latest-row view must filter.

Production considerations

• Restarts. A restarted pipeline resumes from its last confirmed slot position, so a batch may repeat: MERGE absorbs it, but a streaming append doubles rows unless offset and write commit atomically.
• Reloads. Merge into the live Delta table; truncate-and-reload leaves it partial until done.
• Alerting. Watch WAL retained by the slot (on RDS, CloudWatch's OldestReplicationSlotLag and TransactionLogsDiskUsage) and the age of the newest write in DESCRIBE HISTORY.
• Credentials. An expired PAT stops loads; OAuth M2M secrets last at most 730 days.

Common problems and troubleshooting

WAL piling up behind a stalled or abandoned slot

Every slot pins WAL from its restart_lsn, so WAL piles up when the consumer stops confirming. On this route, the consumer is often gone: a stopped pipeline, a deleted Lakeflow Connect pipeline (its slot stays), or a retired Debezium connector. A long-running transaction stalls even a healthy slot.

sql
SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots ORDER BY restart_lsn;

• active is false and nothing owns it: drop it with pg_drop_replication_slot('slot_name').
• active is true but still growing: look for an old xact_start in pg_stat_activity.
• Busy server, quiet captured tables: write to a small published heartbeat table every few minutes.
• Guardrail:max_slot_wal_keep_size turns a full disk into a lost slot; alert as soon as wal_status moves past reserved.

Slot lost after an RDS or Aurora failover or upgrade

After a maintenance event, the pipeline fails with replication slot "…" does not exist. Databricks is correct up to the event; later changes are missing.

Planned work: stop writes, let the pipeline drain, do the work, and start a fresh slot at the current position, with no reload. Unplanned loss with a known start: reload rows with a newer updated_at or xmin and merge them; rows deleted in the window survive. No trustworthy start: merge full reloads into the existing tables; only a full rebuild removes rows deleted in the gap.

MERGE fails with DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE

Hand-built pipelines (Debezium into a staging table, then MERGE INTO) hit this when a batch holds several changes for one key: Databricks rejects a MERGE where more than one source row matches a target row.

sql
SELECT id, count(*) FROM staged_changes GROUP BY id HAVING count(*) > 1;

Keep only the last change per key, ordered by source LSN or commit time rather than arrival, even when it's a delete, or deleted rows come back. Or apply the feed with AUTO CDC and SEQUENCE BY on that column.

How Estuary's PostgreSQL to Databricks pipeline works

This route uses Estuary's PostgreSQL CDC capture and Databricks materialization. On the PostgreSQL side, Estuary decodes pgoutput from its own slot, flow_slot unless renamed, into one collection per table (below). The materialization uploads compressed JSON to a flow_staging volume in each target schema and applies each table with MERGE INTO, or COPY INTO when every row is new, into Unity Catalog managed Delta tables created with column mapping. Mapped to the sections above:

• Warehouse wake-ups: one per sync, 30 minutes apart by default once caught up (Sync Frequency); a fast-sync window applies your interval inside it and 4-hour syncs outside. Backfills skip the delay. Changes to one key collapse between syncs; materializations on one warehouse and schedule wake it together.
• Standard vs delta-update bindings: standard bindings merge and fill in unchanged TOASTed values; Delta Update bindings append via COPY INTO without reading the table, so neither TOAST fill-in nor hard deletes apply.
• Deletes: a DELETE arrives flagged _meta/op = 'd', and the Delta row stays unless Hard Delete removes it.
• Failover slots: the capture creates its slot without the failover option and recreates it on first start and after an all-bindings backfill; a failover-enabled slot you create keeps that setting only if Estuary support sets no_create_replication_slot.
• Types:NUMERIC lands as DOUBLE unless the field has castToString, which keeps the exact text in a STRING. json, jsonb and arrays arrive as JSON text in STRING, not VARIANT (parse_json() converts it). bytea becomes BINARY; both timestamp types become TIMESTAMP (timestamp read as UTC); time and date or timestamp keys stay STRING.
• Names: schema and table names are lower-cased; spaces, commas, semicolons, braces and parentheses in column names become _.

Implementing PostgreSQL to Databricks with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in PostgreSQL requirements and Databricks requirements. Estuary also needs:
   • An existing publication. The capture adds tables to a publication it owns but never creates one; validation fails with publication "…" does not exist.
   • A public.flow_watermarks table that cdc_user can write to, added to cdc_publication by the publication's owner (setup SQL).
   • The warehouse's server hostname and HTTP path, from its Connection Details tab.
2. Create the PostgreSQL capture. On the Sources page, click New Capture and choose PostgreSQL (RDS, Aurora and other hosts have variants). Fill in the endpoint properties:
   • Server Address: host:port, port 5432 by default; on Aurora, the cluster endpoint. Allowlist Estuary's IP addresses, or use Network Tunnel for an SSH tunnel.
   • User (default flow_capture) and Database (default postgres).
   • Authentication: Password, AWS IAM, Google Cloud IAM or Azure IAM (IAM setup).
   • Advanced Options: the publication, slot and watermarks table default to flow_publication, flow_slot and public.flow_watermarks. With the SQL above, use cdc_user and cdc_publication.

3. Pick the tables.Next lists a binding per published table under Output Collections. Drop what Databricks won't use and raise Backfill Priority for tables analysts need first. Leave Backfill Mode blank (the connector picks Precise for most tables) and Automatically keep schemas up to date on, then click Save and publish to start the backfill and WAL reads together.

4. Create the Databricks materialization. On the capture's details page, click Materialize, choose Databricks and fill in the endpoint properties:
   • Address: the server hostname; port 443 by default.
   • HTTP path: /sql/1.0/warehouses/<id>. The connection test starts a stopped warehouse and fails if it isn't up within 60 seconds; then retry.
   • Catalog Name and Schema Name (default default).
   • Hard Delete: on removes deleted rows; off flags them.
   • Authentication: Personal Access Token (default) or OAuth2 M2M, with the service principal's Client ID and Client Secret.

5. Decide how often to wake the warehouse.Sync Frequency (under Sync Schedule) runs from 0s to 4h, default 30 minutes. For frequent syncs only in working hours, set Timezone, Fast Sync Start Time, Fast Sync Stop Time and optionally Fast Sync Enabled Days; outside the window, syncs run every 4 hours. See sync schedules.

6. Map collections to Delta tables. Click Next and pick a naming convention: Match source structure, Set a default schema or Prefix table names. Each binding under Source Collections has Table and Schema overrides, Delta Update for append-only tables, and Additional Table Create SQL, which runs once after the table is created (for example, ALTER TABLE … CLUSTER BY (id)).
7. Publish.Save and publish creates the volume and Delta tables and loads the backfill at once, not on the schedule.
8. Check both ends. The capture details page shows backfill progress under Connector Status and PRIMARY under Shard Information, and a collection's Data Preview (from the Collections list) shows rows; the materialization details page charts Data Read. In Databricks, DESCRIBE HISTORY lists each MERGE or COPY INTO, and query history shows the connector's statements tagged materialization_name:<task name>. Then repeat the checks in Setting up PostgreSQL to Databricks replication.

How Estuary handles backfill-then-sync, Delta table rebuilds and slot loss

Estuary doesn't load Databricks straight from the slot. The capture appends every change to a collection, a durable log of JSON files in your own cloud storage bucket, and the Databricks materialization reads that log, so rebuilding a Delta table or adding a destination never queries PostgreSQL.

Backfill and WAL changes in one log

To backfill PostgreSQL into Databricks and keep it synced, Estuary writes the initial copy and ongoing changes into the same collection. Backfill chunks alternate with WAL reads, so a large table doesn't stall other changes, and in Precise mode each key's insert precedes its updates and deletes. Staged files are recorded in the task checkpoint before they're applied, and unfinished work re-runs after a crash.

Rebuilding Delta tables without re-reading PostgreSQL

Two caveats: the collection replays only what it holds (trial storage keeps about 20 days; use your own bucket for full history), and a WAL gap can only be filled from PostgreSQL. Delta-update tables also get duplicates from an incremental backfill.

Schema changes on Delta tables

The capture re-checks its tables' schemas every 15 minutes by default (Rediscovery Interval), and new columns reach Databricks through ALTER TABLE … ADD COLUMN. Some type changes, such as integers to DOUBLE or STRING, migrate in place, using column mapping to swap in a new column. Other incompatible changes rebuild the table from the collection by default.

When another approach may make more sense

• Staying inside Databricks: the Lakeflow Connect PostgreSQL connector (Public Preview) reads pgoutput from PostgreSQL 13+ primaries and can keep SCD type 2 history. Its gateway runs continuously on classic compute, billed even when the pipeline is idle, and a failover means a full refresh of every table.
• Queries, not copies: Lakehouse Federation reads PostgreSQL in place through a read-only foreign catalog; nothing to sync, but every query runs on production.
• An existing Debezium and Kafka stack: read the topics with Structured Streaming and apply them with AUTO CDC (which replaces APPLY CHANGES). You operate Kafka Connect and the pipeline, and a lost slot means a new Debezium snapshot.
• Infrequent loads: repeat the one-time export, or poll an updated_at cursor with a Lakeflow Connect query-based connector (no gateway; hard-delete tracking in Beta).
• No logical replication on the source (some managed tiers; standbys before PostgreSQL 16): fall back to polling an xmin or updated_at cursor, which can't see hard deletes. Estuary's PostgreSQL Batch connector does this, by default every 5 minutes on xmin.

Related resources

• Estuary PostgreSQL CDC connector, Amazon RDS, Amazon Aurora, Cloud SQL, batch
• Estuary Databricks materialization, sync schedules, deletions
• Replication slot recovery, backfilling data, schema evolution, castToString
• PostgreSQL: replication settings, logical replication restrictions
• Databricks: Lakeflow Connect PostgreSQL connector, Lakehouse Federation for PostgreSQL, AUTO CDC, MERGE INTO, SQL warehouse settings

To try the route, follow Estuary's data flow guide or create a PostgreSQL capture in the Estuary dashboard. Pricing: estuary.dev/pricing.