Using dbt pytest fixtures for plugins

[max-sixty]

Over at prql/dbt-prql — the dbt integration for prql — I'm trying to write some tests to ensure that PRQL queries are parsed into SQL in dbt models.

This currently involves:

• A mock dbt project in repo
• A dbt compile command

This isn't great, since even dbt compile seems to require a live DB connection. That adds a lot of overhead to a test suite, when we only need to test the output it target/.

A couple of questions:

• Is it possible to use dbt pytest fixtures in plugins?
• Any other thoughts or suggestions for testing the output of dbt compile?

[jtcohen6]

Is it possible to use dbt pytest fixtures in plugins?

Yes! In plugins, in packages — really, wherever you can install and run dbt-core + pytest.

Existing documentation: https://docs.getdbt.com/docs/contributing/testing-a-new-adapter. Note that you don't actually need to be testing an adapter to use this.

There's a discussion about this, which will hopefully turn into a developer blog post / guide: dbt-labs/docs.getdbt.com#1808

Any other thoughts or suggestions for testing the output of dbt compile?

Hmm. Appreciate the added overhead required by having a live DB connection. The time overhead does go down significantly if you're using a local or in-memory database (Postgres, DuckDB, SQLite), but it does still require a bit of additional setup. There isn't any reusable tooling we've developed to effectively mock the database connection, such that you can unit-test runtime behavior, including compilation. I do think this would be a highly valuable addition to the pytest toolkit.

---

@max-sixty As a separate line of inquiry — something that's been in my head for a little while now, not really relevant to the narrow scope of this discussion, but here we both are — I'd be interested in imagining a more fundamental integration between prql + dbt. Python models in dbt do not use Jinja at all (at least, not really). We use Python's own AST to parse out ref + source + config, and feed them back into dbt's parsed representation of the model. Then, we use some code-gen Python to "resolve" those method calls at runtime. Maybe prql could do something similar, rather than leaning on Jinja for ref detection + resolution?

[max-sixty]

Re the tests — great — I also just saw the adapter readme a level below tests

There isn't any reusable tooling we've developed to effectively mock the database connection, such that you can unit-test runtime behavior, including compilation.

Yeah, ideally there would be a test harness that could test just the compilation step, without any need for a live DB connection — isolating the library from external dependencies. Looking through the code, it looks like dbt relies on a live connection for more than I expected — maybe it's not easy to compile a model without one.

---

I'd be interested in imagining a more fundamental integration between prql + dbt

For sure, we're doing immense hackery at the moment, because it's not possible to plug into dbt natively. We only use jinja to resolve ref + source + config (and currently as a way of hacking our way into the dbt runtime with the {% prql %} extension). Great idea re giving up jinja — we could instead have a dbt function that resolved these; e.g.

from (dbt source.foo_model)
join (dbt ref.bar_model) [id]
filter baz > 6
group thud (
  aggregate [
    max fuz,
    min qux,
  ]
)
filter fuz < 10

For that to happen, I think we'd need (and obv you know better, particularly having just built the python API!):

• A dbt API for resolving names (likely already exists) that dbt-prql could use
• A way of telling dbt how to run a file — i.e. "call dbt_prql.compile() for any *.prql file"
• A way of the plugin telling dbt the dependencies in a file — i.e. "this file relies on source foo_model and ref bar_model"

This could either be a generic plugin framework or something prql-specific, depending on whether you have other candidates for this. I'd definitely be happy to work together to make this work for PRQL, let me know if you're interested in discussing more. I'll drop you a mail too.

[jtcohen6]

Agreed! It sounds a lot like what we just built for Python. I'm intrigued by the possibilities of what a "language" plugin could look like... let's talk more :)