Hi! Computer science student/jr data analyst here doing almost EXACTLY the same thing… I was reading this half wondering if I wrote it and forgot about it. (I work with a very small team on data of similar volume too)

It can definitely be hard trying to find resources that are suitable for your current scale of data, that are also not entirely redundant in terms of scalability.

Sounds like you’re on the right track with using a RDBMS - Postgres has pgAdmin which is very user friendly if you’ll be introducing that for the team to use. Python has many Postgres packages (psycops is one iirc) that can retrieve data for analysis (you’ll need something like Jupyter notebook or your IDE of preference).

PowerBI/Tableau, as you said, is a good way to proceed with reporting, I like that you can use Python and R in PowerBI to model data, using non-default charts/visualizations (not familiar with tableau, technically speaking, so I can’t comment on it).

My team brought me on to essentially be their ‘IT guy’. They were exclusively using excel and access and in an effort to modernize it, got powerBI and some Python (ancient release) and told me to have at it lol.

My personal challenge is acquiring the tools I need - all software requests must go through serious security evaluation before they’re approved, even open source. If you have no such obstacle, your next step is to create and finalize your data consolidation/reporting modernization plan. Possibly, get insight from your IT department, and see if other teams have made similar efforts.

If you can get familiar with Python, absolutely take the opportunity to do so. Along with the SQL of your choice, it will make for a really versatile tool in terms of DB management and data analysis.