Key Design Considerations for Effective Salesforce Data Reconciliation

 Business Use Case: 

Salesforce serves as the entry point for creating cases & Updates. When a case is created or updated in Salesforce, the data is sent to an external system via a middle layer for processing. The external system is the source of truth, and once the case is successfully created there, the data flows back into Salesforce through the same middle layer.

However, only a subset of the case data fields is saved in the Salesforce database. Data reconciliation is needed to handle scenarios where a case is successfully created in the external system but fails to get updated or recorded in Salesforce. This ensures that both systems remain synchronized and data integrity is maintained.

Objective of Data Reconciliation :

1. Ensure Data Consistency: Maintain alignment between Salesforce and the external system for accurate reporting and operations.

2. Identify and Resolve Discrepancies: Detect missing, incomplete, or incorrect data and address the root causes.

3. Enhance Data Reliability: Build trust in the data by ensuring it reflects the source of truth.

4. Minimize Business Impact: Prevent errors or delays caused by data mismatches in critical workflows.

5. Support Compliance: Ensure data integrity to meet audit and regulatory requirements.

Possible Reasons for Data Discrepancies :

Sno	Category	Reason for Discrepancy
1	Connectivity Issues	- Network interruptions between systems. - Downtime or unavailability of systems.
2	API Failures	- Timeouts during API calls. - Rate limits exceeded. - Invalid authentication.
3	Data Mapping Errors	- Incorrect field mappings. - Schema changes not updated in integration logic.
4	Validation Failures	- Salesforce validation rules rejecting data. - Missing mandatory fields.
5	Middleware Errors	- Logic issues in the middleware causing dropped or stuck cases. - Failed retries.
6	Duplicate Data	- Duplicate case creation due to retries or misconfigurations.
7	Incomplete Data	- Field truncation or data loss. - Partial updates with missing fields.
8	Integration Gaps	- Lack of synchronization for updates. - Missed notifications or events.

Proposed Approach/Process for Data Reconciliation :

1. Who should be responsible for creating data reconciliation jobs when Salesforce acts as the entry point for data creation, but the data is first recorded in the external system, with the middle layer serving only as a pass-through?

System	Responsibility
External System	- Primary owner of data reconciliation. - Ensures data consistency and integrity between systems. - Records all cases and manages the authoritative data. - Handles discrepancies and logs errors.
Middle Layer	- Acts as a supporting role in data reconciliation. - Provides detailed logs of data transfers and errors. - Monitors data flow and triggers alerts for issues. - Can initiate retries or handle temporary failures.
Salesforce	- Consumer role in data reconciliation. - Monitors and displays reconciled data. - Requests reconciliation jobs from the external system if discrepancies are found. - Ensures data accuracy for reporting and operations in Salesforce.

2. What is the best approach for ensuring data reconciliation when the external system cannot directly verify if a record was created / updated in Salesforce?

Data Synchronization and Reconciliation for Case Creation :

1. The external system creates the case, sending a request to the middle layer.

2. The middle layer sends the case creation request to Salesforce.

3. Upon success or failure of the case creation in Salesforce, the middle layer immediately acknowledges the response back to the external system (Acknowledgment Mechanism Approach).

1. If the creation is successful, the external system will be notified by the middle layer.

1.  Salesforce then updates the external system record with the Salesforce Case ID through the middle layer, ensuring that the external system’s record is synchronized.

2. If the update of the Salesforce Case ID  fails even after retries, Salesforce can initiate a data reconciliation process to identify the record discrepancies and reattempt the update.For specific to this scenario requires data reconciliation job at Salesforce end.

2. If the creation is unsuccessful, the external system will be notified by the middle layer, and necessary actions (e.g., retries or manual intervention) can be taken by the middle layer and external system.

4. For cases created successfully, the Salesforce Case ID will be populated in the external system. The external system can periodically check for records with null Salesforce Case IDs to identify those that didn't sync properly.

1. If a null  is found, it will trigger the case record creation process again, ensuring that missing records are re-synced.

Data Synchronization and Reconciliation for Case Updates :

5. Trigger Status Reset on New Updates:Every time the same record update request is made in Salesforce, the Update Sync Status automatically resets to In Progress to accommodate the latest update attempt.Since Salesforce is the entry point on every update request this field value also passed respectively.

6. The external system updates the case, sending a request to the middle layer.

7. The middle layer sends the case update request to Salesforce.

8. Upon success or failure of the case update in Salesforce, the middle layer immediately acknowledges the response back to the external system (Acknowledgment Mechanism Approach).

1. If the update is successful, the external system will be notified by the middle layer.

1.  Salesforce then updates the external system record with the Update Sync Status(Field) value with ‘Success’ through the middle layer, ensuring that the external system’s record is synchronized.

2. If the update of the Update Sync field  fails even after retries, Salesforce can initiate a data reconciliation process to identify the record discrepancies and reattempt the update.For specific to this scenario requires data reconciliation job at Salesforce end.

2. If the update is unsuccessful, the external system will be notified by the middle layer, and necessary actions (e.g., retries or manual intervention) can be taken by the middle layer and external system.

9. For cases updated successfully, the Update Sync Status( Field) will be populated with the value ‘Success’  in the external system. The external system can periodically check for records with ‘Failed’ status in the field Update Sync Status to identify those that didn't sync properly.

1. If a record  is found, it will trigger the case record update process again, ensuring that missing records are re-synced.

Operational Add-Ons  :

Sno	Add-On	Description	Benefits	Responsibility
1	On-Demand Reconciliation Jobs	Salesforce can request the external system to run data reconciliation jobs for a specific date range.	Provides flexibility to address discrepancies for specific periods or scenarios.	External System
2	Long-Term Failed Records Escalation	Automatically identify and escalate records stuck in a failed state for an extended period.	Ensures no critical updates remain unresolved, reducing business impact.	External System
3	Monitoring and Reporting Dashboards	Create dashboards to track reconciliation job status, pending records, and resolution trends.	Improves visibility into system performance and reconciliation outcomes.	Salesforce/External System
4	Audit Trail for Reconciliation Events	Maintain a detailed log of reconciliation attempts, including timestamps, statuses, and error messages.	Enhances troubleshooting and accountability for failed reconciliations.	External System

Summary Action Table : 

Layer	Action Item
Salesforce	- Receives and processes case creation and update requests from the middle layer.
	- Acknowledges success/failure of case creation or update to the middle layer.
	- Updates external system records with Salesforce Case ID after successful creation (for case creation).
	- Resets "Update Sync Status" to "In Progress" upon receiving update requests.
	- Initiates data reconciliation process if Salesforce Case ID or "Update Sync Status" update fails.
	- Requests external system to run data reconciliation jobs for a specific date range (on-demand).
	- Displays reconciliation status and logs for transparency and reporting.
Middle Layer	- Acts as a pass-through, routing case creation/update requests between Salesforce and the external system.
	- Acknowledges the success/failure of case creation and updates to the external system.
	- Sends case creation/update requests to Salesforce.
	- Ensures successful synchronization between Salesforce and the external system by passing Salesforce Case ID and "Update Sync Status" back to the external system.
	- Triggers retries for failed updates (for case creation and update).
External System	- Manages the authoritative data and records all cases.
	- Ensures data integrity and consistency between systems.
	- Monitors case creation and update status for discrepancies (e.g., null Salesforce Case IDs or failed updates).
	- Initiates retries in case of failed crete / updates.
	- Runs data reconciliation jobs periodically to identify missing or failed records.
	- Receives reconciliation job requests from Salesforce for specific date ranges.
	- Escalates long-term failed records for manual intervention if needed.
	- Maintains detailed audit trail logs of reconciliation attempts for accountability.

LWC Custom File-Upload File Size Limit 4MB ??

 

Business Requirement

The system must support uploading files of any size from Salesforce to the external system, as the external system does not impose any file size limitations. This ensures seamless handling of large files without restrictions, aligning with the external system's capabilities.

Current Design: The file upload process is implemented using a custom LWC component instead of Salesforce's standard file upload feature because the files are uploaded directly to an external system, not stored in Salesforce. This design ensures seamless integration with the external system's APIs and allows for additional custom logic during the upload process.

File Upload Limitations : 

1. Heap Size Limit: Files passed from LWC to Apex are restricted by the 6 MB heap size limit, regardless of whether the Apex method is synchronous or asynchronous.

2. Misconception About Limits: Unlike the standard file upload feature, which supports files up to 2 GB, this design cannot handle files larger than 6 MB without chunking.

3. When transferring a blob file from Lightning Web Components (LWC) to Apex, or vice versa, the file size limitation increases due to the need to encode the blob in Base64. This is because:

• Serialization Limitation: LWC cannot directly pass blob data to Apex, as blobs are binary, and binary data is not supported in JSON serialization (the format used for LWC-Apex communication). Encoding the blob in Base64 allows it to be safely serialized and transmitted as a string.

• Size Increase: Base64 encoding increases the file size by approximately 33%. This means that the size of the original blob is effectively enlarged, potentially making it exceed the limits for data transfer between LWC and Apex, which are restricted by Salesforce's platform limits. For example, the JSON response size limit for Apex to LWC communication is 6 MB, so after Base64 encoding, the original blob size should be less (<) than 4.5 MB to stay within the limit.

Why is the upload of a 3.2 MB or 3.5 MB file itself failing when it should allow up to 4.5 MB, as per the above?

1. If LWC component is sending additional parameters or properties along with the file (e.g., File Metadata (FileChecksum,fileId,message,success) Details,user details, configuration settings), these are included in the response as well.When these are included with the Base64-encoded file, the overall response size can exceed the 6 MB limit if the file size is large.

2. In Apex, the file is converted into Hexadecimal encoding (Increases size by 100%) before sending it to the external system because the system expects all file contents to be combined into a single file. Unlike Base64, Hexadecimal allows for more efficient concatenation of multiple file parts into one, ensuring compatibility with the external system's requirements.

3. When calling a server-side action from a client-side controller in Lightning components, the request payload limit is 4 MB. If any greater than this size will receive an error <Error: Received exception event aura:systemError from server>

https://developer.salesforce.com/docs/atlas.en-us.lightning.meta/lightning/controllers_server_actions_call.htm?utm_source=chatgpt.com

Comparison with Base64

• Base64 Encoding: Increases size by ~33%.

• Hexadecimal Encoding: Increases size by 100%.

4. The maximum HTTP request size for Apex callouts is 12 MB, so files larger than this must be split into chunks.

Debugging Steps: 

Environment	Details
LWC	Validate the file size in LWC before sending it to Apex to avoid unnecessary processing. console.log('Base64 size in bytes:', <base64Size>); //File + Additional Parameters
Track Network Requests	Use browser developer tools (e.g., Chrome DevTools) to inspect the size of the payload being sent to Apex. This helps confirm if the issue arises during data transmission.
Apex	Monitor the heap size at critical points to identify where the data might exceed the limit. System.debug('Heap size after receiving file data: ' + Limits.getHeapSize());//After Receiving the File Data +  Additional Parameters data