How to use DBT transformations in-memory?

Question

I want to implement DBT transformation models in Azure Functions without any persistent storage.

By default however DBT will work with a running database instance like PostgreSQL. How can DBT be used purely in memory with, for example, incoming JSON files? Is it possible to create an in-memory version of PostgreSQL, H2, or SQLite which can be created and destroyed within Azure Functions?

Answer 1

You can use DuckDB with the dbt-duckdb plugin. When configured with your dbt model you can either use an existing DuckDB instance, or spin up an in memory instance that will complete the dbt transformations. By default memory is used and it is easy to read and write JSON, csv, etc. The docs are quite good. I haven't tested it on Azure functions yet but plan to soon.