Tohands: A Unified Redshift Data Platform for Retail Transaction Analytics

Executive Summary:

Tohands had data in PostgreSQL, Mixpanel and BigQuery, but no single place to analyse it. Repeated BigQuery scans drove up costs. Rapyder consolidated the sources in Amazon Redshift through its AWS data and AI services, using Zero-ETL, DMS, Glue and tracked incremental loads. BigQuery costs fell 80–85%, pipeline failures fell 25–30%, and data freshness lag fell 20–30%.

About Customer:

Tohands is a technology-driven retail solutions provider serving small and medium businesses through smart calculators and mobile applications used for billing, transactions, inventory and daily operations. The platform generates high volumes of transactional and usage data across devices and applications, and the business depends on consolidated analytics across device usage, transactions and operational workflows to track performance and make decisions.

Problem Statement:

Tohands could not query its PostgreSQL, Mixpanel and BigQuery data together, making reliable reporting difficult. Repeated BigQuery scans increased costs, while inconsistent business logic and manual ingestion created reconciliation work. Without curated reporting tables or reliable load tracking, the team faced duplicate-processing risks and data freshness lag.

Industry:

Industries, Retail / E-commerce

Offering:

Offerings, Data Analytics

Business Challenges:

1. Three disconnected systems: Transactional data, product event data, and analytics datasets lived in three separate systems: PostgreSQL, Mixpanel, and BigQuery. There was no single place to query them together.
2. BigQuery billing: Repeated full scans of BigQuery tables were driving substantial and avoidable source-side billing.
3. Inconsistent logic: Business logic was applied inconsistently across datasets, so the same metric could differ depending on where it was calculated.
4. Manual ingestion: Ingestion and transformation were not automated end to end, requiring manual intervention and reconciliation.
5. No curated tables: BI and reporting tools had no curated, analytics-ready tables to build on.
6. Duplicate processing: Duplicate processing was a live risk without a reliable way to track what had already been loaded.
7. Freshness lag: Data freshness lagged behind what operational reporting needed.

Solution Implemented:

1. Zero-ETL integration: Integrated PostgreSQL source systems with Amazon Redshift using AWS Zero-ETL for supported tables, enabling near real-time replication into Redshift bronze schemas.
2. DMS coverage: Used AWS DMS for tables Zero-ETL could not support, giving complete coverage across a constraint-heavy source.
3. Mixpanel export: Configured Mixpanel to export daily into Amazon S3, with AWS Glue Crawlers discovering schemas and maintaining metadata in the Glue Data Catalog.
4. Spectrum access: Enabled Redshift Spectrum external schemas so Mixpanel data can be queried directly from S3 without consuming Redshift storage.
5. BigQuery export: Exported BigQuery datasets daily into Amazon S3 using AWS Glue jobs orchestrated by AWS Step Functions and EventBridge scheduling.
6. Incremental reads: Enabled Glue bookmarks and incremental exports so only newly available tables are read, eliminating repeated full scans of BigQuery.
7. Partition loading: Loaded newly available partitions into Redshift with AWS Lambda using COPY commands.
8. Idempotent tracking: Tracked exported tables and loaded partitions in Amazon DynamoDB to prevent duplicate processing and keep pipeline execution idempotent.
9. Redshift transformations: Implemented transformation logic in Redshift to standardise raw data into bronze, silver and gold layers, enriching transactional data with shipment, replacement, business context and usage metrics.
10. Layer refresh: Scheduled Redshift stored procedures to refresh curated layers once ingestion completes.
11. Pipeline monitoring: Enabled centralised monitoring and logging through AWS-native services to track pipeline execution, replication health and transformation status.

Technology Stack:

• AWS Lambda
• Amazon S3
• Amazon Redshift
• AWS Glue
• AWS DMS
• Zero-ETL Integration
• Amazon EventBridge
• Amazon DynamoDB
• AWS Step Functions

Business Benefits:

1. 80–85% lower BigQuery costs – Glue bookmarks and incremental exports removed repeated full scans of BigQuery tables, cutting source-side billing sharply.
2. One analytics platform – PostgreSQL, Mixpanel and BigQuery data now sit in a single Redshift warehouse for unified reporting.
3. 20–30% less data freshness lag – Near real-time replication from PostgreSQL and optimised batch pipelines cut data freshness lag for operational reporting.
4. 25–30% fewer pipeline failures – Step Functions orchestration and idempotent loads reduced failures and reprocessing, improving operational stability.
5. Reliable incremental processing – DynamoDB-based tracking of exported tables and loaded partitions helped prevent duplicate loads through tracked, idempotent processing.
6. Analytics-ready data layers – Bronze, silver and gold models gave BI teams standardised, reusable datasets instead of raw tables.
7. Less manual reconciliation – Curated datasets improved trust in the numbers and cut the effort spent checking them.
8. Managed and scalable – AWS-managed services reduced operational overhead and left headroom for data growth.

Key Takeaways:

• Hybrid replication – Zero-ETL with DMS fallback provided both near real-time replication and full table coverage.
• Incremental exports – Incremental exports with bookmarks cut the cost of repeated BigQuery scans.
• Traceable reporting layers – Bronze, silver and gold layers kept curated reporting data traceable back to the raw sources.