Jaco van der Laan

Rethinking EA Mapping: Building a Business-Friendly Mapping Capability with DuckDB, Python, and Notion

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

article base/modeling-tools duckdb metadata
‹ ›

Rethinking EA Mapping- Building a Business-Friendly Mapping Capability with DuckDB, Python, and Notion

From documentation to execution: how to reinvent EA Mapping with modern tools for business-friendly, metadata-driven data engineering.

Summary

Enterprise Architect (EA) Mapping from Bellekens provides a free and open-source way to manage mappings between models inside Enterprise Architect. It supports many-to-many mappings, mapping logic documentation, CSV export/import, and even COBOL copybook imports. While conceptually aligned with model-driven data engineering and business-friendly mapping, its practical use is limited by its dependency on Enterprise Architect, a tool that is often too technical and closed for the modern data stack.

This raises a natural question:

What if we could build an EA Mapping–like capability directly on the modern data stack — with DuckDB, Python, and Notion — so that it aligns with metadata-as-data, business-friendly mapping, and executable pipelines?

Why Rethink EA Mapping for the Modern Data Stack?

1. Metadata as Data

In today’s data platforms, metadata should be stored and queried like any other dataset. EA Mapping keeps metadata inside Enterprise Architect or CSVs, while DuckDB would let us store mappings as structured, queryable tables, ready to integrate with orchestration, lineage, and governance.

2. Business-Friendly Mapping

Our goal is to make mappings understandable to business users. UML diagrams inside EA are often inaccessible. A Notion-based mapping interface could democratize access — allowing business analysts to annotate mappings, transformations, and logic in plain language while still keeping them connected to the technical metadata.

3. Executable Integration

Mappings are not just documentation — they are the blueprint for ETL and integration. With Python + DuckDB, mappings can directly generate SQL, dbt models, or Databricks notebooks. This brings model-driven data engineering to life.

Sketching the Solution: EA Mapping in DuckDB + Python + Notion

1. Core Data Model in DuckDB

We define mapping tables in DuckDB:

CREATE SCHEMA gdrive;

CREATE TABLE gdrive.mappings (
    mapping_id     INTEGER,
    source_path    TEXT,
    target_path    TEXT,
    mapping_logic  TEXT,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This mirrors the EA Mapping CSV format (Source Path, Target Path, Mapping Logic), but puts it in a relational structure. Now mappings are queryable metadata assets.

2. Integration with Notion for Business-Friendly Views

  • A Notion database holds mapping records (linked to DuckDB rows).
  • Business users can:
  • Describe transformations in natural language.
  • Attach business definitions to source and target fields.
  • Track mapping approval workflows.

This gives business context + technical metadata in one interface.

3. Python for Import, Export, and Code Generation

Python scripts orchestrate the flow:

  • Import source models (from JSON, XML, DDL, Cobol Copybooks, or Databricks schemas).
  • Generate initial mapping stubs in DuckDB.
  • Export mappings as CSVs, dbt YAML, or SQL templates.
  • Generate ETL logic: Python can translate mapping logic into executable SQL transformations (e.g., SELECT with transformations, joins, fixed values).

4. Historization & Exposure

Since DuckDB can easily handle SCD2 logic and historization tables, mappings could include versioning metadata:

ALTER TABLE gdrive.mappings ADD COLUMN valid_from TIMESTAMP;
ALTER TABLE gdrive.mappings ADD COLUMN valid_to TIMESTAMP;

This supports dual-SCD2 historization and aligns with your work on query assist tables and exposure views.

5. Governance & Lineage

  • Lineage can be derived by querying mappings (source → target dependencies).
  • Governance rules (e.g., PII handling, transformations) can be enforced by tagging mapping_logic fields.
  • Metadata versioning ensures that mappings evolve under control.

Benefits Over EA Mapping

Conclusion

EA Mapping is a useful proof of concept for structured mapping management, but it belongs to a previous generation of modelling tools. By reimagining it with DuckDB (for metadata), Python (for automation), and Notion (for business context), we can create a truly modern, business-friendly mapping framework:

  • Accessible to business analysts and engineers alike.
  • Integrated into the data platform, not siloed in a modelling tool.
  • Capable of generating executable SQL/ETL pipelines.
  • Governed as metadata, with lineage, versioning, and historization.

This approach embodies model-driven data engineering in practice — bringing your Medium concepts (business-friendly mapping, metadata-as-data, historization, source access views) into a single, executable architecture.