The Lakehouse That Became a Transaction Engine

Nobody sits in a design review and decides to run customer-facing operations on an analytics platform. It happens one convenient query at a time. By the time anyone names it, live processes depend on batch tables, and the platform team is holding an availability promise it never agreed to.

It is a recurring failure pattern, and it is worth walking through end to end: how it starts, why the obvious fix makes it worse, how it actually fails, what the unwind really costs, and why the same boundary now gates how far an organization can push agents.

The example below is an illustrative composite. It is a teaching case built from patterns that repeat across industries, not a report on any one company. If it sounds familiar, that is the point. The mistake is structural, not situational.

The Setup

A global insurer, with the scenario's numbers set for realism rather than drawn from a real account: 15 million policies under management, a bronze/silver/gold lakehouse on Azure Databricks, and around 200 data engineers spread across six business units. For two years it did exactly what it was built for: analytics, regulatory reporting, actuarial modeling. It worked.

The gold layer, by design, held the cleanest, most integrated claims data in the company. Everything reconciled. Everything was documented. And it had a table that looked, from across the room, like the current status of every claim.

That table is where the trouble starts.

The Slide Into an Accidental System of Record

The claims team noticed the gold layer had near-complete claim status. So case workers started opening a Databricks SQL dashboard during customer calls to check where a claim stood. It was right there. It was faster than filing a request. Nobody asked whether the platform was designed for that.

Then leadership saw the dashboards and formed an expectation: sub-second claim lookups, on demand, during live calls. A reporting surface had quietly become an operational interface, and now it carried an operational expectation.

The tell is the word "current." A gold table's current state means "as of the last successful refresh." An operational store's current state means "as of the last committed transaction." Those are different promises. The distance between them is exactly where this incident lives.

The Wrong Fix, and Why It Looked Right

The platform team responded the way most good teams would. They built operational views in the gold layer on a 15-minute refresh. When case workers complained the data was stale, they added more Databricks SQL warehouses to handle the concurrent load.

Every step was locally reasonable. The data was already there. The tooling was familiar. More load looked like a scaling problem, and scaling problems have scaling solutions. On paper it held together.

It was the wrong frame. The problem was never resource allocation. Two different access patterns were being served by one platform, and neither could win.

How It Actually Fails

Put an analytical platform under operational duty and the failures arrive in a predictable order.

Staleness turns into wrong answers. A 15-minute-old status is perfectly fresh for a month-end report and dangerously stale on a live call. Case workers told customers a claim was pending when it had already been approved, and the reverse. Same data, wrong contract.

Operational load starves analytics. Hundreds of case workers hitting the same tables at once degraded the actuarial and finance workloads that depended on those tables for month-end close. The two uses competed for the same engine, on the same schedule, at the worst possible time.

Costs climb quarter over quarter. Every complaint got answered with more compute. The bill only moved one direction.

The failure domain fuses. When you share infrastructure, you share outages. A maintenance window that should inconvenience a few analysts becomes a customer-facing incident, because live lookups now ride the same platform. The team ended up on call for a 99.5% analytical SLA against incidents that were being described in customer terms, not reporting terms. That SLA was never meant to cover real-time serving.

Then the pattern tries to escalate. The final stage, and the one worth watching for, is when consuming teams stop asking for faster reads and start asking for writes. Two teams requested write access to gold tables so their applications could "update statuses faster." That request is the anti-pattern asking to be made permanent. Granting it would have created two write paths into the same records and an audit trail nobody could reconstruct.

The Diagnosis: an EDP Is Not an ODS

Step back and the whole mess reduces to one confusion. An enterprise data platform and an operational data store are different archetypes with different promises. They only look interchangeable from a distance.

In the anti-pattern catalog this is Anti-Pattern 1, the Lakehouse as System of Record, and it drags Anti-Pattern 5, Analytical SLAs for Operational Workloads, along behind it. The second is what makes the first expensive. You can survive serving the wrong reads for a while. You cannot survive promising an operational incident response on a platform engineered for analytical availability.

How to read this diagram

Two columns, one for each archetype. Read them as opposing sets of promises.

On the left, the Enterprise Data Platform, the system of insight. Historical, append-only data. Mutation by appending new records, never overwriting. Latency measured in minutes to hours. Schema applied on read. Optimized for query throughput across large volumes. A 99.5% availability target. It exists for analytics, reporting, ML training, regulatory work, and data products.

On the right, the Operational Platform, the system of record. Current state, point in time. Mutation by in-place update with transactional guarantees. Latency in milliseconds. Schema applied on write. Optimized for transactions. A 99.9%-plus target. It exists for payments, workflows, case management, API serving, event processing.

The band across the bottom is the part people miss: the event backbone that connects them. Data flows from operational to analytical through change data capture and events. It flows back from analytical to operational through serving stores and feature stores. The two platforms are meant to be joined by that backbone, not collapsed into one. The insurer's mistake was deleting the boundary and asking one side to keep both sets of promises at once. Nothing on either list is negotiable by tuning a refresh schedule.

The five differences that bite, made concrete:

• Freshness. Scheduled refresh versus transaction-time. Fresh for analytics, stale for a call.
• Mutation. Append-and-historize versus update-in-place. One is auditable, the other is current. A single table cannot promise both under one contract.
• Latency and concurrency. The archetypes are tuned in opposite directions: scan throughput on one side, tail latency under concurrent point reads on the other. When they share infrastructure and conflict, one of them loses.
• Failure domain. Shared infrastructure means shared outages, and analytical platforms fail on an analytical schedule.
• Consistency. An operational process that writes then reads expects to see its own write. A batch-fed platform cannot promise that. No refresh cadence gets you from "eventually" to "now."

The convergence objection, taken seriously

There is a strong objection to all of this, and it deserves a straight answer rather than a footnote: the platforms are converging. Databricks ships Lakebase, a managed Postgres inside the lakehouse. Snowflake ships hybrid tables and its own managed Postgres. And on the exact platform in this story, Databricks now offers Lakehouse Real-Time, a serverless warehouse type that Microsoft's documentation describes as delivering sub-second SQL reads for "hundreds to thousands of concurrent users." It is in beta and supports SELECT only, but the direction is unmistakable.

So if the engine can serve the query, is the boundary obsolete?

No, because the boundary was never about what the engine can do. Capability convergence is real, and where it lets you consolidate infrastructure and move less data, take the win. Contract convergence is the thing that is not happening, because it cannot. A workload's contract is the set of promises it depends on: consistency semantics, latency variance, failure isolation, recovery objectives, and above all who is accountable when a promise breaks. An analytical estate and a claims desk can share an engine. They cannot share a contract, because the promises are different and sometimes opposed.

Which reframes the insurer's mistake. The error was not running a lookup on Databricks. It was assigning an operational responsibility to a platform because it could satisfy the query, without anyone deciding whether it could satisfy the contract. A vendor benchmark showing fast median latency proves nothing about behavior under concurrent load, during a maintenance window, or in a failure. The right test is not "can it serve this?" but "does its consistency, latency, availability, failure isolation, concurrency, recovery, ownership, and cost model satisfy this workload's contract, in writing?"

That test has a useful property: it survives every vendor announcement. Never confuse shared infrastructure with shared architectural responsibility.

The Target State

The fix is not a better query or a bigger warehouse. It is giving the operational workload its own home and putting the boundary back.

How to read this diagram

Read it top to bottom as layers, and notice the vertical split in the middle.

• Layer 1, Sources. The systems that originate data: core insurance, payments, CRM, custom apps, third parties.
• Layer 2, Event backbone. Event Hubs, Service Bus, and CDC via Data Factory. Everything downstream is fed from here, which is what lets the two sides stay in sync without either writing into the other.
• Layer 3, Operational Services. The systems that run the business. Millisecond latency, 99.9%-plus, transactional guarantees.
• Layer 3a, Operational Data Store and Serving Stores. This is the layer the insurer was missing. Azure SQL Database, Cosmos DB, Redis, AI Search. Current-state, low-latency, transactionally consistent reads. Claim status belongs here.
• Layer 4, Enterprise Data Platform. The analytical heart: Azure Databricks, ADLS Gen2, and Synapse, arranged in bronze, silver, gold. Raw immutable landing, then cleansed and conformed with business keys applied, then consumption-ready products.

Across the top sits the governance plane, spanning both halves: Purview for governance, Unity Catalog for lineage, Entra ID for security, Azure Monitor for observability. It is drawn spanning the full width on purpose. Governance that stops at the analytical boundary cannot answer questions about the data that customers actually see.

The load-bearing idea is the seam down the middle. The operational platform (Layer 3 and 3a) and the analytical platform (Layer 4) are separate, and the event backbone at Layer 2 is what keeps them consistent. Claim status now reads from Azure SQL at Layer 3a, fed by claim events off the backbone, under a 99.9% operational SLA. The gold layer goes back to what it is good at: historical claims analytics, fraud pattern detection, actuarial reserving. Neither one is asked to be the other.

If you want the same diagram for AWS or GCP, the reference architecture has both. The archetype does not change across clouds; only the product names do.

What the Unwind Actually Costs

The diagram is the easy part. Moving a live workload off a platform it should never have been on is where the real work sits, and it is worth being honest about the bill.

You are building a store, not migrating one. There is no lift-and-shift here. The serving store is a new thing with its own schema, its own owner, and its own on-call rotation. That last item is the cost people forget. Someone has to carry a 99.9% pager who was not carrying one before, and that is an organizational negotiation, not a technical task.

You have to choose the feed per product. There are three supply patterns, and picking the wrong one rebuilds the original problem in a new location:

• Push from the EDP on a schedule, the reverse ETL shape. Fine when freshness tolerance is minutes to hours. Wrong for claim status.
• Event-driven projection, where the serving store consumes the same backbone the EDP does and maintains its own view. Right when tolerance is seconds and the events already exist. This is the one claim status needs.
• Direct from source, where the operational system feeds the serving store and the EDP is not in the path at all. Right for pure current-state reads.

The trap is routing everything through the EDP out of habit. For current-state reads that adds staleness and a failure domain and buys nothing.

Every feed needs a contract, and the contract is the deliverable. The minimum fits on a card: named owner, schema, authorization model, freshness per field group, latency SLO, rebuild path, staleness behavior, and whose pager fires. Writing that card is what converts a convenient query into an engineered dependency. Skipping it is how you end up back where you started.

Degraded mode is a requirement, not a refinement. During an upstream incident the serving layer should return last-known-good data with a freshness flag rather than going dark, because a serving store that fails whenever the warehouse is in a maintenance window has silently re-fused the failure domains the whole exercise existed to separate. Decide that behavior per consumer, though, not globally. Serving a stale claim status to a case worker who can see the freshness stamp is reasonable. Serving stale data into a price quote, a payment, an entitlement check, or a workflow transition is not; those should escalate to a human or fail closed. The rule is that degraded behavior is designed and written down, never discovered during the incident.

And you have to say no once, in writing. The two teams asking for write access to gold get refused, but refusal alone is not an architecture. The answer is a sanctioned path: their systems emit events to the backbone, and both the operational store and the EDP consume them. One authoritative flow instead of two write paths. The refusal is only defensible because it comes with somewhere else to go.

And the cutover itself is staged, not scheduled. The failure mode of this migration is causing a second incident while fixing the first, so the sequence matters: build the projection and backfill it from the event history, run it in shadow beside the existing dashboard path, reconcile the two until disagreement sits inside an agreed threshold, then move consumers across one at a time rather than all at once, keeping the old path warm as the rollback until the new one has survived a peak period and a real incident. Only then retire the dashboard. Teams that skip the shadow-and-reconcile step tend to discover their event stream was missing a state transition after the customers do.

That is the real cost: a new store, a new pager, a contract per feed, a degraded-mode path, a staged cutover, and one difficult conversation. It is considerably cheaper than the alternative, which is paying for all of it in incidents instead.

What You Should Expect to Change

This is a composite, so treat what follows as the target outcomes the design is chosen to produce, not as measurements from a real programme. They are also the things worth instrumenting before you start, because a baseline captured after the migration proves nothing.

Four should move. Platform cost, because concurrent operational load stops inflating the bill. Claim-status freshness, from batch staleness to sub-second reads against a store built for it. Analytical query performance during business hours, because month-end work stops competing with live lookups. And platform-team time on unplanned work, because operational SLA breaches stop landing on a team that never signed up for them.

That last one is the honest leading indicator. Freshness complaints do not disappear so much as move: they leave the data-quality queue and reappear, correctly, as serving-layer tickets owned by someone with an operational pager. If nothing shows up in the new queue, the workload probably did not actually move.

None of this comes from new technology. It comes from routing each workload to the platform whose promises match what the workload needs.

The Same Boundary Gates Your Agent Rollout

Everything above reads as a story about 2023. It is not. It is a precondition for the part of AI strategy that no model procurement decision can fix, and the precondition binds hardest exactly where agents stop answering questions and start taking actions.

Agents are a new consumer class on the data platform, sitting alongside dashboards, analysts, and model pipelines. They consume differently in four ways that all sharpen the argument this article has been making.

Volume. An analyst runs dozens of queries a day. An agent loop can run thousands, at machine speed, around the clock. Cost controls sized for human exploration do not survive it.

Autonomy. Nobody reviews each query before it runs. A schema misunderstanding does not surface as a confused analyst asking a colleague. It surfaces as confidently wrong output, repeated at scale.

Blast radius. This is the one that matters. A BI user misreading a chart wastes a meeting. An agent misreading a table feeds an action: a customer message, a priced quote, a workflow decision. The cost of a wrong read moves from a slide to a system.

Credentials. Agents authenticate as service identities, not people. A shared agent service account with broad read access is the new shared-credential anti-pattern, and it defeats least privilege and audit at the same time.

Now put those four properties on top of an accidental ODS. The organization has spent two years teaching everyone that the gold layer is where current truth lives. Then it points an agent at exactly that surface. The agent inherits the wrong contract, cannot tell staleness from correctness, has no colleague to sanity-check it, and acts on the result. Every ambiguity the humans had been quietly absorbing becomes an automated decision.

This is why AI readiness is a data-contract problem before it is a model problem, and why so many pilots stall at exactly the point where they would need to touch real data.

The connection layer is not the governance layer. MCP has become the de facto standard for connecting agents to tools and data, and the major warehouse vendors now run managed MCP servers that do enforce catalog permissions and tie agent identities into platform-native access control. Be precise about what you are getting from which layer. MCP standardizes how an agent finds and calls a tool, the way USB-C standardizes a plug; the protocol has an authorization mechanism, but it does not by itself define your enterprise authorization policy, your delegation semantics, or your audit trail. Those are your platform's job, enforced in the catalog, the policy engine, and the log, exactly as they are for every other consumer. An MCP endpoint in front of an ungoverned table is an ungoverned table with better ergonomics.

What good looks like is the same boundary discipline, extended one consumer class further:

• The semantic layer is the interface, not the tables. An agent reading a governed metric inherits the definition the business signed off on. An agent computing it from raw tables invents its own.
• Data products exposed as named, read-only tools. A typed, rate-limited lookup_claim_status capability beats open SQL. The contract you wrote for the feed already covers the agent.
• Text-to-SQL only inside guardrails. Scope to curated views, cap rows and bytes, budget per identity, log every generated statement. Treat generated SQL as untrusted input, because it is.
• Scoped identity per agent. Not one shared service account. "Which agent read this, under whose delegation, for which task" has to be answerable per agent, or the audit trail means nothing.

A first agent pilot in this scenario would take exactly that shape. A claims copilot answering case-worker questions gets a scoped service identity, governed metric and view definitions as its interface, and read-only data products as tools. Its guardrails are written down before it ships: no free-text case notes in context, every response logged with the tool calls behind it, and an evaluation set maintained on the platform. It reads the semantic layer, never the tables.

And the regulatory tail arrives on the same rails, for the systems actually in scope. Under the EU AI Act, Annex III names risk assessment and pricing for life and health insurance in relation to natural persons as high-risk, which is narrower than "pricing models" generally. Where a system does fall in scope, Article 10 governs the training, validation, and testing data sets, so those become versioned data products with lineage and documented composition. Done that way, producing the evidence is a query. Done the other way, it is a quarterly archaeology project.

The strategic read is short. The organizations that will adopt AI fastest are not the ones with the best model access. They are the ones whose data already has owners, contracts, freshness semantics, and a boundary between what is current and what is historical. If you never drew that boundary, agents will not expose the gap gently. They will act on it.

How to Tell You Have Built One by Accident

You do not need a 15-million-policy insurer to have this problem. The signs are the same at any scale:

• Operational staff use dashboards as their work interface, refreshing to see if a status changed.
• "Data quality" tickets that are really freshness complaints. The number was right, just old.
• Refresh schedules ratcheting tighter (daily to hourly to 15 minutes) under pressure from one consuming team.
• An app or API reading gold tables directly, discovered only when its traffic degrades analytical queries.
• The platform team on call for incidents whose impact is described in customer terms, not reporting terms.

Each of these is the same finding stated five ways: an operational workload consuming an analytical contract.

The design rule fits in one sentence. If a consumer needs read-your-writes, customer-facing latency, or an operational incident SLA, it does not read the EDP directly.

Read that rule in both directions, because the inverse is a permission and the article would be dishonest without it. Plenty of things should read the gold layer directly. Analyst tooling, internal dashboards, ML training jobs, regulatory extracts, the finance team's month-end workbook. None of those need read-your-writes, none carry a customer-facing latency budget, and none page anyone at 2am. Moving them to a serving store buys nothing and costs a pipeline. The goal is not to drain the analytical platform. It is to stop it from silently acquiring promises it cannot keep.

Try It on Your Own Estate

Pick one workload currently running on your analytical platform. Route it through the decision tree: does it need current state, sub-second reads, and an operational SLA? If yes, it belongs in a serving store, and the serving-layer blueprint is your build list. If no, it is correctly placed.

The point is not to move everything. It is to make the boundary a decision you make on purpose, once, instead of one that erodes on its own, one convenient query at a time.

Related

This piece expands Case Study 1 from my open Enterprise Data Architecture guide, which is free and CC BY 4.0. The parts that go deeper:

• EDP Is Not an ODS, the underlying argument in full.
• A European Insurer on Azure, the same organization worked through end to end, with the five hardest decisions and what got routed where.
• Case Studies, including the two follow-on cases: a tier-1 bank on GCP and a US healthcare provider on AWS.
• Agents as Consumers, on MCP, semantic layers as agent interfaces, and agent identity.
• Building Data Foundations While Everyone Chased Models, the same argument four years earlier.

The uncomfortable part of this pattern is that no single decision in it was wrong. Querying the gold layer was convenient. Adding a refresh was responsive. Adding warehouses was proportionate. Each step was a reasonable answer to the step before it, and the destination was still an analytics platform holding a customer-facing promise it could not keep. Architecture erodes that way more often than it fails outright, which is why the boundary has to be something you decide once and defend, rather than something you assume will hold on its own.