Data bases have gotten so good at this, that the term is almost misleading now. “Base” suggests something rigid, without which the data would slip away. But the data is always there, just bits on a nameless hard disk. The structure and the accessibility that a modern database provides exist completely independently from that hard disk. That’s right – most databases no longer have any data in them.

DuckDB is a database built for this age, and a particularly lovely one at that.

Say you run a robotaxi service. And you’re maintaining a growing dataset of interesting ride patterns in blob storage, that you’d like to understand better. The data is split into a separate Parquet file for each day. How do you share that dataset with an analyst?

You could just mail them everything, but the dataset is getting way too big for that. Plus it’d be out of date instantly. If this was a blogpost you would just send a link, but there isn’t really a single thing to link to here. And sending someone a hundred links to raw blobs in S3 can sour any working relationship.

Instead you whip up a little database, just for them:

# Send

import duckdb

db = duckdb.connect("weird_rides.db")

db.sql("""

CREATE VIEW weird_rides

AS SELECT pickup_at, dropoff_at, trip_distance, total_amount

FROM 's3://robotaxi-inc/daily-ride-data/*.parquet'

WHERE fare_amount > 100 AND trip_distance < 10.0

""")

db.close()

This produces a tiny file called weird_rides.db . It contains none of the actual data. What it does contain are the above instructions for how to work with this pile of blobs, as if it were a single table of relevant data points.

You publish this database file to blob storage as well, right next to your data. Now you have a single thing to link to: s3://robotaxi-inc/virtual-datasets/weird_rides.db .

Instead of opening a web browser, the receipient of the link starts their own local DuckDB session, and attaches to the referenced database.

# Receive

import duckdb

conn = duckdb.connect()

conn.sql("""

ATTACH 's3://robotaxi-inc/virtual-datasets/weird_rides.db'

AS rides_db (READ_ONLY)

""")

conn.sql("SELECT * FROM rides_db.weird_rides LIMIT 5")

# ┌─────────────────────┬─────────────────────┬───────────────┬──────────────┐

# │ pickup_at │ dropoff_at │ trip_distance │ total_amount │

# │ timestamp │ timestamp │ float │ float │

# ├─────────────────────┼─────────────────────┼───────────────┼──────────────┤

# │ 2019-04-01 00:03:20 │ 2019-04-01 00:03:54 │ 0.0 │ 240.35 │

# │ 2019-04-01 00:16:16 │ 2019-04-01 00:16:21 │ 0.0 │ 138.36 │

# │ 2019-04-01 02:01:22 │ 2019-04-01 02:01:28 │ 0.0 │ 192.96 │

# │ 2019-04-01 06:26:44 │ 2019-04-01 06:27:14 │ 0.0 │ 115.3 │

# │ 2019-04-01 07:28:12 │ 2019-04-01 07:28:12 │ 0.0 │ 127.2 │

# └─────────────────────┴─────────────────────┴───────────────┴──────────────┘

This query is the first time that any data has to be downloaded from S3. DuckDB supports partial reading, which means that only the columns used in the definition of the weird_rides view have to be fetched, and that filters like fare_amount > 100 can be used to discard even more irrelevant data during the download.

From the perspective of the receiver, this way of accessing the data will remain working unchanged, almost no matter what happens to the underlying data. Changes in format, different partitioning strategies, schema changes – through all of it the receiver’s view remains the same.

from DuckDB Doesn’t Need Data To Be a Database