Salesforce Basics: 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 flows, no standard reports, and 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, and let a nightly batch move older rows to Transaction_Archive__b (Big Object). The audit team queries the archive by AccountId + date via a custom LWC.

🎬 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 (what dispatchers actually open).
2. A nightly Batch Apex 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 and empty the recycle bin.
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 in query flexibility: Queries must filter on the composite index, in index-field declaration order, with no gaps.
• There are no triggers, no flows, no standard reports, and 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.

🧭 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 recalculations 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. Only the last field you filter on may take a range. No query 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: Archiving is what keeps the hot object selective for Large Data Volume (LDV) scenarios.

⚠ 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 forever. Remember that 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 DataBase.emptyRecycleBin, or you haven't actually reclaimed any storage.
• Surface the archived history with a custom Lightning Web Component (LWC) that queries the Big Object, or use Async SOQL for bulk retrieval.
• Critical: Archive-then-delete must be idempotent (safe to re-run). Always reconcile row counts before deleting from the hot object.
• Decide on dependent data: attachments, chatter feeds, and child records.
• Alternative: Off-platform archival into a data lake (like Data 360 / Snowflake) is better when the archive must stay fully 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 filter on them in declaration order without skipping any field.
• Only the last field in your filter clause can use a range operator (>, <, LIKE).
• There is no query optimizer evaluating non-indexed columns at that scale; the index IS the access path.
• You must design the index backwards from the retrieval question (e.g., "Show this account's archived cases by date") rather than forwards from the data model.
• If you have two non-overlapping retrieval patterns, you may need to store the data twice with different composite indexes.

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

A2: You need a restore service that reads the Big Object row and re-inserts a transactional Case.

• The restored Case receives a new Record Id — the old Id is permanently gone.
• External references must key on CaseNumber or a preserved External_Id__c.
• Flag the record as restored to prevent the nightly batch from re-archiving it immediately.
• The archival design must preserve an immutable business key and sufficient field fidelity to reconstruct the record completely.

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

A: Storage cost is the minor reason. The real reason is performance: Massive row counts degrade reports, list views, selective queries, and sharing recalculations. Archiving keeps the working set lean so the hot path stays fast. Buying storage just pays rent on performance degradation.

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 on the retention policy with the business first (e.g., cases closed > 24 months ago). Then choose the storage target based on consumption needs:

• If users need history directly on record pages: Use a Big Object (queryable by index via LWC).
• If only analytics teams need it: Push to an external data lake / warehouse.
• If the archive must feed segments and AI: Land it in Data 360.
• Always implement a scheduled batch that copies, verifies row counts, and then deletes hot rows. Remember that standard reports do not read Big Objects, so plan an external or Data 360 view for historical reporting from the start.