I am working in fintech industry, they recently needed a dash-boarding solution for executive , finance and other departments while they also want AI solution to keep track of valid disputes and fraud transactions, Base for these 2 systems is a data pipeline which gather data from 3 databases (transactional_logs, disputes, User_accounts).

There are 2 tables in transactional_logs db which we needed real-time, so I have created a data pipeline inserted Debezium CDC source connector and Kafka sink so that limited columns transferred into MySQL DB in real-time (records: around 1M), For other 3 tables in transactional_logs db I have used airflow for orchestration and pyspark to get all data (only necessary columns,records: around 1M) and overwrite in the given MYSQL table, 2 tables from disputes and other 2 tables from User_accounts I have created same airflow + spark pipeline to overwrite data every 2 hours in MYSQL.

Now, there is a trans_log table in transactional_logs db (Having around 23M+ Records) which stores information for all financial transactions, I have added debezium CDC connector to it and get data in pyspark via kafka topic, implemented some important transformations and join all other columns, 5 from transactional_logs DB, 2 from disputes and 2 from User_accounts DB and stores into MySQL database (tableName: Transformed_transactionallogs).

Following are my questions related to above pipeline.

  1. In totality I am getting around 150+ columns (after joining and transforming) in Transformed_transactionallogs, Does it effects the performance if I use it in analysis and querying complex SQL queries.

  2. Should I create star schema first ? if yes, then I need to join data again and again in analysis in spark, will it not be a performance issue then ?

  3. For Dash-boarding, I will need aggregated data in other MySql table, Will it not be as issue if I calculate around 50+ KPIS through spark, What will be the best approach for this calculation ? data should be in like flat file or having star schemas ?

  4. Same it goes with AI modeling, what will be the best approach to save data in mysql ?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.