DuckDB: Run dbt build with sub-second execution times

The Agony of a Slow Data Pipeline

I want to tell you a story. A few years ago, I inherited a project that involved generating large graph datasets. The original pipeline was built on Hadoop. It was slow and incredibly difficult to work with. We migrated it to Spark, hoping for an improvement. While it was a step up, it was still complex and, more painfully, expensive. We were spending tens of thousands of euros just running Spark jobs to generate this data [13:51].

The pipeline was so cumbersome that we decided to pre-generate all possible variations of the data. This led to a combinatorial explosion of files, growing to tens of terabytes that we had to store on archival tape. If you wanted to use the data, you had to request it, wait for it to be retrieved from tape, and then download it. As I said to my team, "this is obviously no way to live" [13:58].

This experience set me on a path to find a better way. What if we could take that massive, expensive cloud pipeline and run it on a laptop? What if we could do it in under a second? This is the story of how DuckDB and dbt-core make that possible. To see how this leap is possible, we first need to understand what makes DuckDB's architecture so different.

What Makes DuckDB Different? The "In-Process" Revolution

When developers first encounter DuckDB, the immediate question is: what makes it different from other databases like PostgreSQL or Snowflake? The answer lies in its architecture. DuckDB is an in-process OLAP database.

Traditional databases use a client-server model. You have a database server running somewhere—on-prem or in the cloud—and your application connects to it over a network. This model works, but it has inherent friction. There is the operational overhead of setting up and managing a server, the hassle of configuring credentials, and the network itself, which is often the biggest bottleneck. Fetching a billion-row table from a remote server will always be slow and expensive [01:54].

DuckDB eliminates this entirely by running directly inside your application. There's no server to manage, making it a truly serverless architecture—unlike cloud "serverless" options where a server is still running somewhere [02:21]. You just import duckdb in Python, and you have a full-powered analytical database at your fingertips.

We were heavily inspired by SQLite, which proved the power of a single-file, embedded database. We like to think of DuckDB's place in the ecosystem like this:

"DuckDB has the form factor of SQLite... but it has the workload style of Snowflake." [03:03]

It gives you the simplicity and portability of an embedded library with the analytical power of a modern cloud data warehouse.

How DuckDB Achieves Sub-Second Speed

How can a library running on a laptop outperform a distributed cluster for certain workloads? The magic is in three core design principles.

Hands-On: Migrating a Spark Pipeline to dbt-duckdb

Let's go back to my slow, expensive Spark pipeline. Here’s how we can replace it with a simple, elegant dbt-duckdb project that runs in under a second.

Copy code
-- Gabor's optimization technique

COPY person TO 'person.csv'; -- 13MB

COPY person TO 'person-v1.parquet' (PARQUET_VERSION v1); -- 4.4MB

COPY person TO 'person-v2.parquet' (PARQUET_VERSION v2); -- 3.6MB

COPY person TO 'person-v2-zstd.parquet' (PARQUET_VERSION v2, COMPRESSION 'ZSTD'); -- 2.6MB

Copy code
FROM 'input/person.parquet'

Copy code
{{
  config(
    materialized='external',
    location='person-project-fk.csv'
  )
}}
SELECT 
  creationDate, id, firstName, lastName, gender, birthday, locationIP, browserUsed
  -- some transformations here
FROM {{ ref('person_merged_fk') }}

Copy code
dbt build

Copy code
duckdb dev.duckdb -c ".ui"

From Local Core to Cloud Scale: The Broader Ecosystem

DuckDB is a fully open-source project with a permissive MIT license. To guarantee it stays that way forever, we've moved the intellectual property into the non-profit DuckDB Foundation in the Netherlands [17:43]. This ensures the core project will always remain free and open, addressing a common concern in today's database landscape.

The core development happens at DuckDB Labs, a revenue-funded company (no VC!) that provides support and consulting. But what happens when your local project needs to scale? What if you need to collaborate with a team?

This is where our partner, MotherDuck, comes in.

MotherDuck takes the powerful local core of DuckDB and intelligently builds back the most useful components of the client-server model: collaboration, cloud storage, and easy data sharing [18:47]. It offers a serverless data warehouse based on DuckDB, but with a unique twist: hybrid query execution [18:51].

Imagine you have sensitive PII data on your laptop that can't leave your machine, but you need to join it with a large public dataset in the cloud. MotherDuck allows you to write a single query where one part executes locally on your sensitive data, and the other part executes in the cloud, with the results seamlessly combined. It's the best of both worlds.

Conclusion: A New Default for Your Data Stack

By pairing the lightning-fast, in-process power of DuckDB with the robust framework of dbt, we've solved the problems of speed, cost, and complexity for local data transformation. That monstrous pipeline that once cost tens of thousands of euros on Spark and lived on tape archives now runs on a laptop in the blink of an eye.

DuckDB is pushing the boundaries of what's possible on a single machine, making it the new default for interactive analysis and local development pipelines.

Ready to try it yourself?

1. Get started: Install dbt-duckdb and run through the tutorial.
2. Explore: Check out the official DuckDB documentation and the new DuckDB UI.
3. Scale to the cloud: See how you can take your local workflows to the next level with MotherDuck.