DuckLake: The Lakehouse That's Just SQL and Parquet

0:00

01:11:09

Links Episode Deep Dive Transcript

Panelists

How many files does your query read before it reads any data? On some data lakes, you go through JSON and metadata files first, just to learn which Parquet files matter. DuckLake asks one SQL question instead. The metadata lives in a real database. The data stays in plain Parquet. That's the entire format.

Pedro Holanda joined DuckDB in 2018, when it was still a research prototype at CWI. He's the lead DuckLake developer. Guillermo Sanchez Dionis works on DuckLake and the new Quack protocol.

With Quack as the catalog, DuckLake handles 200 transactions a second under heavy contention. No other open table format comes close.

Play on YouTube

Watch the live stream version

Episode Deep Dive

Guests Introduction and Background

Pedro Holanda is the lead DuckLake developer at DuckDB Labs. He moved to the Netherlands about nine years ago to do a PhD in the Database Architectures group at CWI (Centrum Wiskunde & Informatica), the national research institute where DuckDB was born. That is where he met DuckDB's co-creators, Hannes Mühleisen and Mark Raasveldt. He was Mark's roommate and Hannes was his co-supervisor, so the connection predates DuckDB itself. His thesis, "Progressive Indexes," explored building an index incrementally while queries run instead of paying for it all up front, and it includes a chapter called "The Elephants in the Room" where he critiques his own work. Near the end of the PhD, around 2018, he asked Mark and Hannes if he could hack on their research prototype, and the first thing he built was the CSV reader. He was effectively the first hire after the two founders, on a team that has since grown to roughly 35 people. For the past year he has been the main DuckLake developer.

Guillermo Sanchez Dionis came to DuckDB Labs from the other direction. He spent years as a software and data engineer running heavyweight platforms like BigQuery, Databricks, and Snowflake, and grew tired of how much they cost relative to what simpler tools could do. When DuckLake was announced, he heard the announcement podcast with Hannes and Mark, and it matched his own feeling that other open table formats had gotten something slightly wrong. He emailed the founders directly to say he was quitting his job and wanted to work with them. He joined as a developer relations engineer, then moved into a hybrid role doing product management while still contributing to and reviewing code in DuckLake and in Quack, DuckDB's new client-server protocol.

What to Know If You're New to Python

This episode is less about Python syntax and more about the data systems Python programs talk to: SQL databases, columnar files on cloud storage, and the difference between a database that runs inside your process and one that runs as a server. A little background on these ideas will make the conversation click.

SQL and relational databases: SQL is the query language for tables of rows and columns, and Postgres, SQLite, and DuckDB are all databases that speak it. DuckLake's entire format is a few SQL tables and some queries, so knowing what a table, a primary key, and a transaction are gets you most of the way there.
Object storage like S3: Cloud "blob" stores such as Amazon S3, Google Cloud Storage, and Azure Blob Storage hold files cheaply and at enormous scale, but every request has network latency. Much of the episode is about how many round trips a query makes before it reads real data.
Parquet and columnar data: Parquet is a compressed file format that stores data column by column rather than row by row. That layout is why analytics engines like DuckDB are fast, and Parquet is where DuckLake keeps all of its actual data.
In-process versus client-server databases: SQLite and DuckDB run as a library inside your Python program, with no server to install. Postgres runs as a separate server process you connect to over the network. DuckLake can use either kind as its catalog, and the trade-offs come up repeatedly.
DataFrames: pandas and Polars give you an in-memory table you manipulate from Python. The zero-copy trick Pedro describes, where a DuckDB result and a NumPy array can share the same memory, is one of the reasons DuckDB fits so well in the Python data stack.

Key Points and Takeaways

DuckLake's whole idea: metadata in a real database, data in plain Parquet

DuckLake's premise fits in one sentence: the metadata lives in a SQL database and the data stays in plain Parquet files on object storage. Iceberg, the other major open table format, keeps its metadata in files too (JSON, Avro, manifest lists, and manifests), so before an engine can find out which Parquet files matter, it needs three or four different file format readers and several round trips. With DuckLake, the engine asks the catalog one SQL question: which files do I need to read for this table at this snapshot? It gets back a list of files with statistics and reads them. Pedro pointed out that Iceberg and Delta already needed a database anyway to hold the pointer to the latest metadata file, so Mark and Hannes asked, "why is this not just a database?" and moved everything there. The entire format is a handful of tables and some SQL, which is exactly what the specification describes. Because the catalog is a transactional SQL database, ACID guarantees come built in; as Guillermo put it, they did not have to build ACID at all.

Three moving parts: storage, catalog service, and compute

Guillermo broke the architecture into three pieces. Storage is any object store, typically S3, GCS, or Azure Blob Storage, and in DuckLake it holds only Parquet files, with no metadata files at all. The catalog service is any SQL database; DuckDB's DuckLake extension can talk to DuckDB itself, SQLite, PostgreSQL, MySQL, or a Quack server. Compute is any engine that can read Parquet and issue the catalog queries: DuckDB is the usual choice, but there is a fairly mature Rust implementation on Apache DataFusion and a Spark connector from MotherDuck. The requirements on the catalog database are small, just timestamp, varchar, and integer types plus primary keys, so a distributed database can serve as the catalog if your metadata ever gets that big. A typical production setup is S3 for storage, EC2 for compute, and RDS Postgres for the catalog, though Michael noted that anything S3-compatible, such as DigitalOcean Spaces, works too. Michael also drew a useful distinction: scalable does not mean fast. A design that stays consistent as you add a million users can still be slow for a single query if that query makes twelve S3 round trips before touching data.

Choosing a catalog: DuckDB to start, Postgres in production, Quack for contention

Asked where people should start, both guests said DuckDB in-process, because it is one line and you have a working data lake. The catch is that a DuckDB file allows only one writer at a time, so multiple people cannot write to the lake concurrently through a DuckDB catalog. Postgres removes that limitation and is what Pedro sees most in production. Quack, the client-server DuckDB, already works with DuckLake but is still experimental, and its first stable release ships with DuckDB 2.0. Its big advantage is how it handles retries. When two transactions conflict on a snapshot ID against a Postgres catalog, the conflict bounces back to the application, which has to recompute its snapshot ID and resend its queries, and that round trip is expensive. With Quack, DuckLake is loaded on the server itself, so retries happen there. Pedro's numbers: with about 20 writers under heavy contention, Postgres manages around five transactions per second, while Quack manages around 200. He expects Quack to become the de facto DuckLake catalog, and noted the same trick could in principle be done in Postgres with stored procedures.

Quack: DuckDB grows a client-server protocol

DuckDB has always been an embedded database, and Quack adds a client-server mode so that many processes, local or remote, can share one DuckDB. Trying it takes a few commands in a terminal: install the quack extension, load it, and call quack serve, which spins up a server. From another terminal you connect to that address on localhost and you have a working client-server database. Because Quack runs on plain HTTP, with HTTPS in production, you can host it anywhere you like, for example on EC2 behind a load balancer, without inventing a custom protocol on top of TCP. Guillermo noted that this solves the DuckDB file locking limitation for sharing storage with a broader audience, but by design it puts you back in the client-server world. Guillermo also told the naming story: Quack was Hannes' idea, someone suggested a plainer name like "RPC protocol," and both Guillermo and Gabor, Quack's main developer, refused because the name was too good to pass on.

The "frozen DuckLake": a data lake almost for free

This is one of Guillermo's favorite patterns and it needs no server at all. Use an in-process DuckDB file as the catalog. To update the lake, pull that file to your machine, write new Parquet data straight to S3 through it, then upload the DuckDB catalog file back to S3. From then on, any number of read-only clients can attach to the DuckLake using the catalog file sitting in S3, with no Postgres and no Quack running and nothing to pay for beyond storage. It fits batch workloads well, such as one big load per day. Michael summed it up: the job runs overnight, and in the morning you download the DuckDB file and ask all the questions you want.

Data inlining: small writes go into the catalog, not into tiny Parquet files

The small file problem is a classic data lake headache. Streaming or frequent small inserts produce a pile of tiny data files, and in Iceberg roughly four metadata files per insert on top of that, so reading a snapshot means fetching all of them and paying latency on each. DuckLake already solves the metadata half of that problem; data inlining addresses the data half. Since the catalog is a database that can store tables, small inserts of ten, a hundred, or a thousand rows are written to an inline table inside the catalog, with extra columns recording the snapshot that inserted each row and the snapshot that deleted it. Once enough rows accumulate, they are flushed to a proper Parquet file. Everything stays transactional and snapshot-aware, with no separate streaming tool. Pedro's blog post compares raw Iceberg against raw DuckLake on this workload and lands at roughly a thousand times faster. Michael compared it to Python's file buffering: you do not write every byte straight to disk, and here the disk is an object store far away.

ducklake.select/2026/04/02/data-inlining-in-ducklake

Open table formats are really about not being locked in

Guillermo defined an open table format, increasingly called a lakehouse format (a term he attributes to Databricks), as metadata over Parquet files that lets you query and modify them transactionally. At its simplest it is a pointer to the list of files that make up a table, plus a schema, plus statistics about which files contain which data. Pedro emphasized the openness: both the table format and the file format have public specifications, so anyone can implement a reader and writer. That is what breaks vendor lock-in. If you start on Oracle, migrating away is famously painful, but with an open format you can move from Databricks to Snowflake to your own DuckDB-based solution. Data in Parquet also makes reads easy to distribute across many machines. DuckLake plans to read from and export to Iceberg as well, so nobody ends up locked into DuckLake either.

File sizes, compaction, and keeping a lake healthy over years

File sizes are yours to choose. A target of around 512 MB with sensible row groups is typical for batch writes, and DuckLake respects it. Small writes without inlining produce small files, and engines dislike reading a thousand 5 KB files far more than one half-gigabyte file they can still parallelize. That is why every lakehouse engine offers compaction. Pedro said a big share of the 1.0 effort went into making all of the checkpointing functionality solid and easy to use: compacting small files, rewriting data files to apply their deletions, removing orphan files, and expiring old ones. The goal is that a lake that runs for a long time and keeps growing never grinds to a halt because of its own history.

DuckLake 1.0 and what "production-ready" actually meant

From DuckLake 0.4 to 1.0, the team stopped adding features and only fixed bugs, allowing schema changes only when they fixed a bug. Pedro considers it production-ready as of April. DuckLake 1.1 arrives alongside DuckDB 2.0 in roughly a month and a half, again focused on fixes and smaller optimizations, with deeper Iceberg compatibility as the strategic direction. Guillermo explained the two-speed model: the specification is frozen as much as possible and only gets a new release when the catalog schema truly needs a breaking change, while the DuckDB extension keeps improving independently, for example in how it computes statistics. People ran DuckLake in production before 1.0, which Pedro greeted with "cool, cool, cool, what could go wrong?" Today companies including PostHog, Altertable, and Firebolt build on it, which Guillermo called the biggest testament to something working.

Embedded databases and the zero-copy advantage

Michael opened with the resurgence of embedded databases, and Guillermo said the appeal has always been simple: no separate Postgres server to provision. SQLite solved that for transactional work and DuckDB does it for analytics. His favorite example is DuckDB's WASM build running in the browser against a database served from S3, with no server anywhere. Pedro added the deeper reason it matters for data science: a database inside your process shares your application's memory. A NumPy array is a C array with some makeup on top, and a DuckDB vector is an array with some makeup on top, so you can change the makeup and access the same data at constant cost instead of copying a million floats across a slow database protocol. Moving data from NumPy, pandas, or TensorFlow into a database and back was one of the biggest frustrations the CWI team saw among data scientists. Michael mentioned SQLite's write-ahead log and Litestream, which streams the WAL to S3, as the kind of work that has made embedded databases credible as real application backends.

Columnar storage and vectorized execution, explained with a shop

Pedro's explanation used a table of products, quantities, and prices. A row store keeps each row contiguous, but an analytical query like "average price of all products" only touches one of the three columns, so a column store keeps each column contiguous and reads only what it needs. That layout also enables lightweight compression, since a column of dates that barely change compresses beautifully with delta encoding, while a row that alternates date, double, and string does not. The trade-off is updates: changing one row in a row store is a single random access, while in a column store you first scan a column to find the row and then update values scattered elsewhere. That is the whole reason SQLite is good for transactions and DuckDB for analytics. Columnar layout also unlocks vectorized execution, where batches of data flow through the query plan together and stay in CPU cache, versus SQLite's tuple-at-a-time model from an era when memory was small, which causes constant cache misses. Michael added that with variable-length text in the rows, a row store cannot even skip ahead by a fixed size.

Memory is scarce again, and DuckDB is built to run anywhere

Michael observed that the industry cycles between memory being scarce, then cheap, and now with AI scarce again. Pedro said the database world went through this earlier: the mid-2000s in-memory database wave assumed disk was unnecessary, but memory has limits, and you may want to run on a phone or an Arduino. DuckDB put heavy emphasis on proper buffer managers and operators that spill to disk, so under memory pressure it keeps working rather than crashing. Guillermo pointed to the DuckDB blog series on exotic hardware, from a Raspberry Pi to an iPhone running a large TPC-H workload while cooled in a box of ice, as proof that the out-of-core design holds up under real constraints.

How DuckDB came out of CWI

DuckDB began as a research prototype at CWI, which is a national research institute rather than a university. During their PhDs, Pedro and Mark worked on data science projects with companies and noticed that everyone was running away from database systems toward dataframe and data wrangling tools, and nobody wanted to run a Postgres. Setting up MonetDB or Postgres from source could take Pedro a full day, versus DuckDB booting in half a second and querying in one line. Mark and Hannes talked to data scientists, saw the frustration, and built a database for that gap. People started using it in production before it was even released, which is when the team realized they were onto something. It started as three people, was six within the first year, and is now around 35. Michael covered DuckDB in depth with Alex Monahan back on episode 491.

CSV, the wild west of data formats

Pedro's first job at DuckDB was the CSV reader, he came back to it years later, and he calls it his true passion, or as he also put it, the unsexiest database problem. He wanted to do his PhD on CSV files but was told it was a solved problem. CSV carries almost no type information, and being both fast and tolerant of every weird file in the world is genuinely hard. His favorite war story: he assumed no CSV line would ever exceed 32 MB, which made parallel parsing easier, until a user reported exactly that error. The largest CSV he has personally parsed is close to a terabyte in benchmarks, and he is sure others have gone bigger. As a running joke, he noted that the DuckLake spec technically allows data files other than Parquet, so CSV could in principle make a comeback as the storage layer.

duckdb.org/2025/04/16/duckdb-csv-pollock-benchmark.html

ducklake-dataframe and how easy the format is to implement

While benchmarking data inlining, Pedro found setting up an Iceberg environment so frustrating that he let an AI coding agent write a standalone DuckLake implementation for pandas, Polars, and PySpark instead, with no DuckDB at runtime. He did not review the code closely, but the tests passed and the numbers looked right. He also ran a tongue-in-cheek benchmark changing a table's schema a million times, which Guillermo said is actually a real DuckLake advantage: every schema change in Iceberg writes files and grows metadata that slows later reads, while in DuckLake it is a plain SQL operation. The serious point was that DuckLake is much easier to implement than Iceberg, and that despite its name it is not tied to DuckDB. Other implementations now exist, including a closed-source one at Firebolt, Hotdata's DataFusion implementation, and MotherDuck's Spark connector.

Getting started, and why now is a good time

Both guests pointed to the DuckLake website for examples and tutorials, and Pedro stressed that it is one line to get a data lake working, and one line to get DuckLake with Quack working. Start with DuckDB in-process, since Postgres means setting up a server first. Guillermo encouraged people to try it at whatever scale fits, from a pet project holding personal data to the backbone of a software service. If you hit a bug, open a GitHub issue with a fully reproducible script that generates the data and shows the problem, because the team triages every issue and easier reproduction means faster fixes. Pedro closed with a preview: DuckDB 2.0 ships asynchronous I/O, which will unclog processing when reading files from S3 and make DuckLake much faster.

Interesting Quotes and Stories

The napkin sketch. Pedro held up the original drawing Mark and Hannes made when DuckLake was just an idea: all of Iceberg's metadata files, with a database already sitting at the top because they needed one to point at the latest metadata file. The question written on it was, essentially, why not put all of it in the database? He had them sign it "in case this was a very successful idea," and said it is now for sale for the right price.

Quitting for a podcast. Guillermo heard the DuckLake announcement episode with Hannes and Mark and emailed them that day. "I literally told them, like, I'm quitting my job. I want to work with you."

The CSV reader that never ends. Pedro's first month at DuckDB in 2018 was spent on the CSV reader, and he is still working on readers today. He wanted to do his PhD on CSV but was told it was a solved problem.

"We had people use it in production before DuckLake 1.0, and I was always like, cool, cool, cool. What could go wrong?" -- Pedro Holanda

"What is a NumPy array? It's literally a C array with some makeup on top. What is a DuckDB vector? It's literally an array with some makeup on top. So you can just change the makeup, and then you can suddenly access the same data with constant cost." -- Pedro Holanda

"For us, we didn't have to build any ACID. It was built into the design, basically, because the catalog service is a SQL database that already supports ACID transactions." -- Guillermo Sanchez Dionis

"200 transactions per second in a high contention environment is almost unheard of for any other open table format, because these open table formats are designed for batch processing." -- Guillermo Sanchez Dionis

"Something that's highly scalable doesn't mean it's fast. It just means as you add many, many more users or amounts of data to it, it doesn't get slower. But it could have been kind of slow from the start." -- Michael Kennedy

"You can have a data lake almost for free, because your catalog database is just a file in S3." -- Guillermo Sanchez Dionis

"I call it the wild west of data formats." -- Pedro Holanda, on CSV

"In my thesis, I even have a chapter called 'The Elephants in the Room' where I basically diss all my work." -- Pedro Holanda

"We could theoretically do stored procedures, but I haven't done a stored procedure since college, so that's why I've been going slow on that one." -- Pedro Holanda

"Quack is too good to pass on. We cannot pass on these names." -- Guillermo Sanchez Dionis

"There's a bunch of companies that already trust DuckLake as a thing to build your software on, which for me is the biggest testament of something working." -- Guillermo Sanchez Dionis

"It's literally like one line, and you have a data lake working." -- Pedro Holanda

"I think this is one of the most exciting times to be diving into DuckLake." -- Pedro Holanda

Key Definitions and Terms

Open table format (lakehouse format): A specification for layering table semantics (schema, transactions, snapshots, statistics) over plain data files in object storage. Apache Iceberg, Delta Lake, and DuckLake are examples. Guillermo noted the "lakehouse" name caught on largely because of Databricks.
Catalog: The component that knows which files make up which table, at which version, with what schema. In Iceberg it is a service plus metadata files; in DuckLake it is simply a SQL database holding the metadata tables.
Parquet: A columnar, compressed file format that is the standard for analytical data on disk and in object storage. DuckLake stores all its data as Parquet.
Object storage: Cloud file stores such as Amazon S3, Google Cloud Storage, and Azure Blob Storage. Cheap and massively scalable, but every request carries network latency, which is why round trips matter.
Snapshot: A consistent version of a table at a point in time. Every DuckLake transaction creates a new snapshot, and readers can query a table as of a given snapshot.
Columnar versus row storage: Row stores keep all fields of a record together, which suits point updates. Column stores keep each column together, which suits scanning a few columns over many rows and compresses better.
Vectorized execution: Processing batches of values through the query plan at once, so the working set stays in CPU cache, as opposed to tuple-at-a-time execution.
Out-of-core processing: Running queries on data larger than available memory by spilling to disk through a buffer manager, which is how DuckDB survives on phones and small machines.
Zero-copy: Sharing the same memory between two systems, for instance a DuckDB result and a NumPy array, instead of serializing and copying the data across a protocol boundary.
Small file problem: The performance penalty from many tiny data and metadata files created by frequent small writes, each adding latency and overhead to reads.
Compaction and checkpointing: Maintenance operations that merge small files into large ones, rewrite files to apply deletions, and remove orphaned or expired files so a lake stays fast as it ages.
Data inlining: DuckLake's feature that stores small inserts directly in the catalog database and flushes them to Parquet later, avoiding tiny files while keeping full transactionality.
Frozen DuckLake: A pattern where a DuckDB file acts as the catalog, is updated locally, and is uploaded to S3 so any number of read-only clients can query the lake with no server running.
Write-ahead log (WAL): A log of changes written before the main database file, which SQLite and DuckDB both use; Litestream streams SQLite's WAL to S3 for replication.
Quack: DuckDB's new HTTP-based client-server protocol and extension, allowing multiple processes to share one DuckDB, and usable as a DuckLake catalog with server-side transaction retries.
CWI: Centrum Wiskunde & Informatica, the Dutch national research institute for mathematics and computer science, home of the Database Architectures group where DuckDB originated.

Learning Resources

If this episode has you wanting to go deeper on analytical data in Python, here are resources to keep learning, from the DuckLake documentation itself to hands-on courses on the tools around it.

Polars for Power Users: Polars is one of the dataframe libraries the ducklake-dataframe project targets, and this course takes you from CSV wrangling to lazy evaluation and multi-gigabyte joins with a Rust-powered engine.
Data Science Jumpstart with 10 Projects: Ten hands-on projects with Matt Harrison covering CSV and Excel loading, SQL database integration, and visualization, a solid grounding before pointing DuckDB at real data.
Python for Absolute Beginners: If the Python side of this conversation was the unfamiliar part, this is the place to start, covering the core language from the ground up.
ducklake.select/docs/stable/specification/introduction: The DuckLake specification, which is short enough to read in a sitting and shows how few tables the format really needs.
ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database: The official guidance on when to use DuckDB, SQLite, Postgres, or MySQL as your catalog.
duckdb.org/docs/current/clients/python/overview.html: The DuckDB Python client documentation, the starting point for attaching a DuckLake from Python.
duckdb.org/2026/05/12/quack-remote-protocol: The DuckDB blog post introducing Quack and its client-server model.
ducklake.select/2026/04/02/data-inlining-in-ducklake: Pedro's post on data inlining with the benchmarks discussed in the episode.
talkpython.fm/episodes/show/491: The earlier Talk Python episode on DuckDB with Alex Monahan, a natural companion to this one.

Overall Takeaway

The most striking thing about DuckLake is how little of it there is. Iceberg and Delta set out to avoid running a database, discovered they needed one anyway to make S3 transactional, and then kept the file-based metadata design that the database had made unnecessary. Pedro and Guillermo's team simply finished the thought: put the metadata in a real database, keep the data in plain Parquet, and let ACID, snapshots, schema evolution, and time travel fall out of SQL for free. The result is a format you can read in an afternoon, implement with an AI coding agent in a weekend, and run on anything from a single DuckDB file uploaded to S3 to a Postgres or Quack catalog handling 200 contended transactions per second.

For Python developers and data scientists, the practical message is that a serious data lake no longer requires a serious platform team. One line attaches a DuckLake in DuckDB. A DuckDB file in S3 gives you a "frozen" lake that any number of readers can query with nothing running. And the same zero-copy, in-process design that made DuckDB feel native in a notebook carries through to the lakehouse layer. With DuckLake 1.0 shipped, companies like PostHog betting on it, and DuckDB 2.0 bringing async I/O and a stable Quack, this is a good moment to spin one up, point it at some Parquet, and see how much of your stack you can delete.

Episode Transcript

Collapse transcript

WebVTT format On GitHub

00:00 How many files does your query read before it reads any data?

00:03 On some data lakes, you go through JSON and metadata files first, just to learn which Parquet files actually matter.

00:10 Duck Lake asks one SQL question instead.

00:13 The metadata lives in a real database.

00:15 The data stays in plain Parquet.

00:17 That's the entire format.

00:19 Pedro Holanda joined DuckDB in 2018, and it was still a research prototype at CWI.

00:26 He's the lead Duck Lake developer.

00:28 And Guillermo Sanchez-Doinas works on DuckLake and the new Quack protocol.

00:33 With Quack as the catalog, DuckLake handles 200 transactions per second under heavy contention.

00:39 No other open table format even comes close.

00:42 This is Talk Python To Me, episode 562, recorded August 31st, 2026.

01:06 Welcome to Talk Python To Me, the number one Python podcast for developers and data scientists.

01:11 This is your host, Michael Kennedy. I'm a PSF fellow who's been coding for over 25 years.

01:17 Let's connect on social media.

01:18 You'll find me and Talk Python on Mastodon, Bluesky, and X.

01:22 The social links are all in your show notes.

01:24 You can find over 10 years of past episodes at talkpython.fm.

01:28 And if you want to be part of the show, you can join our recording live streams.

01:32 That's right.

01:32 We live stream the raw uncut version of each episode on YouTube.

01:36 Just visit talkpython.fm/youtube to see the schedule of upcoming events.

01:41 Be sure to subscribe there and press the bell so you'll get notified anytime we're recording.

01:45 This episode is brought to you by Six Feet Up, the Python and AI experts who solve hard

01:51 software problems.

01:52 Whether it's scaling an application, deriving insights from data, or getting results from

01:57 AI, Six Feet Up helps you move forward faster.

02:01 See what's possible with Six Feet Up.

02:03 Visit talkpython.fm/sixfeetup.

02:06 And it's brought to you by us.

02:08 Talk Python and Python Bytes both now have MCP servers.

02:12 Point your AI at 10 plus years of Python episodes, transcripts, and show notes.

02:17 Free.

02:17 Click MCP in the nav at talkpython.fm and at Python Bytes.

02:22 Guys, welcome to the show.

02:24 Awesome to have you here.

02:25 Yeah, awesome to be here.

02:27 Absolutely a pleasure.

02:28 It is a pleasure.

02:30 I'm a big fan of databases.

02:31 I think databases unlock so much potential for software and also data science, especially,

02:39 you know, talking DuckDB side of things.

02:41 But once you really get good with databases, you can just answer questions so well, so quickly.

02:47 And honestly, it kind of makes the other side of programming easy.

02:51 So I'm interested to see what you all are doing with Duck Lake, do some refreshers on DuckDB and all of these things.

02:59 Let's roll.

03:00 Let's do it.

03:01 Let's roll.

03:02 All right.

03:02 Well, Pedro, let's have you kick us off.

03:05 Before we get into that, just to give everyone a bit about your background, introduce yourself.

03:09 All right.

03:10 So, well, my name is Pedro.

03:13 I came to the Netherlands, I think, about nine years ago to do my PhD in the Database Architectures Group at CWI.

03:22 This is also where I met both Hannes and Mark, who are the co-creators of DuckDB.

03:27 I actually used to live together with Mark, and Hannes was also my co-supervisor,

03:34 so the connection has been there pre-DuckDB, in a way.

03:38 And yeah, I did my PhD at CWI and towards the end of my PhD, I had already finished it.

03:45 The book was already being printed.

03:47 I still have six or nine months to go.

03:49 So I just came to market and was like, hey, you guys have this cool research prototype.

03:54 Can I do something in it?

03:56 And basically start nerding out about it and be having tons of fun since then.

04:03 Funny enough, one of the first things I did in DuckDB, I think this is 2018, maybe, like literally the first month that I was in was the CSV reader.

04:12 And I came back to it a few years later.

04:15 And I'm actually currently working on something that's CSV reader as well.

04:18 So it's my true passion, I guess.

04:21 You were born to work on CSV.

04:24 It seems.

04:26 It's also known as the unsexiest database problem.

04:29 But what can I say?

04:31 Well, I think it's a subset of the unsexiest data science problem, but also the biggest is the data wrangling, plant cleaning, all that kind of stuff.

04:41 And CSV is a fairly raw format that doesn't actually communicate a lot of stuff about what types it actually intends that column to be and so on.

04:49 The challenge, of course, not only being efficient about it, but also it's a pretty crazy lens.

04:55 I call it like the Wild West of data formats.

04:58 So being able to actually read these things, it's challenging.

05:02 And I guess, well, also the main reason I've been invited to here is because I've also been working in Duck Lake for the past year.

05:11 Oh, yeah, that.

05:13 I'll dare to say that I'm currently the main Duck Lake developer from our team as well for the past year.

05:19 So I might know a little bit about it.

05:22 Yeah, that is actually why I invited you.

05:24 I mean, CSPs are interesting, don't get me wrong.

05:27 I have a plan of having CSV files replacing parquet in Duck Lake, and then that's a big comeback, you know?

05:33 Oh, yeah.

05:34 Just zipped up compressed CSV.

05:39 Just give it a new extension.

05:40 Nobody will know.

05:41 Yeah, exactly.

05:42 Exactly.

05:43 So it sounds like you were there from early, early days with DuckDB.

05:48 Did it come out of the university there, or was it a project after?

05:54 So it came out from CWI.

05:56 CWA is not actually a university.

05:58 It's, in fact, a research center.

06:01 So this is also kind of a fun thing.

06:02 If you're doing a PhD there, you need to also be affiliated to a university to get a diploma.

06:07 But you don't really step on the university.

06:08 You just stay at the research center.

06:10 But the story goes that we had a bunch of projects with different companies as well during our PhD.

06:17 So I was working with Honda.

06:19 Mark was working with Dottis too.

06:20 And these were more like data science-y projects.

06:23 And one thing that was very clear to us, And Hundes also had a lot of influence in the R community.

06:29 And I think that one thing that was clear to everyone was that everyone was trying to run away

06:34 from database systems, right?

06:35 Like you would see people using data wrangling tools, data frame tools, things like that,

06:40 but no one wanted to use a Postgres or things like similar to that.

06:44 So I think like Mark and Hundes noticed that gap, noticed that like, why are people not using database systems

06:50 and started talking to these data scientists and quickly realized there was a lot of frustration

06:54 in setting things up, running queries.

06:56 Like I remember that during my PhD, anything that I wanted to do with like MonitDB

07:01 or Postgres would take me just like a day to build things from the sources

07:06 and set up a database system.

07:07 And we might take it for granted nowadays that you can just like bootstuckDB up in half a second

07:13 and in one line you are querying something.

07:15 But yeah, like 10 years ago or nine years ago, this was very much not the reality.

07:21 So they realized the gap and started doing this research database system,

07:25 let's say, more of like, yeah, towards academia.

07:29 And I think at some point they had the realization that a lot of people were taking it quite seriously.

07:34 And a lot of people were already trying it.

07:38 This is also the funny thing.

07:40 People try things in production, even though it's not yet to release.

07:44 Same thing with DuckLake.

07:45 We had people use it in production before DuckLake 1.0.

07:48 And I was always like, cool, cool, cool.

07:51 What could go wrong?

07:53 So they basically realized...

07:54 Yeah, but when you see things that are exciting, you're like, this is going to solve our problem, right?

07:59 It's a good sign you're onto something.

08:01 No, no, absolutely.

08:02 I think they were super excited about it when having this realization.

08:08 And same thing for us with Duck Lake.

08:10 It was like, okay, people are taking this seriously enough that they're starting to bet their company on it.

08:15 So that's cool, definitely.

08:18 Always a little bit scary, but cool.

08:20 And yeah, from that on, I think it just took off.

08:23 Initially, I think it was just the three of us, like Mark and Hannes as the creators.

08:28 And I was kind of like the first person to be hired.

08:30 But then it quickly expanded.

08:32 I think in the first year, we're like six people.

08:35 We're now, I think, 35 maybe.

08:39 So yeah, it has been quite a journey.

08:41 Very exciting.

08:42 It's cool to see it gaining traction.

08:44 Now, quickly here, you also worked on progressive indexes.

08:49 Is this for your dissertation?

08:51 If I'm being honest, my plan was to actually work on CSV files during my P2D,

08:56 but I was told that was a solved problem.

08:59 So I went something else, which was progressive indexes.

09:04 The basic gist of progressive indexes is that you can create an index while

09:08 curing the data instead of creating up front and having the downtime and whatnot.

09:14 But it's actually quite difficult to implement it in practice in the database system.

09:19 So in my thesis, I even have a chapter called The Elephants in the Room, where I basically

09:23 diss all my work.

09:25 Now, I point out why it's complicated to implement in production and things that I would personally

09:30 look into if I wanted to still continue in that direction.

09:34 But it was also quite a fun journey.

09:36 And then you have the image of the website, and there's my little cover.

09:41 And my little cover is like a tree eating all these database systems and growing from

09:45 a little tree to a bigger tree.

09:48 It is, isn't it?

09:49 Very cool.

09:51 Guillermo, how about you?

09:52 Welcome to the show.

09:53 Yes.

09:54 Thanks, Michael.

09:55 My background is a little bit different from Pedro.

09:57 I actually was working as a software and data engineer for quite a while before I joined

10:02 Activity.

10:03 And actually, I come from the experience of running some of these beefy systems.

10:09 I think Pedro mentioned the Monet TV or Postgres, but also the BigQuery, Statorix, Snowflakes,

10:15 which is like, let's say, a second generation kind of system where the experience is quite good, but the cost is quite expensive.

10:23 And, I mean, one of the things actually that prompted me to join MDD was that Tag Lake was released, actually.

10:31 And I listened to this podcast that announced Tag Lake with Hannes and Mark.

10:39 And, yeah, just a lot of it really resonated with what I thought was maybe

10:45 slightly wrong from other OpenTable formats.

10:50 I just really like the simplicity of it.

10:51 I was a fan of DuckDB for quite a while now because if you're interested in reducing the cost of your stack,

10:57 it was probably the best tool out there by far.

11:00 So then I actually wrote an email to Hannes and Mark directly, saying like, hey, I want to work with you guys.

11:07 Yeah, I initially joined as a DevRel, actually, and then got ready to work in DuckLake together with Pedro.

11:14 But then as time evolved, I became like this kind of like hybrid role where I was a bit of like doing product management things.

11:21 And also I contribute to Daclex sometimes and also to Quack, which is our new client server protocol.

11:26 So we're not only embedded anymore.

11:29 But yeah, so that's kind of the history of how I got to Daclabs.

11:34 Yeah. If someone reaches out to you and says, I heard you talk about this and I believe in your mission.

11:38 Yeah, that's a pretty big endorsement.

11:40 So I can see why they're like, yeah, you have to come work for us.

11:42 Yeah, for sure.

11:43 I mean, I literally told them, like, I'm quitting my job.

11:47 I want to work with you, you know?

11:48 I think it's actually pretty cool what you're doing.

11:51 Hadoop, it's done to me.

11:52 Spark, never.

11:54 No, I was already kind of, I think, done with those technologies.

11:58 Because the things that, for example, if you have Databricks as a platform, right?

12:04 Like, it's such a large thing that, for sure, they give you, like, a lot of, like, nice services.

12:09 Like, you don't have to worry about things.

12:11 And it's serverless.

12:12 but it's also so expensive.

12:13 And I was thinking like, there's so much better things out there that you can do,

12:18 that you can run yourself, right?

12:20 Like if you're interested in this, this is like DacLig and DacDB feel like

12:23 foundational things that you can run any software on.

12:27 And I think this got me really excited.

12:29 You know, in software and deploying systems and database, there's these things that just layer and layer

12:35 until they just get so complicated.

12:38 And then there's always this alternative, like, well, what if we just did a simple thing?

12:41 You know, if we could just make it not so complicated, would it still be working?

12:45 If it did, how amazing would that be?

12:47 So when you say cost, are we talking like operational costs, licensing costs?

12:52 You know, do I need a large cluster of machines so there's always a quorum to vote on different things for durability?

13:00 Or what do you mean by cost?

13:02 I mean, cost is actually, let's say, the service cost that, for example, platforms like Databricks or Snowflake, you know, charge you to use infrastructure that they run, right?

13:14 Because obviously Snowflake and Databricks, they run on AWS or Google Cloud, depending.

13:18 You can even choose your deployment type.

13:20 But obviously, because they provide the service, but they still have to pay for the infrastructure.

13:24 The service is basically something on top of that infrastructure.

13:27 And they decide how much it is, basically.

13:30 And because they're pretty much the bigger players in the market together with existing cloud vendors like GCP, AWS and Azure, they charge quite steep.

13:41 And I mean, of course, like the service is nice.

13:44 Like you don't have to worry about things, right?

13:46 You don't have to worry about downtime and all these things.

13:48 And for sure, they handle the replication and everything that you need.

13:52 But still, though, yeah, if you want control over your stack, this is like a very high level of abstraction that they are offering.

14:01 Yeah.

14:02 You also mentioned open table formats, and that's going to become relevant to our Duck Lake side of things.

14:09 More generally, what are open table formats?

14:11 Yeah.

14:12 So, I mean, they used to be called open table formats.

14:15 And then I think people are leaning more towards this lake house format name, which I'm not sure why.

14:23 I think it's actually maybe because of data rigs, because I think they started calling it the lake house.

14:28 And then everybody's like, yeah, the lake house.

14:31 But yeah, OpenTable formats are basically, I think Pedro correct me if I'm wrong,

14:36 but it's basically just some metadata on some parquet files that you can query in a transactional manner.

14:45 And you can do operations also, atomic operations on this data as well.

14:50 In the most basic case, it's just basically a pointer, sorry, a metadata file with a pointer to a list of files

14:59 that all of those files form part of a table.

15:03 And you can also have things like a schema to it, like what data types do you expect from this table?

15:09 And you can add more complex things like statistics, like, you know, which files contain which data.

15:16 And it can get obviously a lot more complex than that, but that's sort of the basics, I think.

15:20 Yeah, and I think like the interesting thing of the OpenTable formats is, of course,

15:26 the openness part of it, right?

15:27 So they have a specification, the file formats where your data is stored,

15:31 they also have a specification.

15:33 So by just reading the specification, anyone should be able to implement a reader

15:38 and writer to that format, right?

15:39 So the whole idea of this, or maybe the beauty of this, is that it should be able to solve the problem

15:46 where you're locked in into a certain system, right?

15:49 So you suddenly don't need, like if you start off your database from Oracle, for example,

15:54 you're kind of locked in.

15:56 Like doing an Oracle migration is known to be painful, to be costly, so you're going to be using,

16:01 you're going to be giving Larry Edson a second island in Hawaii in the next few years, that's fine.

16:07 But then if you actually use an open table formats, you kind of solve that problem, right?

16:11 It's like you can literally switch from different engines and different providers.

16:15 And if you're tired of Databricks and now you want to use NoFlake or now you want to use your own solution based on DuckDB, you can theoretically quickly jump from these, right?

16:25 And it's also super scalable because if you're just dealing with file formats or like a catalog as well, but your data is in parquet files, you can easily distribute your reads on it.

16:38 So there's quite some cleverness behind it.

16:43 This portion of Talk Python To Me is brought to you by Six Feet Up.

16:46 Let me ask you a question.

16:48 What's stopping you?

16:49 Maybe it's an application that won't scale or an AI initiative that just isn't delivering.

16:54 That's where Six Feet Up comes in.

16:56 With deep expertise in Python and AI, they solve hard software problems,

17:02 modernize platforms, and get teams to market faster.

17:05 These folks have been doing Python since version one.

17:07 They know the frameworks and ecosystems like the back of their hands.

17:11 Six Feet Up's impact speaks for itself.

17:14 Automated healthcare pipelines for hospitals, helping NASA explore Pluto,

17:18 building severe weather prediction tools, and applying AI to connect farmers with vital crop data.

17:24 When the stakes are high and the problems are hard, Six Feet Up is the partner that delivers.

17:29 See what's possible with Six Feet Up.

17:31 Visit talkpython.fm/sixfeetup.

17:34 The link is on the episode page and in your podcast player's show notes.

17:37 Thanks to Six Feet Up for sponsoring the show.

17:40 The examples I've seen are like a whole bunch of Parquet files on S3 or Azure Blob Storage or something like that.

17:47 And then some other file may be also stored there.

17:50 that you can say, well, let's read the metadata file that describes what files are here.

17:55 But I mean, your dream could be back.

17:57 It could be zipped up CSV files stored up there with some metadata, right?

18:02 So of course in Duck Lake, so maybe one quick thing is that Iceberg, which is, I guess, the other main open table formats,

18:10 it uses files all over, right?

18:12 So you have your files in the metadata and then your files that actually store the data.

18:18 In Duck Lake, we'll talk a bit more about this.

18:20 but the metadata is actually a database system and your files are still parquet files.

18:25 So there's nothing stopping us from a technical point of view to actually replacing these files with CSV files.

18:31 From the Duck Lake spec, that's actually already allowed because we can store in the catalog that these are CSV files

18:38 and we could theoretically bring the stats necessary.

18:41 So it is an achievable dream.

18:44 Exactly. The dream of the CSVs are still alive.

18:48 All right, really quickly.

18:49 I don't know I'm on the CSV kick, but what's the largest CSV you've ever parsed?

18:54 Oof.

18:55 Personally, I think I've reached close to a terabytes on benchmarks.

19:02 But I've seen people, like, you know, people, they, there were many times in CSV land there

19:07 was like, yeah, this sounds like a rather reasonable limitation.

19:11 No one's ever going to do something.

19:13 I think I had a limitation, for example, that no one would ever have a line in a CSV file

19:17 that was over 32 megabytes.

19:19 And that was kind of nice for me to be able to do parallelism.

19:22 And then, of course, some guy, hey, this CSV is throwing an error for me now,

19:28 saying that my line is over 32 megabytes.

19:30 I'm like, Jesus Christ, man.

19:32 What are they storing in there?

19:33 Like a book per line?

19:36 Yeah.

19:37 So if I've done a terabyte, I think some people have gone a bit crazier.

19:43 Let's jump into our topic here a bit.

19:45 And I want to work our way, sort of do a little bit of talking about DuckDB.

19:50 And you talked about Quack.

19:54 In the early days, DuckDB was an embedded database.

19:58 You know, the most common one of these is SQLite.

20:01 And Postgres is all the rage, obviously.

20:04 Others as well.

20:04 But Postgres certainly seems to be taking over a lot of the database side of things.

20:09 But I think there's still a bit of a resurgence of embedded databases.

20:13 And I think DuckDB is an example of that interest, right?

20:17 So maybe just you guys give me your thoughts on SQLite and some of these other embedded database ideas.

20:23 I just wanted to mention that I think Pedro can give more research background on this,

20:30 but I think from a user perspective, I think the idea of having your database within your application system

20:35 was always very attractive to me, right?

20:37 I think Pedro mentioned this at the beginning.

20:39 It's very nice to not have to provision a Postgres database.

20:43 or any other type of database on a separate server to run your application.

20:48 And I think SQL lights off this wonderfully.

20:50 And of course, when DuckDB came by, it was also with the idea to solve this from an analytical database perspective.

20:57 But of course, DuckDB can also do transactions, right?

20:59 Pedro can talk about it if he wants.

21:01 But this is also very nice, right?

21:03 But you can still run transactions in DuckDB.

21:05 But overall, I would just be very happy with the idea of being able to embed your database system wherever your application is.

21:12 because I think there's a lot of use cases where this is just good enough.

21:15 And actually, one of the coolest use cases I think that DuckDB brings to the table

21:20 is the fact that you can use a Wasm client in the browser and serve your database over S3,

21:27 and literally there's no server, and you have an application that can query data from your browser.

21:32 And I think that's amazing, right?

21:33 It reduces the costs completely, because S3 storage is very, very cheap.

21:38 That's really incredible.

21:40 There's just so much operational simplicity if you can make these things work, right?

21:45 If you make these embedded databases work, right?

21:47 It's just like, wow, there's nothing to go down.

21:50 There's no like, oh, this server needs that security.

21:55 But then, you know, it's all those things to juggle, right?

21:58 Just it's a file.

22:00 It's in process.

22:01 You end up eliminating like a lot of this complexity of keeping a server for sure.

22:06 And there are other beauties to it as well, right?

22:09 because you suddenly have your database running within your application.

22:14 So it also can share the same memory space of your application.

22:18 So you can do a bunch of tricks to avoid copying memory all over.

22:23 So this is especially interesting for data science projects because if you're using something like Pandas or NumPy,

22:30 what is a NumPy array?

22:31 It's literally a C array with some makeup on top.

22:34 What is a DuckDB vector is literally an array with some makeup on top.

22:39 So you can just change the makeup and then you can suddenly access the same data with constant cost, right?

22:45 You don't have to transform actual data.

22:46 Oh, that's interesting.

22:47 So when you do a query, you may be able to just return a piece of the in-memory WDB chunk instead of going,

22:55 okay, ours looks like this, but then we're going to copy a million floats over to this thing in this column and then send it back, right?

23:01 So this is what was also like one of the things that was one of the realizations that database protocols, right?

23:08 So the way you transfer data from the server to the clients, they're actually quite slow.

23:13 So this is one of the main frustrations we had seen with the data scientists.

23:17 It's not only like, oh, it's clumsy to set it up and you'd like to start a server and create schemas and whatnot,

23:23 but it's also just to get your data from your NumPy or TensorFlow or Pandas or whatever you're running into the database system and back and forth was super slow.

23:33 So you completely remove that boundary.

23:38 And yeah, like you gain all these benefits, which is inspired and very similar to what

23:42 SQLite already had.

23:44 But of course, with the difference that there's a huge focus on analytics.

23:48 So it's a color format instead of a row format.

23:51 There's a lot of emphasis in compression and vectorized execution and so on.

23:55 I think probably people out there listening, they're like, wait a minute, columnar, row

23:59 format, what does this mean?

24:01 Yeah.

24:02 Give us a bit more detail there.

24:03 Absolutely.

24:04 So, well, there's basically two ways that you can store there, right?

24:08 So your data is a table.

24:10 Say that is a, I don't know, a table that sells products, right?

24:13 So you have products, quantity in your shop and price.

24:17 One of the ways you can store exactly in your memory is contiguous row by row, right?

24:22 So you store first the first row, then the second row, so on and so forth.

24:26 However, analytical queries, they're usually of the type of, give me the average price of all your products.

24:33 So in practice, even though you have three columns, you only really want to access one column, right?

24:38 So on the color formats, you actually store both in memory and on your storage, the columns first contiguously.

24:46 So if you want to access one column, you don't have to read basically all your table,

24:50 but you know exactly from your memory or your disk where you have to read just to access that column.

24:57 And the other nice benefits from that is that lightweight compression is heavily dependent

25:02 on the proximity of your data, right?

25:05 So it's much easier, for example, to compress one column of dates.

25:09 You can maybe do like delta compression quite easily because dates tend to be the same for a long time

25:13 and then they just change one by one.

25:15 They have to do that if it's in a row, right?

25:17 Because the next value of a row is not going to be a date.

25:20 it's going to be a double or it's going to be a string or whatever it is.

25:24 So this change in formats allows you to access your data much faster with the disadvantage that if you want to update one of your rows,

25:34 it's a bit more expensive, right?

25:36 Because again, if you have the row formats and you want to now update the price of your Volkswagen cars or whatever,

25:43 you just need to fetch that one tuple and you immediately can update that.

25:47 So it's one random accessing kind of done.

25:50 If you are on a color format, you're first going to have to check that one whole column to figure out where are your Volkswagen cars and then go to another part of your data to figure out where the price is and update that value.

26:02 So it's a bit more costly.

26:04 And that's why people say that SQLite is good for transactions and transactions in the sense that it updates one row every now and then.

26:10 And DuckDB is great for analytics, which is, again, this type of query that reads huge checks of your data, but like fewer columns instead of lots of columns.

26:21 That's a really good explanation.

26:22 Yeah, I can just see if you want to query, you know, take the average of all of the prices.

26:29 You've got these rows.

26:29 You basically got to seek over every row through the entire database to get those things.

26:35 And if you have a varchar sort of thing, then it's even harder because you're not even skipping known links.

26:41 You've got to compute, well, okay, where is the price in this particular row?

26:46 And it's just very different.

26:47 And maybe just to add, the color format also allows you to do vectorized query execution.

26:54 The basic idea of vectorized query execution is that you can actually have batches of your data going through your query plan in one go, right?

27:04 unless you have a pipeline breaker, like a join or something like this.

27:07 But usually you can go through the whole query in one go.

27:10 And because you have these batches are sufficiently small, they just get cached on your CPU

27:15 and then you don't have to constantly go through memory to fetch the data anymore.

27:19 While in a tuple-wise execution, which is like what SQLite does, and it was created more like when memory was small,

27:26 so you wanted to just have a little bit of your data in memory, like from the 90s.

27:31 You just go and execute through the query plan, tuple by tuple, right?

27:35 So this caused a bunch of cache misses.

27:37 You frequently have to go to memory to fetch the next tuple and so on.

27:40 So that also allows that extra step that makes a huge difference in analytical performance.

27:46 It's funny.

27:47 It used to be things were optimized because memory was expensive in terms of it was scarce, right?

27:53 It was hard to get enough memory to handle all the data that you're working with in the 90s.

27:58 And people have heard of like third normal form and normalization.

28:01 all of this is we must not waste the memory, right?

28:06 I mean, other reasons as well, but still we must not waste the memory.

28:09 And then it got kind of cheap.

28:10 We were able to use it.

28:11 And now with AI, we're back to memory is scarce again.

28:14 How about that?

28:16 It's actually for databases, the cycle has started a bit earlier because I think like in the mid 2000s,

28:22 there was like, oh, we don't need disk.

28:24 We can have our database.

28:26 It was the in-memory database systems, right?

28:27 Just everything in memory and it's all good.

28:30 And then you can map, Like just let the OS handle whatever needs to go to disk every now and then.

28:34 But it's going to be good.

28:36 And the reality is that in this, memory has increased drastically.

28:40 But there's still lots of limitations.

28:42 Maybe you want to run things on your phone or like in an Arduino or something like this.

28:47 And that's also inductive view.

28:48 We put a lot of emphasis in having proper buffer managers.

28:51 And like all our operators built to disk as well.

28:54 So even in scenarios that you still have memory constraints, the tool will just work.

28:59 Like it's not going to crash.

29:00 They'd be like, oh yeah, there's no memory buy.

29:02 Because that's also a little bit frustrating.

29:04 It definitely is.

29:05 And just the problems people are trying to solve.

29:07 It's like, yeah, we have 10 terabytes of data.

29:10 Oh, we didn't used to have that much data typically.

29:13 That actually one of the parts that I love the most about our website is we have like

29:18 this series of blog posts that run on exotic hardware.

29:22 And basically, you know, this can be a Raspberry Pi, but it can also be an iPhone, right?

29:26 And we try to push it to see where's the limit.

29:29 there's this very nice image of an iPhone cooling down in a block of ice

29:34 because we're running the scale factor.

29:36 I don't know if it's 100 or even one terabyte on an iPhone 15.

29:40 Yeah, so I think that's also a very cool thing about DacTV, that it truly can run anywhere

29:45 and that it doesn't matter what constraints it has, it can still run queries with this out-of-core processing

29:51 that it has and the ability to spill to these.

29:54 This portion of Talk Python To Me is brought to you by our AI tools.

29:57 You know that thing where you ask an AI something about Python and it confidently tells you

30:02 about a library version from 18 months ago?

30:05 Well, we fixed that, at least for our shows.

30:07 Talk Python and Python Bytes both have MCP servers now.

30:11 Connect Talk Python and your AI can search over 550 episodes, full transcripts,

30:17 every guest in the entire course catalog.

30:19 Connect Python Bytes and you get almost 500 episodes of Python news going back to 2026,

30:25 including every link we've ever put in the show notes.

30:28 That means you can say things like, ask Talk Python what astral joining OpenAI means for uv,

30:34 or what has Python Bytes said about uv?

30:37 And get real answers with real links, not hallucination.

30:40 Name the show in your prompt, and your AI knows exactly where to look.

30:44 And if you live in the terminal, Talk Python also has a CLI too.

30:48 One line, uvtoolinstalltalk-python-cli, and then search episodes, transcripts, guests, and courses without ever opening a browser.

30:58 It's open source, and it outputs text, JSON, and Markdown, so it feeds the AI tools that don't speak MCP yet.

31:05 And here's the real reason I built it.

31:07 Both shows cover around 10 years of Python history.

31:10 The people, the decisions, the packages that took over, and the ones that quietly didn't.

31:16 This enhanced access is free.

31:18 No account, no API key, nothing to buy.

31:21 This history should belong to all of us.

31:24 Visit talkpython.fm and Python Bytes and click the MCP link in the nav bar.

31:29 Connect them right now to your agents so that they'll be accessible anytime you need them in the future.

31:35 There's also some stuff that's been happening to make running these as real backends for apps better.

31:42 On the SQLite side, we've got the wall or the write-ahead log that allows you to have a lot less locking.

31:49 We've got Lightstream, which will basically build on top of that and stream constant replication to an S3 bucket.

31:57 What's the story with DuckDB on that kind of stuff?

31:59 I mean, you talked about Quack.

32:01 That sort of DuckDB gets to be a little bit more grown up and distributed, right?

32:05 Yeah, I mean, for sure.

32:08 I think the DuckDB experience was always very nice to run on a laptop, right?

32:13 or to basically if you wanted to run an ETL pipeline, let's say, on an EC2 machine,

32:20 DacDB was a great tool.

32:22 But I think at least with the original DacDB file formats, there was this sort of limitation around, you know,

32:31 one writer grabbing the log over the file and then no readers can connect to it.

32:36 And I think that is not a limitation, of course, if you're running DacDB in your laptop,

32:41 But it is a limitation if you want to share a DAGDB storage with a broader audience.

32:48 Quack, indeed, is something that solves this problem, right?

32:53 But then that means, of course, that you go into the client server kind of architecture.

32:57 I think SQLite does this a little bit different, where even on a single machine with a single file,

33:04 you can still have writers and readers at the same time.

33:08 But correct me if I'm wrong, I'm not sure about this.

33:11 putting you on the spot.

33:13 You're not a SQLite expert.

33:16 I think so with the I think with the writer headlock, it does log rather. I think it does

33:21 allow multiple writers to different parts of the database, but there's still some locking.

33:27 We have the same. We also have a writer headlock, but probably they have a different mechanism

33:31 that allows for multiple writers at the same time.

33:34 I mean, DictDB does have like these you know, within one single process you can still have multiple child connections

33:43 right into the same database as well.

33:44 I think the problem is if you try to access that G from a different process,

33:48 then that's where the lock-in mechanism triggers.

33:51 But yeah, indeed, Quack is a good solution for this, but it goes more into this client-server recipe again.

33:59 So if I'm using Quack, do I host my backend with you all at Mother Duck in some sort of cloud situation?

34:06 Do I self-host some backend piece to make this possible?

34:10 What's the story?

34:10 You can host it anywhere you like, actually.

34:13 I mean, also the nice thing about it being DuckDV is it's extremely simple to use, right?

34:17 So if you want to try it out, you'd go to a terminal, you basically type install quack, load quack.

34:25 And then the next thing that you do is you call a function called quack serve.

34:29 And that will already spin up a server for you, right?

34:31 And then from another terminal client, you could just go in, connect to this specific address in your local host, and then you already have a client-server protocol working.

34:41 Which, if you compare it to, again, traditional systems like Postgres, it's extremely simple.

34:47 And obviously, in a more production-grade scenario, you host this on EC2, and then maybe you need something like a load balancer on top of that.

34:55 Because also the nice thing about Quack is it runs on modern HTTPS.

35:01 So it's also very nice.

35:02 You don't need to reinvent a protocol yourself on top of TCP.

35:06 It's just plain old HTTP.

35:08 So yeah, I think it's relatively simple to still get something up and running on the cloud and get it to work.

35:16 Yeah, it's a very nice foundation, I think, for a client-server architecture.

35:20 I like how you all keep it playful as well, you know, with quack and ducks.

35:23 And, you know, it could be now you're going to create a database provider factory,

35:29 and the factory is going to get the provider.

35:31 then the provider is going to, you know, just like, you know, I don't know.

35:34 Yeah, actually, very funny story about this, now that you bring this up,

35:37 is that it was called Quack at the beginning, right?

35:39 This was Hannes' idea.

35:41 And then someone within the company mentioned like, oh, shouldn't we call this a little bit more of a straightforward name,

35:48 like, you know, RPC protocol or something like that?

35:53 And then both me and Gabor, which is the main dev world in the team, we said like, no, no, no way.

35:59 It has to be called Quack.

36:01 Quack is too good to pass on.

36:02 We cannot pass on these names so good.

36:04 I mean, the branding is great.

36:05 You hear Quack, you're like, oh, that's got to be DuckDB, right?

36:08 Like no one else is going to take the Quack protocol and get away with it.

36:13 All right.

36:14 Let's talk about Duck Lake.

36:20 So, yeah, let's talk Duck Lake.

36:21 I think it's a really interesting project.

36:25 It's not a direct follow-on.

36:27 It's not just a more distributed DuckDB, right?

36:30 This is a bigger idea.

36:32 Yeah.

36:33 So the basic gist of Duck Lake is that I think Mark and Hannah were already looking at data lakes for a while because we had been getting lots of requests for support for Iceberg.

36:46 And I think they were already seeing that having the metadata in files was just not as efficient, right?

36:55 Like you suddenly have to do so many hoops on these files to get to your data.

36:59 the more snapshots you have, because maybe researching tons of small files,

37:04 there's a small file problem, right?

37:05 Like we all have heard about this.

37:07 If you've been around data lakes, it's that basically if you have lots of small insertions,

37:12 like in a streaming fashion, for example, you create so much of these metadata files

37:16 that it's hard to get to your actual data.

37:19 So I don't know, I guess as database researchers and engineers, they were like, well, why is this not just a database?

37:28 So I actually have it somewhere here.

37:30 Oh, easy to pick this time.

37:32 This was the initial idea they had.

37:34 So here you can see like all the files, right?

37:37 So they're like, why can't it just be like a database?

37:39 And there's already like a database here on the top because in the end, they needed like a database

37:43 to get a pointer for the latest metadata file.

37:46 So what if we just put this all in the database and then I made them sign for me

37:51 in case this was a very successful idea.

37:53 So if anyone wants to buy this, it's for sale or for the rights price.

37:58 so that was the basic gist right because having this whole metadata in the database you can just

38:03 query with sql anyone can easily see a schema and write sql over it so just specify the schema of

38:12 these tables that hold all this information the metadata and you could you could then have

38:16 something that's more efficient and simpler right because i think for iceberg you have json you have

38:23 Avro, you might have some other file formats that I'm not, just to store

38:29 like the metadata, so just to be able to read this format, you need like three or four different file

38:36 format readers, and then you need to hoop through all of these, and with Duck Lake

38:39 it's simply like, okay, go to the catalog, ask the catalog, which files do I have to read to read my table at a certain

38:45 snapshots, you have the list of files and you read them, so there's the simplicity factor. Nice, I think

38:51 maybe the place, the way to make this clear to folks is let's maybe talk through the architecture

38:59 of these data lakes in general, but also the duck lake specific version, you know, and I know a lot

39:05 of people haven't worked with these necessarily worked with databases, they've worked with APIs,

39:09 and they've worked with storage like S3. But the general idea, I guess maybe the iceberg

39:16 original idea was, well, what if we could just use all of S3 for scaling? Like think how scalable

39:22 that is. And we just put a bunch of files all over the place, like a bunch of parquet files.

39:26 Well, you need something to gather them together if you can do a query across them. So we'll put a

39:30 metadata file up there as well. But then the problem is, well, I'm going to do a query. So it's a

39:35 S3 request to the metadata. And then you parse that. Maybe you got to get some more metadata.

39:40 Then you go to the file. It's just a lot of back and forth. And Pedro, that's what you're talking

39:43 about where you're like, well, what if a lot of that back and forth could just be in a database?

39:48 And then you finally get to the data, right? Exactly. And I think, sure, there's something

39:53 to be said that if you just have your files on S3, you can theoretically scale this indefinitely,

39:58 right? Because you could have bazillion readers reading at the same time, that's fine. But with

40:04 database systems, well, I would say that the point you have to scale is much lower. A database like

40:11 DuckDB can make use of a small piece of hardware quite drastically, but it's also you have distributed database systems, right?

40:19 So the only restrictions for the catalog is that I think it needs to have

40:26 timestamp, varchar, and integer types and use primary keys.

40:30 So any system that has this for can theoretically be a catalog for DuckLake.

40:35 So I do believe that there's also potential for hyperscading there or the case where your metadata is really that big.

40:43 And again, this is not about the size of your data, right?

40:45 It's the size of your metadata.

40:47 Right, just here are the tables, here's the elements, here are the stored.

40:51 You know, it's worth maybe emphasizing that scalability, something that's highly scalable,

40:57 doesn't mean it's fast.

40:59 It just means as you add many, many more users or amounts of data to it, it doesn't get slower.

41:05 But it could have been kind of slow from the start.

41:07 You know what I mean?

41:08 Like 12 back and forths across S3s, even for one query, is still slow if you don't have a ton of data,

41:14 but maybe it's consistent as you add a million more, right?

41:19 So there's that worth considering.

41:20 It's like performance for one thing versus scale.

41:24 Absolutely.

41:25 But as you think, again, that from the catalog side, the scalability of Duck Lake

41:31 doesn't necessarily have to suffer.

41:33 Like you have options.

41:33 No, no, no, no.

41:34 Yeah, absolutely.

41:35 I'm not suggesting it does.

41:37 I'm just saying like, Okay, I built a scalable system, like, great, but it was slow from the start.

41:41 So, like, it doesn't...

41:41 Yeah, yeah, absolutely.

41:42 You know what I mean?

41:43 All right, so basically the architecture looks like this.

41:45 There's three core elements.

41:47 There's storage, there's catalog services, and there's compute.

41:52 Who wants to break that down for people?

41:54 Yeah, so, I mean, basically the storage can be, as you mentioned before, anything that is object storage, like S3 or Blob of Storage.

42:04 Usually, you know, people go for options that are cloud hosted, of course, like S3, GCS, or Azure Blob Storage.

42:13 And that's where the parquet files lead in the case of DuckLake and nothing else because we don't have any metadata files.

42:22 Then you have the catalog service, which you can theoretically run in anything that is BigSQL.

42:27 Well, the DuckDB extension of DuckLake, right, which is basically our implementation of a reader and a writer for DuckLake, can speak to MySQL, Postgres, SQLite, and of course, DuckDB and Quack.

42:43 Because they're not exactly the same, right?

42:45 One lives next to you, the other one lives in any remote server that you want it to be.

42:50 And this catalog service is what contains all of the metadata regarding these parquet files that leave in object storage.

42:57 so basically then the third component which in this case is the compute right and indeed it can

43:02 be anything it can be spark or it can be dactdb and of course any anything that supports this

43:09 format and then the only thing that they need to do in order to query for example some tables right

43:14 is they they make some sql queries against the catalog service right and the catalog service

43:20 returns something like a list of files for example and some statistics regarding those files so that

43:26 the reader knows what it needs to read, basically.

43:30 And I think this is very nice, actually, because you just need one query, actually, in order

43:36 to retrieve all of the information that you need to query these parquet files.

43:40 And I think this is the big, powerful thing about Tag Lake is that we designed it with

43:46 pretty much Iceberg and Delta in our heads, right?

43:48 Where usually there's more than one roundtrip, taking more than one file just to figure out

43:53 which parquet files you need to read.

43:54 In our case, it's just one SQL query.

43:56 I think that that is basically what collapses the complexity quite a bit for engines that are trying to read this format or write to this format.

44:05 Yeah, you can do database things like join or something if you need to, right?

44:09 Yeah, I mean, indeed, actually, like one of the things that is very interesting about Iceberg and Delta is that because they grew backwards, they started to think about operations in a different way, right?

44:20 Like you were mentioning, for example, you know, at the beginning, it was just writing some parquet files and some metadata that points to them.

44:26 But then at some point, they wanted to do acid operations on this data, right?

44:32 And then you need to give some sort of guarantees.

44:34 And to give those guarantees, they built this very complex structure of metadata files and manifest lists and manifest files.

44:42 When for us at Obesity is just basically, you know, if you're writing to a table, right?

44:47 And then, yeah, you're basically raising another transaction.

44:51 You can just see whether the other transaction succeeded or not, right?

44:54 It's just something that is built into your catalog service.

44:58 So for us, we didn't have to build any ACID.

45:01 It was built into the design, basically, because the catalog service is a SQL database that already supports ACID transactions.

45:07 So, yeah, I think that was also an extremely nice thing about DuckLake from the get-go.

45:12 Yeah, absolutely.

45:13 Now, whoever wants to take this, give us a sense of the size of these parquet files.

45:19 I know when I created Datalake, I've got a bunch of different parquet files.

45:23 I've got a database catalog that will tell me, okay, if I have this type of query, go look at these three.

45:27 But are these five kilobytes, five megabytes, five gigabytes?

45:31 What are we talking?

45:32 Well, you decide the size, basically, right?

45:35 You can say that the target file size is something like 512 megabytes.

45:40 And if you're doing batch operations on your data, so batch writing, like Taglic will respect that you write 512 megabytes files with certain row group sites.

45:52 Of course, if you do very small operations on your data where you do very small writes, then you may have, if you don't have data inlining on, which is a feature that maybe we will discuss later, you will have very small files.

46:04 And then what happens usually in Lakehouse or OpenTable formats is that you have some sort of compaction functions that the engine offers that allow you to compact these very small files into bigger files so that, you know, engines can scale reading better, right?

46:21 Because engines don't like to read 1,000 5-kilobyte files.

46:25 They prefer to read, you know, half a gigabyte file that you can still parallelize, by the way.

46:30 Yeah, okay.

46:30 That makes a lot of sense, I guess.

46:32 The smaller they get, the more you're paying the cost of the latency round trip than the actual reading.

46:39 Now, operationally, transferring data is fast on S3 and friends, but it can start to get expensive if you have too much traffic there.

46:47 Do you recommend running this maybe inside of a data center where you have within the data center traffic to your data storage?

46:57 Or what does that look like?

46:58 I think Pedro is the expert on reading from object storage.

47:02 He has a ton of that in the last one.

47:05 I think the typical setup is that people use like an S3 EC2 kind of instance, right?

47:11 So you have an RBS.

47:14 So in the end, you can definitely have this separate.

47:20 I think that's the usual way that people run.

47:23 Yeah, that's what I would imagine.

47:24 or DigitalOcean servers with spaces or anything.

47:29 It could probably talk to anything that talks S3, I would imagine, right?

47:32 Yes, absolutely.

47:34 Yeah.

47:34 Okay.

47:35 Now, I'm back.

47:36 We've got different ways to set this up.

47:40 Oh, sorry.

47:40 Before we move on, there was a third piece.

47:43 There's storage, there's the catalog, and then there's compute.

47:46 What's the story with compute?

47:48 Like, what kind of stuff am I computing, right?

47:50 Yeah, absolutely.

47:52 theoretically, you can use anything that reads Parquet files to perform the computation, right?

47:59 As Guillermo says, in the end, if you have your catalog running, you just have to issue the

48:03 correct queries. The queries will basically tell you, read these files to answer the query you want

48:08 to answer. And you can use any kind of tool that is capable of reading these files. I think that

48:15 most people just use DeckDB. And there's also like a Data Fusion extension that's quite

48:22 evolves already.

48:24 But I'm not sure if there's like a Spark proper implementation of the Spark. Maybe Guillermo knows.

48:31 There is a Spark writer, I think specifically, that was developed by MotherDuck because

48:38 some of their customers were using Spark for ETL so they thought that it would make sense

48:42 to have a Spark writer into Duck Lake. I see.

48:46 So the basic gist is that you can actually use any engine you want as long as you have like these pieces properly built.

48:55 Nice.

48:55 And then you've got some Python code or whatever code that talks to that.

49:00 I have this question.

49:01 Go figure it out.

49:02 Yeah, exactly.

49:03 Okay.

49:03 Now, there's different setups here.

49:05 I would imagine the most common setup is probably Postgres as your catalog and storage as Parquet

49:12 files, though.

49:13 Hat tip to compress CSVs.

49:16 Postgres and Parquet somewhere.

49:19 But you can also do, it's partly why I jumped on this, you can do SQLite as your catalog,

49:24 or you could, which is in-process, and same with the in-process DuckDB, or even Wermos quack backend that's distributed, right?

49:32 Like maybe talk about these different options.

49:35 When do you recommend which?

49:36 So I would say that a lot of people also use DuckDB as an in-process solution

49:41 to either develop their own data lake on their own machine, but I think also a lot of people have been using with their favorites,

49:48 clanker just to store the data also in the local lake house formats.

49:53 But if you're using DuckDB specifically, it also means that as DuckDB works, like Guillermo

49:59 said before, is that you can only have one writer connected at a time, right?

50:03 So you cannot have multiple people connected to the data lake and writing if the DuckDB

50:08 is your catalog.

50:09 So I would say in production, what I've seen is that indeed most people use Postgres because

50:15 it takes that limitation away.

50:18 So you suddenly can operate with it in a way that people are more accustomed to using.

50:25 And of course, Quack is still experimental.

50:28 I think we're going to have the first stable release with DuckDB 2.0, like in a month and a half.

50:34 But Quack already works with DuckLake as well, but don't run that in production right now, people.

50:40 Wait a little bit longer.

50:42 But it's also quite cool because, for example, when using Postgres, one of the problems that we face

50:48 is that if there is a conflict on your snapshot ID, like you're doing multiple transactions on the same table,

50:53 every time you have the conflicts, you have to return to the application from the database

50:57 and say, hey, there's a conflict, recalculate your snapshot ID, some other things,

51:01 and send the queries to me again.

51:03 So you have this round tripping and that actually creates quite a big cost on retrials.

51:09 But with Quack, because it's just DuckDB, what we can do is that we load DuckLake on the Quark server,

51:16 and then we can immediately do the retrials in the server, avoiding this process, right?

51:21 So to give you an idea, if you have Postgres as your catalog, I think in a very contentious environment with, I don't know,

51:29 like 20 writers, you have something like five transactions a second because of this retrial cost.

51:35 But if you're using DuckDB and Quark, it's like 200 transactions a second

51:39 because the retrials are now running on the server.

51:42 So, yeah, my expectation is that as time goes on, Quark will be the de facto catalog for Dark Lake.

51:50 Maybe something to add to that is that, you know, I think Lakehouse formats, they were never designed for transactional workloads in the first place, right?

51:59 But something that we realized quite early together with Pedro is that, you know, actually we should flag that Tag Lake is actually better at doing these transactional workloads because it's one of the selling points, right?

52:12 And already with the Postgres setup, we were doing quite okay in transactional workloads versus, you know, TagDB also doing transactional workloads against Iceberg.

52:25 And with what Pedro implemented, server-side retrials in Quack, which could theoretically also be done in a way in Postgres.

52:32 We will see if that happens or not.

52:35 It's actually very, very powerful to the point where 200 transactions per second in a high-contention environment is almost unheard of for any other open table format.

52:46 Because these open table formats are designed for batch processing.

52:49 right? It's just you write once to a table, very large chunk of data, but they're not really

52:55 thought for like transactional loads or streaming data, things like that. I think the joke was that

53:01 if you wanted to buy something, use your Bitcoin, right? It took like 30 seconds because it could

53:05 only do two transactions and then iceberg was even slower than that, right? So yeah, it is a bit

53:12 heard of and uh just maybe to compliment uh what guillermo said it's indeed uh possible to have

53:19 something similar to postgres the main reason i haven't done it yet is because of course we cannot

53:23 do this trick of loading duck lake uh in a postgres server however we could theoretically do stored

53:29 procedures but i haven't done a stored procedure since college so that's why i've been uh going

53:35 It's really hard to do a stored procedure in a CSV.

53:40 No, I honestly, for all the benefits that stored procedures have, it's just, you know, in general, you have so much more flexibility if you just do SQL and all that on your own.

53:52 But yeah, that makes a lot of sense.

53:53 If you could just package it up and go, you just call this and the database handles it and process.

53:58 Yeah.

53:58 So multiple clients works locally or in the cloud.

54:01 I guess for the non-hosted ones, what's the scenario here?

54:07 Like either DuckDB in process or SQLite.

54:10 Is this, I'm working on my machine and I know that the data is out there

54:15 or how do I share this?

54:16 How do I like replicate?

54:17 Because you've got the S3 storage with all the stuff that could be shared,

54:20 but then you've got the local SQL or DuckDB file.

54:24 Yeah, there's a very cool use case for this, particularly with DuckDB in process,

54:29 which people started calling the frozen DuckLite.

54:32 and the thing is that to update a DAG Lake backed by an in-process DAGDB catalog,

54:41 you do need to pull the DAGDB file locally, right?

54:44 You can still talk to the catalog file locally and then do the updates there,

54:50 write your data to actually S3 directly and then once you've done this, for example, writing process, right?

54:56 Which let's say happens once a day, it's like a batch workload, Then you upload the DACDB file to S3 storage,

55:05 and then you can have any amount of read-only connections to S3 storage backed by a DACDB file as a catalog,

55:14 which means that basically you can have a data lake almost for free, right?

55:18 Because your catalog database is just a file in S3, which is very powerful.

55:22 So this works very well if you're doing, let's say, one update per day, or you just batch load a bunch of data,

55:28 And then you can have an arbitrary amount of read-only connections to this frozen duck lake.

55:33 Yeah, this frozen duck lake.

55:34 That's an interesting idea.

55:36 That way, it just operationally is easier, right?

55:38 Like, just it runs overnight in the morning.

55:41 Download the DuckDB file.

55:43 Ask all the questions you want.

55:44 Yeah, I mean, it's also like the very nice thing, right?

55:47 Like, you can literally have anyone asking questions without a server catalog running, right?

55:53 So you don't need to pay for a Postgres to be running or for a Quack to be running.

55:57 You could just literally have a DuckDV file in S3 storage and then read all of the DuckLake from your laptop.

56:03 Yeah, and operationally, it's so much easier.

56:05 Yeah, so much easier indeed.

56:07 So another interesting optimization, Pedro, that you wrote about is data inlining

56:13 instead of sending every little request all the way back to the object storage.

56:18 Yeah, the basic just here is, again, to try to mitigate the small file problem, right?

56:25 So the small file problem, again, is if you have a bunch of little insertions, you generate a bunch of little files.

56:30 And in Niceberg, you're also going to generate a bunch of metadata files.

56:33 I think it's four files per insertion, if I remember correctly, but I'm far from being a Niceberg specialist.

56:41 And that basically means that if you want to get the information of that one snapshot, you have to request all these files.

56:47 And although they're small, as you said, there's latency playing around and it just adds up and you end up paying quite a big cost on retrieving your data.

56:56 Duck Lake would still have that problem to a smaller scale, right?

57:00 Without inline.

57:01 So basically, you'd still have a bunch of small data files, but you would solve the catalog part.

57:07 So the idea here is like, well, we are using a database management system.

57:13 They can store tables.

57:14 What if we stored the same table as our data, but in our catalog, and then stored the inline, and then stored the actual data

57:23 that would go to the file in this inline table?

57:26 So that's basically what it is.

57:27 So imagine we would have a parquet file with, like, dates and value, two columns.

57:33 You're going to, instead of creating that parquet file, you have, if you're using DuckDB as your catalog,

57:37 you're going to have a table in your DuckDB be called something like underscore duck lake, underscore data table.

57:44 And then you're going to have these two columns plus some extra columns we need to actually

57:49 run the query.

57:49 So like the snapshots ID where that value was asserted, the end snapshots if that value

57:57 ended up being deleted while it has not been created to a fire yet.

58:01 And I think we have a third column, but I don't remember by heart now.

58:05 But that's the basic gist.

58:06 And then, of course, at some points, if your data grows enough, so the idea here is that your insertions are kind of small, right?

58:12 Like 100 tuples, 10 tuples, maybe 1,000 tuples, but it's not really that big.

58:17 And if at some point your table is too big, you can just flush it to disk.

58:21 So you remove the data from your inline table and you create the actual file.

58:25 And then it avoids this mid situation where you have a bunch of small files.

58:29 And then Iceberg tries to solve this issue, I think, with some streaming tools.

58:35 Guillermo is probably more aware of these things than me.

58:40 But this will again force you to have another tool.

58:43 I think it also gets in the way of transactionality or having your snapshots per data file at least

58:50 because you're going to have to start batching your data and try to do it in one go.

58:54 And with Duck Lake, you still preserve all of these.

58:57 And yeah, like I have some numbers in this blog post, but of course we're just comparing raw Iceberg with raw Duck Lake.

59:04 Yeah, so the numbers is like we got basically a thousand times faster.

59:08 It's a little bit of a performance improvement.

59:10 Yes, exactly.

59:11 So it is like a really cool feature in my opinion.

59:14 It is.

59:14 And once you've got this data, this local database in place, you can use it for things like that, right?

59:20 You need the database anyway to have a consistent data lake.

59:24 Yes, exactly.

59:25 Yeah, might as well.

59:26 And then, I mean, when you say with open some file in Python, you get buffering.

59:32 not every single byte you write instantly goes to the file because that's a lot slower and it just

59:38 seems like a much more scaled out way which is even more important right because the latency of

59:44 talking to object storage far away absolutely nice all right let's we got a time for a couple more

59:48 things let's talk about duck lake data frame what is this i know so uh while doing that project

59:56 with the i had to of course set an iceberg instance to compare uh with that could be

01:00:01 I thought it was a bit frustrating, the experience.

01:00:03 It took me like a while.

01:00:05 And then I just had my clanker going over it and I went to do something else.

01:00:09 And it still took my clanker like a while.

01:00:10 So I started like wondering, oh, maybe it would be easier to have my clanker implemented

01:00:14 new clank implementation than to actually get Iceberg to work or find Iceberg

01:00:19 on this machine of like some glue, not glue, sorry, some, the Iceberg catalog,

01:00:25 I forgot the name now, working.

01:00:26 And then the whole idea of this experiment is just like, This was a few months ago, so the Clanker was not as smart as it is now.

01:00:33 And I just let it go, and I just allowed it to write it.

01:00:36 And it came up with something that worked quite well.

01:00:41 I didn't really review the code, but it had nice numbers.

01:00:44 And I checked the test over, looks good enough.

01:00:48 But I also made some fun benchmarks that was like, oh, what if we just change our scheme off the table a million times?

01:00:55 How much faster is this in Epsburg?

01:00:57 And I thought it was just funny.

01:00:59 I mean, actually, it's funny, but it's also like one of the craziest things that the DuckLake improves over Iceberg, right?

01:01:05 Because every time you change schema in Iceberg, you need to write files as well, right?

01:01:09 And I think that's also the crazy thing about DuckLake is that, well, it's not crazy, actually.

01:01:13 It makes a lot of sense.

01:01:14 But we just do basically a SQL operation.

01:01:17 And this is just...

01:01:18 So over time, like, if you have, like, all of these different operations that are, you know, like alter table commands, for example, or stuff like that, which actually are not changing data, but are growing the metadata.

01:01:28 that, then your read performance will decrease because of that.

01:01:32 And of course, I changed it one million times.

01:01:34 The schema probably is not a real case scenario, but anyway, it's quite interesting.

01:01:40 Is this one the schema evolution rename?

01:01:42 I was just like, okay, I have no idea how CloudFont is an interesting metric, but okay.

01:01:49 That's a fair point, I think.

01:01:50 Yeah, what are the things we can do with it?

01:01:52 Very cool. But maybe the more interesting part of this, what I want to show, or at least

01:01:58 experiment myself is I do believe that compared to the iceberg open table formats, DuckLake

01:02:05 is much easier to implement.

01:02:06 That was the idea behind these experiments.

01:02:08 And since then, we've seen some people doing actual implementations.

01:02:13 There are, I think, closed source implementations from FireBot, I think.

01:02:16 But there's also the people from HotData that have done the one from DataFusion.

01:02:20 There is another implementation that's actually more official than mine from a board chat,

01:02:25 I think, that does the actual DataFrame, DuckLake.

01:02:28 So again, it was also to show that despite the name, Duck Lake is not only entangled to DuckDB.

01:02:34 It's an open table format.

01:02:36 We want people to also to write their own readers and writers and to use our own implementation as a reference,

01:02:44 but also our website has like the spec description.

01:02:47 So the idea of that experiment was also in line with that position.

01:02:50 Sure, that makes a lot of sense.

01:02:51 When I first saw Duck Lake, oh, this is DuckDB, but for data lakes.

01:02:56 How does that work?

01:02:56 And then looking more into it, realize what it was now said don't use this in production when when can you what's what's the

01:03:04 path to 1.0 ready for production all these things yeah so i'll say the main like we actually took a

01:03:11 a bunch of time uh to from duck lake 0.4 i think to 1.0 where we decided we're not gonna add any

01:03:17 new features we're just gonna focus on fixing bugs and we're only gonna do schema changes

01:03:23 if they are fixing bugs.

01:03:25 And I believe that since April, it should be production ready.

01:03:31 Of course, as software is, there are still issues and we still have bug reports

01:03:36 and we're still fixing them.

01:03:37 And we're going to be releasing Duck Lake 1.1 soon, I believe most likely

01:03:43 in the month and a half with Duck Lake 2.0.

01:03:44 Again, almost on the same perspective that it will have some smaller features

01:03:50 or smaller optimizations, but it's mostly focused also on bug fixing.

01:03:55 So this is really the direction we're going now.

01:03:58 It's trying to make it more solid and more compatible with Iceberg.

01:04:03 So we can also read from and export to Iceberg.

01:04:06 So in the sense of being an open table format, we also don't want people to be locked in

01:04:11 in our own table formats, if that makes sense.

01:04:13 Yeah.

01:04:14 Yeah.

01:04:14 And one thing that is interesting about what Pedro said is that because we have the specification,

01:04:19 but also the DAC leak implementation of DACDB, right?

01:04:22 I think the specification, we tried to sort of like freeze it as much as possible and

01:04:27 make sure that we only do like new releases of the specification if it's really necessary.

01:04:31 There's like a breaking change in the, yeah, in the catalog schema.

01:04:37 But the extension itself can have like not only back features, but also like performance

01:04:42 improvements, for example, right?

01:04:43 Like I think we've recently seen like some improvements in like the way we calculate

01:04:49 the statistics.

01:04:50 And I think there's a bunch of others.

01:04:52 So basically the extension can still get better, right?

01:04:55 Like the engine can be faster and do things better anyway.

01:04:59 So it doesn't mean that Duck Lake is not evolving.

01:05:02 I think it's like the specification evolves slower, right?

01:05:05 As it should be in our opinion.

01:05:07 And the extension can still get better and better over time.

01:05:10 Now, let me just put this out directly.

01:05:13 It sounds like maybe it is ready for production.

01:05:15 What's the readiness state of Duck Lake?

01:05:17 Yeah, so I think one of the main things we wanted to achieve for 1.0 before, besides all the bug fixing and whatnot, is that one of the main aspects when you run a data lake over a long period of time is that you get more data in it, right?

01:05:32 So we needed all the checkpointing functionality.

01:05:36 And with checkpointing is compaction, is rewriting data files by adding their deletions, is removing orphan files, it's removing old files, all these things completely ready and easy to use.

01:05:50 So I think that's also one of the main things we ended up spending a lot of time in this production readiness state to be sure that people can just run it and not be completely stationary because their data grew too much.

01:06:05 So that was also one of the main concepts behind this.

01:06:08 So it is production ready.

01:06:09 I mean, for sure, there's already a lot of companies that are running this openly and they even base their software on it.

01:06:16 I think BossHawk is a good example.

01:06:19 AlterTable is a good example.

01:06:20 I think Firebolt also reworks their story changing to use DuckLake as well.

01:06:25 So there's a bunch of companies that already trust DuckLake as a thing to trust your software on, which for me is the biggest testament of something working.

01:06:36 Yeah, when people just start diving into it.

01:06:38 That's right.

01:06:39 To keep the lake analogy going.

01:06:41 All right.

01:06:42 Let's wrap it up with Final Call to Action for people.

01:06:46 they want to get started with the lake, you've convinced them that this is awesome,

01:06:50 they should be doing it.

01:06:51 What do you tell them?

01:06:52 I mean, if you want to get started, I think the main way of doing it is going to the Duck Lake website.

01:06:58 There's going to be examples.

01:07:00 I think we have a couple of tutorials as well.

01:07:02 Otherwise, the clankers are also pretty good these days and it's really easy.

01:07:06 It's literally like one line and you have a data lake working.

01:07:10 In one line, you can also have a Duck Lake with Quok working.

01:07:15 To me, it's always a bit mind-blowing.

01:07:18 Just check the website.

01:07:19 That's the best way.

01:07:20 If you find any issues, it's always super helpful to us if you open an issue on a GitHub,

01:07:26 especially with very reproducible steps, like full-on scripts on how to generate the data,

01:07:32 what exactly was the problem.

01:07:34 As easier as it is for us to reproduce, it's easier for us to fix as well.

01:07:39 And we got people covered.

01:07:41 We go through issues.

01:07:42 We have a way of...

01:07:45 going through all of them and prioritizing them within our team as well.

01:07:49 Yeah, I mean, I agree with Pedro.

01:07:51 I think the most important thing is that you try it out for yourself, right?

01:07:54 And you see whether it works or not for you.

01:07:56 I think TagLik can work in multiple different ways, right?

01:08:01 Like you can see it as like your pet project to store all of your data, which is a very comfortable way of running it.

01:08:07 Or it can be even the backbone of your software service, right?

01:08:10 Which is also where many companies are betting on.

01:08:14 So yeah, just try it out and let us know what you think, right?

01:08:17 We will be there in the public issue trackers.

01:08:20 And with DuckDB 2.0 coming, things are also going to get much faster.

01:08:26 Much faster, that is true.

01:08:29 We now have an async.io implemented and that will make a huge difference for reading S3 files

01:08:36 because it will basically unclog your processing.

01:08:40 So yeah, I mean, I think this is one of the most exciting times to be diving into Duck Lake.

01:08:45 Awesome.

01:08:46 Now, finally, there's all these different ways you can run it with Postgres,

01:08:49 with SQLite, with DuckDB in process, or Quack.

01:08:52 Do you recommend people start in one of these?

01:08:55 I'd start with DuckDB.

01:08:56 Yeah.

01:08:57 DuckDB in process, right?

01:08:58 Yeah, DuckDB in process.

01:08:59 That's the easiest.

01:09:00 Okay.

01:09:00 Because with Postgres, you still have to set up the server because that's, of course,

01:09:04 a little bit apart.

01:09:05 If DuckDB is literally online and you're ready to go.

01:09:07 Yeah, it's got all the extensions and it just knows, right?

01:09:09 Exactly.

01:09:11 All right, you guys.

01:09:11 Thanks for being on the show.

01:09:13 And sharing this cool project you're working on.

01:09:16 Thank you so much for the invites.

01:09:18 Yeah, thanks, Micah.

01:09:19 Bye.

01:09:20 This has been another episode of Talk Python To Me.

01:09:22 Thank you to our sponsors.

01:09:23 Be sure to check out what they're offering.

01:09:25 It really helps support the show.

01:09:27 Thanks again to Six Feet Up, the Python and AI experts you call for the hardest software problems.

01:09:33 From scaling applications to simplifying data complexity and unlocking AI outcomes, they help you move forward faster.

01:09:40 See what's possible with Six Feet Up.

01:09:42 Visit talkpython.fm/six feet up.

01:09:46 And it's brought to you by us.

01:09:48 Talk Python and Python Bytes both now have MCP servers.

01:09:52 Point your AI at 10 plus years of Python episodes, transcripts, and show notes free.

01:09:57 Click MCP in the nav at talkpython.fm and at Python Bytes.

01:10:02 If you or your team needs to learn Python, we have over 270 hours of beginner and advanced courses

01:10:08 on topics ranging from complete beginners to async code, Flask, Django, HTMX, and even LLMs.

01:10:14 Best of all, there's no subscription in sight.

01:10:17 Browse the catalog at talkpython.fm.

01:10:20 And if you're not already subscribed to the show on your favorite podcast player,

01:10:24 what are you waiting for?

01:10:25 Just search for Python in your podcast player.

01:10:27 We should be right at the top.

01:10:28 If you enjoy that geeky rap song, you can download the full track.

01:10:31 The link is actually in your podcast blur show notes.

01:10:34 This is your host, Michael Kennedy.

01:10:36 Thank you so much for listening.

01:10:37 I really appreciate it.

01:10:38 I'll see you next time.

01:10:50 I'm out.