🧠 Inside the De Volksbank / ASN Metadata Architecture

How Functional Transformation Modeling United Logic, Lineage, and Requirements — a Decade Ahead of Its Time

Summary

“Before YAML, before DBT, before Coalesce —
we were already modeling transformations as metadata.”

In the previous article, we explored the marriage of data models and data movement, and how modelers and engineers finally began speaking the same language. This story now goes deeper — into a real-world architecture that quietly achieved that unity long before the modern data stack existed.

At De Volksbank (now ASN), the data modeling team extended PowerDesigner beyond its traditional boundaries — transforming it from a schema design tool into a metadata-driven engineering platform.

What emerged was a system that:

• Modeled data transformations step by step in logical form,
• Generated SQL code and unit tests directly from metadata,
• Linked requirements, mappings, and lineage in one environment,
• And enabled a culture where everyone became a data modeler.

🏗️ PowerDesigner as a Metadata Platform

For most organizations, PowerDesigner was used to model data structures — conceptual, logical, and physical. But at Volksbank, it became the foundation for data movement modeling — using extended metamodels and custom stereotypes.

The Innovation

We customized the Logical Data Model (LDM) to include functional transformation logic. Each entity in the LDM could represent not only data at rest but also data in motion.

We introduced new entity stereotypes:

Press enter or click to view image in full size

Each transformation step (such as select, derive, filter, aggregate, pivot, or expose) was modeled functionally using mapping objects inside PowerDesigner. No SQL was written by hand — SQL was generated.

🔁 Modeling Transformations Functionally

Instead of embedding logic in stored procedures or ETL jobs, we expressed it in metadata form.

Example — A Functional Mapping

Target: BUSINESS_AGREEMENT
Source: SOURCE_ACCESS_AGREEMENT
Steps:
1. SELECT agreement_id, customer_id, amount, start_date
2. DERIVE agreement_type = CASE WHEN loan_flag=1 THEN ‘MORTGAGE’ ELSE ‘INVESTMENT’ END
3. FILTER start_date >= ‘2018-01-01’
4. AGGREGATE SUM(amount) BY customer_id

Each operation corresponded to a function stereotype, defined within PowerDesigner’s metamodel:

• FilterFunction
• ScalarFunction
• WindowFunction

By modeling transformations this way, we could:

• Validate them visually,
• Apply naming and lineage rules, and
• Generate executable SQL through Jinja-like templates.

⚙️ From Metadata to Code

The transformation logic was exported from PowerDesigner and rendered into executable SQL. A Jinja template engine generated SQL views or stored procedures for each entity type.

Example (simplified):

– {{ entity.name }}
SELECT
{{ select_clause }},
{{ derive_clause }},
{{ filter_clause }}
FROM {{ source_entity }}
{% if aggregate_clause %}
GROUP BY {{ aggregate_clause }}

Each stereotype controlled how a portion of SQL was emitted. For example, a BusinessEntity might generate a view, while an AggregateEntity generated a table.

Outcome: modeling became code generation — and PowerDesigner became a compiler for transformations.

📚 Linking Requirements (RQM)

One of the boldest design decisions was to connect Logical Data Models directly to Requirement Models (RQM) within PowerDesigner. Each entity, attribute, and mapping was linked to one or more business requirements.

Benefit: end-to-end traceability — from business requirement to physical implementation.

When stakeholders asked:

“Where is this requirement implemented in the data warehouse?”

we could trace it through the model itself, not through a separate document.

🧬 Inheritance and Subtypes — Semantic Unification

To unify data from multiple sources under shared semantics, we leveraged inheritance.

We distinguished between:

• Functional subtypes → conceptual variations, e.g.
  MORTGAGE_AGREEMENT, INVESTMENT_AGREEMENT
• Source subtypes → physical variants per source, e.g.
  MORTGAGE_AGREEMENT_SOURCE_A, MORTGAGE_AGREEMENT_SOURCE_B

These were both subtypes of a common supertype — AGREEMENT.

Because both source subtypes inherited from MORTGAGE_AGREEMENT, the system could automatically generate UNION logic to integrate them:

SELECT * FROM MORTGAGE_AGREEMENT_SOURCE_A
UNION ALL
SELECT * FROM MORTGAGE_AGREEMENT_SOURCE_B

The source access models handled raw-to-logical transformation, preparing each source for mapping onto the business LDM.
The business model unified those sources through inheritance and metadata-driven SQL generation.

The result: semantic integration and physical consolidation — driven entirely by metadata.

🧪 Test Scenarios as Metadata

We also modeled test cases directly inside PowerDesigner.

Each mapping could have one or more test scenarios, including expected input-output pairs or conditions. These were exported and transformed into Gherkin / SpecFlow unit tests:

Feature: Agreement mapping validation
Scenario: Verify derived agreement type
Given an agreement with loan_flag=1
When the transformation runs
Then the agreement_type should be ‘MORTGAGE’

Tests and SQL were generated from the same metadata source.
If the model changed, tests evolved automatically.

🧭 Model Checks and Validation Rules

The LDMs included custom validation rules:

• Mandatory attributes for certain entity types
• Conformance of naming conventions
• Allowed transformation types per entity stereotype
• Requirement-to-attribute coverage checks

These ensured the model was not only consistent but also governed by logic.

The model didn’t just describe the data — it policed its own quality.

👥 Everyone Became a Data Modeler

Perhaps the most profound change wasn’t technical — it was organizational.

By expressing transformations as metadata:

• Engineers, analysts, and architects all worked in the same logical model.
• Business users could understand transformation flows visually.
• Developers focused on automation templates rather than hard-coded SQL.

“The engineers built the templates; the modelers built the logic.”
Together, they co-authored the data warehouse.

This approach blurred the line between data modeling and data engineering — laying the foundation for today’s metadata-driven development.

🕰️ A Decade Ahead of Its Time

Looking back, the Volksbank / ASN implementation anticipated nearly every major trend of the modern data stack:

PowerDesigner became the DBT + Coalesce + Great Expectations + Confluence of its time — wrapped inside one integrated metadata ecosystem.

❤️ The Legacy

The Volksbank approach wasn’t just about automation — it was about cognition. It allowed data teams to see, reason, and act on metadata as a single living model.

Today, as we move to open environments like VS Code and YAML-based modeling, we’re not reinventing the idea — we’re reviving it.

📅 Coming Next

In Part 3, we’ll rebuild this architecture in modern form:
🧰 Building the Integrated VS Code Environment — Where Modelers and Engineers Live Under One Roof.

We’ll explore:

• Folder structures and schemas for metadata-as-code
• YAML entity definitions and transformation specs
• Jinja macros for inheritance and unification
• AI-assisted modeling and validation
• And how VS Code becomes the new data house — with AI as the building crew.

Stay tuned — the marriage now gets a modern home. 🏡