Lineage and impact analysis don’t have to be one-off scripts.
With a small amount of automation, you can rebuild and publish a fresh, Excel-friendly view every day (or week) — so business teams always have up-to-date answers.

Why scaling matters

Articles 1 and 2 showed how to flatten lineage and impact into a single Excel file.
That works great when you run it manually, but in most companies:

• Metadata changes daily.
• Pipelines evolve.
• Product owners want a current picture.

Rather than rerun by hand, we can turn this into a scheduled job.

Architecture at a glance

1. Metadata export — from dbt, ETL tools, or a catalog (CSV/JSON).
2. DuckDB — stores mappings + runs recursive queries.
3. Delivery table — one table for LINEAGE & IMPACT (direction flag).
4. Automated refresh — Airflow, dbt, or a simple cron job.
5. Distribution — drop Excel/CSV into Teams or SharePoint.

Automate: metadata → DuckDB → scheduled job → Excel → Teams/SharePoint.

Step 1 — Automate metadata ingestion

Export mappings from your existing platform:

dbt example

dbt docs generate
jq '.nodes[] | select(.resource_type=="model") |
    {source_schema,source_table,target_schema,target_table}' manifest.json > table_mapping.csv

Load into DuckDB nightly:

import duckdb, pandas as pd
df = pd.read_csv('table_mapping.csv')
con = duckdb.connect('lineage.duckdb')
con.execute("CREATE OR REPLACE TABLE table_mapping AS SELECT * FROM df")

Step 2 — Batch all roots

Use the loop pattern from Article 2 to run lineage + impact for every column/table.

roots = con.execute("""
  SELECT DISTINCT s, t, c
  FROM (
    SELECT source_schema s, source_table t, source_column c FROM column_mapping
    UNION
    SELECT target_schema, target_table, target_column FROM column_mapping
  )
""").fetchall()

for s,t,c in roots:
    run_column_walk(con, s,t,c, "LINEAGE")
    run_column_walk(con, s,t,c, "IMPACT")

Add a run_date or run_id column so you can keep history if desired.

Step 3 — Add environment tags

If you have multiple layers (dev / test / prod):

ALTER TABLE lineage_analysis ADD COLUMN environment TEXT;
UPDATE lineage_analysis
SET environment = CASE
  WHEN root_schema LIKE 'dev_%' THEN 'DEV'
  WHEN root_schema LIKE 'prod_%' THEN 'PROD'
END;

Business users can now filter by environment as well as direction.

Step 4 — Scheduled export & delivery

Use your orchestrator of choice:

• Airflow: PythonOperator to run the DuckDB script + upload to SharePoint/Teams.
• dbt Cloud: run as a job with a final to_excel() step.
• Cron: simple if you only need a nightly CSV.

df = con.execute("""
SELECT * FROM lineage_analysis
ORDER BY root_schema, root_table, root_column, direction, order_key
""").df()

df.to_excel("/mnt/share/lineage_delivery.xlsx", index=False)

Automated job drops an Excel with fresh lineage/impact into Teams or SharePoint every week.

Step 5 — Optional: BI dashboards

If your stakeholders prefer dashboards:

• Expose the lineage_analysis table via SQL or Parquet.
• Use PowerBI / Tableau / Looker to create a tree or indented table visual using order_key + depth.
• Add filters for environment, direction, and root search.

This way you can have both Excel for quick self-serve and dashboards for presentation.

Why this matters

• Always current — no more stale lineage spreadsheets.
• Self-serve impact analysis — product owners trust the view.
• Lightweight — DuckDB can run in CI/CD or on a small VM.
• Scalable — handle thousands of columns/tables without a heavy graph database.