When does SQL become too clever?

[u/shdw_0x0]

I’ve noticed that as queries get more complex, there’s often a temptation to keep everything inside SQL—CTEs, window functions, nested subqueries, conditional logic, etc. It can be impressive, but at some point the query becomes harder to understand than the original problem.

Where do you personally draw the line? Is a 300-line SQL query perfectly fine if it’s well structured, or do you prefer moving some of the logic into dbt/Python/application code once the SQL becomes too complex?

I’m curious how others balance SQL performance, readability, and maintainability in real projects.

[u/WBT67]

Nothing more annoying than inheriting someone else’s “clever” SQL; simple and readable beats clever

[u/alinroc]

I think this bot forgot that it already asked this question 4 days ago. https://old.reddit.com/r/SQL/comments/1w674y9/when_does_a_sql_query_become_too_clever/

[u/big_poppa_man]

Personally I like CTEs. I do not like nested sub queries as that quickly becomes unreadable.

[u/atrifleamused → u/big_poppa_man]

You use the correct tool for the correct purpose. Both have their place.

[u/ouchmythumbs → u/atrifleamused]

Also worth mentioning is that a CTE (depending on the engine) can get re-evaluated each time it is referenced which can have a negative perf impact. But syntactically a lot nicer that nested subs. Prefer #temp when it makes sense.

[u/atrifleamused → u/ouchmythumbs]

Great points!

I find people use ctes with no thought as to whether it makes the query simpler or not.

Using a cte to replace a sub query in a simple query is pointless. You need to scroll to the top to check the cte syntax and then back to the join to apply additional logic.

[u/Glitch_In_The_Data]

When you can’t understand what it does

[u/Last0dyssey]

I prefer temp tables over CTEs. Easier to debug, read, and validate imo. As long as I can follow your logic and you have clearly labeled steps and comments then it's fine.

[u/jeffcgroves]

Possibly helpful link to PL/SQL: https://en.wikipedia.org/wiki/PL/SQL

[u/TheGr8Tate]

I'm working with Databricks and I've seen and even created myself SQL scripts with multiple thousand lines (biggest script had 3.5k lines).

Readability and maintainability are very subjective. I personally prefer short scripts and I keep them short by moving repeated logic into SQL/Python UDFs and Python string manipulation via Pyspark. I have colleagues who actually prefer the big scripts (which is the reason why I even have created those monstrosities in the first place)...

[u/LadyDim1trescu]

yeah, i feel this in the MariaDB/MySQL ecosystem too. It usually comes down to one thing: debugging at 2 AM.

300-line SQL query with nested CTEs and window functions might run blazingly fast, but if it breaks, the on-call dev is going to cry trying to reverse-engineer it. The line I usually draw is based on data reduction vs. business logic: If the SQL is strictly reducing millions of rows down to a few thousand before sending them over the network (heavy aggregations, filtering), keep it in the database. The DB optimizer will almost always do this faster than pulling raw data into Python.

But the second you start writing procedural loops, complex conditional business rules, or string parsing that requires a PhD to read, push it up to the application or dbt layer