---
title: "DuckLake Time Travel & Schema Evolution Explained | MotherDuck"
description: "Learn how DuckLake time travel, schema evolution, and the change feed work: query any snapshot, undo bad writes, and evolve tables without rewrites."
canonical: "https://motherduck.com/videos/ducklake-time-travel-schema-evolution/"
---

[BACK TO VIDEOS](/videos/)

[Stream](/videos/?category=Stream#videos-and-webinars-library)[SQL](/videos/?category=SQL#videos-and-webinars-library)[Ecosystem](/videos/?category=Ecosystem#videos-and-webinars-library)

# Time Travel & Schema Evolution: DuckLake Handles Change

2026/09/03

> **TL;DR:** Alex Monahan, co-author of the O'Reilly DuckLake book, and Hoyt Emerson of Early Signal work through chapter 4 live: time travel with the `AT` clause, what snapshots actually cost, schema evolution without rewriting data, and the row-level change feed.

## Time travel is an undo button for data

Any DuckLake table accepts an `AT` clause in the `FROM`: pass a timestamp or a snapshot ID and you're querying the table as it was. It works per table, so you can anti-join yesterday's table against today's to see what changed, or you can attach the entire lake as of a moment in time. Nothing moves on disk — the catalog just filters to the files that existed then. That's one database query, instead of walking snapshot history file by file on object storage the way older lakehouse formats do.

## What snapshots cost

A snapshot is a commit, and DuckLake can keep far more of them than Iceberg because metadata lives in a database instead of separate files. The real cost is retained data: appends make time travel essentially free, updates cost a copy of what changed, and full refreshes are the case to watch — hourly refreshes kept for a week means 168 copies. The fix is tiered retention: keep every transaction for a few days, daily snapshots for a week, weekly beyond that. Expiration is a SQL query on a schedule, and it's deliberately two-step — expire, then delete about a week later, so you keep an undo window.

## Schema evolution without rewrites

Add, rename, and drop columns, or widen types (an `INTEGER` that overflows becomes a `BIGINT`) without rewriting any existing parquet. Changes must be lossless — no downsizing — which is the compromise that keeps old files readable. Time travel keeps working across schema changes, and you can attach commit messages to snapshots so an audit trail comes free.

## The change feed

The data change feed shows every row inserted, updated, or deleted between any two snapshots, including before-and-after values. That's enough to run change data capture off a DuckLake into downstream systems, or to keep a local copy fresh by pulling only what changed. A related trick from the Q&A: copy just the catalog into a local DuckDB file and query the lake read-only from your laptop, leaving the data files where they live.

## Keep going

Chapter 4 and the rest of [DuckLake: The Definitive Guide](/lp/ducklake-lakehouse-table-format-book-full/) are free to download, with new chapters delivered as they're written. New to DuckLake? Start with [getting started with the DuckLake table format](/blog/getting-started-ducklake-table-format/) — it's three commands to try locally.

TABLE OF CONTENTS

- Time travel is an undo button for data
- What snapshots cost
- Schema evolution without rewrites
- The change feed
- Keep going

Start using MotherDuck now!

[Try 7 Days Free](https://auth.motherduck.com/authorize?app_source=web&response_type=code&client_id=bza3KWQpxRAFlTlRFXUo29AOg9xD7zcp&redirect_uri=https%3A%2F%2Fapp.motherduck.com%2F&state=STATE&auth_flow=signup&screen_hint=signup&ext-ph_distinct_id=f41b6637-2c22-4720-a13b-cb48f42018ba)

## Transcript

Copy

0:00Well, hello, everybody, and welcome. Thank you for joining us today for another DuckLake webinar. We're talking all about [time travel](https://motherduck.com/docs/key-tasks/database-operations/time-travel/) and [schema evolution](https://motherduck.com/docs/key-tasks/database-operations/schema-evolution/). So welcome. Glad to have you. As you think of questions, we'd love to hear them in the side channel. And just say, hey, love to hear where folks are dialing in from. So pop in there, see who else is in your neighborhood. So first, I'd like to welcome Hoyt Emerson. Hoyt, do you want to say hello?

0:26Hey, Alex. Hey, everybody. Look at me in front of a brick wall ready to talk about DuckLake. Let's do this.

0:35Fantastic. Well, all right. Well, hi, I'm Alex. I work here at Mother Duck. I even have the shirt on today. We're in the uniform. And I'm also a co-author on the upcoming O'Reilly book about DuckLake. And so really excited to get into the details with you today. I think this is where we start to get into the details of what it feels like to actually use in production.

0:57And Hoyt's done a lot of work on what does it feel like to use it in production. And super excited for you on that side today. All right. Welcome, folks, from all over. And I do really mean it. As you have questions, put them in the chat. Last webinar, we had so many questions. We actually did a follow up blog post and answered them all.

1:19So even if there's a million questions, you don't think we're going to get yours. We will get yours. And that also means that there's no question too big or too small. We'll still get to it and we'll talk about it. So don't hesitate. I deserve it. I ask a million questions whenever I do one of these.

1:35So I want to start with a couple of things just to introduce folks if you're not as familiar with DuckLake or with Mother Duck. So what is DuckLake and why are we so excited about it? I'm definitely excited about it and Hoyt's done a lot with it as well.

1:49So at a super high level, DuckLake is an open table lake house format. That's a pretty chewy phrase. But at the end of the day, the goal is to store your data in parquet files on object storage in a very inexpensive way, but not sacrifice what makes a database better than managing files on a file system.

2:10Basically, you get the good of a database with the cost structure and the scalability of object storage. This is a look at what it takes to run DuckLake on your laptop. It is three commands. There's one command before this, which is install DuckDB.

2:25And then you can be up and running and playing locally. Today we're talking about maybe a few steps down that road after you've got it up in maybe on the Claude, maybe so that way we can talk about the production side. But testing it is super easy.

2:40You can even do it on the webinar right now. So that would be my challenge is if you haven't played with it yet, play with it on the side. Go ahead and ask Claude. Hey, Claude, set up DuckLake and teach me a little bit about it. So that would be my first challenge.

2:58I'll talk a little bit about just the nuts and bolts and then I want to hear a little bit about what what you're excited about with DuckLake. So I truly believe that DuckLake is is the future of open table formats. And why do I believe that? It has some of the core fundamentals. It's partitioned. You can ingest data very, very quickly.

3:18You can have your own blob storage where you get to own that data for basically forever. [Parquet files](https://motherduck.com/docs/integrations/file-formats/) will basically last until until the end of times at this point. So you'll have it there forever. But you also get this incredible low latency experience. And this is actually a big deal because today lake houses are archives.

3:40You can only push to it every once in a while. You only some applications can handle the latency and use them. You have to put a lot of caches on top. You have to have a pre-calculated thing you put on top of your lake house to actually run your data apps on or to run your BI on.

3:58But DuckLake is the first technology where your single source of truth doesn't need air quotes anymore. It can actually be your single source of truth. And that's that's something that really speaks to me in particular. So Hoyt, I'll turn it to you. What are some of the things you're most excited about DuckLake wise?

4:14Oh, man. Well, you know, first of all, it's just the the introduction of Lake House. It's the most streamlined way to do it. And so this is what got me completely ducked. You'd be pilled kind of last year when DuckLake came out was I went to the quick start. I ran those four lines you were saying. And I said, Whoa, I'm I'm engaging in Lake House architecture just like that.

4:38The speed to get things up, including, by the way, a production grade DuckLake. It's actually scary. So as I write about DuckLake more and I get deeper into it, I have to keep reminding myself, no, I am doing this right. It's supposed to be this easy. And, you know, that's just at the top of what I love about DuckLake.

4:57You bet. Well, thank you for that. I also agree. That's that's my favorite part. Those couple commands. I really enjoy that.

5:28I think this slide also really speaks to that simplicity and you really feel it when you play with it and try it out. So DuckLake is the simplest Lake House and it's not close. So on the left is [Iceberg](https://motherduck.com/docs/integrations/file-formats/apache-iceberg/). This is their architecture diagram on the right is my best attempt to mirror it. But for DuckLake.

5:48In Iceberg, you have a couple of layers. You'll notice the bottom layer is the same on both parquet files at the end of the day. And DuckLake actually uses it's an Iceberg compatible parquet format. So if you have your data in Iceberg, it's one [metadata](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/introduction/) copy to copy it over into to DuckLake. So if you have Iceberg and you want to experiment with DuckLake, that's also a five minute experiment you can do as well, which is which is pretty unique in terms of transitioning between Lake House formats.

6:18And on top of that, Iceberg has a lot of layers in this layer cake. They have three layers of files for metadata and those live on object storage. And when we think of object storage, we think bulletproof, infinite, you know, bottomless pit of where I can put data and not worry about it for it'll be inexpensive.

6:36But one key thing is it's great for throughput, but it is not good at latency. And that actually matters a lot because each time you have to go read and these reads have to be in series, you get a big latency penalty and it takes, you know, 100 milliseconds, 100 milliseconds, 100 milliseconds to even decide where your parquet files are.

6:59And you'll notice up at the top of Iceberg, there's this catalog. There's a lot of catalogs around there. There's a lot of fragmentation in the ecosystem. Each major player has their own catalog that they prefer. So it's it's not that unified, really. But underneath that catalog, it's a web service. It's the REST endpoint. But underneath that is actually a database.

7:20And the DuckLabs folks looked at that and said, you already have a database. What if we use that for a lot more? And so that's what we've got on the right hand side with DuckLake. We are taking the metadata and the catalog and putting them both in a database.

7:34That means you could do one lightning fast database query and then you already know all the parquet files you want to read and you can go read them in parallel. So it's dramatically lower latency. And it really means that your single source of truth doesn't have this big penalty of feeling slow, feeling like an archive that you have to access.

7:56Said another way, yes, that diagram looks very different. But if you think about it, it's one architectural decision. And DuckLake is built out of industry standard pieces. Parquet files and object storage, a database like [Postgres](https://motherduck.com/docs/integrations/databases/bigquery/) or MotherDuck. And that means that it's not that it's at once revolutionary, but also not not revolutionary where it's a big risk.

8:17Right. It's a small step that gives you a big performance win. And really, it's a design decision. You'll see that at the top, Iceberg and Delta use object storage for a lot of things, metadata and storage. But if you look at DuckLake, it minimizes object storage to just what it's really good at. Huge amounts of data for huge throughput.

8:37And then it uses a relational database, which is perfect for tons of tiny operations for both your catalog and your metadata. That's what I had come to show the architecture of DuckLake. I'll briefly introduce you to MotherDuck as well. We like to call ourselves infrastructure for answers because DuckLake is a technology, but it's a means to an end.

8:57We don't do technology for technology sake. We do it to get answers about our business. We can run it better so we can troubleshoot. Maybe we're looking at logs. There's a purpose behind it. So with MotherDuck, we really looked at the existing data warehouse landscape and said, wow, this is really, really complicated and really expensive.

9:15When you run one query, it gets sent to hundreds, sometimes thousands of machines. They have to communicate with each other back and forth and take a few hops to get back to you to finally get an answer. And that means there's this high overhead, just like we talked about with lake houses. The traditional lake house has this high overhead on every query. And we saw that as well at MotherDuck and said, what can we do about it?

9:38What we realized is that hardware is actually huge now. And this is true in how you can use DuckLake as well. You can get a single node from Amazon with terabytes of RAM. And it is a lot easier to spin up one machine than it is to manage a fleet of tiny ones and make sure they talk to each other perfectly right.

9:57We also found that most people don't query all their data all the time. And that's for good reason. If you query a petabyte, there's no way to query a petabyte both fast and cheaply. You can't have both. We're built on top of DuckDB. We love DuckDB. We're incredibly happy with our architectural choice there. DuckLake, that's the most popular engine to use with DuckLake is DuckDB.

10:21There are also emerging other engines you can use like Spark and Data Fusion. So it is, and Trino as well, actually. So it's becoming a multi-engine lake house, which is really fantastic. We love DuckDB because it takes a single node and pushes it to the absolute limit, much beyond what other systems can do.

10:38And finally, what do we add from the MotherDuck perspective there? What's our architecture? We are serverless. What that means is that we only spin up when you need us, but we spin up in well under a second. And so you don't have to worry about infrastructure staying up all the time. It's just up when you need it. And we have a lot of individual DuckDBs. That's why we're a MotherDuck. We manage a flock of DuckDBs, much the way we can manage a flock of DuckLakes.

11:03So what that means is each user or each agent gets their own little sandbox, and they can't muck around with anyone else's sandbox. So the CEO's dashboard loads just as fast, even if your agent is off doing who knows what, which is really convenient. And then we also have a lot of agent-native tools. That's really where we get to this infrastructure for answers idea, because it's a means to an end.

11:27You have a question that you want to get answered, and we have several different AI technologies that make that much, much faster and much, much easier, where your whole company can do this. Our sales team is AI-pilled at MotherDuck, and I don't think that's uncommon anymore. I think there's a ton of orgs all across every company that become AI-pilled, and they can get their answers a lot faster.

11:50So that was what I had for background. Oh, sorry, last bonus slide. We're also really fast, and you think, okay, benchmarks always distrust benchmarks. This is one that's not by us. It's by our competitor. But the key is you have to test it on your own workload and see if it's real for you. But if you look at this industry standard benchmark, we do really well. And in databases, it's kind of a free lunch.

12:13If you're faster, you are going to be both faster and cheaper. And you can choose on that spectrum how you want to kind of spend that capability. But we think we've got a lot of that to spare. All right, that is what I had to cover on the slides. We get to talk a little bit more about what we came here to talk about, which is time travel and schema evolution. So when I first think about time travel, I like to think about an undo button for data.

12:38So Hoyt, I'm curious if you've ever reached for this kind of tool in your toolbox in the past. So time travel is one of those things I don't really have a horror story or a war story for, to be honest. Time travel is one of the things I introduced when I first tried DuckLake out last year. I'd always heard of it.

12:57But, you know, the way that I worked in my data science analytics background and eventually data engineering as well, was I just focused on these pipelines and making sure that they were always right. And when something broke, I just sort of had to deal with it. I kind of shrugged my shoulders. I apologized to the downstream stakeholders and I said, we'll be better in a few days. And that's kind of what happened.

13:22So I think as I've been working with DuckLake more, I've been testing that more, the time travel. And I think to myself, wow, this would have saved me at least half an inch of my hairline a couple of years ago with certain bad scenarios. And so, no, unfortunately, I haven't been able to use it yet.

13:43But for those who may be into the same kind of situations that I ran into, it's here and you're going to be glad it's here. Fantastic. I love it. Well, keep your questions coming, everybody. We'll get to those as we go. We'll do a little bit on the fly and then we'll do some of them at the end. So I wanted to pop one in. I think it's a great initial question about time travel. So, AL, welcome.

14:07Does time travel mean that data is not actually deleted? And that's a great question. DuckLake actually lets you choose here. If you would like to retain data permanently, you can.

14:20If you would like to compact it and reduce your storage space and also potentially remove data that you want deleted for GDPR or other compliance reasons, you absolutely can do that as well. And it's a choice that you have. Basically, you think about how long you want your time travel window to be.

14:41And then you set up maintenance scripts to then kind of compact things and beyond that time window. That's a common pattern. There's also some really fun advanced patterns coming in a subsequent chapter of the book. There's some fancy things you can do here where you could do something like, let's say, for the last three or four days, covering a long weekend. I want every single transaction in the history saved.

15:05Everything. And then after that, I want a daily snapshot of what it looked like at the end of the day. And then more than a week ago, I want it end of a week. And then maybe more than a month ago, I just want it just at the end of the month. And so that way you can actually get less granular with how you store your data, more compact the further back in time. You don't give up time travel at all.

15:29You just change the level of detail of how specific you can be about going back in the past. But you can still go back years if you want, which I think is really powerful. Hoyt, anything you would add to this question? Well, I was it was really interesting the way I realized that when you do delete in DuckLake, like to your point, there are some you have some options.

15:52But the but sort of the default is it doesn't actually delete the data. It just stores a deletion information in the metadata table in your Postgres data catalog. And so I thought that was I thought that was really cool. I was like having to wrap my head around, you know, actually, the data is always there. And then that makes understanding time travel a lot. I think it kind of opens up the possibilities of understanding. Oh, so that is always going to be there.

16:17It's just that whatever I decide I want to work on, that's being stored in the data in the metadata catalog. And that's going to be kind of my first class citizen. But I can always go back to that old stuff if I want to, if I want to leave it there. And that was really powerful to me because I could see a bunch of scenarios, namely what I use. A lot of my old IC work was I was constantly recreating tables.

16:41And things can go wrong when you're doing that. And to understand that if I could do a big right with a table and accidentally say, we'll say delete in a normal, say, data warehouse situation that I have the ability to go back to the old version of that, it wasn't just deleted. Yeah, there's just a lot of really cool details about Lake houses. And then really specifically the way that DuckLake handles it.

17:05You just keep discovering all these really, I think, very elegant solutions to these problems. You bet. I appreciate it. We'll get to one more question here and then I promise we'll get to the other ones a little bit later on. But keep them coming. We'll intersperse them in as we go. So welcome, Troy. Good to have you here. Are you finding use cases for snapshots combined with the time traveling?

17:27So in DuckLake, there's a lot of different kind of terms for each of these things. A snapshot in DuckLake is really the same thing as a as a commit. When I insert data and I end my transaction. So by default, if you just do an insert, it puts it in a transaction for you. But you can also batch them together if you want to do a few things at the same time and do it all at once.

17:49You could put them in one transaction and each of those is a snapshot. In the traditional Lake houses, you have to be very careful about how many snapshots you save. Because it's very difficult to compact across snapshots in iceberg. Each snapshot has to be in its own separate file. DuckLake does not have that restriction. So you can actually keep a lot more snapshots in DuckLake, first of all.

18:14So the first thing is snapshots are much more usable than in the other Lake house formats. The other use case I really see for for snapshots is if you want to record data at a very specific point in time. So we talked a little bit about like the end of month, end of week type of style of use case. I also think it's really useful for when we reported to the board of directors. When I produced my data science analysis output and I published it.

18:39When I finished my experiment and I wrote the paper about it. All these various checkpoint times where you want to be able to go back to exactly how something was at that time. That's what comes to mind first for me for this kind of snapshot use case. And so you guys are on a webinar about time travel and snapshots. If you have other ones that you'd recommend for Troy, pop into the comments.

19:01I'd love to hear about cases where you were really glad you snapshot it or where you would like to do it next time. All right. Well, thank you for that. Well, let me pull up a little bit. We're going to take a look at some of the examples in the actual DuckLake book. And so this is available for download. So you can pop in your email address.

19:23We'll send it to your inbox and we'll even send it to you each time that we add more to it. So let me pop this over here and I'm going to move it over just a little bit. Great. Do that. So. This is Chapter four. Chapter four gets the fun title of advanced features and capabilities. So you guys are buckled in for the advanced features. So thank you for that.

19:47Really, the key that we want to talk about is there's a few pieces that you will want to consistently use, but are still not the things you're going to do in your hello world example. And so the first thing is really this time travel capability. So time travel has a couple of use cases. We talked about some you can audit changes to a table, which is very helpful if you want to know how that came to be.

20:10You can restore a table to a previous state. That's the first thing that comes to mind for me. And really, a lot of the times you're going to want to do that is if there's an oops moment where you really want to undo something dramatic. And here we'll get our first look at the actual syntax of what this looks like. So I'll zoom in just a little bit to make it a little bit easier. Well, that's a little too far.

20:30You can actually in the from clause of any table, that's a DuckLike table. You can use this at. Clause to add into your from clause and you can pick when you want it to be at. And so this timestamp here is the parameters and saying, hey, I want it to be at this particular timestamp.

20:50You could do it by ID number if you if you had that looked up, but you can just say, hey, what did this look like yesterday? And this could be as granular as you want. And what I really love about this is that it's table by table. And so that's really helpful where I can do something like compare yesterday's table to today's table. And I can do, you know, an anti join on it and say, what changed?

21:13I could do something like, you know, compare a two days ago table from one and then like a yesterday's table from another and see if maybe they're incompatible if you looked across time. So there's a lot of ways you can really mix and match these things. That's really, really helpful because it's at that per table granularity, which is really helpful.

21:36So that's our first look at the syntax there. You can also do it in bulk. And so this is sort of the alternative there. If you don't want to go table by table, if you just want to say wind back the clock all the way, you can attach an entire DuckLake as of a certain time. So you have both options available, both the granular but also the high level.

21:57I think that's part of the DuckLake architecture is we will give you both the controls. But the defaults are going to work pretty smooth out of the box and you're not going to have to reach for those advanced controls that much.

22:09So for those kind of oops moments for that sort of, oh, what did it look like when I presented to the board, you know, a month ago, winding back the whole DuckLake may actually be your simplest path to doing that. And again, when you wind back the clock, you're not actually moving any data around at all.

22:27All you're doing is you're telling the catalog, hey, only show me the files that existed as of this time. Don't show me any of the newer files that existed at that time. So that's why those files still have to exist. We can't deleted them to go back and look at them, but there's no files moving around.

22:44This is actually very fast operation. In some other lake houses, you actually have to kind of traverse the history of the catalog on that object storage. And it's very slow to actually go check every single snapshot back through time and kind of wind the clock back file by file. For us, it's one Postgres query. And we put a different where clause on it.

23:05We said, hey, where the snapshot time is a different thing. And guess what? Postgres is really freaking good at doing where clauses like no big deal. I'll turn it to you. I like your gesturing, gesturing there. It's ridiculous. It's ridiculous. I mean, we're, you know, other lake houses that shall not be named.

23:25But like when you when you even think about the amount of work and traversal that has to happen over this, essentially this giant file management system that you have, you know, you have used now as your database. It's just shocking. I think how performant the Postgres, you know, we'll say Postgres. But anyways, the database as the catalog perspective is for DuckLake.

23:46And, you know, there was something I wanted to add. I forgot about Alex, which is that you can add [commit messages](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-sql/) to your snapshot. So specifically, if you run transactions for something really nice asset, you know, you know, acid type transaction that you know is going to go through and be complete. You can add commit messages which show up in the snapshot history. So if you suddenly have something weird happen, you can go over to your snapshots.

24:10So you can be like, what did what was the previous, you know, what was the answer look like? What's the message around that? And it might be that. I don't know. Maybe your agent did something weird. Maybe you're Aaron, you know, data engineer did something weird. But that's really, really, really powerful stuff. And I think that's one of those things you can set up on autopilot, too.

24:29So you're if you put that in your cloud and say every time you put data in DuckLake, put in a commit message for what you're working on and make it as verbose as you want. Claude, feel free. Burn those tokens. You know, you could really get a lot of detail about what was going on at the time. You can also do it in every pipeline. So nowadays, I think most pipelines are actually being written by A. I. as well.

24:51At least with A. I. assistance. I think you're going to want to do some validation on all these things. That's that's the key always is you want to validate the output. But that Claude.nd file would also work, too. Right. When you build that pipeline with whatever, you know, orchestrator or system you're using there, part of that will be to include a commit message. And to pull that back out is as simple as saying select star from snapshots.

25:14Or because inductively, we again like to remove the cruft you don't need. You don't even need select star. You could just say from snapshots. We assume you mean select star. You have a hot key for that, which I, you know, in the past, I had a hot key for select star from OK. Don't need that hot key anymore, which is which is quite pleasant. But once you do those snapshots, that is actually a view.

25:36So DuckLake is, you know, joining a couple of tables for you. You can see the actual query that's happening here. It's just looking at your snapshots and your snapshot changes and telling you what's changed in your in your database. I think we've got a couple of questions, so maybe we'll look at a couple of questions and then we'll jump a little bit more into a bit more of the content.

25:57So the first one is here. Some feedback from Nick. Thank you, Nick. Snapshots are neat for debugging ETL. I love it. You know, I thought this pipeline was going to add, you know, a thousand, you know, update a thousand rows. It updated a hundred thousand rows. Like what is up with my joint condition?

26:16You know, like I expected that to be small, but that query just took, you know, 30 minutes to run. What was it doing? A lot of really good use cases in ETL. So thank you for that. Someone is not giving himself enough credit. They're signing in as not advanced, but you're here for the advanced chapter.

26:37So maybe you'll be advanced by the time we're done. This section is perfect for me. Great. Bring your questions. All right. And so Chris asks, what is the cost of saving deltas of snapshots? So in this case, the real cost of time travel is how much data you retain to be able to go back in time.

26:59And that's basically your raw data files are going to be that cost. The catalog is so efficient in DuckLake that that's actually not really the main bottleneck. In other lake houses, the metadata is actually mostly the bottleneck for managing how many snapshots you have. But we've eliminated that with DuckLake. So what's the real cost? The cost is how many copies of your data you want to keep.

27:21So if you do a full refresh of your data every hour. And you want time travel back to that hour level granularity for the last week. That's seven times 24. What is that? 168 copies of your data.

27:38Right. That's a lot. Now, that will be totally worth it if you come back from vacation and you need to rewind the clock. So it's not without its benefits, but you can start to see how maybe at that week time frame, having something hourly might not be as necessary.

27:55Maybe you could be off by, you know, four or six hours at that time frame and and get just the same benefit, but slash your costs by six X. So that's where you can start to play with that kind of granularity of your time travel. And I would recommend doing something where you get less granular over time. But there's an important note that is sort of the worst case cost when you're doing a full refresh.

28:17But when you're at scale in a lake house, you're mostly doing incremental changes. You're adding data, you're updating data, you're inserting data. And at that point, it actually doesn't cost you anything. All you did was add rows. And if it's always just an incremental change, you need that data anyway, because it's now the latest and greatest version of your table.

28:39So if you're inserting, if you're appending. Time travel is essentially free. If you're updating, it's not free. You pay the same penalty, but it's on a much smaller subset because you're probably not updating the whole data set every time. So you're doing full refreshes. Be mindful. If you're doing appends, let it ride.

29:00And if you're doing updates, see how big your updates are and then you'll kind of make a decision in the middle. Anything you would add to the cost side? Feel free. I haven't seen the cost is low. So until you suddenly feel a pain point.

29:22Yeah, I like to know how hardcore that has to go until you're like, oh, it's too expensive. I mean, especially just the fact that you're working with Postgres. Like, I just think the way that the metadata tables work. Oh, one note. And it's in the chapter there, but it's very convenient to create sort of these observability tables with your metadata tables like very easily.

29:43And you can just go have your agent whip something up, some kind of pipeline and then just alert some mobile app you've had to create on your phone, too. So while you're drinking a pina colada down in Costa Rica, you can at least get your or you're taking the family to Disneyland. You know what I'm talking about, Alex? You can get at least a little note on your app that everything's good.

30:03So the sky's the limit. And DuckLake's architecture just just kind of it's it's they're offering you everything and it's all kind of normalized. You're ready to join. And it's really about like, hey, how do you want to use your imagination in this playground of information? And how do you want to use it to keep track of what's going on? I totally agree with that. I love that suggestion, because in the catalog, it stores how big your data files are.

30:26And that's how many gigs they are. And you just multiply that by your S3 costs, somewhere around 20 bucks a terabyte a month. And you know exactly how much your DuckLake is costing you in storage with one SQL query. And you don't even have to go talk to AWS and go like, say, hey, let me use some random API to go tell me how much stuff costs. You just run a SQL query. And so you could easily have your data pipeline run to completion.

30:50And in the very end, run your your data quality checks. One of your data quality checks might be how much did my costs go up from this one query? And if it went up by double, you might think, huh, interesting. What exactly is happening here? So you have a lot of visibility and it's it's not a mystery. It's not a mystery to us or the agents.

31:09Agents are amazing at writing SQL. And so they could introspect this on your behalf super easily. So I appreciate that. Chris had another question, which we'll get to soon. So I'll flash this up as a preview and we'll get to it shortly. So what's the best way to deal with table deltas when the table schema changes? DuckLake actually tracks all the schema changes as well.

31:29So you could pull one table with a different set of columns than another and compare them. No sweat. So if you're adding and removing columns, DuckLake tracks all that as well. It'll work just as seamlessly as if it had stayed the same. All right. Ilya had a really good question. Can we specify the last timestamp before a given date?

31:51And in this case, this is where we're going to we're going to earn the advanced side here, right? This is where you're going to want to build your own query. And by that, I mean, you're going to want to ask Claude to build a query for you. And you're going to do that by you can put any custom SQL logic you want to pick out a snapshot ID.

32:13And what you can do then is you can put it in a duck to be variable. That could be variable could be, you know, snapshot I care about, you know, last board meeting. Right. And you can say, OK, where, you know, the snapshot is the max less than a given date. Which will give you that last timestamp before a given date and you'll get that snapshot ID.

32:36And then you just pass that right in. To the overall attached statement, if you want. Or you can pass it right in to the at statement and you can pass in a variable and you can say get variable. Last board meeting and you will suddenly see it exactly based on the custom logic that you built.

32:55And so. You can get as creative as you want and it's not that complicated because it's all SQL and the agent will take care of the hard stuff. All right. Very, very fun. Let's see. Read through a couple. I think we've got a couple of good questions here.

33:17I'm going to probably have us go into our next section and then we'll take some more general questions towards the end in our Q&A time slot. Let's look at this one real quick. So I'll probably toss this one to you first. So I'll read it out. So if I'm using data inlining with lots of small transactions, you get a lot of snapshots.

33:39How many is too many? So if you want to get that roll up that we're talking about, kind of day, month, hour. Do you need a custom snapshot expiration policy? So that last part I can take. Yeah, I had a code snippet for that for me recently. Nice. Nice. Yeah. I mean, first of all, I love inlining.

33:58I wrote in an entire article and companion video on this on my sub stack, the full data stack like that's over there. That's in partnership with Mother Duck as well. So I had the Alex Monahan sign off on it. But inlining is just it's just a it's a beautiful way to deal with these small files that you're talking about. So, yeah, if you're doing a transaction each time, maybe in a single row, I don't know how that looks.

34:22It's good to just note that the default for DuckLake is if an insert is at or below 10 rows, it's going to inline it. Anything above that, it's going to write a parquet file for it. So if you do have a lot of small row inserts. Yeah, you're going to build up a lot of snapshots. But no, I don't think too many snapshots is too many personally,

34:43because that's all being stored in Postgres. And so this is what Postgres does. This is what it's built for. This is its performance layer is to be able to deal with large amounts of information around a certain, you know, group grouping of some way and to make these very fast, you know, coordinated efforts based on what you need to know. So, no, I think you can just kind of go gung ho with it.

35:06I have I have inserted I've just been streaming data to DuckLake and I went through millions of rows inlined. And then, you know, we can get into some of the maintenance stuff here, which I'm sure you'll touch on, Alex. But I have yet to feel a real performance hit, even with a bunch of snapshots. It might depend on what you're trying to do with the snapshot.

35:27But I mean, when we think about what maybe, say, a lake house architecture that shall not be named, what it's actually doing to traverse through all the files and the additional files it builds on top of that to understand snapshots and versioning everything, you know, as opposed to just go to the Postgres, go to the table where it's being kept,

35:48maybe do a couple joins that you need to be able to get this information. It's just lightning quick. And at scale, you can probably keep that up for quite a long time. I haven't broke Postgres yet, to let you know. I have to sort of decide to flush this so that I can actually finish my article. And it just is an end that I just kept streaming to DuckLake.

36:08Nice. Well, excited that you play with that exact scenario, which is awesome. The mental model that I would add on top is the biggest table in DuckLake is going to be your column statistics table. Because for every commit, it has information about every file and every column in that file.

36:32So if you think about it, that's kind of the max number of rows you could end up with. So if you had a thousand snapshots, that's going to be, you know, and maybe you had mostly it's one big table, right? You're usually like an events table, something like that. You've got 10 or 100 columns. That's what? 10,000 or 100,000 rows? No sweat for Postgres, right?

36:54You want to multiply that by a couple other words of magnitude. Also, still no sweat for Postgres. So before you start culling, you know, and reducing your snapshots, try slapping an index on that one table. You'll probably keep on cooking. That's how easy this stuff is. It's really fun where you have a lot of those performance tricks where you can store a lot of snapshots.

37:14To get the roll of semantics part, there isn't really anything custom, I would say. It's just a SQL query that you run on a schedule. And that SQL query is going to use a SQL statement to get a list of snapshots you want to expire. And that can do it in any creative way, like expire everything except the last snapshot per week.

37:34We can write a SQL query, clod like one shot at it for me. It's pretty, pretty great. And then you just run that on a cron and it'll run that SQL. It'll decide what needs to expire and you'll expire it. Also, in keeping with the DuckLake ethos, it's a two step process to expire and then later delete. So you even have an undo option in between those two things. You can do things like expire it.

37:58Then you can run a data quality check and make sure you didn't expire more than you expected. And then you can delete it at a later point. And I usually recommend about a week delay of that deletion. Because if you think about it, that's a small fraction of your total data size. If you're going to build up two years of data in a DuckLake, an extra week to have the ability to undo a huge oops moment is super worth it.

38:23So I highly encourage waiting a week before you've really fully deleted something. Let's see. Welcome, Matt. We're glad to have you. You're always invited to every party.

38:45We just want more parties individually, so we're doing small parties. All right. And then I think most of these. There's one more kind of snapshot question and then the rest are kind of general. So we'll cover schema evolution and then we'll jump into Q&A. So Chris asked another good question.

39:07Is there the option to compact snapshots of big tables that change very little into the last snapshot plus the disk, maybe the second to last and then second or third to last, et cetera? So if I'm understanding what you're asking a little bit, I'll answer in two pieces. The first is all of these snapshot exploration settings can be set at the database level or the table level.

39:31So if you have a table that you're not that worried about time travel to it, you can make it really not very granular. Like this was my one time upload of my archive. Like, OK, remove all the other snapshots of that and just keep just the latest. So you can take take this as granular as you want it to go.

39:52In terms of the the diffs there. That's essentially what's happening under. it.

40:00So, for example, if you want to be able to time travel, you have to store both copies

40:21of that data. Again, if you're incrementally appending, you're not storing any copies. It's only if you fully replace your data that you're storing copies. And then compacting, you know, expiring your snapshots is when you decide, I don't want to save that copy anymore. So, thank you for the question. All right. Fantastic. Well, keep your questions coming. It'll be Q&A before long. I figured

40:44we could pivot a little bit into our schema evolution side. And if I could do it, I do pivot in all caps, you know, code block there. So, as a part of time travel, we know it's not just new rows that get added. It's a lot of new columns. And DuckLake's got a couple different options for how to handle that. The kind of core foundational one is schema

41:08evolution, which means you can do things like add a new column. You can change types of columns if it's compatible. So, say, for example, you want to start with an integer and then your data pipeline errors out because there's now a value that's bigger than an integer. Okay. What do I want to do? I can upgrade to a big int and I don't have to rewrite any

41:29of my data. Just new data will flow in as a big int. So, it's really convenient for data sizing. You can rename tables. You can rename columns. And you can drop them as well. And when you do this, but you store the snapshots we've been talking about, you can go back

41:48to the way things were and see that with time travel as well. So, that expiration is again when you choose whether or not you want to go back to it. There are a couple of details as well. When you change a data type, it has to be a lossless change. So, you can't like downsize a column because we can't guarantee that the files that you wrote, you know, two

42:10years ago will be safe to read. And the operation of changing a column type does not rewrite all of your data. And so, it has to be a lossless change. So, we make sure your data is safe that's still sitting there. And I think that's a good compromise because in a lake house, avoiding rewriting your whole lake house is a worthy goal. And that's exactly

42:31what this does. So, Scheme Evolution is super handy. It means that time travel works regardless of how much you mix around your tables. Really, whatever you do to it, you'll be able to wind back the clock just the same. So, Hoyt, anything you would add on the Scheme Evolution side?

42:50I think that is the Scheme Evolution from upstream activities that you don't have any control in are the, I think the ground zero of some of the most terrifying stories in data engineering, basically, and mine included. I'd buy it. I spent three years in a Series B down in Mountain View. I was going down to Mountain View every couple months to work

43:15with them, but I was modeling the entire thing for them, the whole architecture for a robotics startup. And basically, they had created their own C Sharp Kafka, essentially, but they were doing like they were sort of mini batching things and send it to BigQuery in a pending. Now, they did not care about lossless. They were doing things like keeping number as a

43:36string because that's how the machine was sending it is, hey, the number one is a string. And then around two months later, the data type is now just an integer, and it just was appended there, and the table didn't matter. And you're trying to run analytics in the back going far back, and you don't understand why everything's breaking. So, there's just all these things up above you necessarily that's coming down into you that when you're,

44:00I think you're in a traditional data warehouse, it's just landmine after landmine. But here, what DuckLake's doing from a schema evolution standpoint is it's giving you a lot of ways to kind of keep that from hitting you initially, like the idea that you could just simply move from big int to an int, that's okay, and you don't have to think about that.

44:22But also just the way that it's like, hey, there's all these other options for you. You can obviously create a new column. You can put a new version of that value in there. There's a lot of great ways to handle schema evolution in DuckLake. And it does at least give you a couple things out of the box that says, we're not going to just let someone

44:45go from string to int, for example, in a column. We're going to have some guardrails. And so, I personally appreciated that and could have used it three years ago, but c'est la vie. C'est la vie. Yeah, at that point, you'd want to add a column and have that be your new one, but you could rename the old one. So that way, all the existing queries don't break, and they just all point to the nice new integer one. So there is a little bit of a, you could

45:08pull the tablecloth and leave all the dishes still on the table, which is pretty nice. Well, let me share another window and talk about one last feature that I just am a really big fan of. And I will get that going here. And that is the [data change feed](https://motherduck.com/docs/key-tasks/database-operations/data-change-feed/). So I'm looking

45:31here at the DuckLake docs. I am a big fan of the docs that they put together there. And the data change feed shows just how granular this information is that DuckLake is storing for you. It is so granular that not only can you see the columns that changed or the files

45:50that changed, you can see the individual rows that changed. So you can actually say, look at what changed between snapshot three and four. That could also be a timestamp showing you a change between yesterday and today. And it will tell you every row that's deleted, every row that's updated, both what it used to be and what it is now, and every row that's inserted. Everything you could possibly need to know about what changed, even if you did

46:15this three different times to the same row, like delete, insert, delete, insert, update, it will show you the full history of all those changes. And you'll see exactly what happened. This is so much data, you could even do change data capture off of a DuckLake and send it to another downstream system. It tells you everything you need to know to completely recreate your changes of your DuckLake. Someone asked a question earlier that I thought was

46:39interesting, and I will have to find that original question, but about running some things locally. Is there a way to have a local copy? Yeah. So this is one of the ways you could have a local copy, where you could have a local copy you download once, and then you could just see what has changed and only download what changed. That's slightly different

47:02than the question here. So Ilya, thank you for this question. That's actually downloading everything locally when you do these changes. But Ilya's question I think is extra interesting as well and gets to the architecture of DuckLake, which is, can I just replicate the catalog only and leave the files where they are? And then have people just read from their

47:21local machines, from that local catalog, but still leave the data exactly where it lives. And the answer to that is pretty simple. It's yes, you can. And it's pretty darn cool. There's a copy database command. So you can copy a whole database and put it somewhere else. And what that means you can do is there's different systems you can use for your catalog

47:44in DuckLake. You can use Postgres, which is the kind of main one for the open source. You can use MotherDuck, which is the main managed one. Or you can actually use DuckDB or SQLite. And so what I would recommend is copying from Postgres into DuckDB locally and leave the data files right where they are. And then you can just happily do read-only

48:09access to your heart's content. MotherDuck also has a read-only access method as well. So you can have a separate compute node that has read-only access as well. Same, same, same. But this data change feed, I just think it's so cool that you see exactly what happened all the way down to the nitty gritty details whenever you need it. So great to have in

48:29your back pocket. All right. Well, that brings us into Q&A time. We still have a few minutes for that. So your questions are great. Keep them coming. I think we'll catch back up to some of the more general ones up from the beginning. So let me take a quick look.

48:43All right. All right. A couple of good questions that are a little bit tricky where I don't know if I know the answer. So if you've asked a really hard question, I do still appreciate it.

49:05How dare you? Let's see. J Money asked a good question. I don't have the answer off the top of my head. So I think I'll point Clauded it after the fact. Do views inherit from the snapshot

49:25table too so that I can build some conformed or aggregated view but then also filter it based on snapshots? So the one way I could think of off the top of my head to do this today is attach the whole DuckLake at the snapshot you want. And then you can either create or use a view at that point. So if you attach the whole database, everything will be at that exact

49:47snapshot that you want. If you've changed the view in the meantime, you just run another query that says, hey, you know, or just run the SQL statement directly instead of a view, whichever. And that way you'll get a calculated result on top of a changed set of tables or the past set of tables, I should say. So I think there's some cool ways you could do that. Give it a shot and

50:09let me know the smoothest way. But I am confident it's possible. All right. John had a good question. For a small organization with under a million rows, how frequently would you recommend storing deltas? If you're refreshing that every day, you could save it for a long, long time. You know,

50:31a thousand days would get you to a billion rows. And a billion rows, you can do a group buy in a billion rows in under a second with DuckDB. And so I would say if you're doing like a daily update, do it to your heart's content. If you're updating it every five minutes, okay, divide by a couple of numbers and maybe if you're getting up into that, you know, billions and billions of rows that might not be so valuable for you to store that change history.

50:56But for that size of data, you've got a lot of flexibility. And DuckLake, I think, is the first lake house that is approachable for that size of data. Because every other lake house, you'd have this huge overhead every time you create that. It's, you know, like I have a good friend. He said that it was like swatting flies with a Buick, right? Like not what you want. And I just love

51:19that visual as well. It's just like so impractical. And I think that that's what that would feel like with another system. Here, it's so lightweight. You're just doing a query to Postgres and a query to some parquet data. You can store as many copies as you want. All right.

51:41All right. Marshall had a question. Is there a preferred method for handling table relationships like primary keys and foreign keys? Today, that's actually not supported in DuckLake, but it is something that's being considered for the future. With a lake house format, I don't know of any that actually enforce primary keys and foreign keys.

52:01And the reason for that is your data sizes tend to be so large that doing just the check when you insert of like, is this a duplicate or not, ends up being such a big operation that it's often impractical to do that. There's not an easy way to store that full unique set of things.

52:21So what I typically recommend is running like a merge command. So when you insert your data, you don't just do an insert, you do an insert or an update, depending on if the data is already there. And if you scope that down to like, if you know your data only changes in the last week, or if you're using like a Kafka, right? Like there's only 15 minutes of time lag that it could be. If you scope that down, that merge operation can be quite quick because you don't have to check

52:45everything. You just check that time window when you're looking for the duplicates. So that's probably my best practice. The other trick is really with data quality checks. So I would recommend running those after the fact, after the insert, periodically checking if you're violating those constraints. And then you'll be able to also, in that data quality check, get a really

53:07good indication of what's causing it. Sometimes when you put these upfront controls like primary key, foreign key stuff, all it does is throw an error back to you and say, nope, you can't. And if your data processing job was loading a billion rows, you're like, great, which one is it? Whereas if you had a data quality check, you could actually narrow that down and figure out

53:26where your problem is. All right. Well, thank you for that. Let's see. If there's any questions that jump out to you, Hoyt, feel free to toss them up. I'm looking as well.

53:46Here's another great analogy from Andrew. Thank you. Using a sports arena to pound in a nail. Wow. I can't envision it. I love it. That's exactly the vibe I want for that. That is hilarious. All right, great. And John, his organization is changing from Spreadsheet and

54:06Excel over to MotherDoc. We're here to help with that. I've lived that life. I call myself a recovered Excel guru. Those that fail, that underestimate Excel are doomed to be defeated by it. It is a powerful tool and a ton of value for the whole planet. It's kind of crazy. DuckDB and MotherDoc have great ways where you can pull and push from Excel or pull and push

54:29from Google Sheets. You don't have to quit cold turkey, as they say. You can use both even if you want. And DuckLink is a good fit for that as well because you can add columns willy-nilly to Spreadsheets. You can delete whatever you want willy-nilly from Spreadsheets. And it's nice to have that change history, and you will with DuckLink. Yeah, getting to DuckLink, going on

54:54MotherDoc is ridiculously easy. DuckLink is so easy, but somehow MotherDoc. . . I tell you what, we're going to take the entire top of that diagram off that you sent. With MotherDoc, you just got to bring a storage bucket. That's all you're on the hook for, people, with the MotherDoc DuckLink. I wrote an article on it, and I thought to myself,

55:16okay, this is a lake house, so I guess I'm just done. I don't need to worry about this anymore. Right. Yep. It's one of those things, LOMs are teaching us this as well. We are moving up the ladders of abstraction as a field. Data engineering is doing that, I think, pretty rapidly. And in many ways, we're actually ahead, I think, of some of the AI side. We know what item potency is.

55:40We've done a lot of the data quality checks and the evals types of workflows, but I think AI is learning a lot from data engineering, which is a lot of fun. I will add in one quick comment here from Matt. And then if you have any last questions, get them in. We have just another minute or two. So Matt, Excel is the undisputed champion of databases. Everyone knows that.

56:02You're definitely just making no enemies with that statement. I have used Excel as a database. I'll raise my hand, and I'll say that out loud. And it worked pretty well for a while. It's a good tool to have in the toolbox. I'll say openly, I've been fighting Excel since I got into analytics. I brought a gun to a knife fight when I was first

56:26bringing an R into an Excel org that I was working with. And I'm truly, truly impressed by all of y'all of how you've managed to use Excel for things you never should have. Oh, yeah. The Excel World Championships. Hop on YouTube. I've given you something to do over lunch today, everybody. Well, thank you so much for your questions, everybody. It's been a

56:48huge pleasure. As you have other questions about DuckLake, the first spot I'd head to is grab a copy of the free book. We'll send you all the new chapters as they come out. We have a lot of new goodies coming in the future, like that exact query for how to do the weekly, monthly, yearly snapshot kind of persistence. And then a whole lot of other really advanced performance tuning is some of the stuff that's coming soon. So you can really push it to the limit. So

57:10sign up for that. The link is in the chat history near the top. And it's been a pleasure. Thank you for being here, Hoyt. Absolutely. Thanks, everyone. Go get that book right now. Download it. Sign up. It's incredible. Back on and prosper. Cheers. Thanks.

## FAQS

### What is time travel in DuckLake and how do I use it?

Time travel lets you query or restore any DuckLake table as it existed at a past moment. Add an AT clause to the FROM of any query with a timestamp or snapshot ID, or attach the entire lake as of a point in time to wind everything back at once. It's a metadata operation — no files are moved or rewritten — so it resolves with a single catalog query.

### Does DuckLake time travel mean data is never deleted?

You choose. Deletes are recorded in catalog metadata, so old row versions stay readable until you expire snapshots and compact. Set a retention window that fits your needs, run scheduled expiration beyond it, and use compaction to reclaim storage — including hard-deleting data for GDPR and similar compliance requirements.

### How much storage does DuckLake time travel cost?

It depends on your write pattern. Appends are essentially free — the new rows are data you need anyway. Updates cost a copy of what changed. Full refreshes are the expensive case: each refresh you retain is a full copy of the table, so hourly refreshes kept for a week means 168 copies. A tiered retention policy (every snapshot for days, daily for a week, weekly beyond) keeps costs down without giving up time travel.

### How does schema evolution work in DuckLake?

You can add, rename, and drop columns, and change column types as long as the change is lossless — widening an INTEGER to a BIGINT works, downsizing doesn't. None of it rewrites existing parquet files; new data flows in with the new schema and old files stay readable. Time travel continues to work across schema changes, and DuckLake tracks the schema history so you can compare tables across versions.

### How is DuckLake different from Iceberg for snapshots and time travel?

Iceberg keeps three layers of metadata files on object storage, so resolving a snapshot means a series of high-latency reads, and compacting across snapshots is hard — which pressures you to keep few of them. DuckLake puts the catalog and metadata in a regular database, so finding your files is one fast query and keeping thousands of snapshots is fine. The parquet layer is Iceberg-compatible, so migrating an Iceberg table into DuckLake is a single metadata copy.

## Related Videos

[60:29](/videos/ai-agents-semantic-layer/)[2026-08-26](/videos/ai-agents-semantic-layer/)

### [Do AI Agents Need a Semantic Layer?](/videos/ai-agents-semantic-layer/)

Do AI agents need a semantic layer? Evals comparing plain-text context with a Malloy semantic layer show where each wins, and what it costs.

Stream

AI, ML and LLMs

BI & Visualization

SQL

[63:17](/videos/agentic-data-engineering-stack/)[2026-08-18](/videos/agentic-data-engineering-stack/)

### [Beyond Copilots: We're Building a Data Stack Live with AI Agents](/videos/agentic-data-engineering-stack/)

Agentic data engineering in practice: AI agents build dlt ingestion into MotherDuck and a governed Lightdash semantic layer, live in an hour.

Stream

AI, ML and LLMs

Data Pipelines

Ecosystem

[52:41](/videos/guides-context-layer-ai-agents/)[2026-08-13](/videos/guides-context-layer-ai-agents/)

### [Introducing Guides: Warehouse-Native Context for AI Agents](/videos/guides-context-layer-ai-agents/)

Guides store business context inside your warehouse so AI agents write correct SQL. See how warehouse-native context works in MotherDuck.

Stream

AI, ML and LLMs

MotherDuck Features

[View all](/videos/)