3 ms·

Having occasionally wrangled with relativley complicated queries and jungles of views ans procedures, I have to ask that what would be a better way to get compl

Having occasionally wrangled with relativley complicated queries and jungles of views ans procedures, I have to ask that what would be a better way to get complicated stuff out from databases? I can think of two other options, which I personally both dislike: 1. Some fancy graphical ETL monster which takes ages to learn and where the learned skills are more or less untransferable to anywhere else. And which, at the end, makes same things as raw SQL but just in a more opaque way. 2. Build the complexity outside database. Yes, you avoid complex SQL, but you also lose quite a lot. SQL prohibits you from doing quite a many different stupid mistakes with your data. At least 99 times out of 100 databases have better performance making the complicated calculations than your home brewed solution outside the database (Yes, I agree. The exceptions can be notable...) Finally, reusability of the results is way easier if you keep as much calculations as possible in the database. But I can't say that I love hairy SQL, so if there are better ways, I would be keen to have a look.

msluyter 6y ago
Re: point 2. I'm not necessarily advocating it, but one advantage of doing work outside of SQL is testability. Esp. unit tests. It's still relatively difficult to test SQL, for a variety of reason. I'm working with dbt now, which offers some simple validation out of the box (verify such and such is non-null, for example). More complex tests, such as "verify that some rows/columns have some expected values," require writing a query (returning 0 rows on success, 1 or more on failure.) It works, but still rather clumsy because a) requires an actual database (ie, not really unit testing) and b) writing tests this way is pretty tedious and error prone compared to using a typical assertion framework a la python's unittest. I totally agree the options are all somewhat unpleasant. I've found dbt to be a nice step in the right direction.
- beefield 6y ago
  Yep, and another is that version control support of databases is still a bit limited. (There are some solutions, at least redgate is doing something for SQL server on this.) I have a workflow where I recreate all views and procedures in the beginning of the query batch from text files and that gives some ways to version control my views, but as you say, it is clumsy.

msluyter 6y ago

Re: point 2. I'm not necessarily advocating it, but one advantage of doing work outside of SQL is testability. Esp. unit tests. It's still relatively difficult to test SQL, for a variety of reason. I'm working with dbt now, which offers some simple validation out of the box (verify such and such is non-null, for example). More complex tests, such as "verify that some rows/columns have some expected values," require writing a query (returning 0 rows on success, 1 or more on failure.) It works, but still rather clumsy because a) requires an actual database (ie, not really unit testing) and b) writing tests this way is pretty tedious and error prone compared to using a typical assertion framework a la python's unittest. I totally agree the options are all somewhat unpleasant. I've found dbt to be a nice step in the right direction.

beefield 6y ago
Yep, and another is that version control support of databases is still a bit limited. (There are some solutions, at least redgate is doing something for SQL server on this.) I have a workflow where I recreate all views and procedures in the beginning of the query batch from text files and that gives some ways to version control my views, but as you say, it is clumsy.