Hi everyone! I'm not sure this is the channel for this but I'm wondering if anyone has previous experience dealing with this scenario:
In the company I'm working we have this request:
"The Product team wants to improve the insights we are providing our clients (external users), creating new dashboards in one of our products (a web app that the client use to create campaigns)."
We already have a ELT solution:
• Prefect for orchestration
• BigQuery data warehouse
• Data transformations with DBT
We are getting the data from different third party tools as well as from our PostgreSQL database, and creating data marts to serve other teams that are using Superset.
For this scenario, having in mind we want our clients using our platform and that there are data that is only in our DW, I was thinking on create a Reverse ETL solution to ingest the data in our database again in order to create the dashboards getting the information always from the same source.
There are a few thing that I'm concern about this "solution":
• Is there any problem/anti-pattern on save transformed data in a database that is one of the data sources at the same time?
• Using this solution, should I use a tool like Census, MetricQL, Grouparoo, Hightouch etc to ingest the data in the database again?
• If I ingest the data creating a Prefect flow, which are the cons of this instead of use a Reverse ETL tool?
I'm a bit confused about the best approach I should use for this and whether my solution is the best way to move forward or not.
Any guidance/idea or experience implementing a solution to solve this kind of requirement will be more than welcome.