Automating Data Pipelines Between Salesforce and Accounting Databases
I built and maintain the pipeline that moves closed-won opportunity data from Salesforce into our accounting system, which runs on a database that our finance team controls tightly and rightly so, since it's the source of truth for revenue recognition and audit records. Getting this pipeline reliable took about four months longer than I originally estimated, and almost all of the extra time went into problems that had nothing to do with the actual data transfer mechanics.
The Real Problem Is Never the API
Salesforce's API is well documented and reasonably stable, and most accounting systems, ours included, expose either a REST API or a well supported database connector. The technical plumbing of moving data from one to the other is the easy part. What actually consumed months of work was getting Salesforce's data model to mean the same thing as the accounting system's data model, because sales teams and finance teams think about the same transaction differently.
An opportunity closing in Salesforce represents a sales event. Revenue recognition in the accounting system might happen on a completely different timeline depending on contract terms, whether it's a subscription with recognized revenue spread over twelve months, a one-time service fee recognized immediately, or a multi-year deal with milestone-based recognition. Salesforce doesn't natively know anything about revenue recognition schedules, and trying to force that logic into Salesforce fields led to a mess of custom fields that sales reps didn't understand and often filled in incorrectly.
Separate the Sync From the Business Logic
The fix was to stop trying to make Salesforce the source of truth for revenue recognition timing. Instead, the pipeline pulls raw opportunity and product line item data from Salesforce, and a separate business rules layer, which I built as its own service rather than cramming the logic into Salesforce workflow rules, applies the revenue recognition schedule based on product type and contract terms that finance defined. That rules layer is the piece that actually talks to the accounting system, not Salesforce directly.
This separation made an enormous difference in maintainability. When finance changes a revenue recognition policy, and they have, twice, since I built this, I update the rules layer without touching the Salesforce side at all. Before this separation existed, policy changes meant modifying Salesforce validation rules and workflow logic that sales ops owned, which meant coordinating two teams for what should have been a pure accounting decision.
Currency and Multi-Entity Complications
We sell in five currencies and book revenue through three separate legal entities depending on the customer's region. Salesforce's multi-currency feature handles the currency conversion for reporting purposes using exchange rates that update periodically, but the accounting system needs the exchange rate that was locked in at the time of invoicing, which is a completely different rate in many cases. Early versions of the pipeline used Salesforce's converted amount directly, and this created reconciliation discrepancies every month that took our accounting team hours to track down and explain to auditors.
The fix was to stop relying on Salesforce's currency conversion for anything that flows into the accounting system. Instead, the pipeline captures the original transaction currency and amount, and the accounting system applies its own locked exchange rate at the point of booking, using rates sourced from the same provider the accounting system already used for other transactions. Consistency of exchange rate source turned out to matter more than which specific provider we used.
Idempotency Saved Us From a Bad Incident
About five months after launch, a transient network failure caused our sync job to retry a batch that had actually already completed successfully on the accounting system side, just before the connection dropped on our end without confirming. Because I had built the pipeline to be idempotent, meaning each Salesforce opportunity ID maps to a unique, checkable record in the accounting system rather than blindly inserting new records on every run, the retry correctly detected the existing record and skipped it instead of creating a duplicate revenue entry.
Without that idempotency check, we would have double booked revenue for roughly forty deals, which is exactly the kind of error that turns into a very uncomfortable conversation with auditors. I can't overstate how much this one design decision, made early and somewhat out of general caution rather than a specific anticipated failure, ended up mattering.
Reconciliation Reporting Finance Actually Trusts
Finance teams are, correctly, skeptical of automated pipelines touching revenue data, and they should be. I built a daily reconciliation report that compares total opportunity value in Salesforce against total booked revenue in the accounting system for the same period, broken out by entity and currency, with any variance above a small threshold flagged automatically. This report is what actually earned finance's trust in the pipeline, more than any amount of technical explanation about how the sync worked. They didn't need to understand the API calls, they needed to see, every single day, that the numbers matched.
What I'd Advise Anyone Building This Kind of Pipeline
Treat the mapping between sales data and accounting data as a business problem to be solved jointly with finance before you write any code, not a technical detail you'll figure out during implementation. Build idempotency in from day one even if it seems like overengineering early on. And build the reconciliation reporting before you consider the project done, because a pipeline nobody trusts might as well not exist, no matter how technically correct it is.
