Databricks Lakebase

Lakebase Postgres Autoscaling

FIRST: Use the parent databricks-core skill for CLI basics, authentication, and profile selection.

Lakebase is Databricks' serverless Postgres-compatible database, available on both AWS and Azure (GA). It provides fully managed OLTP storage with autoscaling, branching, and scale-to-zero.

Autoscaling by Default (March 2026): All new Lakebase instances are Autoscaling projects. The /database/ APIs now create autoscaling instances behind the scenes. Existing provisioned instances are unchanged.

Compliance: Supports HIPAA, C5, TISAX, or None.

Capabilities

• Project lifecycle -- create, update, delete Lakebase Postgres Autoscaling projects
• Branching -- copy-on-write branches with TTL, point-in-time recovery, and reset
• Compute scaling -- autoscale 0.5--32 CU, fixed 36--112 CU, scale-to-zero
• High availability -- 1 primary + 1--3 secondaries, automatic failover
• PostgreSQL connectivity -- OAuth token refresh, connection pooling, SSL
• Data API -- PostgREST-compatible HTTP CRUD (Autoscaling only)
• Lakebase synced tables -- sync Unity Catalog Delta tables into Postgres (previously known as Reverse ETL)
• Databricks App integration -- scaffold apps with Lakebase feature, deploy-first workflow
• Cloud support -- AWS and Azure (GA)

Reference docs:

• computes-and-scaling.md — Sizing, endpoint management, scale-to-zero, HA
• connectivity.md — Connection patterns, token refresh, Data API
• synced-tables.md — Lakebase synced tables, data type mapping, capacity planning
• lakehouse-sync.md — CDC from Lakebase Postgres to Unity Catalog Delta tables (UI-only — cannot be configured via CLI or API)
• pgvector.md — Vector similarity search with pgvector extension
• off-platform.md — Off-platform Lakebase (NOT Databricks Apps): external Node.js apps connecting via @databricks/lakebase, env management, token refresh, Drizzle ORM

Resource Hierarchy

Project (top-level container)
  └── Branch (isolated database environment, copy-on-write)
        ├── Endpoint (read-write or read-only)
        ├── Database (standard Postgres DB)
        └── Role (Postgres role)

• Project: Top-level container. Creating one auto-provisions a production branch and a primary read-write endpoint.
• Branch: Isolated database environment sharing storage with parent (copy-on-write). States: READY, ARCHIVED.
• Endpoint (called Compute in UI): Compute resource powering a branch. Types: ENDPOINT_TYPE_READ_WRITE, ENDPOINT_TYPE_READ_ONLY.
• Database: Standard Postgres database within a branch. Default: databricks_postgres.
• Role: Postgres role within a branch.

Resource Name Formats

All IDs: 1-63 characters, start with lowercase letter, lowercase letters/numbers/hyphens only (RFC 1123).

CLI Discovery -- ALWAYS Do This First

Note: "Lakebase" is the product name; the CLI command group is postgres. All commands use databricks postgres ....

Do NOT guess command syntax. Discover available commands dynamically:

databricks postgres -h                    # List all subcommands
databricks postgres <subcommand> -h       # Flags, args, JSON fields

Create a Project

First decide: reuse or create. When building or attaching to an app, ask the user whether to reuse an existing project/branch/database — list them with databricks postgres list-projects (then list-branches / list-databases), let the user pick, and confirm which schema the app will own — or create a new project. Only skip listing and create directly when the user explicitly asked for a brand-new project.

databricks postgres create-project <PROJECT_ID> \
  --json '{"spec": {"display_name": "<DISPLAY_NAME>"}}' \
  --profile <PROFILE>

Auto-creates: production branch + primary read-write endpoint (1 CU min/max, scale-to-zero). Long-running operation; CLI waits by default. Use --no-wait to return immediately.

After creation, verify:

databricks postgres list-branches projects/<PROJECT_ID> --profile <PROFILE>
databricks postgres list-endpoints projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>
databricks postgres list-databases projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>

Extract connection values from JSON output:

Updating a Project

databricks postgres update-project projects/<PROJECT_ID> spec.display_name \
  --json '{"spec": {"display_name": "My Updated Application"}}' \
  --profile <PROFILE>

Deleting a Project

WARNING: Permanent -- deletes all branches, computes, databases, roles, and data. Do not delete without explicit user permission.

databricks postgres delete-project projects/<PROJECT_ID> --profile <PROFILE>

Autoscaling

Endpoints use compute units (CU) (~2 GB RAM per CU). Range: 0.5--32 CU (dynamic), 36--112 CU (fixed). Scale-to-zero enabled by default (5 min timeout).

See computes-and-scaling.md for sizing tables, endpoint CRUD, and configuration details.

Branches

Branches are copy-on-write snapshots. Use for testing schema migrations, trying queries, or previewing data changes without affecting production.

databricks postgres create-branch projects/<PROJECT_ID> <BRANCH_ID> \
  --json '{"spec": {"source_branch": "projects/<PROJECT_ID>/branches/<SOURCE>", "no_expiry": true}}' \
  --profile <PROFILE>

Branches require an expiration policy: "no_expiry": true for permanent, or "ttl": "<seconds>s" (max 30 days).

Limits: 10 unarchived branches per project. 8 TB logical data per branch. 1,000 projects per workspace.

Point-in-time branching: Create from a past state (within restore window) for recovery. Run databricks postgres create-branch -h for time specification fields.

Reset: Replaces branch data with latest from parent. Local changes are lost. Root branches and branches with children cannot be reset.

databricks postgres reset-branch projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>

Delete: Protected branches must be unprotected first (update-branch to set spec.is_protected to false). Cannot delete branches with children. Never delete the `production` branch.

Key Differences from Lakebase Provisioned

All new instances default to Autoscaling as of March 2026. Automatic migration of Provisioned instances begins June 2026.

Migration: Manual via pg_dump/pg_restore (requires pausing writes). Automatic seamless upgrades (seconds of downtime) begin June 2026 -- no customer action required.

What's Next

Build a Databricks App

After creating a project, scaffold a connected Databricks App:

# 1. Get branch name
databricks postgres list-branches projects/<PROJECT_ID> --profile <PROFILE>

# 2. Get database name
databricks postgres list-databases projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>

# 3. Scaffold with lakebase feature
databricks apps init --name <APP_NAME> --features lakebase \
  --set "lakebase.postgres.branch=<BRANCH_NAME>" \
  --set "lakebase.postgres.database=<DATABASE_NAME>" \
  --run none --profile <PROFILE>

For the full app workflow, use the `databricks-apps` skill.

Attach Lakebase to an existing app

apps init --features lakebase (above) wires the database at scaffold time. To attach a project to an existing app, update its resources.

Use the `postgres` resource key for an Autoscaling project — its fields are branch + database (full resource paths from the table above). The legacy database key (instance_name + database_name) is for Provisioned instances only; using it for an Autoscaling project fails with Database instance <name> does not exist. Get the exact paths from list-branches / list-databases (the DB name is often hyphenated, e.g. databricks-postgres).

Update the app's resources with `databricks apps create-update` — the method to use for any app (the older databricks apps update is legacy and can't change resources for an app in a space). update_mask=resources replaces the whole resources array, so read the app's current resources and merge the new one in (or you'll detach the rest). Pass everything in --json; only APP_NAME is positional:

databricks apps create-update <APP_NAME> --json @update.json --profile <PROFILE>   # waits for completion; --no-wait to return early

{
  "update_mask": "resources",
  "app": {
    "resources": [
      {
        "name": "postgres",
        "postgres": {
          "branch": "projects/<PROJECT_ID>/branches/<BRANCH_ID>",
          "database": "projects/<PROJECT_ID>/branches/<BRANCH_ID>/databases/<DATABASE_ID>",
          "permission": "CAN_CONNECT_AND_CREATE"
        }
      }
    ]
  }
}

Confirm the branch/database with the user — don't default to `production` silently. The app's service principal must be able to create and own the schema(s) it uses there, so avoid a branch/database where those schema names are already owned by a user (the SP will hit permission denied … 42501) — a fresh/dedicated branch, or a new app-owned schema, is cleanest. See Schema Permissions for Deployed Apps below for the full ownership model.

Schema Permissions for Deployed Apps

The app's Service Principal has CAN_CONNECT_AND_CREATE -- it can create new objects but cannot access existing schemas. The SP must create the schema to become its owner.

ALWAYS deploy the app before running it locally. This is the #1 source of Lakebase permission errors.

Correct workflow: 1. Deploy first: databricks apps deploy <APP_NAME> --profile <PROFILE> 2. Grant local access (if needed): assign databricks_superuser via UI (project creators already have access) 3. Develop locally: your credentials get DML access to SP-owned schemas

If you already ran locally first and hit permission denied: the schema is owned by your credentials, not the SP. Do NOT drop the schema without asking the user -- dropping it deletes all data.

Ask the user to choose:

• (A) Drop and redeploy: databricks psql --project <PROJECT_ID> -- -c "DROP SCHEMA IF EXISTS <SCHEMA_NAME> CASCADE;", then databricks apps deploy from the app directory. The SP recreates the schema on startup.
• (B) Export first, then drop and redeploy: export via pg_dump (use connection details from databricks postgres get-endpoint; see Other Workflows below for HOST and TOKEN) or copy tables to a temp schema using databricks psql --project <PROJECT_ID>, then do option A. After the SP recreates the schema on redeploy, restore with pg_restore or re-INSERT from the temp schema.

Other Workflows

# Connect a Postgres client -- get connection string
databricks postgres get-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> --profile <PROFILE>

# Manage roles
databricks postgres create-role -h

# Add a read replica
databricks postgres create-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID> <ENDPOINT_ID> \
  --json '{"spec": {"type": "ENDPOINT_TYPE_READ_ONLY"}}' --profile <PROFILE>

Run SQL against Lakebase (GRANT, CREATE INDEX, etc.):

# 1. Get endpoint host
databricks postgres get-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> --profile <PROFILE>

# 2. Generate OAuth token
databricks postgres generate-database-credential \
  projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> \
  --profile <PROFILE>

# 3. Connect (use token from step 2 as password, host from step 1)
PGPASSWORD='<TOKEN>' psql "host=<HOST> user=<USERNAME> dbname=databricks_postgres sslmode=require"

Note: generate-database-credential requires the endpoint resource path (.../endpoints/<ENDPOINT_ID>), not a database or branch path.

Scriptable version (single copy-paste, useful for agents):

EP=projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID>
# get-endpoint JSON shape: {"status": {"hosts": {"host": "<HOSTNAME>"}, ...}, ...}
HOST=$(databricks postgres get-endpoint $EP --profile <PROFILE> -o json \
  | python3 -c "import json,sys; print(json.load(sys.stdin)['status']['hosts']['host'])")
TOKEN=$(databricks postgres generate-database-credential $EP --profile <PROFILE> -o json \
  | python3 -c "import json,sys; print(json.load(sys.stdin)['token'])")
PGPASSWORD="$TOKEN" psql "host=$HOST user=<USERNAME> dbname=databricks_postgres sslmode=require"

Grant app SP access to synced tables (run as project owner after sync is ONLINE and app is deployed):

GRANT USAGE ON SCHEMA public TO "<SP_CLIENT_ID>";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "<SP_CLIENT_ID>";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "<SP_CLIENT_ID>";

For least-privilege, consider syncing into a dedicated schema instead of public so the grant is scoped to synced data only.

Get SP client ID: databricks apps get <APP_NAME> --profile <PROFILE> → service_principal_client_id field.

Data API: PostgREST-compatible HTTP CRUD on Postgres tables. See connectivity.md. Synced Tables: Sync Delta tables into Lakebase. See synced-tables.md.

PostgreSQL Extensions

Lakebase supports PostgreSQL extensions (e.g., pgvector for vector embeddings, pg_stat_statements for query statistics). See the full list of supported extensions.

-- List available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- Install an extension
CREATE EXTENSION IF NOT EXISTS <extension_name>;

For vector embeddings with pgvector, see pgvector.md.

Troubleshooting

SDK and Version Requirements

Lakebase Computes and Scaling

Compute Sizing

Each Compute Unit (CU) allocates ~2 GB of RAM. Lakebase Provisioned used ~16 GB per CU.

Endpoint Operations

Each branch can have only one read-write endpoint. Run databricks postgres <subcommand> -h to discover exact flags.

# Create endpoint with autoscaling
databricks postgres create-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID> <ENDPOINT_ID> \
  --json '{"spec": {"endpoint_type": "ENDPOINT_TYPE_READ_WRITE", "autoscaling_limit_min_cu": 0.5, "autoscaling_limit_max_cu": 4.0}}' \
  --profile <PROFILE>

# Get endpoint details (host, state, CU range)
databricks postgres get-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> --profile <PROFILE>

# List endpoints on a branch
databricks postgres list-endpoints projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>

# Resize (update_mask specifies which fields to change)
databricks postgres update-endpoint \
  projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> \
  "spec.autoscaling_limit_min_cu,spec.autoscaling_limit_max_cu" \
  --json '{"spec": {"autoscaling_limit_min_cu": 2.0, "autoscaling_limit_max_cu": 8.0}}' \
  --profile <PROFILE>

# Delete endpoint
databricks postgres delete-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> --profile <PROFILE>

Autoscaling Configuration

• Range: 0.5–32 CU. Constraint: Max − Min <= 16 CU.
• Valid: 4–20 CU, 8–16 CU, 16–32 CU. Invalid: 0.5–32 CU (spread of 31.5).
• Set minimum CU large enough to cache your working set in memory.
• Connection limits are based on the maximum CU in the autoscaling range.

Scale-to-Zero

Automatically suspends compute after inactivity. Default timeout: 5 minutes. Minimum: 60 seconds.

Wake-up: Compute restarts automatically in ~100ms when a connection arrives. Restarts at minimum CU. Applications should implement retry logic for the brief reactivation period.

Session context is reset on reactivation:

• Temporary tables and prepared statements are lost
• Session settings and in-memory cache are cleared
• Connection pools and active transactions are terminated
• Advisory locks released, NOTIFY/LISTEN subscriptions lost

High Availability

1 primary + 1–3 secondary compute instances across availability zones. GA on AWS and Azure.

How it works:

• Secondaries are hot standbys promoted automatically if primary fails
• All instances share the same storage layer
• Total: 2–4 compute instances per HA endpoint

Connection strings:

Enabling HA: Configured at the endpoint level. Run databricks postgres create-endpoint -h and databricks postgres update-endpoint -h for spec fields. Each secondary can be Read-only (serves reads) or Disabled (failover standby only).

Failover behavior:

• All committed transactions are preserved
• Active connections are terminated — applications must reconnect
• Primary connection string routes to promoted secondary transparently
• With only one readable secondary, read traffic is interrupted during failover

HA secondaries vs read replicas:

Constraints:

• Scale-to-zero not supported with HA
• Max autoscaling spread remains 16 CU
• Secondaries autoscale independently but will not scale below the primary's current CU size
• Minimum 2, maximum 4 compute instances

Sizing Guidance

• Use EXPLAIN ANALYZE to understand query plans — rows examined is the most actionable metric
• Create covering indexes for frequent queries (include filter + sort columns)
• Paginate queries that can return unbounded result sets
• Keep transactions short and deterministic to avoid lock contention
• Follow consistent table access order within transactions to prevent deadlocks

Lakebase Connectivity

Authentication Methods

Connection timeouts: 24h idle timeout is guaranteed. Max connection lifetime beyond 24h is not guaranteed — implement reconnection logic. Always use sslmode=require.

Connection Patterns (Python)

JavaScript/TypeScript Databricks Apps using AppKit get Lakebase connectivity via the lakebase() plugin — see the `databricks-apps` skill's Lakebase guide.

Pattern 1: Direct Connection (Scripts/Notebooks)

For one-off queries. Get a fresh token, connect, execute, close.

Key parameters:

host      = endpoint.status.hosts.host   (from get-endpoint)
dbname    = "databricks_postgres"         (or your database name)
user      = w.current_user.me().user_name
password  = w.postgres.generate_database_credential(endpoint=<name>).token
sslmode   = "require"

Pattern (psycopg2 or psycopg3):

# 1. Get host from endpoint
endpoint = w.postgres.get_endpoint(name="projects/<ID>/branches/<BRANCH>/endpoints/<EP>")
host = endpoint.status.hosts.host

# 2. Generate OAuth token (valid 1 hour)
token = w.postgres.generate_database_credential(endpoint=endpoint.name).token

# 3. Connect
conn = psycopg.connect(host=host, dbname="databricks_postgres",
                        user=username, password=token, sslmode="require")

Pattern 2: Connection Pool with Token Refresh (Production)

For long-running apps. Use SQLAlchemy engine with a creator callback that injects the current token. Refresh the token in a background loop before expiry.

Key config:

pool_size       = 5          (adjust to workload)
max_overflow    = 10
pool_pre_ping   = True       (detect stale connections)
pool_recycle    = 3600       (recycle connections hourly)
sslmode         = "require"

Pattern:

# Token management: store current token in a mutable container
current_token = [generate_initial_token()]

# Background refresh: refresh before the 1-hour expiry.
# Official docs pattern: check expiry timestamp, refresh within 2 minutes of expiry.
# Alternative: refresh every 30-40 minutes (Lakebase team guidance).
def refresh_loop():
    while True:
        sleep(refresh_interval)
        current_token[0] = generate_new_token()

# SQLAlchemy engine: inject token at connect time
engine = create_engine(url, pool_size=5, pool_pre_ping=True)

@event.listens_for(engine, "do_connect")
def inject_token(dialect, conn_rec, cargs, cparams):
    cparams["password"] = current_token[0]

For a complete async implementation with FastAPI integration, see Databricks docs: Connect to Lakebase.

Pattern 3: Static URL (Local Development)

Local dev only. Store the URL in a .env file excluded from version control — never commit real credentials or paste them into shell history. Do not use this pattern in production; use OAuth token refresh (Pattern 1/2) instead.

# .env (add to .gitignore)
LAKEBASE_PG_URL="postgresql://user:password@host:5432/database?sslmode=require"

import os
url = os.environ["LAKEBASE_PG_URL"]
engine = create_engine(url, pool_size=5)

Pattern 4: Databricks App (Python)

For Python apps deployed on Databricks (FastAPI, Flask, Streamlit). Platform injects env vars automatically when the app has a Lakebase database resource.

Auto-injected env vars (set at deploy time):

Pattern:

import os
from databricks.sdk import WorkspaceClient

w = WorkspaceClient()

# Generate OAuth token using platform-injected endpoint
token = w.postgres.generate_database_credential(
    endpoint=os.environ["LAKEBASE_ENDPOINT"]
).token

# Connect using platform-injected env vars
conn = psycopg.connect(
    host=os.environ["PGHOST"],
    port=int(os.environ.get("PGPORT", "5432")),
    dbname=os.environ["PGDATABASE"],
    user=os.environ["PGUSER"],
    password=token,
    sslmode="require",
)

For production apps, combine with Pattern 2's token refresh loop and SQLAlchemy pooling. For the full app development workflow (scaffolding, custom endpoints, schema init), use the `databricks-apps` skill.

Pattern 5: Off-Platform Apps (TypeScript/Node.js)

For apps running outside Databricks (external servers, local dev, CI/CD), use the @databricks/lakebase package — it works standalone without AppKit and handles OAuth token refresh, SSL, and connection pooling automatically.

import { createLakebasePool } from "@databricks/lakebase";

const pool = createLakebasePool({ host, database, endpoint });
const { rows } = await pool.query("SELECT * FROM my_table LIMIT 10");

For full configuration, auth chain, and SSL details, run npm view @databricks/lakebase readme.

Best Practices

• Always use `sslmode=require` — Lakebase requires SSL/TLS on all connections
• Refresh tokens before expiry — check expiry timestamp, refresh within 2 minutes; or refresh every 30-40 minutes
• Use connection pooling — avoid creating a new connection per request
• Enable `pool_pre_ping` — detects stale connections after scale-to-zero wake-up
• Handle scale-to-zero reconnection — first connection after idle may take ~100ms; implement retry
• psycopg2 or psycopg3 — both work; psycopg3 recommended for new development (better async, pooling)

Data API

PostgREST-compatible HTTP API for CRUD operations on Postgres tables. Autoscaling only.

Enabling

1. Navigate to Data API in the Lakebase project UI 2. Click Enable Data API — auto-creates the authenticator role and pgrst schema 3. The public schema is exposed by default

Authentication

All requests require a Databricks OAuth bearer token:

Authorization: Bearer <databricks-oauth-token>

Each Databricks identity must have a matching Postgres role — the auto-created authenticator role assumes the caller's identity at query time.

Create a role for Data API access:

CREATE ROLE "user@example.com" LOGIN;
GRANT USAGE ON SCHEMA public TO "user@example.com";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "user@example.com";

CRUD Operations

# GET — query with filters, pagination, ordering
curl -H "Authorization: Bearer $TOKEN" "$DATA_API_URL/public/users?age=gt.21&limit=10&order=created_at.desc"

# POST — insert
curl -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"name": "Alice", "email": "alice@example.com"}' "$DATA_API_URL/public/users"

# PATCH — update (filter required)
curl -X PATCH -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"status": "inactive"}' "$DATA_API_URL/public/users?id=eq.42"

# DELETE (filter required)
curl -X DELETE -H "Authorization: Bearer $TOKEN" "$DATA_API_URL/public/users?id=eq.42"

Row-Level Security (RLS)

Strongly recommended for multi-tenant data. Policies use current_user (the authenticated Databricks email).

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_isolation ON users USING (email = current_user);

Configuration

Via the Data API UI: exposed schemas, max rows, CORS origins, OpenAPI spec.

Unsupported PostgREST Features

Computed relationships, inner-join embedding, custom media type handlers, stripped-nulls, planned/estimated counts, transaction control via headers, EXPLAIN/trace, pre-request functions, GUCs, PostGIS auto-GeoJSON.

Lakehouse Sync: CDC from Lakebase to Unity Catalog

Lakehouse Sync continuously streams changes from Lakebase Postgres into Unity Catalog Delta tables using Change Data Capture (CDC). Each synced table produces an SCD Type 2 history table in Unity Catalog, giving you a full audit trail queryable from the lakehouse.

This is the reverse direction from synced tables (which go UC → Lakebase). No external compute, pipelines, or jobs are required — it is a native Lakebase feature.

When to Use

• Analyze operational data (orders, user activity, support tickets) in the lakehouse
• Need a historical record of every insert, update, and delete from Postgres tables
• Join operational data with analytics data in Spark, SQL, or BI tools
• Feed Lakebase data into downstream pipelines or ML models

History Tables

For each synced table, a Delta history table is created in Unity Catalog:

lb_<table_name>_history

Each row includes CDC metadata columns:

Enablement

Lakehouse Sync is UI-only — there is NO CLI command or REST API to configure it. Do NOT attempt to automate this step. It is configured through the Databricks workspace UI: "Lakehouse sync" tab in the branch overview. It operates at the schema level: once enabled, all current and future tables in that schema sync to Unity Catalog.

Navigate to: Catalog → your Autoscaling project → branch → Lakehouse Sync → Start Sync, then select the source database/schema, destination catalog/schema, and tables.

Prerequisites

• Lakebase Autoscaling project running Postgres 17
• Tables must reside in the databricks_postgres database
• REPLICA IDENTITY FULL must be set on all source tables:

ALTER TABLE <table_name> REPLICA IDENTITY FULL;

• Verify replica identity:

SELECT n.nspname AS table_schema,
       c.relname AS table_name,
       CASE c.relreplident
         WHEN 'd' THEN 'default'
         WHEN 'n' THEN 'nothing'
         WHEN 'f' THEN 'full'
         WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY n.nspname, c.relname;

• Permissions: CAN MANAGE on source project; USE CATALOG + USE SCHEMA + CREATE TABLE on destination
• Catalogs with default storage are unsupported

Supported Data Types

bool, int2, int4, int8, text, varchar, bpchar, jsonb, numeric, date, timestamp, timestamptz, real, float4, float8, plus enum types (typcategory = 'E').

Check for unsupported types:

SELECT c.table_schema, c.table_name, c.column_name, c.udt_name AS data_type
FROM information_schema.columns c
JOIN pg_catalog.pg_type t ON t.typname = c.udt_name
WHERE c.table_schema = 'public'
  AND NOT (
    c.udt_name IN (
      'bool', 'int2', 'int4', 'int8', 'text', 'varchar', 'bpchar',
      'jsonb', 'numeric', 'date', 'timestamp', 'timestamptz',
      'real', 'float4', 'float8'
    )
    OR t.typcategory = 'E'
  )
ORDER BY c.table_schema, c.table_name, c.ordinal_position;

Monitoring

Check active syncs from Postgres (the wal2delta schema only exists after Lakehouse Sync has been enabled):

SELECT * FROM wal2delta.tables;

Querying History Tables

Latest state of each row (deduplicated current state):

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY <primary_key> ORDER BY _pg_lsn DESC) AS rn
  FROM <catalog>.<schema>.lb_<table_name>_history
  WHERE _pg_change_type IN ('insert', 'update_postimage', 'delete')
)
WHERE rn = 1
  AND _pg_change_type != 'delete';

Full change history for a record:

SELECT *
FROM <catalog>.<schema>.lb_<table_name>_history
WHERE <primary_key> = <value>
ORDER BY _pg_lsn;

Schema Changes

If you need to change a synced table's schema in Postgres, you can use the rename-and-swap pattern. Note: this is community guidance — the official behavior is that column changes (add, drop, type change) trigger a full resnapshot of the affected table.

CREATE TABLE <table>_v2 (
  id INT PRIMARY KEY,
  name TEXT,
  new_column TEXT
);

ALTER TABLE <table>_v2 REPLICA IDENTITY FULL;

INSERT INTO <table>_v2 SELECT *, NULL FROM <table>;

BEGIN;
ALTER TABLE <table> RENAME TO <table>_backup;
ALTER TABLE <table>_v2 RENAME TO <table>;
COMMIT;

Limitations

• Partitioned tables are not supported
• Disabling and re-enabling sync does not re-snapshot — missing changes are lost permanently
• Available on AWS, Azure, and GCP.

Cross-references

• For building Silver/Gold layers from CDC history tables, see medallion-from-cdc.md
• For syncing in the reverse direction (UC → Lakebase), see synced-tables.md

Medallion Architecture from CDC History Tables

Build Silver and Gold analytics layers from Lakehouse Sync CDC history tables using Lakeflow Declarative Pipelines.

When to Use

• You have Lakehouse Sync CDC history tables (lb_<table>_history) in Unity Catalog
• You want Bronze → Silver → Gold layers on top of operational data
• You need clean current-state views, deduplication, and business aggregations for BI, ML, or Genie

Layer Mapping

1. Scaffold a Pipeline Project

databricks bundle init lakeflow-pipelines \
  --config-file <(echo '{"project_name": "operational_analytics", "language": "sql", "serverless": "yes"}') \
  --profile <PROFILE> < /dev/null
cd operational_analytics

2. Configure Pipeline Catalog and Schema

Edit resources/operational_analytics.pipeline.yml:

resources:
  pipelines:
    operational_analytics:
      name: operational_analytics
      catalog: <CATALOG_NAME>
      schema: <SCHEMA_NAME>
      serverless: true
      libraries:
        - file:
            path: src/

3. Silver Layer: Current State from CDC

For each entity, create src/silver_<entity>.sql:

CREATE OR REFRESH MATERIALIZED VIEW silver_<entity>
COMMENT "Current state of <entity> records, deduplicated from CDC history"
AS
SELECT * EXCEPT (rn, _pg_change_type, _pg_lsn, _pg_xid, _timestamp, _sort_by)
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY <primary_key>
      ORDER BY _pg_lsn DESC
    ) AS rn
  FROM <CATALOG_NAME>.<BRONZE_SCHEMA>.lb_<entity>_history
  WHERE _pg_change_type IN ('insert', 'update_postimage', 'delete')
)
WHERE rn = 1
  AND _pg_change_type != 'delete'

Replace <primary_key>, <CATALOG_NAME>.<BRONZE_SCHEMA>, and <entity> with your values.

4. Gold Layer: Business Aggregations

Create src/gold_<metric>.sql:

CREATE OR REFRESH MATERIALIZED VIEW gold_daily_order_summary
COMMENT "Daily order counts and revenue by status"
AS
SELECT
  DATE_TRUNC('day', created_at) AS order_date,
  status,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue
FROM silver_orders
GROUP BY DATE_TRUNC('day', created_at), status

Gold tables read from silver tables within the same pipeline.

5. Data Quality Expectations

Add constraints to silver or gold tables:

CREATE OR REFRESH MATERIALIZED VIEW silver_<entity> (
  CONSTRAINT valid_primary_key EXPECT (<primary_key> IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT valid_timestamp EXPECT (created_at IS NOT NULL) ON VIOLATION DROP ROW
)
COMMENT "Current state of <entity> records with quality enforcement"
AS
SELECT ...

6. Deploy and Run

databricks bundle validate --profile <PROFILE>
databricks bundle deploy -t dev --profile <PROFILE>
databricks bundle run operational_analytics -t dev --profile <PROFILE>

7. Schedule Ongoing Refreshes

Create resources/operational_analytics_job.job.yml:

resources:
  jobs:
    operational_analytics_job:
      trigger:
        periodic:
          interval: 1
          unit: HOURS
      tasks:
        - task_key: refresh_pipeline
          pipeline_task:
            pipeline_id: ${resources.pipelines.operational_analytics.id}

Deploy: databricks bundle deploy -t dev --profile <PROFILE>

Troubleshooting

Cross-references

• For Lakehouse Sync setup, see lakehouse-sync.md
• For synced tables (UC → Lakebase direction), see synced-tables.md

Off-Platform Lakebase: Connecting from External Apps

Off-platform apps are NOT Databricks Apps. Do NOT use databricks apps init, databricks apps deploy, app.yaml, or any Databricks Apps platform commands. Off-platform apps run on your own infrastructure (Vercel, AWS, local Node.js, etc.) and use standard Node.js tooling (npm run dev, node server.js).

Connect to Lakebase from apps deployed outside Databricks App Platform (e.g. Vercel, AWS, Netlify, or any Node.js server).

Recommended: @databricks/lakebase Package

The simplest way to connect — a drop-in pg.Pool replacement with automatic OAuth token refresh.

npm install @databricks/lakebase

Zero-config usage (reads from environment variables):

import { createLakebasePool } from "@databricks/lakebase";

const pool = createLakebasePool();
const result = await pool.query("SELECT * FROM users");

Explicit config:

const pool = createLakebasePool({
  host: "your-lakebase-host.databricks.com",
  database: "your_database_name",
  endpoint: "projects/<project-id>/branches/<branch-id>/endpoints/<endpoint-id>",
  user: "user_id",
  max: 10,
});

Key features:

• Automatic OAuth token refresh (1-hour lifetime, 2-minute buffer)
• Token caching to reduce API calls
• Username resolution: explicit config → PGUSER → DATABRICKS_CLIENT_ID → API lookup via getUsernameWithApiLookup()
• getLakebaseOrmConfig() for ORM-compatible connection config
• OpenTelemetry metrics: lakebase.token.refresh.duration, lakebase.query.duration, pool connection gauges
• Logging: { debug, info, warn, error } boolean flags or custom logger instance

Lakebase Autoscaling only. This package is not compatible with Lakebase Provisioned. For the full config reference, see the `@databricks/lakebase` README.

ORM integration:

// Drizzle
import { drizzle } from "drizzle-orm/node-postgres";
const db = drizzle({ client: pool });

// Prisma
import { PrismaPg } from "@prisma/adapter-pg";
const adapter = new PrismaPg(pool);
const prisma = new PrismaClient({ adapter });

// TypeORM / Sequelize
import { getLakebaseOrmConfig } from "@databricks/lakebase";
// Pass getLakebaseOrmConfig() to your ORM's connection config

Environment Management

Required Environment Variables

Authentication

Local dev — use a short-lived workspace token:

export DATABRICKS_TOKEN=$(databricks auth token --profile <PROFILE> -o json | jq -r '.access_token')

Production — use OAuth M2M credentials:

export DATABRICKS_CLIENT_ID=<service-principal-app-id>
export DATABRICKS_CLIENT_SECRET=<service-principal-secret>
export DATABRICKS_HOST=https://<workspace>.cloud.databricks.com

.env.example Template

DATABRICKS_HOST=https://<workspace-host>
LAKEBASE_ENDPOINT=projects/<project>/branches/production/endpoints/primary
PGHOST=<status.hosts.host from list-endpoints>
PGPORT=5432
PGDATABASE=<status.postgres_database from list-databases>
PGUSER=<your Databricks email or service principal application ID>
PGSSLMODE=require

# Option A: local dev, token auth (expires ~1h)
DATABRICKS_TOKEN=

# Option B: production, M2M auth (service principal)
DATABRICKS_CLIENT_ID=
DATABRICKS_CLIENT_SECRET=

Optional: Zod Validation

For strict fast-fail validation at startup:

import { z } from "zod";

const baseSchema = z.object({
  DATABRICKS_HOST: z.string().min(1),
  LAKEBASE_ENDPOINT: z.string().min(1),
  PGHOST: z.string().min(1),
  PGPORT: z.coerce.number().default(5432),
  PGDATABASE: z.string().min(1),
  PGUSER: z.string().min(1),
  PGSSLMODE: z.enum(["require", "verify-full", "verify-ca", "prefer", "disable"]).default("require"),
  DATABRICKS_TOKEN: z.string().optional(),
  DATABRICKS_CLIENT_ID: z.string().optional(),
  DATABRICKS_CLIENT_SECRET: z.string().optional(),
});

function validateAuth(env: z.infer<typeof baseSchema>) {
  const hasToken = Boolean(env.DATABRICKS_TOKEN);
  const hasM2M = Boolean(env.DATABRICKS_CLIENT_ID) && Boolean(env.DATABRICKS_CLIENT_SECRET);
  if (!hasToken && !hasM2M) {
    throw new Error("Set DATABRICKS_TOKEN or both DATABRICKS_CLIENT_ID and DATABRICKS_CLIENT_SECRET");
  }
  return env;
}

export const env = validateAuth(baseSchema.parse(process.env));

Import env at the top of your server entry point for fast-fail on missing variables.

Drizzle ORM Integration

With `@databricks/lakebase` (recommended):

import { drizzle } from "drizzle-orm/node-postgres";
import { createLakebasePool } from "@databricks/lakebase";
import * as itemsSchema from "@/lib/items/schema";

const pool = createLakebasePool();
export const db = drizzle({ client: pool, schema: { ...itemsSchema } });

Schema per domain — organize schemas under src/lib/<domain>/schema.ts:

import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const items = pgTable("items", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

Running migrations — use Drizzle's programmatic migrator with the Lakebase pool:

// scripts/db-migrate.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { createLakebasePool } from "@databricks/lakebase";

const pool = createLakebasePool();
const db = drizzle({ client: pool });
await migrate(db, { migrationsFolder: "./src/lib/db/migrations" });
await pool.end();
console.log("Migrations applied successfully");

`drizzle.config.ts` — used by drizzle-kit generate (no DB connection needed):

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/lib/*/schema.ts",
  out: "./src/lib/db/migrations",
  dialect: "postgresql",
});

Commands:

• Generate: npx drizzle-kit generate
• Migrate: npx dotenv -e .env.local -- npx tsx scripts/db-migrate.ts

Cross-references

• For on-platform connection patterns, see connectivity.md
• For vector similarity search with pgvector, see pgvector.md
• For AppKit-based Lakebase integration, see the databricks-apps skill's lakebase.md

Vector Similarity Search with pgvector

Use the pgvector extension in Lakebase for embedding-based similarity search (RAG, semantic search, recommendations).

Extension Setup

databricks psql --project <project-name> --profile <PROFILE> -- -c "
  CREATE EXTENSION IF NOT EXISTS vector;
"

If you get error code 42501 (insufficient privileges), the extension may already exist — this is safe to ignore in setupVectorTables():

try {
  await appkit.lakebase.query("CREATE EXTENSION IF NOT EXISTS vector");
} catch (err: unknown) {
  const code = (err as { code?: string }).code;
  if (code === "42501") {
    console.log("[vectors] Skipping extension creation — insufficient privileges (likely already exists)");
  } else {
    throw err;
  }
}

Table Schema

CREATE SCHEMA IF NOT EXISTS vectors;

CREATE TABLE IF NOT EXISTS vectors.documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content TEXT NOT NULL,
  embedding VECTOR(1024),
  metadata JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Dimension matching: VECTOR(1024) must match your embedding model's output dimension. Common Databricks endpoints:

• databricks-gte-large-en — 1024 dimensions
• databricks-bge-large-en — 1024 dimensions

If using a different model (768d or 1536d), change VECTOR(1024) to match.

Vector Store Module

Create server/lib/vector-store.ts:

import type { Application } from "express";

interface AppKitWithLakebase {
  lakebase: {
    query(text: string, params?: unknown[]): Promise<{ rows: Record<string, unknown>[] }>;
  };
  server: {
    extend(fn: (app: Application) => void): void;
  };
}

export async function setupVectorTables(appkit: AppKitWithLakebase) {
  try {
    await appkit.lakebase.query("CREATE EXTENSION IF NOT EXISTS vector");
  } catch (err: unknown) {
    const code = (err as { code?: string }).code;
    if (code === "42501") {
      console.log("[vectors] Skipping extension creation — insufficient privileges (likely already exists)");
    } else {
      throw err;
    }
  }
  await appkit.lakebase.query(`CREATE SCHEMA IF NOT EXISTS vectors`);
  const { rows } = await appkit.lakebase.query(
    `SELECT 1 FROM information_schema.tables
     WHERE table_schema = 'vectors' AND table_name = 'documents'`,
  );
  if (rows.length > 0) return;
  await appkit.lakebase.query(`
    CREATE TABLE IF NOT EXISTS vectors.documents (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      content TEXT NOT NULL,
      embedding VECTOR(1024),
      metadata JSONB NOT NULL DEFAULT '{}',
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);
}

export async function insertDocument(
  appkit: AppKitWithLakebase,
  input: { content: string; embedding: number[]; metadata?: Record<string, unknown> },
) {
  const result = await appkit.lakebase.query(
    `INSERT INTO vectors.documents (content, embedding, metadata)
     VALUES ($1, $2::vector, $3)
     RETURNING id, content, metadata, created_at`,
    [input.content, JSON.stringify(input.embedding), JSON.stringify(input.metadata ?? {})],
  );
  return result.rows[0];
}

export async function retrieveSimilar(
  appkit: AppKitWithLakebase,
  queryEmbedding: number[],
  limit = 5,
) {
  const result = await appkit.lakebase.query(
    `SELECT id, content, metadata, 1 - (embedding <=> $1::vector) AS similarity
     FROM vectors.documents
     WHERE embedding IS NOT NULL
     ORDER BY embedding <=> $1::vector
     LIMIT $2`,
    [JSON.stringify(queryEmbedding), limit],
  );
  return result.rows;
}

Call setupVectorTables(appkit) from onPluginsReady before starting the server.

Distance Operators

Similarity score: 1 - (embedding <=> $1::vector) AS similarity (0 = unrelated, 1 = identical).

Indexing

Add an index after inserting initial data (IVFFlat needs representative data to build):

CREATE INDEX IF NOT EXISTS idx_documents_embedding
  ON vectors.documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
ANALYZE vectors.documents;

For higher recall without tuning, use HNSW instead: USING hnsw (embedding vector_cosine_ops).

Cross-references

• For generating embeddings, see the databricks-apps skill's model-serving.md → Embeddings Pattern
• For Lakebase connection patterns, see connectivity.md

Lakebase synced tables

Official docs: https://docs.databricks.com/aws/en/oltp/projects/sync-tables

Lakebase synced tables sync data from Unity Catalog Delta tables into Lakebase as PostgreSQL tables for OLTP access patterns. Previously known as Reverse ETL.

How it works: Synced tables create a managed copy — a Unity Catalog table (read-only, managed by sync pipeline) and a Postgres table in Lakebase (queryable by apps). Uses managed Lakeflow Spark Declarative Pipelines.

Performance (per Autoscaling CU):

• Continuous/Triggered: ~150 rows/sec per CU
• Snapshot: ~2,000 rows/sec per CU
• Each synced table uses up to 16 connections

Sync Modes

Enable CDF on source table:

ALTER TABLE your_catalog.your_schema.your_table
SET TBLPROPERTIES (delta.enableChangeDataFeed = true)

Prerequisites

• A Databricks workspace with Lakebase enabled
• An active Lakebase project with a branch and endpoint
• A Unity Catalog source table to sync
• Permissions: USE_SCHEMA and CREATE_TABLE on the target schema
• For Triggered/Continuous modes: Change Data Feed enabled on the source table

Note: Your Lakebase database must be registered as a UC catalog (one-time setup per project). Skip if already done.

>

```bash

databricks postgres create-catalog <CATALOG_NAME> \

--json '{

"spec": {

"postgres_database": "<POSTGRES_DATABASE>",

"branch": "projects/<PROJECT_ID>/branches/<BRANCH_ID>"

}

}' --profile <PROFILE>

```

>

The <POSTGRES_DATABASE> is the Postgres database name (default: databricks_postgres), not the resource path.

Creating Lakebase synced tables

Source table must exist first. Synced tables sync from an existing UC table, view, or materialized view. If the source needs transformation, ask the user how they want to prepare it (DLT materialized view, regular view, or existing table). Do not run ad-hoc CREATE TABLE AS SELECT statements.

databricks postgres create-synced-table <LAKEBASE_CATALOG>.<SCHEMA>.<TABLE> \
  --json '{
    "spec": {
      "source_table_full_name": "analytics.gold.user_profiles",
      "primary_key_columns": ["user_id"],
      "scheduling_policy": "TRIGGERED",
      "branch": "projects/<PROJECT_ID>/branches/production",
      "postgres_database": "databricks_postgres",
      "create_database_objects_if_missing": true,
      "new_pipeline_spec": {
        "storage_catalog": "<REGULAR_UC_CATALOG>",
        "storage_schema": "default"
      }
    }
  }' --profile <PROFILE>

Note: Nulls in PK columns are excluded from sync.

Long-running operation; CLI waits by default. Use --no-wait to return immediately.

Supported source types: managed/external Delta tables, managed/external Iceberg tables, views, and materialized views.

Check status:

databricks postgres get-synced-table "synced_tables/<LAKEBASE_CATALOG>.<SCHEMA>.<TABLE>" --profile <PROFILE>

Delete:

databricks postgres delete-synced-table "synced_tables/<LAKEBASE_CATALOG>.<SCHEMA>.<TABLE>" --profile <PROFILE>

Deletes the sync pipeline and the UC table entry. The Postgres table remains and must be dropped manually if no longer needed (DROP TABLE <schema>.<table>).

DABs: The bundle schema includes synced_database_tables, but it maps to the Provisioned Terraform resource (databricks_database_synced_database_table), not the Autoscaling API. Do not use `synced_database_tables` in DABs with Autoscaling projects — it routes through the Provisioned API and may create unintended Provisioned instances. DAB support for Autoscaling synced tables (postgres_synced_tables) is blocked on Terraform provider work and not yet available. For Autoscaling projects, use the CLI commands above.

Example: Sync NYC Taxi Data to Lakebase

Sync the samples.nyctaxi.trips sample table into Lakebase for low-latency app queries.

1. Register a UC catalog (if not already done — see Prerequisites above).

2. Create the synced table (Snapshot mode):

databricks postgres create-synced-table <LAKEBASE_CATALOG>.public.nyc_trips \
  --json '{
    "spec": {
      "source_table_full_name": "samples.nyctaxi.trips",
      "primary_key_columns": ["tpep_pickup_datetime", "tpep_dropoff_datetime", "pickup_zip", "dropoff_zip"],
      "scheduling_policy": "SNAPSHOT",
      "branch": "projects/<PROJECT_ID>/branches/production",
      "postgres_database": "databricks_postgres",
      "create_database_objects_if_missing": true,
      "new_pipeline_spec": {
        "storage_catalog": "<REGULAR_UC_CATALOG>",
        "storage_schema": "default"
      }
    }
  }' --profile <PROFILE>

Note: samples.nyctaxi.trips has no single unique column, so a composite primary key is used. Snapshot mode is chosen here — Triggered/Continuous require CDF enabled on the source table.

3. Check sync status:

databricks postgres get-synced-table "synced_tables/<LAKEBASE_CATALOG>.public.nyc_trips" --profile <PROFILE>

4. Query from Postgres once synced:

SELECT pickup_zip, COUNT(*) AS trip_count, AVG(fare_amount) AS avg_fare
FROM public.nyc_trips
GROUP BY pickup_zip
ORDER BY trip_count DESC
LIMIT 10;

5. Clean up:

databricks postgres delete-synced-table "synced_tables/<LAKEBASE_CATALOG>.public.nyc_trips" --profile <PROFILE>

App Access

If a Databricks App reads synced tables, the app's Service Principal needs explicit GRANT access. See the lakebase skill's SKILL.md "Grant app SP access to synced tables" section for the SQL commands and connection steps.

Data Type Mapping

Unsupported: GEOGRAPHY, GEOMETRY, VARIANT, OBJECT

Capacity Planning

• Connections: Each synced table uses up to 16 connections toward the endpoint limit
• Storage: 8 TB logical data per branch (synced tables count toward the branch storage limit)
• Recommendation: Keep individual tables under 1 TB if they require incremental refreshes
• Connections (instance): 1,000 max concurrent connections per instance
• Naming: Database, schema, and table names allow [A-Za-z0-9_]+ only
• Schema evolution: Only additive changes (adding columns) for Triggered/Continuous modes

Cost guidance:

• Continuous mode: Reuse pipelines for ~10 tables/pipeline — roughly 10x cheaper per table than separate pipelines
• Cost formula: [Rows / (Speed × CUs × 3600)] × DLT Hourly Rate (check current DLT pricing for your cloud/region)
• Snapshot vs incremental: Snapshot is ~10x faster when >10% of data changes per cycle

Lakehouse Sync (Beta)

Reverse direction: continuously streams changes from Lakebase Postgres into Unity Catalog Delta tables using CDC (SCD Type 2 history). Destination tables are named lb_<table_name>_history. Does not require external compute, pipelines, or jobs — it is a native Lakebase feature. Available on AWS, Azure, and GCP.

Important: Tables must reside in the databricks_postgres database for Lakehouse Sync to work.

Lakehouse Sync enablement is a UI-only action — configured via the "Lakehouse sync" tab in the branch overview, not via CLI or API. It operates at the schema level: once enabled, all current and future tables in that schema sync to Unity Catalog. When automating CDC workflows, treat this as a manual post-automation step and inform the user.

Prerequisites:

• Lakebase Autoscaling project running Postgres 17
• Tables must reside in the databricks_postgres database
• REPLICA IDENTITY FULL must be set on all source tables before enabling sync:

  ALTER TABLE <schema>.<table> REPLICA IDENTITY FULL;

• Verify replica identity:

  SELECT n.nspname AS schema, c.relname AS table_name,
         CASE c.relreplident WHEN 'f' THEN 'full' WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' END AS replica_identity
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = 'r' AND n.nspname = 'public';

• Permissions: CAN MANAGE on source project; USE CATALOG + USE SCHEMA + CREATE TABLE on destination
• Catalogs with default storage are unsupported

Limitations:

• Partitioned tables are not supported
• Disabling and re-enabling sync does not re-snapshot — missing changes are lost permanently

For the full Lakehouse Sync reference, see lakehouse-sync.md. For building medallion pipelines from CDC history, see medallion-from-cdc.md.

Use Cases

Product catalog: Sync gold-tier product data to Lakebase for low-latency web app reads. Use Triggered mode for hourly/daily updates.

Real-time feature serving: Sync ML feature tables to Lakebase with Continuous mode for sub-second feature lookups during inference.

Best Practices

1. Sync gold/aggregated tables, not raw tables. Synced tables are for serving pre-curated data at OLTP speed. If your app needs aggregations (GROUP BY, JOINs across large tables), create a gold Delta table or materialized view first, then sync that. Syncing raw tables and aggregating in Postgres defeats the latency benefit — Postgres is not optimized for OLAP workloads on millions of rows. 2. Enable CDF on source tables before creating Triggered/Continuous syncs 3. Snapshot mode is ~10x faster than incremental when >10% of data changes per cycle 4. Monitor sync status for failures and latency via Catalog Explorer 5. Create indexes in Postgres for your application query patterns 6. Account for the 16-connection-per-table limit when planning endpoint capacity

Constraints

• Read-only in Postgres: Only SELECT queries, CREATE INDEX, and DROP TABLE are allowed on synced tables. Any data modifications (INSERT, UPDATE, DELETE) corrupt the sync pipeline.
• Null bytes: Null bytes (0x00) in STRING, ARRAY, MAP, or STRUCT columns cause sync failures. Sanitize source data: REPLACE(col, CAST(CHAR(0) AS STRING), '').
• Unsupported types: GEOGRAPHY, GEOMETRY, VARIANT, OBJECT columns cannot be synced.
• FGAC not propagated: Fine-grained access control (row filters, column masks) from Unity Catalog is not propagated to synced tables. Workaround: Create a view on the source table with the desired filter (SELECT * FROM table WHERE ...), then sync the view in Snapshot mode. Caveat: the sync runs as the creator and only sees their visible rows.