When Salesforce Storage Limits Meet 50,000 Claims a Day
The notification landed on a Tuesday afternoon in month four. I was the architect running a Salesforce-based claims platform that processed fifty thousand claims every day, and the storage dashboard I had calibrated carefully three months earlier had crossed the line I had marked as safe. The estimates were not wrong. The problem was that fifty thousand records a day does not stay abstract for long. At that volume, every number you were comfortable with eventually becomes a wall, and walls have a way of appearing faster than planned.
I did not know then that I was three months away from building something I would use to explain distributed data architecture to every team I worked with afterward. I knew I had a problem with no obvious exit.
How 50,000 Claims a Day Breaks Your Assumptions
Let me run through the math the way I ran through it at the time.
Fifty thousand claims a day sounds like a lot. At the individual record level, it is manageable. A claim header in Salesforce is not enormous. Healthcare claims do not travel alone. Each claim brings claim lines, service codes, member information, payer adjudication details, and in many cases supporting documentation. When you count the full object graph, a single claim touches anywhere from five to ten records across the org. At fifty thousand claims a day, that is potentially half a million new records every twenty-four hours.
Salesforce data storage is generous by many standards. But it is finite. And retention obligations mean you cannot delete claims. State law and payer contracts set the floors, commonly six to ten years, and some contracts run longer. So every record written today lives on the org for years, and the storage trajectory is a line that only goes one direction.
Storage is the visible constraint. The less obvious one surfaces later. Salesforce enforces a hard governor limit of fifty thousand rows returned per SOQL query transaction. When historical claims data accumulates across years of operation, queries that need to look across the full retention window start hitting that ceiling. You cannot run a meaningful audit of adjudication patterns across eighteen months of data without breaking the query into fragments and reassembling results in application code. The platform that was excellent at processing individual claims in real time starts to strain when asked to think across the full history of those claims. Both constraints, the storage trajectory and the query ceiling, pointed toward the same underlying mismatch. They just announced themselves at different times.
By month four, the math was clear. The question was what to do about it.
Three Wrong Turns Before the Right One
The obvious answers came quickly and fell apart just as fast.
Buying more storage buys time, not a solution. Salesforce storage is expensive per gigabyte relative to cloud alternatives. At fifty thousand claims a day, you cannot buy your way out of this. The spend compounds with retention. I ran the three-year projection and the numbers were not close to defensible. You are paying a premium to defer a problem, not solve it.
The platform's native archival storage layer looked promising. It was designed for scale and long retention. But when I went deep on the constraints, the picture changed. Standard reports cannot see the data. Built-in automation tools cannot read from it in any practical sense. You can write data in, but getting it back out in a usable form requires significant custom development. The moment you need to correlate a historical claim with a current member record, you are writing code that fights the platform. For a claims operation, historical correlation is not an edge case. It is a daily workflow.
Third-party archival tools introduced new operational dependencies, new contracts, and pricing structures that compound with volume. Two evaluations, two passes.
There was a week in month five where I had eliminated every option I had originally considered and had nothing credible to replace them with. The storage bars kept climbing. The team was watching the dashboard the way you watch a patient's vitals when the numbers are moving in the wrong direction. I had a platform that was excellent at its job and a volume problem that seemed to have no clean answer inside the tools I had been given. The SOQL ceiling meant that even if I solved storage, I would eventually be unable to query the data I was keeping. Fixing one side of the constraint did not fix the other.
That discomfort was, in retrospect, productive. It forced me out of the frame I had been working in.
The Reframing That Became the Architecture
I had been asking the wrong question. The question I kept asking was how to keep more claims in Salesforce. The question I should have been asking was what Salesforce is actually for.
That shift happened on a Friday evening when I was reading the platform documentation for the third time, looking for something I had missed. I had not missed anything in the documentation. What I had missed was the frame. The moment I stopped asking how to store more and started asking what Salesforce was designed to do, the architectural answer became almost obvious.
Salesforce is an operational system. It is excellent at managing the active state of a business process: a claim in flight, a case under review, a member record being updated. What it is not is a long-term historical data store designed for large-scale retention at low cost per byte. That is a different shape of problem. Different shapes call for different tools.
Once I saw that, the architecture followed. Not one tier. Two. And the boundary between them would be the design.
The first tier stays in Salesforce. It holds the active claims window, the data that people are actually touching, that workflows run against, that automations read and write. We settled on a ninety-day rolling window for claims in active status. Everything needed for daily operations lives here, on the platform that does daily operations well. At that window size, the fifty-thousand-row SOQL ceiling is not a practical obstacle. The queries you need to answer about active claims are queries the platform can answer cleanly.
The second tier lives in cloud storage and a relational layer built for archival and analytical workloads. When claims age past the active window, they move here. The full fidelity of the record is preserved, everything a compliance audit or retrospective analysis would need, but the data no longer consumes Salesforce storage because it no longer lives there. This tier scales at cloud-object-storage prices, not per-gigabyte platform prices, and carries no row-count ceiling on the queries you can run across it.
Between the two tiers sits a lightweight sync process. On a schedule, it identifies claims that have crossed the age threshold, writes them to the cold tier with full field mapping, and removes them from the hot tier after confirming the write succeeded. The removal from Salesforce is what frees storage. The write to cold storage is what preserves compliance.
The Seam That Makes It an ODS, Not an Archive
The detail that separates this architecture from a simple archive is the read layer that sits in front of both tiers. Without it, what you have is Salesforce for active claims and a compliance dump for everything older. That is a defensible setup if historical data exists only to satisfy auditors. It is not defensible if your team needs to correlate a current claim with its history, or if any downstream process needs a consistent view of claims regardless of age.
With the unified read layer, downstream consumers do not need to know which tier a given claim lives in. They request a claim by identifier. The layer routes the request to the right store and returns the data in the same schema regardless of source. From the outside, there is one claims store. On the inside, there are two, each doing the job it was built for.
That distinction matters operationally. An archive is a place you go to retrieve compliance evidence. An operational data store is a place your workflows query in real time. The read seam is the difference between the two. Without it, the cold tier is a write-only compliance artifact. With it, the cold tier is a live part of the system, as queryable as Salesforce, just optimized for a different cost and scale profile. The architecture is not a hot database plus an archive. It is one ODS built from two tiers with different optimization targets, surfaced to consumers as a single interface.
That is what makes it worth the overhead.
What the Boundary Taught
The storage problem went away. The query ceiling problem went away with it, because the data that had been accumulating against that ceiling was no longer in Salesforce. Not because we found more room, but because we stopped asking Salesforce to do something it was never designed for.
Every cloud-native tool has a shape. The shape comes from what the tool was built to do well and what starts to cost more or work worse as you push past that design intent. Salesforce's shape is operational: collaborative, process-oriented, running at the speed of human decisions. Cloud object storage's shape is massive, cheap, durable, built for bulk reads and long retention.
When those shapes match the problem, everything moves easily. When they do not, you get symptoms. Storage bars climbing faster than expected. Query timeouts at the row ceiling. Pricing that scales nonlinearly with volume. Those are not bugs in the platform. They are the platform communicating something about the shape of what you are trying to do.
The right response is not to fight the platform. It is to draw a deliberate boundary between what the platform is for and what something else handles better, then make that boundary explicit, tested, and operationally simple.
The overhead is real. There is a sync process to monitor, a mapping layer to maintain, a cold-tier schema that has to stay in step with the Salesforce schema as both evolve. It is not free. At this volume it is still cheaper than the alternatives. And it is far more stable than asking one system to stretch past what it was shaped for.
Fifty thousand claims a day taught me that scaling problems dressed up as resource constraints are usually design problems in disguise. Find the right boundary, and the resource constraint stops being the problem. The design becomes the answer.
Top comments (0)