SF-Data-Arch Data Modeling and Database Design — 15 Questions

Question 1

A company requires a data model where users can associate an Activity with multiple Contacts. Which relationship model best supports this requirement?

Accepted Answer

Junction Object. Salesforce standard Activities use a specific 'WhoId' and 'WhatId' pattern which is restricted. To associate an object with multiple contacts, a junction object is the standard relational database solution. This creates a many-to-many relationship, allowing an unlimited number of associations between activities and contacts, overcoming the limitations of standard lookup fields in the Activity object model.

Answer

Lookup relationship

Answer

Master-Detail relationship

Answer

Text field with IDs

Question 2

An architect notices a LimitException during bulk data loads. Which design pattern in the data model likely causes this?

Accepted Answer

Querying inside a loop. The 'Too many SOQL queries' error is typically caused by performing queries inside a loop, often as a result of a poorly designed data model that necessitates multiple lookups. When objects are not properly indexed or linked, code often resorts to querying related records repeatedly. Restructuring the model to use indexed relationships or using the 'Map' pattern to cache data can resolve this bottleneck.

Answer

Excessive use of indices

Answer

Too many custom objects

Answer

Lack of Validation Rules

Question 3

Which THREE items are true regarding the limitations of Rollup Summary fields in Salesforce?

Accepted Answer

They only work on Master-Detail relationships. Rollup Summary fields are powerful but restricted. They cannot reference cross-object formulas, they are blocked by certain long-text fields, and they only work on master-detail relationships. Understanding these limitations is critical for data architects who must design alternative solutions, such as using Apex triggers or flow-based aggregations, when the declarative rollup summary functionality falls short for complex business logic scenarios.

Answer

They can reference cross-object formulas

Answer

They can be used on Lookup relationships

Question 4

Which tool should an Architect use to visualize and document a complex Salesforce data model?

Accepted Answer

ERD (Entity Relationship Diagram). Entity Relationship Diagrams (ERDs) are the industry standard for modeling database schemas. Tools like Lucidchart, draw.io, or dedicated Salesforce metadata tools allow architects to document object relationships, cardinalities, and field dependencies. This documentation is essential for stakeholders to understand the data flow, maintain the system, and plan future enhancements. Proper documentation prevents technical debt by ensuring that future changes are made with a full understanding of the current structure.

Answer

Schema Builder

Answer

Excel Spreadsheets

Answer

Salesforce Setup Menu

Question 5

What is the primary risk of creating a custom object with an excessively wide schema (many fields)?

Accepted Answer

Performance degradation. Excessively wide tables impact database performance, specifically DML operations, and can hit heap size limits during Apex processing. While modern databases are efficient, Salesforce's platform architecture optimizes storage by limiting the number of fields processed in a single query. A wide table with 500+ fields may lead to performance degradation during indexing, data loading, and query execution, which is why normalization is usually preferred.

Answer

Storage limits

Answer

Security vulnerabilities

Answer

API call limits

Question 6

A Data Architect is designing a high-volume data model for a financial services client. Which TWO considerations must be prioritized to avoid data skew and performance degradation?

Accepted Answer

Avoid having more than 10,000 records sharing the same parent in a lookup relationship.. Data skew is a common bottleneck in large-scale Salesforce implementations. By focusing on index optimization and avoiding excessive parent-child depth, the architect ensures that query performance remains stable as data grows. These choices are critical for maintaining system availability and meeting latency requirements in complex orgs where large numbers of records are updated or retrieved frequently.

Answer

Minimize the number of indexes on the Account object.

Answer

Use Master-Detail relationships for all custom objects to increase speed.

Answer

Store all historical data in the core Salesforce database.

Question 7

Refer to the exhibit. The query is failing on a large dataset. What is the most likely cause of the non-selective query error?

Accepted Answer

The CreatedDate field is not indexed, leading to a full table scan.. The 'Non-selective query' error occurs when a filter condition matches too many records, causing the database to bypass the index and perform a full table scan. In the exhibit, filtering by 'CreatedDate' on a massive table often fails if the index is not effective for the query range. The architect must ensure that query filters target indexed fields that return a small subset of the total dataset.

Answer

The query is hitting the 50,000 record limit.

Answer

The Account object has too many total fields.

Answer

The SOQL query is using a wildcard in the WHERE clause.

Question 8

What is the primary risk of using 'External IDs' for integration that are not marked as 'Unique'?

Accepted Answer

The system will automatically create duplicates during an upsert.. External IDs must be unique to effectively serve as a key for upsert operations. If a non-unique ID is used, the 'upsert' operation will fail because the system cannot identify exactly which record should be updated, leading to duplicate records or system errors. Ensuring uniqueness is a fundamental requirement for maintaining data synchronization and integration reliability across disparate systems.

Answer

The field will not be searchable in global search.

Answer

Reporting will be slower due to index fragmentation.

Answer

The field cannot be used in cross-object formulas.

Question 9

Which THREE actions are required to successfully migrate a Lookup relationship to a Master-Detail relationship?

Accepted Answer

Ensure that all existing child records have a value in the lookup field.. Converting a lookup to a master-detail relationship requires ensuring that all existing child records have a populated parent value. Without a parent, the database cannot enforce the master-detail integrity. Additionally, the field must be converted while following platform limitations regarding existing rollup summaries and record ownership. This structural change is significant and requires careful planning to prevent data loss or migration errors.

Answer

Delete all child records and re-insert them after the conversion.

Answer

Disable all validation rules on the child object.

Question 10

An organization needs to store sensitive project data. Access must be restricted so that only the project team can see the records. Which security feature should be applied at the data model level?

Accepted Answer

Organization-Wide Defaults (OWD).. Organization-Wide Defaults (OWD) are the foundation of Salesforce security. Setting the OWD for the custom project object to 'Private' ensures that records are hidden from everyone except the owner and those with higher hierarchy access. This provides a 'deny by default' posture, which is a best practice for securing sensitive enterprise data before selectively granting access via sharing rules or teams.

Answer

Field-Level Security (FLS).

Answer

Permission Sets.

Answer

Validation Rules.

Question 11

Which design pattern is best for implementing a 'Master-Data Management' (MDM) strategy within Salesforce for Customer objects?

Accepted Answer

Implement a centralized Master-Data object to act as the source of truth.. An 'Hub and Spoke' pattern, where Salesforce acts as the 'Golden Record' hub for customer data, is the most robust MDM strategy. By centralizing the data model, applying data quality rules, and ensuring strict ownership, the architect minimizes discrepancies across integrated systems. This pattern establishes a single source of truth, improving data integrity and reporting reliability across the entire enterprise ecosystem.

Answer

Allow all integrated systems to modify the customer records directly.

Answer

Sync customer data nightly using a flat file CSV dump.

Answer

Use the Salesforce standard Account object without customization.

Question 12

Universal Containers plans to migrate ten million legacy order records into Salesforce. Due to regulatory compliance, these historical records must be marked as read-only and unmodifiable by any user profile, including system administrators, once inserted. Which modeling strategy best achieves this requirement?

Accepted Answer

Store the legacy order records in Salesforce Big Objects to guarantee asynchronous, immutable data persistence.. Utilizing standard or custom fields set to read-only via field-level security does not prevent system administrators from making updates. Implementing an Apex trigger or a validation rule also fails to lock down records completely against administrator overrides. The custom metadata or external object approaches do not fit standard relational order structures. Configuring a custom object with a master-detail relationship to a parent object controlled by sharing rules or leveraging Big Objects provides native immutability at the platform layer.

Answer

Configure standard Field-Level Security to make all fields read-only for all profiles.

Answer

Write a before-update Apex trigger that throws an error whenever a legacy order record is modified.

Answer

Deploy a validation rule checking a custom 'Is_Locked' checkbox field set to true upon record creation.

Question 13

A financial services firm is designing a data model where a custom 'Account_Review' object requires strict visibility controls. The organization-wide default for Account is Private, and reviews must only be visible to the specific review team members assigned via a junction object. How should the architect model this relationship to ensure optimal sharing performance and prevent data skew?

Accepted Answer

Establish standard lookup relationships and implement Apex Managed Sharing on the junction object.. Using standard Master-Detail relationships across junction objects can lead to performance bottlenecks and sharing recalculation locks. Utilizing custom lookup relationships alongside Apex managed sharing provides fine-grained control without triggering costly cascading ownership changes or sharing rule recalculations associated with standard master-detail structures, maintaining high data architecture performance.

Answer

Create a master-detail relationship from the Account_Review object to both the Account and User objects.

Answer

Rely entirely on implicit sharing by making Account_Review a child in a master-detail relationship with Account.

Answer

Configure criteria-based sharing rules targeting the Account_Review object based on custom user lookup fields.

Question 14

A client is migrating millions of records into Salesforce and requires a data model that supports complex, hierarchical relationships while maintaining high performance for SOQL queries. Which strategy minimizes the impact of 'Relationship Query' limits?

Accepted Answer

Use External IDs to denormalize and reduce query depth.. The 'Relationship Query' limit is often hit when navigating too many levels of parent-child relationships in a single SOQL statement. By flattening the data model where appropriate, using denormalization for frequently accessed fields, or utilizing the 'Custom Index' features, an architect can reduce the complexity of the object graph. This approach ensures that the Salesforce query optimizer can efficiently resolve paths without exceeding depth constraints or execution time limits.

Answer

Implement a 5-level deep Master-Detail relationship hierarchy.

Answer

Always use SOQL joins to fetch data regardless of depth.

Answer

Increase the number of lookup relationships on the object.

Question 15

Refer to the exhibit. The query above is performing slowly on an org with 50 million records. Which step should a Data Architect take to improve performance?

Accepted Answer

Create custom indexes on the Industry and Revenue fields.. Performance issues in large datasets are usually due to the query optimizer's inability to use an index, forcing a full table scan. By creating a custom index on the 'Industry' and 'Revenue' fields, the architect enables the database to filter rows efficiently. This is standard practice in Salesforce for high-volume data modeling where queries must scan millions of records; proper indexing is the most effective way to reduce query latency.

Answer

Remove the CreatedDate filter from the query.

Answer

Change the query to use a SOQL 'FOR VIEW' clause.

Answer

Replace the OR operator with an AND operator.