Salesforce Certified Data Architecture and Management Designer (SF-Data-Arch) — Questions 1–75

Using Duplicate Management rules alongside the Data Import Wizard or API-based deletion is the standard approach. By identifying duplicates through native features and merging them, the architect preserves related records (like child objects) that would otherwise be deleted during a manual record deletion. This ensures that the parent-child relationships remain intact, preventing data orphans and maintaining the overall integrity of the relational database structure.

Using a combination of a status field and a date range is the standard way to model active versus historical subscriptions. By flagging records as 'Active' and using the 'End Date' field, the architect allows the system to easily filter for current records while maintaining a full history. This design supports both operational reporting and long-term analytical tracking, which is essential for subscription-based business models.

Salesforce provides built-in Data Classification fields for every custom and standard field. This allows organizations to track the Data Owner, Field Usage, Data Sensitivity, and Compliance Categorization directly in the metadata, which is essential for data governance and regulatory compliance reporting.

The most efficient way to transform legacy values to match Salesforce picklist values is to perform the transformation in an ETL tool before loading. This avoids load errors and ensures data quality. Formula fields, post-load updates, and workflow rules are either not possible or inefficient for this purpose.

The Bulk API 2.0 is designed specifically for handling large data volumes by automating the batching process. It simplifies the developer experience while optimizing for high throughput. By offloading the batching logic to the Salesforce platform, it ensures that data loads occur in a manner that respects system resources and governor limits, which is the foundational requirement for managing enterprise-scale data in the cloud.

The audit asks two distinct questions: where personal data resides and who accessed it. Data Classification metadata answers the first by tagging fields with sensitivity categories, and Event Monitoring answers the second by retaining access and API logs for analysis. Field history, bulk exports, and platform encryption do not provide classification inventory or access evidence.

A Skinny Table is a denormalized copy of a subset of fields from a large object, used to accelerate specific queries. The two critical considerations are selecting only the fields needed for common queries to minimize row size, and implementing a synchronization mechanism to keep the copy up to date. Without these, the Skinny Table would either be too large to be effective or become inconsistent with the source data.

The board wants a native, no-code record of when critical Opportunity fields changed, who changed them, and the prior values. Field History Tracking provides exactly that for selected fields and requires only configuration. Chatter notifications, custom Flow logging, and nightly exports each capture partial or indirect information and cannot deliver a reliable, attributed field-change history.

A Federated Data Governance model is best for large, complex organizations. It allows for central oversight and standardized definitions while giving local business units the autonomy to manage their domain-specific data. This balanced approach avoids the bottlenecks of a purely centralized model while preventing the chaos of a decentralized one, ensuring both compliance and business agility at scale.

The 'UNABLE_TO_LOCK_ROW' error indicates that multiple threads or processes are attempting to update the same record or its parent records simultaneously. In data migration, this usually happens when records sharing the same parent are processed in parallel. By reordering the data or reducing the concurrency in the bulk load, architects can prevent these deadlocks and ensure successful completion without constant retries or system instability.

Field History Tracking is the standard declarative feature for auditing field changes. It automatically records changes, retains them for up to 18 months, and requires no code. Other options either involve code, track the wrong type of activity, or do not store historical data, making them unsuitable for this requirement.

Person Accounts merge the Account and Contact models into a single entity, which has long-term implications for the data schema. Once enabled, this change cannot be reversed, and it impacts existing integrations, custom code, and reporting. A Data Architect must ensure that all third-party systems are updated to handle the new object structure, as the standard Account-Contact hierarchy is fundamentally altered, potentially breaking legacy processes that expect distinct objects.

Maintaining the original CreatedDate requires the 'Set Audit Fields upon Record Creation' user permission. Without this, Salesforce ignores the provided CreatedDate and assigns the current server time upon insertion. This permission is a highly sensitive setting and must be enabled specifically for the integration user through the user record or the appropriate permission set, ensuring audit traceability for historical records.

Field Audit Trail retains field history for up to 10 years, but it does not automatically delete data after that period. Manual deletion or archiving is required to enforce retention limits. This is a critical limitation for data lifecycle governance, as organizations must implement their own processes to purge data when it is no longer needed, ensuring compliance with legal and regulatory requirements.

Normalizing phone numbers during the ETL process before loading them into the standard Phone field ensures that all values conform to one format. This makes the standard field's search indexing effective and provides consistent display without custom code. The other options either leave inconsistent data at rest or rely on features that do not automatically normalize free-text input.

The error indicates that DML operations are occurring inside a loop, exceeding the Salesforce governor limit of 150 DML statements per transaction. To resolve this, developers must use the Bulk Pattern, which involves collecting records in a collection (List or Map) and performing a single DML operation outside of the loop. This ensures efficient resource usage and prevents transactions from failing during high-volume data processing scenarios.

Consolidating multiple Salesforce orgs requires meticulous planning around unique ID collisions, metadata differences (such as custom field mapping), and security model alignment. Since each org has its own unique record IDs and potentially overlapping data, an intermediary mapping strategy is vital to ensure that relationships are preserved correctly in the new, unified environment without creating duplicate records or data conflicts.

Truncating data without business approval is a data integrity risk. The architect should analyze the source data to determine why it exceeds the limit and work with stakeholders to either shorten the source text, map it to a larger field, or confirm if truncation is acceptable. Simply truncating in the ETL layer may result in the loss of critical information, which can lead to compliance or business issues down the line.

Enterprise ETL tools provide advanced capabilities such as graphical orchestration, error logging, automated retry logic, and seamless connectivity to heterogeneous data sources. While Data Loader is sufficient for simple, smaller loads, ETL tools allow architects to manage complex migration workflows, parallel jobs, and sophisticated data transformations across multiple systems, which is required for large-scale enterprise integration projects.

The cross-system key mapping problem is solved by storing the legacy identifier in a custom external ID field on the Salesforce Account. This field is indexed and can be used by the integration's upsert operation to match and update the correct record, preventing duplicates. It also preserves the legacy key in the source system, avoiding disruption to existing processes.

The governance council needs one standardized Industry picklist while still permitting controlled local additions. A global picklist value set provides the single source of truth, and a formal request process ensures any new values are reviewed and added centrally. This prevents uncontrolled divergence, keeps integrations and reporting consistent, and gives business units a governed path to request extensions.

A composite key of source system code and native ID provides a globally unique identifier that also indicates the record's origin. This is essential for master data management, as it allows the organization to trace data back to its source and perform accurate merges. It also supports upsert operations in Salesforce via an external ID field.

A Master-Detail relationship enforces the parent reference at the data layer, so records cannot be saved without a valid Account and referenced Accounts cannot be deleted while children exist. This is the native way to eliminate orphaned child records across UI, API, and data-load paths.

Change Data Capture provides a near real-time stream of record changes that an external middleware can consume and use to update the ERP. It is the most direct and scalable way to synchronize address changes without requiring the ERP to call Salesforce. Scheduled jobs and outbound messages introduce latency or require specific endpoints, while Platform Events add an unnecessary layer.

The requirement is a governed, deterministic conflict-resolution process that records which source system was authoritative. An MDM system of record with survivorship rules encodes the source priority order as governed metadata, applies it automatically when sources disagree, and preserves lineage for audit. Field history, manual review queues, and permission-based write exclusion either act too late, rely on human latency, or block legitimate writes without resolving the underlying conflict.

Validation rules are the native Salesforce mechanism to enforce required values and format constraints at save time. A REGEX check can confirm the external identifier is a valid 15- or 18-character ID, blocking bad records before they reach the ERP. Triggers, field-level security, and email alerts do not prevent invalid data from being saved, so they cannot satisfy the integration requirement.

A data stewardship program and a data governance council are both strategic practices that establish accountability and policies for master data. They ensure that data is managed consistently, which is essential for a single source of truth. The other options are either tactical tools or unrelated to data quality management.

Survivorship rules with trust scores allow the MDM system to apply business logic such as 'most recent, but verified for legacy ERP' when consolidating records. Trust scores can be assigned based on data source and verification status, ensuring the golden record reflects the most reliable and recent information.

Using a combination of a status field and optimized list views/reports provides a flexible, low-impact solution. By setting an 'IsActive' flag, data remains available for historical reporting and audits without cluttering the UI for operational users. This pattern is easily scalable and avoids the complexities of moving data to external systems or utilizing custom objects, while adhering to organizational data retention policies without compromising standard Salesforce object relationships.

Big Objects exist to handle data volumes beyond what custom objects can practically store, and they add an architect-defined index that makes key-based retrieval efficient at that scale. Together, scalability and indexed async query access are the two defining reasons to choose Big Objects for this append-only, high-volume domain.

Utilizing the Bulk API 2.0 with serial mode for initial loads minimizes contention on shared resources and prevents record locking errors during high-volume inserts. Designing around index selectivity and disabling unnecessary automation like workflow rules or triggers before loading is crucial for performance. This approach ensures data integrity while drastically reducing the time required to complete the migration compared to standard synchronous API methods.

Many-to-many relationships are best handled using a junction object in Salesforce. By creating a custom object, you can link two Account records while storing additional metadata like the nature or duration of the relationship. This approach overcomes the limitations of standard lookup fields, which only support one-to-many structures, ensuring data integrity and allowing for robust reporting across complex corporate hierarchies.

Field-Level Security (FLS) is the correct mechanism for controlling field access by profile or permission set. It ensures that users only see and interact with data relevant to their role. By applying FLS, architects can enforce strict data access policies, which is essential for compliance and maintaining the 'need-to-know' principle in complex, multi-departmental Salesforce environments where data privacy is paramount.

Custom indexes are the standard mechanism to improve query performance on large custom objects when filtering on a custom field. Because Invoice_Status__c is not indexed, queries perform full table scans across 8 million records. Requesting a custom index on that field allows the database to quickly locate matching rows, improving list view and report responsiveness without altering the data model or field type.

A robust governance framework requires a combination of clear ownership, standardized processes, and technical automation. Without these pillars, data quality degrades over time due to inconsistent entries and lack of accountability. Prioritizing these actions ensures that all stakeholders understand their responsibilities, data is handled consistently, and the organization has a mechanism to detect and remediate issues before they impact business operations.

A Master Data Management strategy requires a 'Source of Truth' identification. By defining which system holds the primary record and using integration middleware to synchronize these records, the architect creates a consistent 'Golden Record'. This prevents data fragmentation where different systems maintain conflicting versions of the same customer, which is the core challenge MDM aims to solve.

Cross-source customer matching with inconsistent names and addresses requires probabilistic matching over multiple attributes with a tuned threshold, because exact keys alone miss too many true matches. Adding deterministic rules for unique identifiers like loyalty number anchors high-confidence matches. This hybrid strategy controls false positives through scoring while preserving recall, which is exactly what the scenario demands.

An External ID field with the unique attribute allows Salesforce to reliably match incoming records to existing ones during an upsert. This prevents duplicates by updating matched records and inserting only new ones. Bulk API 2.0 supports upsert with an external ID, making it the most efficient and scalable solution for nightly loads of millions of records while maintaining data integrity.

Data profiling uncovers hidden quality issues, such as inconsistent formatting, missing values, or unexpected dependencies. By understanding the true state of the source data, the architect can design effective cleansing and transformation rules. Failing to profile leads to 'garbage in, garbage out,' where inaccurate data undermines the value of the new Salesforce implementation and causes significant post-migration remediation costs and user frustration.

To minimize lock contention, avoid updating the parent record every time a child record is updated. Instead, use an asynchronous pattern like a batch job or a queueable apex to aggregate updates and perform a single update to the parent. This reduces the number of locks requested on the parent object, significantly decreasing the likelihood of row-locking errors during high-volume operations.

Roll-up summary fields are the native, declarative way to perform aggregate calculations across master-detail relationships. They are highly efficient, automatically updated by the platform, and require no code. This ensures the revenue data is always accurate and available for reporting, dashboards, and list views, which is essential for sales teams tracking customer lifetime value.

Salesforce provides declarative Matching Rules and Duplicate Rules that evaluate candidate records on create and edit, plus a standard merge interface for consolidation. Together these cover both prevention and remediation natively. Validation rules, activity capture, and custom Apex each address something adjacent but not the core duplicate detection and merge requirement, so they cannot be the primary recommendation.

Using an intermediate middleware or Integration Hub allows the architect to encapsulate the modern authentication requirements of Salesforce while providing a secure proxy for the legacy system. This prevents the need to downgrade security settings within Salesforce and keeps sensitive credentials secure. It is the professional standard for bridging modern cloud platforms with older on-premises systems while maintaining strict security and compliance standards.

Marking the custom external ID field as 'Unique' and 'External ID' ensures Salesforce automatically indexes the column. This indexing is critical for upsert operations, as it allows the platform to quickly match existing records without performing a full table scan. Proper schema design during migration prevents long-term performance degradation and simplifies the maintenance of relational integrity across interconnected systems.

Non-selective queries force the system to perform a full table scan. In an environment with millions of records, this consumes massive resources, often leading to query timeouts, increased CPU usage, and potential platform instability. Preventing this is the primary goal of data architecture, as it protects the multi-tenant environment and ensures that individual system processes do not consume resources allocated for other tenants or internal tasks.

A functioning MDM governance model needs both accountability and measurement. Stewardship assigns human ownership for resolving exceptions and approving merges, while defined quality dimensions and thresholds give the program objective success criteria. Integration patterns, UI standardization, and administrator-only editing are technical or operational choices that do not by themselves establish governance over master data.

When migrating PII, Data Architects must prioritize compliance and data protection. Data masking ensures that non-production environments do not contain real sensitive data, while Field-Level Security (FLS) ensures that only authorized users can access the data within production. These steps are essential to maintaining regulatory compliance, such as GDPR or HIPAA, throughout the lifecycle of the data migration project.

To link Case Comments to Cases in a two-pass migration, the architect must store the legacy Case ID on the Case record as an external ID. This allows the second load to use that external ID to look up the parent Case and correctly associate comments. Without it, the relationship cannot be established, leading to orphaned comments.

Converting a lookup relationship to a master-detail relationship requires that every existing child record currently in the database possesses a populated, valid value for the lookup field. If any orphan records exist where the lookup field is blank, the database cannot establish the mandatory parent-child integrity required by master-detail semantics, causing the conversion operation to fail.

The duplicate creation is due to the external ID field not being unique across regions. The correct solution is to create a new composite external ID that combines region and legacy ID, ensuring uniqueness. This allows upsert operations to correctly identify records and prevent duplicates, while the original legacy ID can be retained for audit purposes.

The primary risk is the discrepancy between the documented policy and the actual system implementation. If the JSON policy dictates partial masking but the Salesforce configuration allows full view access via profiles or permission sets, the policy becomes ineffective. This gap compromises compliance, as the organization cannot guarantee that data protection rules are actually enforced at the application layer where users interact.

The requirement is about linking records whose names differ lexically but refer to the same real-world entity. Deterministic rules and identifier-only matching both fail when the input strings vary, while blocking is a performance layer rather than a matching algorithm. Probabilistic scoring with a tunable threshold is the standard MDM technique for this kind of fuzzy corporate-name reconciliation.

Pre-aggregating data into a custom object via Batch Apex provides a performant and scalable solution for reporting on large data volumes. The report then queries a small, indexed dataset, ensuring fast load times for many users. This pattern is commonly recommended for large-scale aggregations that cannot be efficiently handled by standard reports or real-time queries.

External Objects allow organizations to view and interact with data stored in external systems without importing it into Salesforce. This strategy keeps the Salesforce database lean, avoiding the storage limits and performance degradation associated with managing hundreds of millions of records locally. It is the architectural standard for offloading non-critical, high-volume historical data while keeping it accessible within the platform's user interface.

Establishing data ownership and stewardship is the critical first step in any Data Governance framework. Without clearly defined owners responsible for data quality, definitions, and lifecycle policies across disparate systems, subsequent technical implementations like Master Data Management or deduplication rules lack institutional direction and authoritative accountability.

Salesforce Connect with External Objects is the ideal solution for large-scale data that shouldn't live directly inside Salesforce. It fetches data in real-time from external sources without consuming Salesforce storage or affecting local database performance. This pattern is critical for architects managing massive datasets, as it keeps the core Salesforce CRM instance light and performant while still providing users with seamless access to historical or external financial data.

A Master-Detail relationship creates a tight coupling where the child's lifecycle is strictly governed by the parent. Deleting the parent automatically triggers a cascade delete of all associated children. This is essential for maintaining referential integrity in parent-child hierarchies where the child cannot exist in isolation, ensuring the database remains clean and logically consistent without requiring custom automation for cleanup tasks.

Roll-up summary fields exist to aggregate values from detail records onto a master record. With Donation in a Master-Detail relationship to Account, the architect can create a roll-up summary field on Account that sums the Amount field of all related donations. The total updates automatically on insert, update, and delete, with no code and no scheduled job.

Probabilistic matching uses statistical algorithms to determine the likelihood that two records refer to the same entity based on similarity rather than exact matches. This is vital in MDM because data across systems is rarely perfectly identical due to typos, variations, or formatting differences. Using algorithms allows the system to identify matches that deterministic logic would miss, significantly improving the completeness and accuracy of the resulting golden record.

The compliance officer is asking for documented accountability, agreed definitions, and a controlled change process. A governance charter defines the program's roles, decision rights, and approval paths, while a data dictionary records the approved definition and owner for each data domain. Together they form the authoritative governance reference that can be audited and maintained over time.

The primary concern is the uncontrolled entry of corrupt data. If a governance rule is defined but not active, the system lacks the technical enforcement to maintain quality standards. This leads to 'data rot' where the system collects invalid entries, which will eventually break downstream integrations, analytics reporting, and business processes, causing significant effort for data cleansing at a later date.

A selective query is one that hits an index and returns a small, efficient subset of records. Salesforce imposes selectivity thresholds; if a query exceeds these (e.g., usually 30% of the first million records), it is considered non-selective. Ensuring queries are selective is the single most important factor in maintaining performance for large datasets, as it allows the database to avoid exhaustive scanning of rows.

Near real time bidirectional synchronization between Salesforce and an ERP without middleware is best achieved with Salesforce's native eventing: Change Data Capture streams Salesforce record changes, and Platform Events can carry ERP changes back. This decouples systems, preserves throughput, and supports the required latency. Batch or synchronous dual-write approaches either miss the latency target or create fragile coupling that undermines master data consistency.

Skinny tables are used to optimize read-only performance for reports and list views by denormalizing data into a single table. They are highly effective for performance, but they are maintenance-intensive because they require Salesforce Support to enable and update. They are not a general-purpose solution for transactional data; rather, they are a performance-tuning tool meant for specific, high-volume reporting needs that cannot be satisfied by standard indexes alone.