External Data And Big Objects

External Data and Big Objects

This skill activates when a practitioner needs to store, retrieve, or decide the placement of extremely large or historical datasets in a Salesforce org. It covers the two main platform mechanisms for high-volume data: Big Objects (on-platform storage tier with Async SOQL) and External Objects (virtual, real-time access via Salesforce Connect). Use this skill to design composite indexes, author Async SOQL jobs, choose between the two mechanisms, and avoid the platform-specific failure modes that trip up every team the first time.

Before Starting

Gather this context before working on anything in this domain:

• Volume and query patterns: Big Objects are only queryable via composite index fields. If you do not know which fields will be filtered, you cannot design the index — and an unusable index means an unqueryable Big Object.
• Common wrong assumption: Practitioners assume they can write a standard SOQL query against a Big Object. Standard SOQL does not work on Big Objects; Async SOQL (via the AsyncQueryJob REST API) is the only scalable query mechanism.
• Storage vs API limits: External Objects count against SOQL query limits on every read because each query results in a live callout to the external system. Big Objects do not make callouts but consume Salesforce data storage. This distinction drives the core decision.

Core Concepts

Big Objects

Big Objects are a dedicated Salesforce storage tier designed for datasets in the hundreds of millions to billions of records. They are defined as custom metadata objects with the suffix __b and are inserted via Database.insertImmediate() (synchronous, fire-and-forget) or the Bulk API v1/v2.

Big Objects require a composite index: a mandatory, ordered list of fields that defines both the uniqueness constraint and the queryable access path. Every field used in a WHERE clause of an Async SOQL query must appear in the composite index in the same left-to-right order the query filters apply. Omitting a field from the index, or querying in a non-leading-column order, produces zero results with no error.

Platform limitations of Big Objects:

• No triggers (before/after insert/update/delete are not supported)
• No standard reports or list views
• No roll-up summary fields pointing at Big Object records
• No SOSL (Salesforce Object Search Language) support
• Standard SOQL returns results only for very small datasets; for production volumes always use Async SOQL

Async SOQL

Async SOQL is a REST-based API (/services/data/vXX.0/async-queries/) that submits a query as a background job and writes results to a target object (standard, custom, or Big Object). It is the only reliable way to query Big Objects at production scale.

Key facts:

• Jobs are submitted via POST with a query body and a targetObject specifying where to write results.
• Job status is polled via GET on the returned job ID.
• Async SOQL supports aggregations (COUNT, SUM, MIN, MAX) but not all SOQL features (e.g., no subqueries across Big Objects).
• Result rows are written as records into the target object — not returned inline to the caller.
• Jobs run asynchronously; there is no push notification; the caller must poll.

External Objects

External Objects (__x suffix) provide a virtual, real-time view of data stored outside Salesforce. They are powered by Salesforce Connect, which uses OData 2.0, OData 4.0, or custom Apex adapters to proxy read and write operations to the external system. Every SOQL query against an External Object translates into a live callout to the external data source at query time.

Because each read is a callout, External Objects consume Salesforce SOQL query limits and are subject to callout timeouts (default 10 seconds). They are best suited for small, latency-sensitive lookups of current external data — not for bulk historical data access.

Common Patterns

Pattern 1: High-Volume Event Log Archival with Big Objects

When to use: You are generating large numbers of platform events, integration logs, or IoT sensor readings and need to retain them for compliance or analytics beyond standard data retention windows.

How it works:

1. Define a Big Object (e.g., EventLog__b) with a composite index on (UserId__c, EventTime__c, EventType__c).
2. In the platform event subscriber or integration handler, call Database.insertImmediate() synchronously after event processing.
3. To query, submit an Async SOQL job targeting a scratch custom object or a summary Big Object for aggregated results.
4. Poll the job until status is Completed, then read result records from the target object.

Why not standard objects: Standard objects cannot hold billions of records without exceeding storage limits and degrading org query performance across unrelated workloads.

// Inserting a Big Object record
EventLog__b log = new EventLog__b(
    UserId__c      = UserInfo.getUserId(),
    EventTime__c   = DateTime.now(),
    EventType__c   = 'LOGIN',
    Payload__c     = JSON.serialize(eventData)
);
Database.SaveResult result = Database.insertImmediate(log);
if (!result.isSuccess()) {
    // Log errors — insertImmediate does not throw exceptions
    for (Database.Error err : result.getErrors()) {
        System.debug('Big Object insert error: ' + err.getMessage());
    }
}

Pattern 2: Real-Time External Data Lookup with External Objects

When to use: You need current data from an external ERP or data warehouse displayed on a Salesforce record page, and the volume of records displayed at once is small (under a few hundred rows per query).

How it works:

1. Configure a Salesforce Connect named credential and external data source pointing to the external OData endpoint.
2. Define the External Object with fields mapped to OData entity properties.
3. Create a lookup relationship from a standard or custom object to the External Object.
4. Use standard SOQL in Apex or standard list views to query the External Object — Salesforce handles the callout transparently.

Why not Big Objects: Big Objects are on-platform; if the data lives externally and must stay external (regulatory, ownership, or cost reasons), External Objects avoid the need to copy and sync data into Salesforce.

Decision Guidance

Recommended Workflow

Step-by-step instructions for an AI agent or practitioner activating this skill:

1. Gather context — confirm the org edition, relevant objects, and current configuration state
2. Review official sources — check the references in this skill's well-architected.md before making changes
3. Implement or advise — apply the patterns from Core Concepts and Common Patterns sections above
4. Validate — run the skill's checker script and verify against the Review Checklist below
5. Document — record any deviations from standard patterns and update the template if needed

Review Checklist

Run through these before marking work in this area complete:

• All fields used in WHERE clauses appear in the composite index in the correct left-to-right order
• Database.insertImmediate() return values are checked; errors are logged (method does not throw)
• Async SOQL job targets a valid writable object and the API version supports the query syntax used
• External Object SOQL query volume is within per-transaction callout limits (100 callouts / 10-second timeout per callout)
• Big Object storage growth projection has been reviewed against org data storage allocation
• No triggers, reports, or roll-up summary fields have been placed on a Big Object

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. Composite index order is absolute — Async SOQL silently returns zero results if you filter on a non-leading index column or skip a column in the filter chain. For index (A, B, C), a query WHERE B = :val returns nothing. Always filter left-to-right continuously from the first index column.
2. Database.insertImmediate does not throw exceptions — Unlike Database.insert, insert failures are returned as Database.SaveResult error objects. Unchecked, they silently fail and records are never written. Always inspect result.isSuccess() and log errors.
3. External Objects count against SOQL limits at query time — Every SOQL query against an External Object fires a live callout to the external system. In a single Apex transaction, this consumes from the 100-callout limit. Bulk processing logic that queries External Objects in a loop will hit limits immediately.

Output Artifacts

Related Skills

• data-archival-strategies — Use alongside this skill when the broader archival strategy (move to Big Object vs delete vs external storage) is not yet decided
• limits-and-scalability-planning — Use when storage growth projections and SOQL limit budgets need formal documentation