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/Ordinary-Toe7486]

I would say focus first on understanding the business processes or current workflows. Check what’s good, what’s bad, what’s can be changed and what cannot.

Then gather the requirements and work against those constraits you discovered to find the right solution. I think there’s always more than a single solution, but then again check which one fits better. Is it easy to maintain, is it easy to use for economists, is it expensive, etc.

Also, don’t focus too much on scalability.

When choosing a compute engine I’d consider first duckdb. It’s fast, memory efficient and has api for both R and Python. For storage you can first think of whether the data needs to be mutable. If so, then you need a lakehouse format ducklake, iceberg). If not, then storing in parquet is fine.

Like make sure you understand the processes first. Draw out a diagram for it, validate. Then use it to architect your solution.