We got a lot of value using SQL unit tests on very complex queries with lots of edge cases. There was one which involved breaking up events into session (a session is a single uninterrupted 'seating' using the product, an interruption being 30 mins gap or more). Re: edge cases, we'll check sessions are broken up as expected when the user changes, when the browser changes, when it goes over midnight UTC etc.

The table was ~250mm rows per day. The alternative would be doing it in memory; Python unit tests do feel a bit more natural but for a table that size we decided it was best to do it on disk / let the DBMS deal with it. Perhaps Spark is an idea but then there's the trade-off of customers losing context.

It also helped us 'refactor with confidence' - we can happily change the query to incorporate new use cases while knowing the core logic is still sound.