Mapping Bank Data to COREP Templates — the dbt SQL That Makes It Work

📅 Day 7 of 18 · COREP Governance Pipeline Series · Estimated reading time: 18 minutes

Day 6 showed how dbt run automatically emits column-level lineage to Marquez. Today we go inside the SQL itself. We will walk through every mart model line by line — the four dbt models that transform aggregated intermediate data into the exact row and column layout required by the EBA Data Point Model 4.0 for the four COREP templates in scope: C 01.00, C 02.00, C 03.00, and C 47.00.

By the end you will understand:

• How the EBA DPM organises templates into rows (r) and columns (c) and why that structure drives your SQL column names
• The exact SQL for all four COREP mart models with inline EBA concept ID comments
• Why NULLIF, ROUND, and CROSS JOIN appear in regulatory SQL in ways you would never write in a product database
• How the mart column names connect directly to the mart_to_xbrl_mapping.yaml file the XBRL generator reads on Day 11
• The five dbt schema tests that enforce Basel III ratio floors as code — not documentation

How the EBA DPM Defines a Template

Every COREP template in the EBA Data Point Model 4.0 is a two-dimensional grid. Rows are identified by an r-code (r010, r020, …) and columns by a c-code (c010, c020, …). A data point is the intersection: r010c010 is row 10, column 10.

In the EBA XBRL taxonomy, each data point has a full concept identifier like ei:c1r010c010 where:

Our dbt mart column naming convention mirrors this directly: r010_cet1_capital maps to ei:c1r010c010. The human-readable suffix (_cet1_capital) is for engineers; the EBA concept ID in the YAML mapping is for the XBRL generator. Both live in the project and stay in sync.

Template C 01.00 — Own Funds: the Capital Stack

C 01.00 is the simplest of the four templates: a single column of amounts representing the bank’s capital structure from highest quality (CET1) to total own funds. Every row is a sum — no ratios, no cross-table divisions.

Notice that r060_total_rwa appears in C 01.00 even though it is an RWA figure, not a capital figure. The EBA DPM places total RWA in C 01.00 because it is used as the denominator for all ratios in C 03.00 — and the regulator wants it sourced from the same template as the numerator for cross-validation purposes.

-- dbt/models/mart/corep_c0100.sql
-- C 01.00 Own Funds — EBA DPM 4.0
-- Single output row. All amounts EUR millions, rounded to 3dp (decimals=-3).

with capital as (

    select * from {{ ref('int_capital_by_tier') }}

),

rwa as (

    select * from {{ ref('int_rwa_by_exposure_class') }}

),

own_funds as (

    select
        -- r010: CET1 Capital → ei:c1r010c010
        sum(case when tier = 'CET1' then total_amount else 0 end)
            as r010_cet1_capital,

        -- r020: AT1 instruments → ei:c1r020c010
        sum(case when tier = 'AT1'  then total_amount else 0 end)
            as r020_at1_instruments,

        -- r030: Tier 1 = CET1 + AT1 → ei:c1r030c010
        sum(case when tier in ('CET1', 'AT1') then total_amount else 0 end)
            as r030_tier1_capital,

        -- r040: T2 instruments → ei:c1r040c010
        sum(case when tier = 'T2'   then total_amount else 0 end)
            as r040_t2_instruments,

        -- r050: Total Own Funds = all tiers → ei:c1r050c010
        sum(total_amount)
            as r050_total_own_funds

    from capital

),

rwa_total as (

    -- r060: Total RWA — placed in C 01.00 per EBA DPM cross-validation rule
    select sum(rwa) as r060_total_rwa from rwa

)

select
    round(o.r010_cet1_capital,    3) as r010_cet1_capital,
    round(o.r020_at1_instruments, 3) as r020_at1_instruments,
    round(o.r030_tier1_capital,   3) as r030_tier1_capital,
    round(o.r040_t2_instruments,  3) as r040_t2_instruments,
    round(o.r050_total_own_funds, 3) as r050_total_own_funds,
    round(r.r060_total_rwa,       3) as r060_total_rwa

from own_funds o
cross join rwa_total r

The CROSS JOIN on the last line is intentional and correct. Both CTEs return exactly one row each. CROSS JOIN of two single-row results is the standard SQL idiom for combining two independent scalar computations into a single output row — which is what the EBA template requires.

Template C 02.00 — Capital Requirements by Exposure Class

C 02.00 is a multi-row template: one row per exposure class. Each row shows the RWA and the 8% minimum capital requirement for that class. This is where the CASE WHEN exposure_class = 'corporate' logic lives.

-- dbt/models/mart/corep_c0200.sql
-- C 02.00 Capital Requirements by Exposure Class — EBA DPM 4.0
--
-- Output: one row per exposure class + one TOTAL row.
-- c010 = RWA for that class.
-- c020 = 8% minimum capital requirement (CRR Article 92(1)(a)).

with rwa_by_class as (

    select * from {{ ref('int_rwa_by_exposure_class') }}

),

-- Build the detail rows (one per exposure class)
detail_rows as (

    select
        exposure_class,
        round(sum(rwa),             3) as rwa_amount,
        round(sum(rwa) * 0.08,      3) as capital_requirement

    from rwa_by_class
    group by exposure_class

),

-- Build the TOTAL row — r010 in the EBA template
total_row as (

    select
        'TOTAL'                         as exposure_class,
        round(sum(rwa_amount),      3)  as rwa_amount,
        round(sum(rwa_amount)*0.08, 3)  as capital_requirement
    from detail_rows

),

combined as (
    select * from detail_rows
    union all
    select * from total_row
)

select
    exposure_class,
    rwa_amount          as c010_rwa,
    capital_requirement as c020_capital_requirement

from combined
order by
    case exposure_class
        when 'TOTAL'        then 1
        when 'corporate'    then 2
        when 'retail'       then 3
        when 'sovereign'    then 4
        when 'institution'  then 5
        else 6
    end

The ORDER BY CASE at the bottom matches the EBA template row sequence. This matters for the XBRL generator on Day 11: it iterates the mart rows in order and assigns them to r010, r020, etc. in sequence. Wrong order → wrong EBA concept IDs in the XBRL file.

Template C 03.00 — Capital Ratios: Where NULLIF Saves the Pipeline

C 03.00 is the most dangerous model to get wrong. It computes the four regulatory ratios that determine whether a bank is capitalised adequately. Every column is a division. Every division has a potential zero denominator.

There are two SQL patterns that appear in this model that are unusual outside regulatory reporting:

Pattern 1: NULLIF for zero-denominator protection

-- Without NULLIF: if total_rwa = 0, this raises a division-by-zero error
-- and the entire pipeline fails with a cryptic Postgres error.
cet1 / total_rwa

-- With NULLIF: if total_rwa = 0, the result is NULL.
-- dbt test catches NULL as a data quality failure — not a pipeline crash.
-- The error message is "cet1_ratio is null" which is actionable.
cet1 / nullif(total_rwa, 0)

Pattern 2: ROUND to 6 decimal places for ratio precision

-- EBA DPM specifies decimals=4 for ratio data points (0.0001 precision).
-- We store 6dp internally so the XBRL generator can truncate to 4dp
-- without rounding twice (double-rounding artefacts change the last digit).
round(cet1 / nullif(total_rwa, 0), 6) as cet1_ratio

-- dbt/models/mart/corep_c0300.sql
-- C 03.00 Capital Adequacy Ratios — EBA DPM 4.0
--
-- All ratios stored as decimals (0.142 = 14.2%).
-- XBRL generator converts to percentage representation if required by taxonomy.

with capital as (

    select * from {{ ref('int_capital_by_tier') }}

),

rwa as (

    select * from {{ ref('int_rwa_by_exposure_class') }}

),

-- Aggregate capital totals — one scalar per tier
capital_totals as (

    select
        sum(case when tier = 'CET1'
                 then total_amount else 0 end)              as cet1,

        sum(case when tier in ('CET1', 'AT1')
                 then total_amount else 0 end)              as tier1,

        sum(total_amount)                                   as total_own_funds

    from capital

),

-- Total RWA — single scalar
rwa_total as (

    select sum(rwa) as total_rwa from rwa

),

-- Total exposure measure for leverage ratio
-- CRR2 Art. 429: simplified as total_rwa / 0.08 (inverse of 8% SA floor)
-- Full Basel IV leverage exposure calculation is out of scope here.
exposure_measure as (

    select sum(ead) as total_exposure from rwa

)

select
    -- r010: CET1 ratio → ei:c3r010c010
    round(c.cet1           / nullif(r.total_rwa, 0), 6)  as cet1_ratio,

    -- r020: Tier 1 ratio → ei:c3r020c010
    round(c.tier1          / nullif(r.total_rwa, 0), 6)  as tier1_ratio,

    -- r030: Total Capital ratio → ei:c3r030c010
    round(c.total_own_funds / nullif(r.total_rwa, 0), 6) as total_capital_ratio,

    -- r040: Leverage ratio → ei:c3r040c010
    -- Tier 1 / Total Exposure Measure (CRR2 Article 429)
    round(c.tier1          / nullif(e.total_exposure, 0), 6) as leverage_ratio

from capital_totals c
cross join rwa_total r
cross join exposure_measure e

Template C 47.00 — LCR: the Most Complex Calculation

C 47.00 is the Liquidity Coverage Ratio template. It is the most structurally complex of the four because it combines three distinct computation chains:

1. HQLA buffer — haircut-adjusted market values of liquid assets, split by level
2. Net outflows — stress-weighted contractual outflows over 30 days
3. LCR ratio — HQLA buffer ÷ net outflows, must be ≥ 100%

-- dbt/models/mart/corep_c4700.sql
-- C 47.00 Liquidity Coverage Ratio — EBA DPM 4.0
--
-- LCR = HQLA Buffer / Total Net Outflows over 30-day stress period
-- Minimum requirement: 100% (ratio stored as 1.0 not 100%)

with hqla as (

    select * from {{ ref('int_lcr_hqla') }}

),

outflows as (

    select * from {{ ref('int_lcr_outflows') }}

),

-- Aggregate HQLA buffer by level
hqla_totals as (

    select
        -- r020: Level 1 assets (sovereign bonds, central bank reserves)
        -- 0% haircut — adjusted_value = market_value
        round(sum(case when hqla_level = '1'
                       then adjusted_value else 0 end), 3)  as hqla_level1,

        -- r030: Level 2A assets (covered bonds, agency MBS)
        -- 15% haircut already applied in int_lcr_hqla
        round(sum(case when hqla_level = '2A'
                       then adjusted_value else 0 end), 3)  as hqla_level2a,

        -- r040: Level 2B assets (corporate bonds IG, equity)
        -- 25-50% haircut already applied in int_lcr_hqla
        round(sum(case when hqla_level = '2B'
                       then adjusted_value else 0 end), 3)  as hqla_level2b,

        -- r010: Total HQLA buffer = sum of all levels
        round(sum(adjusted_value), 3)                       as hqla_buffer

    from hqla

),

-- Net outflows: stressed cash outflows over 30-day scenario
-- stress_rate applied in int_lcr_outflows
outflow_total as (

    select round(sum(stressed_outflow), 3) as total_net_outflows
    from outflows

)

select
    h.hqla_level1,                                           -- ei:c47r020c010
    h.hqla_level2a,                                          -- ei:c47r030c010
    h.hqla_level2b,                                          -- ei:c47r040c010
    h.hqla_buffer,                                           -- ei:c47r010c010
    o.total_net_outflows,                                    -- ei:c47r200c010

    -- r300: LCR ratio → ei:c47r300c010
    -- NULLIF prevents division-by-zero on empty outflows table
    -- Stored as decimal: 1.48 = 148%
    round(h.hqla_buffer / nullif(o.total_net_outflows, 0), 6)
        as lcr_ratio                                         -- ei:c47r300c010

from hqla_totals h
cross join outflow_total o

The Mapping File: How mart Columns Become XBRL Concepts

The mart models produce human-readable column names. The XBRL generator on Day 11 needs EBA concept IDs. The bridge between them is mart_to_xbrl_mapping.yaml. Here is the structure for C 03.00:

# mart_to_xbrl_mapping.yaml — excerpt for C 03.00
# The XBRL generator reads this file and maps each mart column
# to its EBA DPM 4.0 concept identifier, unit, and decimal precision.

c0300:
  source_table: mart.corep_c0300
  template_code: C 03.00
  context_ref: ctx_reporting_period
  columns:
    - mart_column:  cet1_ratio
      concept_id:   ei:c3r010c010
      unit:         pure        # ratios have unit="pure", not EUR
      decimals:     4           # EBA requires 4dp for ratio data points
      nil_allowed:  false

    - mart_column:  tier1_ratio
      concept_id:   ei:c3r020c010
      unit:         pure
      decimals:     4
      nil_allowed:  false

    - mart_column:  total_capital_ratio
      concept_id:   ei:c3r030c010
      unit:         pure
      decimals:     4
      nil_allowed:  false

    - mart_column:  leverage_ratio
      concept_id:   ei:c3r040c010
      unit:         pure
      decimals:     4
      nil_allowed:  false

Three things in this YAML that are easy to get wrong:

• unit: pure vs unit: EUR — ratio data points use pure in XBRL. If you set EUR on a ratio, Arelle’s EBA formula validator will reject the instance document with a unit mismatch error.
• decimals: 4 for ratios vs decimals: -3 for monetary amounts — negative decimals means “rounded to thousands”. A decimal of -3 on an amount of 6341.0 tells the XBRL processor the value is accurate to the nearest €1,000.
• nil_allowed: false — ratio data points in C 03.00 are mandatory in the EBA DPM. If cet1_ratio is NULL (because NULLIF hit a zero denominator), the XBRL generator must raise an error, not silently emit a nil-tagged element.

The dbt Schema Tests That Enforce Regulatory Floors

These tests run on every dbt test execution — automatically after every dbt run in the pipeline. A failure causes QualityGateError and Airflow branches to notify_failure.

# dbt/models/schema.yml — mart layer tests (full definition)

models:

  - name: corep_c0100
    description: "C 01.00 Own Funds — EBA DPM 4.0"
    columns:
      - name: r010_cet1_capital
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "> 0"
      - name: r050_total_own_funds
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              # Total own funds must exceed CET1 (it includes AT1 + T2)
              expression: ">= r010_cet1_capital"

  - name: corep_c0300
    description: "C 03.00 Capital Adequacy Ratios — EBA DPM 4.0"
    columns:
      - name: cet1_ratio
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0.045"   # Basel III / CRR Article 92(1)(a)

      - name: tier1_ratio
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0.06"    # Basel III / CRR Article 92(1)(b)

      - name: total_capital_ratio
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0.08"    # Basel III / CRR Article 92(1)(c)

      - name: leverage_ratio
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0.03"    # CRR2 Article 92(1)(d)

  - name: corep_c4700
    description: "C 47.00 LCR — EBA DPM 4.0"
    columns:
      - name: lcr_ratio
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 1.0"     # CRD IV Article 412 / Del. Reg. 2015/61

      - name: hqla_buffer
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "> 0"

Running the Full Transform and Verifying in Trino

# Via the pipeline CLI
python pipeline.py --module transform

# Or directly
cd dbt/
dbt run  --profiles-dir .
dbt test --profiles-dir .

# Verify all four mart tables via Trino (localhost:8080)
-- Capital ratios
SELECT cet1_ratio, tier1_ratio, total_capital_ratio, leverage_ratio
FROM postgresql.mart.corep_c0300;

-- LCR components
SELECT hqla_level1, hqla_level2a, hqla_level2b,
       hqla_buffer, total_net_outflows, lcr_ratio
FROM postgresql.mart.corep_c4700;

-- Own Funds stack
SELECT r010_cet1_capital, r030_tier1_capital,
       r050_total_own_funds, r060_total_rwa
FROM postgresql.mart.corep_c0100;

-- RWA by exposure class
SELECT exposure_class, c010_rwa, c020_capital_requirement
FROM postgresql.mart.corep_c0200
ORDER BY exposure_class;

Every query goes through Trino — never directly to PostgreSQL. This is enforced by design: Trino is the single SQL access layer, and Apache Ranger (Day 10) attaches its column-masking and row-filter policies to Trino connections. Querying PostgreSQL directly bypasses Ranger entirely.

The Full Data Point Inventory Across All Four Templates

26 data points total across four templates. In a full COREP submission a bank files hundreds of templates with thousands of data points. But the architecture is identical at any scale — one mart model per template, one column per data point, one YAML mapping entry per column.

The Governance Observation: SQL as a Regulatory Audit Trail

Every regulatory calculation in this pipeline is expressed as SQL in a dbt model with an inline comment citing the specific article of CRR, Basel III, or the EBA Delegated Regulation that mandates it. For example:

-- CRR Article 92(1)(a): CET1 ratio = CET1 / total_rwa, minimum 4.5%
round(c.cet1 / nullif(r.total_rwa, 0), 6)  as cet1_ratio

That comment is not documentation separate from the code — it is the code. When a regulator or internal auditor asks “how was this ratio calculated?”, the answer is a Git blame on a single line of SQL with a regulatory article citation. The calculation, the citation, and the version history are in one place.

Compare this to the typical alternative: a Word document titled “COREP Calculation Methodology v3.2 (supersedes v3.1)” stored in SharePoint, last updated 18 months ago, referencing a stored procedure whose logic diverged from the document six sprints later. The SQL-as-documentation approach eliminates that drift by construction.

What Comes Next: Great Expectations Quality Gates

The four mart tables now exist with correct COREP calculations and pass all dbt ratio tests. Day 8 builds the Great Expectations quality gate layer that runs between the dbt transform and the catalog ingestion step. It validates not just that ratios are above the Basel III floors, but that the underlying intermediate tables have the statistical properties expected for a real bank — catching data drift before it reaches the XBRL generator four steps later.

We will also see how a failing expectation suite writes its full HTML data docs report to MinIO, creating a timestamped audit artifact that persists even when the pipeline fails — so the evidence of what went wrong is always available for the post-incident review.

Series navigation:
← Day 6: dbt + OpenLineage — Column-Level Data Lineage for Free  |  Day 8: Great Expectations Quality Gates — Catch Bad Capital Ratios Before the Regulator Does →