Big Objects & Data Archival

💬 In plain words:  Big Objects are a cheap warehouse shelf for billions of old rows: fast to store. But no triggers, no reports, limited queries. They are an ARCHIVE, not a working table — move cold data there and keep the hot data lean.

📌 Example:  7 years × 50M banking transactions can't live on a custom object. Keep 18 months hot, nightly batch moves older rows to Transaction_Archive__b (Big Object). The audit team queries the archive by AccountId + date via a custom screen.

🎬 Real-Life Example: 40 million Scans and a Dying Report  Every parcel gets scanned at pickup, hub, van, and door. That is 40 million Scan__c rows a year.

The Old/Bad Way:  Keep every scan in the normal custom object forever. Storage costs climb. List views time out. Reports crawl. Every SOQL filter fights an ocean of dead rows.

Why this is bad:  Normal objects are built for records people work on today. They are the wrong home for history nobody edits. Large data volume pain — skew, slow queries, indexing trouble — all starts here.

The New/Good Way:  1. Keep the last twelve months hot in Scan__c. That is what dispatchers actually open. 2. A nightly Batch copies older rows into a Big Object, with an index on Delivery Id and scan time. 3. Delete the moved rows from the hot object. 4. Query the archive only through its index fields — that is the contract Big Objects make with you.

The payoff:  Hot data lives in the house. Cold data lives in the warehouse next door — cheap, huge, and reachable when an auditor knocks.

🧠 Warehouse shelf:  Big Objects store billions but cannot act — no triggers, no reports. Archive there, work elsewhere.

Concept

Big Objects hold hundreds of millions to billions of rows, outside the standard transactional store.

•  You pay for that scale. Queries must filter on the composite index, in index-field order, with no gaps.

•  There are no triggers, no flows, no standard reports, no standard UI.

•  Field types are limited. Writes behave as upserts keyed by the index.

•  What you get is the platform-native place to archive: move cold rows off the transactional object and keep it lean. This ties into LDV in 10.6.

🧭 360 Card — Big Objects & Data Archival 

Rule:  archive cold rows out of the working set. Big Objects store. They do not act.

Gain:  billions of rows at low cost, off the transactional store. Reports, list views and sharing recalculation get their speed back.

Price:  no triggers, no flows, no standard reports. A restored row comes back with a new Id, so the old Id is gone for good.

Limits:  SOQL must filter the composite index fields in declaration order with no gaps. And only the last field you filter on may take a range. No optimizer helps you here.

Mirror — leave it in the transactional store:  everything keeps working normally. And everything keeps slowing down as rows pile up.

Later:  pick the index for how you will retrieve, not how you store. You cannot change it later without rebuilding the object.

At volume:  this is the volume answer for 10.6. Archiving is what keeps the hot object selective.

⚠ INTERVIEW TRAP:  They will ask about the restore path. A Big Object row comes back with a NEW Id, so the old Id is gone. Standard reports also cannot read Big Objects.

Core Q&A

Q: Design an archival strategy for a Case object growing 5M records/year where only 18 months must stay 'hot'.

🎯 Say this first:  Keep 18 months hot. Batch-move older Cases to a Big Object or external archive with a scheduled job. Report on hot data only. Query the archive on demand.

A: Start with the index. Define it on the Big Object to match how you will retrieve the data — AccountId, then ClosedDate, then CaseNumber, for example.

•  Run a scheduled Batch Apex pipeline that copies closed cases older than 18 months into the Big Object and hard-deletes the originals.

•  Call emptyRecycleBin, or you have not actually reclaimed anything.

•  Surface the archived history with a custom Lightning Web Components (LWC) that queries the Big Object, or use Async queries for bulk retrieval.

•  One thing is critical. Archive-then-delete must be safe to re-run, and you verify it — reconcile row counts — before anything is deleted.

•  Dependent data needs its own decision: attachments, feed, child records.

•  Name the alternative too. Off-platform archival into a data lake is better when the archive must stay reportable and joinable.

Follow-ups (scenario-based)

Q1: Why can't you just put a WHERE clause on any field of a Big Object. And what does that force in design?

A1: Big Object SOQL can filter only on index fields.

•  You must use them in declaration order.

•  You cannot skip one. And only the last field you filter on may take a range.

•  There is no query optimizer working over non-indexed columns at that scale. So the index IS the access path.

•  You design it backwards from the retrieval question — 'show this account's archived cases by date' — not forwards from the data model.

•  If you have two retrieval patterns that do not overlap, you may have to store the data twice, with a different index each time.

Q2: The business asks to 'un-archive' a case for a reopened dispute. What does your design need?

A2: You need a restore path: a service that reads the Big Object row and recreates a transactional Case.

•  The restored Case gets a new Id.

•  The old Id is gone forever.

•  So any external reference has to key on CaseNumber, or on an external Id that you preserved in the archive.

•  Flag the record as restored, or it will be re-archived in a loop.

•  This is why the archival design must keep an immutable business key, and enough field fidelity to reconstruct the record.

•  It is also why 'we can always delete it, it's archived' needs a contractual definition of what restore actually means.

Q (compare): Big Objects vs just buying more storage — why archive at all?

A: Storage cost is the smaller reason.

•  The big one is speed: huge row counts slow reports, list views, and sharing recalculations (see 10.6).

•  Archiving moves cold rows out of the working set, so everything on the hot path gets faster.

•  Buying storage keeps the pain and pays rent on it.

Q: Where does archived data actually go, and how do you still report on it?

🎯 Say this first:  Write the retention rule in months first. Then pick the store by who still needs to read it.

A: Agree the retention rule with the business before you design anything. For example, cases closed more than twenty-four months ago. Then choose the store by the reader.

•  If users still need history on the record page, use a Big Object: billions of rows, cheap, queryable on the index.

•  If only analytics needs it, push it to an external warehouse or data lake.

•  If the archive should still feed segments and AI, land it in Data 360.

•  A scheduled batch copies, verifies the counts, and only then deletes from the hot object.

•  Two things people forget. The restore path, for when Legal asks for one record back.

•  And reporting: standard reports do not read Big Objects. So plan a Data 360 or warehouse view for history reporting from the start.