Salesforce Data Architect Interview Questions

I treat it as four separate questions, and any one of them can force the answer:

1. Sharing: Does the child need to inherit the parent's sharing and ownership? Only master-detail does that. If the child must have its own owner or its own sharing rules, master-detail is out immediately.
2. Roll-ups: Do I need roll-up summary fields on the parent? Those are native to master-detail only. With a lookup I would need Flow, Apex, or a tool like Rollup Helper, which is extra code to maintain.
3. Lifecycle: Should deleting the parent cascade-delete the children? Master-detail does; a lookup can be configured to clear the field or block the delete instead.
4. Write volume: This is the one people forget. Every DML on a master-detail child locks the parent. On a high-throughput transactional child that produces row-lock contention, so I will often deliberately choose a lookup plus an asynchronous roll-up, accepting eventual consistency to protect write throughput.
5. Practical note: Master-detail also caps at three levels of nesting and the child inherits the parent's record-level security, which can surprise people during a security review. I document that trade-off explicitly rather than discovering it in UAT.

Data skew is an uneven distribution of records that causes locking and sharing-recalculation problems. There are three types worth distinguishing, because the remedies differ:

1. Account (parent-child) data skew: More than roughly 10,000 child records under one Account. Salesforce locks the parent during child DML and recalculates implicit sharing across all its children. Symptoms are UNABLE_TO_LOCK_ROW during loads and very slow sharing recalculation. Remedy: distribute the children across a set of bucket parent records so no single parent exceeds the threshold.
2. Ownership skew: More than roughly 10,000 records of one object owned by a single user — typically an integration user or a placeholder 'unassigned' user. Because ownership drives role-hierarchy sharing, any change to that user's role forces a very expensive recalculation. Remedy: place the owner outside the role hierarchy, or at the top of it with no role, and spread ownership where the business allows.
3. Lookup skew: The same concentration problem on a lookup field rather than a master-detail. It still causes brief parent locks during child DML. Remedy: more parent records, batch by parent, or defer populating the lookup until after the bulk insert.
4. Prevention: Architecturally I try to avoid the placeholder-record pattern entirely. When the business genuinely needs an 'unknown customer' concept, I model it as a set of buckets from day one rather than a single record that quietly grows past the threshold two years later.

They solve different problems and are not interchangeable:

1. Custom index: Added by Salesforce Support on a field the optimiser would otherwise scan. It only helps if the filter is selective — an index on a two-value checkbox is wasted effort. This is the first thing I try because it is cheap and reversible.
2. Skinny table: A Salesforce-maintained copy of a narrow, frequently used subset of an object's fields (up to 100), which removes the join between the base table and the custom-field table. Good for a report or list view that is run constantly over the same handful of columns on a very large object. Caveat: skinny tables do not automatically carry to a sandbox or a new org and must be re-requested, so I record them in the deployment runbook.
3. Big Object: A different storage tier entirely, for hundreds of millions of immutable rows queried by a predefined index. It does not consume standard data storage. I use it for archives and event history, and I query it with Async SOQL, writing aggregates into a small custom object that users can report on normally.
4. Sequencing: In practice I work in that order: make the query selective, then index, then skinny table, and only reach for Big Objects when the real answer is that the data should not be sitting in a transactional object at all.

I work from evidence rather than guessing, roughly in this order:

1. Confirm the pattern: Pull the failed batches and look for what the failing records have in common. Almost always they share a parent record, an owner, or a common lookup target.
2. Check for skew: Query the child object grouped by the parent Id and by OwnerId to see whether any single value is far above the rest. That usually identifies the contention point in minutes.
3. Look at concurrency: Is the job running Bulk API in parallel mode? Parallel batches touching the same parent will contend. Serial mode is slower but often the fastest path to a job that actually completes.
4. Sort the input: Ordering the file by parent Id so all children of a parent land in the same batch removes most cross-batch contention without giving up parallelism entirely.
5. Look for automation: A trigger or flow that updates a shared parent or a shared summary record on every child insert will serialise the whole load. That is a design fix, not a tuning fix.
6. Then tune: Only after the above would I adjust batch size, defer sharing calculation for the load window, or split the job. Reducing batch size first is a common instinct and often just makes a slow job slower without fixing the cause.

I frame it as: what does the data need to do once it is here?

1. Virtualise when: The dataset is large, changes often, and is read in record context — a customer's full invoice history opened from the Account page. There is no storage cost, no synchronisation lag, and no second copy to reconcile.
2. Virtualisation costs: Callout latency on every access, dependency on the source system's availability, and real limitations: external objects cannot back roll-up summaries, have search and reporting constraints, and behave differently in Apex and declarative automation.
3. Replicate when: The data must feed roll-ups, be filtered and aggregated in reports, drive declarative automation, or remain available when the source is down. Replication buys full native behaviour at the cost of storage, integration complexity, and staleness.
4. The hybrid I usually land on: Virtualise the long tail of detail records, and replicate a small set of aggregates — lifetime value, last order date, open order count — onto the parent record. Users get native reporting on the numbers they actually filter by, and the detail stays where it lives.
5. Decisive question: If someone in the room says 'and we need to report on that across all customers', that is usually the end of the virtualisation option.

Storage is the trigger, but the design has to start with policy, not with tooling:

1. Establish retention first: How long must each object be retained, who owns that decision, and what triggers a legal hold? Until that is agreed, any archive design is guesswork and probably not defensible to an auditor.
2. Profile the data: Find where the volume actually is. It is usually one or two objects plus attachments and field history — not evenly spread. This tells you where effort pays off.
3. Choose the tier per dataset: Frequently read recent data stays in the transactional object. Rarely read but retained data goes to a Big Object or an external archive surfaced through Salesforce Connect. Data past retention and free of legal hold is deleted.
4. Preserve access: Whatever the destination, define how a steward retrieves an archived record during an audit and how long that takes. An archive nobody can query is a liability.
5. Execute safely: Reconcile counts between source and archive before deleting anything, use Bulk API hard delete so the Recycle Bin does not hold the volume, and run it in off-peak windows in controlled batches.
6. Make it recurring: A one-off purge buys eighteen months. A scheduled job with a documented policy and monitoring is the actual deliverable.

The optimiser decides, per query, whether to use an index or scan the table, and it decides on estimated selectivity:

1. Selectivity threshold: It uses an index only when the filter is expected to return below a threshold share of rows — commonly cited as roughly 30% of the first million records and about 15% beyond that, lower for standard indexes. Those figures move, so I treat them as an order of magnitude, not a contract.
2. Why an index can be ignored: A checkbox or an evenly distributed four-value picklist over 30 million rows returns far too many matches. The optimiser is behaving correctly by scanning; the field is simply not selective.
3. Index-defeating constructs: Leading wildcards in LIKE, negative operators such as != and NOT IN, comparisons on null, and formula fields that are not deterministic will all prevent index use even on an indexed field.
4. What actually fixes it: Add a genuinely selective filter alongside the weak one — an indexed date range, an owner, an External Id. Or change the shape of the problem: pre-aggregate into a summary object, use a skinny table, or move the volume to a Big Object.
5. How I diagnose it: The Query Plan tool in the Developer Console shows the plan the optimiser chose and its cost, which turns this from an argument into a measurement.

I run it as a repeatable pipeline that gets rehearsed, not as a single event:

1. Profile and scope: Understand volumes per object, relationship depth, data quality, and what genuinely needs to migrate. A significant share of legacy data usually should not come across at all, and agreeing that early removes the most risk.
2. Design the keys: Every object gets an External Id holding the legacy identifier, marked Unique. This makes loads idempotent via upsert and lets children reference parents by legacy key, which removes the entire Id-mapping pass.
3. Cleanse in staging: Deduplicate, standardise formats, and validate picklist and reference values against the target org before anything is loaded. Fixing data in production afterwards costs several times more.
4. Sequence the load: Users and reference data, then parents, then children, then junctions, then attachments and files. Load relationships by External Id rather than resolved Ids.
5. Tune for volume: Bulk API 2.0, non-essential automation suspended for the window, sharing calculation deferred, batches sorted by parent Id, parallel where there is no parent contention and serial where there is.
6. Handle audit fields: If CreatedDate and ownership must be preserved, enable Set Audit Fields upon Record Creation first — those fields can only be set at insert, so missing it means deleting and reloading.
7. Rehearse and reconcile: Full dress rehearsal in a sandbox with production-like volume, then reconcile with row counts, control totals on key amounts, referential integrity checks, and business sign-off on a sample. Then re-enable automation and recalculate sharing once.
8. Plan the cutover: Delta load for records that changed during the rehearsal-to-cutover window, plus a documented rollback position.

An External Id is a field flagged as holding a key from another system. It is indexed, and when combined with Unique it is enforced at the database level. It matters for three reasons:

1. Idempotent writes: It enables upsert keyed on the external value, so an integration can insert-or-update without a prior query and can safely replay a failed batch without creating duplicates.
2. Relationship resolution: A child record can reference its parent by the parent's External Id in the relationship column (Parent__r.Legacy_Id__c), so parent-child links resolve at insert time with no Id-mapping step.
3. Decoupling from Salesforce Ids: Upstream systems should never depend on Salesforce-generated Ids, because those change on re-migration and differ between sandbox and production. The External Id is the stable contract.
4. Design guidance: Mark it Unique as well as External Id, use one External Id per source system rather than overloading a single field, and never expose the Salesforce 18-character Id as the integration key in an interface contract.

This is a master data management problem, and the technology is the last part of the answer:

1. Agree the domain and scope: Which entity are we mastering, which attributes are in scope, and which system is authoritative for each attribute. Authority is attribute-level: the ERP may own billing address while the marketing platform owns consent and email preference.
2. Choose an implementation style: Registry (keys only, resolved on the fly), consolidation (aggregate for reporting, no write-back), centralised (the hub is the system of record), or coexistence (hub masters, sources keep authoring, bidirectional sync). The choice follows how much change the existing applications can absorb.
3. Define matching: Deterministic matching on strong identifiers such as a tax id or a customer number, plus probabilistic or fuzzy matching on name and normalised address for the rest. Set thresholds for auto-merge versus route-to-steward.
4. Define survivorship: Per attribute: source trust ranking, then recency, then completeness, then validation status such as address standardisation. Write these down as rules with weightings, because they will be disputed.
5. Preserve lineage: Store the source system identifier, source name, and last-synchronised timestamp alongside the consolidated values so any attribute can be traced back and reprocessed if the rules change.
6. Staff the stewardship: Someone must work the exception queue for records that fall between the auto-merge and reject thresholds. An MDM programme with no steward capacity degrades within months.

I would not simply refuse — I would quantify the cost and offer a design that meets the underlying need:

1. Establish the real requirement: Usually it is 'we need to report on trends' rather than 'we need every row in a custom object'. Those have very different solutions.
2. Quantify: Project the row count and storage over three to five years, and show the effect on query performance, report timeouts, sharing recalculation, and sandbox refresh times. Concrete numbers change the conversation.
3. Offer the tiered alternative: Recent transactional data in a custom object where it is fully reportable; older data in a Big Object queried with Async SOQL, with aggregates written into a small summary object; or the whole history in a warehouse with Salesforce holding the summary.
4. Show the reporting story: Demonstrate that the aggregates and dashboards the business actually looks at still work, because that is the fear driving the request.
5. Document the decision: If the customer still insists after seeing the projection, I record the accepted risk and the trigger point at which we revisit it. Making the trade-off explicit is a legitimate outcome; letting it happen silently is not.

GDPR touches the model itself, not just the security settings on top of it:

1. Know where personal data lives: Build a field-level inventory using the Data Classification attributes — Data Owner, Field Usage, Data Sensitivity Level, Compliance Categorization — and keep it current by re-running a metadata extract each release.
2. Minimise: Challenge every personal field on the model. If there is no processing purpose, do not collect it. This is the cheapest control available and the one most often skipped.
3. Record lawful basis: Use the standard consent model — Individual, ContactPointConsent, and the related objects — so consent and preferences are captured as data rather than inferred from a checkbox on Contact.
4. Design for erasure: The right to erasure has to be executable. That means knowing every object holding personal data, including custom objects, attachments, field history, archives, and sandboxes, and having a documented hard-delete or irreversible-anonymisation process. Anonymisation is often the practical answer where transactional history must be retained.
5. Retention and audit: Field Audit Trail for defined retention on tracked fields, an agreed retention policy per object, and a legal hold process that overrides it.
6. Protect: Shield Platform Encryption for genuinely sensitive attributes — noting that encrypting a field affects matching rules, filtering, and sorting, so it is a design decision, not a checkbox.

The honest first answer is that cross-org reporting is a symptom; the strategic question is whether the orgs consolidate. Assuming consolidation is not immediate:

1. Short term: Salesforce Connect with the cross-org adapter exposes Org B records as external objects in Org A. Users get contextual visibility with no data copy and no storage cost, and it can be stood up quickly.
2. Its limits: External objects have reporting and aggregation constraints, so this covers 'look up a record' far better than 'analyse the combined pipeline'.
3. For real analytics: Extract both orgs into a data warehouse or a shared analytics layer using Bulk API 2.0 or Change Data Capture, and conform the models there. That is where the cross-org trend reporting genuinely belongs.
4. The hard part is semantics: Two orgs will have different picklist values, different record types, different stage definitions, and different notions of what an Account is. Mapping those is most of the work, and it is a business exercise, not a technical one.
5. Position it as a bridge: I would present this as an interim architecture with an explicit review point, so the organisation does not accidentally standardise on a permanent two-org model by default.

The dominant fact is that enabling Person Accounts is irreversible, so it deserves a proper decision record:

1. What it changes: A Person Account is one record surfaced through both the Account and Contact objects. That affects record types, page layouts, sharing, duplicate rules, roll-ups, and every integration that assumes Accounts and Contacts are separate.
2. When it fits: A genuine B2C model where the customer is an individual with no employing organisation, and where you want the full Account feature set — territories, sharing, roll-ups — on that individual.
3. When I would avoid it: Mixed B2B/B2C models where the added complexity is not repaid, or where a substantial integration estate already assumes the standard model. A Contact-centric model with Contacts to Multiple Accounts often meets the requirement with far less disruption.
4. How I would advise: Prototype it in a sandbox, run the existing integration test suite against it, review the effect on storage and record counts, and get explicit sign-off from integration owners. Then decide — and if the answer is yes, do it early, because retrofitting it later is significantly worse.

Something changed, so I look for the change rather than starting with tuning:

1. Volume growth: Check the object's record count against when the report was fast. Crossing a selectivity threshold is the single most common cause — the optimiser silently stops using an index.
2. Filter changes: Has anyone edited the report? Removing a date filter or adding a NOT IN clause will defeat an index instantly.
3. Query Plan: Run the equivalent query through the Query Plan tool to see whether the optimiser is using an index or scanning, and what it estimates the cost to be.
4. Skew: Check whether the filtered set now concentrates on a small number of parents or owners, which changes sharing evaluation cost for a private model.
5. Sharing complexity: Growth in the role hierarchy, sharing rules, or territory model increases the cost of evaluating record access for every candidate row, independently of the data volume.
6. Then fix: Restore selectivity with an indexed date range, add a custom index if the field is genuinely selective, request a skinny table for a stable hot report, or move the aggregation to a summary object refreshed on a schedule.

Both are audit timestamps, but they are not updated identically:

1. LastModifiedDate: Reflects changes attributable to a user or a process acting as a user. It is what business users expect to see and what appears on the record.
2. SystemModstamp: Reflects those changes plus system-driven updates that Salesforce applies internally without touching LastModifiedDate.
3. Why it matters: A delta integration that bookmarks on LastModifiedDate can silently miss records that were changed by the system. SystemModstamp is the safer high-water mark, and it is indexed.
4. The other half of delta design: Neither timestamp tells you about deletions. Use queryAll and getDeleted, or Change Data Capture, so that deletes propagate. Also overlap the window slightly and design the target to be idempotent, because clock boundaries and long-running transactions will otherwise drop records at the edges.

A concrete example: a utilities client capturing smart-meter readings at fifteen-minute intervals for two million meters. That is billions of rows, immutable, and analysed in bulk.

1. Why Big Object fits: It handles that volume, it does not consume standard data storage, and the access pattern — retrieve by meter and time range — maps cleanly onto a predefined composite index.
2. What you give up: No roll-up summary fields, no validation rules, no standard sharing model, limited trigger support, and querying is constrained to the index you defined at creation — which you cannot casually change later.
3. How I compensate: Async SOQL aggregates the readings into a small custom object holding daily and monthly summaries. Users report on that object with ordinary reports and dashboards and never touch the Big Object directly.
4. The design risk: The index is the whole design. If you get the composite key wrong, the object is effectively unqueryable for the access patterns you need, and the remedy is creating a new object and reloading. I spend disproportionate time on that decision.

Data quality decays unless something actively maintains it, so I design for the ongoing state rather than a one-off cleanse:

1. Prevent at entry: Required fields where genuinely required, validation rules, picklists instead of free text for anything that will be reported on, and Duplicate Management with matching rules tuned and duplicate rules set to block or alert on the entry points that matter — including the API, which is often left unchecked.
2. Standardise: Address normalisation, consistent formats for phone and identifiers, and a single source for reference data rather than each integration inventing its own values.
3. Measure: Define quality metrics per domain — completeness on key fields, duplicate rate, staleness — and report on them on a dashboard that someone actually owns. Unmeasured quality is unmanaged quality.
4. Assign ownership: Named data owners per domain and stewards with time allocated to work exception queues. This is the control most often missing.
5. Govern change: Change control on the data model so fields are not added ad hoc, plus periodic field-usage review to retire fields nobody populates.
6. Remediate in cycles: Scheduled deduplication and enrichment runs rather than an annual heroic cleanup project.

First I would test whether it needs to be in Salesforce at all, because the cheapest control is not holding the data:

1. Challenge the requirement: Is the full identifier needed, or would a tokenised reference, a last-four fragment, or a boolean 'verified' flag satisfy the process? This resolves the requirement more often than people expect.
2. If it must be stored: Shield Platform Encryption on the field, with the understanding that encryption affects filtering, sorting, and matching rules — so I check what breaks before enabling it, not after.
3. Layer access control: Field-level security restricted to the minimum profiles and permission sets, sharing designed so the record itself is not broadly visible, and no exposure of the field in reports or list views used by wider populations.
4. Classify and audit: Data Classification metadata marking sensitivity and compliance category, Field Audit Trail on the field, and event monitoring where the licence allows.
5. Control the copies: Sandbox seeding must mask or exclude the field, integrations must not log it, and the retention and erasure process must cover archives and backups. Sensitive data leaking through a sandbox or an integration log is a far more common failure than the production field being compromised.

A standard delete is a soft delete: the record moves to the Recycle Bin and is recoverable for a limited retention window before being physically removed.

1. Why soft delete is the default: It protects against operator error, and for ordinary business deletions that is exactly what you want.
2. Why it matters at volume: Soft-deleted records still occupy storage and still sit in the index until they are purged. Deleting thirty million records softly does not immediately relieve the storage or performance pressure that motivated the deletion.
3. Hard delete: The Bulk API hard delete option bypasses the Recycle Bin entirely. It is the right tool for large planned purges, and it requires the Bulk API Hard Delete permission.
4. The governance precondition: Because it is unrecoverable, I will not run a hard delete without confirming the records are captured in the archive, that no legal hold applies, and that the selection criteria have been reviewed and dry-run against a count. For compliance-driven erasure, hard delete is usually mandatory rather than optional, since a recoverable delete does not satisfy a right-to-erasure request.

At that scale I stop asking how to tune the object and start asking whether it should exist in that form:

1. Challenge the shape: Is this one object because the data is genuinely one entity, or because nobody separated hot transactional data from cold history? Usually it is the latter, and splitting it is the highest-value change available.
2. Tier the storage: Recent, actively used records in the custom object; historical records in a Big Object or an external store; aggregates in a small summary object that carries the reporting load.
3. Design access patterns first: At this volume you design the queries before the schema. Every access path needs a selective, indexed filter — typically a date range plus an owner or an External Id. Ad hoc querying is not a supported use case.
4. Control skew rigorously: No parent or owner concentration anywhere near the thresholds, enforced by design and monitored, not left to chance.
5. Plan operations: Deferred sharing calculation for loads, skinny tables for the hot reports, PK chunking for extracts, and a documented archive job that runs continuously rather than as an annual event.
6. Set expectations: I would be explicit with stakeholders that some conveniences — unfiltered list views, arbitrary cross-object reporting, casual full exports — are not available at this scale. Agreeing that up front prevents a lot of disappointment later.

All three can hold configuration, but they differ on deployability, caching, and storage:

1. Custom metadata types: The default choice. Records are metadata, so they deploy with change sets and packages, are cached and accessible in Apex without SOQL limits, and consume no data storage. Use for anything that should move with a release: rules, thresholds, mappings, feature toggles.
2. Hierarchy custom settings: Retain one capability metadata types lack — per-profile and per-user value resolution at runtime. Use when the requirement is genuinely a per-user or per-profile override.
3. List custom settings: Largely superseded by custom metadata types. I would not choose them for new work.
4. Custom objects: For configuration that business users maintain themselves as ordinary data, that needs record-level sharing, or that is too large or too volatile to deploy by release — a reference dataset synchronised from an external master, for example.
5. The deciding question: Does this value change by deployment or by business operation? Deployment means metadata; operation means data.

Refresh duration scales with what you are copying, so I look at scope before anything else:

1. Match sandbox type to purpose: Full sandboxes copy all data and are slow by definition. Most development and testing does not need production data volume — Partial Copy with a sampled template, or Developer Pro seeded deliberately, is usually the correct answer and refreshes far faster.
2. Use sandbox templates: For Partial Copy and Full sandboxes, templates control which objects are copied and how many records, which is the main lever on duration.
3. Reduce the source volume: If a Full sandbox is genuinely required, the archiving work that reduces production volume also reduces refresh time. The two problems share a solution.
4. Automate post-refresh: Sandbox post-copy scripts handle masking sensitive fields, deactivating integrations and outbound email, and reseeding test users. Failures here are often mistaken for refresh failures.
5. Check for genuine errors: Persistent failures rather than slowness usually trace to a specific object or a post-copy script and warrant a Salesforce support case with the failure detail rather than repeated retries.

The goal is an artefact that stays true, which rules out anything maintained by hand:

1. Use the platform's own metadata: Field Description and Help Text for business definitions, and the Data Classification attributes for ownership, usage, sensitivity, and compliance category. Populating these makes the org self-documenting.
2. Extract programmatically: Retrieve object and field metadata through the Metadata or Tooling API on a schedule and publish it as the dictionary. Because it is generated, it cannot drift from reality.
3. Detect drift: Diff successive extracts to surface new fields with no description, no owner, or no classification, and route those to the responsible team. This turns documentation from a project into a control.
4. Add what the platform cannot hold: Source-system lineage, transformation logic, and business rules typically live in the integration or warehouse layer, so the glossary needs to reference those rather than pretend Salesforce is the only source.
5. Enforce at the gate: Make description and classification mandatory in the change-control checklist for new fields. Retrofitting three hundred undocumented fields is a project; documenting each one at creation is a minute.

I try to surface the constraints that are expensive to discover late:

1. Volume and growth: Current record counts per object and projected growth over three to five years. This determines whether LDV design is optional or mandatory.
2. Systems of record: Which system is authoritative for each entity and each attribute, and which direction data flows. Ambiguity here becomes an MDM problem later.
3. Access and sharing: Who must see what, and who must not. Sharing requirements shape the model as much as the entities do, and a private model with a deep hierarchy has real performance consequences.
4. Licensing: Which user populations touch which objects, and what licences they hold. Discovering that a population cannot access Opportunity is much cheaper before the model is built.
5. Regulatory obligations: Retention periods, data residency, GDPR or sector-specific requirements, and legal hold processes.
6. Integration estate: What integrates today, at what frequency and volume, and what assumptions those integrations make about the model.
7. Reporting and analytics: What must be reported in Salesforce versus in a warehouse. This is the single question that most often decides virtualise versus replicate.
8. Governance capacity: Who owns data quality, and do they have time allocated. A design that assumes stewardship that does not exist will fail regardless of its technical merit.