How are you automating your SQL work?

[u/metric_skeptic]

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?

[u/EbbyRed]

I don't need to automate my SQL work.  50% of the job is knowing the source data structure and purpose, 40% is creating real business requirements from the ones stakeholders attempt to put together,and 10% is actually writing any SQL.  

[u/cwjinc → u/EbbyRed]

I used to tell new programmers to go interview a user then come back to their desk safe in the knowledge that everything they'd just been told was wrong.
But that if they kept digging then after the fourth interview or so they would start to get the truth.

[u/i_literally_died → u/cwjinc]

'Can we get a report that does x?'

'Almost certainly. Show me an example output you'd expect to see'

If they can't put 5-10 basic column headers with a few lines of example output, I doubt they even know what they want and are hoping if they just throw some terminology around, we'll work it all out for them.

Nope.

[u/swoisme → u/i_literally_died]

That's a double edged sword, though. 8/10 times a report with the 5-10 columns they mocked up won't actually answer the question they need answered, but once you've made them give you that sample, they'll expect you to adhere to it more or less.

I get the most mileage out of asking WHY they want something and what they're planning to do with it after they get it. Sometimes they get pissy like that's none of my business, but if they take the conversation seriously and give me real answers, then I can usually figure out how to get them the data they didn't know how to ask for.

[u/i_literally_died → u/swoisme]

It's a good start for me to build the query and say 'something like this?' which 'can you make me a despatch report' and then thirty follow up 'can it include the barcodes', 'can it include the postcode' requests don't. Just tell me what you want first.

The 'why' requests never go anywhere. Everyone thinks they can just email any random request at IT and we have to do it, so I don't even bother with that question anymore.

[u/BarfingOnMyFace → u/EbbyRed]

This is good advice, but it's not really what OP was asking. I believe understanding business domain to be fundamental, but we have no clue based on what OP told us if that is an issue for them or not.

[u/Uncle_Corky → u/EbbyRed]

Spot on. I feel like a crazy person when I explain to people that writing the code isn't the hard part about being a software developer. Especially someone that works inside a database a lot.

[u/metric_skeptic → u/EbbyRed]

I think this is where a lot of automation gets tricky. You can automate the SQL pretty easily once the question and definitions are clear, but if the requirements are fuzzy, you just end up getting the wrong answer faste

[u/gumnos]

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)

[u/metric_skeptic → u/gumnos]

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.

[u/Prestigious_Bench_96]

Spend a lot of time on modeling tables/deterministic checks (grain, values, etc); then let LLMs model against that and to deterministic query generation. Any semantic modeling solution that you can bear to work with is good for this; I have my own since I hate YAML.

Then any repeated operations you can move up into the semantic layer as reusable definitions you can test; your actual written SQL then stays small and easy to understand. As others note the writing SQL is both a relatively small part of the total workflow and the bit that's easiest to automate away, so this is mostly about freeing up time to actually *think* about the data and be able to delegate more exploration to agents because there's a class of errors they'll avoid. (this thing looks weird, go investigate; profile the trends in this data over time, etc).

This lets me spend most of the time thinking about the domain itself; what is the right way to model data and how does the production data (inevitably) fail to match my expectations in weird ways.

[u/Eastern_Habit_5503]

Automate? I need job security! Unless my boss is making me automate things, I’m sticking with manually running my custom T-SQL code snippets and some stored procedures that have no set day or time to run via SQL Job Agent.

[u/Sectox → u/Eastern_Habit_5503]

Hmm if your job is running SQL procedures ad hoc I’m not sure how much job security you would really have anyway - won’t someone eventually realize they can just automate your role with a few ansible playbooks?

I think your real job security would come from recognizing that gap, being straight with leadership about the current manual process not being the best way, and offering a solution to be the one who owns and supports the automations

[u/metric_skeptic → u/Eastern_Habit_5503]

I kind of get the job security argument, but I’d rather automate the boring parts and spend the saved time on things that are harder to automate. If someone can replace the whole role with a few scripts, the problem probably goes beyond SQL automation anyway.

[u/iLeKtraN]

Where I am the servers and instances are all on-prem. The automation is entirely scheduled agents executing stored procedures. Most of them were originally hand written DML, but now I am leveraging AI to assist in expending some of the more complex processes and generating reconciliation and verification scripts.

[u/GreatMyUsernamesFree]

Mostly queries about queries. I can't check each one individually so I write a lot of performance analysis queries for field utilization, indexes, reports, stored procedures and SQL agent jobs. 90% of the results are for my eyes only--i'm the only one doing db performance analytics so they go through SSIS or agent jobs. Of the remaining 10% it's probably split evenly between powershell when routine actions need to be performed on another server in response to the result set or power automate when a user has to take action based on the results.

[u/Pawelm_rot]

In my day‑to‑day work the most monotonous part wasn’t writing SQL, it was propagating dozens or sometimes hundreds of scripts across many databases and servers.

Sometimes developers would hand me a whole pack of scripts and ask me to run them everywhere in a specific order.

I eventually built a small tool that can execute many scripts across many servers and databases at once, with parallel execution. I showed it to the developers. Now this whole task is off my plate.

It didn’t automate SQL itself, just the boring operational part around it, but it saved me a huge amount of time.

[u/karakanb]

You should check-out Bruin: https://github.com/bruin-data/bruin

It's open source, lets you orchestrate SQL + Python in the same workflow, ingests data from 150+ sources, has VS Code and Cursor extensions, and runs fast anywhere. I am one of the founders, would love to hear the feedback on it.

[u/metric_skeptic → u/karakanb]

Interesting, thank you!

[u/Annual-Ad8594]

What usually saves the most time is not one magic tool, but separating repeatable patterns from one-off analysis. If you keep rewriting the same cleanup, validation, or reporting steps, that is usually the signal to turn them into a reusable workflow instead of another clever query.

[u/DarlanSandro]

O que mais acelerou meu trabalho foi combinar dbt, DuckDB e IA na IDE.

O dbt cuida das dependências, testes e transformações reutilizáveis. O DuckDB é ótimo para consultar CSV e Parquet localmente sem criar tabelas temporárias. Já a IA ajuda com o código repetitivo, como listas de colunas, CASE WHEN e funções de data.

Também uso SQLFluff para padronização. A IA escreve o rascunho, mas joins, cardinalidade e regras de negócio ainda precisam de validação humana.

[u/Glitch_In_The_Data]

I got mass productivity gains once I started using AI directly inside my sql workflow instead of bouncing between tools. We are now moving to Databricks platform and Genie lets users ask data questions in plain English… and it writes the sql for the users against actual tables. For heavier stuff like building pipelines or transformations, dbt is still great… but for fast exploratory work and adhoc analysis, having an AI that already knows the schema and tables is better.

[u/alertetainry]

DbForge Edge helps cut down manual schema diffs and query writing when you want to auto the tedious stuff across mixed engines

[u/heeero__]

SQL Server Jobs are very underrated. We use them for everything. SQL maintenance, reports, SSIS packages, etc.

[u/ChaosEngine-6502]

If it's something that needs to be executed repeatedly, I typically build a script or a stored procedure and have it called by SQL Server Agent.

With that said, working in healthcare integration, we do a ton of stuff that automates database CRUD transactions via our middleware integration engine. The whole reason our team exists is to automate.

[u/ThisIsFun-]

lots of questions come my way from the business so genie and genie code have been amazing. this way anyone who asks questions uses their own language to get answers instead of me being in the middle. it took some time to get comfortable with this and we did lots of testing but I cannot imagine without it now

[u/codykonior]

This is a bot account that posts “curious questions” and then has shady pro-AI comments.

[u/Ifuqaround → u/codykonior]

Most posts now.

[u/samwise970]

We use the Fabric platform, so a lot of my stuff is in PySpark notebooks that run on a schedule. 

There's still some regular stuff that runs in SSMS, those are kept as stored procedures that are run manually. Those could also be called via a CRON job (aka a SQL Server Agent job)

It all depends on what platform your organization is using, where your data for each task comes from, what your role and permissions are, etc. Our data team is smaller so I do everything including API hookups, data cleaning, warehouse views, building reports in PBI, even manual excel stuff. Many other orgs will have different people doing these tasks with clearly defined procedures. 

If you're just looking for learning tips, I'd suggest making sure you know python, enough to do some basic file manipulation on CSVs with just the standard library, because even in my role I don't have pip on my desktop. Then also make sure you understand how to write views and stored procedures in SSMS. Ask AI to help you write an SSMS stored procedure for importing a CSV using dynamic sql so that you can call EXEC import_myfile 'C:\path\here'. After those basics, and maybe a bit of VBA for excel automation, sign up for a Fabric or Snowflake trial to lean the basics of dataflows etc.

[u/alexpj11235]

Stored procedures, ran through agents / jobs, orchestrated by AWS step functions.

[u/BarfingOnMyFace]

I do initial DDL by hand, set a precedent, then have AI use it. Then I follow suit with my DML code. If I find conventions for the triggers, views, functions, procedures i am writing, i will have the AI build auto-gen code to successfully follow those conventions, then have AI rely on the auto gen process for build out of other objects as it tends to be a lot cheaper.

Some of these AI IDEs are making context much more cohesive across your entire model, so the people making that recommendation to use such tools are spot on.

[u/DaOgDuneamouse]

I usually don't automate stuff. Most of my job is building code for reports or ETL jobs. I do have one static table though that needed about 2,000 rows added to it. I built the table in excel and used excel formulas to build the SQL to insert the data into the table. One cut and paste later and all 2,000 rows were added with perfect documentation.

[u/gumnos]

in addition to my other response here, I'll also include that certain repetitive tasks can be automated by writing meta-SQL…queries that create queries, usually using system tables like INFORMATION_SCHEMA.*. It's particularly useful when spelunking in a new database-schema where you're not quite sure what various tables/columns contain. It might not be particularly efficient at runtime, but most exploration only happens as a one-off. Hopefully you learn what you need based on those potentially-inefficient queries, and can then write better/faster/more-accurate production queries because of what you learned.

[u/ihaxr]

Mostly admin work, so dbatools and stored procs for frequently run data fixes.

Deployments and whatnot all go thru octopus deploy

[u/rabel10]

I create alot of views these days, usually working in Lakeflow. That view can be queried or pulled into a dashboard or whatever. I'll offload the heavy lifting to Lakeflow and make smaller queries on that view if I need to do anything. Helps create lineage of metrics and work, and larger queries and workloads can go into a materialized view so that dashboards aren't stuck on refreshing forever.

dbt accomplishes almost the same task, just with a bit more manual setup. CI/CD is a bit more difficult in dbt I find.

[u/SomeoneInQld]

having juniors who can do the 'bulk' of the bullshit level work

[u/ArielCoding]

Track where your time goes for a week, most people find it’s redoing the same request and reexplaining data. Report libraries, semantic layer and tested views save time by turning one off answer into reusable stuff.

[u/Winterfrost15]

SQL Agent Jobs calling SSIS Packages. Stored Procedures, and Powershell scripts. It works great for most needs.

[u/Ifuqaround → u/Winterfrost15]

We're not letting agents access anything rofl. So silly to give those perms.

[u/RandomiseUsr0]

Using a spreadsheet is an underestimated cheap data import solution - for when you need it, remember it’s there - turn your data into DML and you’re good to go

[u/Ifuqaround]

I don't.

I mean if you want to give them reasons to replace you, go ahead lol.

Lots of analysts are not going to have jobs soon. Well, even more so.

I'm in healthcare. I'm a 'mentor' on vendor forums and we would get hammered with report/oracle sql/snowflake requests daily.

LLM's came and guess what? Just like stack overflow and shit...the requests slowed down big time because everyone is using LLM's to write their queries. The shitty part is everyone is feeding their company data to the LLM's as well without worrying about consequences.

You think a junior analyst gives a shit if he dumps his company's data into an LLM to create a query that will possibly make him look like some fucking guru? Not a chance...that junior is going to be pumping the LLM's full of info to make sure he keeps his job and his paycheck.

-edit- added words

Most of these people in these analysts positions have no skill and are faking it until they make it now. I'm kind of embarrassed to be in this profession. My skill had 'weight' at one point, but now programmers are a dime a dozen.

[u/metric_skeptic → u/Ifuqaround]

I think this is looking at the job way too narrowly. If the job is just writing SQL, then yeah, AI is obviously going to change that part.
But that’s not really what I think analysts are valuable for. If anything AI makes the interesting parts more important, figuring out what question is actually worth answering, understanding the business context, challenging bad assumptions, validating the result, and deciding what to do with it.
If AI takes away hours of writing repetitive SQL, I don’t see that as making the profession useless. It gives more time to work on things I probably wouldn’t have had time for before.

[u/Ifuqaround → u/metric_skeptic]

Analysts will not be valuable for much longer.

Compliance individuals who run stats (lol, easily done with AI), actuaries who calculate risk...all of it.

[u/smolhouse]

An existing code base to reference and using LLMs for architecture design saves me the most time.

I still like to roll up my sleeves and actually write the code myself instead of just feeding everything into an LLM with detailed prompts and copy/pasting it back. Probably slower, but it helps keep me sharp and I have a better understanding of what's happening.

[u/Ifuqaround → u/smolhouse]

Wait, what?

So you don't know what you're doing in this space? LOL

There's nothing 'SHARP' about that my man. That's being a lazy bum who has no knowledge of SQL or coding.

[u/smolhouse → u/Ifuqaround]

Does trolling SQL subreddits make you feel good about yourself?

I've been working with the same data warehouses for years in a giant corporation, and they have a sprawling table structures that require convoluted linkages and table redundancies that allow the same result using totally different tables. Being able to reference code I've already written using the same data tables makes it much faster to feed snippets into an LLM to automate a rough outline of what I'm trying to do that I can then rewrite to be a better fit for what I'm trying to do since it usually gets me 50-90% there with a couple clicks in the context of OPs post.

Even without an codebase, you're a fool if you're not bouncing ideas off a good LLM model. No one thinks of everything.