AuthBridge: A Redshift and QuickSight Analytics Environment for Identity Verification

Executive Summary:

AuthBridge’s reporting queries were competing with live identity-verification traffic on the same MySQL database. Rapyder separated the workloads with an AWS data and AI architecture, using Glue for ingestion, Redshift for the warehouse and QuickSight for dashboards and calculations. The result is a cleaner analytics path, faster dashboard access and less pressure on the transactional system.

About Customer:

AuthBridge provides identity management, onboarding and verification services. Its products use technologies such as facial recognition, liveness detection, data extraction and optical character recognition to verify identity documents and educational and professional credentials.

Problem Statement:

AuthBridge’s reporting and analytical queries ran on the same OLTP MySQL system that handled live identity-verification traffic. As data volumes grew, queries slowed, and heavy analytical workloads contributed to downtime, transaction failures and uncontrolled database connections. Grafana also routed complex dashboard calculations back to the database, increasing the pressure on the transactional environment.

Industry:

Industries, IT- ITeS

Offering:

Offerings, Data Analytics

Business Challenges:

1. Analytics on OLTP: Analytics and report generation depended on the same OLTP MySQL system that also ran transactional workloads, so analytical queries competed directly with production traffic.
2. Slower queries: As data grew, the same analytical queries took progressively longer to run, degrading OLTP performance.
3. Production instability: Server downtimes, transaction failures and uncontrolled database connections resulted from running heavy analytical queries on the transactional system.
4. Dashboard limitations: Grafana, the data visualisation tool in use, could not handle complex calculations on dashboards and had to route them back to the database, adding further overhead.
5. No workload separation: There was no dedicated environment separating analytical workloads from transactional ones.

Solution Implemented:

1. OLAP environment: Built a dedicated Redshift OLAP environment so reporting queries no longer ran directly on the transactional MySQL system.
2. Scheduled ingestion: Used AWS Glue to ingest data from the OLTP MySQL database into Amazon Redshift on a scheduled basis, keeping the warehouse updated with the latest information.
3. Refresh frequency: Scheduled AWS Glue jobs at the required frequency, with warehouse freshness governed by those refresh runs.
4. Redshift warehouse: Established Amazon Redshift as the data warehouse, consolidating data from MySQL for efficient querying and analysis.
5. Query optimisation: Applied Redshift workload management and materialized views to enhance query performance and streamline analytical operations.
6. QuickSight BI layer: Connected Amazon QuickSight to Redshift as the BI layer, generating detailed reports and interactive dashboards.
7. Dashboard calculations: Used QuickSight’s built-in calculation capabilities to support complex reporting and reduce reliance on calculations against the transactional database.
8. Historical updates: Handled updates to older historical records through custom scripting within the ETL process.
9. Data reconciliation: Ran periodic data reconciliations to keep incremental loads accurate over time.

Technology Stack:

• AWS Glue
• Amazon Redshift
• Amazon QuickSight

Business Benefits:

1. Reduced transactional overhead – Moving analytics into a dedicated OLAP environment in Amazon Redshift took the analytical load off the transactional MySQL system.
2. Scalable as data grows – Redshift’s scalable architecture let the customer handle growing data volumes without compromising performance.
3. One system, two roles – Amazon Redshift served as both the data warehouse and the data source for visualisation, simplifying the architecture.
4. Faster dashboard performance – Materialized views significantly improved dashboard response times, giving faster access to insight from large datasets and heavy queries.
5. Stronger visualisation capability – Amazon QuickSight’s built-in features let the customer perform complex calculations directly in dashboards rather than routing them back to the database.

Key Takeaways:

• OLAP separation – Once analytics begins to slow transactional work, it needs its own OLAP environment.
• Materialized views – Materialized views improved dashboard response times without simply adding more compute.
• QuickSight calculations – Moving complex calculations into QuickSight reduced the recurring load on the database.