
Case Study
Built an immutable data storage solution to simplify reporting and third-party integration for Reliance Steel & Aluminum Co.
About the Client
Reliance Steel & Aluminum Co. is North America’s largest metals service center company, offering 100,000+ products and value-added processing and serving over 125,000 customers primarily by providing metals processing, inventory management services, and quick delivery.
Challenges
- On-prem and Legacy data model is modifying/overwriting record data all the time, as a result, customer unable to have a single source of truth
- Customer required an immutable data storage/system where they can run Adhoc querying, reporting, and third-party integration
- Need for real-time transactions to data warehouses and low-latency data transfer to operational and analytics users with low production impact
- Batch loads and periodic reloads with the latest data take time and often consume significant processing power on the source system
Solution Highlights
- AWS Lambda is used to handle and process the core data flow and thereby helps in Capturing Data Changes (CDC) in near real-time and applying business rules, and storing it into Aurora RDS (PostgreSQL) for further analysis by the data teams
- AWS services such as AWS Lambda, AWS Secrets Manager, Cloud Watch Rule, Aurora RDS (PostgreSQL), and SNS to finely integrate the requisite technical components
- Rollback Procedures are set in place using AWS Cloud Formation, and in case of failures, rollback will happen, and the reason for failures can be checked
- AWS Lambda and SNS helps in detecting and recovering the failed executions, wherein Lambda functions verify the incoming data records to detect failures
Data Processing & Migration
- AWS Lambda handle and process the core data flows
- CloudWatch rule triggers the Lambda process every 5 mins
- Lambda connects to on-prem SQL Server and extracts CDC data
- Lambda in-turn applies business rules within the extracted CDC data and stores the processed records to Amazon Aurora tables
- EMR Spark-based framework developed to run a distributed application environment
- An automated CI-CD pipeline was created
- Agilisium follows the AWS best-practice where we leverage CloudFormation to deploy the code to various environments (dev, test, and prod) through automation.
Monitoring & Logging
- To monitor the status of the ongoing replication tasks with Amazon CloudWatch
- Configure Amazon Simple Notification Service (Amazon SNS) to notify you of errors in the CloudWatch logs for the task
- Network throughput, client connections, I/O for reading, writing, or metadata operations
- IAM best practices and principles are followed
- The Least privileged access is provided
- Unique non-root credentials are provided
- Programmatic access for API calls
- Security groups are defined to restrict traffic
- All Data stores are in private subnet
AWS services used:
- Amazon Simple Storage Service
- AWS Lambda
- Amazon Aurora
- AWS CodeCommit
- AWS CodePipeline
- AWS CloudTrail
- Amazon CloudWatch
- Amazon SNS
- AWS Identity & Access Management
Results and Benefits
- Near real time insights to data teams and dashboards
- Data Propagation & Synchronization
- Better Data Quality- Capture of real time changes to data record and meta data management
- Improved Scalability and Performance of the system
- Overall TCO is reduced