Hacker News new | past | comments | ask | show | jobs | submit login
Ask HN: How do you organize your analytics/BI SQL code?
3 points by curl-up on March 16, 2023 | hide | past | favorite | 1 comment
I'm primarily asking for the following scenario, but I'm interested in other relevant situations as well.

Imagine you are an analyst in a medium-sized company. Data analytics / Business insights team you're part of has somewhere between 5 and 20 people. Daily, you get questions from other departments for custom reports, dashboards and quick answers. Some of it is part of a bigger project that takes months and results in comprehensive dashboards, some of it is ad-hoc and will never be needed again, but you usually don't know if some simple quick question will turn into a bigger thing. However, in all of these cases, most of the actual "work" is about writing, rewriting, modifying and maintaining SQL code.

My question is: how do you manage all that code? Is there a git repo where you follow some nice structure you defined? Do you build everything in DBT? Do you use fancy tools like Metabase? Do you store it in your data warehouse itself (e.g. as a stored procedure or view)? Do you just write the code and then throw it out because, the next time you need something like this, database would have changed anyway (or a completely new one will become the main source of data), so it makes no sense to even store it?

I'm intentionally not specifying the "stack" here, as I'd like to hear what actually works well, not what is just the least-bad option for a particular tech stack.

I'm especially happy to hear from people who have had bad experiences with whatever they chose to do, as I'm in the process of trying to solve this problem for my team and would really like to avoid as many mistakes as possible.




In my team we used a shared Excel file, with one sheet per customer, and roughly every row would be one query. We'd have some columns reserved for notes, example outputs and links to email threads etc. with more context. The main drawback is that some rows would start to get lengthy since we'd add more and more links, but overall worked quite well. Excel has some neat features, for example you can have a set of queries per customer that contain e.g. their office address, and then use a string replace formula to generate the same set of queries for another customer.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: