If I had a nickel for every time I heard this request from a customer or management, I would have enough money for a Big Mac with fries and soda, even in this economy.
If you are in this position, here are two advice that will save your company millions of dollars in the long term.
1. Don't try to implement a real-time solution from the get-go; first, ask what your manager means with real-time. In very few limited cases, you would ever need real-time. You can pitch a lambda architecture and push the real-time part down the road. https://lnkd.in/gi-kFbwX
2. Use a proper Data Warehouse. By following best practices and designing a proper data model, you can achieve a cost-effective solution.
My stack choice for this specific question would be Dagster Labs + Databricks for the Extraction and Load part from the ELT and then for Transformation Tobiko's SQLMesh. Finally, the data is useless unless presented to users. I'm biased, but I would use Cube for the Semantic Layer and let the analyst choose their preferred BI tool.
I have never used Dagster and SQLMesh together. Do you know if they work well together? It looks like Reuven Gonzales made an integration (WIP). https://lnkd.in/gXJz6HxN