A guide on best practices for developing Python models in dbt 🐍

[joellabes]

As Python model support in dbt comes closer, it's time to start thinking about guides to help practitioners do things in a dbtonic way.

There are two directions we can view this from; I think they're probably different enough that we'll need a guide for each persona, but maybe they can overlap!

1. The dbt user who wants to do some modelling in Python, so that they can do things SQL isn't good at but stay inside of their familiar context
2. The Python user who wants to start hosting their projects in dbt, so that they can take advantage of dbt's existing data models and understanding of lineage instead of babysitting a stack of notebooks.

To reiterate @jtcohen6's callout in the above discussion:

Our primary intent with Python models is to extend dbt’s reach into solving new types of problems, by opening up new kinds of modeling to people for whom it was previously inaccessible: people for whom the data warehouse is their principal tool.

Fortunately, I represent persona # 1 pretty well! Here’s some things I'm hoping we can build out:

• Several worked examples that broaden my horizons: I sort of conceptually understand that Python is cool and you can do predictive stuff with it, but don't know where to start.
• Flagging common pitfalls: how might thinking with SQL get in my way when I start to develop models in Python?
• ⚠️ I don't want us to build here's how you pivot a table with dbt_utils, and here's how you pivot a table with pandas, since our goal is to unlock net-new capabilities without unnecessarily stripping SQL out of the mix
  • which I guess adds another thing to the list: some sort of decision tree on what stuff even needs to be in Python
• But I do want to be able to bootstrap a mental model based on what I already know about Analytics Engineering. What is the equivalent of a dbt macro in Python? How should I implement environment-aware filtering?
• How and where should people develop their models before adding them to the dbt project?

@lostmygithubaccount will represent the dbt-curious Python developer, but for sure we'll want some the same sort of pitfall guidance: what behaviours make sense in other Python contexts but don't make sense in a dbt world?

[xg1990]

Curious is it the right link to thinking with SQL?

[joellabes]

Heh, yep it's just a very bad joke referencing the "now you're thinking with portals" quote.

[Opiano1]

I had to rewatch the url again to confirm😂 and seeing it was meant to be a bad joke

[kenfar]

One of the most valuable features of using python for transformations that was not covered in the documentation is the ability write automated unit tests. Not quality-control checks against existing data, but quality-assurance checks against potential data. To test numeric overflow, invalid dates, encoding issues, validation & exception logic, etc, etc. I would really love to see how folks would leverage this capability: move each field transform into a dedicated python function and then accompany that with a unit test class?

[lostmygithubaccount]

hi @kenfar, we're absolutely thinking about how to support that! in v1.3 however, testing is still done through the existing generic and singular tests authored in SQL. this is in our list of next steps on discovery work (see this discussion: dbt-labs/dbt-core#5742)

[AndrewLane]

I've been trying to explore how I'd do this "unit" testing (where the tests still reach out to Snowflake for computation) given the tools available today, and this is what I've come up with. Would appreciate any criticism or comments:

https://github.com/AndrewLane/dbt-python-model-testing-exploration

[lostmygithubaccount]

very cool! we'll definitely be taking a look 👀 thanks for developing this @AndrewLane!