ARCHIVES

Effective management and amalgamation of the multi-source financial information so as to provide individualized investment and wealth management services require optimization of ETL (extract, transform, load) pipeline in customer-centric investment solution will be provided as the general methodology in this paper. The project contains data conversion of the former on-premises Oracle systems into the snowflake based data warehouse with the type of data they held was the contributions to the portfolios, product relations, customer relations and information about the participants. There is systematization of the process and includes data modelling, pipeline design and performance optimization. To make sure that the data models are aligned to the business objectives as well as the data governance sets, logical, conceptual and physical data models were developed. Informatica IICS and Powercenter were adopted as effective in extracting, processing and loading of different sources like SQL, Oracle and Azure Databrinks. AWS Lambda control-M and cloud-native services, S3, KMS, SQS and SNS were introduced to automate the processes to reduce the number of processes completed manually and provide scale execution. According to the quantitative statistics, the effectiveness of processing increased significantly: the total ETL processing time was decreased by 42 percent and the data rate increased by 38 percent, which makes the reporting and analysis almost real-time. By applying DAX as KPIs of power BI dashboards, the action could be taken on the customer portfolios based on the specific investment campaigning and tailored financial recommendation. The paper sheds light on the effectiveness, quality of data and customer value of business in customer-centric financial platforms with optimized ETL pipelines.