Data management

Metadata is how an application is defined; deploying it is its own pipeline with its own guarantees. This page is about the other half — the rows. Getting them in, getting them out, changing thousands at once, keeping them unique, keeping them recoverable, keeping the old ones out of the hot path, and destroying them when someone has the legal right to demand it.

Two decisions govern everything below.

There is one write path. Every operation that changes record data — a form save, an import row, a mass update, a merge, a restore — enters the same save order of execution and the same three access planes. There is no bulk mode that skips validation, no import setting that suppresses automation, and no privileged loader that runs as the system. “Did my triggers fire?” has one answer: yes, unless a named bypass was explicitly engaged, and the job record says which one.

There is one job engine. Import, export, mass update, mass delete, mass transfer, the duplicate sweep, archive, purge, and restore are all the same thing: a scoped set of rows, a declared operation, a dry run, chunked execution, and a result set. They differ in the operation, not in the machinery, so they share one monitor, one error format, one cancellation semantic, and one set of permissions. The incumbent splits this into a browser wizard, a desktop application, two Setup pages, a REST API, and a paid managed package, each with its own ceiling and its own answer to whether automation ran.

The model

Operation	Direction	Failure atom	Runs the save order
Import	in	The row	Yes
Export	out	n/a (read-only)	n/a — but FLS and record access apply
Mass update / delete / transfer	in place	The row	Yes
Merge	in place	The whole merge (atomic)	Yes, on the survivor
Archive	hot → cold	The row	No — a storage move, not a value change
Purge	out of existence	The row’s delete closure	Yes, on the delete
Restore	backup → live	The row	Configurable; defaults to off

Restore is the one deliberate exception, and the reason is specific: a restore is asserting that a prior state was already valid, and re-running automation on it would fire notifications, re-post integrations, and re-trip validation rules that were authored after the data was written. A restore therefore writes through the constraint layer — foreign keys, unique indexes, check constraints, row-level security — but bypasses ADJUST, EFFECTS, and POST-COMMIT unless the operator opts in. The opt-in exists because a restore into a schema that has moved on sometimes should be re-derived. Which mode ran is recorded on the restore job.

Three ways a record can stop being there

These are constantly conflated, they behave differently, and an admin who confuses them loses data.

State	Row still exists	Still queryable	Comes back by	Frees storage
Soft-deleted (recycle bin)	Yes, tombstoned — deleted_at set	Only through includeDeleted and the Recycle Bin	Undelete	No
Archived	Yes, in a cold partition	Yes, through the same object, at cold-path latency	Unarchive, or nothing — it never left	Yes, from the hot tier
Purged	No	No	Restore from backup, if inside the retention window	Yes
Erased	No	No	Nothing. By design	Yes

Soft delete is reversible and cheap. Archive is not a deletion at all — it is a storage-tier move that keeps the row addressable. Purge is real destruction of the live row, recoverable only from backup. Erasure is destruction of the row and the backup lineage, and is the only operation on the platform that is genuinely irreversible.

Authoring

Four things are canonical components: import mappings, matching rules, duplicate rules, and retention policies. Everything else — a particular import run, an export schedule’s output, a duplicate set, a restore job — is record data or job state.

An import mapping is a component, not a per-user convenience, because an integration’s mapping is part of the application and has to promote from a sandbox to production with everything else. An ad-hoc mapping a user builds in the import surface is saved as a personal draft (record data) until they choose to promote it, at which point it becomes a component with a key.

{
  "key": "import_invoice_from_erp",
  "label": "Invoice import — ERP extract",
  "type": "import_mapping",
  "body": {
    "object": "invoice",
    "operation": "upsert",
    "matchOn": "erp_invoice_id",
    "format": "csv",
    "columns": [
      { "source": "ERP_INVOICE_NO",  "field": "erp_invoice_id" },
      { "source": "CUST_NO",       "field": "account",  "lookupBy": "erp_account_id" },
      { "source": "TOTAL_USD",     "field": "amount",   "transform": "decimal(2)" },
      { "source": "STATUS_CODE",   "field": "status",   "valueMap": "vm_erp_status" },
      { "source": "DUE_ON",        "field": "due_date", "format": "MM/dd/yyyy", "timezone": "America/Chicago" }
    ],
    "onUnmappedColumn": "fail",
    "chunkSize": 5000,
    "partialSuccess": true
  }
}

matchOn names a field marked externalId, which is backed by a unique index — so upsert is a real INSERT … ON CONFLICT, not a read-then-write race. lookupBy resolves a relationship by the parent’s external id rather than requiring the platform’s primary key in the file, which is what makes a two-object load possible from one extract. onUnmappedColumn: "fail" is the default: a column in the file that the mapping does not name is an error, because silently ignoring it is how a renamed source column becomes six months of missing data.

A matching rule declares how two records are judged to be the same thing. It is an ordered list of field comparisons in the one typed language, each with a comparator, and it produces a match key the platform can index:

{
  "key": "match_account_by_name_and_place",
  "label": "Account — name and location",
  "type": "matching_rule",
  "body": {
    "object": "account",
    "criteria": [
      { "field": "name",         "comparator": "fuzzy_company", "weight": 0.6 },
      { "field": "billing_city", "comparator": "exact",         "weight": 0.2 },
      { "field": "website",      "comparator": "domain",        "weight": 0.2 }
    ],
    "threshold": 0.85,
    "nullPolicy": "skip_criterion"
  }
}

Comparators are a fixed, versioned set — exact, case_insensitive, normalized, domain, phone, fuzzy_company, fuzzy_person, trigram — because a customer-authored similarity function cannot be indexed and cannot be reasoned about at deploy time. Each comparator declares the expression index it needs, and deploying a matching rule creates that index. nullPolicy is explicit rather than implied: a blank city either drops that criterion and renormalizes the weights, or fails the match outright.

A duplicate rule binds one or more matching rules to an action and a scope:

{
  "key": "dup_account_block_exact_domain",
  "label": "Account — block on identical web domain",
  "type": "duplicate_rule",
  "body": {
    "object": "account",
    "matchingRules": ["match_account_by_domain"],
    "action": "block",
    "appliesTo": ["ui", "api", "import", "automation", "restore"],
    "condition": "record.record_type != \"Prospect_Shell\"",
    "allowOverrideBy": "data.duplicate_override"
  }
}

appliesTo defaults to every entry path. It is a list rather than an implicit “everywhere” only so that a rule can be deliberately narrowed — for example, letting a restore re-create a record that today collides with something created after the backup was taken. Narrowing is a written decision in the component, visible in a diff, not an undocumented exemption in the platform.

A retention policy is one component per object covering all three stages, because splitting archive, recycle-bin retention, and purge across three surfaces is how rows end up governed by two policies that disagree:

{
  "key": "retention_invoice",
  "label": "Invoice retention",
  "type": "retention_policy",
  "body": {
    "object": "invoice",
    "recycleBinDays": 30,
    "archive": { "after": "record.closed_date < now() - years(2)", "schedule": "weekly" },
    "purge":   { "after": "record.archived_at < now() - years(7)", "requiresApproval": true },
    "legalHold": "record.on_litigation_hold == true"
  }
}

legalHold is evaluated before every archive, purge, and erasure step and vetoes all three. A row under hold can still be soft-deleted by a user — that is reversible — but nothing destroys it.

Semantics & evaluation

Import

An import is four phases, and the third is the one that matters.

Intake. CSV, TSV, JSONL, or Parquet, optionally gzipped, uploaded or streamed. The parser reports encoding, delimiter, row count, and column names before any mapping is applied, so a mis-delimited file fails at second one instead of row 40,000. Files are held in tenant-scoped storage with the same access rules as any other record data.

Mapping. Either a saved import_mapping component or an interactive mapping built in the import surface. Column headers are matched to field labels and API names automatically; anything ambiguous is presented, never guessed. A mapping is type-checked against the object’s current metadata generation at selection time — a mapping that names a retired field is rejected before the file is read, with a deploy-class pointer to the field that moved.

Dry run. The load-bearing mechanism on this page. A dry run is a real transaction that is rolled back, not a simulation: every row is shaped, adjusted, and validated through the actual save order phases SHAPE → ADJUST → VALIDATE, against the real schema, real formulas, real validation rules, real duplicate rules, and the real permissions of the running user. It stops before WRITE. What it returns is exactly what a live run would do:

• row counts by outcome — would insert, would update, would fail;
• the failure list, each row carrying the full error envelope it would have raised;
• the set of records that would be changed, field by field, so a mapping that silently blanks a column is visible before it does;
• the automation that would fire, by rule key, and the resource budget the run would consume.

A dry run costs a full pass over the file. That is the price of the guarantee, and it is why dry run is the default for interactive imports and opt-out (not opt-in) for scripted ones.

Execution. The file is chunked; each chunk is one transaction. Within a chunk, partialSuccess: true (the default) means a failing row is isolated and its siblings commit — implemented by savepoint-per-row within the chunk transaction, so isolation is real and not a second pass. partialSuccess: false makes the entire job atomic, not merely the chunk, because “all or nothing” that silently means “all or nothing per 5,000 rows” is the worst of both.

Two artifacts come out of every run and both are permanent job records:

• successful.csv — the input rows, plus record_id and operation (insert | update | unchanged).
• failed.csv — the input rows, unmodified, plus the error envelope as columns: error_code, error_class, error_message, error_field, correlation_id. Fix the values in place and resubmit the same file against the same mapping. There is no “which of my columns was sf__Error talking about” step, because error_field names it and correlation_id links straight to the execution trace of the transaction that rejected it.

An import job is cancellable mid-run. Cancellation stops chunk dispatch; chunks already in flight complete. The job result states exactly how many rows were applied before the stop, which is the number an operator needs to decide whether to roll forward or reverse.

Export

An export is a read, and every rule that governs reads governs it.

Field-level security applies, and the mechanism is deliberate. A column the running user cannot read is omitted from the output header entirely and listed in the export manifest under withheld. It is not emitted as a column of nulls, because a column of nulls is indistinguishable from a column of genuinely empty values and produces a silently wrong analysis downstream. Record access applies identically: an export returns the rows the user’s record-access predicate admits, no more. There is no export mode that runs elevated — platform.root is the only identity that sees everything, and its exports are audit entries like everything else it does.

Formats: CSV, JSONL, and Parquet. Parquet is first-class rather than an afterthought because the common destination for a scheduled export is an analytics warehouse, and shipping typed columnar data to it instead of stringly-typed CSV removes an entire class of re-parsing bugs.

Ad-hoc export runs from any list view, report, or query, inherits that view’s filters and column set, and produces a signed download. Scheduled export is a component-free job definition (it is operational configuration, not application metadata) with an arbitrary cron expression — not a fixed weekly-or-monthly choice — writing to a signed URL or directly to a tenant-owned object store bucket. Output retention is a per-tenant setting with a floor of 7 days and no hard ceiling; nothing is deleted out from under an operator on a 48-hour timer.

Every export carries a manifest: the query, the metadata generation it ran against, the row count, the file checksums, the user, the timestamp, and the withheld column list. An export without a manifest is an unreproducible artifact, and unreproducible artifacts are how two people arrive at a meeting with different numbers.

Mass operations

Mass update, mass delete, and mass transfer are the same job engine over a selected row set — a list view selection, a filter, or a query. Each is a normal write. Each runs SHAPE → ADJUST → VALIDATE → WRITE → EFFECTS → COMMIT per row. Each supports dry run. Each produces successful.csv and failed.csv. None of them has a checkbox that turns automation off.

That last point is the deliberate answer to the question every admin asks, and it deserves to be stated without hedging: automation runs. Formula fields recompute, roll-ups maintain, validation rules gate, record-triggered automation fires, and the data history stream records every field change with the job’s correlation id. If a bulk load genuinely must not fire automation — a migration, a backfill — the operator engages the named, audited bypass that already exists for that purpose, and the bypass key is stamped on the job record and on every history entry the job wrote. Suppression is possible, it is explicit, and it is permanently attributable. What is not possible is suppressing automation by accident, or by using a different tool.

Mass transfer of ownership is a mass update of owner_id. There is no separate transfer subsystem, because ownership is a field and transfer is a write (roles & record sharing). Two consequences follow and both are improvements over treating transfer as special: automation and validation see the transfer like any other change, and the new owner’s access is correct at the next query because record access is a computed predicate rather than a table of share rows that must be regenerated.

Deactivating a user requires a disposition for their records. A user cannot be deactivated with an unanswered ownership question. The deactivation flow computes, per object, how many records the user owns and how many open work items are assigned to them, then requires one of three choices for each object: transfer to a named user or queue, transfer to the user’s role parent (offered as the default), or explicitly leave in place. “Leave in place” is a legitimate choice — records owned by an inactive user remain fully visible and reportable — but it must be chosen, because the alternative is discovering a year later that a departed employee owns 4,000 rows that no rule reaches. The transfer, if chosen, is an ordinary mass transfer job with a dry run, and it is queued rather than blocking the deactivation.

Duplicate management

Duplicate detection runs at two moments, and they answer different questions.

At save time, in VALIDATE. Every duplicate rule whose appliesTo includes the current entry path evaluates against the fully shaped and adjusted record — which matters, because the value being matched may have been normalized by a formula or a before-write rule, and matching the raw input would miss it. Three outcomes:

• allow — record the match on the record’s potential-duplicates panel; save proceeds.
• warn — surface candidate records to the user with a save-anyway affordance; a save-anyway is logged with the candidate ids.
• block — the save fails with a conflict-class error whose details carry the matching rule key and the ids of the candidates the user is permitted to see. Candidates the user cannot see are counted, not named, because naming them leaks their existence.

Because matching rules compile to indexed expressions, save-time matching is an index probe rather than a scan. That is what makes it affordable to run on the import path, which is precisely the path the incumbent exempts.

In a batch sweep. Save-time rules cannot find duplicates that already exist, and they cannot find pairs that became duplicates when a third record was edited. A scheduled sweep runs the same matching rules over the whole object — or a filtered subset — and materializes duplicate sets: groups of candidate records with their match scores and the rule that grouped them. Sets land in a review queue where they are merged, dismissed, or marked not-a-duplicate; a dismissal is remembered so the next sweep does not re-raise the same pair. The sweep is a job like every other job: dry-runnable, cancellable, and reported.

Merge. Merging is a single atomic transaction over an unlimited number of losers (the practical bound is the transaction’s resource budget, not a count of three), and it is defined by what happens to everything pointing at the loser:

• Field values. The operator picks the surviving value per field; the default is the survivor’s own value, and the merge surface shows every field where the candidates disagree rather than only a curated subset.
• Children. Every child row is reparented to the survivor by updating its foreign key. Master-detail children move with their master and inherit the survivor’s ownership and sharing automatically, because their access defers to the parent’s.
• References. Every lookup anywhere in the tenant that points at a loser is repointed at the survivor — findable exhaustively because relationships are real foreign keys and the kernel holds the dependency graph. This is not a curated list of “related records that get reassigned”; it is every referencing row, including from objects nobody remembered were related.
• History. The loser’s data history is grafted onto the survivor, each grafted entry stamped with the loser’s id as provenance. The survivor’s timeline therefore shows the whole story and shows which parts of it came from where.
• The loser itself. It becomes a redirecting tombstone: its id continues to resolve, and any request for it — a bookmarked URL, an integration holding the old id, a report snapshot — returns the survivor with a merged_into header rather than a 404. The tombstone persists for the object’s retention window.
• Reversibility. A merge is undoable for the retention window. Undo restores the loser, reverses every reparent and repoint using the merge’s recorded before-state, and un-grafts the history. After the window, the tombstone is purged and the merge becomes permanent.

Merge runs the save order on the survivor, so a merge that produces an invalid record fails and changes nothing.

Backup and restore

Backup is continuous, always on, and not a product. The tenant’s Postgres cluster streams write-ahead log to object storage alongside periodic base backups, which yields point-in-time recovery to the transaction, not to the most recent nightly snapshot. The retention window is a plan setting with a floor of 30 days. Metadata is backed up by the same mechanism — a restore that returns rows to a schema shape they cannot fit in is not a restore — and the metadata repository remains the authority for what the schema should be.

Restore has three granularities and they are genuinely different operations:

Granularity	Mechanism	Typical cause
Record	Read the row’s state at time T from the recovery target, write it back into the live schema preserving its primary key	A bad edit, a bad import row, a purge after the recycle-bin window closed
Object	Restore the object’s rows as of T into a staging schema, diff against live, apply the reviewed diff	A mass update that went wrong across an object
Tenant	Recover the whole schema to T into a new schema and cut the tenant over to it	Corruption, ransomware, a catastrophic deploy

Restores preserve primary keys. A restored record comes back with the id it had. This is not a detail — it is the difference between a restore that heals every reference pointing at the record and a restore that produces a new row plus thousands of dangling references nobody finds for a month. The platform can do this because ids are database keys under transactional control, not opaque values minted by an external service.

The object-level path routes through a staging schema and a reviewed diff for a specific reason: a restore is almost never “put everything back.” Between T and now, legitimate work happened, and blindly overwriting it trades one data-loss incident for another. The diff surface shows, per row, what the backup holds, what production holds, and which one wins — and the reviewed decision set is itself a job with a dry run.

The recycle bin is not backup. It is the soft-delete tier: deleted_at is set, the row is still in the table, and RLS filters it out of ordinary queries. Undelete clears the flag, which is why it is instant and why it restores relationships perfectly. Recycle-bin retention is recycleBinDays in the object’s retention policy, defaulting to 30, and it is a time policy only — the bin has no storage-based eviction, so a heavy delete day never silently shortens the recovery window for everything else in it. When the window closes, the row is purged and only backup can bring it back.

Archiving

Archiving moves cold rows out of the hot path without removing them from the object. An archived row keeps its id, its relationships, its history, and its place in the object’s schema; it moves to a cold partition on cheaper storage and is excluded from the hot-path indexes. Queries reach it, list views and reports reach it, foreign keys still resolve to it. What changes is latency and cost, not addressability.

Mechanically, each archivable object is range-partitioned on its archive predicate’s driving column, and archiving is a partition detach-and-move rather than a row-by-row copy — which is why archiving ten million rows is a metadata operation on the partition rather than ten million writes. Queries that filter on the partition key never touch cold partitions at all; queries that do not must span both tiers and are slower, visibly, in the query plan.

Archive is stage one of the object’s retention policy. Stage two is retention — the archived row simply sits there for as long as the policy says. Stage three is purge, which is a real delete subject to everything below. Because all three live in one component, “archive after 2 years, purge after 7” is a single reviewable statement, and there is no way to configure an archive rule that outlives its own purge rule.

Deletion, retention, and erasure

Soft delete is the default for every user-initiated delete. The row is tombstoned, not removed; it appears in the Recycle Bin; it is restorable for recycleBinDays.

Hard delete (purge) removes the row. It is available three ways: a retention policy’s purge stage, an explicit emptying of the recycle bin, and a job-level hardDelete: true for operators holding the data.purge system permission. That permission is separate from delete permission on purpose — the ability to remove a row is not the ability to remove its recoverability.

Cascade behavior follows the field model and nothing else. A masterdetail child is ON DELETE CASCADE and is not optional; a lookup declares SET NULL (default), RESTRICT, or CASCADE. These are real foreign-key actions, so the closure is computed by the database and cannot diverge from what the schema says.

A cascade is authorized against the whole closure. Before a delete executes, the kernel walks the cascade closure and checks the deleting user’s delete permission and record access on every row in it. If any row fails, the whole delete fails with a permission-class error naming the object and the count of blocking rows — not their ids, which would leak the existence of rows the user cannot see. This is more expensive than not checking, and it is the correct trade: a delete that removes rows the user was never allowed to touch is a security hole regardless of how the user reached them.

Soft-deleting a parent soft-deletes its cascade closure; undeleting the parent undeletes exactly the rows that went down with it, identified by the delete’s correlation id rather than by re-deriving the closure from a schema that may have changed since.

Erasure — a data-subject deletion request — is a distinct operation with a distinct guarantee. “Delete this person” is not “purge these rows,” because purged rows still exist in backups, and a record’s history is a second copy of the values that must also go. An erasure request:

1. Resolves the subject to a row set across every object, following declared subject-linkage paths, and presents the set for approval before anything happens.
2. Purges the live rows and their cascade closures, bypassing the recycle bin.
3. Redacts data history in place — every history entry for an erased field has its old and new values replaced by a salted hash of the original, while the entry’s skeleton (timestamp, actor, object, field key, correlation id) survives intact. The audit chain stays complete and verifiable — an auditor can still see that a value changed, when, and by whom, and can still verify an asserted value against the hash — but the value itself is unrecoverable.
4. Writes the erased ids to a tombstone list that is applied to every future restore, so a restore from a backup taken before the erasure cannot resurrect the subject. This is the mechanism that makes erasure real rather than aspirational, and it is why erasure is the only irreversible operation on the platform.
5. Records the request itself — subject, scope, approver, timestamp, row counts — in the metadata audit stream, because a compliance regime that requires deletion also requires proof of deletion.

The approval is the platform’s own, not a second approval mechanism built for privacy. An erasure request is a record on the erasure_request object, and that object ships a lifecycle whose entry gate into Approved is an ordinary approval gate. Everything that behaves well about approvals therefore behaves well here: routing, delegation to a named alternate, recall by the submitter, notification, the pinned-version rule for in-flight requests, and a single approvals surface where a privacy request sits beside a discount request instead of in its own inbox nobody checks.

Who decides, precisely:

• Submitting requires the data.erase system permission. Deciding requires data.approve_erasure. They are separate grants so that the person who can compose an erasure is not, by that fact, the person who can authorize one.
• The shipped gate routes to the erasure_approvers queue, whose membership is administered like any other queue. A tenant may add steps — legal and a data-protection owner in sequence is the common shape — and deploy validation refuses an erasure lifecycle with no approval step, which is the one edit that would turn an irreversible operation into a one-click one.
• The submitter is never an eligible approver on their own request, even holding both permissions, and even as platform.root — root bypasses the three access planes, and a recorded decision by a named approver is not an access check. If the queue’s only eligible member is the submitter, the request goes to the stalled state, visibly, rather than routing to nobody.
• The shipped gate declares no escalation, so it waits indefinitely. Escalation moves a decision and never makes one, which means no elapsed time can approve an erasure; an org may add an escalation step to move an aging request to a named approver, and there is no setting anywhere that converts a timer into a signature.
• The request is locked while pending, so the approved artifact is the resolved row set rather than a description of one. Execution re-resolves the subject immediately before purging: rows that entered the set after the decision are excluded and reported, and widening the scope needs a fresh approval. An approval given for 412 rows is never spent on 900.

Legal hold vetoes erasure. A subject request that collides with a hold surfaces the conflict for a human decision rather than resolving it silently in either direction.

Large data volumes

Most of the platform behaves identically at a thousand rows and a hundred million. The things that do not are listed here, along with what the admin can do about each — which is the part that matters, because a scaling behavior an admin cannot influence is just an outage waiting for a support ticket.

Indexing is declarative and self-service. Primary keys, foreign keys, unique fields, and externalId fields are indexed by construction. Beyond that, an index is a metadata component the admin writes and deploys:

{
  "key": "idx_invoice_status_due",
  "label": "Invoice — status + due date",
  "type": "index",
  "body": {
    "object": "invoice",
    "columns": ["status", "due_date"],
    "include": ["amount", "account", "owner_id"],
    "where": "record.is_deleted == false",
    "method": "btree"
  }
}

include is a covering index — the columns are carried in the index leaf so a query reading only those columns never touches the heap. This is the same optimization the incumbent implements as skinny tables, with three differences: the admin creates it, it is version-controlled and promotes through environments like any other component, and it updates when the query changes instead of requiring a support case. Index creation runs CREATE INDEX CONCURRENTLY through the expand → migrate → contract path, so it does not lock the table.

Selectivity is measured, not assumed. Every list view, report, and saved query carries a plan check: the kernel runs EXPLAIN at the object’s current row count and reports the estimated cost and whether the driving filter is index-backed. A view whose predicate is not selective is flagged at author time with the plan that proves it and the index that would fix it — the same discipline the record-access plane applies to sharing predicates. There is no undocumented percentage threshold to reverse-engineer; the planner’s own figure is shown.

Counts are exact to 10,000, and honestly floored above it. A list view, a report, and any paged query report an exact total while the filtered set holds 10,000 rows or fewer. Past that they report 10,000+ and stop counting.

The reason is that the number on screen is not a fact about the table. Postgres can answer “how many rows does this object have” from statistics in constant time, and nobody is asking that. The question a list view asks is “how many rows match this filter, for this user” — and that set is defined by the view’s predicate ANDed with the object’s row-security predicate, which differs per user and changes whenever a grant, a group membership, or a role parent changes. There is no counter to maintain for a set with those properties, and a maintained one would be wrong for everyone but the person it was built for. Answering exactly therefore costs a pass over every qualifying row — the one query on the page whose cost has nothing to do with the fifty rows being rendered, and the one that turns a fast list view on a hundred-million-row object into a slow one.

Bounding it is mechanically simple: the count runs the page query’s own plan with its row limit set to 10,001 and counts what comes back, so the work is capped at ten thousand and one qualifying rows however large the object is. Three rules keep the bound from becoming a lie:

• A floor is presented as a floor. The list header reads 10,000+ items, pagination offers movement rather than a page count it cannot compute, and the API returns {"totalSize": 10000, "isLowerBound": true} — never a bare integer a caller could mistake for a total, and never a statistical approximation dressed as a count.
• An exact count is available on request. Count all runs the unbounded count: inline when the plan check places it inside the interactive budget, and otherwise as a job with a monitor entry, under the same automatic flip every other operation uses. The result is shown with the time it was taken, because an exact count of a moving set is a fact about a moment.
• Aggregates are never floored. A report’s sums, averages, and grouped subtotals are computed over the whole filtered set or the report does not render. A subtotal that quietly summed the first ten thousand rows is wrong in a way no reader can detect, so a report whose aggregate pass exceeds the interactive budget becomes a job rather than a fast approximation.

Selecting “all” for a mass action selects the query, not the rows that were counted, so a floored readout never bounds what an operation touches.

Partitioning follows archiving. Objects with a declared archive predicate are range-partitioned on its driving column. Partition pruning is what makes a query over the last quarter of a hundred-million-row object fast, and it is the same mechanism that makes archiving a partition move.

Operations stop being interactive at a declared threshold, automatically. One threshold governs every row-scoped operation — update, delete, transfer, merge, export, and Count all. At or below it the operation runs inline; above it the same operation becomes a job with a monitor entry. The flip is automatic, so an operator never has to know which tool survives which row count: it is one tool changing execution mode. The import surface, the running-job monitor, and the confirmation step are package-delivered UI; the one write path and the one job engine underneath them stay in the kernel.

The default is 200 rows. Every row is a full save-order pass, so the flip has to sit where the slowest qualifying row set still returns inside the interactive request budget, and 200 passes of the ordinary save path fits it with headroom on a typical object. It is also the page size of a list view, which makes the boundary legible instead of arbitrary: selecting everything visible on a page never flips, and selecting everything the filter matches usually does.

The value is a per-environment setting on the Data management page in Setup, beside the retention policies, held as configuration data rather than as a component — it is operational tuning, not application shape, and a sandbox is often set deliberately low so the job path is the one being exercised. It is also shown, read-only with the rest of the environment’s budgets, on the environment record. The floor is 1 — a legitimate choice for a tenant that wants every mass change to carry a dry run, a monitor entry, and a result file — and the ceiling is the plan’s interactive request budget, so raising it can never produce an inline operation that outlives the request it is running in.

The user is told at the moment the scope is known, before anything runs. The confirmation step states the row count, that the work will run as a job, and that the page may be closed; confirming returns a run id immediately, docks the running-job panel, and finishes with a notice linking to the job. When the scope is a query rather than a counted selection, the count may be a floor — 10,000+ — rather than an exact number, and the message says the operation runs as a job regardless of where the true count lands. Nothing else about the operation changes: the same dry run, the same save order, the same partial-success semantics, and the same successful.csv / failed.csv. That sameness is the point of making the flip automatic — if crossing the threshold changed the semantics as well as the execution mode, an operator would have to care where it sits.

Deletes at scale are partition operations where possible. Purging a partition’s worth of expired rows detaches and drops the partition rather than issuing tens of millions of deletes, which is the difference between an hour and a second, and which is only available because rows live in real tables with real partitions.

Limits, and the reasons behind them

Concern	CAOS	Salesforce	Why their limit exists
Import size, UI path	Same engine as every other path; bounded by the job’s resource budget	Data Import Wizard: 50,000 records, and a limited object list	A browser-hosted wizard is a separate implementation from the API loader
Import size, bulk path	Same engine; chunked and resumable	Data Loader: 150 million records; Bulk API 2.0 150,000,000 records / 24 h, 15,000 batches / 24 h, 150 MB per job	Shared multitenant ingest capacity
Mass delete, UI path	Same engine; dry-runnable; no count ceiling	250 records per operation, fixed object list (reported)	A synchronous Setup page with no job infrastructure behind it
Mass inline edit	Flips to a job past the interactive threshold	200 records per list view page (reported)	The list view holds one page in the browser
Automation on bulk paths	Always runs; suppression only via a named, audited bypass	Import Wizard: workflows/processes off by default, a checkbox to enable, Apex triggers fire regardless; Data Loader: workflows fire and cannot be disabled	Two tools, two implementations, two defaults
Duplicate rules	No cap; bounded by the object’s index budget	5 active duplicate rules per object; 3 matching rules per duplicate rule; 5 active matching rules per object	Each active matching rule is a maintained match index
Duplicate coverage	Every entry path unless narrowed in the component	Skips lead conversion, undelete, manual merge, Quick Create, self-registration, and sync-sourced data	Each entry path is a separate code path that had to opt in
Merge width	Unlimited losers; bounded by the transaction budget	3 records per merge (reported)	A synchronous UI operation with no job behind it
Recycle bin	Time policy only, per object, default 30 days	15 days (30 in Classic with extended retention); capped at 25× org storage, oldest purged first when full (reported)	The bin consumes the same storage the customer pays for
Purge from the bin, API	Job-scoped	emptyRecycleBin(): 200 records per call; records may remain visible to queryAll() for ~24 h	A synchronous SOAP call
Backup	Continuous WAL-based PITR, included	Weekly/monthly CSV export, or the paid Salesforce Backup managed package; the free Data Export Service caps ZIPs at 512 MB and deletes files after 48 hours (reported)	Backup is a separate product with its own SKU
Archive	Partition move; rows stay in the object and stay queryable	Big Objects — 100 big objects per org, no transactions, async Apex required from triggers and flows, no encryption; or the separately-licensed Salesforce Archive (reported)	Archived data lives in a different storage engine with a different query surface
Covering index	An index component the admin deploys	Skinny tables: max 200 columns, created only by Salesforce Support, must be re-requested when the query changes	The customer has no DDL
Query selectivity	EXPLAIN-based plan check shown at author time	Optimizer thresholds — 10% of the first million records and 5% thereafter, capped at ~333,000 records, “subject to change”	An opaque optimizer over a shared schema

Caution

The 250-record mass delete cap, the 200-record list-view inline edit cap, the 3-record merge limit, the Recycle Bin’s 15/30 days and 25× storage figures, the Data Export Service’s 512 MB ZIP cap and 48-hour file lifetime, and the licensing status of Salesforce Archive are widely reported by corroborating secondary sources rather than quoted from a page that renders to a scriptless fetch. Confirm them against a live org before citing them to a customer. The Bulk API allocations, Data Import Wizard record count, duplicate/matching rule counts, emptyRecycleBin() cap, skinny-table column cap, optimizer thresholds, Big Object considerations, and the import-tool automation defaults are quoted from Salesforce’s own documentation and are cited below.

How Salesforce does it

Salesforce splits data management across a large number of separately-evolved tools, and the seams between them are where the operational pain lives.

Import is two products. The Data Import Wizard runs in the browser, imports up to 50,000 records at a time, and supports a fixed list of standard objects plus custom objects (Trailhead — Mastering Data Import). The Data Loader is a desktop application that handles up to 150 million records, uses SOAP API by default and Bulk API optionally, and covers every object (same unit). Underneath the loader, Bulk API 2.0 allocates 150,000,000 records and 15,000 batches per rolling 24 hours, 10,000 records per batch, 150 MB per ingest job, and keeps results retrievable for 7 days (Bulk API Limits and Allocations).

The two import tools disagree about automation, which is the single most-asked question in Salesforce data loading and has a genuinely confusing answer. Salesforce’s own knowledge article states it plainly: the Data Import Wizard does not fire workflow rules and processes by default and offers a checkbox to enable them, the Data Loader does fire them and “it is currently not possible to disable this behavior,” and Apex triggers fire in both cases regardless of the checkbox (How to Control Workflow and Process Execution). Two tools, two defaults, one shared exception.

Failed rows come back from Bulk API 2.0 as a failedResults CSV carrying sf__Error (code and message) and sf__Id, alongside the original row data; successfulResults carries sf__Created and sf__Id, with the caveat that “the order of records in the response is not guaranteed to match the ordering of records in the original job data” (Get Job Failed Record Results, Get Job Successful Record Results).

Mass operations live on Setup pages with synchronous ceilings. Mass Delete Records covers a fixed object list at 250 records per operation, a cap that has an open IdeaExchange request against it (Mass Delete Data (250 record limit)). List-view inline editing handles 200 records per page. Anything larger is a Data Loader job.

Duplicate management is capped at five active duplicate rules per object, three matching rules per duplicate rule, and five active matching rules per object, with actions of alert, block, or report (Things to Know About Duplicate Rules). The same page enumerates a long exemption list where duplicate rules simply do not run: Quick Create, community self-registration, lead conversion (unless Apex lead convert is on), undelete, data arriving via Lightning Sync or Einstein Activity Capture, and manual merges — the exact paths through which duplicates most reliably enter an org.

Deletion is layered and the layers surprise people. The Recycle Bin holds deleted records for 15 days (extendable to 30 in Classic only) and is capped at 25 times the org’s storage in MB, purging oldest-first and without warning when full (Gearset — restoring from the Recycle Bin, secondary but consistently corroborated). Programmatic purge via emptyRecycleBin() handles 200 records per call, errors if the id list contains cascade-deleted records, and leaves records visible to queryAll() for roughly 24 hours afterward (emptyRecycleBin()). Cascade delete on custom lookup relationships is available only by asking Support to enable it, is unavailable for lookups to standard objects, and — the part that matters — bypasses sharing and security, deleting children the acting user cannot see (Enable cascade delete on custom lookup relationships).

Backup has the most instructive history on this page. Salesforce’s Data Recovery Service was the last-resort restore path: it cost upwards of $10,000 and took six to eight weeks. Salesforce retired it on 31 July 2020, stating it “has not met their high standards of customer success and trust” (Nerd @ Work). Customer reaction was severe enough that the service was reinstated in March 2021, on the reasoning that its value “simply lay in its very existence” as an emergency option — and even then it required the request to be filed within 15 days of the loss and could not recover anything deleted more than three months earlier (Spanning). It was retired again once Salesforce Backup — a paid managed package with automated daily backups and click-through restore (Restore Records with Salesforce Backup) — became generally available. A platform that had no first-party backup, then had one that cost five figures and took two months, then had none, then had one again, then made it a SKU, is the strongest available argument for making continuous backup a kernel property rather than a product.

The free alternative has been getting narrower, not wider. The Data Export Service runs weekly or monthly depending on edition, caps each ZIP at 512 MB, and deletes export files after 48 hours. As of Winter ’26, manual downloads from Setup are rate-limited to one file at a time with roughly a 60-second wait, returning HTTP 429 on violation — which, for an org whose weekly export spans dozens of 512 MB files, turns a backup download into a multi-hour supervised task (Odaseva — Winter ’26 weekly export rate limiting).

Archiving is either Big Objects or the newer Salesforce Archive add-on license. Big Objects are capped at 100 per org, don’t support transactions, require asynchronous Apex when accessed from triggers, processes, or flows to avoid mixed-DML errors, don’t support encryption (archiving encrypted data stores it “as clear text on the big object”), and expect partial batch failures on write with the documented guidance being to implement retry rather than identify which rows failed (Big Object Considerations).

At scale, the two levers are both gated behind Salesforce Support. Skinny tables hold a maximum of 200 columns, exclude soft-deleted rows, cannot contain fields from other objects, “can’t be created, accessed, or modified” by the customer, and must be re-requested from Support whenever the underlying report or query changes (Skinny Tables). Custom indexes are likewise added by Support on request, and the query optimizer’s selectivity threshold is documented as 10% of the first million records and 5% thereafter, capped at roughly 333,000 records — with the explicit note that the threshold is “subject to change” (Improve Performance of SOQL Queries using a Custom Index).

Where CAOS is genuinely better:

• One write path for every ingest surface. Because import, mass update, merge, and restore all enter the same save order, the automation question has one answer instead of a per-tool matrix, and suppression is a named, logged bypass rather than a tool choice.
• Dry run is a rolled-back transaction, not a preview. It exercises the real schema, real formulas, real validation, real duplicate rules, and the real user’s permissions, so what it reports is what will happen.
• The failed-row file is the input file plus the error envelope. error_code, error_class, error_field, and correlation_id as columns make the file directly re-submittable and link each rejection to its execution trace.
• Covering indexes are customer-deployable metadata. An index component with include columns delivers the skinny-table optimization through version control and the normal deploy path, with no support case in the loop.
• Selectivity is shown, not guessed. The planner’s actual EXPLAIN cost is surfaced at author time instead of an opaque percentage threshold that is documented as subject to change.
• Restores preserve primary keys. Ids are database keys under transactional control, so a restored record heals every reference pointing at it rather than becoming a new row beside a field of dangling ones.
• Archived rows stay in their object. A partition move preserves the id, the relationships, the history, and the query surface — as against a separate storage engine with its own object type, its own query dialect, and no transactions.
• Duplicate rules have no exemption list. Every entry path is covered by default, including import, undelete, and merge, and narrowing is a written line in a component that shows up in a diff.
• Cascade deletes are authorized across the whole closure. A delete that would remove rows the acting user cannot see fails, rather than silently succeeding.
• Erasure is enforced against backups. A tombstone list applied at restore time is what separates “the rows are gone” from “the subject cannot come back.”

Parity: an upsert keyed on an external id; saved, reusable field mappings; partial-success semantics with per-row results; scheduled export; a soft-delete recycle bin with undelete; matching rules with alert/block/report actions and a batch duplicate sweep; merge with a surviving master and reparented children; point-in-time restore; and archive tiering for cold data. These are the right capabilities and CAOS ports them rather than reinventing them. What differs is that they are one engine with one set of rules instead of nine tools with nine.

Costs and risks:

• Dry run doubles the work. A full validating pass before a full executing pass is the price of the guarantee, and on a hundred-million-row load it is real time and real money. It is the default because silent corruption is more expensive, but the trade is not free and the opt-out exists for scripted paths that have already been proven.
• Running full automation on bulk paths is expensive. Salesforce’s tool-level automation switches exist because customers need the escape hatch. Making the bypass named and audited keeps it honest but does not make automation cheap — a ten-million-row load that fires per-row automation will be slow, and operators will reach for the bypass routinely.
• Per-row savepoints cost. Real row isolation inside a chunk transaction is implemented with savepoints, and savepoint churn has measurable overhead in Postgres. Chunk size becomes a tuning parameter, not a constant, and the wrong value degrades throughput sharply.
• The cascade-closure authorization check is a real query. Walking and permission-checking a deep closure before a delete costs a traversal that Salesforce’s cascade skips entirely. On a wide master-detail tree it is the dominant cost of the delete.
• Erasure is unforgiving. A tombstone list applied to restores means an over-broad erasure scope cannot be walked back. The approval step and the pre-execution row-set preview are load-bearing controls, not conveniences, and an operator who rubber-stamps them has no second chance.
• History redaction weakens one audit property. Hashing old values preserves verifiability of an asserted value but destroys the ability to reconstruct history from the log alone. That is the intended trade for erasure, and it must be documented to auditors rather than discovered by them.
• Customer-deployable indexes can be misused. Handing admins DDL removes a support bottleneck and introduces a new failure mode: over-indexed objects with slow writes and bloated storage. Index components need the same per-object cost budget the record-access predicates carry, and enforcing it is required work, not a later addition.
• Continuous PITR for every tenant is a standing operational cost. WAL archiving, base backups, retention tiering, and periodic restore rehearsal for every tenant is infrastructure Salesforce monetizes as a SKU. Including it means owning it, and an unrehearsed backup is not a backup.
• Partitioning constrains the data model. Range-partitioning on an archive predicate means that predicate’s driving column is effectively immutable and must appear in queries that want pruning. Choosing it badly is expensive to undo.

Metadata & deploy representation

Component	type	Body
Import mapping	import_mapping	object, operation, matchOn, format, columns[], onUnmappedColumn, chunkSize, partialSuccess
Matching rule	matching_rule	object, criteria[] (field, comparator, weight), threshold, nullPolicy
Duplicate rule	duplicate_rule	object, matchingRules[], action, appliesTo[], condition, allowOverrideBy
Retention policy	retention_policy	object, recycleBinDays, archive, purge, legalHold
Index	index	object, columns[], include[], where, method

Jobs are not components. An import run, an export file, a duplicate set, a merge, a restore, and an erasure request are all record data with their own objects, their own retention, and their own audit trail — because a job is a thing that happened, and things that happen do not deploy between environments.

Deploying a matching_rule or an index creates a physical index and therefore routes through the expand → migrate → contract path with CREATE INDEX CONCURRENTLY; the deploy reports index build progress and the generation flip waits for the build to complete. Deploying a retention_policy whose archive predicate names a new partition key is a physical schema change and takes that same path — the deploy validator rejects an archive predicate that cannot be satisfied by a partition boundary rather than accepting one that would silently degrade into a full scan.

Salesforce Metadata API analogs, for migration mapping: MatchingRule (matchingRuleItems with matchingMethod and blankValueBehavior), DuplicateRule (duplicateRuleMatchRules, actionOnInsert, actionOnUpdate, alertText, securityOption), and CleanDataService for the third-party enrichment side. Salesforce has no metadata type for an import mapping, a retention policy, or an index — those are, respectively, browser session state, a per-object Setup toggle, and a support case.

Sources

• Bulk API and Bulk API 2.0 Limits and Allocations — Salesforce Developers — 150,000,000 records and 15,000 batches per rolling 24 h, 10,000 records per batch, 150 MB per ingest job, 7-day result retention.
• Get Job Failed Record Results — Bulk API 2.0 — the sf__Error and sf__Id columns.
• Get Job Successful Record Results — Bulk API 2.0 — sf__Created, sf__Id, and the unordered-results caveat.
• Mastering Data Import — Trailhead — Data Import Wizard 50,000 records; Data Loader 150 million; SOAP by default with optional Bulk API.
• How to Control Workflow and Process Execution When Using the Data Import Wizard vs. Data Loader — Salesforce Help — the wizard’s off-by-default checkbox, the loader’s non-disableable behavior, and Apex triggers firing regardless.
• Things to Know About Duplicate Rules — Salesforce Help — 5 active duplicate rules per object, 3 matching rules per duplicate rule, 5 active matching rules per object, alert/block/report, and the exemption list.
• emptyRecycleBin() — SOAP API Developer Guide — 200 records per call, the cascade-delete error, and ~24 h queryAll() visibility.
• Enable cascade delete on custom lookup relationships — Salesforce Help — Support-enabled, unavailable for lookups to standard objects, and bypasses sharing and security.
• Big Object Considerations — Salesforce Developers — 100 big objects per org, no transactions, async Apex required from triggers and flows, no encryption, expected partial batch failures.
• Skinny Tables — Best Practices for Deployments with Large Data Volumes — 200-column max, no cross-object fields, created only by Salesforce Support, must be re-requested when queries change.
• Improve Performance of SOQL Queries using a Custom Index — Salesforce Help — the 10% / 5% / ~333,000 selectivity thresholds, “subject to change,” and the Support-request process.
• Salesforce is Retiring Their Data Recovery Service — Nerd @ Work — 31 July 2020 retirement, $10,000+ cost, six-to-eight-week turnaround, and Salesforce’s stated reason. (Community source; the retirement is not documented on help.salesforce.com.)
• Salesforce Data Recovery Service — Spanning — the March 2021 reinstatement, the 15-day filing window, the three-month recoverability ceiling, and the launch of Backup and Restore. (Secondary.)
• Salesforce Winter ’26 Weekly Export Changes — Odaseva — one download at a time, ~60-second wait, HTTP 429 on violation, and the multi-hour impact on large exports. (Secondary.)
• How to restore deleted Salesforce records from the Recycle Bin — Gearset — 15 days in Lightning, 30 in Classic with extended retention, the 25× storage cap, and oldest-first purging when full. (Secondary, consistently corroborated.)
• Mass Delete Data (250 record limit) — IdeaExchange — the open request against the mass-delete cap.
• Restore Records with Salesforce Backup — Salesforce Help — the paid managed package’s restore surface.