How we translate a financial services integration model into SQL and tests on Databricks.
Summary
After converging AnaCredit, RRE, and BIRD into a Generic Financial Services LDM, the next step is making it executable.
With GenAI, we can generate dbt scaffolding — sources, staging, integration views, and tests. This makes the LDM not just a theoretical artifact but a living data product.
Press enter or click to view image in full size
Key Takeaways
- A Generic LDM is only valuable if we can operationalize it.
- GenAI assists in generating dbt models, tests, and documentation.
- Databricks SQL provides the perfect execution platform.
- The result is transparent, testable, and business-friendly mappings.
- This closes the loop between business modeling and engineering execution.
From Model to Code
I often describe my approach as model-driven data engineering — the model is the blueprint, dbt is the engine.
How can we turn a logical model into executable code?
- Sources: define the raw landing tables (e.g., AnaCredit, RRE, BIRD extracts).
- Staging: standardize and rename columns to follow the LDM.
- Integration Layer: create dbt models for entities like Counterparty, Instrument, Property.
- Business-Friendly Mappings: express the joins and transformations in clear SQL.
- Tests + Docs: enforce rules and document semantics.
This is what I meant when I wrote about Business-Friendly Mapping Meets dbt: dbt brings logical data models to life.
Example dbt Sources (YAML)
version: 2
sources:
– name: anacredit
schema: raw_anacredit
tables:
– name: counterparty
– name: instrument
– name: rre
schema: raw_rre
tables:
– name: property
– name: valuation
– name: bird
schema: raw_bird
tables:
– name: party
– name: group
– name: position
Example Staging Model (Instrument)
– stg__instrument.sql
select
instrument_identifier as instrument_id,
type_code,
currency,
original_amount as orig_amount,
outstanding_nominal
from {{ source(‘anacredit’,’instrument’) }};
We start by renaming and cleaning raw data into a business-shaped staging layer.
Integration View (Counterparty)
– ldm__counterparty.sql
select distinct
c.counterparty_id,
coalesce(c.lei, p.lei) as lei,
coalesce(c.name, p.party_name) as name,
coalesce(c.country_code, p.country_code) as country,
c.sector_code
from {{ ref(‘stg__counterparty’) }} c
left join {{ ref(‘stg__party’) }} p
on c.counterparty_id = p.party_id;
Here, I unify AnaCredit’s Counterparty with BIRD’s Party. This is one of the points where I personally see GenAI providing huge value: it helps propose join strategies that would normally take hours to model and document.
dbt Tests (example)
version: 2
models:
– name: ldm__counterparty
columns:
– name: counterparty_id
tests:
– not_null
– unique
– name: country
tests:
– relationships:
to: ref(‘ref__country’)
field: country_code
We use tests to enforce integrity and reference conformity — principles I emphasized in Data Contracts: Anchoring Reliability in Model-Driven Data Engineering.
Why dbt + GenAI Works
- dbt provides modular, testable SQL and clear lineage.
- GenAI accelerates scaffolding, documentation, and mapping sheets.
- Together, they make model-driven data engineering practical and scalable.
This is the bridge between design and delivery that I described in From Coding to Modeling.
What’s Next
In the next article, we’ll dive into business-friendly mapping sheets generated with GenAI. These show how source attributes from AnaCredit, RRE, and BIRD map into the Generic LDM — expressed in plain language that both business and IT can follow.