#154 gold_consolidated_trial_balance keeps stale slices: delete+insert never removes a key that left the SELECT

[Anonymous]

Originally created by: grynn-in

What happened

This came up while verifying grynn-in/konsolidat#151 on the local stack. Entity AMIT had 2 consolidated rows (EUR → CHF at 0.9528). Its currency was then blanked, so the model's SELECT no longer produced AMIT at all.

• A normal no-vars dbt run --select +gold_consolidated_trial_balance left both AMIT rows in place, still translated from EUR at 0.9528.
• Only dbt run --select gold_consolidated_trial_balance --full-refresh removed them (0 rows).

Root cause

dbt_project/models/gold/gold_consolidated_trial_balance.sql:1-9:

materialized='incremental',
incremental_strategy='delete+insert',
unique_key=['consolidation_group', 'data_area_id', 'fiscal_year', 'fiscal_period'],

delete+insert deletes only the target rows whose unique_key appears in the new batch. A key the SELECT no longer produces is never deleted. So the model comment at lines 20-22, "No vars => the SELECT returns every slice => delete+insert touches every key => identical to a full table build", is false for every key that disappears.

It predates konsol#110 (the incremental design is [#116], "A2"). The case verified here is an entity whose currency stopped resolving. By the same mechanism, any entity × group × period that leaves the SELECT should keep its last consolidated rows: an ownership period ending, consolidation_method moving to equity/none, has_complete_chain dropping to 0, or an entity leaving consolidation_ancestry.

Those rows keep feeding every downstream consolidated model (gold_fully_consolidated_tb, NCI, cash flow, YTD). That part is inferred from the same mechanism and was not individually reproduced.

Suggested fix

Delete by the run's scope, not by the new batch's keys:

• No period/scope vars: the run is a full build, so replace the table. Branch the materialisation on var presence, or have the orchestrator pass --full-refresh for unscoped runs.
• Scoped runs: a pre_hook deleting where 1=1 {{ period_filter(...) }} {{ scope_filter(...) }} from {{ this }}, then append. That empties the whole slice first, so a key that left it goes too.

Add a dbt test that fails when the model holds a (group, entity, year, period) key that gold_entity_ownership × gold_trial_balance × resolved currencies no longer produces.

Repro

On the [#151] branch: blank a consolidated entity's konsol functional currency (one with no ERP row), re-sync epm_staging.entities, then run dbt run --select +gold_consolidated_trial_balance. select count() from epm_gold.gold_consolidated_trial_balance where data_area_id = '<entity>' is unchanged. On main: end a consolidated entity's Ownership Period before its latest ledger period, and the same query shows the rows past the end date surviving.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed