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.