Every pension data engineer eventually hits the same wall. The numbers reconcile on Monday, break on Tuesday, reconcile again on Wednesday, and nobody can explain why. The SQL is correct. The joins are right. The business logic passes every unit test. And yet the state contribution report disagrees with the fund NAV report by 137.42 TL, and the auditor wants an answer.
The answer is almost always the same: you have a clock problem, not a data problem.
BES (Bireysel Emeklilik Sistemi) pipelines run on top of at least four independent temporal axes. Standard data architecture — the kind taught in warehouse courses and baked into most ETL frameworks — assumes there is one clock, maybe two if you're sophisticated enough to distinguish event time from processing time. Pension systems demand four, and they refuse to stay synchronized.
The Four Clocks
Every record in a BES pipeline exists simultaneously on four timelines:
- Transaction time: when the operation was physically recorded in the source system. The row hit the OLTP database at 14:32:07 on Thursday.
- Effective time: when the operation is considered to have taken economic effect. A contribution paid Thursday afternoon may be effective as of Wednesday's valuation date, or Monday's, depending on cutoff rules.
- Regulatory submission time: when the operation was reported to EGM, SPK, or the Takasbank pipeline. This is often T+1 or T+2, and once submitted, it is frozen. Corrections require a separate reversal-and-resubmit flow with its own timestamp.
- Fund valuation time: when the operation was priced against a specific fund NAV. This is deterministic per fund and per business day, but a single contribution can be split across multiple funds with different valuation windows.
Each of these clocks ticks independently. Each has its own concept of "yesterday" and "as of now." And each is the correct clock for a specific downstream consumer.
Where the Collapse Happens
Most warehouses solve this by picking one clock — usually transaction time, occasionally effective time — and treating everything else as a derived attribute. This works until it doesn't. Concrete examples from HAYMER and state contribution pipelines:
The late Friday contribution. A customer pays Friday at 17:45. Transaction time is Friday. Effective time is Monday (post-cutoff). Regulatory submission time is Tuesday. Fund valuation time is Monday's NAV. If your pipeline uses transaction time as the primary key for daily aggregation, the state contribution calculation on Saturday will include a payment that has not yet been valued, priced, or submitted. The number is wrong for three of the four consumers.
The retroactive correction. An operator fixes a wrongly-coded contribution on the 15th of the month, effective the 3rd. Transaction time is the 15th. Effective time is the 3rd. Regulatory submission is the 16th, but it must reference the original submission from the 4th as a reversal. Fund valuation must be recomputed against the NAV of the 3rd, not the 15th. A pipeline that stores only one timestamp cannot express this event. It will either overwrite history (breaking the audit trail) or double-count (breaking the balance).
The state contribution cutoff. Devlet katkısı is calculated on effective-time windows but paid on regulatory-submission-time windows. A contribution effective in December but submitted in January belongs to two different fiscal treatments simultaneously. If you built your fact table around a single date column, you will spend the next quarter explaining variances to finance.
The fund switch during valuation lag. A customer switches funds Thursday. Old fund's exit NAV is Thursday's close. New fund's entry NAV is Friday's close (T+1 valuation). Transaction time is a single instant; fund valuation time is two different days on two different funds for the same logical event.
Why This Looks Like Random Bugs
The reconciliation failures produced by clock collapse are structurally inevitable but statistically sparse. On any given day, most contributions have transaction time, effective time, and valuation time close enough together that the collapse doesn't matter. The bugs appear only at the seams — month-end, cutoff windows, corrections, retroactive adjustments, fund switches during volatility.
This is why teams misdiagnose them. The bug "only happens sometimes." The bug "is not reproducible on test data." The bug "went away after we reran the job." None of these are true. The bug is deterministic. It fires every time a record crosses a temporal boundary that the model doesn't represent. It just looks random because you cannot see the boundary from inside the model.
What Actually Works
After enough years running these pipelines in parallel, a few structural decisions have proven non-negotiable:
- Store all four timestamps on every fact row. Not derived, not computed on read — persisted, indexed, and immutable.
txn_ts,effective_dt,reg_submit_ts,valuation_dt. Storage is cheap. Reconciliation meetings are not. - Never overwrite. Append with validity intervals. Bitemporal modeling is not optional for pension data. Every correction is a new row with its own transaction time and a reference to what it supersedes in effective time.
- Make the consumer declare its clock. State contribution reports read on effective time. Fund NAV reports read on valuation time. EGM submissions read on regulatory time. Operational dashboards read on transaction time. Build separate views, not a shared "truth" table.
- Reconcile across clocks, not within them. The interesting check is not "does today's total match yesterday's total plus today's contributions." It is "does the sum of contributions with effective_dt in November match the sum with reg_submit_ts in the November submission batch, adjusted for known reversals." These two numbers should agree, and when they don't, the delta tells you exactly which clock drifted.
- Treat cutoff rules as code, not documentation. The rule that determines whether a 17:45 payment is Friday-effective or Monday-effective belongs in a versioned, tested function, not a paragraph in a specification document that three people interpret differently.
The Deeper Point
Pension systems are not unusual. They are just honest about a problem that exists in every regulated financial pipeline: time is not a scalar. Insurance claims, securities settlement, tax accruals, IFRS 17 reporting — all of them run on multiple clocks, and all of them produce the same class of "random" bugs when the clocks are collapsed.
BES pipelines are the clearest teaching case because the four clocks are all official, all auditable, and all consumed by different regulators who will notice if you get them wrong. There is nowhere to hide.
The fix is not more validation, more tests, or more monitoring. The fix is admitting, at the schema level, that "now" is a vector, not a value. Once the model reflects that, most of the bugs stop being bugs. They become what they always were: correct answers to questions nobody thought to ask.