I have a general question about design pattern for an enterprise application. I read a lot about it but its actually hard to find an answer because most you find it rater about how to design a data warehouse (DW) or about how to design pipeline, ETL and so on. But I'm stucking on a more top-layer question.
Setup
I have a IoT base business model basically like this:
- Project
- Location
- Customer
- [...]
- DataSource
- Device
-ValueType
- Data (not include in the relational database)
- Device
-ValueType
Its currently persisted by a relational database, where we use a lot of features like spatial types, hierarchyids, json types and more that are not available in a DW.
Effort taken
We have a very well-designed data pipeline to solve the ETL process. Works great and a lot of data is coming in.
For storing mass data I now started to design a data warehouse model (snowflake) to allow effective storing into a DW.
Looks currently like this:
Question
My actual question is more in general about where to put what data. I currently have the idea of keeping the relational database as it is and creating a separate DW with the schema I shared. From our business logic (API service) we need then to receive data from both storages to give valid results to a (for example) web application. From a data science perspective you can use the DW for doing ML, BI and other analytics stuff.
Question:
- Is this really common practice to have storages side-by-side like this?
- Do I need to stored device data also somehow inside the relational database?
- What is happening (on separated storage) to the loose related DIM tables inside the warehouse when an entity like location changed inside the relational database?
- Am I still a too big dummy and should I read more? (then ignore 1 -3) ;)
Thanks for reading!