Support for calling _any_ Python module within Jinja context

[Bilbottom]

Describe the feature

I love that dbt allows us to template SQL with Jinja -- it adds so much flexibility and enriches the development experience with dbt!

...until we start having very Jinja-heavy files (I'm mainly thinking of macros). While Jinja is awesome for templating and definitely has a place in the dbt world, it's not as feature-rich, as user-friendly, or as readable as Python when there's some moderately complicated logic.

Including support for some of the Python standard library modules was an awesome addition: this discussion is about extending this support to any Python module that the user needs*, including local Python modules which could then replace clunky custom Jinja code.

I'll go into some detail below about an MWE implementation that has worked for me in a small personal project (but I've not tested it at scale so it's not a solution that's ready to go).

Note that this has only been tested with .sql files since it exposes the modules only to the Jinja context. (I may follow up with a comment about how this plays with Python models.)

*With great power comes great responsibility -- this feature would put a lot of responsibility on the users to not abuse this power, like how Python models shouldn't be abused by doing things outside of the T in ELT.

---

Implementation notes

I've raised a PR on my fork of dbt-core which is an MWE implementation with some implementation notes, available at:

• https://github.com/Bilbottom/dbt-core/pull/1

To summarise the implementation, it does 2 things:

1. Adds a new configuration file, modules.yml, which lists the Python modules/packages to expose to the Jinja context.
2. Behind the scenes, dynamically imports the modules (in a backwards-compatible way) and binds them to the Jinja context.

For example, suppose the content of my modules.yml file was:

modules:
  - package: math
    exports: [e, inf, nan, pi, tau]

Then I could reference these math constants in a Jinja context, such as:

SELECT
    2 * radius * {{ modules.math.pi }} AS perimeter
FROM circles

(This is a bit of a superficial example since most databases expose a PI() function which should be used instead.)

The real power of this feature is being able to use custom Python code -- I could define some helper functions in a Python module in the same repo as my dbt project, and call them in the Jinja context:

# modules.yml
modules:
  - package: custom_package
    location: "path/to/parent/directory/"  # Not super future-proof, just for the MWE
    exports:
      - some_helpful_function_1
      - some_helpful_function_2

{#- macros/some_complicated_macro.sql -#}
{% macro some_complicated_macro(arg_1, arg_2) %}
    {#- Some complicated macro (when using only Jinja) -#}
    {% set something_1 = modules.custom_package.some_helpful_function_1(arg_1, arg_2) %}
    {% set something_2 = modules.custom_package.some_helpful_function_2(arg_1, arg_2) %}
    ...
{% endmacro %}

When custom Python can be exposed to the Jinja context, the sky's the limit -- this unlocks even more potential for dbt!

---

Why does this help?

I'll call out 3 main reasons:

1. Provides an alternative to clunky Jinja macros which can be hard to read and understand (especially compared to Python which has docstrings and type-hints).
2. The Python code can be unit-tested, which gives everyone more trust in what we build.
3. It unlocks even more features that can be utilised natively inside dbt.

As I mentioned above, this does introduce the potential for people abusing this -- but I think the pros outweigh the cons, and it would certainly encourage more people that I know to adopt dbt since they're far more comfortable writing Python than they are writing Jinja.

---

Describe alternatives you've considered

The only other thing that I could find to support this is fal, but this becomes another tool to add to the stack rather than exposing the functionality in dbt-core.

My company also has a POC to append modules to the existing modules dictionary ({{ modules.*** }}) at runtime, which is working for us (but I can't share details of this, and is built specifically for our setup).

The approach described above extends dbt-core itself, so would require very little work from the developer to start using custom Python packages in their dbt project.

EDIT: As per the comments, the following PR also requested this feature:

• draft: allow users to augment their jinja context with python objects #5274

As per the top comment (not the bot), this wouldn't be accepted into dbt-core so this discussion can probably be closed. However, I'll leave it open (for now) since I like the idea of exposing this through a new adapter instead.

---

Who will this benefit?

This is another one where the answer is, hopefully, "everyone"!

A lot of the resistance I've seen to dbt at scale is people complaining about clunky Jinja, so this unlocks the freedom to keep Jinja to what it's good at (templating) while still being able to inject custom Python at runtime.

---

Next steps

Regardless of how this discussion goes, I'll keep working on this in my fork since I have loads of plans to enrich my dbt projects with extra Python packages -- so I'm very happy to play a significant part in contributing this functionality to dbt-core if there's interest 😄

[kyle-kent]

@Bilbottom based on your implementation is it possible to make this a package so we don't have to wait for the kind @jtcohen6 and @dbeatty10 to consider this? Happy to contribute if so!

cc: @kentkr

[kyle-kent]

This seems pretty sought after but discussions like this pr make me think it won't be a part of the core functionality. A package/adapter seems like the answer, if there is one.

[Bilbottom]

Hey @kyle-kent -- thanks for sharing the link, I think I agree that this'll only get any traction as a package/adapter rather than being part of core (even though ). Unfortunately, I have no experience doing this so I won't do it any time soon 😄

For the time being, I've merged the PR I mentioned in the discussion above so that my fork can be installed (instructions here), but lemme have a play around with a potential plugin approach and I'll drop any updates back here 😋

[kentkr]

The other day I tried running your code and ran into an issue but I'll try it again soon. Either way your implementation made sense and I got the jist. It looks great and would be so convenient.

Since we'd need to change the underlying python code it'd have to be an adapter not just a package, I believe. Based on my reading of that link I posted it might not even be possible? I also don't have experience with adapters so i don't know. I'm finishing up another project now so I'll take a look this weekend.

[Bilbottom]

I've been playing around with the adapter approach, and I can't get it to work either 😞 As far as I can tell, it's only possible to have 1 adapter "enabled" at a time (via the profiles) -- so if I were using, say, the Snowflake adapter I couldn't then also use some custom Python adapter at the same time

What does seem possible is to create a Python plugin on existing adapters, but that would require buy-in and support from the adapter maintainers rather than being something that any user can enable themselves

I'll keep thinking and tinkering, but I'm not sure that this is something that dbt can support right now without wrapping something custom around dbt 🤔

[Bilbottom]

@kentkr / @kyle-kent I've been doing some more tinkering and stolen an idea from my boss -- I've whipped up the following repo as a starting point:

• https://github.com/Bilbottom/dbt-py

This is a separate Python package which exposes a new executable, dbt-py, which injects custom Python into dbt before running the dbt command

It's still in its infancy so the features/configurations are super limited, but I've been able to get it working with one of my other repos and this feels like a fairly promising approach (for now)