Data Warehousing

Industry: Banking and Financial Services

Problem Context

The client has various data sources as well as an MS SQL database on the On-Premise data center. They need to create a secure and efficient data warehouse and data marts to derive insights, generate dashboards and perform analytics.

Challenges

 Data migration from multiple sources

 Masking of financial PII data during migration

 Setting up a large number of ETL pipelines in a short time period

Technologies Used

AWS DMS

Amazon S3

Amazon Glue

AWS Glue Crawler & Catalog

Amazon Redshift

Amazon CloudWatch

AWS Step Functions

Amazon Athena

Amazon SNS

Amazon SES

AWS IAM

AWS KMS

AWS Secrets Manager

Creating secure and efficient data warehouse and data marts

Solution

S&M Tech Solutions is helping the client with the following solutions:

Data Migration and Ingestion:

 One time migration of existing data will be done from the on-premise MS SQL database to Redshift

 Integration of other data sources to provide the incremental data on a daily basis and process at a scheduled time in batch

Data Processing:

 Use cloud-native services and develop scripts for processing data from various sources and then loading them into the Data Warehouse

 Performing validations and benchmarking on the raw data obtained from various sources

Data Warehousing:

 Creation of a data warehouse and data marts on Amazon Redshift for the business requirements

Result

 Greater ease for the business users to maintain and manage data

 Scalable to store huge amount of data and the ETL jobs also accommodate the same

 High level of data security during transit and storage with secure management and governance