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.

It 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.

It 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).

With SQL Server you can create TVFs (table-valued functions). So long as they are 'inline' (lacking BEGIN and END) they will be inlined into any query that uses them. If your SQL fu is up to scratch they are extremely powerful - e.g. CROSS APPLYing them is one of the best ways I've seen to do reusable row-level filtering.

Would you happen to have a link that goes into further detail?

Great timing for me on this, thanks for sharing!

np!