Skip to contents

Column lineage for ducklake pipelines

Source: vignettes/articles/ducklake-lineage.Rmd
ducklake-lineage.Rmd

ducklake gives you a versioned data lake: related tables stored together, every change snapshotted, full audit trails. dplyneage answers a question ducklake deliberately leaves to its query layer: within a pipeline, where did each column come from?

The two compose without any glue code. get_ducklake_table() returns an ordinary dbplyr lazy table, and that is exactly what extract_lineage() accepts, so any query you can write against a lake, you can also diagram.

Setting up a small lake

We’ll build a lake with two related tables. If you already have one, skip ahead and pipe your own queries.

library(dplyneage)
library(ducklake)
library(dplyr)

install_ducklake()

lake_dir <- file.path(tempdir(), "lineage_lake")
dir.create(lake_dir, showWarnings = FALSE)
attach_ducklake("demo_lake", lake_path = lake_dir)

Load customers and their orders, with commit messages so the lake’s audit trail says why each table exists:

customers <- data.frame(
  customer_id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  region = c("east", "west", "east", "south", "west")
)

orders <- data.frame(
  order_id = 1:10,
  customer_id = rep(1:5, each = 2),
  amount = c(100, 150, 200, 75, 300, 125, 180, 90, 250, 160)
)

with_transaction(
  create_table(customers, "customers"),
  author = "Data Engineer",
  commit_message = "Load customer master"
)
#> Committed snapshot 1 (Data Engineer): Load customer master

with_transaction(
  create_table(orders, "orders"),
  author = "Data Engineer",
  commit_message = "Load order transactions"
)
#> Committed snapshot 2 (Data Engineer): Load order transactions

Lineage for a lake pipeline

Here is a typical analysis query (join orders to customers, aggregate by region) piped straight into lineage extraction:

get_ducklake_table("orders") |>
  left_join(get_ducklake_table("customers"), by = "customer_id") |>
  group_by(region) |>
  summarise(total_sales = sum(amount, na.rm = TRUE), .groups = "drop") |>
  extract_lineage() |>
  lineage_flow(height = "400px")

Two things to notice:

  • Lake tables appear as blue source nodes under their plain names (orders, customers), the same names you passed to create_table(), with no catalog prefix cluttering the diagram.
  • No Python was involved. Because ducklake queries are dbplyr pipelines under the hood, dplyneage’s pure-R engine reads column provenance directly from the query tree. total_sales attributes to orders.amount and region to customers.region, exactly, with nothing to install beyond the two packages.

Layered lakes: one diagram per hop

ducklake encourages a layered (bronze/silver/gold) architecture where each layer is materialized with create_table(). Lineage slots naturally into that workflow: the pipeline that builds a layer is also the recipe for its lineage diagram, so you can document the hop at the moment you materialize it.

region_sales <- get_ducklake_table("orders") |>
  left_join(get_ducklake_table("customers"), by = "customer_id") |>
  group_by(region) |>
  summarise(total_sales = sum(amount, na.rm = TRUE), .groups = "drop")

with_transaction(
  create_table(region_sales, "region_sales"),
  author = "Data Analyst",
  commit_message = "Gold layer: sales by region"
)
#> Committed snapshot 3 (Data Analyst): Gold layer: sales by region

A downstream query now sees region_sales as its source, and its lineage diagram documents just that hop:

top_regions <- get_ducklake_table("region_sales") |>
  filter(total_sales > 300)

top_regions |>
  extract_lineage() |>
  lineage_flow(height = "300px")

The whole lake in one diagram

Per-hop diagrams are useful at the moment you build a layer; a named list stitches them into the full picture. Pass each layer’s build pipeline under the name it was materialized as, and extract_lineage() connects the hops: any source table matching a model name becomes an intermediate transform node.

lake_lineage <- extract_lineage(list(
  region_sales = region_sales,
  top_regions = top_regions
))

lineage_flow(lake_lineage, height = "450px")

Now orders and customers (blue) feed region_sales (orange), which feeds top_regions (green): the whole lake, bronze to gold, with every column edge preserved. Aggregation edges carry their defining expression as a label, so total_sales reads as sum(amount, na.rm = TRUE) right on the diagram.

The stitched graph is also where impact analysis pays off. lineage_upstream() walks the hops transitively:

lineage_upstream(lake_lineage, "top_regions.total_sales")
#> [1] "orders.amount"            "region_sales.total_sales"

So a rename of orders.amount touches every layer of this lake. Better to know that before you commit it.

When a pipeline builds the layers instead of a notebook, the named list can come from the pipeline’s own store. The project-level lineage for targets pipelines article runs a bronze/silver/gold lake as a targets pipeline, keeps each layer’s build query in the target’s value, and stitches the whole project from tar_read() without opening the lake.

Time travel and lineage

ducklake’s snapshots version your data; lineage describes your pipeline’s structure. They compose: you can extract lineage from a time-travel query just like any other.

first_version <- list_table_snapshots("orders")$snapshot_id[[1]]

get_ducklake_table_version("orders", first_version) |>
  group_by(customer_id) |>
  summarise(total = sum(amount, na.rm = TRUE), .groups = "drop") |>
  extract_lineage() |>
  lineage_flow(height = "300px")
#> Falling back to the sqlglot engine: The pure-R lineage engine does not support tables defined by raw SQL (`tbl(con, sql(...))`).

One implementation detail surfaces here. get_ducklake_table_version() builds its query from raw SQL (AT (VERSION => ...) under the hood), which the pure-R engine can’t see inside, so extract_lineage() falls back to the sqlglot engine, with a message. sqlglot parses DuckDB’s time-travel syntax and traces the columns identically; the only practical difference is that this path uses dplyneage’s Python dependency (provisioned automatically; see vignette("python-integration")).

The diagram is the same at every version, and that’s the point: snapshots change what the data was, not where the columns come from. If a refactor ever does change provenance, lineage_check() can fail the pull request that introduced it; the lineage checks in CI article has a copy-paste GitHub Actions job.

A time-travel query carries today’s recipe over yesterday’s rows, so it cannot say how those rows were derived at the time. To keep that record, store the lineage on the snapshot itself: the lineage that travels with the data article passes lineage_json() as the commit_extra_info of the commit that materializes each layer, and reads it back with lineage_from_json() as of any snapshot.

Next steps