Salesforce Certified Data Architecture and Management Designer (SF-Data-Arch) — Questions 151–222

To keep Salesforce performant, architects must offload heavy processing. Integrating with an middleware, using asynchronous patterns, and leveraging external objects (Salesforce Connect) are all strategies to avoid hitting governor limits. These methods decouple the heavy lifting from the Salesforce transaction, allowing the platform to remain responsive while the external system handles the data processing, aggregation, or long-term storage, keeping the overall architecture lean and within prescribed operational constraints.

The scenario defines a clear attribute-level system of record: the ERP owns billing address, Salesforce merely consumes it. That maps to a unidirectional publish-and-subscribe integration combined with UI-level protection so Salesforce users cannot create authoritative edits. Bi-directional approaches and federation both contradict either the direction requirement or the expectation that the value is persisted and usable natively inside Salesforce.

The many-to-many requirement with attributes unique to each pairing is the textbook case for a Junction object built from two Master-Detail relationships. Placing Rate__c and Effective_Date__c on the junction keeps pairing-specific data normalized and reportable, and the master-detail links provide integrity and roll-up options.

Record locking in Salesforce often occurs when multiple processes or threads attempt to update the same parent record or records with common children. To resolve this, the integration process should be serialized or partitioned to group related records together. By ensuring that transactions affecting the same parent record are processed sequentially in the same thread, the architecture prevents the 'Unable to lock row' errors, enhancing overall stability and throughput.

Big Objects are designed for massive data storage but have unique requirements compared to standard objects. When inserting data into a Big Object, the CSV file must include all fields that are defined in the Big Object's custom index. This index is required for querying and uniquely identifying records within the Big Object store.

Managing Large Data Volumes (LDV) requires moving infrequently accessed data out of the primary transactional tables. Moving data to Big Objects keeps it within the Salesforce ecosystem at a lower cost, while off-platform archiving to a data warehouse provides maximum performance and long-term storage flexibility.

The primary factor is the volume of data being moved. Bulk API 2.0 is designed specifically for large datasets (hundreds of thousands to millions of records) and operates asynchronously, reducing the likelihood of request timeouts. The SOAP API, while powerful for real-time transactional operations, is synchronous and poorly suited for large-scale data migrations due to its overhead and batch limitations.

Enforcing metadata standards like requiring a description and data owner on custom objects requires checking metadata before deployment. A CI/CD pipeline with Salesforce CLI or scripts can automate these checks, preventing non-compliant metadata from being deployed. Validation rules, permission sets, and workflow rules operate on data or user access, not metadata, so they cannot enforce this policy.

Salesforce Data Loader is the standard tool for managing complex data migrations. Its ability to map CSV files to objects and maintain relationships via External IDs or record IDs makes it the primary choice for data architects. Using a mature, supported tool minimizes errors during migration and ensures that data integrity is preserved across relational structures, which is critical for successful implementation projects and long-term data reliability.

A Master-Detail relationship is the platform feature that natively enforces a required parent, prevents deletion of referenced parents, inherits the parent's sharing model, and supports roll-up summary fields. Converting the existing Lookup satisfies all compliance and reporting needs with declarative configuration rather than custom automation.

The configuration allows the field to serve as a key for 'Upsert' operations. By marking the field as an External ID and unique, Salesforce can automatically match records from an external system to existing Salesforce records. This prevents duplicates and allows for automated, incremental data updates without needing to know the internal Salesforce record ID, which is critical for robust, scalable integration architectures.

The recommended approach is to create a dedicated 'External ID' custom field on relevant objects to store the source system's unique identifier. This field should be marked as an External ID and indexed. This allows for easy upsert operations, prevents duplicates during ongoing synchronization, and provides a clear audit trail for compliance teams to trace records back to their original source systems during data lifecycle management.

Data residency requires the records to physically reside and be processed in the approved region, and only anonymized aggregates to cross borders for reporting. Hosting EU data in an EU-region instance with controlled cross-region flows achieves that. Encryption key custody, sharing rules, and authentication each improve security but do not govern the location of storage or processing.

Loading massive datasets into objects with complex sharing logic triggers significant overhead as Salesforce recalculates sharing access for every record. Deferring sharing calculations allows the data load to proceed without immediate recalculation, which is then performed in a single, optimized background process once the load is complete, saving hours of processing time.

Master-detail relationships enforce two key behaviors: the parent field is required, and deleting the parent cascades to delete the children. The scenario needs both a mandatory Account on every invoice and deletion of invoices when an Account is removed. Those requirements are satisfied only by the master-detail model, while lookup relationships leave the parent optional and do not cascade delete by default.

Minimizing locking contention requires optimizing the database interaction patterns within Salesforce. Serializing record processing and organizing batches by OwnerID or AccountID prevents multiple threads from attempting to lock the same parent records simultaneously. This approach ensures that the database index updates are serialized per specific record groups, drastically reducing the incidence of 'UNABLE_TO_LOCK_ROW' errors during large volume data loads in a multi-tenant environment.

The requirement is one authoritative reference covering field definitions, ownership, allowed values, and system of record. A Data Dictionary is precisely that artifact and is the standard foundation for a Data Governance program. ERDs, audit retention policies, and migration mappings each serve narrower technical purposes and cannot substitute for a maintained catalog of business and technical metadata.

Data Mask is specifically designed to obfuscate sensitive data in sandboxes without affecting Production. It applies masking policies during sandbox creation or refresh, ensuring that PII is protected in non-production environments while maintaining data integrity in Production. This supports governance by enabling safe testing and development with realistic but anonymized data.

A Data Steward is the role specifically designed to own data domains, ensure data quality, define standards, and resolve issues. They bridge business and technical teams, providing accountability for data within their domain. This aligns with the governance team's need for clear ownership and accountability at the domain level.

The two correct practices are defining an MDM strategy and implementing validation and duplicate rules. MDM establishes a single source of truth, while validation and duplicate rules enforce data quality at the point of entry. Together, they ensure data consistency and accuracy across integrated systems, which is essential for effective data governance.

The architect should use an ETL transformation to map legacy picklist values to valid Salesforce values before loading. Mapping 'Open' to 'New' and 'Resolved' to 'Closed' ensures the data conforms to the standard Case Status picklist, preventing load failures and maintaining data integrity. This approach is scalable for 20 million records.

The external ID field must contain a unique, non-null value for every record to support upsert and maintain data integrity. Generating a synthetic value from the legacy primary key and a timestamp ensures uniqueness and populates the field for the 0.2% of records with missing last_modified. This avoids data loss and meets the field constraint without modifying the source system or relaxing the Salesforce field definition.

Standard Field History Tracking records changes but does not store the duration between states effectively for analytics. Creating a secondary 'Audit' object that captures snapshots of status changes via a record-triggered flow is the best approach. This allows the architect to calculate intervals between timestamps using reporting tools or custom formulas, providing the necessary visibility into business process bottlenecks that native history tables cannot easily compute.

Consolidating heterogeneous source systems calls for a neutral surrogate master identifier plus cross-reference attributes that retain each source system's original key. This avoids collisions between country numbering schemes, keeps political decisions out of the technical design, and preserves the traceability that tax reporting and reconciliation demand. Reusing one country's scheme or discarding legacy numbers both break auditability.

Establishing a centralized hub or golden record strategy is critical for large enterprises. By defining a source of truth for specific data attributes and orchestrating updates through an integration layer, organizations prevent data drift. This approach ensures that downstream systems consume validated, reliable data, which is essential for maintaining compliance, improving reporting accuracy, and providing a unified customer experience across disparate business units and global regions.

A scheduled batch Apex job is the most appropriate solution because it can automate the export of old records to an external archive and then delete them from Salesforce. Batch Apex is designed for processing large data volumes and can be scheduled to run regularly. This approach ensures compliance with the retention policy and offloads data to reduce storage costs.

Pre-aggregating data into a summary object is the most reliable way to keep a dashboard fast when the source object has millions of records. By storing totals per Account and Fiscal Year, the dashboard queries a small, indexed dataset instead of scanning Invoice__c. Asynchronous maintenance via batch Apex or scheduled flow avoids impacting user transactions and keeps the summary current enough for reporting.

Salesforce Connect allows for the integration of external data sources via OData or custom adapters, exposing external data as External Objects. This allows users to view and interact with the data in real-time within Salesforce without the storage costs or synchronization issues associated with physically importing the data.

Master-detail relationships enforce sharing inheritance from parent to child and enable roll-up summary fields on the parent for aggregation. These two features are critical for designing efficient reporting and security. The other options describe limitations or incorrect behaviors, such as independent sharing rules or ease of conversion, which do not apply to master-detail relationships.

A junction object with two Master-Detail relationships is the standard Salesforce pattern for many-to-many relationships, and it enables roll-up summary fields on both parents. Adding a semester field and a unique composite key satisfies the duplicate-prevention rule. Reciprocal lookups, asymmetric relationships, and multi-select picklists cannot simultaneously support per-assignment attributes, aggregate reporting, and uniqueness enforcement.

A many-to-many relationship is required to associate multiple Employees with multiple Training Sessions. This is implemented via a junction object. This pattern is fundamental in relational database design, as it resolves circular dependencies and allows for granular tracking of attendance, grades, or completion status per individual pairing, which cannot be achieved using simple lookups or single-parent structures on the employee or session records.

When an object has large data volumes, non-indexed fields cause full table scans, which are highly inefficient and slow. By marking the 'External_ID__c' field as an External ID or unique field, Salesforce automatically creates a database index. This allows the query engine to perform a targeted lookup rather than scanning all 20 million records, providing an immediate and significant performance improvement for queries and searches involving that specific field.

UNABLE_TO_LOCK_ROW errors during parallel Bulk API loads typically occur when concurrent batches attempt to update child records that reference the same parent records. Salesforce locks parent records to enforce referential integrity, and simultaneous access causes contention. Reducing parallelism or serializing the load for affected objects resolves the issue.

Bulk API parallel processing often triggers lock contention when multiple batches attempt to update different child records that roll up to the same parent record simultaneously. By organizing the CSV file such that records with the same parent are grouped together and then processing the load in serial mode, the system ensures that only one batch accesses a parent at a time.

Survivorship rules are the logic engine that determines which source provides the 'best' version of a record when multiple systems provide conflicting data. This is essential for MDM because without these rules, the system cannot intelligently reconcile discrepancies, leading to a fragmented customer view. Proper survivorship logic ensures the golden record is reliable, accurate, and reflects the most trusted information available across the entire enterprise ecosystem.

Deleting child records in a lookup relationship acquires exclusive locks on the parent records to maintain referential integrity. When concurrent updates target those same parent Accounts, lock contention triggers UNABLE_TO_LOCK_ROW errors. This is a common issue with high-volume deletes on objects with lookups to frequently updated parents.

To enforce data quality at the point of entry, the architect should implement validation rules to reject invalid data and duplicate rules to prevent duplicates. These are proactive controls that operate when records are created or updated. They are standard Salesforce features that can be configured declaratively and apply across all entry points, including UI, API, and integrations, ensuring consistent data governance.

Deterministic, attribute-level golden records require two foundations: a consistent identity across sources and explicit trust ranking with survivorship rules. A global identifier and cross-reference table group all source records for the same entity, while source ranking with field-level survivorship and documented tie-breakers decides which value wins. Together they deliver the required trust and determinism; duplicate management, manual review, and external read-only access do not.

Big Objects are optimized for massive scale but do not support standard SOQL features like aggregate functions or complex filtering over large sets. Async SOQL was the historical solution, but it has been retired. The modern recommendation is to use CRM Analytics or external reporting tools to process Big Object data.

The architect should first inspect the query's filter criteria to determine if it is selective. If the filter is not indexed or is too broad, the system will perform a full table scan. Verifying the selectivity of the query and ensuring an index exists is the fundamental first step in optimizing performance for large data volumes within the Salesforce platform.

When queries filter on one field and sort by another, a composite index covering both fields allows the database to use the index for both operations, avoiding expensive sorting and full scans. This is the most effective optimization for the given query pattern.

Custom indexes are necessary when standard indexing is insufficient for complex filtering. By creating a custom index on high-cardinality fields used in 'WHERE' clauses, the optimizer can skip full table scans, drastically reducing query time. This is critical in large data volumes where even a small performance improvement in query speed can make the difference between a successful report and a timeout failure.

Data skew occurs when an exceptionally high volume of child records are associated with a single parent record. During DML operations, Salesforce locks the parent record to maintain referential integrity, blocking concurrent threads attempting to update related children and causing transaction timeouts.

Data cleansing identifies and fixes errors such as duplicates, invalid formats, and incomplete fields before they are propagated into a new system. If dirty data is ingested, it corrupts the new MDM environment, rendering reports unreliable and forcing developers to build complex, expensive workarounds. Cleaning first ensures the initial state of the master system is pristine, providing a reliable foundation for all future business operations and data-driven decisions.

A Junction Object is the standard Salesforce solution for many-to-many relationships. By creating a custom object between Projects and Consultants, the architect can include extra fields to store metadata about the assignment. This design ensures referential integrity, enables robust reporting across both parent objects, and allows for security modeling via Master-Detail or Lookup relationships, which is essential for complex consulting resource management.

Data stewards bridge the gap between technical IT teams and business users. They are responsible for the daily management, quality, and definition of data assets. By ensuring metadata is documented and data quality issues are remediated, they maintain the integrity of the data assets, which is critical for trustworthy analytics and regulatory reporting within the Salesforce ecosystem.

A Picklist field is designed to allow a single selection from a predefined list of values, making it ideal for the priority field. It is easily reportable and enforces the one-value constraint. The other options either allow multiple selections, require custom validation, or introduce unnecessary complexity with a custom object.

Skinny tables are a specialized feature used to improve performance by flattening data structures to avoid joins. They should only be used when standard indexing is insufficient for performance. Because they require Salesforce Support to maintain and are automatically synchronized, they are a powerful but rigid tool that should be applied sparingly to the most critical, high-read-volume scenarios only, to avoid unnecessary maintenance overhead.

High-volume loads often fail due to locking on parent records referenced by lookups. Sorting the input data by the lookup ID ensures that all children for a single parent are processed together, reducing the time a parent record remains locked. Additionally, avoiding unnecessary triggers on the parent object prevents downstream locking, significantly improving throughput for bulk data migrations.

An External ID field with the Unique attribute is the correct mechanism for upsert matching and duplicate prevention. When the Bulk API processes an upsert, it uses the External ID to determine whether to insert or update. Without the Unique constraint, Salesforce cannot reliably match records, and duplicate Legacy_Id__c values can result in multiple Customer__c records.

Establishing a formal Data Classification Policy is the foundational step for governance. It defines the sensitivity levels and handling requirements for data elements. By standardizing these definitions, architects ensure that technical controls like Shield Platform Encryption or Field Level Security are applied consistently across distributed environments, minimizing risk of unauthorized access and ensuring compliance with global data privacy regulations.

Big Objects provide native, scalable storage for massive data volumes and support asynchronous queries, making them ideal for long-term archival within Salesforce. Salesforce Connect allows real-time access to external data without replication, so archived records remain queryable for audits. Together, they satisfy retention, performance, and compliance requirements without overloading the transactional object.

Field History Tracking is purpose-built to capture old value, new value, user, and timestamp for selected fields, and it surfaces that data in a related list that auditors can query and report on. For a moderate-volume regulatory need on a specific field, it avoids the overhead of a custom audit object or streaming integration.

Effective MDM strategies require a combination of automated matching rules, robust integration standards, and consistent data governance. These factors ensure that data entered through various channels is normalized and deduped at the point of entry. By implementing these controls, organizations maintain a single source of truth, improve data quality, and reduce the operational costs associated with manual data cleansing and conflicting records within their Salesforce environment.

For large data volumes, selective queries require indexed filter fields. Requesting a custom index on a frequently filtered custom date field enables the query optimizer to use the index and avoid full table scans. This directly addresses the report and list view timeouts without changing the data model or introducing external dependencies.

Skinny tables are used to optimize performance by flattening data from multiple related objects into a single table. This removes the need for joins when querying across related records, which significantly speeds up SOQL queries and reporting. They are a powerful tool for large-scale data environments where complex relationship traversal is the main bottleneck for query latency.

Data cleansing is the process of detecting and correcting corrupt or inaccurate records. Doing this before migration is vital because it prevents the 'garbage in, garbage out' syndrome. High-quality data ensures that the new system is reliable from day one, improves user adoption, and prevents the need for costly post-migration data remediation projects which are significantly more complex once the data is integrated into existing business logic.

The query fails because 'Status__c' is likely not indexed, or the 'Pending' value is too common, exceeding the selectivity threshold. On objects with millions of records, Salesforce requires that filters target a small enough subset to be efficient. When a filter is too broad or lacks an index, the system rejects the query to prevent performance degradation for the entire multi-tenant environment.

Archiving to Big Objects keeps data on-platform, preserves identifiers and relationships, and costs far less than keeping everything in standard storage. Async SOQL supports bulk retrieval so records can be produced within the 48-hour window. Deleting records, exporting to CSV, or simply buying more storage each fail at least one of the three requirements: retention, retrievability, or cost.

External objects allow Salesforce to access data stored in an external system in real-time without importing it into the local database. This keeps the Salesforce storage usage low and avoids the performance impacts associated with hosting millions of records locally. It is an ideal solution for historical or secondary data that needs to be visible in the UI but does not require being in the same transactional database.

Many-to-many relationships in Salesforce are modeled with a junction object that has two Master-Detail relationships. The junction record represents the association itself, which is exactly where pairing-specific attributes such as Quantity and Discount belong. Because both relationships are Master-Detail, the junction inherits sharing from both parents and is automatically deleted when either parent is removed, preserving referential integrity.

Concatenating the composite key into the external ID field and using Upsert allows the migration to match existing records by that key. Upsert updates existing records and inserts new ones, preventing duplicates. This is the most efficient and standard method for handling composite keys in large migrations, as it leverages Salesforce's built-in matching and bulk processing capabilities without custom code.

A master-detail relationship is the only standard relationship that enforces a required parent, inherits the parent's sharing settings, and automatically deletes children when the parent is deleted. The other options either lack sharing inheritance, are limited to User, or model a different cardinality. Therefore, the master-detail relationship meets all stated requirements without custom automation.

Field-level security is the correct feature to restrict access to sensitive fields based on profiles. It allows administrators to hide fields from users who should not see them, regardless of record access. This ensures compliance with privacy regulations by limiting field visibility to authorized personnel only.