Beyond SQL Traceability Understanding Horizontal vs Vertical, Technical vs
Functional, and End-to-End Data Lineage
Why simply parsing SQL is not enough — and how a metadata-driven approach creates lineage that engineers can trust and business users can understand.
Summary
Data lineage is essential for trust, governance, and impact analysis — but most lineage diagrams end up being unreadable spaghetti derived from SQL parsing.
This article clarifies the four key perspectives on lineage (horizontal, vertical, technical, functional), explains end-to-end lineage, and shows why a metadata-driven, model-driven platform is the best way to achieve clear, trustworthy lineage for both engineers and business stakeholders.
What Data Lineage Really Is
Data lineage is the record of how data moves, transforms, and relates as it flows from source to consumption.
It’s not just a technical artifact — it’s a foundation for:
- 🔍 Root cause analysis — trace broken metrics back to upstream data.
- 🔄 Impact analysis — know what will break if you change a column or table.
- ✅ Compliance & audit — prove data origins and transformations for regulations.
- 🤝 Transparency & trust — show analysts and business owners where numbers come from.
Lineage can be captured at:
- Table level (dataset lineage): which datasets feed or consume each other.
- Column level (field lineage): how individual attributes are calculated from upstream fields.
These layers are the building blocks for horizontal/vertical and technical/functional perspectives.
Horizontal vs. Vertical Lineage — Two Complementary Views
🔹 Horizontal Lineage — Flow Across the Architecture
- Tracks datasets across layers:
Source → Landing → Integration → Delivery → Consumption. - Helps navigate upstream/downstream impacts and architecture flow.
- Useful for impact analysis and understanding system dependencies.
🔹 Vertical Lineage — Column-Level Derivation
- Dives into how individual attributes are derived inside a step.
- Answers: “Where does this column come from and how is it calculated?”
- Supports debugging, trust building, and KPI validation.
Horizontal = navigation; vertical = explanation.
Both are essential — but they answer different questions.
Technical vs. Functional Lineage — Why SQL Parsing Alone Fails
🔸 Technical Lineage
- Built by parsing SQL, notebooks, job logs, and orchestration DAGs.
- Great for engineers debugging pipelines.
- But often devolves into spaghetti graphs incomprehensible to business users.
- Shows how code runs, but not what the transformation means.
🔸 Functional Lineage
- Starts from business-friendly mappings — describing transformations in clear business terms.
- Generates step-by-step lineage diagrams and links each step to the actual SQL.
- Gives analysts and stewards clarity and traceability without exposing dense CTE chains.
Key insight: Technical lineage is valuable for debugging; functional lineage builds trust and understanding_._
End-to-End Lineage — Connecting the Whole Journey
End-to-end lineage means tracing the complete flow:
From the original source → ingestion → transformations → warehouse/marts → dashboards & apps.
It unites:
- Horizontal lineage (system-to-system flow).
- Vertical lineage (column-level derivations).
- Upstream & downstream visibility — no blind spots.
- Cross-tool integration — ingestion, SQL warehouses, dbt, notebooks, BI dashboards.
Modern tools (e.g., Metaplane, OpenMetadata) have made this more visible, but without a metadata-driven foundation, end-to-end lineage is often incomplete or brittle.
Why Metadata-Driven Platforms Make Lineage Work
Reverse-engineering lineage after the fact is hard and fragile.
A metadata-driven, model-driven approach flips the script:
- Model once — use business-oriented data models and business-friendly mappings
- Generate SQL, orchestration, diagrams, and lineage from the same metadata.
- Keep it alive — update metadata → regenerate → publish to catalogs or Confluence.
This delivers:
- ✅ Horizontal lineage from layer mappings.
- ✅ Vertical lineage from column-level mapping.
- ✅ End-to-end lineage by connecting ingestion → transformation → delivery.
- ✅ Traceability linking each functional step back to the actual SQL.
Instead of messy reverse engineering, lineage becomes an intentional design artifact — always up to date and business-friendly.
This is the philosophy behind my work in:
- From Coding to Modeling: The Rise of Model-Driven Data Engineering
- Business-Friendly Mapping in Action
- Metadata-Driven Data Architecture with draw.io & VSCode
- Data Contracts: Anchoring Reliability in Model-Driven Data Engineering
Key Takeaways
- Horizontal vs. Vertical = flow across systems vs. column derivation.
- Technical vs. Functional = code parsing vs. business-friendly mapping.
- End-to-end lineage = full chain from sources to dashboards — horizontal + vertical combined.
- Metadata-driven design is the only sustainable way to keep lineage trustworthy, understandable, and up to date.
- Your users don’t want 500-line SQL graphs — they want a clear, functional story of where data comes from.
References & Further Reading
- Olga Maydanchik — perspectives on horizontal & vertical lineage
- Han Yu — Introduction to Data Lineage, Data Governance, and Data Dictionary
- Metaplane Docs — End-to-End Lineage and dbt exposures
- From Coding to Modeling: The Rise of Model-Driven Data Engineering
- Cooking with Data: How Business-Friendly Mapping Brings Clarity to ETL
- Automating draw.io Diagram Generation from Metadata
- Metadata-Driven Data Architecture with draw.io & VSCode
- Data Contracts: Anchoring Reliability in Model-Driven Data Engineering