[fifilura]

Long reply for a workday, but a quick reaction from your first example (with a slight risk that I misunderstood your problem).

What you do here is to also wrap the main query into a CTE and then end everything with SELECT * from main_query.

Then you can easily change that last clause to do SELECT FROM query_two while you keep everything as is, even the CTE you called main_query.

[KronisLV]

That's indeed a useful trick, but wouldn't it be better to be able to execute pieces of SQL without altering the actual code?

For example, selecting the CTEs up to a certain point and using a particular button or keyboard shortcut within a development tool, much like we can already do with executing selection?

Of course, adding "clever" functionality like that might as well create some risks and inconsistencies, so i'm not entirely sure about that.

[fifilura]

Yes, this is only a quick trick and does not solve the general question about unit/integration testing that you are used to in other types of programming.

This is an entirely different subject and deserves a long discussion, and one which SQL is not ideal for.

As you said, breaking CTEs into separate views is the start, and then you can use a tool like getdbt.com to make your references into parameters. And then you also need to create the mock data (which you can do for example by writing it into csv:s)

From the role of an analyst I must say though, that once you have done all that work, the risk is that you forgot to worry about to the biggest risk here. What is actually inside that data you are querying? Maybe your biggest problem is not the logic of the query, but rather how dirty your input data is? Or for that matter, that you made completely incorrect assumptions on your input data, like that column X contains distinct values when it contains duplicates. That type of error wreaks havoc on your end result, with a big chance you'll never notice.

[KronisLV]

I have to agree with most of what you're saying. Another commenter also voiced concerns about how SQL is sometimes a bit difficult to work with because the underlying domains and concerns that are handled by it are also inherently complex in many ways.

In regards to the correctness of the data and the quality, there are at least constraints that you can put into the DB, but i've personally seen plenty of cases where that isn't even considered and is forgotten about, without even getting into the OTLT and EAV anti-patterns and the implications that they may have: https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-bi...

In every case where i've seen someone attempt to introduce polymorphic foreign keys, a lot of those consistency guarantees and control mechanisms have gone out the window, since you can't really have conditional constraints or complex logic in there either. Though thankfully not everyone has to deal with things like that in their projects.

One cannot overstate how important modelling your data is, to the point where schema-first is a strong and reliable approach most of the time.