How to run a script on the compiled SQL?

Testing
#1

Hello there!

We have a lot of comments in our Jinja files that show up in the compiled SQL files which makes it difficult to debug the compiled SQL. Also, the whitespace is usually messed up in the compiled SQL files. I understand there is a way to fix whitespacing by modifying the Jinja code but that makes Jinja less readable.

Hence, is there a way to perhaps override the “compile” macro and let it remove the comments and reformat the SQL? Or, is there a way to run some arbitrary script on the compiled SQL post compilation via a hook?

Thanks!

#2

If you replace your SQL comments with Jinja comments they won’t show up in the compiled SQL:

dbt model:

/*
SQL COMMENT 
{# 
JINJA COMMENT 
JINJA COMMENT 
-#}
SQL COMMENT 
*/

compiled:

/*
SQL COMMENT 
SQL COMMENT 
*/

You can stop the additional whitespace by adding “-” to the start or end of the jinja tags as shown in the Jinja comment above

#3

Right, but it’s somewhat less than ideal to go change the entire codebase to incorporate a different style of commenting code.

But anyway, I have created a workaround using a Linux library “inotifywait” to listen for any changes in the target folder, and then running a script that removes all the comments and formats the SQL code.

#4

Could you use a similar script to the one that reformats the compiled SQL on the non-compiled SQL to change the comment syntax?

That way if someone compiles the dbt on a different machine they won’t have to reimplement the inotifywait fix

#5

That’s an interesting point. But it’s somewhat harder because the non-compiled SQL isn’t simply just SQL. It’s as you know Jinja+SQL, for which I don’t have any formatter. Regardless, a simple script could be written to change the comment syntax. But as I said, I am not the most comfortable running that on existing codebase and pushing that as a change. To me it is somewhat unreasonable to change those many lines of “code” for this reason.

Maybe if I have a reliable formatter, I’d be more open to running that on the entire codebase. :smile: