Nat Taylor — Blog, Product Management & Tinkering

Duplicative unions in dbt

Published on May 27, 2023. Updated on

Recently I was implementing a dbt model that involved a union all of nearly identical 15-line queries and I wanted to avoid the duplicative code. It turns out to be an easy problem to solve with format(), which is a bit of Jinja that I wasn’t familiar with.

I started with SQL something like the following, which involves almost the same exact code twice, although you have to use your imagination a bit to picture this as 30-lines of SQL.

select 'mytype' as class, foo, bar, baz, bat, bam
from foo
union all
select 'mytype2' as class, foo, bar, baz, bat, bam
from bar

The solution I came up with is as follows

{% set sql = "select '%s' as class, foo, bar, baz, bat, bam from %s"}
{{ sql|format("mytype", "foo")}}
union all
{{ sql|format("mytype2", "bar")}}

This is the power filters

Variables can be modified by filters. Filters are separated from the variable by a pipe symbol (|) and may have optional arguments in parentheses. Multiple filters can be chained. The output of one filter is applied to the next. The List of Builtin Filters below describes all the builtin filters.

https://jinja.palletsprojects.com/en/3.0.x/templates/#filters