Business Impacts
750+
tables migrated from Aurora to Redshift warehouse
50%
reduction in data refresh on dashboards
Customer Key Facts
- Country : United States
- Industry : Ecommerce
Problem Context
JM Bullion, an e-commerce retailer specializing in precious metals and a subsidiary of A-Mark Precious Metals, a Fortune 500 company, played a crucial role in both retail and wholesale markets. Operating in the direct-to-consumer sector, JM Bullion managed multiple e-commerce websites, each with its own database. This fragmented structure posed significant challenges in financial reporting, compliance, and leveraging market-driven pricing opportunities. To address these issues, JM Bullion aimed to build a dynamic and adaptable data warehouse pipeline. The primary objective of this pipeline was to facilitate data-driven decision-making and formulate customer-centric strategies.
Challenges
- Lack of data pipelines and reporting in Quicksight, restricting data driven decisions
- Unconsolidated data across different sources with columnar storage of data in json and text format
- Significant delay in refreshing when merging log tables with base tables inย QuickSight
Technologies Used
Amazon S3
Amazon Redshift
AWS Glue
Amazon Quicksight
AWS DMS
AWS Glue Crawler
Amazon SNS
AWS IAM
AWS KMS
AWS Secrets Manager
AWS Security Hub
AWS Inspector
Solution
Building Data Warehouse Pipeline For Enterprise Analytics
Quantiphi team meticulously designed and implemented a comprehensive data pipeline, encompassing ingestion, transformation, staging, and warehouse layers. Leveraging DMS, Quantiphi efficiently migrated data from the Aurora data source to S3 and applied essential transformations using Glue scripts. Following this, the data was transferred to the Staging layer (Redshift) and organized into Facts and Dimensions tables, as initially defined during the initiation phase. With the warehouse layer in place, a reporting layer was constructed, employing materialized views to support Quicksight dashboards, henceforth facilitating downstream analytics.
Results
- Single source of truth for data is created by consolidating the information across individual tables and merging the historical information
- Scalable and secure solution which ensures new data source (post company acquisition) can be added to the warehouse
- Consolidated dashboards created with governace enabled for materilized views to ensure singularity of reporting data tables
- Enabled business teams to publish their segregated business insights, allowing them to generate their own reports.
- Enabled QBR/MBR Leadership reporting
