Executive Summary:
A retail-tech platform needed a shared reporting view across PostgreSQL, Mixpanel and BigQuery without repeated full-table scan costs. Rapyder built an Amazon Redshift data platform with hybrid replication, scheduled exports and tracked incremental loads. The case study reports 80-85% lower BigQuery costs, 25-30% fewer pipeline failures and 20-30% faster data availability.
About Customer:
The customer provides smart calculators and mobile applications for small and medium retailers, supporting billing, transactions, inventory and daily operations. Growing merchant adoption generated transactional and usage data across several systems, increasing the need for consolidated reporting.
Problem Statement:
Transactional and usage data sat in PostgreSQL, Mixpanel and BigQuery without a shared reporting view. Repeated BigQuery scans increased costs, while manual reconciliation and inconsistent raw datasets slowed analysis. The team also lacked reliable load tracking to manage duplicate-processing risks.
Industry:
Industries, Retail / E-commerce
Offering:
Offerings, Data Analytics
Business Challenges:
- Fragmented sources: Transactional and usage data sat in PostgreSQL, Mixpanel and BigQuery, with no shared reporting view.
- Repeated scan costs: Full-table BigQuery scans increased source-side billing as data volumes grew.
- Manual reconciliation: Analysts spent time checking overlapping datasets across systems instead of analysing business performance.
- Uncurated reporting data: Dashboards relied on inconsistent raw data without a standardised BI layer.
- Duplicate-load risk: Limited tracking of completed processing created a risk of duplicate loads and unreliable downstream metrics.
Solution Implemented:
- Hybrid replication: Rapyder used Zero-ETL for supported PostgreSQL tables and AWS DMS for tables outside that coverage, replicating source data into Redshift bronze schemas.
- Mixpanel export: Daily Mixpanel exports landed in Amazon S3, were catalogued by AWS Glue Crawlers and queried through Redshift Spectrum without copying them into Redshift storage.
- BigQuery orchestration: AWS Glue jobs, Step Functions and EventBridge coordinated daily exports, with Lambda COPY commands loading new partitions into Redshift.
- Tracked incremental loads: Glue bookmarks and incremental exports reduced repeated scans. DynamoDB tracked exported tables and loaded partitions to support idempotent processing and help prevent duplicate loads.
- Curated reporting layers: Redshift SQL transformations organised data into bronze, silver and gold layers for consistent, analytics-ready reporting.
- Pipeline monitoring: Amazon CloudWatch provided centralised monitoring across Glue, Lambda and replication pipelines.
Technology Stack:
- AWS Zero-ETL Integration
- AWS Database Migration Service (DMS)
- Amazon Redshift
- AWS Glue
- AWS Step Functions
- Amazon EventBridge
- AWS Lambda
- Amazon S3
- Amazon DynamoDB
- Amazon CloudWatch
Business Benefits:
- 80-85% lower BigQuery costs – Glue bookmarks and incremental exports eliminated repeated full-table scans, cutting source-side billing dramatically.
- 25-30% fewer pipeline failures – Step Functions orchestration and idempotent, DynamoDB-tracked loads made ingestion far more reliable.
- 20-30% faster data availability – Near real-time PostgreSQL replication combined with optimized batch pipelines shortened the time from data creation to data availability.
- Three sources unified into one – PostgreSQL, Mixpanel, and BigQuery now feed a single Redshift warehouse instead of three disconnected systems.
- Reduced operational overhead – AWS-managed services across Zero-ETL, DMS, Glue, and Redshift lowered the manual upkeep burden on the engineering team.
- Higher trust in reporting – Curated, reconciled bronze, silver, and gold datasets reduced manual reconciliation effort and improved confidence in business metrics.
Are disconnected data sources driving up your analytics costs?
Unify reporting and reduce repeat data scans with an AWS data platform.
Key Takeaways:
- Match replication to table support – Use Zero-ETL for supported tables and DMS where additional replication coverage is needed.
- Track completed loads – DynamoDB tracking helps reruns identify completed work and avoid loading it again.
- Reduce repeat scans – The reported BigQuery savings came from incremental processing rather than repeatedly scanning historical data.
Frequently Asked Questions
Repeated full-table scans were increasing source-side billing. Glue bookmarks and incremental exports reduced repeated reads while BigQuery remained a source for the AWS platform. The case study reports an 80-85% reduction in BigQuery costs.
The platform needed consistent reporting across three disconnected sources. Bronze held raw data, silver standardised and cleaned it, and gold added business context for BI consumption. This gave reporting teams curated datasets rather than inconsistent raw feeds.
PostgreSQL tables had different replication support constraints. Zero-ETL handled supported tables, while AWS DMS covered tables outside that support. The combination brought the required source data into Redshift.
Pipeline reruns needed a record of work already completed. Amazon DynamoDB tracked exported tables and loaded partitions so orchestration could skip completed work. This supported idempotent processing and helped prevent duplicate loads.
