If you've ever worked on an insurance data warehouse, or really any warehouse where data arrives from different systems at different times, you know the pain of keeping things in sync.
I spent years building data warehouses for a property and casualty insurer. Policy data came in batches from one set of systems. Claims data arrived hours later from a completely different system. We ingested and processed them separately, which was fine until we needed to bring them together.
We had a process called claim wrap-up. The idea was simple: match each claim to its policy. Check that the claim number ties back to a valid policy. Make sure the loss date falls within the policy's effective and expiry dates. Confirm that the claim section has a matching section on the policy. For property claims, that might be buildings or contents. For motor claims, it could be accidental damage or third party injury. If all the rules passed, we'd mark the claim as "wrapped", stamp it with a date, and attach the policy's foreign key so downstream consumers could join the two together. If any rule failed, the claim stayed as an orphan and we'd try again the next day.
Simple enough in theory. In practice, it was anything but.
The workaround tax
Here's what used to go wrong. The wrap-up process would update the claims table with the wrapped status and the policy foreign key. Then it would insert a record into the wrap log. Then it would post an initial reserve entry. Three separate writes, three separate commits.
If the reserve insert failed, say a data type mismatch or a transient error, you'd end up with a claim marked as wrapped but no corresponding reserve. Or a wrap log entry pointing to a claim that never actually got its status updated. The data was inconsistent, and you wouldn't always know about it until someone downstream raised a flag.
So we built workarounds. Reconciliation jobs that ran after the wrap-up to check everything lined up. Retry logic with idempotency checks. Manual cleanup scripts for the edge cases the automated checks missed. We called it "eventual consistency", which was really just a polite way of saying the data might be wrong for a while.
Every team I've spoken to that deals with multi-system ingestion has some version of this story. Whether your pipelines run in Informatica, dbt, or Spark, the problem is the same once you're writing to multiple tables. The specifics change. It might be order processing, patient records, or trade settlement. But the pattern is the same. Multiple tables need to update together, and when they don't, you pay a tax in extra code, extra jobs, and extra time spent investigating data quality issues.
What just changed
Databricks just released multi-statement, multi-table transactions into public preview (March 2026). In plain terms: you can now wrap multiple SQL statements across multiple tables in a single atomic unit. They all succeed together, or they all roll back together. No partial updates. No inconsistent state.
There are two ways to use them:
Non-interactive mode is where you write a block of statements and Databricks handles the commit or rollback automatically. This is the one you'd use in scheduled jobs and pipelines.
BEGIN ATOMIC
-- Update claim status and attach policy FK
UPDATE claims
SET status = 'wrapped',
wrap_date = current_date(),
policy_id = 'POL-2024-001'
WHERE claim_id = 'CLM-10042';
-- Log the wrap event
INSERT INTO claim_wrap_log
VALUES ('CLM-10042', 'POL-2024-001', current_date(), 'auto');
-- Post the initial reserve
INSERT INTO claim_reserves
VALUES ('CLM-10042', 'POL-2024-001', 'buildings', 15000.00, current_date());
END;
If any of those three statements fails, none of them commit. The claim stays unwrapped, the log stays clean, and no phantom reserve appears.
Interactive mode lets you start a transaction, run statements, inspect the results, and then decide whether to commit or roll back. This one's useful when an adjuster is manually reviewing orphaned claims and wants to verify things before making changes permanent.
BEGIN TRANSACTION; -- inspect, update, validate... COMMIT; -- or ROLLBACK;
Under the hood, this is powered by catalog-managed commits in Unity Catalog. Instead of each table managing its own commit log independently, Unity Catalog coordinates the commit across all tables in the transaction. That's what makes the atomicity possible.
Why this matters beyond data engineering
If you're a data engineer, the benefit is obvious: fewer moving parts, less defensive code, simpler pipelines. But the impact goes further than that.
Data trust. When your analytics team queries the warehouse and finds a claim marked as wrapped with no matching reserve, they lose trust in the data. They start building their own validation checks in their dashboards. They caveat their reports. That erosion of trust is expensive and hard to reverse. Atomic transactions eliminate an entire category of inconsistency.
Audit and compliance. Insurance is heavily regulated. When a regulator asks you to show the state of a claim at a point in time, you need that state to be internally consistent. A claim that's half-wrapped is hard to explain. A transaction that either fully committed or fully rolled back is much easier to audit.
Simpler pipelines. Every workaround you remove is code you don't have to maintain, test, or debug at 2am. The reconciliation job that checks whether the wrap-up completed cleanly? You might not need it anymore. The retry logic with the idempotency checks? Probably simpler now. Less code means fewer bugs and faster delivery.
What you should know before diving in
This is a public preview, so a few things to keep in mind:
- All tables you write to must be Unity Catalog managed (Delta or Iceberg) with catalog-managed commits enabled. You set this with a table property:
'delta.feature.catalogManaged' = 'supported'. - You can read from up to 100 tables and write to up to 100 tables in a single transaction. That's plenty for most real-world scenarios.
- No DDL inside transactions. You can't CREATE, ALTER, or DROP tables within a transaction block. Do your schema changes outside the transaction.
- No streaming writes. Structured Streaming isn't supported inside transactions yet. This is for batch and interactive workloads.
- Non-interactive mode works on SQL warehouses, serverless compute, and clusters on DBR 18.0+. Interactive mode is SQL warehouses only for now.
- Interactive transactions have a 10-minute idle timeout. If you walk away mid-transaction, it rolls back automatically.
None of these are dealbreakers for the claim wrap-up scenario or similar batch processing patterns. They're worth knowing so you can plan around them.
What's next
In Part 2, we'll get hands-on. I'll walk through building the claim wrap-up process from scratch using multi-table transactions on Databricks. We'll create the tables, seed them with sample P&C insurance data, run the wrap-up with business rule validation, and show exactly what happens when a rule fails. We'll also look at what happens when two processes try to wrap the same claim at the same time.
If you want to get a head start, the documentation is here: Multi-statement transactions on Databricks.
If you've been building workarounds for cross-table consistency in your warehouse, this feature is worth a look. It won't solve every problem, but it removes a genuinely painful one.
Have questions or want to share how you'd use this in your domain? Drop a comment below. I'd love to hear about it.
