Salesforce Developer: Data Modeling & LDV Optimization

Architectural Principles of Large Data Volumes

For enterprise multi-tenant architecture, there are no issues in transaction execution provided that the tables contain small amounts of records. But if the tables contain hundreds of thousands and millions of records, all processes become different. Slow reporting, record locking deadlocks, data integration failures, and timeouts occur not because of the degradation of any system but because of the problems with architecture that result from the misunderstanding of physical storage, indexes selectivity, and sharing re-computation.

The multi-tenant metadata architecture is used at Salesforce that allows separating business logic from the physical storage. Behind each object of Salesforce, there is a huge relational database structure that consists of a set of shared tables containing records, field values, and relationships. In case an application makes any request on a custom object with standard fields, the underlying database does dynamic joining of many internal tables to get one logical row. In case the number of records is low, these processes occur within milliseconds. With the object becoming part of the LDV, the cost of I/O operations of table joining and full sequential scans becomes larger and larger.

As a result of scalability in the architecture as a consequence of scalability of architecture for scalability requirements, Salesforce as a scalable and infinite data warehouse idea is discarded. Understanding the complexity of patterns in architecture will require a great deal of effort, and this can be achieved only via salesforce developer online training. The training will include data modeling, data indexing, data skew, sharing, and lifecycle governance.

Data Modeling Techniques – Normalization vs Denormalization

A proper data modeling strategy is the first thing needed to optimize the performance. The classical theory of relations always emphasizes on normalization which is aimed at avoiding data redundancies and facilitates transactional integrity. However, normalization within an enterprise environment implies additional relational overhead and hampers scalability of performance.

Normalization of each relation either master-detail or look-up would result in a foreign key relationship. In case of operational reporting as well as UI page rendering, there is need to use joins to get the required information. In case the volume of the child object at 3-4 levels below parent and grand parent is quite large, the performance of the query would be affected adversely.

In this case, denormalization acts as the balancing factor. Denormalization makes it possible to flatten out the relationship and make use of automation to place the attribute values of the parent object onto the child objects. For instance, in the situation where there is need to query through millions of line items of orders and know the industry of the parent account, the attribute value can simply be embedded onto the child record and then query by just selecting the single table.

Obviously, denormalization has its drawbacks. In order to sacrifice some storage space and transactional update difficulties, denormalization increases the speed of queries. In situations where some changes have been made to the attribute values of the parent object, it will be necessary to change millions of child records. This makes denormalization a decision that needs careful consideration based on the characteristics of the attributes; static attributes should be denormalized whereas dynamic attributes should not because of synchronization difficulties.

Relationships Architecture: Master-Detail or Lookup Relationships?

The selection of master-detail and lookup relationships is about a lot more than just cascading deletions via the user interface – it impacts capabilities related to database locking and sharing of records.

Master-Detail Relationships

When it comes to master-detail relationships, the dependency is obligatory. The sharing and security context of the child record is same as that of the master record, hence no separate sharing table should be created for the child object. As a result, the effort required for re-calculating the sharing on the child record level gets reduced. With master-detail relationships we have roll-up summary fields, which help us to add up the number of child records, total value, minimum, and maximum value in the parent record.

The roll-up, however, plays a significant role when it comes to concurrency issues, as the database lock is put on the parent record until the calculation of the roll-up takes place. It will be problematic in terms of database locking in the scenario where there are hundreds of concurrent users/threads editing the child records of one parent record.

Lookup Relationships

Lookup Relationship is the one which allows the entities to operate independently of each other. Entities can manage their own sharing, ownership, and life cycle separately. However, although lookups do not result in any locking of the parent records as compared to native roll-up summaries, there are two major issues in lookups at high volume – independent sharing calculation and relationship skew. While building the high throughput pipeline for loading the data, lookup relationships are chosen over master-detail relationship to increase the throughput. All required calculations are done asynchronously.

Dealing with Data Skew

In case there is an imbalance of the data records across the relationships or the ownership hierarchy within an organization, then it is called Data Skew. Data Skew is considered to be one of the most harmful and often overlooked performance issues.

Skew in Account Data

The skew in account data happens when there is a parent account associated with an abnormally large number of child records that consist of cases, contacts, and custom transactions. When there is an update, reparenting, or deletion operation of those records, the system needs to assess and lock the parent account. In the event that there are multiple updates taking place simultaneously through automated processes for the same parent account but different child records, there will be multiple locking attempts for the parent account leading to concurrency problems.

The solution to this issue is through the use of pool architecture. Instead of having all the generic child records associated with just one parent account such as "Unassigned Corporate Account," the pool architecture enables the usage of several bucket accounts. Round-robin and hash partitioning technique is used to distribute the workloads within the pool.

Ownership Skew

Ownership skew is where one user, usually the system administrator, service account, or default integration user, owns hundreds of thousands to millions of records on one object. Ownership of records directly impacts the Salesforce Role Hierarchy and sharing recalculation.

And in case, when the owner of millions of business records is the integration user who has certain role, and the roles of that integration user or his/her parent role or the organizational group are modified, then the access path will have to be recalculated for each and every single record out of those millions of records. And, that will create a massive task of calculations that will take hours together, as well as locking of sharing tables and performance deterioration.

The following measures of explicit integration governance must be followed by the organization:

The integration users shall never be owners of business records. Transactions must be assigned to actual business owners or queued up properly or assigned based on territories and rules.
And, in case of non-availability of other options where default owner has to be used, then the integration user needs to be kept in a special role, which is above all other roles hierarchically and has no peers as well as no subordinates.

Lookup Skew

In lookup skew, there is a huge number of records in the child table that point to one lookup record in the other unrelated table. These include the default lookup catalogue item, retail location, and status category. As the updating of the lookup field holds the lookup parent record for a while to check the referential relationship, any high concurrency on the child records will only conflict with the parent record. The lookup field must be eliminated by the architects through making the lookup value distributed or eliminating the lookup field, making use of static text picklist.

Query Optimizer, Selectivity, and Indexing

The effectiveness of the query that has high volumes depends on the selectivity. During the performance of the query on the database, it determines whether it needs to do the sequential scan of the whole object or index access of the required records.

Query Optimizer of Force.com

One of the distinctive features of Salesforce Query Optimizer is its placement in between the user queries and actual database. Since Salesforce is a multitenant environment, the traditional cost-based query optimizers do not know anything about the data distribution of individual tenants. The Salesforce Query Optimizer considers specific statistical tables of individual tenants in order to analyze the data distribution, field cardinalities and filter values to determine the execution plans.

Selectivity Thresholds

The indexes would be considered for use only when the filters are selective. If the number of filtered records is less than the platform selectivity threshold, the query optimizer considers it selective. In the case of filters on indexed standard fields, the filters should impact less than thirty percent of the initial one million records and less than five percent of the other records. In case of indexed custom fields, the filters should impact less than ten percent of the initial one million records and less than five percent of the other records.

In this case, the query filter passes the threshold, the Query Optimizer will stop doing indexing and run the table scanning process. That is, if you have an object which has twenty million records, the entire table scan will exceed the threshold of the execution time and you will receive a query timeout error.

Standard and Custom Indexes

The system creates an index automatically for the following fields:

Record ID,
Name,
CreatedDate,
SystemModstamp,
RecordType,
Foreign key relationships (lookups, master-detail)

Indexed may either be manually created based on the fields that are used most often while filtering or may be automatically created during creation of external ID and Unique Constraint. Creation of the appropriate indexed schema and query selectivity analysis is the essence of salesforce dev training.

Database indexing does not support the following fields:

Multi-select picklists,
Rich text areas,
Long text areas,
Non-deterministic formulas

If your formula has relationships, dynamic function (current date/time), or non-indexed fields then your formula is non-deterministic and you can't use database indexes.

Negative conditions ("not equal" "does not contain"), leading wildcards, or null conditions will make your indexes useless and cause a table scan.

Skinny Tables

Since there are many custom objects available, there is going to be a need for join overhead for the combination of both standard fields and custom fields since both of them are available in separate physical tables in the database.

The solution to this problem is requesting the formation of a Skinny Table that has the selected standard fields and custom fields together in a single physical table in real time always in sync with the source objects. Join overhead does not exist in Skinny tables and soft deleted records do not exist in them and thus reading and reporting becomes optimized.

Security Architecture and Sharing Optimization

Security Architecture impacts the scalability of the platform. Overheads increase exponentially because of the complex sharing model.

Organization-Wide Defaults

Creation of Organization-Wide Defaults (OWD) decides the visibility of all records. If the OWD for an object is made Public Read/Write or Public Read Only, then the overhead of sharing tables at the record level gets minimized.

The creation of the Private label results in a generation of a special sharing table for handling accesses on the row level. After the private record creation, any further access to this record will be calculated as well as user shares, public groups, and role hierarchy. With respect to fifty million records on the object, the sharing table will consist of several hundred millions of rows which will make additional access calculations rather expensive.

Depth of Role Hierarchy and Public Groups Changes

Complexity and depth of role hierarchy add to the cost of access calculation. For instance, access to the user at the bottom of the eight levels hierarchy will imply traversing all parents to see whether this user has such access rights inherited from his/her managers. It is essential for the architecture to provide for flat and wide but not vertical and deep hierarchies.

Furthermore, the regular reparenting of roles and frequent changes in public group structure will result in full recomputation of all the sharing process in the whole organization. It is necessary to carry out this calculation process during off-hours.

Depth of Role Hierarchy and Public Groups Changes

Data Lifecycle Management and Data Archiving

All of the above improvements concerning the index and the data model do not change the fact that storing limitless transaction data within the operational database is impractical. As a result of the increasing volume of the historical data in the system, there are delays in processing time, additional costs for license storage, the slowdown of sandboxes refresh process, and poor performance. In such case, the optimized architecture will consider Salesforce as an operational platform rather than a data lake.

Multitier Storage Architecture

An enterprise-class company needs to apply data retention policies and classify the data into the following tiers:

Active Tier: Active data storage that contains up to two years of transactions.
Warm Analytics Tier: Houses historical data that is accessed less frequently by the user but is not often updated (e.g., orders or cases that were closed two years ago). These data may be stored in Salesforce Big Objects and Salesforce Connect & External Objects without breaching the storage limitations of native objects while remaining visible in the user interface.
Cold Compliance Tier: Historical data for audit and compliance that is retained only for compliance purposes. These data are exported and stored externally from Salesforce within data warehouses, data lakes on the cloud, or object stores.

Deletion of Data & Recycle Bin

The process of deleting data needs to be understood before any data deletion on Salesforce is carried out. Data deletion is performed using soft deletion of data where the data is first deleted and stored in the Recycle Bin for fifteen days.

All those deleted records will still be present in the database tables while they will still be considered by the Query Optimizer until they are explicitly ignored. Therefore, the deletion of ten million records will not automatically help improve query performance right away. The only way to do that is via the Bulk API hard delete.

Technical Governance For Long-Term Scalability

Building the architecture for huge volumes of data is not something that should be considered one-time effort, rather a continuous process. An architecture that works fine for five hundred thousand records would definitely not work properly for twenty million transactions.

Consistent technical governance for such performance over large volumes of data is required:

Usage of the Query Plan Tool: Architects and developers need to ensure that the Query Plan Tool is utilized in the developer console as well as automated test tools for estimating the cost of queries prior to coding into the production environment. All queries that have cost factor greater than one must be indexed.
Strategies for Sandboxes: Although full sandboxes reflect the performance of production environment but developer sandboxes are in no way helpful in bringing out any issues related to LDV. The architecture teams need to ensure that the test environment reflects the production environment with similar seed data.
Health Monitoring: Continuous monitoring of the storage volume growth rate, time taken by batch jobs, rows in sharing tables and API errors.

With the help of relational schema which are connected with physical storage level, selectivity of query execution, data skewing, and complete lifecycle archiving, businesses will not have any difficulty in making sure that the platform they will be using will remain performant. In case the group wants to gain expertise about this architecture in-house, then signing up for salesforce development course would be their best bet.

In order to get an understanding of the architecture used in these tactics, see this Large Data Volumes Best Practices Overview. This video is related to the topic because it mentions some of the tactics used for optimizing Salesforce queries and indexing.