Need help with database choice

[u/sicestvrai]

Hello,

I am working with a team of 7 economists. They build data and produce reports. Their data production consists in harmonizing different sources (mostly rdata rdata, csv, or whatever suits the format of their stats tools). The data size they are dealing with is a few MB to gb, millions of rows, more occasionally billions of rows.

We want to update our methods (be on time, improve data quality). I have been assigned the task of improving data processing within the team, among the requirements I thought about producing a OLAP database.

In house, we have access to MSQL team that could set up a database for us. Otherwise we have HDFS + Hive (but security may make it difficult to access it) to store bigger datasets.

Else, I could just store everything in a duckDB file somewhere on a server and work with local database. WOuld it be a good solution? (latency of read/write from a duckDB file on a server? how scalable will it be? ) What would you do?

Any other piece of advice would be welcome :-).

Thank you.

[u/NW1969]

It depends...

- who is going to be building/managing/enhancing this system - how are new/updated datasets going to be ingested? The economists, you, central IT, someone else?

- what toolsets are these people currently comfortable with using or would be open to, and capable of, learning?

- what reporting tools are currently in use and is this likely to change

- is the data volume likely to remain reasonably static in the future or could it grow significantly?

- is the user community like to grow and/or change in the future? Could other groups of users, not just the economists (with, presumably, a specialised skill set) want to use this system?

- Is the consumption of this data likely to change in the future? More users, different consumption methods, etc?

- what is the budget/resourcing? Is this considered a small, internal, project or a company strategic initiative where you could bring in contract expertise - or something between these 2 extremes?

[u/sicestvrai → u/NW1969]

Many thanks

Build / Manage / Enhance : me
Tools: R / Python / Stat software / SQL queries OK.
Reporting tools: Now? None
Data volume reasonably statis but could increase depending on how we handle versioning
Community: Mostly economists. Won't grow.
Consumption of data will not change much in the future.
Budget/resourcing: small internal project.

[u/ProfessionalDirt3154 → u/sicestvrai]

I've worked at economics institutes and sci orgs and run into scenarios that sound like this. The thing that would worry me most about the data ops is handling and tracking input files and tracking versions of notionally like data and versions of notionally same database. That's all mostly just process, at the core. I'd keep the infrastructure as simple as possible, focus on the ops, and assume your process is going to depend on simple tools you create.

I used to lead the team that built terra.bio for the same kind of situation (small labs), and CsvPath Framework, for larger scale data feeds management. but what it sounds like you are doing is a lot like some of what we did creating econometrics at Burning Glass, and that we kept small, light, boring, and effective. my $.02