Here in The Information Lab's Data Engineering (DEng) extension program, we learn all sorts of things. We learn advanced SQL, python, even Jinja in dbt. What if I told you I learned to tap into all three? Using the power of Jinja, I learned to make even the most lengthy SQL scripts fully modular.
Say you have several models you want to create, each of which all need a heap of CTEs (Custom Table Expressions) pre-loaded ahead of the final Select statement.
I created a macro that will type this all out for you. To be clear, this is a silly use of a macro. But, it was fun to make, and it's a great example of how to write a macro using for loops, if statements, project folder navigation, and custom SQL query writing.
This fancy macro creates a CTE for every table within a folderpath you specify. For example if you have a folder for your "wizard" data and want to write CTEs for each model within it, you'd call the function with:
{{ cte_macro('wizard') }}
There's also the second argument selection. This is, by default, set to '*'. You can see on line 16 where {{selection}} is plugged in. In this context, it will probably only make sense to plug in a SELECT * from each table. (Again, this is somewhat of a made up scenario.)
Lines 3 through 8 perform a for loop in which, for each file in the folder that is a model (i.e. is it a .sql file?) append it to the list we denoted as models_in_folder. Once this is complete, "models_in_folder" should contain all the models in the folder!
10 through 13 creates a commented out list of these models that gets printed to the terminal when the macro is compiled.
The remainder is what forms the meat of the SQL code we want. It takes the first model in the list and writes the "WITH model_name AS ..." syntax, which is necessary no matter how many models we want to make CTEs out of. With that done (no pun intended) it then adds an additional CTE to the query contingent upon three things, each nested within the last:
- If the models_in_folder list has more than one item in it, then...
- For each model in the list...
- As long as it's not the first model in the list (since we already wrote that CTE)...
Then - write a CTE as denoted in the SQL written there, plugging in the model_name where appropriate.
You may notice the odd indentation. The if statements and for loop beginnings and ends are indented as you'd expect in traditional python syntax, but the actual SQL code has to be shoved over to the left. That's because those indents will actually carry over into what gets printed when the macro is run! Thus, they've been removed here. It makes reading the macro itself a bit more difficult, but the CTEs come out shiny and perfectly formatted.
Note that the SQL text in the macro begins with the comma. Leading commas are your friend when writing things this way!
Hopefully this has given you a bit of insight and possibly even inspiration as to how to write your own macros in dbt. Ideally ones that suit realistic use cases.