Re:Build your own “data lake” for reporting purposes
by 0xbadcafebee on Sunday March 14, 2021 @09:49PM
(#26461730)
> When the team was small and had to grow fast, no one in the tech team took the time to build, or even think of, a data processing platform.
Good! This is much better than building something well before you need it. So when do you know you'll need it? By having each team track trends in metrics for their services/work and predicting when limits will be exceeded. (This used to be mandatory back when we had to buy gear a year or two ahead) This catches persistently increasing data sizes as well as other issues. Each team should track their metrics, write a description for what its effect means for the whole product, and forward them to a product owner (along with estimates of when limits will be exceeded).
> do try to set up some streaming replication on a dedicated reporting database server for quasi-live data (or set up automated regular dump & restore process if you don't need live data)
Both of these are potentially hazardous to performance, security, regulatory requirements, customer contract requirements, etc. Replication can literally halt your production database, as can ETL, and where it goes and how it's managed is just as important as for the production DB. So look at your particular use case and design the solution just as carefully as if you were giving direct access to production.
As for the structure of it all, once you start adding outside data, you'll find it's easier to architect your solution to have multiple tiers of extracted data in different pipelines, and to expose them each to analysis independently. You can make all of them eventually land in a dedicated database, but allowing individual analysis allows you to later build purpose-fit solutions for just a subset of the data without having to constantly mutate one "end state" database (which may become vastly more complex than your production database).
Good! This is much better than building something well before you need it. So when do you know you'll need it? By having each team track trends in metrics for their services/work and predicting when limits will be exceeded. (This used to be mandatory back when we had to buy gear a year or two ahead) This catches persistently increasing data sizes as well as other issues. Each team should track their metrics, write a description for what its effect means for the whole product, and forward them to a product owner (along with estimates of when limits will be exceeded).
> do try to set up some streaming replication on a dedicated reporting database server for quasi-live data (or set up automated regular dump & restore process if you don't need live data)
Both of these are potentially hazardous to performance, security, regulatory requirements, customer contract requirements, etc. Replication can literally halt your production database, as can ETL, and where it goes and how it's managed is just as important as for the production DB. So look at your particular use case and design the solution just as carefully as if you were giving direct access to production.
As for the structure of it all, once you start adding outside data, you'll find it's easier to architect your solution to have multiple tiers of extracted data in different pipelines, and to expose them each to analysis independently. You can make all of them eventually land in a dedicated database, but allowing individual analysis allows you to later build purpose-fit solutions for just a subset of the data without having to constantly mutate one "end state" database (which may become vastly more complex than your production database).
Btw, the name for this kind of work is called Business Intelligence (https://en.m.wikipedia.org/wiki/Business_intelligence)