dbt & Python: language agnosticism

[dataders]

The dbt labs community has found great success in SQL-based data warehousing by making the work as smooth and performant as possible. Concurrently, python also found profound adoption amongst data professionals. While Python dbt models are only a single step in the direction of language-agnosticism for working with data, that step places us firmly in the land of “Not Only SQL''. This is well on the way to: “use whatever language you want”, as long as your database can interpret and execute what you’ve written performantly. So, sure, maybe R support comes next, but what about a language plugin that enables you to write the fashionable and ergonomic PRQL. and dbt and Snowpark abstract the tedium away from you and it just works. Imagine a world where the perpetual Python vs. R vs SQL debate is made redundant! What future do you imagine for input languages?

[cnolanminich]

I would love it if dbt could have different SQL "compilers" in a language agnostic way, perhaps via a plugin system. dbt could articulate the necessary inputs / outputs (very similar to the Python models that are currently in beta).

Imagine if I could have one dbt model that is written in dplyr in R, another in PRQL, a third in Malloy, and one in Ibis in Python. The "compiled" versions would all be SQL, so the business users / teams could understand exactly what transforms are happening, but the inputs could be different languages.

These would be optional "templaters," kind of a souped-up version of what jinja-sql does today

Of course this would make static analysis of dbt projects .. difficult! OTOH, maybe not so much more difficult

[binste]

Fully with you! This would be great and giv e a lot more flexibility. For writing dbt models in Ibis, I built https://github.com/binste/dbt-ibis which does exactly what you described, compiling it to SQL so that dbt can pick it up and users still exactly understand what transforms are happening. Just in case this is useful to you.

[max-sixty]

Hi @dataders — thanks for kicking this off!

I had discussed the possibility of a PRQL plugin a few weeks ago with @jtcohen6 & @lostmygithubaccount (Cody!) and we agreed to start a broader discussion with the intent of implementing this when I was back. I'm including some notes from our emails and Zoom.

For context, I'm a maintainer of PRQL, and a huge dbt fan since the ancient days of 2019.

Goals

• Enable writing PRQL in dbt models
• Potentially enable a generic interface for writing any language that compiles to SQL in dbt models

Proposal

(I'm using "PRQL" here but depending on how general this becomes, that could be a generic adapter)

We'd need three methods for PRQL to communicate with dbt:

• List dbt references — dbt needs to know the ref / sources in a model, so dbt can build its DAG
• Resolve dbt references — we need to resolve ref / source so we can replace it with the resolved or query text
• Compile the model — turn the PRQL into SQL

Two options for these:

1. Jinja — PRQL uses jinja, just like SQL does now.
   • A model could be from in_process = {{ source('salesforce', 'in_process') }}. For dbt, listing & resolving the references is exactly the same as SQL.
   • The only change required of dbt is calling dbt_prql.compile({prql_text}, dialect="snowflake") on any *.prql file prior to replacing the references. PRQL then compiles it to SQL, as it does now.
2. API — PRQL & dbt implement these methods in python:
   • We don't need to use jinja syntax; we can use something like from (dbt source.salesforce.in_process). Because PRQL has abstractions like variables and basic functions, jinja isn't needed
   • It's currently implemented this way for Python with dbt.ref("my_sql_model")
   • The plugin interface would work as:
     • dbt calls a dbt_prql.list_references({prql_text}) method
     • dbt resolves the references to table names, as it does now
     • dbt supplies a dict of references when compiling: dbt_prql.compile({prql_text}, references={"source.salesforce.in_process": "salesforce.in_process"}, dialect="snowflake").
     • PRQL compiles the query into SQL, as it does now

I think the main tradeoff is that Jinja is simpler to implement, but the API approach is more general, and would allow languages which can't contain Jinja to be syntactically correct.

How generic?

It's worth discussing how generic this should be, so we don't over-build something at first. There's a spectrum:

1. Add PRQL to core as an experimental sub-package
2. Create an experimental plugin interface in dbt-core, a PRQL adapter dbt-prql, and allow for iteration on the interface
   1. A mapping from plugin->file extension is hardcoded in dbt-core
   2. There's a convention of dbt-{extension} for plugin names
   3. Plugins announce their extension to dbt on import
3. As above, but also produce a dbt-python adapter
4. As above, but have the plugin interface be complete at first so it's reliable across versions

A couple of thoughts on the value of starting with something generic:

• Python is the obvious candidate. It's a bit different to PRQL in that we send python to the DB, not compiled SQL. I'd have to look more at how coupled it is within dbt-core to assess whether it would be worth abstracting into a separate plugin, at least initially.
• @dataders mentions Malloy & R. I'm a big fan of both! Though I'm not sure how easy it would be for them to work in a dbt environment; IIUC they don't have python runtimes. Assuming that's correct, people would need to install an R or javascript environment and someone would need to write bindings to those.
  • I'd be interested to know from my friends @mtoy-googly-moogly & @lloydtabb at Malloy how they would prioritize an integration here; one of the great things about Malloy is that it does some of the "project integration" & "db management" that dbt does. PRQL is laser-focused on the data transformation, and so is a natural partner to tools like dbt / jupyter / rill.

Existing state

Currently there is a plugin https://github.com/prql/dbt-prql, which does extreme python hackery to monkeypatch dbt's jinja environment whenever dbt is imported. It unfortunately only supports DBs which use backticks, like BigQuery (this is because we can't get the model before refs have been replaced; I can explain more if anyone is interested...).

It would likely be less work to implement this properly!

Path forward

Assuming we can agree on an approach, I'd be happy to contribute the PRQL integration and the changes needed to dbt-core.

I would vote to do something less abstract, prove the concept, and then make it more generic later. So that would mean doing "Jinja", and (1) or (2.i) / (2.ii) in the "How generic?" options above. That would then be easy to extend to "API" and (3) & (4) as we gained more experience. But very open-minded to other ideas.

[jtcohen6]

@max-sixty Thanks for the thoughtful and detailed post!

I'm super interested in seeing what's possible here, and would love to support any experimentation you're willing to take on. I fully agree with starting with what's easiest to get off the ground, which is probably a branch/fork of dbt-core that adds built-in support for PRQL. That would also confirm for us the places we'd need to make pluggable — both for a dbt-prql plugin, and for a dbt-python plugin, too!

I'm hesitant about starting with the "Jinja"-based approach, though. When I close my eyes, and look a year+ into the future, dbt offers language support for Jinja-SQL, Python, PRQL, ... That is to say, Jinja may always be in the mix, but I'd like it to be one of several options, with its own pros (familiar templating syntax, interactive querying) and cons (trickier to unit-test, missing a structured internal representation, ...). Users can choose it when it makes sense, and we can have strong opinions about when it really shines.

I think it's easier to start on the "API" path if we also start working directly within dbt-core. I expect that we'll need to start by adding code to these spots, which should later be carved out as part of a "language plugin" API interface:

1. Add prql as a ModelLanguage, and add .prql as a supported file extension in read_files. Maybe someday we want users or plugin authors to provide custom language <> file extension mappings, but I also think there are lots of fair reasons to want a 1:1 mapping, and it feels like a reasonable starting point for now (for IDEs, extensions, etc).
2. Model parsing. Essentially, in place of parse_python_model using Python's AST, we'd want to use PRQL's (dbt_prql.list_references() or similar) to return all instances of ref, source, config. (Then again: Why not the whole AST, including column-level transforms?)
   • We'd need to update more code in more spots if we want to support PRQL tests, snapshots, etc.
   • Macros? Where do PRQL functions get defined / imported?
   • N.B. The code for Jinja-SQL is way more complex, because we try doing static analysis, and otherwise fall back to a special sort of "no-op" Jinja render.)
3. Compilation. For PRQL, I think this would want to work exactly as you say: dbt_prql.compile({prql_text}, references={"source.salesforce.in_process": "salesforce.in_process"}, dialect="{adapter_type}").
   • Python "compilation" happens via code-generation, which is a lot like templating... so we're we're using Jinja here, only because it was an expedient way to provide the same information in the same basic ways. It definitely shouldn't look like this over the medium/long term, and I think this work could lay some foundation we could later use to clean it up.

And I think that's it! If we can manage to get all that working, by adding code directly into dbt-core—then we can also think about how we could load a language plugin, and call it in each of those spots.

[max-sixty]

I'm super interested in seeing what's possible here, and would love to support any experimentation you're willing to take on. I fully agree with starting with what's easiest to get off the ground, which is probably a branch/fork of dbt-core that adds built-in support for PRQL. That would also confirm for us the places we'd need to make pluggable — both for a dbt-prql plugin, and for a dbt-python plugin, too!

Super!

I'm hesitant about starting with the "Jinja"-based approach, though. When I close my eyes, and look a year+ into the future, dbt offers language support for Jinja-SQL, Python, PRQL,

OK I agree re "closing eyes, and not seeing Jinja in the happy place" :) . And very much agree with your 1/2/3. Let me give that a start this weekend.

Then again: Why not the whole AST, including column-level transforms?)

Yes, good point. We can return the whole AST now; probably we want to transform it into something more helpful for dbt to consume, like a mapping of output column -> input columns; could be very cool.

---

The only thing that I'm unsure of — but it's very tractable and internal to prql, not expecting a response from others unless they have a bright idea — is how to manage dbt_prql.compile({prql_text}, references={"source.salesforce.in_process": "salesforce.in_process"}, dialect="{adapter_type}"):

I want to reduce the amount of back & forth between prql & dbt to reduce coupling. And probably we don't want every single PRQL compile call to take a references mapping. One approach would be for prqlc to compile to the "jinja" representation; e.g.:

from (dbt source.salesforce.in_process)

is compiled by prqlc (the rust compiler) to:

SELECT * FROM {{ source('salesforce', 'in_process') }}

...and then dbt-prql does the replacement, with the references dict it's supplied (maybe using Jinja, maybe just text replacement given we know its exact text) into:

SELECT * FROM salesforce.in_process

The other option I can imagine is that compiler does take some args, like replacements. dialect is another example. For dialect I was planning for dbt-prql to just add the text into the query; i.e. it inserted this if it didn't exist:

+ prql dialect:snowflake
from (dbt source.salesforce.in_process)

...though it could be an argument to the compile call instead. There was a nice encapsulation we got when everything was represented by only the text that this would lose.

I'll pause my thinking aloud here, have a go at getting something working, and then we can touch base when that's done.

[max-sixty]

FYI I'm making decent progress on this!

It's not going to be that general — only a few parts of my code has some notion of an interface. The code handling python models is quite "special cased" e.g. the link above

dbt-core/core/dbt/parser/models.py

Lines 219 to 233 in 13a5957

	def render_update(self, node: ParsedModelNode, config: ContextConfig) -> None:
	self.manifest._parsing_info.static_analysis_path_count += 1
	if node.language == ModelLanguage.python:
	try:
	verify_python_model_code(node)
	context = self._context_for(node, config)
	self.parse_python_model(node, config, context)
	self.update_parsed_node_config(node, config, context=context)
	except ValidationError as exc:
	# we got a ValidationError - probably bad types in config()
	msg = validator_error_message(exc)
	raise ParsingException(msg, node=node) from exc
	return

; the Prql part at least moves us closer to generality.

The amount of work isn't trivial, so it is important that the investment leads to it being merged in some form. If there's still ambiguity over whether this is a good idea for dbt, let's have that discussion now, as it would be a shame to report back to the other PRQL devs that I'd chosen to focus on this without it helping the cause. I hope that's a reasonable ask (I mentioned something similar when we spoke IIRC). Thank you.

[jtcohen6]

@max-sixty Once you have a version of the code you're able to share — even if it's still quite "special cased" — I'd love to share with folks on the Core-Languages eng team!

For starters, I'm imagining something like:

if node.language == ModelLanguage.python:
    ...
elif node.language == ModelLanguage.prql:
    ...
else:  # ModelLanguage.sql
    ...

I think that would be enough to kick off the next round of conversation.

[max-sixty]

OK great, that seems reasonable

(Yes, that's what it does. It then calls out to a LanguageProvider class, rather than just plonking another method on that class, so baby steps...)