4 ms·
To make progress on this problem, I wrote a light SQL preprocessor to support INCLUDE statements. It enables me to write code like this: WITH frequently_bo
by teej 9y ago
To make progress on this problem, I wrote a light SQL preprocessor to support INCLUDE statements. It enables me to write code like this:
WITH frequently_bought_together AS (
INCLUDE frequently_bought_together.sql
)
SELECT ...
This allows way better isolation and reuse of business logic than before. In Redshift, I combine this with an assert user-defined function to enable writing unit tests in raw SQL.
With all that together, I can trust analysts to update complex data assets and I can ask them to take any data issue investigation they've done and turn it into a re-usable test. Tests end up looking like:
CREATE TEMPORARY TABLE frequently_bought_together AS
INCLUDE frequently_bought_together.sql
;
SELECT f_assert(COUNT(*) > 0, 'Table is empty');
SELECT f_assert(COUNT(DISTINCT item_bought || item_recommended) = COUNT(*), 'Table is fanned out');
...
It has made a huge difference in how we write SQL.
- mistermann 9y agoIt warms my heart to see people in the wild doing smart things like this (after getting laughed at by moron managers who wouldn't authorize any good ideas like this for years).
- mshenfield 9y agoIt took me a second, but I can see why this would be handy. Whether you just use it in a with, or create a view from it, you have a single definition of the query that anyone can use. And you can test the INCLUDEd query. Neat.