From PostgreSQL to Redshift using Azure Datalake Gen2

Configure your LakeXpress data pipeline with the selected components

LakeXpress Orchestrator

Manages end-to-end pipelines
Orchestrates FastBCP extracts with retries & logging
Handles incremental sync and schema-aware metadata

Control DB for increments/custom rules & logsMetadata-drivenLinux or Windows

Data

FastBCPEngine

Parquet

Data or Link

Terminal

./LakeXpress config create \
  -a data/ds_credentials.json \
  --log_db_auth_id log_db_ms \
  --source_db_auth_id datasource_postgresql_01 \
  --source_db_name sales \
  --source_schema_name "sales,dim" \
  --fastbcp_dir_path ./FastBCP_linux-x64/latest/ \
  --fastbcp_p 2 \
  --n_jobs 4 \
  --target_storage_id adls_lake_prd \
  --generate_metadata \
  --sub_path /ingest/bronze \
  --incremental_table "sales.orders:o_orderdate:date" \
  --incremental_table "sales.lineitem:l_shipdate:date" \
  --publish_method internal \
  --publish_target redshift_tgt

# First sync - full load
./LakeXpress sync

# Subsequent syncs - incremental updates (much faster!)
./LakeXpress sync

Documentation

Get LakeXpress

Source - PostgreSQL

PostgreSQL is a powerful and robust open-source relational database. LakeXpress optimizes extractions from PostgreSQL using FastBCP's native connector for excellent performance.

Features:

•Direct streaming read from database
•Full support for PostgreSQL data types
•Secure SSL connections
•Parallel extraction to Parquet

Format - Apache Parquet

Parquet is the industry-standard columnar file format for analytics. LakeXpress uses FastBCP to extract data from source databases and convert it to Parquet format, ensuring optimal compression, query performance, and compatibility with all modern data platforms.

Advantages:

•Columnar format optimized for analytics
•Efficient compression (typically 3-10x)
•Schema evolution support
•Predicate pushdown for fast queries
•Universal compatibility with cloud platforms
•Preserves data types and precision

Cloud Stage - Azure Data Lake Storage Gen2

ADLS Gen2 combines Azure Blob Storage with a hierarchical namespace. LakeXpress leverages ADLS for enterprise-grade data lake storage with optimized analytics performance.

Features:

•Hierarchical namespace for big data analytics
•Fine-grained access control with ACLs
•Optimized for Databricks and Fabric
•High-performance parallel access
•Native integration with Azure analytics services

Destination - Amazon Redshift

Amazon Redshift is a fast, scalable cloud data warehouse. LakeXpress publishes Parquet files from S3 directly into Redshift using COPY commands for optimal performance.

Publishing method:

COPY FROM S3 with Parquet format

Features:

•Direct COPY FROM S3
•Automatic schema mapping from Parquet
•Massively parallel processing (MPP)
•Columnar storage for fast queries
•Integration with AWS ecosystem

Related Pipelines

Teradata → GCS → Redshift

SQL Server → GCS → Redshift

PostgreSQL → ADLS → Databricks

PostgreSQL → GCS → DuckLake

MariaDB → S3 → Redshift

Teradata → ADLS → AWS Glue