PnL Rollup

Purpose

Produce a reporting-ready PnL dataset where amounts are available at all GL hierarchy levels, not just leaf accounts. This model centralizes hierarchical aggregation logic so downstream consumers do not need to reimplement it.

Business Logic

Start from atomic PnL entries at the lowest GL account level
Use the GL account hierarchy to identify parent–child relationships
Recursively propagate amounts from child accounts to all parent accounts
Aggregate amounts per:
- Date
- GL account (leaf and parent)
- Value Type

Output Contract

Definitions

Column	Description
date	Date represented as `YYYY-MM-DD`. Links to Dates semantic contract
gl_id	GL Account identifier. Links to GL Accounts semantic contract
gl_account	GL Account name at any hierarchy level. Links to GL Accounts semantic contract
value_type_id	Value Type identifier. Links to Value Type semantic contract
type	Value Type name. Links to Value Type semantic contract
amount	Aggregated amount including all child accounts

Sample & Implementation

PnL Raw Data with aggregated values for each parent GL
date	gl_id	gl_account	value_type_id	value_type	amount
2025-01-01	1000	revenue	1	actuals	13744178
2025-01-01	1010	subscriptions	1	actuals	13743233
2025-01-01	1020	services	1	actuals	451
2025-01-01	1030	others	1	actuals	494
2025-01-01	2000	cogs	1	actuals	584704
2025-01-01	2010	payment_fees	1	actuals	584236
2025-01-01	2020	other_costs	1	actuals	468
2025-01-01	4000	commercial_costs	1	actuals	222010
2025-01-01	4010	sales	1	actuals	117750
2025-01-01	4011	sales_staff	1	actuals	107188

{{config(materialized='view')}}

WITH RECURSIVE pnl as (
    select * from {{ref('pnl')}}
),

gl_accounts as (
    select * from {{ref('gl_accounts')}}
),

pnl_rollup as (
    select
        date,
        gl_account_id,
        value_type_id,
        amount,
        0 as level
    from pnl

    union all

    select
        pr.date,
        ga.parent_gl as gl_account_id,
        pr.value_type_id,
        pr.amount,
        pr.level + 1
    from pnl_rollup pr
    join gl_accounts ga on ga.id = pr.gl_account_id
    join gl_accounts parent_ga on parent_ga.id = ga.parent_gl
    where ga.parent_gl is not null
)

select
    p.date,
    ga.id as gl_id,
    ga.name as gl_account,
    pnlr.value_type_id,
    vt.name as value_type,
    SUM(pnlr.amount)::double as amount
from pnl_rollup pnlr
join dates p ON p.date = pnlr.date
join gl_accounts ga ON ga.id = pnlr.gl_account_id
join value_types vt ON vt.id = pnlr.value_type_id
group by p.date, ga.id, ga.name, pnlr.value_type_id, vt.name
order by value_type_id, p.date, gl_id

Dependencies

Facts: PnL
Semantic Layers: GL Accounts