[halfcat]

> ”I have manually aggregated data, messy data, in awk, perl, python... it is much, much worse.”

Yes, but with Python/etc you can at least do the same logic in a plain loop, which is much slower but serves as a more reliable reference of what the data is supposed to be, which can be used to validate the functionality of SQL output.

Is there an equivalent in SQL of this “slow and dumb” approach for validating? Like, I’m not sure if a lateral join is essentially doing the same thing under the hood.

[rawgabbit]

Most databases have the concept of temporary tables that will automatically disappear when your session ends. For troubleshooting, I would breakdown each step and save it in a temp table. Validate it. Then use it as the input for the next step. Etc.