In the ever-evolving world of data and AI, flexibility is crucial. I recently had the opportunity to design a data warehouse pipeline for a client preparing for upcoming AI and data analytics initiatives. The client was unsure about the specific data they would need, but they wanted to establish their analytics capabilities first, ensuring they had a robust platform ready to support their future needs.

The Challenge: Uncertain Data Requirements and Handling Large Objects

The client’s goal was to synchronize all their PostgreSQL data into Amazon Redshift, ensuring they had access to all their information for future AI and analytics work. However, Redshift has a limitation: it truncates large objects (LOBs) like TEXT and JSONB fields that exceed 65,535 characters. This posed a risk of losing critical data, especially given the client’s uncertainty about which data would become essential for their future projects.

The Solution: A Multi-Pipeline Approach for Flexibility and Completeness

To address both the uncertainty around data needs and the limitations of Redshift, we implemented a dual-pipeline system:

1. Pipeline 1: PostgreSQL to Redshift

This pipeline was designed to synchronize all tables from the client’s PostgreSQL database to Redshift. By syncing all data, the client could build their analytics capabilities without worrying about missing important information. However, due to Redshift’s character limit, LOBs were truncated. This pipeline served the client’s immediate needs but was insufficient for preserving large data fields required for in-depth analysis.

2. Pipeline 2: PostgreSQL to S3, Delta, and Redshift

We built a second pipeline to ensure no data was lost, particularly LOBs. This pipeline replicated all the data to Amazon S3, preserving even large objects. We then used Delta Lake for Change Data Capture (CDC) and AWS Glue to perform data transformations before loading the data into Redshift. This allowed the client to query complete data sets, including LOBs while maintaining the flexibility needed for their AI and analytics work.

Key Components of the Solution

• Amazon Redshift Cluster: This served as the primary data warehouse. Redshift was ideal for fast analytical queries, but due to its limitations with LOBs, we needed additional storage solutions for complete data replication.
• Amazon S3 and Delta Lake: S3 became the storage solution for raw, complete data, especially large objects. Delta Lake was used to manage ongoing data updates and CDC.
• AWS Glue: Glue played a critical role in managing data transformations and preparing the data for Redshift after full and incremental loads.
• AWS Step Functions: Step Functions were essential for orchestrating the entire replication process, automating the data flow from PostgreSQL to S3, Delta, and Redshift.

Why This Approach Was Necessary

Since the client was uncertain about their exact data needs, replicating all tables ensured no data was overlooked. Redshift provided the necessary analytical capabilities for their immediate needs, while S3 and Delta Lake ensured the long-term preservation of all data, including large objects.

This approach allowed the client to explore their data more freely, enabling them to build a flexible analytics platform to support AI initiatives.

Automation and Scalability

To ensure the system could scale with the client’s growing data needs, we automated the entire deployment process using AWS CloudFormation templates. Step Functions automated the replication tasks and Glue workflows, enabling seamless data updates and transformations without manual intervention.

We also integrated AWS Glue Crawlers with Amazon Athena, giving the client the flexibility to query data stored in Delta tables directly from Athena, further enhancing their data exploration capabilities.

Lessons Learned

• Be Prepared for Uncertainty: In cases where the client is unsure about their future data needs, it’s essential to build a solution that covers all possibilities. By syncing all tables and building in flexible querying capabilities, we allowed the client to evolve their analytics efforts without needing to re-engineer the data platform.
• Handling Platform Limitations: Redshift's character limit for LOBs was a key challenge. By pairing Redshift with S3 and Delta Lake, we ensured that all data could be accessed and analyzed without loss.
• Automation is Vital: Automating data workflows through AWS Step Functions and Glue saved countless hours and ensured the platform could scale effortlessly with the client’s future demands.

This project reinforced the importance of building flexible and scalable data platforms. By proactively addressing the client’s uncertainty and leveraging a multi-pipeline solution, we delivered a robust, future-proof platform to support their current and future data and AI initiatives.