Resources
Jan 9, 2025 |
58 min
|
2.5k views
|

DuckDB and the future of databases | Hannes Mรผhleisen | Data Science Hangout

To join future data science hangouts, add it to your calendar here: https://pos.it/dsh - All are welcome! We'd love to see you!

We were recently joined by Hannes Mรผhleisen, Co-founder and CEO at DuckDB Labs, to chat about the evolution of database systems, the unique architecture of DuckDB, and how to find opportunities in areas others might consider "boring." He also showed us a picture of the real life duck, Wilbur, that inspired the name DuckDB! ๐Ÿฆ†

In this Hangout, we explore how DuckDB is changing the traditional concept of a database by making it accessible to a wide range of users and use cases. We also discuss its origins and how it was inspired by the challenges of data analysis and the importance of focusing on real user problems when building tools.

What is a database and where does it live? DuckDB is challenging database assumptions by saying... maybe your expensive laptop can hold a database instead of just pinging one? Maybe it can do more than just run web browsers! Hannes wants you to know that database doesn't have to be a bad word, and that you can actually have a great experience with one. If the data community's affinity and enthusiasm for DuckDB is any indication, we think he's right.

We hope you listen in to hear Hannes talk about: a simple explanation of what DuckDB is, DuckDB's in-process architecture and why it's so cool, different DuckDB use cases (should you be using DuckDB?), DuckDB's compatibility with cloud data systems, and more.

Resources mentioned in the video and zoom chat:
๐Ÿ”— DuckDB Website โ†’ https://duckdb.org/
๐Ÿ”— DuckDB Labs Website โ†’ https://duckdblabs.com/
๐Ÿ”— Posit Conf talk by Hannes โ†’ https://www.youtube.com/watch?v=GELhdezYmP0
๐Ÿ”— Sarah Alman's webinar on using DuckDB and Shiny โ†’ https://www.youtube.com/watch?v=6AGroJb4zPM
๐Ÿ”— Forbes Article on Imposter Syndrome โ†’ https://www.forbes.com/sites/marycrossan/2024/12/18/how-to-combat-imposter-syndrome-by-developing-character/
๐Ÿ”— MotherDuck โ†’ https://motherduck.com/
๐Ÿ”— MotherDuck Blog Post "Big Data is Dead" โ†’ https://motherduck.com/blog/big-data-is-dead/
๐Ÿ”— Posit Blog post on Shiny with Databases โ†’ https://posit.co/blog/shiny-with-databases/
๐Ÿ”— DSH episode with Marco Gorelli โ†’ https://www.youtube.com/watch?v=lhAc51QtTHk
๐Ÿ”— Emil Hvitfeldtโ€™s talk at posit::conf about orbital โ†’ https://youtu.be/Qnm1y0KPxVM
๐Ÿ”— Marcos Huerta's Blog post on DuckDB and Bert โ†’ https://marcoshuerta.com/posts/duckdb-and-bert/

If you didnโ€™t join live, one great discussion you missed from the zoom chat was about whether industry teams who use and benefit from open source tools could devote 5-10% of their time to giving back to the open source projects with code contributions or other support. Let us know below if youโ€™d like to hear more about this topic!

โ–บ Subscribe to Our Channel Here: https://bit.ly/2TzgcOu

Follow Us Here:
Website: https://www.posit.co
Hangout: https://pos.it/dsh
LinkedIn: https://www.linkedin.com/company/posit-software
Bluesky: https://bsky.app/profile/posit.co

Thanks for hanging out with us! ๐Ÿ’›

image: thumbnail.jpg

Transcript#

This transcript was generated automatically and may contain errors.

Welcome back to the data science hangout everybody and welcome to the last data science hangout of 2024. If we haven't had a chance to meet before, I'm Rachel, I lead customer marketing at Posit and I'm based in the Boston area so if you ever want to grab coffee or get together I'm in Boston.

Posit builds enterprise solutions and open source tools for people who do data science with both R and Python. We are also the company formally called RStudio just in case anybody needs to hear that but I'm joined by my lovely co-host today Libby. Libby do you want to introduce yourself?

Hello everybody I'm Libby, I'm a community manager who works with Posit to help our beautiful hangout community thrive and I'm also a Posit Academy mentor so speaking of R and Python I teach both R and Python, I mentor both R and Python for Posit Academy to help working professionals learn R and Python and use them in their data work.

We're so happy to have you joining us today. If this is your first hangout let me just tell you a little bit about it. So the hangout is our open space to hear what's going on in the world of data across all different industries, to get to chat with others about data science leadership and connect with other data community members who are facing similar things as you and we get together here every Thursday at the same time, same place, of course not if it's a holiday week so we will be on a little bit of a holiday break and coming back January 9th but if you are watching this as a recording on YouTube and you want to join us live in the future there will be details to add it to your own calendar below.

Thank you so much to those who have helped make this the friendly and welcoming space that it is and we're all dedicated to keeping that it that way so if you ever have feedback for me about your experience anything you want to share with me anonymously or maybe suggestions for topics I'll share a Google form in the chat with you right now but feel free to use that you can always reach out to me directly on LinkedIn too.

And here at the hangout we love hearing from you it doesn't matter what your years of experience are, what your title is, what industry you're in, or what languages you use or do not use, you are welcome here in the spaces for you. I encourage you to connect with each other in the chat so introduce yourself, say hi, say where you are and what you do, by the way I'm in San Antonio, Texas, and then also what you like to do for fun that's often the way that we connect best with each other right. Feel free to share your LinkedIn profile and also a link to your website so that people can actually find you once this Zoom chat goes away.

If you are hiring for any roles please put them in the chat we would love for everybody in the community to benefit from that exposure and then if you're looking for a role let people know that you're looking what you're looking for, where you're located, all that stuff. Okay so this is a community-driven discussion that we're going to be having with Hannes and that means that you ask the questions.

There are three ways to jump in, you can raise your hand on Zoom, we'll just call you to ask your question. You can put a question in the Zoom chat and if you can't ask it, maybe your mic's not working or you're somewhere loud, put an asterisk somewhere in there at the beginning of the or the end and we will ask it for you. And then we also have a Slido link where you can ask questions anonymously. Isabella just put that in the chat.

Perfect, very quick shout out. You'll see in my virtual background I have a shout out to Posit Comms. Just letting everybody here know that registration is now open and it's going to be the lowest price that the tickets are ever going to be so I just want to make sure I tell everybody that here, didn't want to forget. With all that, thank you so much for being here. Libby and I are so excited to be joined by our other co-host today, Hannes Muelleheysen, co-founder and CEO of DuckDB Labs. Hannes, I would love to have you introduce yourself first and share a little bit of what you do at work but also something you like to do for fun outside of work.

All right, well thanks for having me. I'm really, you know, really happy to see so many people. On the Thursday before Christmas I was a bit concerned but my name is Hannes. I mean, yes, I work at DuckDB Labs but I'm also academic so I'm also a professor at university. I teach data engineering and I'm also active in the open source space that we make a database system called DuckDB and it's an open source project and, you know, it takes most of my time so at some point we spun out of the research world into a company, DuckDB Labs, where I'm now sitting. I'm still at the office.

It's here in Amsterdam where it's the longest stretch. We haven't seen the sun in 30 years so nice. And yeah, so what I do, yeah, I'm leading the company and I'm still hacking around at DuckDB so today was a glorious day because I got to just sit here and hack around because it's, you know, it's kind of quieting down for the holidays and it's really amazing so I did some stuff.

But anyways, what do I do outside work? I like boats so I live on a houseboat with my family in Amsterdam. I actually can drive around so sometimes I do that and when we're not driving around it's usually some maintenance issues that have to be addressed which I also really love. I'm a practical person. I like fixing things and so I also like making software. It has somehow, it's connected. Making software and fixing things, I don't know why.

Explaining DuckDB

Yeah, so does that answer your first question? Yes, absolutely. My second question is going to be can you explain DuckDB to me like I'm five? For anybody here who hasn't used DuckDB, who doesn't know what it's about or why anyone would ever use it?

Right, so it's kind of funny because DuckDB is a data management system, okay. It speaks SQL, it's, you know, can do transactions, it can store data tables, these kind of things. Quite traditional in that sense but it's quite new in the sense that it's how it's deployed. It can run anywhere. It can run in your, you know, Python shell, it can run in your R shell, it can run in the browser, it can run on your phone, it can, you know, kind of, it kind of puts a technology that used to be sort of locked up in one place and can kind of, because we used to have like data warehouse servers and they were really like one place. And DuckDB really sort of opens that up in sort of deployment sense and you can put it anywhere.

Now, it's interesting because it was really built with Anthony D'Amico, maybe some people know him. He did like asd3.com, it's like just to analyze survey data with R. And at some point, we started talking about, you know, the American community survey which was creating issues because there were too many Americans or something. And this is, you know, there were too many rows, what can I say. And so he started like exploring database technology to make that work. And from these discussions over many years, it's been almost 10 years now that we had the initial discussions, we started designing a database system from scratch that can do that.

And so yeah, DuckDB is open source. It's the fastest growing database system I think ever. We are sort of, there's some of these metrics that we're tracking, kind of interesting to see that. It's free, anybody can use it. It's used all over the place.

It's the fastest growing database system I think ever.

The company is huge and small. And we have a soft spot for data science since because of kind of that was the original sort of mental discussions or the initial discussions that eventually led to it came out of interactions with, you know, the R community specifically. So that's why we've been working with people. I mean, I've been working, we have been working with Posit to make that player, but also it's just generally the R community. We've been spending quite a lot of time because it's really great as inspiration to see what people's data problems are, right? Like we are database systems people. We're like, okay, what are the actual problems? And you find interesting problems like, Hey, I need to, I'll just wear like accidentally unmuted.

Yeah. Because yeah, you'll just, you'll just, you know, I need, need to import CSV files or something like that. And these are things that are very unwieldy. It can be very unwieldy and we've spent a lot of time on it because it's such a relevant thing. So yeah, DuckDB, it's a, it's a piece of software. You can, you can write SQL queries. You can have a bit of a syntax tree and you can have all these things.

Yeah. Awesome. Thank you. And I think that the thing that I've gleaned from people, because I have not had a need to use DuckDB. And so I'm coming at this from like a, I don't know all the answers standpoint is that when people need to work with data, that's bigger than working memory and they can kind of create a temporary database on their system and store that in disk instead of in working memory. Is that right? That definitely can be done. It doesn't have to be temporary. It can also just be, you can just keep your data in the database.

Absolutely. It becomes interesting. So people generally say, right, Excel stops working at, you know, a hundred thousand rows. R maybe stops working at, I don't know, 10 million or something like that. It's like base R, dplyr, that kind of stuff. And then, so DuckDB is kind of for this mid range where you go, you know, let's say up to a couple of billion of rows or something like that. That's really where people used to go for like these big data solutions in the past. And now you have like an engine where that can get you, get you sort of much further on your, on your local laptop, right? Because you have your laptop already paid for it. It's there sitting there. It can do, it can do surprisingly a lot of computation, but indeed if you're, you know, if your biggest file is 15 megabytes, then there is probably no need for it. Does it make sense?

Finding opportunities in overlooked problems

Yes. Thank you. Thank you. So Hannes, one of the takeaways for me from Hangouts this year, and I was thinking about our conversation a few weeks back is around like getting, like finding the things that people might not think are cool and focusing on them. And I was thinking back to when Marco Gorelli was also talking about like getting excited about things that people might think are boring. And I've just been thinking about for myself, what are those areas that I want to dive deeper into? And I was wondering if you could talk to us a little bit about that. But working on things that are boring. Yeah, it's very, I don't know. I don't think there's boring things.

I guess it's more so like your thoughts on that and you sharing like advice for finding opportunities where there are things that might be uncool to others. Like how did you start databases? Yeah. So I think in general, there's really no shortage on sort of ideas on what to work on. And I think personally there is a bunch of, I really like looking at Stack Overflow and Reddit and see what people kind of, what kind of problems people have. Right. And then you can see, oh, you know, like, and really you have to squint, right. Because like one specific person's issue is not really interesting because there's so many of them. But if you see the same exact sort of class of problems sort of coming up, you know, 10,000 times, then maybe, you know, there is, there is something there.

So one of the things that we, that we did was we were teaching these courses with Spark. Maybe some people now have worked with Spark. So I was teaching a course with Spark. Yeah. It wasn't a great success because people generally didn't manage to work with Spark. So we were like, okay, here's how you Spark. We tried to teach them how, here's how you operate this thing. Obviously we had some concept on how to do it. But then nobody managed. And these were not, you know, these were not, you know, toddlers. These were like people who had been around data for ages and they just couldn't manage. And after a couple of years of giving this course, we thought, we started thinking like, yeah, maybe, maybe there's, maybe there's something there. Maybe, maybe that doesn't have to be so difficult to, I don't know, write a parquet file.

Right. So, and I really encourage also all my colleagues in research and in the open source space, it's like, it's so easy to, I mean, people tend to want to set their own agenda and then just basically execute that. But I feel like for me, I really want to, you know, address an issue. And for that, really, there's, there's really, there's so much information, there's so much, so many resources on, you can just download the Reddit dump or whatever it's called. And then, and just start, you know, analyzing it and you will see, you know, what are the, what are the things people struggle with? You know, I can't get this to work, you know, just search for that. And then you have some idea.

And that's, and that's, I think also part of the, you know, that's also part of the secret of maybe the tidyverse that this really addresses a need that, that, that, or this idea that you have to have some concept of what your data should look like. That, that's also, I think, a result of just seeing people struggle for a long time.

Why the name DuckDB?

Yeah. Thank you. Mike, I see you asked a question earlier in the chat, and I think this is always a fun one to talk about. Do you want to jump in? Mike Smith. Sorry, frantically coming off mute. This is probably the Christmas season type question. So why duck? Why duck? Yeah. So there used to be a duck called Wilbur and he was my pet duck. And he lived with me on the boat and it was very cute. Very cuddly duck. Who could

have imagined that ducks can be cuddly? But yeah, so he eventually flew away and, you know, we like to think started a ducky family somewhere, but he, in his, in his, in his memory, DuckDB is called DuckDB. Yeah. A little Wilbur. I have a picture here somewhere. Hang on.

Yes. Duck pictures. We demand them. Duck pictures. Libby, did you bring your stuffed animal duck? No, I didn't. I thought about it. It was all dusty. So here's, this is little Wilbur. You can see him there. This is me on the other side, obviously. And he's just standing there hanging out, you know, being happy, being very loud. Ducks are loud, man. Ducks are loud. I love ducks. I have a, I have a bit of a duck collection. Duck books, duck stuffed animals.

DuckDB in corporate and cloud environments

Well, I have some, there's some amazing questions that are popping up in the chat. So we've totally got to get to some of them. Marcos, you had a great question. Do you want to jump in? Yes. Can people hear me? Do you remember? Yes. Yes. Yes. My question is, I've used DuckDB for some of my personal projects. Like, it's great at, like, CSV to Parquet if their files are big. It's, it's, I got the vector data support, which was really helpful, combined with some other simple small language model stuff. But work, my data's all up in the cloud. My data's all, like, in Snowflake. Like, how do you see, do you see DuckDB as mainly a tool for, like, kind of researchers working on the laptops, kind of in, like, academic environments? Or, or do you see a, a corporate kind of, you know, use case where our data's mostly in the cloud?

Yeah, that's a great question. So we do see actually a ton of corporate use. This is the fastest growing, let's say, area of DuckDB use. So there is a lot of the, sort of, I'm here on my laptop and I just have to deal with this data sort of use. And that's also where DuckDB came from, as I've explained. But there's, but there is a ton of use in corporate. And it's less, let's say, as a sort of complete sort of system, but people use it a lot as a building block. So say I have, you know, some, for example, if you look at one of the modern BI tools out there, almost all of them use DuckDB under the hood. If you look at, you know, for example, there's big sort of data transformation companies like Fivetran that are also using DuckDB under the hood. So you might not actually realize it that you're, that you're, it's not, it's not like, it's not like, I don't know, Snowflake, where, you know, you're talking to Snowflake, you know, you're talking to Snowflake because there's a like little blue logo up in the corner there. But what we see a lot with, with DuckDB is that people put it in, put it in somewhere and, you know, just, you can kind of figure it out if you really want to, that it's running DuckDB under the hood sometimes because, you know, the SQL dialect will have some things that will give this away. But very often it's also just, you know, pre-baked queries, pre-baked schemas, just run this thing and people are happy. And that's, that's, that's really fine with us. So, so we're not, we don't, we don't, you know, it's open source software, people can do whatever we want, but that's, I think the growth that we see a lot in, in these sort of use cases.

And for people that do want an end-to-end sort of solution, there is MotherDuck, right? There is a company that is building a DuckDB SaaS, which is, you know, has look and feel, I would say, similar to Snowflake or BigQuery or, you know, whatever else there is. So there is, so there is, there's these two things, right? Yeah. And some use cases that are kind of wild. So, you know, people put it in cars and then I'm like, are you sure? It's like, but, but yeah, so I think, I think there is a lot of use in, in big, in big tech. It's just sometimes not visible. Cool. Thank you very much. Thank you.

DuckDB with Shiny and Python integrations

I see Zach asked a question with an asterisk, so I'll read it, but it was, I believe I read somewhere that DuckDB can be used with Shiny. Generally, how does it integrate with our Python and data visualization tools? Yeah, I think there's some, some demos out there, people using DuckDB with Shiny, generally. So how does it integrate our, and that's, okay, my, my headset is letting me know that my battery is low, so I will switch to something else.

Your audio was like just clipping and cutting out a tiny bit, but it was still okay. We could hear you. Can you still hear me? Yep. Yep. Great. So you get to see my ears. But, but, but the question, so the integration with R is really interesting and it's not like anything you've kind of ever seen, I think, in terms of data management systems, maybe except with SQLite, because it's this in-process architecture where the whole thing runs inside whichever process invokes it. So with R, you just have an R package that's, you know, installed on packages, DuckDB. And that actually is not a client or a driver, like maybe for other systems, it's actually contains the whole thing. And then when you start it, it actually runs inside this R process that you're sitting in front of. And yeah, that has some, some very nice benefits. For example, you can read stuff from memory. Like if you have a data frame in memory, we can read the data frame. When you have a query result, you can directly turn that into data frame or error structures. And the same thing also happens in, in Python, where you can, you can just load the, you know, pip install DuckDB and install the package. And then you can just load it up inside this Python environment and, and look at, you know, Pandas data frames or Polar structures or, or IR structures again, because it's, yeah, it's just right there in this process. And it doesn't have all this like client service stuff. You don't have to set a password because there's no point of having a password. It's right there in your thing. It's yours, right? You can, you can do whatever you want with it.

the integration with R is really interesting and it's not like anything you've kind of ever seen, I think, in terms of data management systems, maybe except with SQLite, because it's this in-process architecture where the whole thing runs inside whichever process invokes it.

I wanted to just call out quickly. I see Isabella shared in the chat. Sarah Altman at Posit gave a great webinar yesterday on using DuckDB and Shiny together. Hey, my job is done. There's a great blog post there and then the, the YouTube recording too.

What is a database? Rethinking the concept

Great. Elaine, I see you asked a question in the chat just now. Do you want to jump in next? And nice to see you.

Sure. Hi. So, so I've been reading more about DuckDB and using it in a, in some of the creative architecture bucket of use cases and doing serverless apps and things. And I, I realized that there's a lot of things I think I know that I don't really know, like what is a database?

And I, I think in the past I've thought of as a database as something that has to be in the cloud because lots of things are happening to it in different places. And therefore I have to query that instead of just, you know, having my, my data files locally, but files can also be in the cloud and DuckDB can query files. So what is like, I need to update my mental models of a lot of things. Can you weigh in on how data scientists should think about what a database is and is DuckDB actually like changing the concept of database or do I just not understand that a database is always like being created under the hood somewhere?

No, I think, I think it's definitely changing what people, what sort of this conception of, of what that, that what people have of what a database is and what you alluded to where it lives. Okay. So, I mean, what is a database? There's there, especially in stats, there is a lot of confusion with the word. So, I mean, because there is the database, which is just a collection of rows. And then there's the database management system, which is of course something like DuckDB, which is the system that you used is confusing terminology, but in principle, it's just a collection of tables, right? And so that can, and that's, that's, you know, that's what I consider to be a database is a collection of tables. And then you have, the database management system, which, you know, provides management of these tables, like, you know, store them, change them, query them most prominently.

And now it's not, not like, and again, in the past, it used to be the case that these things were sort of, you know, cloud, you know, some, some, some sort of data warehouses also termed there. It's like a, like a Teradata somewhere, or maybe a green, or I don't know, some Oracle server somewhere. And that was your database. And it's, it's true that in the past, these things were kind of locked into these specific pieces of hardware also often because they would just don't run on anything else. But what we are seeing, and I think that's kind of the point of DuckDB is that, that you can just pull a lot of this stuff, you know, into places that you haven't, that you haven't sort of considered yet. And like your laptop is something that it's, I find it always funny when people sit in front of a $3,000 sort of MacBook that runs a web browser and nothing else. Right. And then they use that to spend more money on Snowflake in order to query like a bunch of rows, right. When they have an actually pretty powerful machine sitting right there.

And so what you said, I really liked it's also the creative architecture. It does really change people's perception, I think, and for people's perceptions also maybe should change in terms of what does database actually mean. And part of our mission is to, is to maybe reduce the negative connotation of it with the word, because I think data scientists in particular have been, how should I say, a bit, a bit fearful or loathsome of database tech in general, because it's, you know, it worked differently from the other stuff. So it's why we're trying really hard to, you know, to make it easy to use friendly, these kinds of things. But, but in principle, yeah, it does actually change the, the architecture. I've done, I've done, I've done some talks if people are interested on how the two-tier architecture is sort of no longer up to date because it's just one of like 15 different models of deploying data management systems. So yeah, it's pretty cool, but it's, it is, I agree. It is, it also took me a while to realize this from, you know, from, and I'm doing this all day long, so.

I think data scientists in particular have been, how should I say, a bit, a bit fearful or loathsome of database tech in general, because it's, you know, it worked differently from the other stuff.

It's kind of pretty, maybe. Hannes, we can hear you okay, but it's doing a little bit of a weird like jumbling thing at the end of some words. I don't know if it's trying to reconnect to your headphones or.

Oh, I can try another. Let me maybe just try another set of, I can just go to my, it's not, it's not awful, but if it could be better, may as well spend the one minute to figure it out. Absolutely.

And now I'm going to go to the speakers. So is this better? Yep. You sound good. Great. And it wasn't really bad. I just wanted to, just in case it stayed the whole time. That's, that's, that's perfectly fine. I'm sorry about that. No, that's okay.

DuckDB for small relational data in clinical trials

And I think Libby, you were going to call somebody in from the chat. Yeah. Travis had some great questions and I, Travis, I have the same question, so I would love you to ask.

Sure. Thank you. So you mentioned that maybe 50 megabyte or less files, maybe DuckDB isn't the right solution, but are there wrong solution of the saying then you're maybe existing stack also works fine. I mean, there's no, there's no lower limit if you, if that's what you want to know. Yeah. Cause I was kind of curious. I, so for example, I work in clinical trials and most of our data is actually small, but we do often have a lot of relational tables is, is I assume this could be a good use case for, for DuckDB. There was like, could you walk me through that? Because I'm sort of a newbie to this stuff, like many here, like what, how does that look for us? If I, I don't ever want Snowflake because I don't have data that large. But we also do lack proper databasing practice. I think as an industry, we don't do that in pharma because our data is so small. So what could you dream up like a stack for me?

Yeah, for sure. So I think, I think, I think you're in bioinformatics. Is that fair to say? Sure. I mean, a lot of it's actually just straight up clinical data. So we'll have like patients come in because bioinformatics kind of starts to imply more large data. We don't even have that. It's just going to be like people and they showed up at a clinic and they had a lab value or something like this. Right. Yeah.

No. So I think, I think what's, what's, what, what really is interesting is what people sometimes forget is that there's like more to database systems already traditionally than like just store of tables. And I think what's interesting for things like small, maybe smallish data is all this integrity sort of dance, right? Where you say, okay, um, I'll actually define some, some, some relationships here, like, you know, foreign keys, primary keys, constraints, these kinds of things. And that will actually allow me to keep my data, um, consistent and, um, with a really good, a transactional system like DuckDB actually will also make sure that any changes you make to the data will not break any of these constraints. And that's actually something that I think data frame libraries users in general have just no concept of because it's never been something they had. Um, but it's pretty standard, uh, in, in, in, in sort of relational technology. So I think what might be interesting, if you say, I don't have so huge tables, but, um, they are kind of connected in a complex way. And I need to do all these funky recombinations. Um, then there's definitely, definitely a great use case because you, um, yeah, you, you are able to basically go from one consistent stage, another consistent state and your, um, let's say your value per byte is extremely high, right? Like it, it's not like that you collected some log files somewhere and it's like probably garbage, but maybe something interesting is there. It's more like, no, no, no, this took, you know, every row, you know, was, was, was a whole patient trajectory and that costs, you know, a hundred thousand dollars. So it's very high value data. Um, and maybe we want to treat high value data better than sticking it in a CSV file. Maybe we do want to have a schema. Maybe we do want to make sure that we

have the right types. Maybe you want to have the constraints, maybe you want to have the integrity, uh, relationships on the table. So that's also something that, um, that was actually in my, in my talk at Posit Conf this year that I tried to sort of make this point where I was trying to say, Hey, um, yeah, uh, it's, it's, there's more to, to table technology, uh, than, than just running queries. It's also just all these other guarantees that you kind of get for free. Um, that, that, uh, that could be interesting for data practitioners.

That's an awesome insight. So where would the, um, the file, I mean, I'm assuming DuckDB, this is another novice like question, right? So like DuckDB it's serverless is a word. So there's a file and where does it live? Is it like, do you pass it around? Do people email DuckDB? Yeah, people do actually. So, uh, it's a fun, fun story. So, um, when you, when we designed the data file, the format, the file format for DuckDB, uh, you know, way back when, um, we actually, this use case of people emailing around files was something is definitely on the, on the board because it's, we saw it happening and, uh, uh, with other things. Um, no, so how does this work in the deployment model? Well, this depends a bit, right? So, so for some first depends a bit on how your update process works on how, how often things are updated, but for example, in easiest case with static data files, what we see a lot of people putting, uh, these files in a central location, maybe on a cloud storage or something like that. And then DuckDB can directly query that, right. Or you put, you put this on a, on your server and you just interact with this file there, like, you know, in the old days with a mainframe, perhaps. Um, we also see some interesting use cases where people just jury rig some sort of RPC calls around DuckDB because indeed, um, the multiplayer experience in DuckDB isn't, isn't the, isn't, it wasn't a design goal and still isn't the design goal. And for that, you have other things, you have things like MotherDuck that actually that's kind of their core thing. So they kind of extend this thing with, with a sort of, um, multi-user sort of sharing capability when you don't have the stuff on your computer. But that's, that's, that's more, more complex deployments. I think that's, that's a different, it's not a traditional data warehouse.

When you're saying multiplayer, so if I wanted to have multiple users, let's say access their front end and write new entries to a DuckDB file, um, that's where MotherDuck and other things come in. Is that right? That is definitely where these come in. I mean, you can definitely have, if you have like a web application that's sitting on top of a DuckDB file, they can definitely have multiple users writing it at the same time. It's just that you cannot, I don't know, you cannot copy that file to another computer and magically have these two things connected. It's just not what we do. Right. Got it. That's so helpful. Thank you. Thank you.

Community questions

All right. We have a question from another amazing developer in our open source community. Marco, can you ask your question live if you're still here? Um, yeah, sure. I would love to. This is an okay forum to ask this question. Just noticed, uh, upgrading to DuckDB 1.01. Now, if I do lots of operations over an empty window, like lag over just empty bracket, that now the order of rows is preserved compared to the input. And before that it didn't used to happen above a certain size, but in the release, and this seems like an awesome change, but in the release notes, I couldn't find anything about it. And is this an intentional change in 1.1? Uh, probably. Um, so the thing is the, we get a bit overwhelmed with the release notes because, uh, it's usually such a gigantic amount of changes that, that go in that, uh, you mean that it wasn't mentioned in the blog post or it was just not in the release notes at all? Yeah, not in the release notes. Yeah. Okay. Um, it could, I mean, there have, this is a window function related change, right? So I'm not the expert on window function. I did implement the first iteration of it, but since Richard, one of our guys has taken over, um, and he has been, has been spending a lot of time on the order of window functions. So I just merged a pull request yesterday with another set of changes to the order of window functions. So it could be that it's, um, that it's, um, changing it again. In general, the, um, SQL order is not defined, right? Like SQL systems don't have a defined order. Um, window functions usually have, uh, have an order defined. So then we can, we can, we can use that. Um, but, um, we have, we are trying more, let's say to keep the, um, input order, um, alive. Um, let's say as we go forward, because people just expect it. So it could be that this was intentional. I'm not entirely sure. Uh, there's so many things going on in DuckDB land that, uh, that I'm not, you know, that I'm not actually on top of. So that's not entirely sure, but great to hear it's, uh, it's improved. Okay. Thank you. Great to see you.

Thank you, Isabella. I see you had a question a bit earlier. Hi everyone. Um, I had a roadmap question if that's all right. All right. So row-wise operations in dplyr are nice and legible, but they're not as fast as other options with large databases. Uh, but the data.table package recently implemented argument for faster row-wise operations. So my question is, um, do you know if DuckDB and dplyr has any roadmap for faster row-wise operations? Um, you would have to explain to me what you mean by row-wise operations. Oh, okay. Um, it's like a different way. If I.

No. Isabella, freeze. It's just another way. Oh, you froze for just a second, but you're back now, Isabella. Okay. Um, maybe I can follow up with you. It's, it's a way of work. Okay. We'll jump to the next question. Sorry, Isabella, it keeps freezing, but if you reconnect after we can jump back. Um, I see there was a Slido question. Now I'm wondering, am I the one that's frozen? No, no, no. Okay. I can make some movement. Okay. Okay. Perfect. Yeah. Um, it's still working. You're still alive. Okay. Great. There was a Slido question that was, how would you describe the culture at DuckDB and what steps do you take to nurture and maintain that culture within the organization?

DuckDB culture and project governance

Um, that's a great question. Um, so the, um, the culture here at the company or the project, I mean, I can talk about both, right? The, um, the culture here at the company is, um, a bit like we took what we knew from a research group and try to improve it where we thought it was going wrong. Um, uh, so, you know, it's a very sort of, you know, I would say, uh, progress sort of oriented, uh, team here that we, you know, we, we all, yeah, all very good at database engineering and, um, and, uh, you know, we just enjoy each other's sort of company. And generally we have a rule that we only hire friendly people that has worked really well so far.

Um, and, uh, in the project itself. So DuckDB is a bit special because we don't have open governance in DuckDB. So that's, um, it is a open source MIT license. You can, you know, do whatever you want with it. Um, but there is no, there is no sort of democracy that sounds a bit terrible. I have to admit, um, there's no democracy in terms of, um, direction of the project. And that's, that's intentional because we've seen, um, specifically Apache projects to go down the drain, um, because they couldn't agree on, on, on a vision. And in the end, just, they just did whatever, you know, everything everyone wanted. And in the end, they're unmaintainable messes. And, um, it's because we have seen this so many times we've, um, we just decided that we're gonna keep control over what goes in. Um, so does Mark and me, uh, the two creators, original creators of DuckDB, um, uh, in, you know, just to, um, you know, to keep the vision. We do have very strict rules on behavior, like not strict. I would say we do, and we are very conscious of keeping, uh, productive and friendly tone on things like GitHub issues, comments. And we, we are, you know, we're trying to, we're trying to really, really be friendly to anyone. I think we have over the years, we've, I think, banned two people of our GitHub repo, which is, uh, for really unacceptable behavior, but we are really trying to have a positive, um, interaction also on our discord and all the other places we interact with the community. And I think it's worked out really well. The, the, the tone is incredibly friendly. People want to help each other. So, um, yeah, I think it's also just, you know, leading by example, there's other open source projects, you know, like Linux where the head guy in charge is famously grumpy all the time, like that will trickle down through the community.

leading by example, there's other open source projects, you know, like Linux where the head guy in charge is famously grumpy all the time, like that will trickle down through the community.

Um, and we're not doing that. We're, we try to be, you know, consistently friendly. Um, and it does actually, and I think which shows in the, um, community, but it's also something that I think, um, Hadley is doing extremely well. It's just this incredible civility that he brought to the previously grumpy, our community, I think. Thank you. So maybe just a real quick clarification. So I think we, we find this with our studio being a product and then being the company and open source and pro product name, and then becoming posit. So how, how are DuckDB and DuckDB labs separate in like sentences? Yeah. I mean, the DuckDB is the project that's database projects called DuckDB and DuckDB labs is just the company, the commercial entity that exists where most of the developers, um, work and, um, where we work with, you know, corporate partners that have, you know, uh, special corporate needs around DuckDB.

Roadmap: machine learning and database connectors

Thank you. Um, Sunday, I saw you ask the question and I, it actually also might answer Javier's question. Javier, if you wanted to jump in and follow up with Sunday too. Sunday, go ahead. All right. Sure. Uh, can you hear me? Yep. All right. Um, oops, one moment. Yeah. Uh, first of all, thank you so much. I'm a big DuckDB enthusiast. Um, can't remember when last I used SQL light since I found DuckDB. Yeah. Thanks for that. And thanks for the great things like, you know, group by all, it was long overdue. Um, now thinking about, um, you know, things like orbital and how that integrates nicely with tidy models and then post queries, which you could pass into, um, SQL for machine learning. Is any plan for some support for machine learning as we go into the future with DuckDB? Um, yeah, I think we're quite happy where we're at in terms of, um, you know, uh, API or general focusing on relational transformation of tables. I mean, we are sometimes expanding. We recently added like pivot statement, which is a pivot statement, which is doesn't exist in, in, um, in SQL in general. So we are expanding there, but we try to keep it focused on, uh, let's say table-y operations. Um, there are, there are some operations to do simple, you know, regressions and stuff like that in DuckDB, but it's really not our focus. There are other tool kits that are great at, um, at machine learning tasks and we try to integrate. So that's one of the strengths that I think DuckDB has by being in process. We can, for example, export to, uh, I don't know, scikit-learn fairly efficiently. Um, and then they can do their thing and we can pull back the results and do something else. It's not like traditional databases, like, for example, Oracle that have to sort of push everything into the database system itself for them to be efficient. We can just say, yeah, you want to pull this out and run something in it. Great. Just do it. It's not going to take forever. Um, so yeah, we try, we try to focus and we're a small team with 20 people. We need to focus. We need to, you know, only do things we can, we can sort of do. Uh, and table SQL is already a, um, let's say challenging, uh, target.

Thank you. Thank you. And Javier, does that cover your question as well? It does, but I do have other questions. Oh, do you want to hop in? Sure. Yeah. Um, so, uh, I think it was Marco earlier asked about, um, a different Marco asked about DuckDB's kind of roadmap for interacting with maybe data warehouse data. Oh yeah. Um, is there anything DuckDB can do today with, um, ADBC, like the ODBC, ADBC competitor? Yeah. So we do have ADBC support, um, in DuckDB. So for those who don't know, this is like something came out of Voltron, um, or the Arrow project, uh, where the idea was to update the standard database protocols a bit. And, uh, we do support, um, ADBC. Uh, we also generally support Arrow ingestion. So if your database supports Arrow, then you can ingest that into DuckDB quite efficiently. Um, we do have specialized connectors for things like SQLite, PostgreSQL, MySQL. Um, and what we do have on the roadmap is a generic ODBC connector that you basically can also just pull stuff out of your SQL server or Oracle or DB tool, or, you know, these, these, these ancient things, um, into DuckDB. Um, but we also seeing a lot of interesting developments with things like, um, Iceberg or Delta Lake where, uh, you know, the, the, the big players are starting to make things accessible through these interfaces. And, um, yeah, we are, we are working on that, that already works okay with DuckDB, but we're working also on that, um, to improve that further. So, um, we do see a lot of use cases where basically, you know, an analyst will just pull some stuff from the production database into the local DuckDB and then just run with it and, and do analysis on it. That, that we see a lot and we want to support that more. Yeah. Okay, cool. Awesome. Yeah. I'm looking forward to using the, uh, the UC catalog extension. Yeah. Yeah. That's, uh, that's, that's, that's pretty, that's pretty experimental though. So you were warned. Um, and then another just like request, um, you know, and I know this stuff takes time, it's not priority, but like the way

our users today are accustomed to seeing package documentation, it's very much in the form of like, here is a vignette that if you've never used this package or tool, like by the end of the vignette, you'll be able to at least replicate a lot of it. And I feel like DuckDB and Arrow, Polaris, like these other Polars, even like these other frameworks are sort of written with a software engineering mindset where it's like, here's my documentation. And the documentation is like, here's a function name. Oh, and here's the argument names underneath this function. Like it's very topical, right? So, um, again, like low priority, I love DuckDB. I love what you guys are doing, but just like more robust documentation, um, would be awesome.

Yeah, I agree. And there's a shout out to Gabor, who is our dev rel, who is really spending a ton of time on documentation. Um, one thing that we sometimes run into is that the surface is just so gigantic that it seems like kind of pointless to show you three examples of SQL, right? Like, because there's just this, it's like trying to teach you programming in the vignette. It's not entirely the format for it, right? So sometimes we say, okay, go, go learn SQL somewhere else and then come back. Um, or at least because it's, it's difficult, but it's a point well taken. Um, absolutely that, uh, we could, we could have something a bit more on, on the, on, on sort of the getting started side there. Yeah, absolutely.

Thank you. Javier, I see Seb just, uh, volunteered you to help update documentation.

I know, I know. I need to do more of this, honestly. Uh, like, I don't know. I just feel like by the end of the work day, I'm so spent and many others in my shoes are like so spent that we don't want to contribute back to the open source community, despite us, like literally my bread and butter is open source at work. So yeah, it's all my 2025 goals. I'll help you out.

Thanks, man. Do you want to jump in next with your question?

Uh, yeah. So, uh, Hey, honest, nice to, uh, nice to see you. Um, my question was kind of around, and I'm not, I haven't contributed to open source projects, so this may be even just a general question, but especially since DuckDB is, you know, increasingly used everywhere. I wonder how, um, how security is evaluated for open source projects. Like, you know, obviously companies where we work with clinical data or anything like that, it's a, it's a huge possible risk. And so I'd be curious to see what take from the open source side.

Yeah, I think this is an excellent question. And I mean, it's not, we do get some sort of improvement in the general situation, but not having client server because there's no server to run. There's no sort of port that can be accidentally left open or something like that, right? Like there is no, like this whole, this whole traditional security problem of, oh, you're, you know, you can attack this protocol over the, you know, with, with some, some crafted numbers or something that we don't have. But there are, um, there are things that, that, uh, we are, we do have a, we do have a website on our documentation about securing DuckDB because there are aspects that, that can be surprising. Like if you're, for example, you're running it somewhere and you're just opening up the SQL impromptu to the world, expecting nothing bad will happen. Well, uh, bad things can happen because the SQL prompt is extremely powerful. It would be like opening up an R shell to the world. You can do it, but you have to be a bit careful, right? So we have a website up that describes that.

Um, the biggest security sort of, um, concerns we do that we have is around distribution of binaries, right? So we, um, we make, we build, we build the binaries in our CI and, um, yeah, we distribute them and CRAN builds binaries and, you know, um, so what we do, we have to do very, we have to be very careful that nobody manages to implant, um, you know, malicious code into the stuff that we distribute. Right. So that, that is, I think the thing I am mostly concerned about and that has that, that kind of, that comes down to like, you know, nailing down your CI, um, having, you know, signatures on things, having, having sort of, uh, hashes being checked regularly that to make sure that there's a lot of sort of stuff in the background that we have to do that, that doesn't really end up that it's not visible in the product, but there are, we are definitely aware of it. Um, one thing sometimes that's a bit annoying is when some open source user shows up and, uh, asks me to fill their 40 question security questionnaire, uh, that does happen. It has happened with NASA even that, uh, you know, they say, oh, we can only use your thing when you fill this thing. And I'm like, what I'm, what am I supposed to do? I should fill your questionnaire. I don't, I don't understand, but I mean, some, but if it's somehow reasonable, we try to help obviously, because we know that it helps the users, but, but it is, it is a, is definitely something that, uh, I understand people's concerns about. Uh, we do offer, you know, sorry for the plug. We do offer commercial support contracts for people who are, who want extra assurances. Um, but we try our, you know, our, our, our, our best to, uh, make sure that the distribution is intact and, you know, not nobody, nobody that pulls a, uh, what's this X that or whatever it was on us.

Appreciate it. Thank you. Um, and I see a question here in the chat from Sanjay. Did you want to jump in and ask? It has an asterisk, but if you're available to ask.

Oh, hi, this is Sanjay. I had a question around production grade database use. So we have, uh, IOT, uh, sensors and for thousands of them, which keep sending data and we, we pump them through MQTT, we pump them into a MariaDB database, which is working fine. Only thing is when the database grows in size, the querying gets slow. So is, is DuckDB going to help there where it can write consist continuously every second into a database and then also allow read simultaneously?

Yeah. Um, so the querying thing, yes, I think DuckDB is for complex queries, something on the order of a hundred or a thousand times faster. So that's, that's really not compared to my SQL. So that's, that's really not the problem. The problem or the challenge comes indeed with the ingestion. If you have, so, so DuckDB is a, uses a columnar storage layout, which means that, you know, the tables are organized in columnar way. Um, and that means that uh, uh, doing a lot of small appends can be problematic. And that's something that, uh, but that have, you have to sort of try out to see if the, the right speed can keep up with whatever you're sending in it. So 10, 10 tends to be the case that row layouts like, uh, my SQL or Postgres will generally be better at appending quickly, but, um, the querying speed is definitely going to be better.

Yeah, but that's half problem solved only unless we append it. I mean, it's probably going to work fine. I'm just warning you there's, if you're looking for transactions per second or something like that, then the row-based guys are probably going to win.

Yeah. Yeah. All right. Thank you.

DuckDB's early struggles for acceptance

Libby, do you want to call on the next one? It's hard to pick the final questions when we get down to these few. It is. I was just thinking five minutes left. Oh my gosh. Um, there's a great question on Slido, which is when you first came up with DuckDB, did you struggle to get acceptance from the data industry? And if you did, how did you get over it?

Um, so that, yeah, great question. Um, so we were initially, uh, ridiculed for not having a distributed system and it was our strong belief and, you know, Hill, we were willing to die on that. You don't actually need distributed systems in order to query sort of significant amounts of data. And, um, I did, I was like, you know, I mean, I didn't really worry me so much because I thought, great, this is, you know, I'll just, I'll just, you know, if that takes 10 years for the world to acknowledge distance, fine. It actually didn't take all that long. Um, I can't, I was, I was ready to sort of be, be, um, be ridiculed for, for much longer than, than we actually were. And I think the, the crazy growth we see with DuckDB and, uh, you know, the, the blog posts like big data is dead and stuff like that have really changed the data industry in my, in my opinion. And, and so we are no longer ridiculed. I'm happy to report even, uh, we were working with, with some of the, uh, big, big, big, big data companies.

You don't actually need distributed systems in order to query sort of significant amounts of data.

Um, and, and so, um, yeah, I think that was, that was kind of the, um, that was a big thing. And of course we are from Europe. Uh, the data industry tends to ignore things that are not made in, in, in, uh, you know, in the, in the Bay area. Um, that was also something I was a bit concerned about in the beginning was like, yeah, we can be as good as we want, but is it gonna actually arrive, um, you know, in the U S uh, and, and that has also worked out. So that was also something I was like, maybe a little bit concerned. Thank you.

Data heroes

So earlier this week, we had a, a customer meeting with, with Wes and Hadley and somebody had asked them both, who are their data heroes? And Wes McKinney pointed to you. And so I wanted to ask you that question too. Who are your data heroes?

Um, well, okay. Uh, I think that Hadley is definitely one of my heroes. Uh, so I, one of the reasons I was extremely nervous about talking at the Fawcett Conf this year was because I, it's just, I have talked in front of a hundred thousand people, but somehow in front of Hadley was making me extremely nervous. So that, that was, uh, that was interesting to see also about myself. And I think it has to do with, um, yeah, just, just, uh, you know, people that have, you know, managed, managed single-handedly to turn the industry around, um, that, that, uh, yeah. Who are my heroes? Yeah. I think, I think I have to, have to name Hadley, uh, and, uh, of course Wes, but I cannot say him. This would be circular. So I would, uh, I would have to, I would have to think about that a bit more, but, um, no, I think, I think I really, um, in my field in, in data management systems, it's really, if you can show that people use your thing, then you have kind of won the argument. And I think both, both, uh, Hadley and, uh, and Wes have shown this in, in, in a great sort of way. And so for me also people like, um, you know, stone breaker, the, the Mike stone breaker, the Turing Award winner, and one of the people behind Postgres is, is somebody who I look up to, um, because just of the immense amount of impact they've had. Um, so, so yeah.

Closing remarks and career advice

I love that. Thank you. I just put this up as a post on LinkedIn earlier today, if anybody wants to go and add their own too. Hannes, we have one minute left here and I'm wondering, was there anything you had to share with us today, whether that's, well, anything top of mind for you or a meaningful piece of career advice or anything else you wanted to share with us?

Um, yeah, I think, um, first of all, I mean, I'm sorry, I couldn't answer all the questions. Feel free to email me. Um, it's Hannes at duckdb.org. It's very easy. Uh, if you have any sort of, uh, direct questions about, uh, to, to me, um, I'm not kind of, can't promise I will, um, I will immediately answer, but career advice, I think the, I think going for impact, uh, I think that's just, just, that's what I mentioned earlier with looking at Reddit. If you, if you look at what will have the biggest sort of impact for the most people, that's, that's something that, um, I think is worthwhile. Um, and, uh, yeah.

Thank you so much. Well, Hannes, I know we are the last call holding you off from your holiday vacation right now, but thank you so much for joining us to close out a year of amazing data science Hangouts and really appreciate you taking the time and thank you all who are here and make this space what it is and join us week after week. Uh, we will be on a bit of a holiday break until January 9th, where we'll be joined by Michael Chow and Rich Iannone at Posit who work on the GT and Great Tables packages. But I wish you all a wonderful holiday season with your friends and family and a happy new year. Thank you all. Thanks everyone. Bye everybody. Bye.

I love seeing the messages in the chat, so I'm not ending it yet, but thank you all so much. There might be a survey that pops up at the end. If there's any feedback or suggestions or things you want to share with us. Thank you all so much. Bye.