---
title: "Getting Started with dplyneage"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Getting Started with dplyneage}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
# The raw-SQL examples need Python sqlglot, so only evaluate chunks when
# the full stack is available. has_sqlglot() initializes Python, which lets
# reticulate provision an environment over the network, so it must not be
# called during a CRAN check; there the chunks render as unevaluated code.
can_eval <- identical(Sys.getenv("NOT_CRAN"), "true") &&
  requireNamespace("dplyr", quietly = TRUE) &&
  requireNamespace("dbplyr", quietly = TRUE) &&
  requireNamespace("duckdb", quietly = TRUE) &&
  tryCatch(dplyneage::has_sqlglot(), error = function(e) FALSE)

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = can_eval
)
```

dplyneage answers a simple question about your data pipelines: *where did each column come from?* You pipe a dplyr/dbplyr query (or pass raw SQL) into `extract_lineage()`, and render the answer as an interactive diagram with `lineage_flow()`.

This vignette starts with the smallest possible example and works up to the cases where lineage gets hard: joins with ambiguous columns, CTEs, and columns computed from several sources at once.

## Installation

Install the released version from CRAN:

```{r, eval = FALSE}
install.packages("dplyneage")
```

Or the development version from GitHub:

```{r, eval = FALSE}
pak::pak("tgerke/dplyneage")
```

dbplyr, dtplyr, and arrow pipelines are analyzed entirely in R, so for those there is nothing else to install. Raw SQL strings and duckplyr frames (see [Other lazy backends](#other-lazy-backends)) are analyzed by sqlglot, dplyneage's one Python dependency: install the reticulate package to enable that engine, and sqlglot itself is provisioned automatically the first time it's needed. See `vignette("python-integration")` if you manage your own Python environment.

## Your first lineage diagram

First, create a small in-memory DuckDB database to work with:

```{r message=FALSE}
library(dplyneage)
library(dplyr)
library(duckdb)

con <- dbConnect(duckdb(), ":memory:")

customers <- tibble(
  id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  email = paste0(tolower(name), "@example.com")
)

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

copy_to(con, customers, "customers", overwrite = TRUE)
copy_to(con, orders, "orders", overwrite = TRUE)
```

The simplest lineage there is: two columns selected from one table.

```{r}
tbl(con, "customers") |>
  select(id, name) |>
  extract_lineage() |>
  lineage_flow(height = "300px")
```

Each output column connects back to the source column it came from. Try dragging the tables around, zooming with the mouse wheel, and hovering a column to highlight its connections. Clicking a column isolates its trace cone, everything upstream and downstream of it, and Escape (or a background click) releases it. The legend in the corner names the colors; `lineage_flow()` also offers `theme = "dark"`, a `minimap`, and a PNG download button in the zoom controls.

## A realistic pipeline

Lineage becomes useful once transformations pile up. Here is a join followed by an aggregation:

```{r}
tbl(con, "customers") |>
  left_join(tbl(con, "orders"), by = c("id" = "customer_id")) |>
  group_by(id, name) |>
  summarise(total_spent = sum(amount, na.rm = TRUE), .groups = "drop") |>
  extract_lineage() |>
  lineage_flow(height = "400px")
```

Notice that `total_spent` traces back to `orders.amount`, not to `customers`, even though `amount` appears unqualified in the generated SQL. When you pass a dbplyr table, dplyneage doesn't parse SQL at all: it walks the pipeline's own query tree, which records exactly which table each column came from, so attribution is always right.

If a pipeline embeds raw SQL with `dbplyr::sql()`, the query tree can't see inside that string, so `extract_lineage()` hands the whole query to sqlglot instead (with a message). You can also force a specific engine with `engine = "r"` or `engine = "sqlglot"`; see `?extract_lineage`.

## Computed columns

Most columns in a pipeline are computed rather than selected, and `mutate()` is where that happens. Lineage treats a computed column like any other: its edges start at every column the expression reads. This pipeline builds a few of them the usual ways: a plain expression, a `case_when()` written against a column defined two lines earlier, an `across()` that names its outputs, and a per-customer share computed with `.by`.

```{r}
computed <- tbl(con, "orders") |>
  mutate(
    amount_eur = amount * 0.92,
    tier = case_when(
      amount_eur >= 180 ~ "large",
      amount_eur >= 90 ~ "medium",
      TRUE ~ "small"
    ),
    across(c(amount, amount_eur), round, .names = "{.col}_rounded")
  ) |>
  mutate(
    customer_share = amount / sum(amount, na.rm = TRUE),
    .by = customer_id
  ) |>
  extract_lineage()

lineage_flow(computed, height = "400px")
```

Every new column traces to `orders.amount`, including `tier`, which was written against `amount_eur` rather than `amount`: a reference to a column defined earlier in the same `mutate()` resolves through it to the source. The two `across()` outputs each trace to their own input. `customer_share` also draws an edge from `customer_id`, because on a SQL backend the `.by` column becomes the `PARTITION BY` of a window function and so sits inside the expression that computes the share; the `sum()` in that expression makes the edge an aggregation, which the diagram animates.

`lineage_edges()` gives the same lineage as a table, one row per edge, with each computed column's classification and defining expression:

```{r}
lineage_edges(computed)
```

The rest of the family follows the same rule. Overwriting a column (`mutate(amount = amount * 0.92)`) keeps an edge from the source column, now classified as a transformation instead of an identity. `transmute()` and `mutate(.keep = "none")` drop the passthrough columns and their identity edges with them, and `mutate(x = NULL)` drops one. `rename()` and `relocate()` change nothing about provenance, so their columns keep identity edges to the original names. All of this holds on `tbl_lazy()` frames, dtplyr, and arrow as well as on a database, with one backend difference: data.table and Acero have no window clause, so on dtplyr and arrow a `.by` column stays an indirect source (see [Other lazy backends](#other-lazy-backends)).

## Where lineage gets hard

These are the cases that break naive lineage tools. dplyneage handles them because both engines resolve the full query structure rather than pattern-matching column names: dbplyr pipelines through their query tree, raw SQL through sqlglot's lineage engine. We'll use raw SQL here to keep the examples compact.

### Tracing through CTEs

Columns are traced *through* intermediate CTEs back to the base tables. `recent` is transparent, and `amount` correctly attributes to `orders`:

```{r}
extract_lineage("
  WITH recent AS (
    SELECT customer_id, amount FROM orders WHERE order_date > '2024-01-01'
  )
  SELECT customer_id, SUM(amount) AS total FROM recent GROUP BY customer_id
") |>
  lineage_flow(height = "300px")
```

### Columns with multiple sources

A computed column can come from several tables at once. `COALESCE` over a full join gets an edge from *both* sources:

```{r}
extract_lineage("
  SELECT COALESCE(u.email, a.email) AS email
  FROM users u FULL JOIN archive a ON u.id = a.id
") |>
  lineage_flow(height = "300px")
```

The same applies to arithmetic across tables (`o.amount * r.rate`), `CASE` expressions, and both branches of a `UNION`. dbplyr pipelines get the same treatment: a `full_join()` key column traces to both sides, and a `union_all()` column to every branch.

### Expanding `SELECT *`

With a schema available, `SELECT *` expands to real columns. dbplyr input never needs a schema (the pipeline itself knows its columns), but for raw SQL, pass one yourself:

```{r}
extract_lineage(
  "SELECT * FROM customers",
  schema = list(customers = c("id", "name", "email"))
) |>
  lineage_flow(height = "300px")
```

## Raw SQL and schemas

As the examples above show, `extract_lineage()` accepts a SQL string directly, useful for auditing queries you didn't write in R. Two things to know:

- **Qualified columns** (`o.amount`) always resolve correctly, schema or not.
- **Unqualified columns** need a schema to be attributed with certainty. Pass a named list mapping each table to its columns:

```{r}
extract_lineage(
  "SELECT c.name, order_date
   FROM customers c JOIN orders o ON c.id = o.customer_id",
  schema = list(
    customers = c("id", "name", "email"),
    orders = c("order_id", "customer_id", "order_date", "amount")
  )
) |>
  lineage_flow(height = "300px")
```

Without a schema, `SELECT *` cannot be expanded and produces a warning rather than a silently empty diagram.

Queries in other SQL dialects work by setting `dialect`:

```{r, eval = FALSE}
extract_lineage(query, dialect = "postgres")
extract_lineage(query, dialect = "snowflake")
```

## Local data frames

`extract_lineage()` reads lineage from a *lazy* query, the tree dbplyr builds up before anything touches the database. A pipeline on a plain tibble has no such tree: dplyr executes each verb immediately, so by the time you could ask about lineage, only the result is left.

The lightest fix is `dbplyr::tbl_lazy()`, which wraps the frame in a lazy table backed by no database at all. Such a pipeline can't be collected, but lineage never runs the query, so a diagram doesn't need it to be:

```{r, eval = can_eval}
sales <- dbplyr::tbl_lazy(
  data.frame(
    customer_id = c(1, 1, 2),
    amount = c(100, 250, 40)
  ),
  name = "sales"
)

sales |>
  group_by(customer_id) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  extract_lineage() |>
  lineage_flow(height = "300px")
```

When the pipeline should stay runnable, use a real (if throwaway) database instead. `dbplyr::memdb_frame()` builds the data in in-memory SQLite (install the RSQLite package once), and `copy_to(dbplyr::memdb(), df, name = "df")` does the same copy for a frame you already have. Lineage depends only on the pipeline's structure, never on the data, so for large frames copying a slice is enough: `copy_to(dbplyr::memdb(), head(df), name = "df")` yields the same diagram as copying every row.

The duckdb connection from earlier in this vignette works just as well (`copy_to(con, df)`); `tbl_lazy()` is simply the fastest route when no connection exists and none is needed.

## Other lazy backends

dbplyr is not the only backend that builds a query tree before executing. [dtplyr](https://dtplyr.tidyverse.org/) records steps toward data.table code, [duckplyr](https://duckplyr.tidyverse.org/) holds a duckdb relation behind an ordinary-looking tibble, and [arrow](https://arrow.apache.org/docs/r/) queries compile to Acero plans. `extract_lineage()` reads all of them, and the pipe looks the same:

```{r, eval = can_eval && requireNamespace("dtplyr", quietly = TRUE) && requireNamespace("data.table", quietly = TRUE)}
dtplyr::lazy_dt(
  data.frame(
    customer_id = c(1, 1, 2),
    amount = c(100, 250, 40)
  ),
  name = "sales"
) |>
  group_by(customer_id) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  extract_lineage() |>
  lineage_flow(height = "300px")
```

dtplyr and arrow pipelines are walked natively in R, like dbplyr's. duckplyr is the exception: its relation is a duckdb object with no R-readable structure, so the engine renders it to duckdb SQL and analyzes that with sqlglot, which means duckplyr lineage needs reticulate where the others need nothing. The metadata records which engine ran and a matching dialect label (`"data.table"` for dtplyr, `"arrow"` for arrow, `"duckdb"` for duckplyr).

Three backend habits affect the diagram you get:

- Name your frames where the backend lets you. `lazy_dt(df, name = "sales")` puts `sales` on the source node; without it dtplyr auto-names frames `_DT1`, `_DT2`, and so on. In-memory duckplyr frames and arrow tables have no name slot at all, so their nodes come out as `df`/`df_1` and `arrow_table`/`arrow_table_2`. File-backed sources (`duckplyr::read_csv_duckdb()`, `arrow::open_dataset()`) use the file path, which reads much better.
- data.table and Acero have no `OVER` clause, so a windowed column's grouping keys stay indirect on dtplyr and arrow (dashed `group_by` edges under `include_indirect = TRUE`) instead of becoming direct sources the way rendered SQL makes them.
- duckplyr hands anything it cannot translate back to eager dplyr without a message: `group_by()` followed by `mutate()`, `rowwise()`, and functions with no duckdb translation (`case_when()`, `paste0()`, `between()`, and `rename_with()` are common ones). The result comes back as an ordinary tibble, or as a duckplyr frame with no relation left behind it, and `extract_lineage()` says so either way. `Sys.setenv(DUCKPLYR_FALLBACK_INFO = TRUE)` makes duckplyr name the step that fell back; extract lineage from the step before it, or rewrite it (a `.by` mutate whose aggregates pass `na.rm = TRUE` stays lazy, for instance).

Column labels travel on every backend: `label` attributes survive `lazy_dt()` and `arrow_table()`, and arrow schemas contribute column types to the hover cards for free.

## Column labels

Column names say what a thing is called; labels say what it means. R data often has them already: haven and labelled store a `label` attribute on every column imported from SAS, SPSS, or Stata, and database tables keep the same idea as column comments. `extract_lineage()` collects both (`label` attributes from local frames, comments from a live duckdb or postgres connection), and a `labels` argument covers everything else, winning over the automatic sources:

```{r, eval = can_eval}
sales_df <- data.frame(
  customer_id = c(1, 1, 2),
  amount = c(100, 250, 40)
)
attr(sales_df$customer_id, "label") <- "Customer surrogate key"
attr(sales_df$amount, "label") <- "Order amount in USD"

lineage <- dbplyr::tbl_lazy(sales_df, name = "sales") |>
  group_by(customer_id) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  extract_lineage(labels = list(output = c(total = "Total spent per customer")))

doc <- jsonlite::fromJSON(lineage_json(lineage), simplifyVector = FALSE)

doc$nodes[[1]]$labels # sales: both labels, captured from the attributes
doc$nodes[[2]]$labels # output: total hand-labelled, customer_id propagated
```

That receipt shows the whole pattern. `customer_id` passes through the `group_by()` unchanged, so its label travels to the output node on its own: labels (and types, when captured) propagate along identity edges, the way dbt Catalog carries descriptions through passthrough columns. `total` is a computed column no propagation can describe, so it gets a `labels` entry keyed by `"output"`, the synthetic name of a single query's result; in a multi-model pipeline, the model's name plays that role. Had two sources fed one column conflicting labels, it would have stayed bare: missing beats wrong.

In the diagram, those labels become hover cards. Try it on this rendering: `customer_id` shows its label on both nodes, and `total` shows the hand-supplied one.

```{r, eval = can_eval, fig.alt="Column-level lineage diagram from the sales table to the output table; hovering a column shows a card with its label"}
lineage_flow(lineage, height = "300px")
```

`lineage_json()` records the same labels as per-node `labels` and `types` maps, and each OpenLineage schema-facet field gains a `description`; the [OpenLineage article](https://tgerke.github.io/dplyneage/articles/openlineage.html) shows the rendered event.

## Building diagrams by hand

For documentation or design sketches, skip extraction entirely and build the diagram yourself:

```{r}
nodes <- list(
  create_table_node(
    table_name = "customers",
    columns = c("id", "name", "email"),
    x = 0, y = 100,
    table_type = "source"
  ),
  create_table_node(
    table_name = "customer_summary",
    columns = c("customer_id", "full_name", "contact"),
    x = 500, y = 100,
    table_type = "target"
  )
)

edges <- list(
  create_column_edge("customers", "id", "customer_summary", "customer_id"),
  create_column_edge("customers", "name", "customer_summary", "full_name"),
  create_column_edge("customers", "email", "customer_summary", "contact")
)

lineage_flow(nodes, edges, height = "300px")
```

Nodes come in three types: `"source"` (blue), `"transform"` (orange), and `"target"` (green). Edges accept a `label` (e.g. `"SUM()"`) and `animated = TRUE` for emphasis. `lineage_example()` renders a complete hand-built diagram you can use as a template.

## Exporting lineage

Diagrams answer questions interactively; sometimes you need the same lineage as plain data. Two exporters cover the common cases.

`lineage_json()` serializes the nodes, edges, and metadata to a small, stable JSON document, stamped with a `format_version` so consumers can rely on its shape (see `?lineage_json` for the full schema). Because the output is deterministic, you can commit it alongside your pipeline code and let CI diff it: if a refactor silently changes where a column comes from, the diff shows it before it ships; the [lineage checks in CI](https://tgerke.github.io/dplyneage/articles/lineage-ci.html) article turns that into a one-call gate with `lineage_check()`. It is also the natural handoff format for data catalogs or anything scriptable with jq.

```{r}
lineage <- tbl(con, "customers") |>
  left_join(tbl(con, "orders"), by = c("id" = "customer_id")) |>
  group_by(id, name) |>
  summarise(total_spent = sum(amount, na.rm = TRUE), .groups = "drop") |>
  extract_lineage()

lineage_json(lineage)
```

`lineage_graphml()` writes GraphML, the XML format that graph tools speak: igraph, Gephi, and yEd all open it directly. Every column becomes its own node, which is what makes real graph queries possible. The classic one is impact analysis ("if `orders.amount` changes, which outputs are affected?") or its reverse, tracing an output back to every source column that feeds it:

```{r, eval = can_eval && requireNamespace("igraph", quietly = TRUE)}
path <- tempfile(fileext = ".graphml")
lineage_graphml(lineage, path)

g <- igraph::read_graph(path, format = "graphml")

# Everything upstream of total_spent
igraph::subcomponent(g, "output.total_spent", mode = "in")

# Everything downstream of orders.amount
igraph::subcomponent(g, "orders.amount", mode = "out")
```

Both functions return the serialized string when called without `path`, so they compose in pipes and tests.

You don't need a graph library for the everyday questions, though. `lineage_upstream()` and `lineage_downstream()` answer them straight from the lineage object (pass `"orders.amount"` for one column, or `"orders"` to trace every column of a table at once), and `lineage_unused()` lists the columns with no path to any target, which is how dead weight in a multi-model pipeline shows up.

```{r, include = FALSE}
dbDisconnect(con)
```

## Next steps

- The [lineage checks in CI](https://tgerke.github.io/dplyneage/articles/lineage-ci.html) article sets up `lineage_check()` as a GitHub Actions gate that fails pull requests on breaking provenance changes
- `vignette("python-integration")` explains how the Python dependency is managed, and how to use your own environment
- The [function reference](https://tgerke.github.io/dplyneage/reference/) documents every argument
- Found a query that traces incorrectly? Please [open an issue](https://github.com/tgerke/dplyneage/issues) with the SQL