Quack to the Future

Building traceable data lakehouses with DuckLake in R, on a laptop

DestinationPOSIT::CONF 2026 · HOUSTON TX

PresenterTRAVIS GERKE · PCCTC

Last time departedFINAL_v2_FINAL.CSV · 1985

Hi, I’m Travis. I lead applied intelligence at the Prostate Cancer Clinical Trials Consortium, and this talk is about time travel for your data. Ironically, this session is called from laptop to cluster, and I’m going to describe a lakehouse approach that in many cases enables the reverse: going from cluster to laptop.

Unversioned data fades

~/sales-report/analysis.R  ·  git: last commit Mar 14

df <- read_excel( "data/2026-03-14/export_v2_FINAL.xlsx" ) sum(df$revenue) #> [1] 48123004633150

~/sales-report/data

2025-12-01/ 2026-01-15/ 2026-03-14/ export.csv Mar 14 export_v2.csv Mar 14 export_v2_FINAL.xlsx Mar 14Jun 2 ← edited in place

“Why did this number change?” You have no idea. Somewhere in the past, the timeline skewed.

This setup probably looks familiar. A dated folder for each data drop, a script in git that reads one of them, and the number it printed. Then in June, someone opens the spreadsheet, fixes a cell, and saves. Same code. Git shows no change. And the number is different. The only trace is a modified date three months after the folder’s. In September a colleague asks why the number changed since March, and you have no idea. Nothing says who or why. Filenames are our version control, Slack is our audit log, memory is our lineage system. This is Marty’s alternate 1985: someone changed the data upstream, and you can’t see where the timeline forked.

We solved this once, for code

For code, versioning is a reflex:

• every change is a commit: author, message, diff
• any past state is one checkout away
• “who changed this, and why?” is git blame

For data, we do our best:

• dated folders, _v2 suffixes
• careful names, good intentions
• but no author or diff, no way back

Data pipelines need a paper trail, what changed, who changed it, why, and where every column came from.

Trip I: build the time machine

DestinationA VERSIONED LAKE

Present timelibrary(ducklake)

Last time departedFLAT FILES · 1985

Two lines of R to a lake where every write is an authored commit.

Chapter one: build the time machine. First, what we’re building. Then the build itself, which is two lines of R: load the package, attach a lake. {ducklake} is an R package that makes DuckLake feel native: dplyr in, versioned lake out.

The lakehouse, in one slide

• Your tables live as open files, almost always Parquet (cheap, columnar)
• A catalog tracks which files make up each table, at each version
• Writes are transactions: atomic, versioned, never in a half-state
• This is what Iceberg and Delta Lake do for the Spark world

The catch: built for Spark clusters, run by platform teams.

You don’t need 1.21 gigawatts for this.

Here is the lakehouse in one slide. Your tables live as open files, almost always Parquet: cheap, columnar, readable by everything. A catalog tracks which files make up each table, at each version. Writes are transactions: atomic, versioned, never left in a half-state. This is what Iceberg and Delta Lake do for the Spark world. The catch is that they were built for Spark clusters and run by platform teams. This room was priced out of the solution, not the problem. You don’t need 1.21 gigawatts for this.

What a DuckLake is

A lakehouse that fits in a folder:

1. Parquet files hold the rows
2. A plain SQL database is the catalog: every table, column, and change

DuckDB, the analytical database that already lives inside your R session, does the work. {ducklake} puts dplyr on top.

your entire lakehouse

data/lake/
├── quack_lake.ducklake  # catalog
├── bronze/vehicles/*.parquet
├── silver/vehicles/*.parquet
└── gold/efficiency_by_cyl/*.parquet

A DuckLake is a lakehouse that fits in a folder. Parquet files hold the rows, and a plain SQL database is the catalog: every table, every column, every change. DuckDB, the analytical database that already lives inside your R session, does the work, and {ducklake} puts dplyr on top. You can ls your lakehouse. Files you can see, a catalog you can query. Everything the fancy formats promise, at a scale one person can own. Same pattern as Iceberg and Delta, minus the cluster and the platform team. The catalog can be DuckDB, SQLite, or Postgres, and the files can sit on S3; this is the version of the pattern that fits in a folder. DuckDB installs like any other R package: no server, nothing for IT to provision, and dbplyr already speaks it, so your dplyr code is the SQL. The engine underneath is columnar and vectorized, parallel across cores, and it spills to disk, so it can scan data larger than RAM without falling over. That is why one laptop clears benchmarks that were built assuming a cluster.

Attach a lake

library(ducklake)

attach_ducklake("quack_lake", lake_path = "data/lake", author = "doc")

One call opens a lake, and creates it first when nothing is at the path yet.

That’s a working data lake in a folder. Every write from here is transactional and versioned.

This ran for real when the deck rendered. If you remember one code slide from this talk, make it this one. One call opens a lake, and creates it first if nothing is at that path, with Doc on record as the one who built it. The DuckLake extension downloads itself the first time you attach and tells you where it went, so there is no install step. That’s a working data lake in a folder, and every write from here is transactional and versioned. By default the catalog is a DuckDB file next to the data, which is right for one person on one machine. A team lake keeps its catalog in SQLite or Postgres, and that is a one-argument change to this call.

The plan: a medallion pipeline

Bronze

raw, as received

nothing edited, ever

bronze.vehicles

Silver

cleaned & conformed

types, names, dedup

silver.vehicles

Gold

analysis-ready

business logic applied

gold.efficiency_by_cyl

Each layer is a schema: a named group of tables inside the lake. On disk, it’s a folder.

Keep raw data sacred, refine in stages, analyze from gold. Each hop is one versioned, documented transaction.

The plan is a medallion pipeline: three layers, and each layer is a schema, a named group of tables inside the lake. On disk it is a folder: those were the three folders in the listing a couple of slides back. From here on every table name carries its layer, as in bronze.vehicles. Bronze is the raw data as received, and nothing in it is ever edited. That is the answer to the spreadsheet from the opening: load the file as received, under an authored commit, and the lake’s copy is the record of what arrived and when. Silver is cleaned and conformed. Gold is analysis-ready. Keep raw data sacred, refine in stages, analyze from gold. Each hop is one versioned, documented transaction.

Create the layers

with_transaction({
  create_schema("bronze")
  create_schema("silver")
  create_schema("gold")
}, author = "marty", commit_message = "Create medallion layers")

Created schema "bronze".
Created schema "silver".
Created schema "gold".
Committed snapshot 1 (marty): Create medallion layers

Everything inside with_transaction() lands as one snapshot. If any step fails, nothing lands.

This is the signature move in {ducklake}: the transaction wrapper is where who and why get recorded. Every write from here on has a name attached. Three schemas, one commit. Everything inside with_transaction() lands as one snapshot, and if any step fails, nothing lands. An R error partway through, a DuckLake error like a type that won’t cast, or a conflicting commit from another writer: any of those rolls the whole transaction back, so a half-finished change never reaches the lake. Each layer gets its own schema, which keeps bronze, silver, and gold one lineage under three roofs, and lets you restrict access to the raw data at the schema level.

Load the raw layer

with_transaction(
  create_table(mtcars, "bronze.vehicles"),
  author = "marty", commit_message = "Load raw vehicle data"
)

Committed snapshot 2 (marty): Load raw vehicle data

Every commit carries an author and a message, and the confirmation names the snapshot.

The raw data is mtcars, and it lands in bronze as received, one commit. Every commit carries an author and a message, and the confirmation names the snapshot. That snapshot number is the version number we’ll travel to later. Snapshot 0 is the lake’s creation.

Derive silver and gold

silver <- get_ducklake_table("bronze.vehicles") |>
  select(model, mpg, cyl, wt) |>
  mutate(cyl = as.integer(cyl))

with_transaction(create_table(silver, "silver.vehicles"),
  author = "doc", commit_message = "Clean and standardize vehicles")

Committed snapshot 3 (doc): Clean and standardize vehicles

gold <- get_ducklake_table("silver.vehicles") |>
  group_by(cyl) |>
  summarise(avg_mpg = mean(mpg, na.rm = TRUE), n_models = n())

with_transaction(create_table(gold, "gold.efficiency_by_cyl"),
  author = "doc", commit_message = "Gold: efficiency by cylinder count")

Committed snapshot 4 (doc): Gold: efficiency by cylinder count

get_ducklake_table() returns a lazy table. create_table() runs the query inside DuckDB and writes the result into the lake: the rows never pass through R.

No new verbs to learn: select, mutate, group_by, summarise. The only additions are the bookends: get a table, create a table, wrapped in a documented transaction. get_ducklake_table() gives you a lazy table, and create_table() runs the query inside DuckDB and writes the result into the lake, so the rows never pass through R. I keep each recipe in a variable on purpose, because the recipe that builds a layer is also its lineage, and in chapter three we draw the diagram from these two objects.

Your data has a git log now

list_table_snapshots() |>
  select(snapshot_id, author, commit_message)

  snapshot_id author                     commit_message
1           0    doc                        Create lake
2           1  marty            Create medallion layers
3           2  marty              Load raw vehicle data
4           3    doc     Clean and standardize vehicles
5           4    doc Gold: efficiency by cylinder count

Six months from now, “why did this change?” has an answer you can query.

This output is real; it was generated when the deck rendered. Snapshot 0 is the lake’s creation, and every row, that one included, is a commit with an author and a message. Your data has a git log now. Six months from now, “why did this change?” has an answer you can query. The lake exists, and it remembers. Chapter two is what the remembering buys you.

Trip II: drive the time machine

The lake remembers everything: query the past, see exactly what changed, undo mistakes.

Chapter two: drive the time machine. Surviving Biff, reading history, and fixing the timeline without erasing anyone from the log.

Biff gets write access

butthead_cars <- get_ducklake_table("silver.vehicles") |>
  filter(mpg >= 20) |>         # 20+ mpg? butthead cars
  select(model)

with_transaction(
  rows_delete(get_ducklake_table("silver.vehicles"), butthead_cars, by = "model"),
  author = "biff", commit_message = "small tweak"
)

Committed snapshot 5 (biff): small tweak

get_ducklake_table("silver.vehicles") |> tally() |> collect()

# A tibble: 1 × 1
      n
  <dbl>
1    18

32 rows → 18 rows, labeled “small tweak.”

Every team has a Biff. Sometimes Biff is you, at 4:47 PM. Biff wants a table of gas guzzlers, so every car that gets 20 mpg or better goes. 32 rows become 18, labeled “small tweak.” This is the spreadsheet edit from the opening, done inside a lake. Same act, but here it cannot happen without leaving a snapshot, an author, and a message behind. You can’t prevent the mistake. You can make it visible, attributed, and reversible.

“What happened?” is now a query

get_table_changes("silver.vehicles", start = 5, end = 5) |>   # snapshot 5: Biff's commit
  select(change_type, model, mpg) |>
  collect() |>
  print(n = 10)

# A tibble: 14 × 3
   change_type model            mpg
   <chr>       <chr>          <dbl>
 1 delete      Mazda RX4       21  
 2 delete      Mazda RX4 Wag   21  
 3 delete      Datsun 710      22.8
 4 delete      Hornet 4 Drive  21.4
 5 delete      Merc 240D       24.4
 6 delete      Merc 230        22.8
 7 delete      Fiat 128        32.4
 8 delete      Honda Civic     30.4
 9 delete      Toyota Corolla  33.9
10 delete      Toyota Corona   21.5
# ℹ 4 more rows

All 14 deleted rows, with the values they had before Biff touched them.

DuckLake calls this the change feed: every insert, delete, and update between any two snapshots, with the values before and after, as a lazy table you filter with dplyr. Snapshot 5 is the number Biff’s confirmation printed on the last slide. Start and end are both 5 because the question is about that one commit; start = 2, end = 5 would give you everything since the raw load. Here are all 14 deleted rows, with the values they had before Biff touched them.

The change feed, point and click

get_table_changes("silver.vehicles") |> view_table_changes()

Click a snapshot: who, when, why, and every row it touched.

The feed has a viewer, too. Same feed as the last slide, this time the table’s whole history, piped into view_table_changes(), and it opens in the RStudio viewer or a browser. The sidebar lists what changed in the range: the columns, the rows inserted, updated, and deleted, and every snapshot with its author and message. Here Biff’s snapshot is selected: who, when, why, and the fourteen rows it deleted, with the values they had before. An update shows as one row with its changed cells highlighted, and old and new on hover. Filter the feed with dplyr first to narrow it to one row or one column. This is a screenshot. The real thing is one call away from any table in the lake.

Fixing the timeline

get_ducklake_table_version("silver.vehicles", version = 3) |> tally() |> collect()

# A tibble: 1 × 1
      n
  <dbl>
1    32

restore_table_version("silver.vehicles", version = 3, author = "doc")

Committed snapshot 6 (doc): Restored silver.vehicles to snapshot 3

list_table_snapshots("silver.vehicles") |> select(snapshot_id, author, commit_message)

  snapshot_id author                         commit_message
1           3    doc         Clean and standardize vehicles
2           5   biff                            small tweak
3           6    doc Restored silver.vehicles to snapshot 3

Snapshot 3 never went anywhere. Restore rolls forward as a new snapshot: Biff’s mistake stays in the log.

You fix the timeline without erasing anyone from the photo.

Any table can be read exactly as it was at any snapshot, or at any timestamp with get_ducklake_table_asof(). Snapshot 3 never went anywhere: 32 rows. Restore rolls forward as a new snapshot, so Biff’s mistake stays in the log. In the flat-file world the fix is overwriting the overwrite and praying. Here, history is append-only, and even the recovery is auditable. You fix the timeline without erasing anyone from the photo.

Trip III: trace the flux capacitor

{dplyneage}: column-level lineage for dplyr pipelines

Every column in a result, traced back to the source columns it came from.

Chapter three: trace the flux capacitor. Snapshots answer what changed, who, and why. That leaves one clause: where every column came from. SQL teams already get this from dbt and SQLMesh: gold.avg_mpg is computed from vehicles.mpg, through these two models. For dplyr pipelines there was nothing, so the lineage lived in your head, and your head does not survive job changes. Table-level lineage stops at “these tables are related.” Column-level says this column traces to that column, via this expression, and that is the version that answers real questions in an audit or a debugging session.

The recipe is the lineage

library(dplyneage)

lake_lineage <- extract_lineage(list(
  "silver.vehicles"        = silver,
  "gold.efficiency_by_cyl" = gold
))

lineage_flow(lake_lineage, theme = "dark", height = "560px")

{dplyneage} walks the query tree dbplyr already built.

For one pipeline it is just gold |> extract_lineage() |> lineage_flow(). Here I pass the two recipes from chapter one, under the names they were saved as. No YAML, no SQL parsing: {dplyneage} walks the query tree dbplyr already built. Bronze is not in the list, but each recipe’s query tree names the tables it reads, so silver’s tree has bronze.vehicles at its base and gold’s tree has silver.vehicles. Every table a recipe reads becomes a source node, and when a source name matches a name in the list the two hops are stitched. That is how silver becomes the transform node in the middle, and bronze stays a plain source on the left. Nothing is traced to draw this; the graph is the recipes’ own structure. On the next slide is the live widget: drag a table, hover a column to highlight its connections and see the expression, as.integer(cyl) into silver, mean(mpg) into gold, and click a column to isolate everything upstream and downstream of it.

The recipe is the lineage

Before you change the past…

lineage_downstream(lake_lineage, "bronze.vehicles.mpg")           # what would I break?

[1] "gold.efficiency_by_cyl.avg_mpg" "silver.vehicles.mpg"           

lineage_upstream(lake_lineage, "gold.efficiency_by_cyl.avg_mpg")  # where did this come from?

[1] "bronze.vehicles.mpg" "silver.vehicles.mpg"

Impact analysis as a function call: know what a change touches before you make it.

This is the almanac lesson: changing upstream data without knowing the downstream consequences is how you get the bad 1985. What would I break if I changed mpg in bronze? lineage_downstream() tells you. Where did avg_mpg come from? lineage_upstream(). Renaming a column? Ask lineage_downstream() first. Pass a bare table name instead of a column to trace every column of a table at once. Impact analysis as a function call: you know what a change touches before you make it.

Where we’re going

The whole paper trail, assembled

	question	answer
{ducklake}	when / who / why did tables change?	every write is an authored, versioned commit
{dplyneage}	where did each column come from?	exact lineage from your dplyr code

tgerke.github.io/ducklake-r · tgerke.github.io/dplyneage
slides: tgerke.github.io/quack-to-the-future

So, the whole paper trail. What changed, who changed it, and why: that is {ducklake}, where every write is an authored, versioned commit. Where every column came from: that is {dplyneage}, exact lineage from your dplyr code. They compose, because both work on lazy tables: query a past snapshot, extract lineage on it, same DAG. The ask is Monday-sized. Take one pipeline you already own and put its tables in a lake, with authored commits. Thirty minutes, and reversible. Your data’s future is whatever you make it, so make it a good one. Thank you, and drive safely.