Reconstructing call events across carrier networks

A phone call is one event to a person and many rows to a network. Switching systems emit call detail records in fragments: a call that was forwarded, or moved between cells, or crossed a carrier boundary, arrives as several partial records, out of order, sometimes across different systems. Nothing downstream can be trusted until those fragments are put back together correctly.

Two things needed solving at once. The reconstruction itself, and the fact that the AWS Athena data lake the whole workload depended on was scheduled for decommissioning. There was no soft landing available: the reporting carries a regulatory obligation, so “we will fix it after cutover” was not an option.

Reconstructing the events

I built the dbt models that classify the call type and assemble the fragments into a single complete, auditable event: who called whom, at what time, for how long, with the network elements for both parties retained.

The difficult part was never the volume. It was the ordering and the edge cases: deciding which fragments belong to the same real-world call, in what sequence, and what to do with the partial and ambiguous states in between. That logic is built from window functions, array handling and layered CASE waterfalls, running incrementally in micro-batches on Iceberg tables so it stays affordable as volume grows.

Extending it across networks

The first model handled traffic from a single network. Real activity spans several. I built models per network and a further model that unions them into one combined view, so consumers query a single governed model rather than stitching sources together themselves and getting slightly different answers each time.

Moving off the data lake

With the models in place, I rebuilt the workload to source from Snowflake rather than the data lake, validating outputs against what the existing process produced before anything moved.

Rather than recreate the same fixed reports on a new platform, the replacement is served through a Streamlit application over the governed models, so the team pulls the extracts they need themselves.

What changed

• The legacy reporting platform and the data lake dependency are both gone, along with around NZ$240,000 a year in running costs.
• It runs faster than the reporting it replaced, and the team is no longer waiting on an analyst for routine extracts.
• The logic came out of hiding. It used to sit inside a report definition where nobody could inspect or test it, so when a number looked wrong there was no way to check without recreating the whole thing. Now it lives in dbt, readable, tested and version controlled.

A forced migration is the cheapest opportunity you will ever get to stop rebuilding the thing you already had. The decommissioning was the reason to do the work, but replacing fixed reports with self-service was the part that mattered.

Detail is deliberately general. This work supports a regulatory obligation and the specifics stay confidential.