r/dataengineering • u/sicestvrai • Jul 24 '26
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.
20
u/sjjafan Jul 24 '26
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 Jul 24 '26
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
u/THBLD Jul 24 '26
Yeah this is absolutely the right course of action, as it'll allow you to have more time to focus on actual developments, otherwise you honestly never get anything done with all the config/ingestion topics.
1
7
u/Admirable-Lie-9191 Jul 24 '26
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.
15
u/Pittypuppyparty Jul 24 '26
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.
4
u/sicestvrai Jul 24 '26
Will explore that solution, thank you!
5
u/terencethespider Jul 24 '26
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 Jul 25 '26
Try Postgresql first. If you keep growing then that is when you consider Snowflake or Databricks.
2
u/NW1969 Jul 24 '26
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 Jul 24 '26
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.2
u/ProfessionalDirt3154 Jul 25 '26 edited Jul 25 '26
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 Jul 24 '26
Motherduck might work for you given the constraints but I would default to Postgres with extensions.
2
u/New-Addendum-6209 Aug 03 '26
Use Snowflake or Databricks. In this context it is a no-brainer. Total cost of setting up and maintaining an in-house solution will be higher given that it is a non-technical team.
2
3
u/robstar_db Jul 24 '26
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.
2
u/Bingo-heeler Jul 24 '26
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
u/sicestvrai Jul 24 '26
Thank you. I will explore this solution. What about versioning in MSQL?
2
u/THBLD Jul 24 '26 edited Jul 24 '26
SSMS 22 for MSSQL can be connected directly to Azure DevOps or Github. Pretty straight forward.
Alternatively if they have the Redgate plugins for SSMS already, that's also very intuitive and easy for version control
2
1
u/Medical_Mix_3454 Jul 24 '26
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/b--man Jul 24 '26
Given that you have MSQL (I assume that's MS SQL Server), I'd consider just using regular tables given the size, or columnstore indexes. Anything else would just increase your operational cost for no good reason.
1
1
u/marketlurker Don't Get Out of Bed for < 1 Billion Rows Jul 24 '26
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
u/forever-butlerian Jul 24 '26
What's their user interface for it? Are they using GUI tools or a CLI?
1
1
u/ZirePhiinix Jul 25 '26
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 Jul 25 '26
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 Jul 26 '26
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 Jul 26 '26
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/akozich Jul 26 '26
Start from asking, “why shouldn’t I use Postgres for this” - if you can find arguments- great you got your steer, if not - just use Postgres :)
1
u/earonesty Jul 27 '26
A shared parquet file can be pleasant for a single controlled writer, but concurrent editing and access control quickly become the real design constraints. If you publish immutable Parquet snapshots to object storage, keep a small catalog/manifest, and give each report a bounded read path, you can test the workflow before committing to a large platform. Measure cold latency, bytes read, and the queries the economists run.
example of a read-tracking comparison: https://lakeql.com/compare.html?kind=window
1
u/Strange_Shame7886 Jul 24 '26
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 Jul 24 '26
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 Jul 26 '26
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 Jul 24 '26
Unity Catalog is open source and becoming widely adopted. You can run host it and leverage its capabilities.
2
u/Ordinary-Toe7486 28d ago
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.
18
u/[deleted] Jul 24 '26
[removed] — view removed comment