0:00Hello, hello, everyone. Welcome to our live stream on the DuckLake lake house. Today, we'll be going from getting started to going fast. My name is Jerel. I'm on the marketing team here at MotherDuck, and I'm joined by Alex. And as we get going, as we let people come in, I'm going to start with a quick poll just to kind of see
0:20how familiar are all of you with DuckLake? Have you, I mean, I didn't leave. Have you not heard of it? Because hopefully, if you signed up for this, you've heard of it. But have you not tried it yet? And have you just kind of played around with it a little bit? Or are you running it in production? Give it a little bit and see what all, I think if you just type in chat, one, two, or three, we can see
0:45some results. Yeah, two, play around with it a little bit. Okay, welcome, welcome. We'll get started in just a little bit. Sounds like, you know, a lot of twos, maybe, maybe there's a, maybe a two, maybe three, running it, almost running it in production. A couple running it in production is awesome. Some haven't tried it yet. Love it. We'll let this run for just a little bit longer.
1:08Let's see if anyone else won. A couple, a lot of twos. Awesome. Okay, with that, and we'll give it five more seconds, and I'll close this one down. And we'll kind of, we'll continue on. Uh-oh, a lot more people haven't tried it yet, which is good. Well, hopefully, by the end of today, you will have at least tried it, you know, played around with it a little bit,
1:29and maybe we'll help you get it in production, or maybe even, you know, for those of you that are running it in production, we'll hopefully, you'll learn some tips and tricks on making it even faster. So let me figure out how to stop this poll, and we'll leave it at that. Seems like, you know, we're relatively evenly split. Most people have played around with it a little bit. Some haven't tried it, and then a few of you are running it in production. Awesome.
1:51Like I said, my name is Jerel. I'm on the marketing team here at MotherDuck. A few housekeeping items. We'll be recording this session, so, you know, if you have the job, if you want to watch it again, we'll send out a link to it if you registered via email, and we'll also post this on our website later on. And we'll have, agenda-wise, we'll kind of keep it, hopefully, nice and tidy.
2:13We'll start with just kind of, what is DuckLake, and why is it exciting? We'll talk about, you know, what MotherDuck is, and how it relates to DuckLake, and how you can use the two of them together. Then Alex will do a short demo of how to get started, and then we'll finally close up with tuning your DuckLake, how to customize it, make it faster, optimize it, and then also we'll have some time for some Q&A at the end.
2:38If there's other questions, you know, after each section, we'll do questions, take some questions there as well. So feel free, if you ever have questions, put them in chat. Love to see how you guys are using DuckLake, and what questions you have. And with that, too, we'll do one more quick poll, and then we'll let Alex get going with his talk. I guess, you know, it's covering Alex, I don't even know how to move it. Let's do this.
3:06Which Lakehouse technologies are you most familiar with? None, Iceberg, Delta Lake, or DuckLake? I mean, there's also other options out there. This tool only lets me pick four options. So these were the four that I chose to pick. We kind of, we didn't, we purposely said, hey, Hudi, we'll relegate you to not on the list. So you can also type, you know, you can type in chat if you use something else, too.
3:32You bet. And the questions are my favorite part. So don't hesitate at all. If you've got any kind of question, throw it in the chat. You're probably not alone. And also, if you guys know the answer to a question in chat, we'll love to hear kind of your perspective as well, especially when we get into tuning. In some ways, it's an art more than a science. It's also, it's just very experimental. So I'd love to hear thoughts from folks that are running in production in particular. Let us know how that's going and the advice that you have.
3:57Yeah, awesome. Love it. All right, let's, let me close this one out now. It looks like it's relatively evenly split. At first, there was a lot of DuckLake, but now, you know, we're pretty evenly split between Iceberg, Delta Lake, and DuckLake. So with that, let me hide this. And I'll put Alex's slides back up. There you go. And go ahead, Alex. Well, thank you. Well, welcome, everybody. First, I'll say a quick howdy. Howdy, I'm Alex.
4:23Really excited to be talking with you about this today. My background is industrial systems engineering, but then decided that I really like this data stuff. So I spend a lot of time at Intel, and then I work part-time for DuckDB Labs on the documentation and blogging side of things. And I've been here at MotherDuck for right around three years. Initially doing a lot of customer support, bringing people into production, and then now I'm on the DevRel team.
4:46But I like ducks a lot. So you saw a lot of that is very orange. Spent a lot of time working on DuckDB-related technology since 2020 or so, been involved. I've read just about every PR in the DuckDB repo across that time. So I tried to stay pretty up on what's happening there. I'm also a co-author on the upcoming O'Reilly book, DuckLake, The Definitive Guide. We've released two chapters already.
5:09And Jerel put the link in the chat. You can head there and download those first two chapters. We'll send them to your inbox, and we'll send every subsequent chapter to your inbox as we release it. Chapter three is an architectural deep dive. That's coming next. And chapter four is performance tuning. So you're getting a preview of that today, but we're going to do even more experimentation and benchmarking for the book. So definitely sign up for that. It is free.
5:34So what is DuckLake? Why are we so excited about it? I'll go faster because you guys are fairly familiar. DuckLake is an open table lake house specification. So it has Duck in the name, but it's a fully open spec, can be implemented by anybody. And there's a few implementations in addition to the DuckDB implementation already in Spark, Trino, and in Data Fusion. So it is truly an open spec.
5:58The key reason we love DuckLake is the whole design goal of DuckLake is simplicity. And that is not the word that you hear as the number one feature of any other lake house. It's designed to be simple. So start by making it simple. Start by making it elegant. And that elegance leads to the speed, leads to the scalability. And the key architectural decision to enable that
6:22is that we keep both our catalog data and our metadata data inside of a SQL database. That could be DuckDB. It could be SQLite. It could be Postgres, or it could be MotherDuck. And there's lots of different ways you could choose to mix and match those. So to illustrate the simplicity, the getting started for DuckLake are these three commands. Install it as a DuckDB extension,
6:47which is just an install command. You'll attach a catalog and then you'll start using it. Simple as that. A little bit more about DuckLake. We believe it's really the future of OpenTable formats. We are very, very excited about it. It has partitioning capability so you can scale up to petabyte scale data.
7:10However, you retain high ingestion speed as well. You're able to load data in very, very quickly. That's sort of a lake house style approach where you can bring your data in as it is and then compact it later in the background. On MotherDuck, you can bring your own blob storage. You know, likewise on open source, of course you have full control over the storage and it's using industry standard parquet and industry standard blob storage technology.
7:34So it's very, very open. And a key differentiator, because of that SQL database architecture, your query latency can be very, very low. So instead of queries taking multiple seconds to do even basic queries, you can have millisecond response time, you know, hundreds of millisecond response time, which is just fundamentally different than other architectures.
7:57That also means you can ingest very, very frequently, you know, 30 times per second, whereas you can typically do a lot less with other formats. So it really opens the aperture of what's possible. Now, another way to understand DuckLake is a little bit by analogy. And this is comparing to Iceberg. Delta Lake is fairly similar here. It has a few less layers of the hierarchy, but the architecture is quite similar.
8:22So at the bottom, you have your data files. And in both Iceberg and DuckLake, those are parquet. It's a columnar format. It's got indexes built in in the metadata. It's got strict data typing. And we actually have the exact same, you know, almost identical format as Iceberg in DuckLake. So we didn't reinvent the wheel. We're using best of breed of what's out there.
8:46The next layer is that metadata layer. And this is how Iceberg tracks where all these parquet files are, which tables they relate to, which versions of which table it relates to. So if you delete some data out of a table and then reinsert over top of it, we have to kind of track that change history. And that's handled in the metadata layer. One layer above that is the catalog. And this manages multiple different tables.
9:10It manages concurrency across those. And you'll notice in that diagram on Iceberg, that's a database canister. Well, it's actually a web API with a database behind it. And with DuckLake, we said, hey, if you have a database in the stack, let's use it for everything that it's really good at. And relational databases are very good at managing high concurrency, transactional workloads,
9:35and metadata operations look a lot like that. So with Iceberg, to query your data, you have to do four round trips. You have to talk to the catalog. You have to talk to your metadata file. And then you have to talk to your manifest list. And then your manifest file. And only then do you go get your data. And those have to be in sequence because they each depend on each other. And everything in that metadata layer
9:59is talking to object storage, which is great for throughput, but it's very slow for latency. It could be 100 milliseconds each of those requests. So even in the very, very fast case, it takes often seconds to read data out of a traditional lake house format. Whereas with DuckLake, you're talking to one relational database that can be measured in single digit milliseconds to go talk to a database. Very, very low latency.
10:23And at that point, then you can just go grab and start reading the parquet files you need. Another way to think about the differences, another analogy, we are iterating on the lake house architecture and we're making an adjustment, which is that we're taking the catalog and metadata and managing them both inside of a relational database.
10:47And that we think is a much better architectural fit for what relational databases are really good at. But again, it's not reinventing the wheel. It's still parquet on object storage and we still have scheme evolution and time travel, all the things that you love about lake house formats that innovations that Iceberg and Delta and Hudi brought, but in a much simpler way. And that was the crash course on DuckLake.
11:13Jerel, any questions at this point? Yeah, let me see here. There's a few that maybe we'll answer later. Maybe this one here quickly. I think any path forward for K8 Helm chart on-prem implementation? I don't even know. That's a good question. I see no barrier to that. I don't know if it's been built yet.
11:38DuckLake, you can deploy it in a variety of ways. I'll show that very, very soon. It's very amenable to be deployed on-prem and all the resources that you'd be managing are things you could manage with Kubernetes. So it's managing a database, most likely Postgres, and managing DuckDB compute, which you can manage with Kubernetes as well. Cool. Let's just do this one other one that I think we might not cover later,
12:02which is, can you explain how the tables in DuckLake map to the parquet files? I added some rows into a table in DuckLake and I did some deletes, but I just see one parquet file with a deleted row. Where is the other data in the table? Sure. So the structure for DuckLake is set up in an append fashion, where as you add data in, we create new files on your behalf. And when you delete data
12:27to maintain the ability to time travel to see the data that used to exist, what we do is we add a delete file and it's following the iceberg spec for how delete files are managed. Currently using the V2 spec, the V3 spec is going to be released with the next version, I believe. It's kind of in testing mode right now. So you can see the deletions and the original data
12:51as you're doing the inserts and deletes. If you want to merge those and clean them up, there's a metadata function that you call that says merge your deletes and that will actually read the raw data, read the deletes, and rewrite a new file that actually removes that data. And then you can run another cleanup maintenance operation to remove it from existence if you don't want to time travel back to it. In DuckLake,
13:15there's also a special feature called inlining that might also be at play here. If you insert, by default, inlining is enabled for rows inserting 10 rows or less. So if you inserted five rows in, you won't actually see any Parquet files generated. That's because we have a database there already and we can use that database to store small row counts of data
13:39and flush it out to Parquet later. So I've got a diagram on that. Stay tuned. If you're playing around testing it live now, try inserting 100 rows. It might behave a little more like you expect. Cool. Thank you. All right. I think we can go on to the next section. And I'll give a shout out to Sahil. I think you're exactly right. I think inlining is what's happening there. So thank you for chiming in and helping out as always.
14:03Well, also, we'll cover a little bit about what is MotherDuck and how it works together with DuckLake. So what is MotherDuck? We are a serverless cloud data warehouse and the engine at our heart is DuckDB. And we are big fans of DuckDB. We think it's pound for pound the best data engine out there. Our emphasis is really on being very, very straightforward to use, similar to the DuckLake ethos,
14:27where we think that your time is incredibly valuable. The key thing we should optimize for is time to insight. How fast could we help you answer your business questions? So we like to call ourselves infrastructure for answers because that's what a data warehouse is for at the end of the day. You can connect through the DuckDB drivers. You see that example connection there. Again, it's a couple lines of code to get started with MotherDuck, just like it's a few lines of code to get started with DuckLake,
14:51as we'll see in the demo. A bit more about the architecture so we can see kind of how it relates. So MotherDuck is serverless, which means we only spin up when you connect and we spin down when you're done. And we can do that very, very quickly. And that is very efficient from a cost perspective. You only pay for what you use. And it's also very different than most lake houses.
15:14Most lake houses have a big cluster that is used to manage it. And you might be running Trino on top of Iceberg or you have a long lived set of compute that's up or at some capacity all the time. And MotherDuck takes a very different approach and will spin down aggressively whenever you aren't using it. We are unabashedly single node. What does that mean?
15:38It means each query runs on one piece of hardware. However, if you have many queries that you want to run at once, you can have many individual pieces of hardware. And we believe that that lets us use much more efficient algorithms where we aren't taking one query, splitting it up on thousands of nodes and having to then recombine it, doing shuffle joins, broadcast joins. They're very computationally expensive
16:02and expensive in dollars. By being single node, we can be more efficient. And lastly, MotherDuck has this unique capability of doing dual execution where some of your queries, you could choose to run them in the cloud or you could choose to run them locally or even a little bit of both in the same query. And that is very helpful for things like caching, for follow-up analysis, for slice and dice, or for just doing your development on your laptop
16:26before you push to the cloud. So a couple of quick diagrams of different ways you can deploy DuckLake and how it fits in with MotherDuck. So with DuckLake, you have three different components. You have a storage component, it's going to be parquet files. The question is, where are you storing those parquet files? Your metadata is your catalog and your metadata together,
16:49which is kind of abbreviated as your metadata. And that's a relational database and you have a lot of choices of which type of database. And then lastly is your compute. You can run that compute locally or you can run that compute in the cloud with a variety of engines now. So the first kind of configuration for DuckLake you could use is lightweight and local. You don't even have to run an object store API on your laptop.
17:13You could just use files and folders and run it right on your SSTA of your laptop. Your metadata database could just be DuckDB running locally, and you're using that laptop CPU you've already paid for. If you want to then do cloud persistence with local compute, you can use any object store, AWS S3, Google Cloud Storage, Azure Blob. And then your metadata database,
17:36you'll want something multiplayer. So you, I would highly recommend Postgres. And then you can still run your compute on your laptop, but everyone connecting to the DuckLake can run it on their own laptop. So you get a nice distribution that way. Or you can build your own cloud DuckLake where you run your own compute on server somewhere. There's a variety of different ways you can run a server-side compute. Similarly with MotherDuck,
18:01we have a fully managed offering where you can have storage managed by a bucket that we manage for you. Our metadata is managed in a MotherDuck catalog, and we have the MotherDuck compute, which is that nice elastic serverless compute there. And dual execution means you don't have to give up the laptop side either. If you'd like to compute on your laptop for that low latency interactivity, you sure can. We also have the option where you can bring your own bucket.
18:25And this is important, I think, for how MotherDuck and DuckLake work together, because this keeps the openness part. This is a bucket you own. Those are your parquet files. You can see them. You can touch them, almost, right? They're still just bytes, but you can feel them and know that you own them forever. It's an industry standard format that'll last for 50 years, right? So it's really nice to have that portability. So that is what I had to cover
18:50on the high-level intro side. Any questions on kind of DuckLake, MotherDuck, how they fit together before we jump into a demo? I don't think I saw any come in. Hopefully, whoever asked before, David, hopefully we answered all your questions about, I know, the relationship between DuckLake and MotherDuck.
19:16Otherwise, I think we can keep going on to the demo. Sweet. Well, thank you for your questions. Keep them coming. We will also get to them as we go. So this is a look at the DuckDB UI. To enable this, you just have to download the DuckDB CLI. There's a single curl command that you can run to go ahead and download and install it. And then you'll execute one command
19:39to bring up this UI, DuckDB-UI. And then you're here. So this is using the CLI, the command line compute, with a visual interface on top in the browser. You see I'm running here on localhost. So this is a fully open source DuckDB approach here to start with. And this. . . this UI looks a lot like the MotherDuck UI because we've contributed it back to the community
20:04where anyone can use it for free. We made that available to everybody. So the first thing to use DuckLake, you'll go ahead and install it and load it. And that does tend to happen automatically when you start to use it. But that's just to let you know a bit about what's happening under the hood. We're downloading an extension from an S3 bucket, and we are putting it in a common location to find and then bringing it into
20:26memory. So nothing too surprising there. The next thing here is how we actually go ahead and connect to our DuckLake. So we're going to do that with an attach command. This is a SQLite style command. And we are attaching a DuckLake type catalog here. So we've got DuckLake colon to indicate that we want this to be DuckLake. And then we could optionally specify the type of database here, SQLite,
20:50DuckDB, Postgres. In this case, we're using the default, DuckDB. So we omit that. And then we give it a name. And this is going to be a file on disk where we actually store the catalog. Technically, this is a . DuckDB file. So you can say . DuckDB here, . db, . ddb, at whatever extension you could possibly desire. You can get creative. I'm calling it . DuckLake
21:14so that I remember what the heck that file is when I go to it later. And then from there, we give it a name. We're going to call it Lake, and a data path. So this is just the name of the folder where we want to store those parquet files. And later, you can use this to point to an S3 path if you'd like. For local, this is just a folder name. And we'll create the folder if it doesn't exist yet. So I'll go ahead and run that. Hey, 40
21:38milliseconds. I have a lake house running on my laptop. So let's prove that. What does that look like? Well, now I'm using Lake here because I ran use. And now you can see that's what's highlighted as my active database. And so I'm going to create a table inside of that DuckLake database. And let's go ahead and query out of it. So we'll say, from
21:57testing. And DuckDB fans will recognize I didn't put any select star in there because it's optional in DuckDB. But this is the same as running select star from testing. And I've got two columns, some nice, very generic data here for our quick little demo. So I've got this table. I've done one insert into it. Let's look at what the files look like. This
22:20is a nice benefit of DuckLake. Part of the simplicity is this catalog here is already visible in our UI. And this is the catalog. This is all the metadata about our database. You can see what columns there are, what data files there are. If you're using macros or views, we have that information. And you can just quickly poke around and see what we have.
22:41So here's the DuckLake table. We have one table ID of nice UUID here. And it's table testing, which we just created. We can look in DuckLake column. And we can say, hey, what do we have in here? OK, we've got table ID one. It's our testing table. And we have two columns. And what are their names? Oh, very creatively named, I and J. Should have
23:03chosen something that looks a little bit different in small font. But I apologize. We've got columns I and J. But not only can you just look at each table to really know what's happening with your data lake, you can query them and join them together. So here, I'm going to look at our data file table and join with our table. So I get the name of our table and see how many data files we have. And you can see here, we just have one parquet file so
23:25far. And that's because I inserted 100 rows, not under 10. We'll get to the secret inlining, not secret, the fantastic inlining approach here in a bit. So what happens if I put in a little bit more data? So I put in another couple rows. Let's go look at our data files again. And you'll see now I've got two files. And they have
23:47different snapshots. So that way, we could travel back in time. If we want to see what did my data look like in the first snapshot or at this particular timestamp, we can still see exactly what it would look like only at that particular time. From there, there's a lot more that we could pull in to understand a bit about how DuckLake works. So here, I'm joining to a few other things. I'm joining to my column stats
24:09and my column table. So I just want to say, hey, what statistics do I know about these parquet files so I can read only exactly the file I care about, which we're going to see is our main way of doing performance tuning. So I could see that I've got my table name testing. I've got my columns i and j and these two files. So data file ID 0 and 1. And it lists the min value and max value of each
24:32of those files for these columns. And you can see my column i is nice and ordered, 0 and 99. My column j is totally random. And so you'll see that my min max filtering is almost identical between the two data files for j. But with i, let's say I only cared about data where i is less than 20. I only have to read one file because I can look in
24:56the statistics and decide exactly which file I want to read. And I'm doing that decision making with one SQL query. Figure out, hey, what are all the files I need. We're running a filter, a filter where my value is between my min and max value. Simple as that. Now, there's a lot more that you could do with DuckLake to maintain this
25:17over time. It's also an important performance consideration. A lot less so than other formats. DuckLake tends to need less maintenance and less frequent maintenance. But it's still something that is critical to having a lake house work well. You want to have at least one background job running. So you can, for example, merge your adjacent files. So these
25:36are the two files that we created. I took two files and I converted them into one. So let's look at that real quick. Let's look at all those files and the statistics of those files now. And you can see that I have now combined those together into a single file that ranges all the way from 0 to 999. So they're all together in one file. So it's a super crash course on DuckLake and some
26:01of the nice advantages of having your metadata right there in a database. To do the same in MotherDuck, you could just click this sign into MotherDuck option here. And that will take you to MotherDuck where you can log in. I'm going to log into the DevRel account. And we'll take a quick look. So now I can see some notebooks I've stored in the cloud.
26:24You also can take advantage of the built-in visualization platform we have on MotherDuck called Dives. But I just want to show the slightly different syntax for getting started with a MotherDuck DuckLake. And that is to create a database instead of running an attach command to create a DuckLake. So you do a create database command and you tell us what
26:45type it is. And this will go ahead and create a DuckLake for you. Then you can do all the same things that we just saw before, where you can go ahead and create a table within that Lake and then push and pull data from it. All right. Well, that's what I had on the crash course demo. I will swap on back to talk a bit about tuning. Any questions
27:07about the demo? Yeah, let me see here. I think we had. . . I see. Well, there's one question about the syntax. It says, is DuckLake Catalog 1 in the database schema format? Good question. That is actually not. That's actually a file name. So if I looked on my actual file system,
27:30you'd see a file called DuckLake Catalog 1 dot DuckLake. DuckLake, here's the file extension. A little bit confusing. I probably could have just called it, you know, dot dot something like that. But in this case, that's just the name of the file. Cool. Let me see.
27:48Let's. . . I'm going to save some of these for the longer Q&A at the end. Yeah. So let's go on to the tuning part and then we'll get to the Q&A after that. Wonderful. And keep those questions coming. Like I said, we have saved time for that. We do really want to talk about that. So the next part here is tuning your DuckLake. Some practical tips
28:08for how to get more out of it. The first thing is I want to start with things that you will probably always want to set. Of course, I have an asterisk on always. It couldn't be a talk about a database without some it depends in there. But most of the time, you're going to want to set these three settings right out of the gate. The first thing is you want to use Parquet version 2. So Parquet version 2 is a version of the spec that came out many
28:31years ago that has some improved compression methods. And so it means for more common patterns of data, you'll just get better compression with very little negative performance impact. It's pretty close to a free lunch to have an improved compression algorithm. The only downside here is ecosystem compatibility. Just if you want to use other tools with DuckLake,
28:52just make sure that they can read Parquet v2. Again, it's many years old at this point. It's got broad adoption, but not universal adoption. So that's why it's not the default. So that's the first thing to do. And you'll see that syntax there. Call my DuckLake dot set option. My DuckLake there is just the name of that DuckLake database. So our previous example we used Lake. And then on MotherDuck, I used MotherDuck Lake. That's just the name
29:16of our DuckLake. The next one is to change the type of compression on your Parquet files. This is, again, an industry standard technique. ZStandard is considered really the best in class compression for Parquet. And by default, we use Snappy because that's the default. And that's got the kind of lowest common denominator. Most readers are most familiar with it. But
29:36ZStandard is going to be your best bet. Again, only downsides compatibility. The next one is a little bit more. It depends, but it's still, I would call it an always type of option. And that's a larger row group size. If you're running your DuckLake on the cloud, you should have a larger row group size. If you're running it on your laptop, keep the default.
29:58So when we're retrieving data from object storage, object storage has high latency but high throughput. So that means you want to request things in bigger batches. And there's a paper by the TU Munich, Technical University of Munich, that did some empirical benchmarks of what is sort of the kind of location of diminishing returns for chunk sizes downloading
30:19from object stores for database operations. And it's somewhere between eight megabytes and 16 megabytes that you want to read in one chunk. And the way that DuckDB tends to read is a column row group chunk at a time. And so you want to set your row group size. You can set a row group size bytes parameter. So basically take your look at how many columns
30:42are in your table and then split this up so you get about eight megabytes in a given column. So in this case, 80 megabytes, that's if I had like a 10 column data set, I would consider an 80 megabyte row group size. There are some boundaries here. You don't want to obviously set your row groups to be giant, giant, giant. But just keep in mind that kind of eight megabyte
31:04or so rule of thumb. The next thing that you want to think about when you're setting up your DuckLake is you want to keep your compute and your storage close together. And now this feels a little bit different than the kind of default assumption of, you know, using your laptop as your compute engine. When you want to run DuckLake at scale, you want to
31:26keep your compute and your storage. And that's because the simple fact is most queries filter your data or aggregate it. And you want to do that without having to download it all to your laptop whenever possible. So the main thing is you probably want to run some cloud compute. Also, when you run that cloud compute, make sure to put it in the same region as
31:50your storage bucket. Same cloud, same region. You're going to be moving data back and forth quite a lot. It's much faster and better really cost wise and speed wise to put them in the same location. Then we start to get into some workload specific guidance. It really depends on what you need to tune. Again, one of the big benefits of DuckLake is it's easy.
32:13If you had to tune it all the time, it wouldn't really be that easy. So this tuning is not always necessary. This is sort of an optional thing to get the most out of it. And that depends on your workload. And the first one is really getting data ingested as fast as possible. The first feature of DuckLake to take advantage in this case is data inlining.
32:34So data inlining is in place by default if you insert data less than 10 rows at a time. But you can change that however you'd like. So what is inlining? What inlining means is if I insert small amounts of data, instead of writing a separate parquet file every insert, I'm going to batch those up by putting those as rows in my catalog database first. And
32:57then later I will flush those rows out of my catalog database into larger parquet files. However, things don't disappear when they go into that catalog database. All of your read queries will still be able to read out of that catalog and it'll still be completely transactionally sound. So sort of like you get the buffering type of capability of a
33:16Kafka, but you keep the transactionality of a Lakehouse. It really is a significant simplification over having to run Kafka and a significant performance boost over having tons and tons of tiny little files. So what this practically means is that you can ingest much more often into DuckLake than in other formats, multiple times per second, which will be significantly
33:40better. So if you want to insert in bulk, we will typically be skipping the inlining step and we'll be going straight to parquet. So how do I optimize for that bulk workload that's a little more common in traditional Lakehouse scenarios? The first thing is I'd recommend enabling per-thread output. What does this mean? It means that each DuckDB
34:01thread is going to write its own parquet file, which allows us to blast data at object storage a lot faster. It doesn't make us synchronize things across threads. We could just push the data as fast as we can. There's a trade-off here. You're going to have more parquet files. And so what that means is you might want to run your compaction a little bit more often.
34:21And often that trade-off is worth it. The next thing, if you're frequently loading data in bulk, I would recommend lowering the default retry wait time in DuckLake. And that means that your catalog is going to be able to move faster. So the way that DuckLake handles transactionality, it does optimistic concurrency control like all the other Lakehouses do,
34:42where it will by default try and write your data and then it will force a retry if there's some reason that it's not valid to commit. Like let's say someone else got there first. And typically it waits 100 milliseconds before trying that retry. And for most Lakehouses that could be a while, but for a transactional database, or that could be short. For a transactional
35:03database, that's a long time. So you could shrink that number way down and insert data more quickly. So that is kind of the fast ingest workload. Another common workload that you want to tune for is on the opposite end of the spectrum of how fast can you read data
35:21back out of your Lakehouse. The key way to do this is to read as little data as possible. We saw a bit of that in the demo. And DuckLake does that at two layers. The first layer is what we looked at. File level statistics stored in the catalog. So when you read data out of DuckLake, step one, talk to the catalog. What files do I want to look at? And then
35:44step two is go grab those files or pieces of those files. So we want to make sure that list is as small as possible by keeping the statistics as narrow of a range as possible for the things you're filtering on. One key thing to look at there is partitioning. We'll see that in the next couple slides. Now, the second level of filtering is once you've decided
36:05what files to read, I don't want to read the whole file. So DuckDB and any other DuckLake reader are just going to read just the columns that you want and just the row group chunks. So parquet files are split up into chunks, and we only want to read the chunks that match our filters. And the way to improve this and tune this is with sorting, which other
36:26folks call clustering. Same approach. DuckLake has that capability. So if you want to reduce the number of files that you read in total, you may want to partition your data. Frequently, you can partition by time buckets, year, month, day, or you might partition by something that's domain specific. Maybe you're always querying by customer. Maybe you want
36:47to split out your data by customer to make sure you only read exactly what you care about. But what if you have a million customers? I would not recommend creating a million partitions because those files will be tiny, and you'll spend so much time deciding what to read that you might as well just read a little bit of extra data. And so there's a tuning knob here that is a Goldilocks approach called bucket partitioning. This is also something supported
37:11in the other Lake houses, where I can say, hey, make 1,000 buckets of customers. So if you know which bucket to go to, like for one specific customer, you did save 1,000x the amount of data you're reading. Maybe you won't have too many small files that way. So that's a number that's worth tuning. Somewhere in the hundreds to thousands is probably a good
37:32partition count to have. I don't know if I'd recommend hundreds of thousands of partitions, for example. So there are things to be careful of. Partitioning does take more work on the ingest side, and you can end up with more small files. So those are the other side of the coin. Be careful while you tune it. But by default, hundreds of partitions is probably
37:54a pretty safe sweet spot. The next level of tuning is with clustering or sorting. And the idea is to read as few pieces of Parquet files as you can. So you can imagine in that demo I had a column that was totally random. I'm going to have to read all the rows of
38:12my data to decide what J values I want to grab, because it was randomly distributed. But if I sort my data, I could pick only one row group out of a file. And I tend to aim for a rule of thumb of about 10 row groups per file, plus or minus. So that means that if you sort correctly, you get a 10x boost. And that can be huge. So somewhere around
38:3510 row groups per file is probably what I recommend. That works out pretty well with that 80 megabyte example we had before. That means each file is close to a gigabyte. That's a pretty good file size for a lake house format, somewhere around that gigabyte to half a gigabyte range for each file. The syntax for this is set sorted by, and you can set any expression
38:56or set of expressions. So you can sort by customer, then by date, or date, then by customer, or any other kind of combination. And I'm very biased. This was my main contribution to DuckLake so far, is this sorting capability. So I added the syntax, and I'm really a big fan, and I think it'll come in really handy. So if you have detailed questions on this
39:20one, I think I can help with that. Other detailed questions, I will do my absolute best. So that's a couple of pieces of things you can tune. You can tune your types of files, your par case settings. You can tune how often you do compaction. You can tune your partitions.
39:40You can tune your clustering and sorting approaches. And that can really improve either your ingest speed or your read speed, depending on what is your bottleneck for your application. If you want to learn more, I highly recommend checking out the link in chat to download a free e-book. It's an O'Reilly book, so this is not like a lightweight, not-too-heavy book. not going into the details. We're going all the way into the weeds. We've got fun diagrams.
40:03You'll learn exactly how this works. And we'll send you every chapter as it comes out. But I think we've got a couple of questions. Keep them coming. It is Q&A o'clock. Sounds good. All right, let me work through these. First one here is, how do you manage multiplayer in catalog backed by MotherDuck? You bet.
40:27So we do manage multiplayer. That's part of the reason that MotherDuck was created as well, is that DuckDB is this amazing single-player engine and MotherDuck brings it into the multiplayer realm. So we are able to handle multiple connections to the same database. There are some more things we can do with this in the future, but in general, with the MotherDuck ownership model,
40:50we tend to have ownership by user. And that keeps things nice and sandboxed, especially in the agentic era. It's nice to have things sandboxed that way. We tend to recommend using service accounts, so that way you can have multiple humans logging into the same account to manage a central resource. So that's our main way that we approach it today. It allows you to really scope down exactly what those agents can play with.
41:19Can you talk a little bit about, I guess, security access governance? I think there's a couple of questions around that. Yes, great questions there. I think this is an area where there's, I think there's multiple patterns to choose from, and there's more experimentation to do to see which pattern is the best. And this is an area where MotherDuck can manage it on your behalf, if you'd like.
41:42So the traditional way of managing access control on a lake house is to manage it on the object store side, where typically you're gonna have your data in folders by table, and you can grant access to those tables or partitions of those tables by granting a folder access in your cloud provider. And that does work in DuckLake. There's an option you can set.
42:05By default, we store things in high partition format, so you can kind of find your files easily. If you don't like that, you can disable that, and all your files will be kind of in the same place, and the catalog will be the only one that knows kind of where the folders should be. The other option that is unique to DuckLake is by using this encryption feature.
42:27So other lake house formats use a combination of Parquet and plain text formats or binary formats like Avro in the Iceberg case that don't support encryption. Parquet does. So what DuckLake can do, there's a single one Boolean you set. I would like encryption, please, and we will give it to you. And that means that every Parquet file will be encrypted with a different encryption key, and that key will be stored
42:52in your metadata catalog database. And so if you protect your metadata catalog, you don't have to secure the object storage at that granular level. You can have granular permissions on your catalog, like, say, row-level security, table-by-table, and that will grant access to those encryption keys. Now, that's a little bit harder because those are long-lived keys. It's built into the file on object storage,
43:16so there isn't really a way to revoke that access. So that's part of the value that we want to add from the mother perspective, is the ability to manage that credential lifecycle a bit more. Whereas with DuckLake, once you grant access, you have access to that file as long as you have access to that object store bucket. Cool. Let's see here. What is the best practice to use the number of workers
43:41based on the hardware resources? That's a great question. So this is going to vary based on which engine you're using. Most of the time with DuckLake, that'll be DuckDB, but Spark, Trino, Data Fusion, they'll have a different answer here. Typically, the rule of thumb is about one of two things. Depends if you're CPU-limited or you're network-limited. If you're CPU-limited, you should have the same number of workers,
44:06same number of threads, as you have CPU threads. So if you have hyper-threading, you know, it's the Intel term for that, you would want the number of hyper-threads that you have. So you might have 16 physical cores, 32 threads. I would set your DuckDB thread count to 32. Now, if you're network-bound, if you're spending more time talking back and forth to object storage, which is very common in a lake house,
44:28I would take that thread count and multiply it by two to five and you might actually see a significant improvement in total performance, even if your CPU is operating a little less efficiently, even if you're having to kind of thrash your CPU around. If you're spending all your time waiting on your network, it's good to make more network requests. So try bumping that up two to five times. Cool.
44:53Ooh, interesting one here. Can you make DuckLake or MotherDuck table, native tables available as Iceberg tables? That's a good question. It is very straightforward to import from Iceberg into DuckLake. It is a metadata-only copy because we use the same exact parquet format as Iceberg,
45:18which means if you've got 100 terabytes of data in Iceberg, you don't have to copy those 100 terabytes. You just copy the megabytes or gigabytes you have of metadata and DuckLake can take off and run from there. Exporting back to Iceberg is still something that's being worked on and how we want to do it because DuckLake files are slightly more flexible than Iceberg files.
45:40We allow multiple snapshots within one file because that's how inlining can really work well. If each insert to an inline table is a new snapshot and I have 1,000 snapshots in my inline table, and then I go to export it to parquet, if I have to create 1,000 separate files, I've lost all the benefits of that inlining. And so with DuckLake, we are able to have a separate column in there
46:04for your snapshot ID. So we can do time travel within a file, which is a unique capability to DuckLake. What that means is you have to make a decision when you export back to Iceberg. You might have to do something like grab the latest snapshot for each file because Iceberg doesn't have the understanding of that row level snapshot information. So we're working on that. I've also got like a side project I'm working on a little bit
46:29to see if I can do an Iceberg REST API wrapper around DuckLake. So if you're interested, shoot me a note, alex. motherduck.com. We'll play around with it a little bit. Yumi and Claude, see how far we can get there. I think we might be able to impersonate Iceberg a little bit, but use DuckLake as the engine. So that's something I'm playing around with.
46:54Oh, David's asking, just to be clear, you ingest the data in DuckLake and the engine generates the parquet file? That's exactly right. So in the DuckDB case, DuckDB is going to be writing the parquet file and in other engines, they can choose to wrap around DuckDB. DuckDB is very portable and you could use DuckDB's engine to write that file or you can write it yourself with whatever engine you'd like.
47:18You know, Spark could write those files, for example. So part of that optimistic concurrency idea is that we actually write the parquet file first all the way to object storage and it sits there, it's already there. But nobody knows about it because it's not in the catalog. So it's transactionally safe. And then we do a commit into the catalog after the fact to tell everyone that's querying
47:40all the read queries, hey, this file exists now. And so that means that it's very decoupled. However you get that parquet file in, you can then just register it with the catalog later and that's when it becomes visible. And so there's actually a way to write the parquet files with whatever engine you want, Daft, Pandas, Polars, and then just register it with the DuckDB engine to the catalog. So you can, however you can get your parquet files
48:05into object storage, we can read them. Cool, easy. Next one here is, is the reading utilizing the built-in DuckDB functionality to cache remote files? Is there a plan to support caching locally on disk? Yes, and yes. Excellent question. Thank you, Christophe. So yes, the DuckDB engine does cache parquet files
48:28in memory while you are querying them. And it's, again, not reading the whole file, it's just caching the pieces that you've gone and pulled. There are some extensions in the ecosystem that extend this to your SSD as well. One of them by our own Dr. Peter Boncz here at MotherDuck. He has built an extension that will do just that. And so there are a lot of different ways you can approach this.
48:53There's other community extensions as well. That's absolutely very possible. Let me see, I have so many good questions. I'm trying to find, what's this one? It's here. Last I checked, DuckLake doesn't natively have primary keys or foreign key for relations. I have a use case with legacy reporting software
49:17that uses ODBC exclusively, and want built-in getPrimaryKeys function. Is there a clean way to bridge the gap? I think that your approach is already quite innovative, so I like your approach there, Lodi, to work with the DuckDB driver. This is very difficult because lake houses, it's very hard to store primary keys and get all the other benefits
49:41you already have from a lake house. So I would recommend some sort of workaround. I think today you can also, with MotherDuck, you can move data back and forth between DuckLake and MotherDuck. So you could, if you need those primary keys, you can move data over to MotherDuck selectively. The other thing is that I believe it's on the roadmap for DuckLake
50:04to have kind of informational primary keys that are not enforced. And that might be just enough for what you're looking for. Oh, interesting. Cool. Ooh, a couple more questions on tuning. Are there recommended performance improvements to make the internal catalog queries quicker, such as adding indexes? That's an excellent question.
50:29So on the MotherDuck side, we tend to not need a lot of that just because our catalog is already very fast. However, if you're using Postgres, there's a lot of literature out there about how to tune Postgres. Do highly recommend doing some of those things. The main first thing is I would put some profiling on your database to see if it's necessary. Postgres by just out of the box is so much faster than talking to object storage three separate times.
50:54Probably don't need to tune Postgres that much to already be having a great time. Things I would look at, I would consider putting indexes on table ID because it's very often that you're querying just a small subset of your tables per query, and that would allow you to very quickly skip around to just the tables that you want. You could even consider doing Postgres partitions by table ID if this gets very, very large.
51:19And that will allow Postgres to treat each metadata table about a physical DuckLake table as a separate physical Postgres table. So that was a little bit of too many times using the word table, but what that means is that you get performance advantages from Postgres because it can keep things very separate by separating it out partition-wise.
51:43So yes, indexes are great. Yes, partitions are great. The good news is there's a lot of existing literature out there about how to do that for Postgres. This is not a DuckLake specific tuning exercise. This is just profile your Postgres and add a couple indexes. So my hope is that it's very low-hanging fruit if you do run into that as a barrier. Cool. A question here from Hoyt.
52:06Are there any best practices for tuning the flushing threshold for inline data? That's a good question. Currently today, when you do a read query on inlined data, DuckLake will read the whole inlined table and then do the filtering in memory. And it does that because catalogs like Postgres just can't filter as fast as DuckDB.
52:29And so it often makes sense to take that workload out of the catalog database and do more of that work in the analytical engine. So the rule of thumb there is just to kind of think about that mental model of how many rows do I want to pull out of my catalog in one shot? And so like maybe a million, Postgres isn't super happy about downloading a million rows at a time. It's just, it can do it. It's a sweet spot.
52:53So I tend to think of less than a million for that kind of inline flush amount. The other way to think about it is your parquet size. You don't want to be outputting every two rows because then you're going to have tiny parquet files anyway. So that's the other side of the coin is maybe wait until your parquet files are a reasonable size. You may still want to do compaction anyway.
53:17Inlining and compaction, you can do one or the other, but you may want to do a little bit of both. Inlining is going to mean you get to do compaction way less often, which might be enough. Cool. Awesome. I'll take a few more here and then we'll let you all go. Is the set sorted by feature available at table creation? Great question.
53:41Today it is a separate command that you run after the table is already created. So you'll create the table and then you'll do a set sorted by. So if you're using something like dbt, it does mean that you'll need a hook there to run that after the table is created. The good news is that it has a deduplication there. So if you run the exact same set sorted by that's already there, it's a no-op. So there's really no downside
54:04to kind of always running set sorted by in your dbt pipeline, for example. There is syntax support in DuckDB for putting that into the create table command a la Spark. So we have plans to implement that in DuckLake as well, where you can do a create table with a sorted statement inside of the create statement. So that's on the roadmap.
54:27Thank you for the question. Cool. Ooh, I don't know if much of a question here, but this is also dbt related. Nicholas has been trying dbt DuckDB towards a DuckLake as well as reading DuckLake through Metabase. Some dirty hacks to make it work with glitches. Have you tried it out? What's your experience if you have tried it out?
54:50If not, I know we have some other internal dbt DuckDB experts here at MotherDuck and we can get answers there. Sure. Yeah, we do definitely use dbt internally and extensively. So it's absolutely something that we've tested and used. I have as well. I'm not aware of any outstanding issues that are significant. Please let me know if that's not the case.
55:15File some bugs in that repo here at MotherDuck. We're co-maintainers of the dbt DuckDB repo. And so we do look at all those issues and triage them and help to get them fixed. Definitely let us know if you're seeing anything there. That's a key workload that we want to be super smooth on DuckLake. We want it to be just like any other database you're working with dbt on. Metabase likewise, we do also want that to work smoothly too.
55:39We probably have some extra things that might make that even a little bit smoother on the MotherDuck side. That's another option if you're really running Metabase at scale. We have a different connection approach in MotherDuck where you can actually pretend to be Postgres to your BI tool. We call it our Postgres endpoint. And sometimes that can have things be smoother. So please have a look there on the MotherDuck side. Cool.
56:03This is a long, beefy one, but a couple of people have asked about this. It covers your face also, I'm gonna remove this. Does MotherDuck offer self-hosted deployment models? My customer data cannot contractually leave my cloud account and cannot be provided to third-party vendors without major process. So can I set up DuckLake in S3, but can't spin up MotherDuck? I can spin up the DuckDB UI in a container
56:27and expose the port, but that doesn't seem super scalable. Sure. I think that is something where if you're limited to your own cloud account, today, open source DuckLake is gonna be your best bet. There is not a limitation on how much concurrency you do. You can spin up as many compute containers as you'd like there and use the same bucket, use the same metadata catalog and get a decent amount of compute there.
56:52So I do think open source is the approach for now. On the MotherDuck side, we are a database as a service and our architecture is fairly complex under the hood to make that very seamless for you, which means that it's not straightforward to go ahead and deploy in another cloud environment today. So today we are a database as a service as our only deployment model. That said, we do wanna get to that point
57:15where we can deploy into VPCs. We're a little ways out from that at the moment. Cool. One last one. I will say also that you said without major process, let us know how we can help with that as well. If you're interested in MotherDuck, we can jump through hoops or help make the case for you internally. I was a customer engineer for a couple of years here and we're happy to be the person
57:39that gets the questions asked too on your behalf. Yeah, I'll take this last one. In DuckLake, with respect to keeping storage and compute close to each other, what are the implications of bring your own bucket on R2 versus MotherDuck managed storage and what is the cost difference? That's a great question. We do recommend having the bucket be really close by.
58:03So with R2, that's not as much of a cost concern because they don't charge egress fees. They have a different billing model, which is really excellent. So with R2, you don't necessarily pay the same kind of cost penalty that you would with some of your other cross-cloud or cross-region operations that you would do. You will still pay a cross-performance, cross-region performance challenge.
58:24That will be most acute based on how many files you have. The smaller your files, the more often you pay that latency penalty. The bigger your files, the bigger chunks, the less often you'll pay that penalty. So you might have to really tune your workload more to get it to perform well on R2. So it's a little bit more difficult. Price-wise, R2 is very cost-effective. It does mean it's a little less performant than some of the other providers.
58:48You think of object storage as a commodity. Technically, it's not. R2 is a little slower, but it's a lot cheaper. We are very competitive storage-wise on both DuckLake and managed MotherDuck storage because that's not where we're trying to necessarily be prohibitive, right? We want the cost to be very, very competitive.
59:12So take a look at our pricing on the MotherDuck side. It's very similar to what you'd pay for just a traditional bucket. Most of the cost of a Data Lake of any kind comes on the compute. We tend to see it's 80, 90% of the cost is compute. And so really what you want to optimize for is your fastest possible query response time. And that oftentimes means storage that might be a little more expensive
59:37right next to your compute. So be sure to benchmark the whole system cost, not just your storage cost. Awesome. With that, we'll wrap it up. Thank you for all the great questions. If you have more questions, join us. We have our community Slack, Alex is out there. I think there's even a DuckLake channel. If not, you can post it in general and we'll answer your questions. Cheers, everyone. Have a great day.
60:01Cheers. Thanks for the questions.
