With over 9 billion messages sent each month, the client is a market leader in conversational texting with data coming in from various sources like MySQL, logfiles, S3, etc. The client develops numerous chatbots and wants data visualization for the chatbot to derive insights from the bot chat sessions.

• Algoscale created a data warehouse and launched a set of nodes, called an Amazon Redshift cluster. Our experts used Amazon Redshift Serverless to run and scale analytics without having to provision and manage data warehouse clusters.
• To import the bot data from S3 to Redshift, we created an automated data pipeline that runs every 1 min and copies the new s3 files to Redshift. And as the application stores the bot data on S3 on a daily basis, therefore, the command is run every day as well.
• A stored procedure was designed to load the data from S3 to Redshift.
• Further, a python script was created that runs on Kubernetes. The script creates manifest files and bookmarks in s3. Manifest file collected all URLs of S3 files which are to be loaded in redshift and bookmarks files save the current timestamp. 
• The script was employed to invoke the procedure which we have created in redshift and then the procedure will run all the queries.

• Our solution helped the client in getting useful insights on over 6 billion messages per month. We built the data warehouse utilizing the Kimball data warehouse model using star schema that provides for facts, dimensions tables, and materialized views.
• Regardless of the intricacy of the query or the volume of data, the data warehouse created by Algoscale performs queries over thousands of parallelized nodes to produce quick responses and allows for the simplification of raw data into a form that is easily comprehensible.
• Our solution enables the client to conduct and scale analyses in a matter of seconds without having to set up and maintain data warehouse infrastructure. Insights can now be acquired from data by simply loading and querying data in the data warehouse.