duckdb.mk is a minimal, highly opinionated build system for local DuckDB
projects, inspired by dbt and implemented as a short
Makefile. Write interlinked SELECT queries in an organised directory of SQL
files; duckdb.mk parses them into a dependency graph using DuckDB's
json_serialize_sql function and builds a corresponding directory of Parquet
tables.
DuckDB and Make are both wonderful 'Swiss army knife' tools -- together, they make it extremely easy to build multi-stage data pipelines querying local and external sources, e.g. for research or analytical dashboards. duckdb.mk is a formalisation of an ad hoc approach that I've developed over many years of working on data journalism and research projects at NGOs.
See example/ for a duckdb.mk project that analyses methane
emissions using public data and REFERENCE.md for the full
interface.
- dbt with
dbt-duckdb: a Python install, project
and profile configuration, and ref()in each query. It has many more features, such as incremental models, snapshots and generated documentation.
- SQLMesh: also infers dependencies from SQL, and adds plans, virtual environments and column-level lineage. It is a larger Python framework.
- A hand-written Makefile: works well, but you maintain each dependency by hand. duckdb.mk generates those rules from the SQL.
You need DuckDB 1.4.1 or later, GNU Make 3.81 or later
(the version that ships with macOS works) and curl.
Include this snippet at the top of your Makefile to install duckdb.mk v0.1.1
on next build; set DUCKDB_MK to a newer tag, or main, to upgrade. Then
write a SELECT query as a .sql file under models/ and run make to
generate a table.
DUCKDB_MK = v0.1.1
include .duckdb.mk/$(DUCKDB_MK)/duckdb.mk
.duckdb.mk/%/duckdb.mk:
curl -sSfL --create-dirs -o $@ \
https://raw.githubusercontent.com/ltrgoddard/duckdb.mk/$*/duckdb.mkTwo models, one referring to the other by name:
-- models/staging/orders.sql
select * from read_csv('data/orders.csv')
-- models/revenue.sql
select customer, sum(amount) as total
from staging.orders
group by customer$ make
build/staging/orders.parquet: 3 rows
build/revenue.parquet: 2 rows
$ make
make: Nothing to be done for `all'.duckdb.mk implements the four features that represent 99% of my own dbt use:
- Individual SELECTqueries map one-to-one to output tables
- A query in one file can refer to the output table from another
- Tables are rebuilt like Make targets, including their dependencies
- Settings can be controlled through project-level variables
Unlike dbt, duckdb.mk requires no configuration or hand-maintained metadata:
table and schema names are derived from filenames and directories, while
internal references are resolved automatically (no need for dbt's
ref("model")).
On each run, DuckDB parses every model and test with json_serialize_sql. One
SQL query over the resulting syntax trees finds table references, file paths,
URLs and getenv calls, and writes them as Make rules to build/plan.mk. Make
includes that file and rebuilds only the tables whose inputs are newer. Each
Parquet file also stores a fingerprint of its query, remote sources and
environment variables. Make rechecks remote data and settings on every run,
but rebuilds a table only when that fingerprint changes.
This project does not aim to implement the 'full fat' features of dbt like incremental rebuilds, automated documentation and tight integration with remote data warehouses. It's aimed at individual data engineers and small teams who want to build neat, reproducible data pipelines that run on a single machine.
duckdb.mk is delivered as a single ~250-line file, which is mostly SQL.
The recommended way to deploy it is to include it in your project's existing
Makefile, alongside variable definitions and additional targets for things
DuckDB can't do (e.g. downloading and extracting complex source data). For
simple projects using only DuckDB features and passing variables at build time,
the file can be renamed to Makefile and used directly.
SQL queries representing tables are stored in a models/ directory, with
subdirectories representing (and naming) schemas. Each table definition is a
.sql file containing a single SELECT statement, which can refer freely to
other schemas and tables in the collection by name.
Tests are stored in tests/ and macros -- SQL run before every model statement
-- in macros/. Built Parquet tables are stored in build/ organised by
schema. data/ is conventionally used for downloaded source data, but this
location is optional and sources are often remote.
Build tables in the normal Make way, by running make build/schema/table.parquet. duckdb.mk will take care of dependencies,
including checking modification dates of local and remote resources to save on
full rebuilds and ensure fresh data when needed. Each run also writes the
dependency graph to build/dag.mmd as a Mermaid
flowchart.
./test.sh runs the end-to-end tests in test/ in parallel against real make
and duckdb executables (set MAKE and DUCKDB to try others). They cover
rebuilds, dependency resolution, errors, remote sources over HTTP and S3, a
pipeline over millions of generated rows and a random 300-model project checked
against an independent model of its dependency graph. ./bench.sh [git ref ...]
times the working copy against earlier versions.
Contributions -- bug fixes, ergonomics, new features -- are encouraged. Please file an issue in the first instance.