Mastering Salesforce Data Types: Objects, Fields, External Objects, Big Objects & Polymorphic Fields

Introduction

In Salesforce, data modeling sits at the core of everything—APIs, automation, UI, analytics, all of it. Beyond standard and custom objects, you’ve also got External Objects for virtualized data, Big Objects for massive, append-only datasets, and polymorphic fields (like WhatId/WhoId) for flexible relationships. This guide gives Developers, Admins, and Architects a quick, practical playbook for choosing the right data type, modeling relationships, and querying efficiently.

Core Concepts

1) Objects & Fields (your building blocks)

• Standard objects: Predefined (e.g., Account, Contact, Opportunity).

• Custom objects: Your domain entities (e.g., Invoice__c), supporting master-detail or lookup relationships, record types, validation, triggers, flows, and reporting.

• Field types: Text, number, checkbox, date/time, picklist, formula, roll-up (MD only), geolocation, encrypted (Shield), external ID, unique.

Design tips

• Normalize where it clarifies the model; denormalize (carefully) when reporting or performance needs it.

• Prefer Master-Detail when you need ownership alignment, roll-ups, or cascading deletes; use Lookup for looser coupling or cross-object sharing.

• Index fields you filter on often, and design for selective SOQL.

2) External Objects (virtualized data)

• What: Objects mapped to external systems via OData/Salesforce Connect—rows aren’t stored in Salesforce.

• When to use: Near-real-time access to large datasets you don’t want to replicate (ERP catalogs, legacy records).

• Pros: No storage growth, live data, reportable (with limits), point-and-click setup.

• Trade-offs: Latency depends on the provider, limited triggers/transactions, some SOQL features aren’t supported, and licensing applies.

3) Big Objects (mass scale, historical/immutable)

• What: Natively stored, horizontally scalable tables for billions of rows (logs, histories).

• Characteristics: Append-only, read-optimized; you define an index (1–5 fields) that dictates the filter order in queries.

• Querying: Subset of SOQL—filters must include leading index fields, in order. Great for time-series/history lookups.

• Access patterns: Async processing, extract to analytics, archive old transactional data.

4) Polymorphic Fields (flexible relationships)

• What: A single field can relate to multiple object types (e.g., WhoId → Lead or Contact; WhatId → Account, Opportunity, or custom).

• Why: Reuse one relationship slot across many targets (Tasks, Events, Feed Items).

• Querying: Use TYPEOF in SOQL or instanceof/getSObjectType() in Apex to branch per target type.

5) Real-world choices

• External Objects for a product catalog in SAP—live pricing without replication.

• Big Objects to retain 5+ years of Case history and event logs—cheap, scalable archive.

• Polymorphic Tasks to track activities across Accounts, Opportunities, or custom records—one UI, many targets.

Code Snippets / Examples

A) Polymorphic queries with TYPEOF and Apex branching

// SOQL using TYPEOF to project different fields by target type
List<Task> tasks = [
  SELECT Id, Subject,
    TYPEOF What
      WHEN Account THEN Name, Industry
      WHEN Opportunity THEN Name, StageName, Amount
      ELSE Id
    END
  FROM Task
  WHERE WhatId != NULL
  LIMIT 200
];

// Apex branching by sObjectType at runtime
for (Task t : tasks) {
    if (t.What.getSObjectType() == Account.SObjectType) {
        Account a = (Account) t.What;
        System.debug('Task on Account: ' + a.Name);
    } else if (t.What.getSObjectType() == Opportunity.SObjectType) {
        Opportunity o = (Opportunity) t.What;
        System.debug('Task on Opportunity: ' + o.Name + ' / ' + o.StageName);
    }
}

B) External Objects (OData/Salesforce Connect) — SOQL & limitations

// External object ends with __x
List<EXTERNAL_PRODUCT__x> items = [
    SELECT ExternalId__c, Name, Price__c
    FROM EXTERNAL_PRODUCT__x
    WHERE ExternalId__c = :someKey
];
// No triggers on external objects; DML behavior is limited/passthrough (depends on adapter)

C) Big Objects — querying on the index

// Big Object: CASE_HISTORY__b with index (CaseNumber__c, EventTime__c)
List<CASE_HISTORY__b> history = [
  SELECT CaseNumber__c, EventTime__c, Action__c, UserId__c
  FROM CASE_HISTORY__b
  WHERE CaseNumber__c = :caseNum
    AND EventTime__c >= :DateTime.now().addDays(-7)
  ORDER BY EventTime__c ASC  // supported only if aligned to index
  LIMIT 10000
];

Best practices

• Filter in the exact order of the defined index; avoid non-indexed predicates.

• Use async patterns for heavy reads; export to analytics for aggregations.

Interview Questions (with brief answers)

Master-Detail vs. Lookup—when to use which?
MD for ownership alignment, roll-ups, cascade delete; Lookup for looser coupling or independent sharing.

When choose External Objects vs. replication?
External for live data without storage growth; replicate if you need complex triggers, heavy joins, or offline resilience.

How do Big Objects scale—and how do you query them?
They’re append-only and index-driven; queries must filter on index fields in order.

What are polymorphic fields and where used?
Fields that reference multiple object types (e.g., WhoId, WhatId on Task/Event).

How do you report when data lives in External Objects?
Use Salesforce Connect for basics; for complex analytics, consider caching/materialized views or replication.

What causes non-selective SOQL on large datasets?
Filtering on non-indexed fields, leading wildcards, broad ORs; fix with indexes, selective predicates, skinny tables where appropriate.

How do you enforce FLS/CRUD with polymorphic access?
Check permissions per resolved target type (Schema.sObjectType and Security.stripInaccessible).

Conclusion

Pick the right construct for the job:

• Use standard/custom objects for day-to-day CRM data.

• Use External Objects when you need live, remote datasets without storing them.

• Use Big Objects for long-term, massive, append-only histories.

• Use polymorphic fields to simplify relationships across many targets (Tasks/Events).

Get these choices right, and you’ll have a model that scales cleanly, queries efficiently, and plays nicely with automation and analytics.