#116 incremental-by-period materialization for scoped consolidation closes

[Anonymous]

Originally created by: grynn-in

Problem

The orchestrator's Execute plane (konsol#60) and dbt PR [#115] added opt-in scope and period filters to consolidation: the entity_scope, fiscal_year, and fiscal_period dbt vars, resolved by the scope_filter / period_filter macros in dbt_project/macros/orchestrator_filters.sql and applied at gold_consolidated_trial_balance.entity_tb.

They work (verified live: entity_scope=GROUP_EMEA, fiscal_year=2023 narrowed the consolidated TB from 13,483 rows to 95). But the gold consolidation models are full-table materializations (MergeTree table, not incremental). So a scoped/period run overwrites the consolidated tables with only that slice — every other period/entity disappears until someone runs a full (no-var) rebuild. That makes "single-period close" and "consolidate one group" destructive for the rest of the warehouse.

Desired

Make the consolidation outputs incremental, keyed by the slice, so a scoped close inserts/replaces only its (fiscal_year, fiscal_period, entity/group) rows and leaves all other slices intact.

Scope / affected models

The chokepoint and everything downstream of it:

• gold_consolidated_trial_balance (the chokepoint where the filters apply)
• gold_fully_consolidated_tb
• gold_consolidated_cash_flow
• gold_consolidated_ytd
• gold_nci_movement_schedule

Suggested approach

• Convert these to dbt materialized='incremental' with incremental_strategy='delete+insert' (ClickHouse adapter) and a unique_key / partition on the slice keys (fiscal_year, fiscal_period, and the entity/group grain).
• On an incremental run with fiscal_year / fiscal_period / entity_scope vars set, delete+insert only the matching partition(s); a no-var run still does a full refresh / full rebuild.
• Keep the existing period_filter / scope_filter macros (they already produce the predicate); the is_incremental() branch should reuse them to bound the slice.
• Preserve current full-build behavior when no vars are passed (no regression).

Acceptance

• A scoped/period close updates only its slice; previously-built periods/entities remain queryable afterward.
• A full (no-var) build still produces the complete consolidated set.
• The orchestrator gold step (konsol#60) drives this via the same vars; no orchestrator change required beyond what already exists.

References

• dbt filters PR: grynn-in/konsolidat [#115]
• Orchestrator epic / Execute plane: konsol#60
• Macros: dbt_project/macros/orchestrator_filters.sql

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed