Migrating from Redshift/PostgreSQL Federation to Native Databricks Delta Tables

De-coupling OLTP Analytics: Replacing Redshift Spectrum Federation with Databricks Pipelines

Migrating your PostgreSQL transactional (OLTP) data directly into native Databricks Delta tables moves your data architecture from an inefficient, high-risk "federated" setup to a modern, decoupled Lakehouse architecture.

Currently, your setup routes queries from Databricks through Redshift Spectrum down to live PostgreSQL. This burdens your live operational database with heavy analytical queries, risks production downtime, and racks up massive compute costs across three separate layers. By copying this data directly into Databricks Delta tables, you completely isolate your operational database from analytics, process queries instantly, and dramatically cut down cloud infrastructure costs.

To copy your PostgreSQL data directly into a native Databricks Delta table, you need to set up a pipeline that bypasses Redshift. The best approach inside Databricks is to use Delta Live Tables (DLT) or a standard PySpark Notebook pipeline.

Here is a complete, professional, and ready-to-use Executive Summary and Technical Design Document (TDD) Outline based on your recommended title.

Transitioning from Live PostgreSQL Federation to Native Databricks Delta Tables

Executive Summary

Our current data architecture utilizes a multi-hop federation layer (Databricks ➔ Redshift Spectrum ➔ PostgreSQL OLTP) to run analytical workloads against live operational data. While this allows immediate access, it exposes our production database to high operational risks, introduces massive multi-layer compute costs, and suffers from severe query latency due to lack of optimization and network hops.

This initiative outlines the strategy to replace live federation with an automated data ingestion pipeline that copies transactional PostgreSQL data directly into native Databricks Delta tables. By decoupling analytics from our live transactional environment, we will completely eliminate production downtime risks, dramatically accelerate query performance via Delta’s optimized columnar format, and optimize cloud spend by cutting out redundant Redshift processing layers.

Technical Design Document (TDD) Outline

1. Objective & Scope

• Purpose: Document the migration path from a federated model to a scheduled ingestion model.
• In-Scope: PostgreSQL source tables (list names), target Unity Catalog schemas, and Databricks Workflow configuration.
• Out-of-Scope: Real-time streaming (CDC) architectures (if sticking to batch processing), schema changes to the operational PostgreSQL database.

2. Current Architecture vs. Proposed Architecture

• Current State: Databricks SQL/PySpark ➔ Lakehouse Federation ➔ Redshift Cluster ➔ Redshift Spectrum / Federated Query ➔ Live Production PostgreSQL Database.
• Proposed State: Operational PostgreSQL (Read Replica) ➔ Databricks Secret Scope ➔ PySpark JDBC Multi-threaded Ingestion ➔ Unity Catalog Delta Table (Managed).

3. Network & Security Requirements

• Source Connectivity: Target endpoint mapping to the PostgreSQL Read Replica (never the primary master node).
• Firewall Configuration: Explicit whitelisting of Databricks NAT IPs / Control Plane VPC within the PostgreSQL security groups.
• Credential Management: Implementation details for storing database connection strings, user credentials, and SSL certificates inside a Databricks Secret Scope.

4. Ingestion Engine & Code Design

• Parallelism Strategy: Column-based partitioning rules using partitionColumn, lowerBound, upperBound, and numPartitions inside Spark JDBC to avoid single-thread bottlenecks.
• Data Type Mapping: Explicit handling of PostgreSQL-specific data types (e.g., JSONB to String/Struct, UUID to String).
• Initial Load vs. Incremental Strategy:
  • Phase 1: Full historical drop-and-reload (.mode("overwrite")).
  • Phase 2: Incremental micro-batches tracking a modified_at timestamp paired with a MERGE INTO statement to handle upserts.

5. Orchestration & Scheduling

• Scheduler: Configuration of Databricks Workflows (Jobs).
• Compute Strategy: Utilization of isolated Job Clusters rather than all-purpose interactive clusters to reduce DBU consumption costs.
• Frequency: Schedule cadence (e.g., nightly at 02:00 AM UTC or hourly) based on stakeholder data freshness requirements.

6. Data Governance, Optimization & Quality

• Unity Catalog Permissions: Definition of user/group access control lists (ACLs) for the new native Delta tables.
• Performance Tuning: Automatic scheduling of file compaction tasks (OPTIMIZE and Z-ORDER BY) to ensure high-speed querying for downstream BI dashboards.
• Failure Handling: Configuring automated alerts (Slack or Email notifications) on workflow failures or latency SLA breaches.

Below is the detailed, production-ready procedure using a Databricks Notebook pipeline, which is the most common and flexible method

---

Step 1: Gather PostgreSQL Connection Details

Before writing code, secure your database credentials. You will need:

• JDBC URL: jdbc:postgresql://<database-host>:<port>/<database-name>
• Username and Password
• SSL Configuration: Most production PostgreSQL databases require SSL.

Note: Never hardcode passwords. Store your username and password in a Databricks Secret Scope.

---

Step 2: Configure Databricks Network Routing

Your Databricks workspace compute clusters must be physically able to reach your PostgreSQL server.

• Whitelisting: Add your Databricks cluster's NAT IP addresses to your PostgreSQL database's security group / firewall rules.
• Read Replica: Target your PostgreSQL Read Replica host endpoint, not the primary master database. This prevents your data pipeline from slowing down your live application.

---

Step 3: Create the Ingestion Notebook

Create a new Python notebook in Databricks. This script will pull data using Spark's JDBC connector and save it as a native Delta table.

1. Fetch Credentials Securely

# Fetch secrets from your Databricks Secret Scope

pg_user = dbutils.secrets.get(scope="my_secret_scope", key="pg_username")

pg_password = dbutils.secrets.get(scope="my_secret_scope", key="pg_password")

 

# Define connection parameters

jdbc_url = "jdbc:postgresql://your-postgres-replica-host:5432/your_db"

driver_class = "org.postgresql.Driver"

2. Read Data Efficiently (with Parallelism)

Reading a massive PostgreSQL table using a single thread will crash your database or take hours. Use Spark's partition properties to split the load.

# Optimize reading by partitioning across a numeric column (e.g., id or created_at)

lower_bound = 1

upper_bound = 10000000  # Max ID value

num_partitions = 10     # Number of parallel connections to open to Postgres

 

postgres_df = (spark.read

  .format("jdbc")

  .option("url", jdbc_url)

  .option("dbtable", "public.your_oltp_table")

  .option("user", pg_user)

  .option("password", pg_password)

  .option("driver", driver_class)

  # Parallelism properties (Crucial for performance)

  .option("partitionColumn", "id")

  .option("lowerBound", lower_bound)

  .option("upperBound", upper_bound)

  .option("numPartitions", num_partitions)

  .load())

3. Write Data to a Native Delta Table

Save the data into your Databricks Unity Catalog or local workspace catalog.

# Write as a native, highly optimized Delta Table

(postgres_df.write

  .format("delta")

  .mode("overwrite") # Use "append" or "overwrite" based on your needs

  .saveAsTable("catalog_name.schema_name.delta_target_table"))

---

Step 4: Automate the Pipeline with Workflows

To keep the Delta table updated automatically, schedule the notebook using Databricks Jobs:

1. Navigate to Workflows in the Databricks sidebar and click Create Job.
2. Name your task (e.g., Ingest_Postgres_To_Delta).
3. Type: Select Notebook and point it to the script created in Step 3.
4. Cluster: Select an existing cluster or configure a new Job Cluster (Job clusters are cheaper and cleaner for automated tasks).
5. Schedule: Click "Add trigger" on the right sidebar to run this daily, hourly, or via a cron expression depending on how fresh your analytics data needs to be.

---

Production Enhancements to Consider

• Incremental Ingestion (Upserts): Instead of overwriting the whole table every time (.mode("overwrite")), modify your script to only pull rows where updated_at > last_pipeline_run_time. You can then use the MERGE INTO SQL command to update changed rows in your Delta table.
• Network Costs: Ensure your Databricks cluster and PostgreSQL instance are in the same cloud region (e.g., AWS us-east-1 or Azure eastus) to eliminate heavy cross-region data transfer fees.

----------------------------------------------------------------------------------------------------------------------------

Alternative migration solutions, ranked from lowest to highest engineering effort.

Alternative 1: No-Code/Low-Code Managed ETL Tools (Fivetran, Airbyte, Matillion)

Instead of writing and maintaining custom Spark code, you use a third-party data integration platform to handle the replication automatically.

• How it works: You connect Fivetran or Airbyte to your PostgreSQL read replica. The tool automatically reads the logs and writes the data into your Databricks Delta tables.
• Pros:
  • Zero code maintenance: If PostgreSQL schemas change (e.g., a column is added), the tool automatically updates the Delta table without breaking.
  • Built-in log-based CDC: It reads database logs to sync updates efficiently.
• Cons:
  • High software costs: These tools charge based on the volume of data moved, which can become expensive.

Alternative 2: Change Data Capture (CDC) with Debezium & Kafka (Real-Time Streaming)

If your business cannot afford stale data (e.g., hourly updates are too slow), you need a streaming architecture.

• How it works: Debezium listens to the PostgreSQL write-ahead log (WAL). The moment a row changes in PostgreSQL, Debezium streams that exact change event through Apache Kafka or AWS Kinesis into Databricks. Databricks uses Spark Structured Streaming to update the Delta table in real time
• Pros:
  • True real-time data: Data in Databricks stays less than a few seconds behind production
  • Zero query load on PostgreSQL: It reads the database logs directly from disk, placing almost zero CPU/memory load on your live database
• Cons:
  • Massive engineering complexity: Requires managing a Kafka/Debezium infrastructure and streaming code.

Alternative 3: Cloud-Native ETL Tools (AWS Glue, Azure Data Factory, GCP Dataflow)

If your cloud provider already handles your infrastructure, you can use their native serverless ETL tools instead of Databricks compute for ingestion.

• How it works: For example, an AWS Glue job or Azure Data Factory pipeline extracts data from PostgreSQL, saves it as raw files in your cloud storage (S3/ADLS), and triggers Databricks Auto Loader to ingest those files into Delta tables.
• Pros:
  • Integrates seamlessly with existing cloud security, networking, and IAM roles.
• Cons:
  • Splits your logic across two platforms (Cloud provider for moving data, Databricks for querying data).

---

Strategy Comparison Matrix

Migration Solution	Data Freshness	Operational Risk to Production	Engineering Maintenance	Software/Infra Cost
PySpark Notebook (Previous Option)	Batch (Hourly/Daily)	Low (Uses Replica)	Medium	Low (DBUs only)
Managed ETL (Fivetran/Airbyte)	Near Real-Time (Mins)	Very Low (Log-based)	Very Low	High (Tool fees)
Streaming CDC (Debezium + Kafka)	Real-Time (Seconds)	Lowest	Highest	Medium-High
Cloud-Native ETL (Glue/ADF)	Batch (Hourly/Daily)	Low (Uses Replica)	Medium	Medium