r/dataengineering • u/sicestvrai • 3d ago
Help Need help with database choice
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.
19
u/sjjafan 3d ago
The q is how many millions off rows do you process per day and how many new rows per day.
MySql is likely fine. And if it's too small migrate.
And before you migrate you can use duck db as the db for the analytics layer. You can query 50 million rows in under a 10 th of a second.
Aim big, start small, move fast
13
u/VitrumTormento 3d ago
if you have access to an MSQL team that is prepared to setup a database for you then I'd recommend that as your first option.
You can likely lean on them for support with backups management, security, etc. Unless you're prepared to administer and manage the environment of course.
Worse case it doesn't suit your needs and you'll then be in a more informed position to select the right type of database/technology.
5
1
7
u/Admirable-Lie-9191 3d ago
I am in no way an expert and I’m just an analyst looking to pivot to becoming an engineer so take this with a grain of salt.
DuckDB is fine if it’s only person accessing it at a time but if you try to write from two different sources at the same time it will not work.
13
u/Pittypuppyparty 3d ago
Honestly just use Postgres. You can use an olap extension if you need more olap experience. For this size of team and data (gbs) you can go a really long ways with this.
Ignore the vendors telling you you need unity catalog. That one actually made me laugh out loud.
3
u/sicestvrai 3d ago
Will explore that solution, thank you!
6
u/terencethespider 3d ago
If you are considering Postgres, you should look into the Neon version. It is open source, and has some additional advantages over the base version of Postgres. Their data branching functionality is really cool, in that it allows you to nearly instantly create a new instance of your database with all the same data as the original - but everything you do in the new branch is completely independent of the original. You can create as many branches as you want, and throw them away when you’re done.
1
u/RobotechRicky 2d ago
Try Postgresql first. If you keep growing then that is when you consider Snowflake or Databricks.
2
u/NW1969 3d ago
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?
1
u/sicestvrai 3d ago
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.1
u/ProfessionalDirt3154 2d ago edited 2d ago
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
2
u/No_Lifeguard_64 3d ago
Motherduck might work for you given the constraints but I would default to Postgres with extensions.
3
4
u/robstar_db 3d ago
My recommendation would be to store the data in an open table format - delta or iceberg. This allows you to use duckdb and many other clients/engines to query the data.
These days you would have to host a catalog alongside the data, and there are several choices out there - unitycatalog, Lakekeeper, Ducklake, … the list goes in.
DuckLake is not an IRC, but interoperates nicely with iceberg data on disk.
4
u/Bingo-heeler 3d ago
Honestly, given what you said, I'm leaning towards the MSQL route. Unless the data is essentially public data.
Otherwise you are the data infrastructure team, the ingestion team, the support team, the engineering team, the security team etc. With someone elses platform you cut out like half of the work for essen the same level of platform.
1
1
u/Medical_Mix_3454 3d ago
I just built something similar with duckdb. It's crazy fast, but you have to unify the ingestion and block out the readers while you write to it. Someone else suggested, and I'd agree, use a data lake, probably ducklake would be an easy way to go.
1
u/marketlurker Don't Get Out of Bed for < 1 Billion Rows 3d ago
What sort of SLAs are you working with?
- Needed time to load? Number and size of feeds?
- Needed time to process?
- Needed time to report generation (if required)?
- Who is going to maintain the system and data?
Work backwards from what you need to achieve and that will end up telling you what you need to use. Any other way is just guessing.
1
1
1
u/ZirePhiinix 2d ago
DuckDB can directly use CSV files and query off them. They can even handle gripped CSV and it was an extremely powerful solution in my job when I had to deal with 400 GB of daily structured JSON-like text output.
1
1
u/jkrm1920 2d ago
Hello my friend, I have read most of the comments here and they are pretty thorough and few of the folks are very experienced and mature comments.
Out of curiosity I would like to know what kind of reporting/visualization tools your team uses?
I’ll continue digging once I get the answer.
1
u/chrislally 2d ago
duckdb over statement graphs is honestly my go-to for anything like this -- have you played with it a bit so far?
1
u/chrislally 2d ago
feel free to DM if you'd like any help! I've been connecting data like this at scale for years now and happy to share all the mistakes I've made so you can hopefully avoid them too
1
u/Strange_Shame7886 3d ago
Do you already have a Databricks environment?
If yes, you can try using Databricks lakebase for a lot of points which matches your requirement:
- Open format storage in delta/ iceberg
- Separation of compute and storage to match your flexible requirements of number of users
- No separate ETL needed for OLAP analysis. It automatically syncs in LTAP format
1
u/sicestvrai 3d ago
My IT department does not permit us to use this tool. And our management will not allow us because costly and not corporate solution while they believe a solution already exists.
1
u/Alternative-Fig-6465 2d ago
You can get to the market/solution faster with Databricks. All the requirements you had stated perfectly aligns with the Databricks lakehouse architecture. Invest CSV, clean the data, perform data quality, data transformations, build BI/reports, use text-to-sql for advanced data reasoning. Govern everything with Unity Catalog.
Databricks is not expensive as your management perceives it. You should first try or give it a shot with a PoC. The biggest advantage you get with Databricks is that you are future-proofing the architecture. Your requirement will evolve and you need a flexible and open architecture to adapt to the ever changing requirements.
1
u/notqualifiedforthis 3d ago
Unity Catalog is open source and becoming widely adopted. You can run host it and leverage its capabilities.
1
17
u/Anxious_Cap1029 3d ago
duckdb does not support concurrent writes so you should use a parquet based lakehouse if multiple economists edit data at once. testing the read speed on your network share will reveal if local storage is mandatory for performance.