Policy Time Machine: Teaching Genie to Answer "What Changed Before the Claim?

[sushruth91]

The problem: History that nobody can query

Insurance systems are hoarders. Every coverage change, deductible change, vehicle swap, address move and status flip on a policy is preserved, usually as SCD Type 2 history — years of it. And almost nobody uses it, because the analytical surface built on top answers exactly one question: *what does this policy look like now?*

The moment someone asks how it got there, the work changes character. "What changed before this claim?" sounds trivial. Answering it means joining policy versions on effective-date intervals, sequencing events across two different grains (changes and claims), and writing a correlated look ahead from each change to the claim that followed it. That's a specialist's query. So in practice the question gets routed to an engineer, or asked in a meeting, or — most often — not asked at all.

The people whose job it is to understand policy behavior (claims professionals, operations analysts) understand policies. They should not need to understand window functions.

What I built

Policy Time Machine is my entry for the Databricks Genie App Challenge: a Databricks App (React + FastAPI) that puts Genie in front of a curated temporal semantic layer, so a claims analyst can type "show policies where coverage increased within 30 days before a claim" and get a correct answer, a timeline, and a next question to ask.

It deliberately supports four investigations and no more:

1. Individual policy history — one policy's chronological story, changes and claims on one spine.

2. Change-before-claim — material changes and the claims that followed, at any window the user names.

3. Portfolio patterns — which change categories most often precede severe claims.

4. Similar histories — policies whose behavior resembles this one's, never whose demographics do.

One boundary I enforced in the data itself, not just the copy: this is not a fraud detector. The dataset is synthetic (8,000 policies), and investigation-worthy patterns are deliberately seeded at declared, documented effect sizes — the app demonstrates how historical patterns are surfaced and investigated, not that policy changes predict claims. There's even a pipeline expectation (more below) that fails the run if any user-facing string contains words like "fraud" or "suspicious."

The key decision: Genie never sees the raw history

Text-to-SQL is least reliable at exactly the SQL this problem needs — look ahead joins across grains — and its failures are silent. So I split the temporal problem in two:

- Relationships are pre-computed in the pipeline as plain columns: `next_claim_id`, `days_to_next_claim_loss`, `change_timing`.

- Thresholds stay with Genie as filter literals, which is the SQL Genie is genuinely good at.

No joins, no window functions. Genie sees six flat gold tables and never the bronze SCD2 layer. The trade-off: a new question shape is a pipeline change, not a prompt tweak. I accepted that — an opinionated semantic layer you can test beats a flexible one you can't.

How it's built on Databricks

Everything ships as one Asset Bundle: synthetic data generator → serverless Lakeflow Declarative Pipeline (medallion: bronze → silver → gold in Unity Catalog) → Genie space → Databricks App.

Pipeline expectations enforce correctness. Every temporal invariant is a `@dlt.expect_all_or_fail` expectation — twenty of them. A violation fails the run instead of quarantining rows, because these rules *are* the product's correctness. Example: derived percentages are NULL, never a `9999` sentinel, because Genie will happily average a sentinel into a mean.

The Genie space is code. Instructions, example queries and trusted-asset Unity Catalog functions live in one Python module, rendered both as markdown for review and as the `serialized_space` JSON pushed via the REST API. One source of truth, so the docs and what Genie was told can't drift.

Genie is tested like software. There's no benchmark API, so I built a contract suite. Since scenarios are planted at known effect sizes, I know the right answer to every question independent of whatever SQL Genie writes. Fifteen contracts assert on results — must-include/must-exclude policy sets, orderings, negative checks — each run three times. 3/3 is green; anything else is red, no retries. A 2/3 means Genie is choosing between readings of the question, and that instruction ambiguity gets fixed, not retried.

Unity Catalog is the whole authorization story. The app declares `user_api_scopes` (`dashboards.genie`, `sql`), and Databricks Apps forwards each viewer's token as `x-forwarded-access-token`. Every Genie call and query runs *as the viewer*, so I wrote no authorization layer — UC grants decide everything, and a viewer without grants gets a clean "ask your admin" state.

What generalizes

If your domain has history and your users ask *how things got this way*: pre compute the relationships and leave only flat filters to Genie; enforce invariant — including vocabulary — as pipeline expectations that fail loudly; plant ground truth so you can regression-test the natural-language interface; and let Unity Catalog, via on-behalf-of-user tokens, be your entire authorization story.

The history was always there. The work is making it safe to ask about.

Video (5mins)  : policy-time-machine