Python Integration

dplyneage has two lineage engines, and only one of them involves Python:

So if you only ever pipe dplyr/dbplyr queries into extract_lineage(), you can stop reading here: Python never enters the picture, and neither does reticulate. The rest of this vignette covers how the sqlglot dependency is managed when you do analyze raw SQL. It follows the current best practice from the reticulate package documentation: Python dependencies are declared with reticulate::py_require() when the package loads, and reticulate provisions them automatically.

Installation: There Is No Step Two

With reticulate installed, Python setup is automatic. The first time lineage extraction needs sqlglot, reticulate will:

  1. Find a suitable Python (downloading a self-contained build via uv if none is configured)
  2. Provision an ephemeral virtual environment containing sqlglot
  3. Cache everything, so subsequent sessions start quickly
library(dplyneage)

# This just works - no install step required
extract_lineage("SELECT id, name FROM customers") |>
  lineage_flow()

You can verify availability at any time:

has_sqlglot()
#> [1] TRUE

Note: install_sqlglot() from earlier development versions is deprecated and does nothing — there is no manual installation step anymore.

Using Your Own Python Environment

If you manage your own Python environment (a project virtualenv, conda env, or a system Python), reticulate will respect it as usual. Just make sure sqlglot is installed there:

pip install 'sqlglot>=23.0.0'
# Point reticulate at your environment before loading dplyneage
Sys.setenv(RETICULATE_PYTHON = "/path/to/your/python")

library(dplyneage)
has_sqlglot()

See ?reticulate::use_virtualenv and the reticulate Python version docs for other ways to select an environment.

How It Works

Architecture

dbplyr pipeline ──→ pure-R walk of the lazy query tree
                                  │
raw SQL string ──→ Python (sqlglot.lineage engine)
                                  ↓
              Column Lineage Metadata (per output column)
                                  ↓
                    R (create nodes & edges)
                                  ↓
                 React Flow Visualization

Both engines emit the same lineage metadata, so everything downstream is shared. Which one runs is controlled by the engine argument of extract_lineage(): "auto" (the default) picks the R engine for lazy tables and sqlglot for SQL strings, falling back to sqlglot if a pipeline uses something the R engine cannot trace. metadata$engine in the result records which one ran.

Key Components

  1. R/lineage_r_engine.R: The pure-R engine
    • Walks dbplyr’s lazy query tree, reading exact column provenance through selects, mutates, joins, and set operations — no SQL parsing, no Python
  2. R/zzz.R: Package initialization
    • .onLoad(): declares the sqlglot requirement via py_require() and imports the bundled Python module with delay_load (Python does not start until first use)
    • has_sqlglot(): checks availability
  3. inst/python/dplyneage_lineage.py: The sqlglot engine
    • Built on sqlglot.lineage.lineage(), which handles scope resolution, aliases, CTE trace-through, set operations, and star expansion
    • extract_lineage(): traces each output column to its source columns
    • list_tables(): enumerates base tables (used for schema harvesting)
  4. R/sqlglot_utils.R: R-side orchestration
    • extract_lineage(): main user-facing function, dispatches to an engine
    • harvest_schema(): reads table schemas from your database connection so unqualified columns are attributed to the right table
    • convert_lineage_to_graph(): creates visualization nodes and edges

Schemas and Attribution Accuracy

SQL alone does not always say which table an unqualified column belongs to. dbplyr lazy tables sidestep the problem entirely: the R engine reads provenance from the query tree, so no schema is ever needed. (When a lazy table falls back to sqlglot, dplyneage lists the columns of each referenced table from the live connection and hands that schema to sqlglot automatically.)

For raw SQL strings, you can pass a schema yourself:

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"),
    orders = c("customer_id", "order_date")
  )
)

Without a schema, fully qualified columns still resolve correctly; unqualified ones may not be traceable, and SELECT * cannot be expanded (you’ll get a warning).

SQL Dialects

sqlglot supports many SQL dialects. Specify the dialect when extracting lineage:

extract_lineage(query, dialect = "duckdb")     # default
extract_lineage(query, dialect = "postgres")
extract_lineage(query, dialect = "snowflake")
extract_lineage(query, dialect = "bigquery")
extract_lineage(query, dialect = "mysql")

The dialect should match your database backend to ensure accurate parsing. See the sqlglot documentation for the full list of dialects.

Performance

Troubleshooting

Check Python configuration

# See which Python reticulate is using
reticulate::py_config()

# List installed packages
reticulate::py_list_packages()

sqlglot not found in a custom environment

If has_sqlglot() returns FALSE and you have set RETICULATE_PYTHON (or activated an environment), sqlglot is missing from that environment — install it there with pip install sqlglot. If you have no custom configuration, reticulate should provision automatically; see ?reticulate::py_require for details.

References