Gold Models

Business-ready models in the epm_gold schema, consumed by the Frappe API and Excel reports.

Trial Balance & GL

gold_trial_balance

Period-level trial balance aggregated from GL entries. Primary model for the actuals scenario.

Column	Type	Description	Test
data_area_id	String	Legal entity identifier	not_null
fiscal_year	UInt16	Fiscal year	not_null
fiscal_period	UInt8	Fiscal period (1–12)	not_null
main_account	String	Main account ID from chart of accounts	not_null
dim_cost_center	String	Cost center dimension	—
dim_department	String	Department dimension	—
dim_business_unit	String	Business unit dimension	—
period_debit	Decimal	sum(debit_amount)	—
period_credit	Decimal	sum(credit_amount)	—
period_net_amount	Decimal	sum(accounting_currency_amount)	—
transaction_count	UInt64	count(*)	—

API mapping: scenario=actuals → queries this table.

gold_ytd_trial_balance

Year-to-date running totals over gold_trial_balance.

Column	Type	Description	Test
data_area_id	String	Legal entity identifier	not_null
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Main account ID	—
dim_cost_center	String	Cost center	—
dim_department	String	Department	—
dim_business_unit	String	Business unit	—
ytd_debit	Decimal	Cumulative debit through period	—
ytd_credit	Decimal	Cumulative credit through period	—
ytd_net_amount	Decimal	Cumulative net amount through period	not_null desc

Test: assert_ytd_p12_equals_annual — YTD at period 12 must equal sum of all 12 periods.

P&L Models

gold_pnl_by_period

P&L view — revenue and expense accounts only.

Column	Type	Description	Test
data_area_id	String	Legal entity	not_null
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account (revenue/expense only)	not_null
account_type	String	Revenue or Expense	—
period_net_amount	Decimal	Net amount for period	—

Test: assert_pnl_only_pnl_accounts — only P&L account types present.

gold_pnl_quarterly

Quarterly P&L aggregation for P&L accounts.

Column	Type	Description	Test
data_area_id	String	Legal entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_quarter	String	Quarter label (Q1–Q4)	not_null
main_account	String	Account	—
quarter_net_amount	Decimal	Sum of period_net_amount for the quarter	—

gold_pnl_half_yearly

Half-yearly P&L aggregation for P&L accounts.

Column	Type	Description	Test
data_area_id	String	Legal entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_half	String	Half label (H1, H2)	not_null
main_account	String	Account	—
half_net_amount	Decimal	Sum of period_net_amount for the half	—

Balance Sheet Models

gold_balance_sheet

Balance sheet view — cumulative balances for BS accounts.

Column	Type	Description	Test
data_area_id	String	Legal entity	not_null
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account (asset/liability/equity only)	not_null
account_type	String	Asset, Liability, or Equity	—
cumulative_balance	Decimal	Running sum of period_net_amount within the year	—

Test: assert_bs_only_bs_accounts — only BS account types present.

gold_bs_movement

Balance sheet movement schedule: opening, movement, closing.

Column	Type	Description	Test
data_area_id	String	Legal entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	not_null
opening_balance	Decimal	Cumulative balance at prior period	—
period_movement	Decimal	Net amount for the period	—
closing_balance	Decimal	Cumulative balance at current period	—

Consolidation Models

gold_consolidated_trial_balance

Multi-company consolidated trial balance with currency translation.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Legal entity	not_null
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	—
local_amount	Decimal	Amount in entity's local currency	—
accounting_currency	String	Entity's local currency code	—
reporting_currency	String	Group reporting currency	—
translation_rate	Decimal	FX rate applied	—
closing_rate	Decimal	Period-end FX rate	—
average_rate	Decimal	Period average FX rate	—
translated_amount	Decimal	local_amount × translation_rate	—
ownership_pct	Decimal	Parent's ownership percentage	—
group_amount	Decimal	translated_amount × ownership_pct	—
nci_amount	Decimal	translated_amount × (1 − ownership_pct)	—

Tests: assert_translated_amount_formula, assert_group_amount_formula, assert_nci_plus_group_equals_translated, assert_nci_zero_for_full_ownership, assert_bs_uses_closing_rate, assert_pnl_uses_average_rate.

gold_ic_eliminations

Intercompany elimination entries.

Column	Type	Description	Test
consolidation_group	String	Group identifier	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
rule_id	String	IC elimination rule ID	not_null
debit_elimination	Decimal	Debit-side elimination amount	—
credit_elimination	Decimal	Credit-side elimination amount	—
elimination_amount	Decimal	Lesser of debit/credit IC balances	—

Test: assert_ic_elimination_nets_zero — eliminations net to zero per group/year/period.

gold_fx_revaluation

Currency translation adjustment (CTA) entries.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
cta_amount	Decimal	sum(local × (closing − average) × ownership)	—

Tests: assert_cta_not_zero_when_rates_differ, assert_cta_zero_for_same_currency.

gold_consolidation_adjustments

Top-side journal adjustments for consolidation.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
journal_id	String	Journal entry ID	not_null
adjustment_type	String	Type of adjustment	—
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	—
debit_amount	Decimal	Debit	—
credit_amount	Decimal	Credit	—
net_amount	Decimal	debit_amount − credit_amount	—

Test: assert_topside_journal_balanced — each journal must balance (debits = credits).

gold_fully_consolidated_tb

Unified consolidated TB: entity balances + IC eliminations + CTA + topside adjustments.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
adjustment_type	String	Layer: entity, ic_elimination, cta, or topside type	not_null
data_area_id	String	Entity (or blank for non-entity layers)	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	—
amount	Decimal	Amount for this layer	—

Test: assert_fctb_entity_layer_ties — entity layer ties to gold_consolidated_trial_balance.group_amount.

gold_consolidated_ytd

Year-to-date running totals on fully consolidated trial balance.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
adjustment_type	String	Layer type	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	—
ytd_amount	Decimal	Cumulative sum of amount through period	—

Advanced Consolidation Models

These models implement IFRS consolidation mechanics on top of gold_consolidated_trial_balance. See the PRD-* comment at the top of each .sql for the requirement reference.

gold_consolidation_hierarchy

Multi-level consolidation group hierarchy with effective ownership (PRD-8). Reads from the epm_staging.consolidation_hierarchy source when populated; otherwise falls back to a flat, single-level hierarchy derived from the consolidation_groups seed. Grain: one row per consolidation_group × data_area_id.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Legal entity	—
parent_group	String	Parent group in the hierarchy (blank at top level)	—
hierarchy_level	UInt8	Depth in the hierarchy (1 = top in fallback)	—
effective_ownership_pct	Decimal	Effective ownership through the hierarchy chain	—
path	String	Materialized path, e.g. group/entity	—

See gold_consolidation_hierarchy.sql.

gold_equity_method_associates

Equity-method entries for associates with 20–50% ownership (PRD-14), generated for entities where consolidation_groups.consolidation_method = 'equity'. For each entity/period it computes equity_income = net_income × ownership_pct (P&L accounts from gold_trial_balance, P&L filtered via silver_main_accounts.is_pnl) and emits two single-line entries per period using reserved accounts EQ_INCOME and EQ_INVEST. Grain: two rows per consolidation_group × data_area_id × fiscal_year × fiscal_period (income recognition + investment movement).

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Associate entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	EQ_INCOME or EQ_INVEST	—
account_name	String	Equity method - share of profit / Equity method - investment	—
reporting_currency	String	Group reporting currency	—
amount	Decimal	net_income × ownership_pct	—
adjustment_type	String	Constant equity_method	—
journal_id	String	EQ_{entity}_{year}_P{period}	—

See gold_equity_method_associates.sql.

gold_acquisition_adjustments

Step-acquisition adjustments — P&L proration, goodwill, and fair value adjustments (PRD-11), driven by epm_staging.ownership_periods rows with an acquisition_date. P&L for the acquisition year is prorated to post-acquisition months only (via the prorate_period_amount macro), and goodwill = acquisition_price − (net_assets × ownership_pct / 100). Grain: one row per generated adjustment entry.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Acquired entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Source account, 1800 (goodwill), or 1900 (FVA)	—
account_name	String	Account / entry description	—
adjustment_type	String	pnl_proration, goodwill, or fair_value_adjustment	—
adjustment_amount	Float64	Amount of the acquisition adjustment	—
acquisition_date	Date	Acquisition date driving the proration	—

See gold_acquisition_adjustments.sql.

gold_disposal_adjustments

Disposal gain/loss and CTA recycling at deconsolidation (PRD-12), driven by epm_staging.ownership_periods rows where is_disposal = 1. Computes gain_loss = disposal_price − (net_assets × ownership_pct / 100) − remaining_goodwill (remaining goodwill sourced from gold_acquisition_adjustments), plus reclassification of accumulated CTA from gold_fx_revaluation to P&L at the disposal date (IAS 21.48). Grain: one row per generated disposal entry.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Disposed entity	—
fiscal_year	UInt16	Year derived from disposal_date	—
fiscal_period	UInt8	Period derived from disposal_date	—
disposal_price	Float64	Disposal proceeds (0 on CTA recycling rows)	—
net_assets	Float64	Net assets at disposal (0 on CTA recycling rows)	—
ownership_pct	Float64	Ownership percentage disposed	—
remaining_goodwill	Float64	Unamortized goodwill at disposal	—
gain_loss_amount	Float64	Disposal gain/loss or recycled CTA amount	—
adjustment_type	String	disposal_gain_loss or cta_recycling	—
disposal_date	Date	Disposal date	—

See gold_disposal_adjustments.sql.

gold_nci_movement_schedule

Non-controlling interest movement schedule (PRD-13), built for fully consolidated entities where consolidation_groups.ownership_pct < 100 and consolidation_method = 'full'. NCI share of profit comes from gold_consolidated_trial_balance.nci_amount over P&L accounts; the NCI closing balance from the same column over balance-sheet accounts. Grain: one row per consolidation_group × data_area_id × fiscal_year × fiscal_period.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
data_area_id	String	Subsidiary entity	not_null
entity_name	String	Subsidiary name	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
nci_pct	Decimal	NCI percentage (1 − ownership_pct)	—
nci_closing_balance	Decimal	NCI balance from BS nci_amount	—
share_of_profit	Decimal	NCI share of profit for the period	—
consolidation_method	String	full	—

See gold_nci_movement_schedule.sql.

gold_ic_reconciliation

Intercompany reconciliation — matched vs unmatched balances per entity pair (PRD-15). Joins gold_consolidated_trial_balance.group_amount for both sides of each ic_elimination_rules debit/credit account pair within a group/period, restricted to entity_a < entity_b to avoid duplicate pairs. Grain: one row per group × period × entity pair × matched account pair.

Column	Type	Description	Test
consolidation_group	String	Group identifier	—
entity_a	String	First entity in the pair	not_null
entity_b	String	Second entity in the pair	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
account_a	String	IC account on entity A (rule debit account)	—
account_b	String	IC account on entity B (rule credit account)	—
balance_a	Decimal	Entity A group balance	—
balance_b	Decimal	Entity B group balance	—
net_balance	Decimal	balance_a + balance_b	—
match_status	String	matched when abs(net_balance) < 0.01, else unmatched	—

See gold_ic_reconciliation.sql.

gold_consolidation_journal

Unified audit trail of all consolidation journal entries (PRD-22). One row per non-entity journal entry from gold_fully_consolidated_tb (i.e. all rows where adjustment_type != 'entity'), split into debit/credit by sign. Grain: one row per source journal entry.

Column	Type	Description	Test
consolidation_group	String	Group identifier	—
data_area_id	String	Entity (or blank for non-entity layers)	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	—
account_name	String	Account / entry description	—
adjustment_type	String	Source layer type	not_null
journal_id	String	Journal identifier linking to source adjustment	—
reporting_currency	String	Group reporting currency	—
debit_amount	Decimal	amount when amount >= 0, else 0	—
credit_amount	Decimal	-amount when amount < 0, else 0	—
net_amount	Decimal	Signed amount	—
entry_source	String	Copy of adjustment_type	—

See gold_consolidation_journal.sql.

gold_consolidation_waterfall

Single-row consolidation build-up per group per period (PRD-22). Pivots the layers of gold_fully_consolidated_tb into cumulative checkpoints: entity → IC elimination → CTA → topside → equity method → acquisition/disposal → final. Grain: one row per consolidation_group × fiscal_year × fiscal_period.

Column	Type	Description	Test
consolidation_group	String	Group identifier	not_null
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
entity_amount	Decimal	Entity-layer total	—
after_ic_elim	Decimal	entity + ic_elimination	—
after_cta	Decimal	after_ic_elim + cta	—
after_topside	Decimal	after_cta + topside (incl. reclassification, auto_reversal)	—
after_equity_method	Decimal	after_topside + equity_method	—
final_amount	Decimal	Sum of all layers	—
ic_elimination_amount	Decimal	IC elimination layer	—
cta_amount	Decimal	CTA layer	—
topside_amount	Decimal	Topside layer	—
equity_method_amount	Decimal	Equity-method layer	—
acq_disposal_amount	Decimal	Acquisition/disposal layer (proration, goodwill, FVA, disposal gain/loss, CTA recycling)	—

See gold_consolidation_waterfall.sql.

Allocation Model

gold_allocation_results

Results of driver-based cost allocations (multi-step cascade).

Column	Type	Description	Test
allocation_rule_id	String	Rule identifier	not_null
step_order	UInt8	Step number in cascade (1, 2, 3)	—
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
source_account	String	Account being allocated from	—
source_cost_center	String	Cost center being allocated from	—
target_cost_center	String	Cost center receiving allocation	—
target_account	String	Account receiving allocation	—
driver_type	String	Driver used (headcount, sqm, revenue)	—
pool_amount	Decimal	Total amount in the allocation pool	—
driver_weight	Decimal	Recipient's share (0–1)	—
allocated_amount	Decimal	Amount allocated to this target	—

Tests: assert_each_step_sums_to_pool, assert_no_self_allocation.

gold_allocation_audit_trail

Audit trail of all allocation runs — active and reversed — for traceability (PRD-21). Joins epm_staging.allocation_runs to gold_allocation_results on fiscal_year / fiscal_period; for runs with status = 'Reversed' the allocated amount is negated. Grain: one row per run × allocation result line.

Column	Type	Description	Test
allocation_run_id	String	Run identifier for traceability	—
run_status	String	Active or Reversed	—
run_by	String	User who ran the allocation	—
run_at	DateTime	Run timestamp	—
reversal_of	String	Run ID this run reverses (if a reversal)	—
allocation_rule_id	String	Allocation rule	—
step_order	UInt8	Step number in the cascade	—
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
source_account	String	Account allocated from	—
source_cost_center	String	Cost center allocated from	—
target_cost_center	String	Cost center receiving allocation	—
target_account	String	Account receiving allocation	—
driver_type	String	Allocation driver	—
pool_amount	Decimal	Total pool amount	—
driver_weight	Decimal	Recipient's share (0–1)	—
allocated_amount	Decimal	Allocated amount (negated when run is reversed)	—

See gold_allocation_audit_trail.sql.

Budget & Variance Models

gold_spread_budget

Annual budget spread across 12 periods using profile weights. Primary model for the budget scenario.

Column	Type	Description	Test
scenario_id	String	Budget scenario ID	not_null
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period (1–12)	—
main_account	String	Account	—
dim_cost_center	String	Cost center	—
dim_department	String	Department	—
annual_amount	Decimal	Total annual budget	—
spread_profile_id	String	Spread profile used	—
period_weight	Decimal	Weight for this period	—
period_amount	Decimal	annual_amount × period_weight	—

API mapping: scenario=budget → queries this table. Tests: assert_spread_sums_to_annual, assert_spread_has_12_periods.

gold_variance_analysis

Actual vs budget variance with favorable/unfavorable logic. Primary model for the variance scenario.

Column	Type	Description	Test
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	not_null
account_type	String	Revenue or Expense	—
actual_amount	Decimal	From trial balance	—
budget_amount	Decimal	From spread budget	—
variance_abs	Decimal	actual_amount − budget_amount	—
variance_pct	Decimal	Variance as % of budget	—
variance_favorable	UInt8	1 if favorable, 0 if unfavorable	—

API mapping: scenario=variance → queries this table. Tests: assert_variance_abs_formula, assert_favorable_revenue, assert_favorable_expense.

gold_variance_ytd

YTD variance: actual vs budget with running totals.

Column	Type	Description	Test
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	not_null
ytd_actual	Decimal	Year-to-date actual amount	—
ytd_budget	Decimal	Year-to-date budget amount	—

gold_variance_quarterly

Quarterly variance: actual vs budget aggregated by quarter.

Column	Type	Description	Test
data_area_id	String	Entity	—
fiscal_year	UInt16	Fiscal year	—
fiscal_quarter	String	Quarter (Q1–Q4)	not_null
main_account	String	Account	—

gold_prior_year_comparison

Current vs prior year comparison with YoY variance.

Column	Type	Description	Test
data_area_id	String	Entity	—
fiscal_year	UInt16	Current fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	not_null
current_amount	Decimal	Current year period amount	—
prior_year_amount	Decimal	Same period prior year amount	—
yoy_variance_abs	Decimal	current_amount − prior_year_amount	—

Scenario & Period Models

gold_scenario_versions

Scenario version metadata from seed and API.

Column	Type	Description	Test
scenario_id	String	Scenario identifier	not_null
scenario_name	String	Display name	—
scenario_type	String	actual, budget, forecast, whatif	—
is_active	UInt8	1 = active	—

gold_scenario_trial_balance

Union of actual + budget + forecast across all scenarios.

Column	Type	Description	Test
scenario_id	String	Scenario identifier	not_null
data_area_id	String	Entity	not_null
fiscal_year	UInt16	Fiscal year	—
fiscal_period	UInt8	Fiscal period	—
main_account	String	Account	—
amount	Decimal	Period amount for the scenario	—

gold_period_hierarchy

Period dimension mapping fiscal_period to quarter and half labels.

Column	Type	Description	Test
fiscal_period	UInt8	Period number (1–12)	not_null, unique
fiscal_quarter	String	Quarter label (Q1–Q4)	—
fiscal_half	String	Half-year label (H1, H2)	—

Data Quality Models

gold_unmapped_dimension_values

The "needs harmonization" review queue. Surfaces distinct (erp_source, dimension, source_value) triples present in stg_gl_entries (across dim_cost_center, dim_department, dim_business_unit) that have no published row in dimension_mappings — matched against both canonical_value and source_value of Published mappings via a left anti join. Because dim_harmonize() passes unmapped values through verbatim, an unmapped value appears in stg_gl_entries as its raw source value. Drives the Dimension Mapping review report. Grain: one row per distinct unmapped (erp_source, dimension, source_value).

Column	Type	Description	Test
erp_source	String	Source ERP system	—
dimension	String	dim_cost_center, dim_department, or dim_business_unit	—
source_value	String	Raw, unmapped dimension value	—

See gold_unmapped_dimension_values.sql.