🧭 Business-Friendly Lineage in Excel — Powered by DuckDB

Data lineage doesn’t have to live only in technical catalogs or complex graph UIs.
With a bit of SQL, a small DuckDB database and a clever export, you can hand business teams a clean, sortable Excel view of how data flows.

We start with simple mapping tables, run a recursive query in DuckDB, and deliver a business-friendly Excel.”

Why lineage is frustrating for business users

Most organizations have metadata and data catalogs. Yet business analysts still ask:

• “Where does this KPI come from?”
• “If we change this column, who will break?”

Catalogs show beautiful graphs but are often hard to search or filter. Analysts and product owners prefer Excel — they know how to sort, filter, and scan rows quickly.

A simple idea: a delivery layer for lineage

Instead of forcing everyone into a specialized tool, we build a delivery layer:

Load your mapping metadata (source → target) into DuckDB.

Use recursive SQL to walk the graph:

• Start at a root column or root table.
• Follow mappings downstream (lineage) or upstream (impact analysis).
• Track depth and build a hierarchical sort key like 1.3.4.

Store the flattened result in a table (lineage_analysis).

Export to Excel with filters and an indented name column.

Anyone can open the file, search or filter, and follow a column from its source to its report.

Minimal metadata

You only need two tables:

column_mapping
  source_schema | source_table | source_column
  target_schema | target_table | target_column

table_mapping
  source_schema | source_table
  target_schema | target_table

Most ETL tools, dbt, or data catalogs can export this easily.

Flattening the graph with recursive SQL

Recursive CTEs let us follow every hop until there are no more mappings.

Key ideas:

• Depth → indent level in the tree.
• Order key → 1.2.3 style string for perfect Excel sorting.
• Visited path → prevents infinite loops.

Recursive CTE walks each mapping, tracks depth, and builds an order key for Excel sorting.

Simplified example (column-level lineage):

WITH RECURSIVE walk AS (
  SELECT
    0 AS depth,
    1 AS step_index,
    '1' AS order_key,
    source_schema, source_table, source_column,
    target_schema, target_table, target_column,
    source_schema || '.' || source_table || '.' || source_column AS visited
  FROM column_mapping
  WHERE source_schema='core' AND source_table='orders' AND source_column='amount'
UNION ALL
  SELECT
    w.depth+1,
    ROW_NUMBER() OVER (PARTITION BY w.order_key ORDER BY c.target_schema, c.target_table, c.target_column),
    w.order_key||'.'||ROW_NUMBER() OVER (PARTITION BY w.order_key ORDER BY c.target_schema, c.target_table, c.target_column),
    c.source_schema, c.source_table, c.source_column,
    c.target_schema, c.target_table, c.target_column,
    w.visited||'>'||(c.target_schema||'.'||c.target_table||'.'||c.target_column)
  FROM walk w
  JOIN column_mapping c
    ON c.source_schema=w.target_schema
   AND c.source_table=w.target_table
   AND c.source_column=w.target_column
  WHERE INSTR(w.visited,c.target_schema||'.'||c.target_table||'.'||c.target_column)=0
)
SELECT * FROM walk;

Automating with Python + DuckDB

We wrap the SQL in a small Python script to:

• Load the metadata (CSV, dbt manifest, catalog export…)
• Run lineage & impact directions
• Fill a delivery table lineage_analysis
• Export to Excel with filters and indentation

import duckdb, pandas as pd

# --- 1. Load mapping metadata ---
column_mapping = pd.DataFrame([
    ("raw","orders_raw","order_id", "stg","orders_stg","order_id"),
    ("stg","orders_stg","order_id", "core","orders","order_id"),
    ("core","orders","order_id",    "mrt","fct_sales","order_id"),
], columns=["source_schema","source_table","source_column",
            "target_schema","target_table","target_column"])
con = duckdb.connect("lineage.duckdb")
con.execute("CREATE OR REPLACE TABLE column_mapping AS SELECT * FROM column_mapping")

# --- 2. Create the delivery table ---
con.execute("""
CREATE OR REPLACE TABLE lineage_analysis(
  root_schema TEXT, root_table TEXT, root_column TEXT,
  direction TEXT, depth INT, order_key TEXT,
  from_schema TEXT, from_table TEXT, from_column TEXT,
  to_schema TEXT,   to_table TEXT,   to_column TEXT
);
""")

# --- 3. Run a lineage traversal for one root ---
con.execute("""
WITH RECURSIVE walk AS (
  SELECT 0 AS depth, 1 AS step, '1' AS order_key,
         source_schema, source_table, source_column,
         target_schema, target_table, target_column,
         source_schema||'.'||source_table||'.'||source_column AS visited
  FROM column_mapping
  WHERE source_schema='core' AND source_table='orders' AND source_column='order_id'
  UNION ALL
  SELECT w.depth+1,
         ROW_NUMBER() OVER (PARTITION BY w.order_key ORDER BY c.target_schema),
         w.order_key||'.'||ROW_NUMBER() OVER (PARTITION BY w.order_key ORDER BY c.target_schema),
         c.source_schema,c.source_table,c.source_column,
         c.target_schema,c.target_table,c.target_column,
         w.visited||'>'||c.target_schema||'.'||c.target_table||'.'||c.target_column
  FROM walk w
  JOIN column_mapping c
    ON c.source_schema=w.target_schema
   AND c.source_table=w.target_table
   AND c.source_column=w.target_column
  WHERE INSTR(w.visited,c.target_schema||'.'||c.target_table||'.'||c.target_column)=0
)
INSERT INTO lineage_analysis
SELECT 'core','orders','order_id','LINEAGE',depth,order_key,
       source_schema,source_table,source_column,
       target_schema,target_table,target_column
FROM walk
ORDER BY order_key;
""")

# --- 4. Export to Excel ---
df = con.execute("SELECT * FROM lineage_analysis ORDER BY order_key").df()
df.to_excel("lineage.xlsx", index=False)
print("Wrote lineage.xlsx")

In Excel: filter, sort by order_key, and follow the indented hierarchy to trace data.

Open lineage.xlsx → Sort by order_key → you get a clean, hierarchical path.

What business users see

They can filter by root, choose direction (LINEAGE vs IMPACT), and collapse or expand levels.

Why this works

• Business-friendly: simple table, no new UI.
• Lightweight: DuckDB runs locally or in CI/CD.
• Impact-ready: generate reverse direction easily.
• Automatable: rebuild whenever pipelines change.

Coming next in this series

1. Impact Analysis for Data Product Owners — reverse lineage for change risk.
2. Scaling & Scheduling Lineage Delivery — run for all columns and publish weekly.
3. Beyond Excel: Visualizing Lineage in BI Tools — PowerBI/Tableau from the same delivery layer.

Lineage doesn’t have to stay locked in expensive catalogs.
With two simple mapping tables, a recursive query and a clever sort key, you can hand your business users a clean, Excel-friendly view of data flow.