Python Integration

Whether dplyneage involves Python depends on what you hand to extract_lineage():

  • dbplyr, dtplyr, and arrow pipelines are walked in pure R, straight from their lazy query trees. No Python is initialized, let alone required.
  • Raw SQL strings and duckplyr frames are analyzed by sqlglot’s lineage engine, called through the reticulate package. A duckplyr frame keeps its lazy tree inside duckdb, where R cannot read it, so dplyneage renders the relation to SQL and parses that. The rare dbplyr pipeline that embeds raw SQL via dbplyr::sql() takes this route too. reticulate is a Suggests dependency, so install it once with install.packages("reticulate") to enable this engine.

So if you only ever pipe dbplyr, dtplyr, or arrow 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 need it. It follows the setup the reticulate package documentation recommends: 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

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, dtplyr, or arrow pipeline ──→ pure-R walk of the lazy query tree
                                  │
raw SQL string ─────────────────→ Python (sqlglot.lineage engine)
duckplyr frame ──→ duckdb SQL ──→ Python (sqlglot.lineage engine)
                                  ↓
              Column Lineage Metadata (per output column)
                                  ↓
                    R (create nodes & edges)
                                  ↓
                 React Flow Visualization

Every path emits 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 by input: the R walkers for dbplyr, dtplyr, and arrow pipelines, and sqlglot for SQL strings and duckplyr frames. A dbplyr pipeline that uses something its walker cannot trace falls back to sqlglot. dtplyr and arrow pipelines have no such fallback, because they compile to data.table code and Acero plans, not SQL, so an untraceable construct there is an error. metadata$engine in the result records which one ran.

Key components

  1. R/lineage_r_engine.R: The pure-R engine for dbplyr
    • Walks dbplyr’s lazy query tree, reading exact column provenance through selects, mutates, window functions, joins, and set operations: no SQL parsing, no Python
  2. R/lineage_dtplyr_engine.R and R/lineage_arrow_engine.R: The other R walkers
    • Walk dtplyr’s lazy_dt() step tree and arrow’s query objects the same way, reading the translated forms each backend produces (n() arrives as .N on dtplyr, for instance). No Python here either
  3. R/lineage_duckplyr_engine.R: The duckplyr route
    • Renders the frame’s duckdb relation to SQL, rewrites it into a form sqlglot can bind without a live database, and hands it to the sqlglot engine
  4. R/lineage_engines.R: Engine dispatch
    • Maps each input to its kind and holds the registry of native engines, including which engine = values each one accepts
  5. 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
  6. 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)
  7. 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. Pipelines walked in R (dbplyr, dtplyr, arrow) sidestep the problem entirely: the walker reads provenance from the query tree, so no schema is ever needed. (When a dbplyr table falls back to sqlglot, dplyneage lists the columns of each referenced table from the live connection and hands that schema to sqlglot automatically. duckplyr frames do the equivalent for their file readers, so a read_parquet_duckdb() source binds its columns without help.)

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. A dbplyr pipeline that reaches the sqlglot engine infers its dialect from the database connection, and a duckplyr frame is always parsed as "duckdb". For SQL strings, pass it explicitly:

extract_lineage(query, dialect = "duckdb")     # default for SQL strings
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

  • dbplyr, dtplyr, and arrow pipelines: no Python startup cost at all; the R walkers run immediately
  • First raw-SQL or duckplyr call: may take a moment while the Python environment initializes (and, on the very first run, provisions)
  • Subsequent calls: fast (<100ms for typical queries)
  • Complex queries: sqlglot handles CTEs, subqueries, window functions, complex joins, and set operations (UNION, INTERSECT, etc.)

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.