Salesforce Big Objects: Architecture, SOQL Indexing & Data Archiving Guide

In plain words: A Salesforce Big Object is a specialized, high-scale database table with the suffix __b that allows you to store and query hundreds of millions or billions of records natively within Salesforce at a fraction of standard data storage costs, without slowing down your everyday CRM transactions.

Standard Salesforce objects like Account or custom objects (__c) are optimized for real-time transactional processing (OLTP). However, retaining years of audit trails, historical customer interactions, or high-volume IoT telematics on standard objects quickly drains data storage allocations and degrades query performance. Big Objects solve Large Data Volume (LDV) challenges by using an underlying columnar datastore engineered for massive retention and predictable query speeds.

Standard vs. Custom Big Objects

• Standard Big Objects: Pre-built by Salesforce to power platform-managed features such as Field History Tracking (FieldHistoryArchive) and Real-Time Event Monitoring logs.
• Custom Big Objects: Defined by developers with the __b suffix to house proprietary archives, historical logs, billing receipts, and multi-year customer activity feeds.

Defining a Custom Big Object & Index Architecture

Unlike standard objects, a Big Object requires defining a composite Custom Index before it can be deployed. This index forms the unique primary key for every record and dictates how data is queried.

Step-by-Step Metadata Definition:

Big Objects are deployed via Metadata API using .object-meta.xml definitions:

<?xml version="1.0" encoding="UTF-8"?>
<CustomObject xmlns="http://soap.sforce.com/2006/04/metadata">
    <label>Customer Interaction History</label>
    <pluralLabel>Customer Interaction Histories</pluralLabel>
    <deploymentStatus>Deployed</deploymentStatus>

    <!-- Custom Fields -->
    <fields>
        <fullName>AccountId__c</fullName>
        <label>Account ID</label>
        <type>Text</type>
        <length>18</length>
        <required>true</required>
    </fields>
    <fields>
        <fullName>InteractionDate__c</fullName>
        <label>Interaction Date</label>
        <type>DateTime</type>
        <required>true</required>
    </fields>
    <fields>
        <fullName>Channel__c</fullName>
        <label>Channel</label>
        <type>Text</type>
        <length>50</length>
    </fields>

    <!-- Composite Primary Key Index -->
    <indexes>
        <fullName>Interaction_Index</fullName>
        <label>Interaction Index</label>
        <fields>
            <name>AccountId__c</name>
            <sortDirection>ASC</sortDirection>
        </fields>
        <fields>
            <name>InteractionDate__c</name>
            <sortDirection>DESC</sortDirection>
        </fields>
    </indexes>
</CustomObject>

Warning Trap — Immutability of Big Object Indexes: Once a Big Object is deployed with an index, that index cannot be modified or reordered. To alter an index, you must delete the entire Big Object and re-create it from scratch. Plan your composite key ordering carefully to match the exact search paths required by your queries.

Querying Big Objects via SOQL

SOQL queries executed against Big Objects must strictly follow the defined sequence of fields in the index. You cannot filter on non-indexed fields or jump ahead in the composite key hierarchy.

// Valid: Filtering adheres strictly to the defined index order
List<Customer_Interaction__b> interactions = [
    SELECT AccountId__c, InteractionDate__c, Channel__c 
    FROM Customer_Interaction__b 
    WHERE AccountId__c = '001xx000003DGbYAAW' 
      AND InteractionDate__c <= :Datetime.now()
    ORDER BY AccountId__c ASC, InteractionDate__c DESC 
    LIMIT 100
];

Inserting and Ingesting Data

Data can be written to Big Objects using asynchronous Apex database operations or Bulk API 2.0:

// Asynchronous Insertion via Apex Database Queue
Customer_Interaction__b logRecord = new Customer_Interaction__b(
    AccountId__c = '001xx000003DGbYAAW',
    InteractionDate__c = Datetime.now(),
    Channel__c = 'Web Portal'
);

// Big Object insertions are queued asynchronously
Database.SaveResult sr = Database.insertImmediate(logRecord);
System.debug('Record queued for write: ' + sr.isSuccess());

360 Architecture Summary:

• Supported Field Types: Text, Long Text Area, Number, DateTime, and Lookup (to standard/custom objects).
• DML Restrictions: Standard DML (insert, update, delete) is not supported. Use Database.insertImmediate() or Bulk API. Existing records with identical composite index keys are automatically overwritten (upsert behavior).
• No Triggers or Process Automation: Big Objects do not support Apex Triggers, Salesforce Flows, Workflow Rules, or Validation Rules.
• Index Limits: Up to 5 fields per index with a maximum total index length of 100 characters.

Key Architectural Use Cases

• 360-Degree Customer View: Store years of historical transactions, purchase logs, and customer support touches without polluting production object storage.
• Regulatory Auditing & Compliance: Archive financial transactions, security access logs, and compliance records required by GDPR, HIPAA, or SOX mandates.
• Data Archiving Pipelines: Build automated scheduled Batch Apex or Flow jobs to archive closed-won opportunities and resolved cases older than 3 years into Big Objects.

Core Takeaway: Salesforce Big Objects provide a cost-effective, billion-record storage tier inside your org. Designing clean composite indexes upfront guarantees sub-second SOQL retrieval without impacting transactional platform limits.