Jaco van der Laan

Roadmaps, Not Pipelines — Business‑Friendly Mapping from Sources to Targets

28/09/2025  ·  Data Architecture  ·  Data Engineering  ·  Data Modeling

dbt metadata
‹ ›

Turn your source‑to‑target mappings into living diagrams — automatically.

Summary

  • Treat mappings as data. Generate draw.io diagrams from metadata so business and engineering see the same story.
  • Use a roadmap metaphor: roads (flows), stops (transformations), start/destination cities (source/target).
  • Keep it vendor‑neutral but compatible with tools like draw.io/VSCode, dbt, OpenMetadata, and frameworks such as CrossBreeze.
  • Optionally export SVG and add light animation for presentations and Confluence pages.

Why this matters (and how it fits our series)

In our work on Business‑Friendly Mapping and Model‑Driven Data Engineering, we’ve argued that the fastest way to align teams is to make the model the product and the metadata the single source of truth. This article shows a pragmatic pattern: take the mappings you already maintain and render them as readable, navigable roadmaps — automatically.

This extends themes from:

  • From Coding to Modeling: The Rise of Model‑Driven Data Engineering
  • Metadata as Data — Controlling and Governing Data Models
  • Metadata‑Driven Data Architecture: Leveraging draw.io and VSCode for Scalable Design
  • Business‑Friendly Mapping in Action — From Sources to Integration
  • Business‑Friendly Mapping Meets dbt: Virtualizing Logical Building Blocks into SQL

The promise: one artifact (your mapping metadata) that powers SQL generation and human‑friendly diagrams — no more drifting PowerPoints.

The roadmap metaphor

  • Road = the path of data from source to target.
  • Stops = transformations (select, filter, derive, join, union, pivot/unpivot, aggregate).
  • Start city = source system or access view.
  • Destination city = target table/view/data product.
  • Road styles = semantics (batch vs. streaming; raw→bronze→silver→gold; operational vs. analytic).

Why this metaphor works:

  • It’s instantly legible to business users.
  • It maps 1:1 to lineage for governance.
  • It’s easy to animate without changing the underlying metadata.

From metadata to map (the 3‑step loop)

  1. Author mappings as metadata (YAML/CSV/DuckDB table). Each flow lists source, ordered transformations, and target.
  2. Generate a diagram (draw.io XML). Each transformation becomes a node; arrows connect nodes in order.
  3. Publish to Confluence/GitHub Pages as PNG/SVG — and optionally add light animation.

Because the diagram is generated, you can regenerate on every commit. No manual diagram drift.

A minimal, vendor‑neutral schema

Keep it boring and explicit:

flows:
– name: Customer to Dim
source: crm.customer
transformations:
– select: [id, name, birthdate]
– filter: “birthdate IS NOT NULL”
– derive: “age = datediff(year, birthdate, current_date)”
target: dwh.customer_dim

This structure is easy to produce from dbt, SQL lineage, or a modeling framework. Add more ops as needed.

Shapes that carry meaning

Map transformations to consistent shapes and very short labels:

Styling tips:

  • Color by layer (raw/bronze/silver/gold) or by purpose (curation vs. integration).
  • Keep labels two lines max; truncate long expressions with ….
  • Use uniform spacing so your eyes follow the road left→right.

The generator (ready to use)

We provide a Python script that:

  • Reads the YAML schema above.
  • Emits a .drawio file you can open in diagrams.net.
  • Uses semantic shapes per operation and draws a clean left→right “road”.

CLI

pip install pyyaml
python drawio_flow_generator.py –in mappings.yaml –out flows.drawio –name “Business Flows”

Pro‑tips

  • Check the STYLE_* constants to align colors with your brand.
  • Add ops by extending LABEL_PREFIX and STYLE_BY_OP.
  • Group related flows in separate pages (one page per domain) for clarity.

Framework compatibility: You can source the YAML from dbt, CrossBreeze, or your own metadata store (DuckDB/SQLite/Postgres). The approach stays vendor‑neutral.

Bringing it into VSCode & Confluence

  • VSCode: keep mappings.yaml and the generated .drawio together. With the draw.io extension, diffs and previews are trivial.
  • Confluence: export PNG for static pages; export SVG to enable tooltips and CSS animations. Embed a small legend once per space.
  • OpenMetadata: link flows to their tables/views; reference the diagram per dataset. The YAML is a perfect seed for lineage.

Make it move (optional animation)

If you export SVG from the generator, you can animate the “road” in 2–10 lines of JavaScript:

  • Line‑draw the path on load (e.g., Vivus or a few stroke-dasharray tweaks).
  • Pulse a dot along the road to show execution.
  • Hover to reveal SQL snippets for each stop.

This is perfect for demos and onboarding without changing the metadata model.

Governance & data contracts

Roadmaps aren’t just pretty pictures. They are evidence:

  • Every stop is a controlled transformation — traceable back to a contract or modeling rule (see: Data Contracts: Anchoring Reliability…).
  • Publishing to Confluence makes the lineage discoverable, not just queryable.
  • Consistent shapes enable automated checks (e.g., a rule that a union must be preceded by a schema‑harmonization step).

Anti‑patterns to avoid

  • Hand‑drawn flows that diverge from code. Your diagram should be compiled from the source of truth.
  • Over‑labeling every step with full SQL. Put detail in tooltips or the repo; keep the diagram readable.
  • Mixing concerns (business rules, physical performance hints, and deployment metadata in one lane). Keep views: logical → integration → serving distinct.

Implementation blueprint

  1. Metadata store (DuckDB/SQLite/Postgres) with a flows table or YAML files in Git.
  2. Generator (the Python script) that produces .drawio and/or .svg on CI.
  3. Publisher that drops artifacts into Confluence/GitHub Pages with a legend and index.
  4. Runtime hooks (optional) to update status badges on the diagram after each run.

FAQ

How do we show joins from multiple sources?
Use parallel roads that converge into a JOIN stop. If you need details, render a sub‑flow page focused on the join.

What about historization (SCD2, validity)?
Model this as distinct stops (e.g., DERIVE valid_from/to, AGG collapse to latest) or as a dedicated Historize icon.

Batch vs. streaming?
Use line styles: solid = batch; dashed = streaming. Or add tiny badges on the road.

dbt or frameworks like CrossModel?
Treat them as producers/consumers of the same mappings. Your diagrams compile from the metadata either way.

Further reading in this series

  • From Coding to Modeling: The Rise of Model‑Driven Data Engineering
  • How Model‑Driven Data Engineering, Business‑Friendly Mapping, and Business‑Oriented Data Modeling Reinforce Each Other
  • Publish Your Metadata Story to Confluence

Appendix A — YAML schema (extensible)

flows:
  - name: <string>
    source: <system.object>
    transformations:
      - select: [<col>, <col>]
      - filter: <expr>
      - derive: <expr>
      - join: <table or key>
      - union: <table or pattern>
      - pivot: <expr>
      - unpivot: <expr>
      - aggregate: <expr>
    target: <system.object>

Appendix B — CLI quickstart

pip install pyyaml
python drawio_flow_generator.py --in mappings.yaml --out flows.drawio --name "Business Flows"

Design note: Use a flat, clean visual style for icons and minimal color. Keep text high‑contrast and legible in dark and light themes.