Solution design · Jul 2025 – Present · Palantir Foundry

From raw ledger exports to a reconciled, forecast cash position.

A medallion pipeline with a config-driven categorization engine and a hybrid forecasting layer, surfaced to Finance through an Ontology-backed console. This page documents every layer and the test cases that hold each one in place.

uploads→bronze parse & merge→silver clean & categorize→gold daily & monthly actuals→forecast ML + deterministic, horizon-aligned→enriched actual + forecast→position roll-forward & DCOH→ontology→console ↳  rule edits write back to LIVE config and re-trigger the chain
9
pipeline layers
509
GL_EXACT rules
5
forecast models
3
ontology objects
53
documented tests
00

Overview

medallion + writeback

The system turns raw bank and GL transaction exports into three deliverables: a reconciled monthly cash-flow statement, a twelve-month forward forecast, and a cash-position view with Days Cash on Hand. Each is served to Finance through the Cash Console.

Two design decisions shape everything downstream. First, categorization is data, not code — rules and overrides live in writeback-enabled datasets that Finance edits from the UI, and a change re-triggers the build chain without a deployment. Second, the actual/forecast boundary is a single derived value: last_actual, the maximum month present in cashflow_monthly. Every downstream transform reads it rather than carrying its own cut-off, so the boundary can never disagree with itself.

End-to-end data lineage Vertical flow from media set uploads through bronze parse and merge, silver clean and categorize, gold aggregation, the forecast ensemble, the statement and position layer, the ontology and the Cash Console application, with a governed config lane on the right feeding categorization and a writeback loop returning from the application. UPLOADS — MEDIA SETS BRONZE — PARSE & MERGE SILVER — CLEAN & CATEGORIZE GOLD — AGGREGATE FORECAST LAYER GOLD — STATEMENT & POSITION ONTOLOGY APPLICATION CONFIG — WRITEBACK Cash transaction uploads Excel / CSV GL distribution uploads Excel / CSV parse_cash_transactions header detect · coerce · 1800s guard parse_gl_distributions header detect · coerce CashLedgerTransactions_Merged + static history · dedupe UniqueID CashLedgerGLDistributions_Merged + static history · dedupe UniqueID clean_transactions legs · signs · cash_date · void reversal categorize_transactions rule cascade · 509 GL_EXACT · overrides cashflow_daily per day · parameter · direction cashflow_monthly actuals · defines last_actual ml_forecast_weekly → weekly_to_monthly ml_forecast_monthly extended horizon ap_tuesday_forecast check-run cycle payroll_biweekly biweekly Wednesday schedule_forecast bonds · property tax score_forecast union · precedence · seasonal extension actuals train the models cashflow_monthly_enriched actual + forecast · variance · is_actual cash_position_monthly roll-forward · closing balance · DCOH actuals ≤ last_actual Cash Transaction categorized Monthly Cash Flow enriched Cash Position position + DCOH Cash Console — OSDK React statement · drill-down · scenarios · config seed_config config_tables.sql Ontology config objects Extraction Rule · Override RULES_LIVE materialized OVERRIDES_LIVE materialized rules read at build time Investments Finance edits write back — auto-rebuild fires
Figure 1 — end-to-end lineage, uploads to console
load-bearing dataset transform / input governed config ontology object config & writeback path

Layer map

read top to bottom
Uploads — media sets
Cash transaction and GL distribution exports dropped by Finance
Bronze — parse & merge
Spreadsheet parsing, type coercion, union with static history, dedupe
Silver — clean
Standardisation, GL distribution join, void reversal synthesis
Silver — categorize
Config-driven mapping of every transaction to a cash-flow parameter
Gold — aggregate
Daily and monthly actuals; defines last_actual
Forecast layer
ML ensemble plus deterministic calendar models, unioned and horizon-aligned
Gold — statement & position
Continuous actual+forecast series, balance roll-forward, DCOH
Config — writeback
LIVE rule and override datasets, authoritative over seeds
Ontology & application
Three object types read by the OSDK Cash Console
01

Ingestion — Bronze

parse & merge

Finance drops Excel and CSV exports into two media sets. Each parser handles real-world spreadsheet mess — preamble rows above the header, inconsistent types, dotted column names — and writes a typed dataset. Parsed uploads are then unioned with a static historical dataset and deduplicated.

Datasets

bronze
parse_cash_transactions_uploads
Header detection, type coercion, per-file read guarded by a timeout raised to 1800s
parse_gl_distributions_uploads
Same parsing approach for journal entries with per-leg GL splits
CashLedgerTransactions_Merged
Parsed uploads ∪ static history, deduplicated on UniqueID
CashLedgerGLDistributions_Merged
Same union and dedupe for the GL distribution stream
Known failure mode The original 300s read timeout caused large annual files to be silently skipped rather than failing the build — the totals looked plausible while a year of history was missing. The timeout was raised, but the deeper fix is that a timeout must fail loudly. See R-01.
02

Cleaning — Silver

clean_transactions

Standardises types and dates, deduplicates, and joins cash transactions to their GL distribution context. This is where the accounting correctness lives.

Transformations

silver
Leg unions
PER_LEG for shared GLs, GL_SPLIT taking the largest leg, GL_ONLY, plus A/P bank and clearing settlement streams
Derived fields
sign, signed_amount, cash_date = coalesce(post date, issue date), status, recon_date
Prefilters
ZBA and liquidity noise dropped, with carve-outs for specific cash codes
Void reversal
Status = 3 with a void date emits a mirror row on the void date with the opposite sign
Why void reversal matters A cheque cut in one period and voided in a later one must reduce cash in the first month and restore it in the second. Without the synthetic reversal, the original month stays overstated forever and the two months never reconcile independently.
03

Categorization — Silver

the semantic heart

Maps every transaction to a cash-flow parameter using a rules engine read from the LIVE config datasets at build time. No categorization logic is hardcoded, which is what allows Finance to re-categorize without a code change.

Rule cascade

first match wins
GL_AND_DESC
Priority 0 — GL account combined with a description pattern
GL_EXACT
Priority 1 — roughly 509 rules sourced from the Finance master list
GL_RANGE
Priority 2 — retained in the engine, currently unused
CASHCODE
Priority 3 — bank cash code match
DESCRIPTION
Priority 4 — description pattern of last resort
Overrides
Targeted reclassifications, including Investments and cash-code/GL pair rules
Coverage risk Unmatched GL accounts are dropped from the categorized output. A dropped dollar and a correctly excluded dollar are indistinguishable unless the unmatched set is materialised and monitored — that is what the mapping queue and R-04 exist to catch.
04

Aggregation — Gold

actuals backbone

Daily and monthly nets per parameter and direction. cashflow_monthly is the backbone: the forecast models train on it, the enriched statement builds from it, and its maximum month defines last_actual for the whole platform.

Datasets

gold
cashflow_daily
Per-day, per-parameter, per-direction net amounts
cashflow_monthly
Monthly actuals statement; Investments injected from override config with a sign flip
05

Forecasting

hybrid ensemble

Demand-driven series get machine learning; scheduled outflows get deterministic calendar models. Mixing the two is deliberate — a model has nothing useful to learn about a payroll run that occurs every second Wednesday, and a calendar has nothing useful to say about patient payment volume.

Forecast ensemble precedence Five forecast sources feeding score_forecast, showing which model wins for which keys, the exclusion of accounts payable and payroll from the machine-learning feeds, and the seasonal extension to a common horizon. MACHINE LEARNING — DEMAND-DRIVEN DETERMINISTIC — CALENDAR-DRIVEN ml_forecast_weekly weekly history · better MASE weekly → monthly day-proportional allocation ml_forecast_monthly extended horizon ap_tuesday check-run cycle payroll_biweekly every second Wed schedule_monthly bonds · tax score_forecast union · precedence · horizon alignment cashflow_forecast every parameter to last_actual + 12 Weekly wins for every key it covers; monthly fills only the complement. A/P and payroll excluded from ML — deterministic substituted, sign-negated. All series seasonal-naive extended to one common horizon.
Figure 2 — forecast ensemble and precedence in score_forecast

Model branches

forecast layer
ml_forecast_weekly → weekly_to_monthly
AutoGluon TimeSeries trained on weekly history; validated materially better on MASE than the monthly model. Aggregated to months by day-proportional allocation
ml_forecast_monthly_extended
Monthly model covering the longer horizon
ap_tuesday_forecast
A/P disbursements on the Tuesday check-run cycle
payroll_biweekly_forecast
Biweekly Wednesday payroll cadence
schedule_forecast_monthly
Bonds, property tax and other fixed schedules
score_forecast
Reconciliation point — union, precedence and horizon alignment
Precedence rules in score_forecast The weekly model wins for every (month, parameter, direction) key it covers; the monthly model fills only the complement. A/P and payroll are excluded from the ML feeds entirely and replaced by the deterministic cycles, sign-negated to the outflow-negative convention. Every series is then seasonal-naive extended to a common horizon of last_actual + 12 so all parameters share one forecast window.
06

Statement & position — Gold

what Finance reads

Datasets

gold
cashflow_monthly_enriched
Actuals at or before last_actual merged with forecast beyond it into one continuous series; each row tagged is_actual and forecast_method, with actual-versus-forecast variance computed
cash_position_monthly
closing = opening + net change (incl. Investments) + investment amount
DCOH
closing balance ÷ (annualised operating outflow ÷ 365); Investments shown but excluded from the denominator
income_statement_metrics
Parallel P&L-style reporting and variance branch
07

Config writeback

config as data

Two writeback-enabled LIVE datasets are authoritative for categorization. Seed versions are produced from a SQL definition, but the app writes user edits back through Ontology actions into the LIVE datasets, and the categorize transform reads LIVE.

Config writeback loop A four-step cycle: Finance edits a rule in the console, the edit materializes through ontology actions into the LIVE config datasets, an auto-schedule force-rebuilds the categorize to cash position chain, and the updated statement returns to the console. 1 · Edit Finance changes a rule or override in the Config tab 2 · Materialize Ontology action → object → RULES_LIVE / OVERRIDES_LIVE 3 · Rebuild Auto-schedule force-rebuilds categorize → … → position 4 · Serve Updated statement and position return to the console asynchronous — the rebuild trigger is asserted, not assumed
Figure 3 — the config writeback loop

The loop

writeback
1 · Seed
SQL definition seeds the reference datasets; a nonce bump forces a re-run
2 · Edit
Finance edits a rule or override in the Config tab of the console
3 · Materialise
Ontology action → object → RULES_LIVE / OVERRIDES_LIVE, asynchronously
4 · Rebuild
Auto-schedule fires on LIVE update and force-rebuilds categorize → daily → monthly → enriched → position
Operational quirk Config and media-set changes do not automatically mark downstream datasets stale. Rebuilds depend on the auto-schedule or a nonce bump. If either is disabled, the statement silently serves pre-edit numbers — which is why R-05 asserts on build timestamps rather than trusting the trigger.
08

Ontology & application

semantic layer

Object types & console

serving
Cash Transaction
From categorized transactions — backs every drill-down
Monthly Cash Flow
From the enriched statement — backs the statement and variance views
Cash Position
From cash position monthly — backs closing balance and DCOH
Cash Console (OSDK React)
Statement with drill-downs, forecast versus actual, DCOH trend, scenario planning, and rule editing that feeds the writeback loop
09

Orchestration

build & trigger

Mechanics

operations
Transforms repository
Mixed Spark and lightweight Python pipeline
Auto-rebuild schedule
Triggers on any update to RULES_LIVE or OVERRIDES_LIVE and force-rebuilds the categorize-to-position chain
Nonce bump
Manual escape hatch when a seed or media-set change needs to propagate
App release
Console published as versioned tags, independent of pipeline builds
10

Test catalogue

unit · integration · regression

Every case names what it asserts and why it matters — a test whose purpose is not obvious gets deleted the first time it fails inconveniently. Regression cases are tied to failure modes that have occurred or to invariants that are expensive to discover broken.

Show
11

Coverage & open gaps

read before signing off
29
Unit
Pure transform logic against fixtures. Fast, run on every commit.
12
Integration
Multi-transform chains and the writeback loop against seeded datasets.
12
Regression
Golden-file and invariant checks tied to known failure modes.

Gaps worth naming

honest assessment
Unmatched-GL quarantine
Several regression cases assume a materialised unmatched-transaction dataset. Until that exists, coverage of dropped rows is asserted indirectly and is weaker than it looks.
Forecast accuracy over time
Model quality is validated at training time. There is no standing backtest that fails a build when accuracy degrades against fresh actuals.
Writeback race conditions
Materialisation is asynchronous. Concurrent edits by two users are not covered, and last-write-wins behaviour is assumed rather than asserted.
Opening balance anchor
The roll-forward is anchored once. No test re-validates the anchor against an external bank statement, so a bad anchor propagates silently.