Datawise
My Data Journey

Hands-on with DuckDB

Constantin LunguUpdated 2 min read

Photo by Andrew Wulf on Unsplash

The right tool for the right job, right?

When developing a basic application or testing a concept, an option could be SQLite. It's a self-contained database system that offers a SQL interface and conveniently stores data in a file on your device. Perfect for straightforward tasks.

But what if you need an OLAP solution? There's DuckDB. This in-process DBMS is great for extracting, processing, and analyzing data. It can even support an analytical application you're working on. It's also super-fast.

I've had the pleasure of using DuckDB to establish a Data Warehouse for a quick proof of concept [link in comments]. Pairing it with Apache Superset was interesting – a robust BI toolkit right there.

Many analytical workloads don't demand the vast scale of cloud data warehouses like BigQuery or Snowflake. DuckDB offers a simple yet very powerful alternative, making analytics more accessible to all.

How easy is it to use it? Simply download an executable and start querying your files.

CREATE TABLE ecommerce_demo AS  SELECT * FROM './repos/duckdb-demo/export.csv';
SHOW ecommerce_demo;

Terminal output: SHOW ecommerce_demo lists 16 columns with their inferred types, all nullable: id, f_orderquantity, f_employeeid, f_storeid, f_vatrateid, f_productid, f_campaignid and created_by BIGINT; f_productprice, f_NetAmountEUR and f_GrossAmountEUR DOUBLE; f_orderdate TIMESTAMP; is_active, created_on and modified_on VARCHAR; modified_by BIGINT.

Where can you use it? In the console, using a host of programming languages, and more recently in the cloud, with Motherduck, currently in beta, a new offering also based on DuckDB.

WITH input_data AS
(
SELECT
    CAST(f_orderdate AS DATE) AS order_date,
    SUM(f_NetAmountEUR) AS sum_net_amount,
    SUM(f_GrossAmountEUR) AS sum_gross_amount
FROM main.ecommerce_demo
GROUP BY
    CAST(f_orderdate AS DATE)
)

SELECT
    order_date,
    sum_net_amount,
    sum_gross_amount
FROM input_data;

DuckDB results in DBeaver: order_date with daily sum_net_amount and sum_gross_amount, starting 2014-01-01 at 398,607 and 483,941, then roughly 1.1 to 1.2 million net and 1.4 to 1.5 million gross per day through 2014-01-23.

WITH input_data AS
(
SELECT
    CAST(f_orderdate AS DATE) AS order_date,
    SUM(f_NetAmountEUR) AS sum_net_amount,
    SUM(f_GrossAmountEUR) AS sum_gross_amount
FROM export
GROUP BY
    CAST(f_orderdate AS DATE)
)

SELECT
    order_date,
    sum_net_amount,
    sum_gross_amount
FROM input_data;

MotherDuck results: the query ran in 2.27 s and returned 730 rows of order_date, sum_net_amount and sum_gross_amount, with a histogram over each amount column; the first row is Fri Oct 10 2014 with 1,155,998.00 net and 1,404,026.00 gross.

SELECT COUNT(1) FROM main."export";

MotherDuck results: the count query ran in 2.53 s and returned count(1) 9305031.

Where do I see it applicable? Useful to DAs, DEs, and even those who only get started. One-off analysis, exploratory data analysis, super-fast DW for small-to-medium analytical apps, and prototypes. Anywhere else you might use a pop-up OLAP database. Further down, perhaps separate queues for workloads based on their size.

In the example below I'm using it to query a sample 9.3M row CSV file.

Spreadsheet view of the sample e-commerce CSV used with DuckDB, with columns id, f_orderquantity, f_productprice, f_employeeid, f_storeid, f_vatrateid, f_orderdate, f_productid, f_campaignid, f_NetAmountEUR, f_GrossAmountEUR and audit columns.

Since competition in the analytics space is a driving force for progress, I'm enthusiastic about what's to come next. Hopefully, further democratization of this space can happen, so that even smaller companies are empowered by analytics.