0:00Well, hello, everybody, and welcome. Thanks so much for joining our webinar today, looking deep inside of the DuckLake. We're doing a deep dive all the way to the bottom of the lake. So thanks so much for being here. What I really love most about these things are your questions. So definitely hop in the chat. No question is too basic. No question is too detailed. The worst thing I can do to you is say, 'Hey, let's talk about it later'. Pretty low bar. Happy to have your questions there.
0:29If it makes you feel any better as well, I'm a notorious question asker. So you know that awkward silence when someone says, hey, does anyone have any questions? I have a very low tolerance for that, which means that I deserve any question that you send my way. So please jump in. It makes it more fun.
0:46So what are we talking about today? First thing I want to do is a bit of a quick review of what is DuckLake and why we're so excited about it. What are the key differentiating features you should consider with DuckLake? And then I'll try and make the case a little bit about why, even in the age of AI, it's still really helpful and beneficial to understand the internals.
1:06And then we'll spend most of our time talking about the internals. And we'll do some Q&A as well throughout and at the end as well. But we'll really dive in deep to the three-component architecture of DuckLake. First, I'll say hi. Howdy, I'm Alex. I've been here at MotherDuck for about three years. I'm a developer advocate. Previously, I worked at DuckDB Labs as a blogger and documentation expert. And then I spent nine years at Intel before that.
1:30And most relevant for today, though, I am a co-author of the book, DuckLake, The Definitive Guide. And I'm working together with Matt Martin, who's the lead author on the kind of subject matter expert side of the tandem here. We're having a great time really going very, very in-depth on DuckLake. And not only learning about it, but also going and testing it and empirically building examples and things like that. So a lot of hands-on experience there as well.
1:56And a lot of what we're talking about today is from this book. So we've released an early release of the first three chapters. And a lot of this content is coming from Chapter 3. And I'll kind of give you some previews of some of the upcoming chapters as well as we talk through it. But you can see the link in the chat there. If you sign up with an email, you'll get a free PDF copy of the first chapters that are already out. And you'll get all the other chapters as we release them. We release about one every month.
2:21All right. So what is DuckLake? Why are we so excited about it? Before this, I'd love to actually do a quick poll and see what is your experience with DuckLake? So hop in the chat, type either one, two or three. You've heard of it. That should cover everybody. You've heard me say it at least a few times already. And then have you used it a bit or have you used it a lot? And that'll help me understand how much time to spend on each piece.
2:46So if you could jump in and type in a one, two or three, that'll give me a quick sample. Thank you, folks. Seen a couple answers coming in. All right. Got a couple people using it a lot. Most folks have heard of it or used it a bit. We'll give you like 10 more seconds. We'll do a countdown. No, I won't do that to you.
3:13All right. Great. Well, fantastic. I'm glad so many folks have used it a bit. This could have been 90% just heard of it. So I'm really glad that you guys have used it a bit. And we'll go deep. We'll have something for everybody here. Something for folks that have just heard about it and those that are using it a lot.
3:28And please don't forget, even though the chat is now full of the numbers one, two and three, I still want you to use that chat for your questions. I will answer them along the way as they come up and we'll have dedicated time at the end, too.
3:41So what is DuckLake? DuckLake is an open table lake house format. So what does that mean? That's a way of storing large amounts of data. Hundreds of terabytes or more at the top end.
3:56In a way that is open and transferable and it's an open source format. The main engine for working with it is DuckDB, but you can also use Trino and Spark and Data Fusion to work with DuckLake as well. So it is open in the sense that it is not tied to a particular vendor. It is open to the community.
4:17And what are the key benefits of DuckLake? Well, it can handle that type of scale of data, but the key feature, the key design principle is simplicity. And I don't think you hear that a lot from the data space. You hear a lot about scale and speed and, you know, a whole lot of other things.
4:35But DuckLake was really designed with a clean sheet of paper to say, how simple and easy to use can we make this? And that has a lot of implications. It means you can get up to speed faster. It means your agents are more likely to get it right. And it means that it is much easier to self-host as well while you're getting going. You can run it on your laptop with those three commands you see at the bottom of the screen.
4:57So the first command that I didn't show is install DuckDB. That's one curl command. And then run these three commands and you have a lake house on your laptop. And there's no Docker. There's no, you know, configuration even. It's all right there, which is pretty wild. Now with that simplicity, it's not just easy to use. It's not a benefit you just get on day one setting it up.
5:19That architecture is actually very, very elegant, as we'll see. And that elegance leads to speed because we've cut out a lot of excess. Things that really slow down the existing formats. And we do retain that great scalability of a lake house format.
5:36You know, if you didn't need scalability, DuckDB is great. But as you get beyond, you know, tens of gigabytes, you really want something more scalable. How do we do that? What's the secret sauce? How is this happening? Well, what we're doing is we're storing catalog data and metadata together, both in a SQL database. Instead of storing it on object storage, and we'll have a diagram that will really make it clear. Why does that really matter?
6:01A couple more fast facts about DuckLake. It is partitioned for scalability. That's part of how we get this large scalability is that we only read exactly the data that you need. We're not going to read all the data every time, which means you can handle data sets up into that petabyte range.
6:19You can load data really fast. We'll see some specific ways that are really novel, where you can get very high ingestion speed, even if you're doing tons of small inserts. You can bring your own blob storage. Because this is an open format, we use parquet files under the hood. And then the SQL database to manage those parquet files using open standards.
6:40And because of that architecture, you get excellent low latency queries. So in a lot of cases, existing lake houses are in somewhat of a compromise. You get the nice benefits of openness. You get nice time travel. You get a lot of nice benefits. But you give up this low latency. You give up some of the ease of use of a warehouse.
7:02And really, DuckLake is this Goldilocks approach. Ducks and bears, they get along too. This diagram for me, comparing and contrasting with Iceberg, kind of really puts it into sharp contrast. On the left side, you have Iceberg. On the right side, you have DuckLake. And if you want to pull data with Iceberg, there's a lot of steps.
7:24The first step is to talk to the catalog. This is a REST API web service that you would have to run. And by the way, behind the scenes, it talks to a database. That's interesting. In that database is one table. That one table contains one row per table in your Iceberg lake house. And it just says, what's the latest snapshot number? So you have a whole database just for that one table.
7:49And so you do that. Once you get that answer, you have to read three different layers of metadata files on object storage. And you cannot do these in parallel. You have to do them in series because they depend on each other. And why is that important? Well, object storage is great for being bottomless, for having high throughput, and for being low cost.
8:11But what it is not great at is low latency. Each of these requests can take 100 milliseconds. And if you're doing these back to back, not in parallel, that means you're about half a second delayed before you even get out of the gate in Iceberg. Just to figure out what files should I read, it's half a second right off the bat. And then at the bottom, you'll see the data layer there. Those are the parquet files.
8:33In contrast, in DuckLake, we have a metadata database and we have those data files. And those data files actually are completely Iceberg compatible. Or you can migrate from Iceberg to DuckLake without moving those parquet files at all. There's a single function call you can do to take all that metadata data in Avro files and JSON files scattered across object storage.
8:55And compress it down right into a database. So very easy migration path. The other key thing here is that DuckLake is not reinventing the wheel where it doesn't have to. It's simplifying where we can. And so the data format is the same on the parquet side. Looks like we've got a couple of questions coming in. Thanks, everybody.
9:16We've got the first question. Does it work with all cloud providers, specifically Azure Blob Storage? Thank you, Ben. Yes, it does. When DuckLake was first released, when it was in beta, it did not support Azure, but Azure support has been added. So now you can do reads and writes to Azure and it works with DuckLake. So thank you for the question. I've got a question. Why not SQL Server yet? That's a good question. I don't have the necessarily the inside scoop on that.
9:41That said, it's a very reasonable, reasonable thought. And we'll talk more about the options that we have so far. And those options are going to continue to grow. So thank you for that. All right. So I think the next couple of questions I'm actually going to answer with the next few slides, but I will come back to them. So thank you. Keep them coming. Those really help me very much.
10:06Last kind of overview slide here for this piece. Really, this is kind of hammering home the idea that in some ways, DuckLake is revolutionary. In other ways, it is, you know, making a pragmatic choice. And that is it's using the right tool for the right job. Iceberg and Delta already use a database for their catalog, but they put their metadata on object storage.
10:27And I believe and the data shows that fundamentally that is, I think, a poor design decision. And what DuckLake does is it reverses that design decision. It says this would be simpler and faster if we use that database for more than one table. We use that database to store your metadata also. So here I want to make the case a little bit about how does knowing the internals help?
10:49And I've just got a couple of notes that I'm looking at on my other screen here. So how does knowing the internals help? Well, first of all, there's a lot of options when you first set up a platform where you get to select the options of how you configure it. And after the fact, if you have that reviewed by an agent after you've already set it up, there's nothing the agent can do to undo those initial design decisions.
11:13So it's very important to get a baseline foundational knowledge of something so you know how to set it up correctly so that you can tune it to how you'd like it so that it will meet your needs. So that's the first thing. The next thing is once you have it set up, you want to push it to the limit. And knowing how the architecture works fundamentally will help you understand how to push things to its limit. And we'll talk about that.
11:36You'll also know where to look for issues and especially how to stop issues before they happen. So, for example, in DuckLake, like all lake houses, there's a maintenance aspect where over time you're inserting data. You want to do some cleanup and consolidate for performance. If you were not kind of familiar with lake house style, makes sense. There's a lot of alternatives to lake houses, data warehouses, et cetera.
12:00If you haven't used one before, you might not set up maintenance. And over time, your lake house might slow down. But understanding that kind of core key concept will allow you to avoid issues before they even happen. Even with AI, it still helps to know the funnels. We have to know what questions to ask and where to poke. All right, we're getting ready to jump into the architecture deep dive.
12:24Let me look and see if there's a few other questions I can answer before we get into it. All right. Can DuckLake read DuckDB files, not just Parquet stored in object storage? It's a great question. It is actually deliberately built with Parquet because it is more readable by more systems.
12:45So that way you could have a Spark reader more easily or, you know, Trino or Data Fusion, et cetera. So Parquet was chosen deliberately. Parquet is also really well optimized for object storage. And so it is a solid fit there. That being said, DuckLake is very extensible. As we'll see, it's very modular. This is something where it is possible and feasible to do in the future. Let us know if you're interested in why.
13:10And it's also a spot where we might be able to collaborate. So thank you for the question. As far as the slides, I could probably make those available. But you'll definitely have this recording available where you can skip through those. So thank you for your question, Johnny. All right. OK, David. David, I will answer your questions as we go. So thank you for that question as well.
13:35Let me see if there's others to answer at the moment. Sweet. Keep them coming as we go deeper into the lake. So while DuckLake looks and feels like a data warehouse when you access it, you know, it's got this logical table approach. You don't have to necessarily be thinking about files while you create. You just think about tables. If you look a little deeper, there are three components to the architecture. You've got your storage, where the Parquet files actually get stored.
14:00You've got your metadata, which is your database, storing your catalog data and your metadata data. And then you have your compute, which is where it's actually pulling the data from the database and your compute, which is where it's actually pulling the data from the storage. It's communicating with the catalog, and it's doing the actual, you know, SQL query engine work, you know, doing the group by, doing the join, returning your results. A unique thing about DuckLake is that these pieces are all pluggable.
14:25You have many options within them, and they're pluggable in a very simple and easy way to make it very easy to start local and move to the Cloud. So to test out DuckLake like we saw in one of those very early slides, those three commands, you'll use storage of your local disk. You will use a metadata catalog of a local DuckDB file, and you'll use the compute of your laptop CPU.
14:48And you can play around, and you can get familiar with the syntax. It's perfect for testing. It's perfect for CICD. You know, this SSD doesn't have to be on your laptop, right? It could just be the SSD attached to your GitHub Action or your Lambda function or what have you. So there's a lot of interesting options you can do for testing with this. And it doesn't require like an S3 file system. It just uses folders and files. So it's very compatible with really any kind of storage medium
15:12that you'd be using. If you want to move into production and have multiple people accessing it at once, though, you need to put your catalog somewhere multiple people can access it. And in that case, in the open source side, you're going to want to use Postgres as one of your core options for your metadata catalog. So multiple people can be using this lake at the same time. And for storage, you could use AWS S3.
15:37You can use Azure Blob. You can use Google Cloud Storage, whichever you'd like. Any kind of object storage there. You could use Cloudflare R2. You can use, you know, Tigris. Wide variety of options there. In this case, you could still use your laptop for compute. You don't even have to do cloud compute. However, there are some real benefits to doing that because most queries that we run in the analytical world are going to summarize your data.
16:01Take two big tables, join them together, summarize. Do a group by. You're not usually querying your full 100 terabytes of data all in one shot. And so keeping your compute close to your storage is really the first piece of advice here. If you built your architecture on everyone's laptop, there are certain constraints that you will have moving forward. So that's kind of one of your first architectural decisions
16:25is where is my compute going to run? There's some other options as well. There's a new project in beta out of DuckLabs called Quack. The Quack protocol is a way to treat DuckDB like a client server database, like a more traditional database. And so you could operate your own Quack server instead of Postgres. It's a little bit of therapy dragons right now. It's in beta.
16:50There's a little bit more to work out, whereas Postgres, you know, there's 15 different solutions you can use for Postgres or you can host it yourself. The next option is to use a MotherDuck managed DuckLake. So with MotherDuck, we can operate a storage bucket under behalf. We host the metadata database for you. We actually use DuckDB, a MotherDuck-powered DuckDB database as your metadata catalog,
17:15which has some really cool, powerful properties. It turns out metadata can get somewhat large and having an analytical database for that actually helps a lot. And then we have our compute, which we'll talk about a bit more at the end, but it's really, really convenient. It can scale from a couple CPU cores, a couple gigs of RAM, all the way up to 192 cores and a terabyte of RAM. So if you have a really big job,
17:40we have a really big serverless instance that can handle that for you. But we don't give up the local compute. That's another powerful piece of MotherDuck. We have a concept called dual execution. So you can choose to run parts of your queries on your laptop as well. And that's really nice because you don't have the latency of DuckDB, of skipping those network hops. And so you don't give that up with MotherDuck either. However,
18:05part of the benefit of DuckLake is the openness. So you also have the option of owning the bucket yourself and bringing the bucket with you and just letting us know that you have this bucket you want to store things in. And you can take that bucket with you wherever you want to go. You can use it with other engines, like your Sparks, your Trinos of the world. So we also bring that nice openness as well, and compute management on the MotherDuck side.
18:31So a couple pieces of advice based on knowing that architecture, how does that help us then operate DuckLake? Well, as we talked about, the latency between storage and compute matters. You want to keep those close to each other. If you're deploying on-prem, keep them close together on-prem. If you're deploying in the Cloud, keep them together in the Cloud. You don't want them cross-region. You don't want them cross-Cloud.
18:55You really want them co-located and for latency and cost reasons, you want them together. All right. So that was our quick overview here. Let me see what other questions we can jump into here. Let's see. How should I think about deployment boundaries on AWS, especially RAM, local disk, and network limits?
19:20That's a great question. I think that's one of those things where the pluggability here is one of the design choices. One key thing about DuckLake is you can have multiple instances talking to the catalog and running. So you can have one workload use a really big, beefy instance, and you can have other workloads use a really tiny one. And that benefit there
19:45means you could customize this down to the individual query if you really wanted to. So very, very flexible here. The compute nodes do not have to have a lot of RAM there. It does tend to help. But again, you can run this in a very lightweight way. part of the magic of DuckDB. It is lightweight all the way to heavyweight. Thank you for the question. Let me see.
20:12How do we know when we need DuckLake and when DuckDB might be enough? That's a great question. I think there's really kind of two or three key reasons. The first one is if you want things to be multiplayer. So if you want two different people on two different machines to be asking questions of the data set at the same time, you really want to have DuckLake because DuckLake's catalog will
20:34handle the concurrency. We'll get to that very shortly. That's a key value of that database is it makes sure that people don't step on each other's toes, that you can safely write data and read data from anywhere, and it'll be handled in a transactionally safe manner. The other thing is with scale of data. If you've got 10 gigabytes of data, you know, you can store 10 gigabytes of data
20:55in one file on S3. It won't be perfect, but it'll work okay. So you can keep things pretty simple if you're in that 10 gigabyte range. Once you get above that to hundreds of gigabytes, DuckLake has some real advantages, and I do recommend looking at it in that range. Thank you for the question.
21:13Thanks, Tophie. Ah, this is a great question. Thank you, Alexis. Can a Parquet written through DuckDB and DuckLake be read without DuckDB and without DuckLake? So they are just regular Parquet files. So if you wanted to read them with any other engine, you absolutely could. Nothing's stopping you there. You can bring your own bucket to MotherDuck and do that as well.
21:35So you definitely can do that. They're fully compatible. In general, having the catalog, you know, talking to the catalog first can be helpful. Otherwise, you might not know which Parquet files to read, right? So it does help to use DuckLake, I'd say, in most cases. So what I'd probably say is more common is to use DuckLake catalog, but not use DuckDB, rather than skipping
21:58both of them. But you can absolutely, those files are yours. You can use them however you'd like as well. All right, this is a great question here. Thank you, LinkedIn user. We appreciate your question. Delta has metadata bloat optimization techniques like optimizing vacuum. How does
22:20DuckLake deal with this? Well, we absolutely have techniques for doing this. We'll talk about it a little bit today. It's not as much in the fundamentals here, but the key is that lake houses get benefits by loading your data quickly and then doing some of the maintenance later. A lot of data warehouses do that kind of maintenance on insert time, and that has trade-offs
22:45as well. You get the read performance immediately, but it means your inserts can also be a little bit harder to get fast. So you absolutely have these same options in DuckLake. You can do things like say, I only need to time travel back seven days. Any files that are outdated that are more than seven days old, I don't need those anymore, and it'll clean that up. That'll be removed from the
23:06catalog. It'll also be removed from the object storage location as well. So there's a few different steps to make that happen, but you can absolutely do that by changing your time travel window. You can also make smaller files be combined together into larger files so you can do more operations in bulk and get faster performance from S3, and those are absolutely available in
23:28DuckLake as well. Thank you for the question. All right, other good questions. Is an upsert operation costly on performance in the DuckLake? That's a great question. In general, an upsert in DuckLake is going to be a read of your data to see, you know,
23:51how much data you need to read to do that comparison to see, have I seen this before? And then it's going to be, you know, a combination of that data. You'll do some deletes and you'll do some inserts. In general, it is manageable performance. Obviously, just inserts are always going to be the fastest, but upsert performance is still scalable on DuckLake
24:13because we're able to be very selective in what we read when we do those upserts. So definitely give it a try. I think upsert performance should be smooth. All right, getting into the details already. So thank you, Andreas. Are there relevant performance benefits of using a cloud VM SSD instead of S3 with local caching? That's a good question. So
24:37let's maybe hop back to kind of that architecture side here, right? So if your compute node has storage on it, you absolutely can use that for caching. There's a couple extensions that will help with that. Out of the box, DuckDB does its caching for DuckLake in RAM, not necessarily on
24:58SSD. So not as necessary to have SSD on your compute today, but that's something we could do. The nice thing is once you have written your data, you can move that data around as long as you make DuckLake aware of where that data sits. So the migration path from disk to S3, there isn't like a one function call to do that if you wanted to move things around.
25:22But the catalog is a database. You can run your own queries on that database. So if you'd like to manipulate it directly, you absolutely can. That's a bit of a power user mode there. Shandong, there will absolutely be a recording. It's live, but it's also on YouTube and some
25:41other places as well. All right. Thank you for all these questions. Just having a quick look. All right. So here's another kind of trade-off question. Then I'll come back to a couple others as well. So
26:05I'll summarize your question a little bit, Cece. The Postgres-based catalog came before Quack. What is better for concurrent access deployment in the long term? It's an excellent question. I would say that while Quack is in beta, I would recommend Postgres for sure.
26:28When you're building a lake house, you want that to be your rock solid foundation. You know, things are shaky enough with agents as it is. Let's give them a nice foundation to work with. I think Postgres is probably the best bet there. MotherDuck similarly is, again, just as solid as well. I think it'll be a little while Quack matures. I do think in the long run, having a MotherDuck-backed catalog or a Quack-backed catalog has some advantages because
26:53when you're doing these catalog operations, there are a lot of joins. And there's some scans as well. And if you have, you know, a lot of inserts very frequently, you end up with a lot of snapshots very quickly. So it might be millions of rows joined with other millions of rows. And once you get there, Postgres would prefer not to do that. It would prefer to do index lookups and join on the index lookups. And you could definitely index your Postgres database
27:16in a DuckLake. But there is a core part of the catalog workload that is a good analytical fit. So to your time horizon, I expect Quack to be a very, very solid option here. Thank you for your question. All right. Well, I'm going to go a couple more slides, and we'll come back around to some of
27:37the other questions as we go. So thank you. Please, questions are welcome, and they've been really good. Let's jump into storage a little bit, the plumbing side. The point of storage here is to be straightforward. It's simple and scalable, and it's compatible. You can use object storage as your typical deployment model for production. You get that separation of storage to compute that way, which means your storage can grow
28:00a huge amount. But you might not need that much compute to access it, because you probably aren't going to query it all at once. And DuckLake will help you avoid doing that. It's Iceberg compatible, so you can copy from Iceberg with one command in, you know, seconds and leave all your parquet files where they already sit. And with storage, we get a new file for every change. We get a new file for deletes, a new file for inserts. That way, we can time travel back to the past,
28:25you know, before that file was inserted. There's another really innovative part of DuckLake on the storage side. It actually works together with the catalog and the storage. With DuckLake, you don't have to write directly to parquet files. We have a catalog database, and catalog databases are excellent for lots of small,
28:48concurrent inserts. Parquet is not. So what you can do in DuckLake, we call it inlining. If your data is below a certain threshold that you can set, say it's 100 rows or less, 10 rows or less, something like that, something small, instead of going directly to parquet, we will insert that data directly into the catalog database in a table, usually a Postgres table,
29:09in a table, usually a Postgres table, right? And we will let it build up there after inserts, you know, come in. And then eventually, you can push that out to parquet once you have enough to be a large enough parquet file. And what are the benefits of this? Well, you can see on the left-hand side is kind of the way the other incumbents do this. If you insert frequently, every insert gives you, you know, three or four files, a couple JSON files,
29:32an Avro file, and then a parquet file. And then those add up really, really quickly, and it slows down your system having to access all those. You also have to compact them very frequently, which is an expensive operation. And with those other lake houses, you can get one transaction per second, state of the art of 1985, right? That's a limitation in a lot of cases,
29:55right? One transaction per second is not a lot. It means that your lake house used to be an archive. But now with DuckLake, you can have 100 transactions per second. And that means that it's a living, breathing, you know, system of record where your source of truth can actually be used for everything, not just for occasional inserts. That's a huge difference. There's also
30:18another important thing to understand about inlining that's a really cool design choice. It's not actually a buffer. It's designed like a filter. So this is an analogy from audio, but I'll contrast it with Apache Kafka a little bit as well, like a streaming system. When you're using Kafka, if you're buffering in a Kafka stream, you're buffering in memory. And it's very hard to pull that data out of memory in a performant way.
30:42The key thing with Kafka is you want to get the data out of memory as fast as possible, and then pull it out of the system of record once it lands there. While it's in flight, it's a little bit hidden because it's very slow and hard to pull data out of it. In DuckLake, as soon as it hits the catalog database, you can read it back out. So if you did a select statement, we will auto combine the data from the catalog and your parquet files. You don't even have to worry about it. And so that makes it instantly
31:07transactional. And so there is no buffering time where your data is in flight. As soon as it's inserted, you can read it. So the audio analogy, I'm not a super audiophile, but I've put together one set of speakers in my life. And I bought this little device called a crossover. And what it does is you just have one set of signals coming into the speaker, but you have two pieces of your
31:29speaker. You have one part that's really good at tons of tiny things, like symbol crashes. You've got one that's really good for the deep bass. And you need to split those sounds out because if you mix them together, it muddles the system and you end up with really garbled sound. And that's what happened when I plugged it in the first time before I learned about this. So the small changes go to the catalog and the large changes go straight to parquet.
31:52They just bypass the catalog and go straight to parquet. But it doesn't auto-flush what's in your buffer. It sort of just bypasses the buffer and lets the buffer sit there and continue building up until you decide to flush it. So the flushing is only when you decide to. You set it up on a schedule to do whenever you need it. But it's a little bit of a different mental model and this
32:14analogy helps me understand it a bit better. So that is the storage side of Dougleg. Let me take a look again at some of your questions. All right, Andreas, are there any obvious reasons not
32:35to use Dougleg for time series data? It's mostly column-based, some filtering and aggregation window functions. I think we didn't really talk about use cases because I wanted to spend as much time as possible on the deep dive architecture side. But if we back up a little bit and talk about use cases, event data and time series data is an amazing use case for Dougleg. Your data
32:58builds up very, very quickly over time, but it also gets stale really quickly over time. So you want to store that data for analytical queries, but you don't want to burden every query to have to go read that data to get an answer. You want to compartmentalize it. So the partitioning of the lake house is a great fit for this. And you can compress data very well in Parquet with that
33:19time series data, so it is a very good fit for this use case. Also take a look at the variant data type if you have data that is a little bit varied, like log data. If you've got JSON logs coming in, I highly recommend using the variant data type supported in DuckDB and Parquet and
33:35other engines as well. It gives you much faster reads of semi-structured data by breaking it apart instead of storing it in just a blob of text. So it's a great use case. I love what you're thinking of. So thank you for the question. All right.
34:05So we had a question from Levy. Thank you for your question. What do you think about open source tools having support for reading and writing? So right now, a lot of the other tools are mostly reading with Duckleg, but they're working on the writing side. So Spark and Trino can read Duckleg already. Pandas and Polars, I have to look into that a little bit more. I'm not sure the status there. But Data Fusion is another example where they can already read Duckleg. So
34:27it is already happening. It's moving, and we'll get there. It's coming. It's being added to other fun places as well. All right. Well, let's talk a little bit about the catalog. These are excellent questions. Keep them coming, and we will have dedicated time at the end as well to get to a
34:47lot of the questions. So the catalog is a really important part of Duckleg. It has a lot of things that are important to it. The first thing, kind of its core responsibilities, it stores metadata. Well, metadata is kind of a fluff word. Like, what am I talking about? What is metadata? Data about
35:05data. It's just which Parquet files hold what. This file goes to table customer. This file goes to table inventory. This table goes to, you know, log events. It tracks when those files were added. So, you know, if I wanted to wind back the clock to yesterday, I need to know when the files arrived so I know how to wind back the clock and do time travel. It also stores your configuration.
35:29You know, what are your settings for your Duckleg, you know, that you've configured it with. But it also can hold your business logic. So you can do views. You can do macros and functions. So you can actually have a central storage location for combining tables as well right in your lake house. Another key responsibility is managing concurrency. And this is why you want
35:50to choose a database that's a multiplayer database for doing this in production. So you want something like Postgres or MotherDuck. So that way you can manage that concurrency of multiple different systems working with it at the same time. And with Duckleg, you get full multi-table transactional guarantees where you can do something like delete data out of your active
36:15table and move it to the archive table in one atomic step. So there's never a case where you accidentally can duplicate and re-duplicate data. Having that ACID consistency is a big part of why lake houses were created. And Duckleg out of the box supports that across tables. The next thing the catalog is going to do is it's going to use that metadata that it's got stored. And when you go to query data, it's going to decide which files you should look at,
36:41which files are currently valid, which ones are no longer necessary. They're just there for time travel. And then it actually uses statistics. And it can look into the file and say, hey, you wanted data from customer A. This file doesn't contain any data from customer A. All of its data starts with customer C and goes to customer Z. And it'll actually avoid reading that file entirely. And so the catalog is able to decide what files to read. And it does that in
37:05a very quick way. It just talks to the database. It runs a database query to do a where clause to say where your customer is in the range of the file. And that way it's all in one shot. You can figure out exactly what files to read just with one database lookup. And we already talked about it as well. It also stores your inline data. So the catalog also does double duty. It's the
37:27storage engine in some cases as well. We already have a database. Let's use it for what it's really good at. And this catalog is not some opaque thing. I think in the Lakehouse space, the word catalog is very messy because there's so many of them. They're all different. They're, in many cases, you know, open in name, but very proprietary in terms of how to actually operate them. It's
37:50very hard to operate catalogs. In contrast, this is just a database. In many cases, it's Postgres. It is bread and butter for the last, you know, 20 years. It's great because you can see it. If you want to know how many files you have in your Lakehouse, you run a query that says select
38:08star from DuckLake data files. And then you do a cal star. If you want to see, you know, how many files are larger than this size, throw a where clause on it and say, hey, where the file is greater than 10 gigabytes or whatever. So you can introspect your Lakehouse with SQL in a really
38:27easy, easy way. And that's really powerful because it adds to the simplicity. You don't have to know anything specific about Lakehouses for this. It's just a SQL database. Just, you know, you can ask it or you can point your agent straight at it and say, hey, if you need to get some high level information, don't bother reading the raw data. Just go ask the catalog and you'll get that even
38:46faster. So this is a diagram. This is the deepest of the deep dive, I would say. This is a diagram of how the concurrency is managed with this catalog. So like other Lakehouses, DuckLake uses an optimistic concurrency control setup. What does that mean? It means that
39:10as requests come in, it's not going to prematurely lock your system for you. It's going to allow you to go through. And when someone else comes along and tries to go through along with you, it will reject their attempt. Rather than locking it ahead of time, both kind of proceed and then the other
39:30gets canceled and undone. And that allows you to have really high throughput in the common case where there's not a lot of overlap. So it's a really modern way to approach this. And this is how that operation works. Within DuckLake, the first step is actually to write the whole parquet file. You might be thinking, hang on a second, I wrote my whole parquet file. Doesn't that mean
39:54that it's going to be seen by other people? Like how do we prevent it from stepping on each other's toes? I've already written that. rainfall, doesn't that mean like it's there too early? Well, it's not because we haven't told the catalog about it yet. So no one's going to read it because they don't know it exists because they're always talking to the catalog. Now, what's the benefit of that? It means that if you have five different systems writing data at the same time, they can write
40:18that data. They can do the slow part totally in parallel without even talking to each other. Just put the data where you want to go. And then at the very last minute, once it's done, then you just check it in and say, hey, I've been working on this PR for two weeks, right? I'm going to do my Git commit now. And then it's a very quick operation to just say, oh, that file has this metadata. Now make everyone aware. And that's a key part of the architecture
40:43to think about is that it is very parallel because of that design decision. So what if there is kind of a conflict here? So you're going to start your transaction. You can look at did my snapshot ID change? Well, what does that mean? We keep track globally of each operation that comes in. And that's how we synchronize things across tables. And this
41:03is a primary key in Postgres with an auto incrementing ID. It's really that simple. It's has someone else done something while I wasn't looking? And if nobody has, I go ahead and I update things to say this file is now available and I commit and I'm done. So that's the fast path going kind of straight down here to the edit catalog tables to the commit. Now, if the snapshot ID changed, it means, hey, somebody somebody already jumped
41:28in before I, you know, without me seeing it. Did they do anything to change things for me? Did they change the schema? Did they add a column? Did they remove a column? Did they do something different? If they didn't, which they typically don't, OK, just try again and and commit. So what I said before, you know, where it's optimistic concurrency control, where it rejects things, it's not a one and done rejection where a lot of time you have
41:51to worry about this at all. It's all under the hood. It'll do an auto retry. You can configure how many times you want to retry it. There's really very little overhead to doing that. And it'll just simply basically get back in line and say, oh, hey, OK, someone got in first. Now I'll try again. Oh, cool. I'm I'm first in line now. Now I'll go ahead and commit. Now, if the schema did change, it's impossible for DuckLake to know what
42:14you want to do here. OK, we deleted the column and you're trying to insert that column in there still. Do we want to add the column back or do we want to drop it silently? There's no way to know. It's an application decision. So we will abort that transaction and give you a clear message about why. And then you can retry it from your application and make the right decision. That is our catalog. That's probably the deepest part, but the compute
42:38part is still very fun as well. So let me look at a couple of questions here. How do we handle Postgres long running transactions for big inserts? Now, the key here is that they are not actually long running transactions. The transaction in Postgres only starts after
43:02the file is already completely written to object storage. And so it is an incredibly short running transaction. And that is why you can get 100 transactions per second on a DuckLake. So you're asking exactly the right question, like, hang on a second. If I'm writing all this data to a lake house, I'm managing it with a database. I don't want that transaction
43:22open that long. That causes havoc on Postgres. You're exactly right. So we don't. So that's an excellent question. Thank you for asking. Fantastic. Keep your questions coming. The catalog is a pretty deep part. I want to make sure I get your questions answered about it. Put them in the channel chat, and we'll get to them shortly. I'm going to jump back into the compute
43:44side. So what is the role of that third leg of the stool, the compute? The goal of compute is to read as little data as possible, to cache data you've already seen before, and then to do the SQL execution part. So the catalog has already decided what files to read. The compute now
44:06decides, within that file, do I really have to read this whole file? Or can I read just pieces of it? And Parquet, just like the DuckDB format, has these compartmentalization capabilities where you can skip around within the file and only read little tiny pieces of it. And that can be dramatically faster, excuse me, dramatically faster than reading the whole file. So we're going to
44:26pick out which columns we want and only read those. We're going to pick which row groups, which chunks of rows we want to read and only read those. And we do that with statistics. Those statistics come from the Parquet file. So we actually go out, we talk to object storage, we say, hey, I want to read this file. Let me read the stuff that's stored at the footer, the bottom of this file. And we'll
44:48see that in a diagram next. Once I've read some data, I don't want to have to go back and forth to object storage more than I have to, you know. And that's part of the whole architecture of DuckLake is doing that a whole lot less. And another part of that is caching. And it'll automatically cache this data in memory for you. So if you're doing repeated queries on similar data, a lot of it will be cached. And then we're actually going to do the SQL execution. We're going to do
45:11the group buys, the joins, all of that. And we want that to happen as close as possible to the storage. So that way you don't have to send unaggregated data all the way out somewhat really far away to a client to then aggregate it later. So this diagram, I think, helps me a little bit kind of understand the separation of concerns here. The catalog is going to give Compute a list of files.
45:35Hey, these files might have data in it that you want. Take a look at them. And then Compute is going to say, OK, I'm going to look at the footer of each of these files that contains metadata about the file there and then decide which pieces of the file to read. In some cases, it might choose none. It might be able to skip that whole file. It just couldn't figure it out until a little bit
45:56later on. How do you improve these two pieces? On the catalog side, the way you improve this is through partitioning. You can partition by time. You can partition by customer, partition by order item, by product, by region. When you partition, we can automatically skip a lot more files at
46:18the catalog layer. On the Compute side, how do I skip around within a file? If your file is randomly ordered, you're probably going to need to read a little bit from all the pieces of a file. What you want to do is you want to order your files. So that way you only have to read a certain piece of them and you know that piece because it's in order. And so you improve that using sorting, which you can
46:41also configure in DuckLake. So both of those you just kind of set up when you set up your table. Hey, I want to partition by this. I want it sorted by that. You can change it at any time, but that's going to speed up your read queries if your read queries can skip a lot of your data. And so knowing that these options exist, knowing at what layer it helps, catalog layer, compute layer, that's a lot
47:02of the levers for tuning and improving performance in DuckLake are those two pieces. All right, we have a little bit here about what is MotherDuck. I'll mention it very briefly and then we're going into Q&A time. So with MotherDuck, we are a serverless cloud data warehouse. We're also a serverless cloud data lake house. So the only difference between a warehouse and a lake house
47:23on MotherDuck is this little type DuckLake you see in that code snippet example. You can create a DuckDB style database with MotherDuck enhancements for the cloud, or you can create a DuckLake database just by changing that one bit there. So very, very easy to use DuckLake together with DuckDB. So there was a question previously about which one should I use.
47:44With MotherDuck, it's two commands to try both, so you can absolutely use them together. A couple things about the architecture, about why MotherDuck is a good fit for DuckLake as well. It's serverless. Lots of lake houses, you have to have some big cluster running all the time, you know, your Trinos, your Sparks, that's always running, even if you're not querying it. But
48:07that's not the case with a DuckLake lake house hosted on MotherDuck. We will pop up an instance for you in 100 milliseconds, and then you can use it, and then it'll go away in as quick as one second. And that's a game changer for just ease of use and also cost. If you have a fleet of agents, they're very bursty. They'll ask a bunch of questions, then they'll think. They'll ask a
48:28bunch of questions, then they'll think. Are you paying that whole time? Other systems, you might be. Now, as many nodes as you need. This is key to the DuckLake architecture as well as MotherDuck. Really, you can have lots of individual compartmentalized instances, and they're all synchronized together, like we saw with the DuckLake catalog. They're not all tiny. You can also
48:51have huge ones. So you can have some that are up to a terabyte of RAM, and you can have some that are small as well. And we also have that dual execution, which we talked about, which means you can use your laptop when you want to, to have incredibly low latency without having to go back and forth to the Cloud as well. So nothing to give up from the delightful DuckDB experience that we love.
49:12So we're moving into Q&A, but I also want to mention that we still do have our free book available. So if you wanted to go ahead and dig into this even deeper, I'll post a link back here again. You'll get the first three chapters in your inbox in a PDF, and we'll just email you once a month with the new chapters, and you'll be getting other updates as well.
49:39All right, it is Q&A time. So if you haven't gotten your question in, now is a great time. I'm going to take a read back on a couple of questions. All right, Roberto, you might need to clarify your question a little bit. I'll post it up here just so that way you can ask it a little bit more detail, then I can get it fully answered for you. So why is the DuckDB catalog able to handle multi-user while the normal DuckDB is not?
49:59So you might have met MotherDuck. Why is the MotherDuck catalog able to handle multi-user? And that's because we've enhanced DuckDB and made it cloud-native to where we can accept multiple different connections from different places. So that's part of the investment we've made in building MotherDuck is to make it multiplayer. Now, if you're asking about
50:22DuckLake open source, the DuckDB catalog, the DuckDB catalog with DuckLake actually cannot handle multi-user. The only way to handle multi-user in DuckLake is to use MotherDuck or use Postgres or use the Quack protocol, which enables multiple connections to DuckDB. And that's in beta right now.
50:46So those are your options. If that did not answer it, please jump in and ask a follow-up. I will try and get to it. Okay. Chandrakant, thank you for your question. When you say the catalog has figured out what files need to be read, isn't the figuring out part of the engine compute as well? We did talk about that a little
51:09bit in tandem, but I think what's interesting about DuckLake is just how much it's able to do in the catalog. So we're writing SQL queries against this catalog to get these answers. And those SQL queries have where clauses in them that say where my where clause from my user query, you can push that all the way up into the catalog and say, if I care about, you know,
51:33data from the last week, I can push that filter of weeks into my catalog query on my catalog database and say, I'm asking for all the files, but don't give me all the files. Give me all the files from this week. And so, yes, the very, very first step, you're exactly right, comes from the compute to take the user's query and figure out, okay, there's a filter on date. But then the hard
51:57work of doing the filtering on the files is actually inside the catalog, which is really powerful. That's also part of why on the MotherDuck side, we use the DuckDB MotherDuck catalog. It's because that's an analytical question. Doing that kind of quick filter, which could have any type of where clause shape, not necessarily a good fit for an index, right? That is an analytical
52:17question. And so it helps to have that analytical database even as your catalog. Thank you for the question. All right. Let me see. Nick, thank you for your question. Can we currently tune the
52:42incremental update staged in the catalog database threshold and write to a par case cycle on MotherDuck? Is it handled through SQL statements? Great question. Yes, you have full control of this on MotherDuck. So there's a single statement you run that says, what is my row count threshold for inlining? And that's, you know, if I insert less than that amount, I want to put it into the
53:04catalog. So something like a hundred rows or a thousand rows is probably a very reasonable number there. And we'll put that in the catalog for you. And then when you want to flush, it is just another SQL statement. You call a flush procedure and it will run. On MotherDuck, we actually have a scheduler now and a scheduled runtime called flights. It's something where you can author
53:26it directly. You can author it through an agent and it will run Python and SQL. So you can just set that up on a schedule and it can do a check to say, hey, do I have enough data to where I want to flush? I do. Okay, great. And I can flush. And so it's fully customizable, but fully built into the platform. So you can just schedule it however often you'd like it to be. And it is all in SQL.
53:48Thank you, Nick. All right. We had a question about Looker support. I have to check on that briefly. I have to double check on that. I think we're close if we're not there already. So take a look at our Postgres endpoint on MotherDuck if you're looking at Looker
54:11for DuckLake. MotherDuck is probably your quickest path to Looker support for DuckLake. Is it easy to convert existing OpenTable catalogs to DuckLake? Yes. So the metadata can be copied over in one statement from Iceberg and the files are forward compatible where you can reuse your Iceberg files in DuckLake. So you don't even have to copy your files over if you don't want to.
54:36It's a little bit different than Delta. So your migration process from Delta would probably be inserting and moving your data over. But for Iceberg, it's a metadata only copy, which is really seamless. Thank you for the question. All right. We have a question from Donna. If the setup is serverless at MotherDuck and I have a constant stream of users,
55:01doesn't that increase my costs? Doesn't that cause delays? Excellent question. Does MotherDuck provide dedicated servers? Yes. We really have kind of both options where our servers stay up for a second if you want them to. Our larger instances stay up for a minute. So if you have other requests coming in, it's not starting from scratch every time. So we get the benefits of
55:24serverless, but we're not forcing you to restart from zero on every request. So you get the benefits of serverless without sort of upfront cold start costs on every request. So it really is the best of both. It's a great way to go. And so, yes, we do have that kind of data key adoption as well.
55:40Thank you for the question. Does DuckLake cache parquets that it reads from S3? DuckLake does cache parquet files from S3, but it caches them in memory today. It does not actually cache them on disk. There's some extensions that are working on that to do some caching on disk,
55:59but in the DuckLake extension itself, it caches it in RAM today. Thank you for your question. Thank you for your follow-up question, Prabhakar. I might need to learn a little bit more about your
56:21question there as well. So you can follow up here in the chat or we can follow up later as well. Following up on long-running Postgres transactions, the answer applied to S3 storage as well. They do face, you face inserts with big inserts, face issues with big inserts. With DuckLake, there's a setting called target file size. So if I am doing an insert
56:44where I'm inserting a billion rows, I'm doing a backfill, I'm doing my first time load, I'm doing a bulk job overnight, that doesn't all have to go in one file. That can be split up into multiple different files. And that alleviates a lot of that, I think, because it'll do that in parallel and write out those files separately. And it'll be at the file size that you want it to be.
57:05So I believe that we handle that as well. Let me know if you have any other questions there. Brandon, any quick tips on maintaining the Lake files over time, similar to Vacuum? It's a great question. So the way that DuckLake does this is really convenient. You set up a
57:26couple of settings of how you want it to be, and you can run one command, and that is checkpoint. So you set up things like how far back do I want to time travel? After I expire something from time travel, how much longer do I want to wait before I actually delete it for real? So you can give yourself a couple day grace period there as well. So you can make sure you don't need it. And then
57:47if you accidentally need it, you have even extra protection there. There's also settings around how big do you want your files to be? For example, how much do you want inlined? And then when you run checkpoint, it'll do them in a series of steps where it'll flush your data, it'll compact your data, and vacuum up and remove your old files as well. So very, very straightforward. It's all SQL
58:12commands. And on MotherDuck with a flight, you can set it up on a schedule. I actually have an example flight for that. Let me pull that up and send it your way. So I'll send that to you as we go here. Thank you for the question. All right.
58:36Well, thank you so much for all of your questions, everybody. We have run out of time in my favorite way possible, which is because we had so many awesome questions. So once again, if you want to read up on this, hop on in and grab a copy of our book. You can pass that out to your agent as well, or you can use it for your own reference. And we're happy to answer more questions. Reach out to us as well. I am very, very hard to find. Alex at MotherDuck, thank you so much for joining us.
59:01Reach out to us as well. I am very, very hard to find. Alex at MotherDuck. com. So it should be a note. I love talking about this stuff, as you can tell. Thank you all for your questions. They were really, really excellent. And I look forward to diving in deeper to the lake with you.
