Apex Bulkification Explained: From 'Too Many SOQL Queries: 101' to Bulk-Safe Code

Bulkification means writing Apex that handles a list of records with a fixed number of queries and DML statements, however many records arrive. Triggers receive up to 200 records at a time, and a synchronous transaction can run only 100 SOQL queries and 150 DML statements. Put a query inside a for loop and the 101st record throws System.LimitException: Too many SOQL queries: 101. The fix is always the same shape: collect IDs into a Set, query once into a Map, process in memory, and save with one DML statement per object.

Below I'll show why the error happens, the anti-patterns that cause it, a full before-and-after refactor of a realistic trigger, how to test it with more than 200 records, and how the same idea applies to Flows.

Why triggers receive up to 200 records at a time

A trigger doesn't fire once per record. It fires once per chunk of records. When Data Loader, an API client or another piece of Apex saves 200 Accounts, your trigger runs once with all 200 in Trigger.new.

Two details catch people out:

• Chunks share a transaction. If one Apex DML statement saves 500 records, the trigger runs three times (200, 200, 100) in the same transaction. A trigger that uses 40 queries per chunk passes with 200 records and fails with 500.
• Your trigger isn't alone. Other triggers, Flows, validation rules and managed packages on the same object all run in that transaction and draw from the same limits.

The governor limits you'll hit most often:

These limits exist because your code runs on shared infrastructure. They aren't a bug to work around. They describe how the platform expects you to write code.

The anti-patterns: SOQL and DML inside loops

Here's the classic version:

for (Contact c : Trigger.new) {
    Account acc = [SELECT Id, Rating FROM Account WHERE Id = :c.AccountId];
    c.Description = 'Account rating: ' + acc.Rating;
}

That's one query per Contact. At 200 Contacts it fails on the 101st.

DML in a loop is the same mistake with a different limit:

for (Opportunity opp : closedWon) {
    insert new Task(Subject = 'Send welcome pack', WhatId = opp.Id);
}

That's one DML statement per Opportunity, and the 151st throws Too many DML statements: 151.

The sneaky version is the one that looks clean:

for (Case c : Trigger.new) {
    c.Priority = PriorityService.priorityFor(c.AccountId);
}

If priorityFor runs a query, this is still a query in a loop. It's just hidden one method away. When I review code, I follow every method called inside a loop to see whether it touches the database.

The bulk-safe toolkit: sets, maps and aggregate queries

Almost all bulk-safe Apex uses these four tools.

Sets to collect IDs. A Set<Id> removes duplicates for free, so 200 Contacts on 3 Accounts produce 3 IDs.

Maps to look things up in memory. Passing a query straight into the Map constructor gives you records keyed by ID:

Set<Id> accountIds = new Set<Id>();
for (Contact c : contacts) {
    if (c.AccountId != null) {
        accountIds.add(c.AccountId);
    }
}

Map<Id, Account> accountsById = new Map<Id, Account>([
    SELECT Id, Rating FROM Account WHERE Id IN :accountIds
]);

for (Contact c : contacts) {
    Account acc = accountsById.get(c.AccountId);
    if (acc != null) {
        c.Description = 'Account rating: ' + acc.Rating;
    }
}

One query, whether there are 2 Contacts or 200.

Relationship queries for parent and child data. When you need children grouped by parent, a subquery saves a second query and the grouping code:

for (Account acc : [
    SELECT Id, Name, (SELECT Id, Email FROM Contacts WHERE Email != null)
    FROM Account
    WHERE Id IN :accountIds
]) {
    Integer reachable = acc.Contacts.size();
    // work with the parent and its children together
}

Aggregate queries for counts and sums. Don't query 10,000 rows to count them in Apex. Let the database do it:

Map<Id, Integer> openCasesByAccount = new Map<Id, Integer>();
for (AggregateResult row : [
    SELECT AccountId, COUNT(Id) openCount
    FROM Case
    WHERE AccountId IN :accountIds AND IsClosed = false
    GROUP BY AccountId
]) {
    openCasesByAccount.put((Id) row.get('AccountId'), (Integer) row.get('openCount'));
}

One caveat: every row an aggregate query reads counts toward the 50,000-row limit, even though you only get the grouped rows back. It saves heap and CPU time, but it doesn't buy you extra rows.

And for DML: add records to a List inside the loop and run one insert, update or upsert after it. If some records might legitimately fail, use Database.insert(records, false) and handle each SaveResult, rather than letting one bad record roll back the rest.

A realistic refactor: before and after

Here's a requirement I've seen in some form in nearly every service org:

1. When a Case is created on a Hot account, set its Priority to High.
2. When an account goes over 5 open Cases, create one escalation Task for the account owner.

Before: works in the demo, fails in production

trigger CaseTrigger on Case (before insert, after insert) {
    if (Trigger.isBefore) {
        for (Case c : Trigger.new) {
            if (c.AccountId != null) {
                Account acc = [SELECT Rating FROM Account WHERE Id = :c.AccountId];
                if (acc.Rating == 'Hot') {
                    c.Priority = 'High';
                }
            }
        }
    } else {
        for (Case c : Trigger.new) {
            Integer openCases = [SELECT COUNT() FROM Case WHERE AccountId = :c.AccountId AND IsClosed = false];
            if (openCases > 5) {
                Account acc = [SELECT Name, OwnerId FROM Account WHERE Id = :c.AccountId];
                insert new Task(
                    Subject = 'Escalation: ' + acc.Name + ' has ' + openCases + ' open cases',
                    WhatId = acc.Id,
                    OwnerId = acc.OwnerId
                );
            }
        }
    }
}

Creating one Case through the UI works fine. Here's what happens when an email-to-case burst or an import creates 200:

• The before trigger runs up to 200 queries and fails at the 101st.
• The after trigger runs two queries and one insert per Case, so it would fail even sooner.
• There's also a logic bug: an account with 30 new Cases gets up to 25 identical Tasks, because every Case past the fifth creates one.

Bulk testing exposes that last one too, which is why bulkification is about correctness as well as limits.

After: a bulk-safe handler

The trigger becomes a dispatcher with no logic in it:

trigger CaseTrigger on Case (before insert, after insert) {
    if (Trigger.isBefore && Trigger.isInsert) {
        CaseTriggerHandler.beforeInsert(Trigger.new);
    } else if (Trigger.isAfter && Trigger.isInsert) {
        CaseTriggerHandler.afterInsert(Trigger.new);
    }
}

The handler uses the toolkit above:

public with sharing class CaseTriggerHandler {
    @TestVisible
    private static final Integer ESCALATION_THRESHOLD = 5;

    public static void beforeInsert(List<Case> newCases) {
        Set<Id> accountIds = new Set<Id>();
        for (Case c : newCases) {
            if (c.AccountId != null) {
                accountIds.add(c.AccountId);
            }
        }
        if (accountIds.isEmpty()) {
            return;
        }

        Map<Id, Account> accountsById = new Map<Id, Account>([
            SELECT Id, Rating FROM Account WHERE Id IN :accountIds
        ]);
        for (Case c : newCases) {
            Account acc = accountsById.get(c.AccountId);
            if (acc != null && acc.Rating == 'Hot') {
                c.Priority = 'High';
            }
        }
    }

    public static void afterInsert(List<Case> newCases) {
        Map<Id, Integer> newOpenByAccount = new Map<Id, Integer>();
        for (Case c : newCases) {
            if (c.AccountId != null && !c.IsClosed) {
                Integer soFar = newOpenByAccount.containsKey(c.AccountId) ? newOpenByAccount.get(c.AccountId) : 0;
                newOpenByAccount.put(c.AccountId, soFar + 1);
            }
        }
        if (newOpenByAccount.isEmpty()) {
            return;
        }

        Set<Id> crossedThreshold = new Set<Id>();
        for (AggregateResult row : [
            SELECT AccountId, COUNT(Id) openCount
            FROM Case
            WHERE AccountId IN :newOpenByAccount.keySet() AND IsClosed = false
            GROUP BY AccountId
        ]) {
            Id accountId = (Id) row.get('AccountId');
            Integer openNow = (Integer) row.get('openCount');
            Integer openBefore = openNow - newOpenByAccount.get(accountId);
            if (openNow > ESCALATION_THRESHOLD && openBefore <= ESCALATION_THRESHOLD) {
                crossedThreshold.add(accountId);
            }
        }
        if (crossedThreshold.isEmpty()) {
            return;
        }

        List<Task> escalations = new List<Task>();
        for (Account acc : [SELECT Id, Name, OwnerId FROM Account WHERE Id IN :crossedThreshold]) {
            escalations.add(new Task(
                Subject = 'Escalation: ' + acc.Name + ' has more than ' + ESCALATION_THRESHOLD + ' open cases',
                WhatId = acc.Id,
                OwnerId = acc.OwnerId,
                Priority = 'High'
            ));
        }
        insert escalations;
    }
}

What changed:

• Before insert: one query per chunk instead of one per Case. Setting c.Priority in a before trigger needs no DML at all, because the record hasn't been saved yet.
• After insert: one aggregate query, one Account query and one insert per chunk, whatever the volume.
• The duplicate-task bug is gone. The handler creates a Task only when an account crosses the threshold. It compares the open count before this chunk with the count after it. That matters because a 300-Case insert arrives as two chunks, and without the "before" comparison the second chunk would escalate the same account again.

The handler is with sharing, so it respects the running user's record access, which suits Cases created by agents. If this logic must run regardless of who creates the Case, for example from an integration user with narrow access, make that a deliberate decision and document it. Don't get there by accident.

Testing with 200+ records

A bulkified trigger needs a test that proves it. I always test with more than 200 records, so the test crosses a chunk boundary and catches bugs like the duplicate escalation:

@IsTest
private class CaseTriggerHandlerTest {
    private static final Integer ACCOUNT_COUNT = 10;
    private static final Integer CASES_PER_ACCOUNT = 30;

    @IsTest
    static void handlesThreeHundredCasesAcrossTwoChunks() {
        List<Account> accounts = new List<Account>();
        for (Integer i = 0; i < ACCOUNT_COUNT; i++) {
            accounts.add(new Account(
                Name = 'Bulk Account ' + i,
                Rating = Math.mod(i, 2) == 0 ? 'Hot' : 'Warm'
            ));
        }
        insert accounts;

        List<Case> cases = new List<Case>();
        for (Account acc : accounts) {
            for (Integer j = 0; j < CASES_PER_ACCOUNT; j++) {
                cases.add(new Case(AccountId = acc.Id, Subject = 'Case ' + j, Status = 'New', Origin = 'Web'));
            }
        }

        Test.startTest();
        insert cases;
        Test.stopTest();

        Assert.areEqual(
            ACCOUNT_COUNT,
            [SELECT COUNT() FROM Task WHERE Subject LIKE 'Escalation:%'],
            'Each account should be escalated exactly once, even across chunks'
        );
        Assert.areEqual(
            (ACCOUNT_COUNT / 2) * CASES_PER_ACCOUNT,
            [SELECT COUNT() FROM Case WHERE Priority = 'High'],
            'Every Case on a Hot account should be High priority'
        );
    }
}

Run that against the "before" trigger and it fails with Too many SOQL queries: 101. Run it against the handler and it passes, with exactly 10 escalation Tasks.

A few more testing habits:

• Put the bulk DML between Test.startTest() and Test.stopTest() so it gets its own set of limits, separate from your setup.
• Add an update test as well if the trigger handles updates. Bulk updates are where Trigger.oldMap comparisons go wrong.
• If you want a hard guardrail, call Limits.getQueries() inside the test before Test.stopTest() and assert it's below a budget. Keep the budget loose, because other automation in the org shares it.

I cover test design in more depth, including factories, @TestSetup and negative tests, in Apex test classes: stop chasing 75% coverage.

When bulkification isn't enough

Some jobs are simply too big for one transaction, even when they're written perfectly. Recalculating something across 2 million records will blow the 50,000-row or CPU limit no matter how you write it. That's when you move the work out of the trigger:

• Queueable Apex for follow-up work that can happen a moment later, with higher async limits.
• Batch Apex for processing large data sets in scoped chunks, each with its own limits.
• Platform Events to decouple a heavy process from the user's save.

Bulkification is still the base layer. Batch Apex with a query inside its execute loop fails just as surely as a trigger does.

Bulkification in Flows

Record-triggered flows follow the same rules, with a twist. When 200 records fire a record-triggered flow, Salesforce runs 200 flow interviews but batches their data elements together. A Get Records element that every interview reaches at the same point runs as a single query across the batch. That's why a simple record-triggered flow is usually bulk-safe without any effort from you.

The batching stops at loops. A Get, Create, Update or Delete Records element inside a Loop runs once per iteration, exactly like SOQL in an Apex for loop. A flow that loops over 300 related Contacts and updates each one inside the loop hits the same limits as the "before" trigger.

The Flow version of the fix:

• Inside the loop, use Assignment elements to change the record variable and add it to a collection variable.
• After the loop, run one Update Records (or Create Records) element on the collection.
• Get related records once before the loop, with a filter that fetches everything you need, rather than once per iteration.
• Use a before-save flow (Fast Field Updates) when you're only changing fields on the triggering record. Like a before trigger, it needs no DML at all.

Remember that flows and Apex share the transaction's limits. A bulkified trigger can still fail because a flow on the same object queries in a loop, and the error message won't point at the flow. When I debug a limit error, the debug log's limit usage lines tell me who spent the budget.

The bulk-safe checklist

Before any trigger, handler or record-triggered flow goes to production, check that:

• no SOQL, SOSL or DML sits inside a loop, including inside methods called from a loop
• IDs are collected into a Set and parent data is queried once into a Map
• counts and sums use aggregate queries, not Apex loops over thousands of rows
• every object is saved with one DML statement per chunk
• a test inserts or updates more than 200 records in one statement and asserts the results
• Flows have no data elements inside loops.

Learn it by building it

Bulkification clicks fastest when you watch code fail at record 101 and then fix it. That's how I teach it. In Salesforce Development (Visualforce & Apex) I write SOQL, DML, collections, async Apex and test classes live in a real org, hitting the limits on camera so you see exactly why the bulk-safe version works. My free Apex Triggers Framework 101 course takes you from your first trigger to a handler framework like the one above.

For the theory and interview angle, see the Apex learning guide, the Apex trigger interview questions and the SOQL interview questions.