0:54Yo! What's up everybody? We're super pumped to have you here I am Jacob and we are here with Alex to talk about DuckDB 1. 5 and all the awesomeness that it is bringing to the next
1:18version of DuckDB and MotherDuck Alex why don't you go ahead Howdy! Welcome everybody! So really great to have you all here one of my favorite topics to talk about is DuckDB of course please ask your questions as we go it's a lot more fun if we get to cover exactly what you're interested in so definitely use the Q&A there we will answer it as we go and we will also carve out some time at the end so if we don't
1:42answer your question immediately don't worry we'll get to it towards the end but please keep them coming I did want to start off with about just five minutes or so as kind of an intro to MotherDuck so that way you guys can see a little bit about how we relate to DuckDB and just give you some background so here at MotherDuck we like to say we are infrastructure for answers and that's because a data warehouse at the end of the day is a means to an end
2:06the goal of a data warehouse is to understand your business and make better choices or to display analytics to your customers so they can make better choices really the key is the output and that's what we really focus on is being the most efficient to actually get the answers that you're looking for key among that as we believe that the big data era is over so big data
2:30as a concept has a lot of overhead and a lot of tax the goal of big data was to say we can handle any scale that you want if you want to query a petabyte you know every five minutes we'll do it for you well that was possible but it's not possible to do quickly and cheaply those are some of the key weaknesses of the traditional big data approach which all the incumbent data warehouses use so one query comes in it gets split across
2:54thousands of tiny individual machines they all work some of them fail some will get restarted they all communicate back and forth bring the data back to the original node and you get the answer back but when that happens there's a lot of overhead on a network cost and time and you end up with a lot of latency and overhead what we've really found is that latency is actually the main performance metric for almost
3:18all queries if you're going to be running queries you're you know run on a petabyte query it's not something that everyone loading the dashboard should hit if that's true I highly recommend you do some data pipelines that way you're not querying the petabyte every time right so if you look at the chart on the right this is data analyzed for our competitors and this is they publicly released some
3:42data sets of usage on their platforms and looks like most people aren't querying more than a terabyte in any individual query and that's really we decided to focus on that workload because we think that's 99% of workloads so if that's you that's what we're built for the real world of data and not the the fortune 10 scale problems something that helps with that is that hardware has gotten huge now so
4:06now you can rent a single server with over a terabyte of RAM and because of that you might not need to string together thousands of tiny machines maybe you could use one beefy one and that's a key thing that allows us to do this efficiently so to summarize what are we what are we all about at MotherDuck we want to make your big data feel small so we want to get rid of the overhead of these legacy systems we want to help you get your answers
4:30at faster again that's the what we need at the end of the day and then we want this to be best in class and a key way of doing that is DuckDB brings us a little bit to what we're here for today so what is DuckDB if you're here you probably have a pretty good idea as a refresher it's an in process analytical engine it means it sits right inside of your app and can
4:54analyze data really large scale analysis is what it specializes in when you want to look at billions of rows and read them all it is highly analytic or sorry highly efficient for that purpose it does a great job using all your cores and being very parallel but it can also run anywhere can run even all the way in your web browser the ergonomics are great it's the friendliest database
5:18out there especially the sequel dialect is amazing and you can pull files of all kinds from all over the place how does MotherDuck tie into DuckDB well here at MotherDuck we run DuckDB as our engine under the hood MotherDuck is actually an extension to DuckDB we use DuckDB extension mechanism that allows us to keep pace with DuckDB as it innovates really really quickly
5:42changes rapidly and they improve a lot and we get to join them as that happens a key part of that relationship is that the DuckDB labs team is a separate company based in amsterdam and MotherDuck a separate company based in the US and we get to work together on the parts that we like the best so DuckDB labs builds the best hardcore database engine and MotherDuck builds the best scalable cloud platform on
6:06top of it and there's a great symbiotic relationship there where we can battle test things at a scale and then DuckDB labs can build custom hooks if there are cases where we want to do things a little differently in the cloud MotherDuck is serveO'Reillyss so we are spinning up little tiny DuckDB instances or very giant DuckDB instances whenever you need it really fast 100 milliseconds
6:30and you can have as many of them as you want and so that's a key thing is it's not one big node for your whole company every user can have their own big DuckDB instances and we also have this unique capability called dual execution that means that you don't give up the magic of local DuckDB you can have the magical local DuckDB experience either during development and then push to prod seamlessly or even
6:54within the same query part of it can run locally part of it can run in the cloud this is amazing if you have a ton of data in the cloud you want to aggregate it down partially then you can bring it locally and slice and dice it so really a lot of flexibility that's very unique to MotherDuck because DuckDB is so unique all right alex we have our first question from the audience all right thank you
7:18I'm going to pull it up here here we go this is a pre warm up for 1. 5 here we go all right power bi and how does this combination perform love it the question peter yes we have we we do work with power bi we've we've had an integration there for a long time from the deck db side we've recently made some innovations there connecting even through our
7:42newly launched postgres endpoint so what is that that means that MotherDuck can pretend to be postgres and you can connect to us with a postgres driver as if you're just connecting to postgres but your analytics are about a thousand times faster and if you think that's an exaggeration have a look at the clickbench benchmark not our benchmark that's about the difference that you're looking at so we think it performs really well
8:06power bi is a great fit with MotherDuck amazing thanks alex keep rolling for us sweet well thank you for the questions keep them coming if you have MotherDuck questions you can send them in we may save that to the end but so we can get into the DuckDB 1. 5 step so get hype for for DuckDB 1. 5 you can see my my title here is sequel hype man so it's pretty consistent for me so let's go feature by feature what's new
8:30and what are the benefits you get let's go yes the first one is that if you've got a really simple query like i want to get the latest timestamp of this table to know if my data is up to date or not you don't have to read every row anymore we're just going to check the statistics and we track statistics about every hundred thousand rows so that's a huge reduction in the amount of data you have to check for these
8:54simple queries so that's a 10x or more boost for those simple queries and again those come up all the time like check the latest timestamp check the most recent activity those are all going to be lightning fast now the next thing is that was performance on a simple query this is actually boosting performance on very complex queries so joins with complex expressions
9:18can now take advantage of the typical fast hash join method that DuckDB uses so that's pretty in the technical weeds but that basically means is DuckDB is able to better handle some of these edge cases to use the fast path whereas before it kind of fell back to the always works but is slow way which is a loop join and now it does it the fast way which is a hash join and
9:42anytime this happens it's a 10x boost right out of the gate it's a huge difference it's just a better algorithm and there was always a way to work around this if you rewrote your join in an exact way or you maybe pre-processed your data before you did your join now it just works out of the box so not only is this a speed thing this is also an ease of use thing where your life got easier for free with DuckDB 1. 5
10:06keep on moving on so this one is a pretty powerful feature DuckDB can now write data to Microsoft Azure blob storage now this is huge this was the last missing piece to be able to read and write to all the major clouds as well as cloud flare r2 object stores that means you can really pick pick your cloud object
10:30store that you want to write to with DuckDB and that's really helpful it also comes in handy for DuckLake which we'll see a little bit later on these rights also apply to DuckLake so now you can have DuckLakes that sit on Azure with DuckDB amazing Alex we got a question about the Microsoft stack that I'm going to pull up here here we go this is in relation to us talking about pg
10:54endpoint a little bit what about facade for ms sequel which I think means t sequel as it relates to as it relates to sequel server Alex you want me to take this one or do you want to take it you go ahead I have a couple thoughts but I want your thoughts first okay so you can actually already connect MotherDuck and DuckDB to
11:18sequel server as a link server using odbc so this does work it is using odbc which is not the fastest connection I don't know if there's other pathways yet available there what I have seen is because sequel server for example is so fast at reading rose is if you can get incremental row updates you know via cdc
11:42or whatever you're using into MotherDuck it's really really nice combination for those analytical loads when the column or you know formats in sequel server aren't good enough but yeah that's what I would that's my answer I don't know if you want to add more Alex sweet I would say also we can go the other direction as well so also in 1. 5 there's an odbc scanner so DuckDB
12:06can actually read through odbc drivers to other database so you can query your ibm db2 databases I believe it's not now soon but similarly for sequel server you can from DuckDB reach out and talk to sequel server so goes in that direction as well all right I got a couple more marks off questions I'm going to pull up here's one what is the best way to use DuckDB from
12:30. net do you endorse any entity framework libraries I'm not sure I have any endorsements of entity framework libraries but there is a native DuckDB . net driver and so that should work very smoothly it's in production people are using it and it's been around for a couple years now it's got a lot of maturity to it so we have a native . net connector you should definitely check out yeah what
12:54that lets you do is you just connect it to your kind of . net program and then you can write sequel strings and pass them across there may be some more things you can do there too I'm not an expert in that specific language but it's very very powerful uses the hybrid connection methodology Alex was talking about previously let's see is there let's be one more all right
13:18Elvis hello Jake can you talk about more about connecting it to Excel I believe the latest Excel has a Postgres connector in the in the available kind of add data from the add data drop down you just use that to add the connection string it should work out of the box reads all right I'm going to build that one Alex why don't you
13:42keep rolling here there's a bunch of questions coming in but we will get to them there's a few Postgres ones that we will kind of bundle together here in a little bit sweet well thank you keep them coming and if your questions in there we will get to it so this feature is an incredibly powerful one and this is a cool feature coming out of the relationship between DuckDB Labs and MotherDuck and this is background checkpointing
14:06what this means is that you can be continuously ingesting data into DuckDB because DuckDB might be a single process database but it's definitely a multi threaded database as you can be ingesting things at the same time as you're querying and now at the same time as you're checkpointing and that checkpoint is where you take kind of your work in progress data that's been safely persist as a disk
14:30and you compress it down into the excellent DuckDB storage format and that compression and archival kind of process is a checkpoint and now that can happen completely transparently and in the background so this is a huge change this is something where SQLite considered doing this and decided not to it involves having multiple write ahead log files it's a really
14:54powerful fundamental thing and I think it's a great example of how DuckDB is both a mature technology and an incredibly innovative technology you don't see databases that have been around and in production for six seven years making changes like this except for a very select few that are really nimble and that's DuckDB is in that upper echelon MotherDuck is in the process of implementing this we've been testing it internally
15:18in our own internal workloads and we are ironing out every single kink because again this is a huge and innovative change and at MotherDuck part of the benefit that we offer as a part of that relationship is the battle testing before we even deploy it to customers we're working on this it's coming in a matter of weeks and we're very excited about it and I also mentioned there in the text that shares will be more up to date that's a
15:42MotherDuck concept where you can share your data set with another user and they can have a read only copy of the data that you're writing to and now even if you're ingesting as fast as you possibly can that will stay nice and up to date because we can always be checkpointing whenever we need to be all right the next really big feature in 1. 5 is the variant type
16:06and the variant type recently came to Parquet and DuckDB can read and write the Parquet variant it has also come to the DuckDB native storage format there's now a DuckDB native type that is a variant type so what is a variant type variant type says if you've got a JSON document even if each row is not identical maybe you've got some events from one
16:30service some events from a different micro service and the keys aren't exactly the same traditionally in DuckDB that meant you'd store it as a JSON column and we would store it as a string for maximum flexibility but if you wanted to go query it your performance meant you had to read through the string convert it back into kind of more of a binary JSON representation and then work with it what variant does is it will
16:54split apart that JSON object into separate columns automatically where you don't have to make sure that your JSON is exactly the same all the time it can handle that variation and it will automatically split out into separate keys and what that means is that if you are trying to filter on a specific key like I want to filter to event type logs
17:18or event type metrics or event type customer A that filtering operation is going to be dramatically faster because now it's going to work on a column that just has that data in it and you don't have to parse the whole JSON object so very specific targeted queries on JSON data are going to be dramatically faster and better with 1. 5 this is another giant feature
17:42this is something where this is a very different way of storing and handling semi-structured data and we are also in the process of battle testing this here at MotherDuck and we'll be rolling it out in the short term as well again this is another area where we're really able to battle test this at scale with our own internal workloads we run our internal data warehouse on MotherDuck of course and so we're able to battle test it on a data warehousing workload at
18:06significant scale before rolling it out to the community again you can use it in opensource. db today already of course so with that any questions that have come in Jacob? um there's a question about there's a hash collision question which may be too in the weeds but I'll pull it up anyway I don't know how deeply you read the code here Alex
18:30I know a bit of it yes so how do we have hash collisions when doing a fast hash join so DuckDB has a couple really cool blogs that talk about exactly how they implemented their hash algorithm and their whole hash join approach it is really really cool so I highly encourage you to go to the DuckDB. org blog and check it out it is a parallel approach where it's splitting things into
18:54threads and it is doing a join thread by thread and then even doing the recombination across threads in parallel as well so there isn't even a sequential step at the end it's actually two parallel steps it's really wild I believe they use a linear hash table where they do you know linear search on collisions in the past that was you know a difficult decision but now
19:18with the way processors are architected being you know using SIMD where they can do multiple things at once believe it or not doing things linearly is actually easier for the CPU to do it's more predictable so DuckDB has an industry leading hash join implementation and it's built for how modern processors work so definitely check out those blogs for the full details but really fun to geek out on thanks for the question
19:42you're on the boundary of my knowledge there so I'll do it like that thanks Alex that's a really great answer boundary of my knowledge as well why don't we keep rolling here excellent well lots more so not only is DuckDB working on new features as well as speed improvements. They're also working on stability, robustness, and really,
20:05you know, industrial strength types of features. And one of those areas that they've been making huge strides on over the last year or two is memory management. And so this allows you to handle workloads that are much, much larger than RAM. Not just like barely over the RAM of your computer, but multiple times larger than the RAM you have available seamlessly. And some of the key
20:28areas that have been improved in 1. 5 are around query results. So if you have a query where you're returning a huge result set, now that is managed much more smoothly. As well as when you're working with a lot of parquet data and you have a lot of metadata that's being cached, now that is managed nicely as well. So even if you have a lot of that lying around, it gets seamlessly handled based on the amount of RAM that you have.
20:53So lots of improvement really in all directions. This one is in the efficiency direction. This is an approach called common subplan elimination. So what does that mean? It means that the gnarliest queries that you've got get faster automatically. So if you have a thousand line SQL query and you've got common table expressions, CTEs, they're also called with
21:18statements, where you are reusing that with statement a couple of different places, now DuckDB is able to intelligently understand when that CTE is being reused multiple places and only compute it once. And it will store it in memory and also buffer it out to disk if you're
21:36low on memory. But it'll manage that so that way it only runs once. And that's really designed for, and that's really designed for, you know, we saw some improvements for the simplest queries possible. Give me a min of the whole table. And now this is for the most complex, those are also getting faster. So that's a really powerful technique there. Again, not something you have to change a single thing to take advantage of.
22:02This is just DuckDB doing less work so we can go faster. This is one that you'll see if you use the DuckDB command line tool, it is way more colorful now. And not only is it way more colorful, it's way friendlier and more usable. It will do things like show you the current database that you're using,
22:22so that way you can make sure you're writing queries that are either fully qualified or not, so you know what database you're pointing at. You can also go back and use the previous set of results in your subsequent query with an underscore, really some very cool quality of life features on the command line. And not only that, you can now install the command line
22:44through Python. So if you want to do pip install duckdb-cli, you will get a command line tool that is using a Python based wrapper for that. So it's a very, another really easy way to go ahead and install that if you're in a Python centric environment. Or you can do UV install if you're a UV fan like me. I can never go back to pip. I wish I put UV install on the slide.
23:07But lots of really nice quality of life features for just day-to-day work in the command line. This one is a really cool investment in kind of the future of geometry data. So the spatial extension in DuckDB has been around for several years, and it is a pretty incredible piece of
23:27technology. It combines several industry standard libraries, compiles them together to have them work harmoniously, can read and write tons and tons of geospatial formats, all kinds of coordinate reference systems. It's really a Swiss army knife. And when you want to use the spatial extension, it's great to have that. Now though, we've taken some of the capabilities from that
23:48extension and brought it back into the core. That means that all kinds of extensions can use that data type, not just the spatial extension. It also means that our storage format for spatial data can be a lot more efficient. We're now using the industry standard well-known binary storage format, which is a great name. And we're able to compress it as well. Plus, now that it's embedded
24:12more deeply in the engine, we have the ability to collect more statistics about geometry data types. For example, if you want to filter to cafes that are near my location, you want to filter on latitude and longitude on your points or your lines. And now we're able to skip statistics to
24:33be able to do that filtering very, very efficiently because it's now a native data type. So if you're a geospatial fan, there's a lot of goodies both now and in the long term with DuckDB by really embracing spatial data as a key first-class citizen in the engine itself. Alex, we've got a couple good questions here coming in. I'm going to pull up a couple. Here's
24:56the first one. Are there any breaking changes between 1. 4 and 1. 5? I want to say there are a few. Let me go ahead and see. Where I would point you to for that is the DuckDB. org 1. 5 release post. And I'm headed there now. I believe there were just a few small things.
25:20So there's a little bit of a change in the date time function. So if you're doing date trunk on a date, you get a timestamp back instead of a date. So a little bit of data type change there. And I think, yeah, there's a couple changes in spatial. So spatial, a big one. They flipped the x-axis order of latitude and longitude
25:42to be more aligned with the industry standard. This was so annoying when it would do that. For a long time, it was different than everybody else. And we were like, oh, no. Yeah, correct. It was a big foot gun. And now it is totally removed. Your feet are safe. And it behaves just like you would expect. Oh, God. OK. I have another good question here. Let me pull this up.
26:05This is actually about the previous slide, I think, if you want to go back to it. Do common subplanes just get materialized, or can they be inlined and further optimized and further optimized in their various downstream uses? It is a very advanced implementation of the common subplane elimination. It's able to do kind of fuzzy matches to really do some interesting overlap.
26:30So it doesn't have to match exactly to be able to reuse it. So it is something where it's a very intelligent setup. I am not sure if things always get materialized or not. But what I can say is that even if it were to get materialized, it will be safely spilled to disk. So you do have a lot of protection there where it is not a real risk. It's more just a performance tradeoff.
26:54You do have the option to hard code that if you wanted to. You can say as materialized or as not materialized. So there's always an escape hatch if you need it. But in general, this is really just making the right decision for you automatically. It's working pretty well. Amazing. And then one more kind of on the geometry type here. Come on, here we go. Will geometry type help with the Arrow connections?
27:18That's a great question. I do not have that specific answer. What I can say is there's been some really great collaboration between DuckDB Labs and the Arrow community for a long, long time. But also, especially in the geometry space with GeoParquet, for example, DuckDB was involved with the creation of it and collaborating there and was one of the very first systems to be able to support it.
27:41So it is absolutely something that we continue to actively look at. We're Arrow fans around here, for sure. Absolutely. Love Arrow. Love ADBC as well. Much better than ODBC. Oh, yes. All right, let's keep rolling, Alex. Yes, thank you for those questions. Those are great. Please keep them coming.
28:05All right, well, what we had to cover next are around the DuckLake side of things. So if you aren't familiar with DuckLake, DuckLake is an open table lakehouse specification. So it's comparable to something like Iceberg or Delta Lake. What's even nicer, though, is DuckLake is actually kind of a combination of a catalog and the table spec itself.
28:27So when you use DuckLake, you get the combination of something like Apache Iceberg and Apache Polaris or Delta Lake and Unity Catalog. So DuckLake is fundamentally simpler than those. That's kind of its key, key architecture point is that it is the simplest lakehouse, partially because those things are bundled together and partially because
28:49DuckLake manages your metadata in a SQL database. We all love SQL databases. Speaking for myself, at least, most of us love SQL databases. And what that means is that you can simplify your stack a lot with DuckLake. You don't have to have a separate catalog service. You can run, for example, a Postgres database as your catalog and your metadata store
29:14and then just write your data to like object storage like another lakehouse would. So you can put it in an S3 bucket or now an Azure bucket or really any other bucket you'd like. Yeah, Alex, and I would be remiss if you didn't plug your latest project on DuckLake as well. Yes, so I am a tremendous DuckLake fan.
29:33I am a co-author on DuckLake, The Definitive Guide, which is an O'Reilly book all about DuckLake. We actually have chapter one as an early release already out. So maybe Jacob, if you could throw that link in the chat. I will. I will do that right now. Yep. If you guys sign up for that, you will not only get the PDF of chapter one in your inbox, you'll also get all the subsequent chapters in your inbox as well.
29:57I just was working on like a deep dive into the architecture chapter. So lots of really good in-depth analysis. And then the next one I'm working on is performance tuning. So DuckLake works great out of the box. But if you want to turn the knob up to 11, as they say, we can help. So thank you, Jacob, for the opportunity to do that plug. It's been a ton of fun.
30:22Matt Martin is my co-author there. He's got a ton of data engineering expertise. And I'm really excited to be teaming up with him on that. Amazing. Sweet. So that's the crash course on DuckLake. Really, we think DuckLake being the simplest lake house is huge. You know, nobody really thinks of lake houses as simple today, except for DuckLake. So I encourage you to try it out.
30:43You can run on your laptop in about one command. Yeah, one line. And two SQL statements, which is install DuckLake and attach a DuckLake. And we've got all those steps in that chapter one. So hit up that link. So what's new in DuckLake? Well, DuckLake has been, we're marching towards 1. 0, coming in the next month or so.
31:08And so there's been a lot of work on features to really make it really very, very feature rich, even compared to some of these lake houses that have been around for a decade, as well as really, really robust. So there's been a lot of bug squashing that's been going on in DuckLake. One of the new features, though, are macros. So now you can actually have some of your business logic encoded as macros,
31:31either scalar macros or table macros, and store them inside your DuckLake catalog. So macros are super handy if you've got some custom business logic. So like, I used to work in supply chain for a long time. There's a lot of rules around if something was considered to be a perfect order, you know, perfect delivery. It has to be in the right place, has to be at the right time, has to be the right amount to the right customer.
31:53And so there's a custom case when statement there that gets to be really gnarly. And you can build that into a macro to bring it back to be a one liner. And you could put it everywhere. The other real nice thing about macros is they are great for working with AI, because it means the AI only has to get the logic right once when it writes the macro. And then after that, you'll already be able to reuse that logic.
32:18And your AI will be able to get it right every time. So putting your business logic in the database is a great way for your text to SQL to work a lot smoother. Now you can do it with DuckLake. The next thing is. . . We have a question here. I think it's a great question. I want to just throw up into the chat here. Bam, here we go.
32:40How should we be thinking about deciding whether to use DuckLake versus MotherDuck's native catalog? Excellent. I've got a blog on that coming up really soon. So I've been thinking very deeply on that topic. The first thing to say is that it is not a one-way door at all. On MotherDuck, you can have one table that's a DuckLake table and one table that's a MotherDuck table. You can insert back and forth.
33:04You can join them in the same query. It is really flexible where you can choose the right tool for the right job, table by table. Really, the key things to think about here are around. . . Firstly, it's really about scale. So if you've got 100 terabytes of raw data in a table, that's a great fit for DuckLake because DuckLake has the ability to do partitions. So you could partition things by year, month, day,
33:29or you can partition it by customer ID. And that allows you to scale to significantly larger datasets. The other thing is if your data really naturally fits into partitions. So let's say you've got a thousand customers. And when you run your queries, you only ever query one customer at a time for 99% of your queries. Well, if you partition by customer, your performance is just going to improve across the board
33:53because you can always just get exactly what you want. So that's a partitionable workload, is the way I like to think about it. That's a great fit for DuckLake. The last piece where DuckLake really shines in particular is if your ingestion speed needs to be really high. You are constantly loading in data, like doing observability or logging. DuckLake can do ingestion very, very quickly
34:16because it can ingest it in somewhat of a raw format and compact it later in the background. And that separation of ingestion and compaction allows you to ingest really, really quickly. So it's good for those high volume ingestion workloads. MotherDuck's catalog is really excellent for a lot of the core traditional workloads. So if you are doing a dbt pipeline, for example, a lot of what you're going to do is you're going to write out a dataset.
34:40You're going to immediately read that dataset again to then further process it in the next step of your pipeline. MotherDuck Native Storage has an incredible set of caching infrastructure that allows that to be really, really quick because we don't just cache when we read the data. We cache when we write it because we assume you're about to look at it again. You just wrote it. And for pipelines, that is almost universally true.
35:04And so for that kind of silver layer where you're doing that multi-step processing of your data, MotherDuck Native Storage is great. The other layer where MotherDuck Storage is really, really great is in the gold tier where you want to take advantage of that caching as well for really, really low latency queries, either serving your application or serving your BI tool. And that's another spot where MotherDuck Native Storage really, really shines.
35:28But again, there's a large Venn diagram overlap here, and it's really easy to go back and forth. It's not a hard choice. But thank you for the question. That's right. Good question. All right. Keep rolling, Alex. Sorry. Let's see if I can throw anything else in here. All good. All good. These are great questions. The next thing is a key addition to a feature that's very disruptive in DuckLake.
35:52And that feature is called inlining. So what inlining does is it says if you have a small insert, typically in a data lake, if you have a small insert, that's kind of like kryptonite. Because if you have one small insert, it means you're having them all the time and you're going to end up with tons and tons of tiny files all over. And sometimes that tiny file buildup can be so fast, you can't even compact the data fast enough
36:16to keep up with all these small files and your whole lake slows way down. So what DuckLake does instead uniquely is that it can, instead of creating a parquet file for every insert, for small inserts, you can choose to insert directly into your catalog because your catalog is a database. So for example, you can insert it into your MotherDuck catalog or your Postgres catalog.
36:41And that is far more efficient for small inserts than writing out totally separate 10 line files to parquet. And so you can do that at a much higher transactions per second. And your reads are faster as well because you're not trying to read thousands and thousands of tiny, tiny files. When you're finally happy with the amount of data you've kind of built up in that buffer,
37:04you can flush that buffer out to parquet. And so you can say, oh, I've got a million rows now in my inlined table. Let me move that from Postgres to parquet. Parquet is a lot more efficient with a million rows than Postgres. What's nice though, is this buffer is not like a Kafka queue where it's a kind of an unobservable black box that you can't see into for your read queries. Each row that's put in your inline table
37:28is immediately transactionally safe and it gets returned with any query where it's relevant. So it's really a no downside situation where you can get really great small inserts. The key innovation in DuckDB 0. 4 is that way more workloads can do this now. So if you have updates or deletions, now those can also be inlined
37:51because there's a separate inlining table for deletions. When you do an update in DuckLake, it does a delete and then a reinsert. So it's kind of using that same infrastructure here. So if you're doing like an upsert workload where new data is coming in, just common in streaming, but you wanna de-duplicate it. Now you can do that as well and you can do it for smaller batches than ever before in a lake house.
38:15This one I'm very biased on. This was my first contribution to DuckLake. What this allows you to do is take advantage of a clustering approach. What that means is that when you compact your data or you flush that inline buffer, you can sort that data and you can sort it in the same way that you would be doing your querying. So if you always query by customer
38:38or if you query typically by recent dates, now you can cluster your data so that your reads are faster, but you can do that optimization totally in the background because it's during compaction time, not when you first load your data. And you can kind of retune and re-optimize your dataset when you do that kind of periodic compaction. So very excited about that. This is a great performance boost for the, again, low latency reads.
39:06DuckLake also gets variants. And this is using the Parquet variant setup because DuckLake uses Parquet files under the hood. And this is great for semi-structured use cases. DuckLake in general is great for that. If you have millions and millions and billions and billions of rows of log data, DuckLake is an excellent fit for that particular type of workload and variants are a key unlock there
39:31because how many of your logs have some JSON in them? Too many. Too many? Yeah, that's the secret. Talking like a SQL enjoyer. No, no, we like JSON around here. We like JSON around here. You're not wrong. Oh, yeah. This is a key unlock. When you use a variant and you are doing a targeted query and just querying and filtering on a specific column,
39:54it can be 10 to 100x faster for that very targeted workload. It's a big deal. all right, maybe just a couple more things, and then it'll be Q&A time. So once again, with DuckLake, very basic queries also get very fast. So DuckLake has a separate catalog where it tracks things like your total row count. And now we can just look in your catalog and not even look at your parquet files
40:19to figure out how many rows you have, which is really nice optimization. Again, there's a lot, oftentimes a lot of queries just check, has anything changed? Has anything changed? Those are now going to be lightning speed. And that is what I had to cover on all the fun stuff in DuckDB 1. 5 and DuckLake 0. 4. Both of them are available on MotherDuck already,
40:42and definitely head to app. motherduck.com, give it a shot. And it's of course available in the open source community as well. Amazing. Thank you, Alex. We got some really, really good questions here in the audience. So I'm going to start kind of with a few that are more recent, and then we'll go back because I did get a couple of postgres ones that I want to circle on. Okay. So first one here is from David,
41:06and he's asking, does DuckDB handle OCR at all? Optical character recognition. And Alex, I will start, and then you can go wherever. Okay. So there was a helpful allusion to the way that DuckDB works with other Python libraries via Arrow, brought up by Jim earlier.
41:28And what that means is DuckDB itself does not do OCR. I don't think there's an OCR extension, for example. However, it's really, really fast to basically connect any Python library into it that can, and it can interact at the native kind of Python in memory level. Alex, keep me honest here in terms of how to describe that.
41:49But we do see a lot of people using other libraries to use Python or maybe like run, let's say DuckDB on top of something like Modal, and where they can get all those sweet GPUs and then write into MotherDuck as part of their jobs. So you can kind of mix and match. That's the advantage of the fact that there was a question earlier where someone was like, hey, how do I use DuckDB with . NET?
42:13It's like, actually there's a DuckDB driver for . NET. So like you just put the database in whatever runtime you have, and MotherDuck seamlessly connects those two things. So like, the answer is like for a very specific set of relational database things, DuckDB will handle all of that natively. Obviously with SQL, for the things that are, you know, less structured, you can use the native libraries
42:38to kind of interact with all those pieces. Alex, anything you want to add? You bet. That's exactly right. So DuckDB seamlessly interoperates with Python in particular really well, and Apache Arrow as well. And it can actually do it in large chunks. So most databases really struggle with these kinds of separate user-defined functions because they go one row at a time, and your performance gets destroyed. Yeah. DuckDB does it a vector at a time,
43:03which is 2,048 rows at a time. So you really can mix and match. You really can have it all. Yeah, absolutely. Related question from Peter. I'm going to throw up here. Can DuckDB use and benefit from using GPU in conjunction with the CPU? You know, there's an experimental extension to that as well. So there's some work being done in that area.
43:29It's a really interesting area where there are some things that GPUs are really excellent at that we're trying to tap into. This is also one of those, you know, you can combine for best of both scenarios where if you have a GPU-centric workload, you can seamlessly use Arrow to interoperate with DuckDB and the work you're doing on the GPU in a zero-copy type of way. The zero-copy piece, I think, is really important to emphasize.
43:53Like, you're not having to serialize and deserialize. It's all in memory. It just works. It's very, very, very fast. So even for things where it's like, oh, maybe natively it's not supported, it's so fast that you don't even notice for the most part, which is crazy. Yes. All right, one more question here from Jim related to Arrow here. I think I know the answer to this. DuckDB does not expose CRS.
44:17Will geometry type and WKB help? My guess is we don't know at this moment. It's probably a really good question for DuckDB itself. Alex, I don't know if you have more to add. I think this is a little bit into the details outside of my understanding of A, Arrow, and B, Spatial. Just to be clear here. So I'm also at the edge of my understanding,
44:42but in the 1. 5 announcement post, there are now, I believe, some things that are gonna be unlocked in the future with that movement of the geometry data type to core. And one of the things it calls out specifically is CRS awareness capabilities. Okay, great. So it is absolutely a key roadmap area around that. And that's part of making geometry a first-class citizen,
45:06which 1. 5 makes it. So stay tuned there. 1. 5 has been out for a couple of weeks now. There could be progress there. So please have a look in the repo and reach out if you have questions. Amazing. Okay, got another question here. This is, I'm gonna just, well, this is kind of long, but I'll summarize it. Can I connect to Power BI
45:30without the on-premise data gateway is the question. And Alex, you wanna answer that? Thanks to the Postgres connector, I believe that is launching very, very soon. It's live now. Yeah, you can use it now. It is in preview. Power BI specifically is in preview. Perfect. Yeah. We have more features coming out for it this week for Power BI.
45:55So it should be good to try it now. And if you have any issues, find us in our community Slack, slack. motherduck.com and go from there. Yes, so what we do is we pretend to be Postgres to Power BI, but you get all the horsepower and serveO'Reillyss behavior of MotherDuck behind the scenes. So it's a really great fit. And Postgres support in Power BI is top-notch,
46:19which means that now MotherDuck support in Power BI is also top-notch. Exactly right, exactly right. And like I said, that is brand new for us. We were probably, it came out in the last few weeks and we are actively supporting and improving it. Okay, we've got a few Postgres questions. I'm gonna throw the first one up here. This is from Peter. Here we go. What about Postgres 18 as a data warehouse
46:44with PgDuckDB? Also thoughts of extending the attached to ANSI SQL databases? I can take the second question if you wanna take the first one. Sure, so we are big Postgres fans at MotherDuck and DuckDB. Our SQL syntax is very heavily aligned. That said, there's a fundamental, two big fundamental design differences between Postgres and DuckDB.
47:07Postgres is row-based in storage and in execution, which means that everything has to go one row at a time in Postgres and it stores data, all the data for one row together on storage. Where DuckDB in contrast is columnar, which means that it will store similar data in the same column, like dates all together, integers all together.
47:30And that means that the compression can be dramatic, often 10 times more compressed. And you can also use columnar structure during execution. So DuckDB processes things 2,048 rows at a time instead of one. And that can be a huge difference in execution speed as well, especially given modern CPUs that can do more than one thing at a time.
47:53So that said, Postgres is a great database and you can stretch it towards being a data warehouse, but there's some fundamental design decisions where it's gonna be, no matter how hard you tune Postgres, it's gonna be somewhere around two orders of magnitude slower than a well-tuned analytical database. And that's just the kind of nature of the beast. PgDuckDB is an extension to Postgres
48:16where you can run DuckDB inside of your Postgres database. And it brings some of the benefits of DuckDB's columnar execution right there inside of Postgres. It's really cool technology. Some of the challenges though, is that that's still kind of using the kind of large beefy server model of Postgres where Postgres is typically one big long running database, whereas something like MotherDuck is serveO'Reillyss,
48:39it's very scalable, both vertically and horizontally, and it's bursty for kind of those business intelligence workloads. So often what we see is people using Postgres plus PgDuckDB and connecting it to MotherDuck at the same time. And using PgDuckDB as a bridge over to MotherDuck. So that way you can use the right tool for the job and get really fast analytical performance and really fast transactional performance with Postgres.
49:03Yep, absolutely. As it relates to. . . Actually, sorry, Alex, do you have anything else to add there? No, I was gonna look for your help on the part two of the question. On part two, extending to ANSI SQL databases, there is an extension for DuckDB that exists today called NanoDBC that lets you attach anything that has an ODBC connector into DuckDB
49:27and to query it with DuckDB SQL. I'm not quite sure how robustly tested it is across all databases. I do know it happens to work quite well with SQL Server, DB2, Oracle, et cetera. I have no experience with those, but it's a very good question. There's another Postgres question. Oh, go ahead.
49:51I was gonna jump in as well. There's also a core extension now with 1. 5 that is the ODBC scanner. So now there's both a native capability as well as the wrapper of NanoDBC. I missed that part. Use both, try them out. All right, we've got one more about Postgres here. What parts of the Postgres protocol are supportive of querying MotherDuck as a Postgres database?
50:17I'll have to check exactly where we are at this exact moment. If you have an updated answer, go for it, Jacob. I don't have an updated answer. We've been focusing on BI tools first, but the goal is to have complete compatibility at some point in the future. Yes, so we're working actively towards that and we are implementing quite a variety of things there. So there's been a lot of work in this area to be highly compatible.
50:41BI tools, believe it or not, write some pretty wacky SQL queries. You guys may know that even better than me. So we are already supporting some pretty advanced Postgres syntax already so that BI tools work smoothly and seamlessly out of the gate. And there's more to go. Absolutely. Okay, do I have anything else in here? Let me just check. Let's go down to the bottom. Okay, this is the question.
51:05I don't know what this means. Is there an OIDC integration in DuckLake or DuckDB? I'm going to, what does that mean? Is that for auth? Okay. OpenID Connect. This is an SSO question, I believe. So with MotherDuck, we do have support for SSO
51:29and we support a variety of different providers for that. And so have a look at our SSO feature in the MotherDuck docs that might be exactly what you're looking for. SSO is one of those things where it's best provided as an enterprise type of service and that is something where MotherDuck could probably be helpful there. I'm not familiar with anything kind of in that area in the open source side of DuckDB. Again, if folks in the community know,
51:53feel free to jump in and add some answers in the chat. But I do know that we've added SSO, single sign-on capability in MotherDuck. All right, one more here. I think there's multiple questions here. We can take the first one. The second one I think is interesting, but off topic. What about encryption? Can you talk about how encryption is integrated into DuckLake, Alex?
52:17Yes, so there's encryption both in the DuckDB side and in the DuckLake side. And they use two different mechanisms. So DuckDB can encrypt its own storage format now. So you can have encrypted DuckDB databases. So you can email a file and then separately give someone a password and then only they can see it. So that's really helpful. It's great for storing it in other locations where you want to only grant access to certain people.
52:42DuckLake takes advantage of Parquet's encryption. So Parquet has had encryption support for some time. And what DuckLake will do is you pass in a single flag, encryption is true, and DuckDB will encrypt every Parquet file it writes with a separate encryption key. And that encryption key is stored in the catalog, which is a SQL database. You know, Postgres, MotherDuck, DuckDB.
53:04And so it's very, very seamless. There's also very low overhead to this as well. The decryption is quite fast because of the hardware capabilities to do that decryption. So it's something that's pretty unique in the Lakehouse space because DuckDB only uses Parquet files for storage. And all the metadata is safely in a database. Whereas other Lakehouses have to write Avro files
53:28and JSON files as a part of the spec on object storage, and they cannot be encrypted. They're plain text formats, or in the case of Avro, a binary format with no encryption support. And so there's no way to fully encrypt your data and you have to keep your object store super locked in. With DuckLake, you can encrypt your data and you could even go so far as to use a totally public bucket
53:53because it's all gibberish to everyone else unless they can connect to your super safeguarded, you know, database, either MotherDuck or Postgres. And so it's a very unique, very cool capability. If you are interested in encryption, definitely check out how DuckLake has implemented this. I think it's a really unique differentiator in the Lakehouse space. PostQuantum, I cannot help you. It's beyond me. It's a great question. Please ask in the DuckDB Discord.
54:18I think it'll be a fun chat. Yeah, indeed, indeed. Yes. Let's see. Okay, we'll take one more question here, which is about data masking. I don't know. This is probably more of a product question than a, you know, question for us, but I think it's interesting to think about any thoughts about using DDM for data masking. I'll just start with,
54:44we're definitely thinking about this, right? Especially on the column level, but it is, you know, early days for us in terms of how we wanna frame this and think about it. You know, DuckDB, because it operates on columns, column level masking is something that I think would be a natural fit at some time in the near future. You know, we are definitely thinking about, from a product perspective,
55:07what do our customers need, you know, kind of from a enterprise governance and data management scale here, but it is not like, you know, priority number one in terms of like where we're aiming the roadmap in the short term, but it is, you know, we're thinking about RLS and, you know, more SSO,
55:31and then, you know, obviously data masking fits in there. How do you do all of this in a performant way? There's a bunch of things to do here to make it all work. So yeah, it should, you know, I don't think there's anything that's impossible here. It's just a matter of kind of prioritizing it as it makes sense for our customers. Alex, you wanna add anything? I would just say that it is very important to us and it's on our roadmap to be able to provide
55:53any level of access to any specific object that you'd like. So you can- Yeah, that's right, that's right, that's right. A lot of access control, but we wanna get more and more and more granular over time because we recognize enterprises are complex, you know. Yeah, that's right, that's right. So we're headed in that direction. Yeah, and AI makes it worse, right? So you got way more, there's way more things that you need to think about controlling in terms of what does it look like
56:16to add agents to the mix as well, right? So totally, yes, it is top of mind for us, but it is, you know, we wanna make sure we do it right. You only get one shot at it, right? If you implement it and it's not right, you are in trouble. So yeah. Thanks everybody. I think we're gonna wrap this up. Really, really appreciate all the awesome questions.
56:40Alex, thank you so much for reading the PRs so I didn't have to, and this is great. You bet. Shout out to DuckDB Labs for their incredible work and the MotherDuck team working together with them. So hug an engineer that's making DuckDB 1. 5 happen. Give him a high five if you see him. And pleasure having you all here today. Thank you for the great questions. Thanks everybody. We'll chat later. Bye.
