Every pension company in Turkey has a table somewhere called something like DIM_PRODUCT_SEGMENT or REF_URUN_SEGMENT. It has maybe 40 rows. It looks trivial. It is not trivial. It is, in my experience, the single most quietly broken layer in the entire BES reporting stack, and the reason nobody notices is that it fails silently until a regulator asks a question that forces you to defend a number from 2013.
I've owned this layer, in one form or another, for about ten years. Here is what actually goes wrong.
The Comfortable Lie: Segment Is a Dimension
In every data model I've inherited or built, product segment is treated as a dimension attribute. You have a policy, the policy has a product, the product has a segment. Star schema, clean join, everyone happy.
This works right up until the moment you have to answer one of these questions:
- What was the segment classification of this 2011 policy as of the reporting date in 2016?
- Which policies moved from segment A to segment B when the 2019 SEDDK circular reworded the group pension definitions?
- Why does the HAYMER extract for Q2 2022 show 14,000 more policies in the "Otomatik Katılım" segment than the Q1 extract, when we onboarded no new AK policies that quarter?
The answer to all three is the same: segment is not a property of the product. It is a legal designation valid for a specific window in time, and it depends on which version of which mapping table your pipeline happened to be running against on the day the row was written.
How the Rot Sets In
The typical lifecycle of a segment code in a BES pipeline looks like this:
- Product is launched in, say, 2010. Someone assigns segment
BIR_001at inception based on the definitions in force at the time. - In 2013, the regulator publishes a circular that redefines what qualifies as "bireysel" vs. "gruba bağlı bireysel." Nobody updates the historical rows. A patch is applied to the mapping table for new policies only.
- In 2016, Otomatik Katılım launches. A new segment code is added. Someone writes a backfill script for a subset of policies that were converted. The script is run once, results are not versioned.
- In 2018, the product team retires the original product name and folds it into a new SKU. The mapping table gets a manual UPDATE. No history. No effective date. No changelog beyond a Jira ticket that references a Confluence page that has since been archived.
- In 2021, someone from actuarial asks why the GEV submission shows a different segment distribution than last year's audit file. Three people spend two weeks trying to reconstruct which version of the mapping was in effect when the audit file was produced. They fail. A senior manager decides the current numbers are "close enough" and signs off.
I have watched this exact sequence play out at more than one institution. The details differ, the pattern does not.
Why the Fix Nobody Applies Is the Only Fix
The segment code has to be modeled as a bitemporal fact, not a dimension attribute. Specifically:
- Valid time: the period during which the segment classification legally applied to the policy under the regulatory definitions in force.
- Transaction time: the period during which your system believed that classification to be true.
Those are two different axes and you need both. When HAYMER asks what segment a policy belonged to on 31.12.2015, you need to answer using the regulatory definition that was in force on 31.12.2015, applied to the policy attributes as they existed on 31.12.2015 — not the definition your mapping table holds today.
In practice, this means:
- A
SEGMENT_ASSIGNMENTtable withpolicy_id,segment_code,valid_from,valid_to,recorded_from,recorded_to, and aregulation_version_id. - A
REGULATION_VERSIONtable that captures which circular, which effective date, and which classification logic was in force. - Every mapping table change goes through the same versioning. No UPDATE-in-place. Ever.
- Reports declare which regulation version they were produced under, and that gets stored with the report output.
This is not exotic. It is the same pattern any halfway serious accounting system uses for chart-of-accounts changes. The reason pension pipelines don't do it is that segment was originally treated as reference data, and reference data culture in Turkish finance IT is, to be blunt, appalling. Excel sheets get emailed. DBAs run one-off UPDATEs. Nobody versions anything until an auditor asks.
What This Actually Costs You
The cost is not visible until it is catastrophic. Some concrete examples from things I've seen or been called in to unwind:
- A reconciliation between internal AUM reporting and the regulator's aggregate figures that was off by roughly 3% for eighteen months because two segments had been silently merged in one system and not the other.
- An audit finding that required reconstructing the segment classification for approximately 200,000 policies as of a specific date four years prior. The reconstruction took a team of three about six weeks and the final answer was defended with the phrase "best available reconstruction," which is auditor-speak for "we made it up."
- A product profitability analysis that misallocated commission expense across segments for two full quarters because the mapping had been patched mid-quarter and the finance cube was rebuilt from current mapping, not point-in-time.
None of these show up as pipeline failures. Nothing throws an error. The numbers just quietly diverge from reality, and the reality they're diverging from is itself moving because the regulator keeps redefining the terms.
The Uncomfortable Part
If you own a BES pipeline and you have not already done this, you almost certainly cannot answer, with a straight face, what segment a randomly selected 2012 policy belonged to on any given historical reporting date. You can produce a number. You cannot defend it.
The fix is not technically hard. It is politically hard, because it requires admitting that a layer everyone has been treating as trivial has been quietly wrong for years, and that the historical reports built on top of it are, at best, approximations produced under whatever mapping happened to be live that week.
Start with the regulation version table. Attach every existing report retroactively to the mapping version it was produced under, even if you have to reconstruct it from git history and old Jira tickets. Then, for new work, stop treating segment as a dimension. It never was one.