3 ms·

What I personally like about SQL here is that you can start to think about your transformations in a purer "relational algebra" sense. You're able to declare th

by d_watt 4y ago

What I personally like about SQL here is that you can start to think about your transformations in a purer "relational algebra" sense. You're able to declare the expected outcomes, and let the engine figure out the best way to handle that, as opposed to actually having to figure out how to join, aggregate, window, etc. SQL transformations shine in environments where regular batching in short intervals is acceptable. Materialize and Flink are both super interesting for overcoming the batching shortcoming into realtime materialization via SQL. Regarding the concerns about tests, it's true it's harder to do unit testing of SQL, but for me the tradeoff is it feels like there's a whole class of bug that's eliminated by focusing on the relational algebra. If you can avoid the category of work of defining the procedures to transform the data, and only define the expected outcome, things can be much simpler.

jaggederest 4y ago
In my experience, testing SQL is just like any other language. You define input data, execute the script under test, and then check the output (and possibly the state of the system) to confirm. The issue is more pronounced when you start doing DDL on the fly, but as long as you generally confine yourself to extracting data in a specific format, it's not too hard. I've done it entirely in sql - you can have a "test data" table and compare it to a "results" table that gives you an oracle of truth*. The best is when you have rollups or other basic math that needs to be tested, so you can do parallel calculations (in another language or by hand) to ensure that the math is right. The worst is when you have small format changes that alter e.g. the order of the output - then you're left with either making your tests order-invariant or twiddling them for every ORDER parameter, which can be frustrating. *Truth is only as good as your ability to input results, of course.

by d_watt 4y ago

jaggederest 4y ago

In my experience, testing SQL is just like any other language. You define input data, execute the script under test, and then check the output (and possibly the state of the system) to confirm. The issue is more pronounced when you start doing DDL on the fly, but as long as you generally confine yourself to extracting data in a specific format, it's not too hard. I've done it entirely in sql - you can have a "test data" table and compare it to a "results" table that gives you an oracle of truth*. The best is when you have rollups or other basic math that needs to be tested, so you can do parallel calculations (in another language or by hand) to ensure that the math is right. The worst is when you have small format changes that alter e.g. the order of the output - then you're left with either making your tests order-invariant or twiddling them for every ORDER parameter, which can be frustrating. *Truth is only as good as your ability to input results, of course.