Connect dbt to DuckDB: A Step-by-Step Guide

Introduction

Modern data work doesn’t always require complex infrastructure. If you're looking for a fast, local-first analytics stack, dbt + DuckDB is a powerful combination. In this tutorial, we'll walk through connecting dbt to DuckDB — perfect for analysts, developers, and data enthusiasts who want simplicity without sacrificing power.

🐤 What is DuckDB?

DuckDB is an in-process SQL OLAP database management system. Think of it as SQLite for analytics — it's optimized for complex queries on large datasets, all while running locally and without needing a server.

💪 Why use DuckDB with dbt?

• Local-first: Everything runs on your laptop — no cloud or database setup needed
• Blazing-fast performance: Optimized for analytical workloads
• Zero infrastructure: Skip the DevOps — perfect for prototyping, notebooks, and data apps
• Seamless dbt integration: Works out of the box with dbt-duckdb

✅ Prerequisites

Before you begin, ensure you have set up a dbt project. Your project structure should look like this:

👤Configure profiles.yml

dbt uses the profiles.yml file to store connection information to the data warehouse of your choice. Here's a minimal configuration for DuckDB:

dlnerds_university:

  target: dev
  outputs:
    dev:
      type: duckdb
      path: 'dlnerds_university.duckdb'
      threads: 4

Let's break down the components:

• dlnerds_university: Profile name (must match the one in dbt_project.yml)
• target: Specifies which output to use by default (in this case, dev).
• outputs: A dictionary of all available connection targets (e.g., dev, prod). You can switch between them if needed.
• type: Adapter type — in this case, duckdb
• path: File path to the DuckDB database
• threads: Number of threads dbt can use when building models

🔁 Test DuckDB connection

Let’s make sure that the connection works. From your dbt project directory, run: