Success Story – Emerging

Transforming ETL Architecture on AWS for Real-Time Analytics and Cost Efficiency

Download
Engagement Background

Apexon’s collaboration with a rapidly growing automotive SaaS provider was initiated to modernize its data platform to support real-time analytics and improve operational efficiency. The scope of work involved designing and deploying a real-time, serverless data ingestion architecture using AWS DMS, Lambda, S3, Glue, and Apache Iceberg. The solution supported all dealership database schemas, reduced Glue DPU usage, and enabled reporting freshness within minutes. Apexon’s solution eliminated costly batch-based ETL processes, streamlined data management, and ensured scalability, aligning with the client’s goals of delivering real-time insights and optimizing costs for their growing customer base.

About the Client

The client provides a cloud-based retailing platform to automotive dealerships across the United States. Their platform streamlines customer journey workflows through automation and transparency, enhancing both the customer and dealership experience.

The company uses a multi-tenant Aurora MySQL RDS setup, where each dealership has a dedicated schema. With increasing demand and client growth, the client required a scalable, cost-efficient, and real-time analytics platform to support data-driven decision-making. By partnering with Apexon, the client leveraged expertise in AWS-native services to achieve a modern, high-performance data architecture.

The Challenge

Battling Legacy ETL Limitations in a Growing SaaS Environment

The client’s legacy batch-based ETL pipelines couldn’t keep pace with their rapidly growing customer base. High AWS Glue DPU costs, delayed analytics insights, manual monitoring overhead, data duplication issues, and scalability limitations when onboarding new clients hindered their ability to deliver real-time analytics and maintain cost efficiency. These challenges impacted the client’s ability to provide timely insights and scale operations effectively.

Key obstacles included:

High ETL Costs
High ETL Costs

Glue jobs scanning multiple database schemas consumed excessive DPUs, driving up monthly costs.

Reporting Latency
Reporting Latency

Batch ETL processes delayed insights, making reporting data stale by several hours or a full day.

Limited Scalability
Limited Scalability

Adding new dealership clients required code changes, reducing onboarding agility.

Data Duplication
Data Duplication

CDC data was difficult to deduplicate across multiple Glue jobs, impacting data quality.

Manual Operations
Manual Operations

Failure monitoring and reprocessing required manual intervention, increasing support effort.

These challenges stemmed from two key root causes:

Operational Inefficiencies
Operational Inefficiencies
  • Manual Reliance: The client relied on manual processes for monitoring and reprocessing, which were time-consuming, error-prone, and unscalable.
  • Resource Strain: Growing client demands strained resources, hindering effective data management.
  • High Costs: Inefficient ETL processes increased cloud spend and resource costs.
  • Limited Insights: Lack of real-time data handling limited ad-hoc insights for strategic decisions.

Data Overload and Complexity
Data Overload and Complexity
  • Information Avalanche: The client struggled to process data from multiple schemas in a multi-tenant setup, requiring efficient consolidation and deduplication.
  • Schema Evolution Demands: Changing schemas made consistent data handling challenging.
  • Scalability Demands: Legacy batch systems couldn’t handle growing volumes or real-time needs.

These challenges created significant barriers to providing real-time analytics and scaling efficiently, necessitating a robust, scalable solution.

The Solution

Implementing Real-Time Data Streaming with AWS and Apache Iceberg

Apexon partnered with the client to deliver a real-time data streaming architecture using AWS-native services. Change data from Aurora MySQL was captured using DMS and streamed to S3. Lambda functions normalized and deduplicated CDC records using PyIceberg, while Glue jobs handled full-load and compaction processes. The result was a cost-effective, real-time, and scalable data lake solution using Apache Iceberg, consolidating multiple client database schemas into a single, query-optimized table.

Key components of Apexon’s solution included:

AWS DMS
AWS DMS
  • Captured full-load and CDC ingestion from Aurora MySQL for real-time data changes.

AWS S3
AWS S3
  • Stored raw CDC streams in a multi-tenant structure.

AWS Lambda
AWS Lambda
  • Used PyIceberg for schema normalization and ingestion into Iceberg tables.

AWS Glue
AWS Glue
  • Performed deduplication, compaction, and maintenance workflows.

Scalable Analytics Architecture
Apache Iceberg
  • Supported schema evolution, query optimization, and a cost-effective data lake.

Scalable Analytics Architecture
AWS SQS and SNS
  • Enabled decoupled processing, retry logic, and alerting.

Apexon’s comprehensive approach included:

Multi-Tenant Schema Consolidation
Multi-Tenant Schema Consolidation
  • Consolidated multiple database schemas into a single Iceberg table for unified access.

CDC-Based Incremental Ingestion
CDC-Based Incremental Ingestion
  • Implemented a pipeline for real-time data capture and processing.

Automated Retry and Normalization
Automated Retry and Normalization
  • Used Lambda for schema inference, normalization, and error handling.

Cost Optimization
Cost Optimization
  • Optimized Glue job scheduling and DPU usage for cost efficiency.

By providing a scalable and automated data platform, Apexon empowered the client to streamline operations, reduce costs, and deliver real-time analytics.

Key Results

Cost Savings and Real-Time Insights for Automotive SaaS

Apexon’s strategic solutions enabled the client to achieve significant advancements in cost management, data freshness, and system scalability:

Cost Reduction and Efficiency

Cost Reduction and Efficiency
  • Reduced Costs: Cut AWS Glue costs by 90% through optimized job scheduling.
  • Automated Workflows: Reduced manual intervention via retry and alert automation.
  • Resource Efficiency: Optimized DMS and Lambda workloads for better performance.

Scalable Multi-Tenant Architecture

Scalable Multi-Tenant Architecture
  • Unified Data: Consolidated multiple dealership schemas into a single Iceberg table.
  • Easy Onboarding: Enabled new client onboarding without code changes.
  • Efficient Querying: Simplified partitioning and querying through schema normalization.

Real-Time Analytics Enablement

Real-Time Analytics Enablement
  • Near Real-Time Insights: Improved data freshness from 24-hour latency to minutes.
  • Frequent Updates: Enabled multiple refresh cycles per day across all clients.
  • Eliminated Delays: Removed batch processing delays for real-time data access.

Overall Business Impact

Overall Business Impact
  • 80-90% Reduction in AWS Glue Costs: Optimized processes significantly lowered monthly expenses.
  • Enhanced Reporting Latency: Transitioned from daily batch processing to near real-time insights.
  • Unified Data Architecture: Consolidated multi-tenant schemas into a single Apache Iceberg table.
  • Advanced Data Handling: Supported schema evolution and deduplication with Apache Iceberg.

By delivering these results, Apexon enabled the client to enhance analytics capabilities, improve operational efficiency, and establish a foundation for scalable growth, strengthening their market position.