ETL:
14 hours → 13 minutes

What the ETL does

• Reads every client's data from the app
• Ledger metrics — NAV, IRR, MOIC, commitments…
• KPIs, cap tables, financing rounds, industries, stakeholders
• Writes 21 flat tables to the analytics warehouse
• Powers benchmarking & analytics in Dash

KPIs are the biggest table by far — 3.7 million rows. The ledger metrics are the slowest to produce.

That gap is the whole story: the heavy part isn't the volume, it's the calculation.

How it worked

• One pod, once a week, 6 GB RAM
• All clients, one after another
• Everything through GraphQL, one call at a time
• No way to scale it up

A full run on PROD. Locally the first runs took 8–9 hours.
Weekly cadence — the data was always stale.

A free win

FX rates

• The ETL loaded and calculated FX rates
• Wrote them to the warehouse as a table
• Nobody ever queried them — Dash converts at query time
• Removed entirely

Pure waste — deleted before optimizing anything else.

portfolioSummary

• The single slowest query in the pipeline
• Returns all metrics for one fund, at one date
• The ETL needs every month-end of a fund's life
• Up to 200+ calls per fund

Underneath it

• NewSqlLedgerDashboardDetails
• One class, 7,411 lines
• Orchestrates ~200 SQL functions
• Every call recomputes from scratch for a single date

Ask for all dates at once

• One call per date → one call for all dates
• SQL functions extended to accept a list of dates
• Same work, far fewer round trips

The obvious win — we were paying the round-trip tax hundreds of times per fund.

What that required

• ~200 SQL functions had to support arrays of dates
• Each returns one row per date instead of one row
• Results grouped and matched back per date
• Behaviour had to stay identical

Which queries use that class?

• portfolioSummary
• ledgerDashboardDetails
• newLedgerDashboardDetailsPerPeriod
• newFundLedgerDashboardDetailsPerPeriod
• newLedgerDashboardDetailHistory
• investmentFilterData
• portfolioLatestStages

Seven queries. Each asks for a slightly different slice — all computing the same underlying numbers.

One query could serve them all

• Same data, different shapes
• A query that takes what you want and when
• Later: delete a lot of overlapping code

The ledgerMetrics query

• Pick any metrics, any dates, any scope
• Replaces several overlapping queries
• One trip fetches what previously took several
• Less data fetched, less repeated work

And a long-overdue cleanup

Split by metric — commitments, costs, IRR, shares, status…
Something we'd wanted to do for a very long time.

Fewer round trips, one unified query. Still all SQL.

Move the resolver to Go

• We already run a Go service for ledger charts
• The query moves there — still calling the same SQL functions
• Node no longer serializes millions of rows
• One ENV switches between Node and Go

Nothing about the calculations changed yet — only who orchestrates them.

Then reality hit

• Locally: a full run was down to 110 minutes
• On DEV it was still slow
• The SQL is limited by database hardware
• My laptop is ~3× faster than the Aurora CPU
• PROD might be faster — no chance to test it

Do the maths in Go

• Stop asking the database to calculate
• Read raw events once, compute in the service
• Database does what it's good at: fetching rows
• Scales with cheap app CPU, not the DB

We tried this before

• Years ago: NewLedgerDashboardDetails — 4,663 lines of TypeScript
• Same idea: load raw events, compute in memory
• It was slow — so we moved the maths into SQL instead
• That became NewSqlLedgerDashboardDetails — where we started

SQL was the right call then: simple queries, no recursion, fast.
The ledger has grown a lot since — and so has the SQL.

Both classes still exist side by side — this is the third attempt at the same problem.

Why Go works where TypeScript didn't

• One CPU core per process → now all cores
• decimal.js allocates objects for every operation — millions of them
• Go decimals are cheap; no garbage-collection stalls
• No JSON serialization between calculation and caller

Same algorithms, same results — the language is what changed.

Go vs SQL

~3.3× faster across a full ETL run.
92.7% of all calls run in Go — the rest fall back to SQL.

What still falls back — and why

• Everything meaningful is ported — 43 of 44 calculations
• Program-filtered calls — one function
• And — deliberately — any error at all

The fallback is a feature, not a gap: if a Go engine fails for any reason, the call silently runs the SQL function instead. Worst case is slower, never wrong.

Editing an event used to fall back too — that's now in Go (next slide), because it's the one path the frontend migration depends on.

Editing an event now runs in Go

• Editing shows the value before that event — recomputed without it
• That path used to drop to SQL — on the screen a user waits on
• Now computed in Go, like everything else
• Needed before the frontend can use the new query

The ETL never excludes an event — this was purely for the app.

Coordinator + workers

• Coordinator — prepares, distributes, finishes
• Workers — each processes clients independently
• Each worker has its own calculation service
• Work is claimed dynamically, not pre-assigned

How one run works

1. Coordinator creates staging tables
2. Fills a queue with all clients
3. Starts N workers
4. Each worker claims the next client, repeats until empty
5. Coordinator swaps staging → live, atomically

Dynamic claiming means one huge client never blocks a worker's queue — the others keep taking work.

Why it's safe

• Live tables untouched until the final swap
• Crashed worker → its client is re-claimed
• Failed client → keeps its previous data
• A dead run breaks nothing

The whole journey

Last two measured cold — caches flushed, worst case.

How many workers?

More workers help — up to a point. This laptop has 14 cores, and locally they all share one calculation service. In the cluster each worker gets its own, so the sweet spot moves higher.

More cores, more benefit

The same full run on the cheapest MacBook and on mine. Fewer cores → less to gain from splitting the work.

Which is the argument for the cluster: real nodes have more cores than either.

How we know it's correct

• Same test suites as before — vitest + cypress
• No separate test path for the Go code
• A full ETL run compared value by value against the old path
• Byte-identical — including AU data and all currencies

573 ledger tests green against the Go engines.

Three implementations, one truth

Every ledger test now runs all three and compares them.
A mismatch anywhere fails the build.

This is the real safety net — 573 ledger tests, three ways each.

What happens next

1. Use the new query in the ETL only
2. After it settles — the 7 queries on the frontend
3. Replace the class where it's used directly — 105 files
4. Deprecate, then delete the old queries and the class
5. Keep the SQL functions as the reference

Step 2's prerequisite — event editing in Go — is done.

Step 3 is the bigger half: exports, integrations, risk, waterfall, CLI — everything that never went through GraphQL.

The replacement already exists

85 accessors with the same names — ownership(), entryRound(), multiple()…
Most call sites become a constructor swap.

One query needs a bit more

• investmentFilterData — used by KPI Comparison
• Wants metrics per company, across all visible funds
• ledgerMetrics returns per-investment or summed — not per company
• So: add a per-company row mode

Ownership, multiple and exposure can't simply be added up, so it has to be computed per company — today it loops one call per company.

All five of its metrics already exist on the new row; only the grouping is missing. The min/max "ranges" block is just a reduce over the result.

• 7,411-line class → a structured module
• One query instead of many
• Calculations in Go, scaled across workers
• Every number verified against the old path