dbt Core
TRANSFORMATION
dbt (data build tool) is a command-line tool that enables data analysts and engineers to transform data in their warehouse more effectively. It allows users to write modular SQL queries, test data integrity, and document data transformations, streamlining the analytics engineering workflow. dbt supports version control and collaboration, making it easier for teams to manage and deploy data models.
dbt + MotherDuck
dbt integrates with MotherDuck by allowing users to perform data transformations and analytics directly within the MotherDuck cloud data warehouse. By connecting dbt to MotherDuck, users can leverage DuckDB's efficient query execution engine for transforming and modeling data, ensuring seamless and optimized data workflows. This integration empowers teams to build, test, and document their data pipelines efficiently while utilizing MotherDuck's scalable and performant data infrastructure.
Since both dbt and DuckDB can run locally, users can do development and testing using the same technologies they run in production.
Customer Success Stories
Data teams rely on dbt with MotherDuck for efficient transformations:
- UDisc reduced their dbt job runtime from 6 hours to just 30 minutes by switching to MotherDuck.
- Gardyn uses dbt as part of their transformation layer, processing IoT and ecommerce data with 10x cost savings.
- Trunkrs leverages dbt for data transformation in their same-day delivery analytics pipeline.
- Reflex builds analytics-ready tables with dbt, powering insights for gig economy retail.
FAQS
How do I set up dbt with MotherDuck?
Install dbt-duckdb with pip3 install dbt-duckdb, then configure your profiles.yml with the MotherDuck connection string: path: "md:my_db?motherduck_token={{env_var('MOTHERDUCK_TOKEN')}}". Set your MOTHERDUCK_TOKEN environment variable before running dbt commands. See the complete setup guide in our dbt documentation.
What is the dbt-duckdb adapter?
The dbt-duckdb adapter enables data transformation workflows using DuckDB locally or MotherDuck in the cloud. It lets data analysts and engineers transform data by defining SQL in model files. Installing dbt-duckdb via pip gives you both dbt and DuckDB in one package, making it easy to develop locally and deploy to MotherDuck.
Can I use dbt with multiple MotherDuck databases?
Yes, dbt-duckdb supports multi-database operations using the attach configuration in your profiles.yml. You can attach additional databases with aliases and reference them in your models using {{ config(database='other_db') }}. This allows you to read from and write to multiple MotherDuck databases in a single dbt project.
What thread setting should I use for dbt with DuckDB?
Set threads: 4 in your profiles.yml for optimal performance. While you might expect more threads to be faster, DuckDB already parallelizes individual write queries internally, so additional dbt threads provide diminishing returns. The recommended setting of 4 threads provides the best balance for most workloads.
Can I use dbt Cloud with MotherDuck?
Yes, MotherDuck integrates with dbt Cloud. You can configure your dbt Cloud project to connect to MotherDuck by setting up the connection credentials in your dbt Cloud environment settings. See our dbt Cloud documentation for detailed setup instructions.
