100k Blobs a Day and the Retention Rule Azure Doesn't Have

How a “should we just move this to SQL?” question turned into a lesson about where cloud costs actually hide.

---

It started with a Slack message about decommissioning a web app.

“How much storage is that? Curious what the pros and cons are of storing in blob vs Azure SQL?”

I thought I’d answer it in five minutes. I was wrong in three separate ways, and each one taught me something.

Wrong #1: “Storage is basically free”

My first answer was the one everyone gives: blob storage is ~$0.02/GB/month, the data is small, cost is a rounding error. Move it to SQL, kill the web app, save the hosting bill.

Then I actually looked at the write rate: ~100,000 blobs per day, one per availability check, around 20 KB each, never deleted.

That changes everything:

• 2 GB/day → ~60 GB/month → ~730 GB/year, growing forever.
• Blob storage also charges per write transaction. 3 million writes/month is another $15–20, every month, regardless of retention.
• After one year: ~$28/month. After three: ~$60/month and climbing.

Blob storage held if nothing is ever deleted (GB)

Show data

	Stored
Year 1	730 GB
Year 2	1,460 GB
Year 3	2,190 GB

Still not a crisis, but no longer “free” — and crucially, growing without bound.

Lesson: Ask for the write rate before you quote a storage price. Per-GB pricing is the number vendors put on the pricing page; per-transaction and unbounded-growth are the numbers that show up on your invoice.

Wrong #2: “Then move it to SQL”

Fine — if blob is growing, put the data in Azure SQL where it’s queryable and joinable, and let Access read it directly.

Except the filtered data is 0.5–1 GB/day. In Azure SQL that’s 200–350 GB/year at 6–14× the per-GB price of blob, and it pushes the database past the included storage of its tier. Worse — and this is the part I underweighted until my colleague corrected me — retention in SQL is expensive in a way that has nothing to do with storage:

• Bulk-deleting hundreds of GB of old rows spikes DTUs on the whole database.
• Long-running deletes take locks and block the transactions that Access users and background jobs are running.

Blob storage has neither problem. Deleting a blob is a free operation with zero impact on anything else.

Lesson: “Cheaper per GB” and “cheaper to operate” are different questions. For high-volume, write-once, rarely-read data, blob wins on operations even when SQL would win on query ergonomics. The right split was: raw data stays in blob; SQL only gets the small filtered slice for events someone actually analyses.

Wrong #3: “Lifecycle policy will handle retention”

So: keep blob, add retention. Azure Blob has lifecycle management — a rule like “delete blobs older than 90 days.” Five-minute fix.

But the requirement wasn’t “delete after N days.” It was:

Delete a blob when the event it belongs to has passed.

An availability check taken 300 days before a concert is still relevant until the concert happens. A check taken 3 days before a different concert is garbage a week later. Age is the wrong axis.

And here’s the thing Azure lifecycle policies cannot do: compare against a date stored somewhere else. They can filter on blob age (created/modified/accessed), prefix, and blob index tags — but tag matching in lifecycle rules is equality only. There is no “delete where tag EventDate < today.”

So we needed a small job of our own.

What we built

The information to make the decision already existed in SQL: every blob is named by an AvailabilityCheckAutoGroupID, and that group row points to an event with an EventStartDate. The design fell out from there:

flowchart LR
  T([Daily timer function]) --> Q[SQL: group IDs whose event<br/>ended over 7 days ago,<br/>not flagged deleted]
  Q --> D[Blob Batch API:<br/>delete id.json,<br/>256 per request]
  D --> F[SQL: flag those<br/>IDs as deleted]
  F -->|repeat until empty| Q

A few details made it cheap and safe:

One nullable column, one filtered index. Instead of a tracking table, we added BlobDeletedDate to the existing groups table and a filtered index WHERE BlobDeletedDate IS NULL. The index only ever contains rows still waiting for cleanup, so it stays tiny on a table with tens of millions of rows.

Don’t scan what can’t exist. Blobs only started being written on a specific date. Rather than back-filling a “deleted” flag onto tens of millions of historical rows (exactly the kind of mass UPDATE we were trying to avoid), we found the first group ID created after that timestamp and made everything below it out of scope with a single WHERE AvailabilityCheckAutoGroupID >= @FirstBlobGroupID. Cheapest possible seek, zero backlog.

Treat 404 as success. Blob batch deletes return a status per sub-request. A 202 means deleted; a 404 means it was already gone. Both get flagged done. Anything else stays unflagged and is retried next run. Because progress is tracked in SQL, the function can time out or be killed halfway through with no harm.

Keep a dumb safety net. We also added a plain lifecycle rule: delete anything older than 540 days. It catches blobs the join misses — groups whose event row was deleted, orphaned files, whatever. The two mechanisms never conflict; the rule only touches what the job hasn’t already removed.

What it costs

This is the part that surprised my colleague:

	Before	After
Write transactions	$15–20/mo	$15–20/mo (unchanged)
Storage	$13 → $40/mo and growing	$2–7/mo, flat
Cleanup job	—	< $1/mo

The job itself is essentially free: blob deletes aren’t billed, a daily function fits inside the free grant, and the SQL side is a few hundred index seeks. Steady-state storage becomes 2 GB/day × average days between check and event — a number you can actually predict.

The trap I almost walked into

While designing this I nearly added “move to Cool tier after 30 days” to the lifecycle rule. Cool storage is half the price per GB — obvious win, right?

No. Tier changes are billed as write operations in the destination tier, and Cool writes cost ~$0.10 per 10k. At 3 million blobs a month that’s ~$30/month to tier, more than the entire storage bill we were trying to reduce.

At high blob counts with small files, tiering is a cost increase. Delete or don’t; don’t tier.

Takeaways

1. Get the write rate first. Every cost conversation about storage is really a conversation about volume × transactions × time.
2. Per-GB price is not the decision. For write-heavy, read-light data, operational cost of retention (DTU spikes, blocking, mass deletes) dominates. Blob’s free, side-effect-free delete is a genuine architectural advantage.
3. Lifecycle policies are age-based. If your retention rule depends on business data — an event date, an order status, a customer tier — you need a small job that reads that data. Plan for it.
4. Don’t back-fill; bound. When you introduce tracking on a huge table, find the boundary where the new behaviour started and filter to it, instead of mass-updating history.
5. Belt and braces. A precise job plus a coarse safety-net rule is cheaper and safer than either one alone.
6. Small files make tiering expensive. Check per-operation prices, not just per-GB, before adding a tier transition.

And one bonus finding that had nothing to do with cost: while reading the service code I found the storage account key hardcoded in a C# constant, committed to git. It had been sitting there for years. Decommissioning the web app makes it go away — but the audit that found it only happened because someone asked “how much does this cost?”

Sometimes the cheapest question is the one that makes you read the code.