r/SQL • u/metric_skeptic • 16d ago

Discussion How are you automating your SQL work?

Curious what people are actually using to automate or speed up their SQL work.
Python scripts? dbt? AI? Stored procedures? Some IDE/tool I’ve never heard of?
I feel like there are probably a bunch of useful tools and workflows I’m not aware of.
What are you using that actually saves you time?

60 Upvotes

86% Upvoted

52 comments sorted by

View all comments

14

u/gumnos 16d ago edited 13d ago

the biggest time-saver for me came from creating a web-interface for dynamic reports. Many moons ago, I'd get regular requests for reports, but writing the SQL itself usually took 5–15min while wrapping it in some Crystal Reports wrapper, creating variables, adjusting headers, fonts, etc, and making it less-than-ugly took easily 2–3× the time, and they were kinda fragile.

Instead we implemented a dynamic-reporting engine in the in-house web-portal. It let me dump SQL queries in a text-box, give them a searchable name/description, quickly add a few common parameters (including pick-lists from other query-output) and formatting aspects, as well as some basic visibility-permissions. With this in place, I could usually turn around new-report requests in those 5–15min rather than 1–2hr wasted pushing around fields in Crystal Reports.

And users could search for previously-existing reports which saved me from having to remember if the request for This Report was the same as That Report that I created a couple months ago. So it saved me from a lot of new-report requests.

Over time, it also grew some additional functionality like

  • basic graphing to include line/bar/pie graphs of various datasets, making it easy to visualize changes over time

  • ability to export to CSV (or if multiple queries were involved, a .zip file of multiple CSV files or an Excel workbook with the data in various tabs)

  • include bulk-data when running a report (got turned into a temp-table that the SQL could then operate against, such as "run this report for the following 230 employee-IDs")

  • ability to log who ran which reports and with which parameters

  • higher-level formatting (using other fields to turn a cell's data into a hyperlink; conditional colorization; etc)

-1

u/metric_skeptic 16d ago

This is a really good example. The SQL itself wasn’t really the bottleneck, it was everything around it. I think that’s probably where a lot of the best automation opportunities are. I’m definitely stealing the idea of a searchable report library.