Quick Summary
This article will take you through how we created a data integration pipeline using AWS for a client whose business ran on six systems with no connection to one another. You will witness exactly how we narrowed the data together, how we managed to find conflicts and sync failures in the process and how we set up monitoring so that the client’s team could ensure the pipeline runs smoothly on its own.
Introduction
Nobody plans to build a data pipeline. Most teams back into it on the day their numbers stop matching.
Someone pulls revenue from the CRM. Someone else pulls the same number from accounting. The two don’t line up, and nobody in the room knows which one to trust.
The average company runs hundreds of applications. Only a fraction of them are actually connected. Everything outside that fraction sits on its own, holding data that never makes it anywhere else.
That mismatch shows up in ways that don’t look like a data problem at first:
- A board deck with the wrong churn number
- A restock order based on inventory that already sold out
- The same welcome email sent to a customer three times, because three systems each think they’re the one supposed to send it
- A support agent promising a refund that finance has no record of approving
None of that looks like a data issue from the outside. It looks like someone dropped the ball. Usually nobody did. The systems just never agreed with each other to begin with.
The longer this goes on, the more expensive it gets. Small mismatches compound. A team that starts by double checking one report ends up double checking all of them, and the time saved by having six specialized tools gets eaten up by the time spent reconciling what they each say.
This is how we built an AWS pipeline for a client dealing with exactly that.
The Client Who Had Six Systems and No Single Source of Truth
Mid-sized distribution business. Their stack grew the way most stacks grow, one tool at a time, whenever something new came up.
A few years of that gets you six systems:
- A CRM for sales
- An ERP for inventory and finance
- An e-commerce platform for online orders
- A support and ticketing tool
- A marketing platform
- An older internal database running the warehouse
Each one worked fine on its own. None of them knew the others existed.
Before we got there, keeping things in sync meant CSV exports, spreadsheet macros, and one employee who had basically become the company’s ETL pipeline without applying for the job. Every Monday she pulled fresh exports from all six systems and reconciled numbers by hand.
Here’s what that actually looked like on a weekly basis:
- Export customer data from three systems separately
- Manually match records that should have been the same customer
- Cross check inventory counts between the ERP and the warehouse database
- Flag anything that looked off and chase it down system by system
- Rebuild the same spreadsheet from scratch the following week
Reports took days. By the time one was finished, some of the underlying data was already a week stale. Nobody fully trusted the dashboards, so decisions got made on gut feel about half the time.
What actually got them to call us was small. Finance closed the month with one inventory number. Warehouse had a different number for the same products. Someone traced the gap three systems deep and found a sync script that had quietly stopped updating two weeks earlier. Nothing had flagged it, because nothing was watching it.
They didn’t want a fix for that one script. They wanted to stop landing back in the same spot every few months.
Why Multi-Source AWS Integration Needs Its Own Approach
Two systems are manageable. Map the fields, write a script, patch it when something changes.
Six systems is not that job times three. It’s a different job, for a few reasons.
Shared entities show up everywhere, and they rarely agree
A customer might exist in the CRM, the e-commerce platform, and the support tool, each with a slightly different name, email format, or ID. Ask something as basic as total lifetime value per customer, and now three records need to agree they’re the same person.
Point-to-point connections don’t scale
Wiring each system directly to whatever it needs works fine for two or three systems. Past that, you get a tangle nobody fully understands, where changing one field breaks an integration nobody remembered was connected. This is usually the point where teams decide to hire AWS developers to build one real hub instead of adding another patch.
The systems don’t run on the same clock
One updates hourly. One only on manual export. One updates in real time. Without something coordinating all six, you end up comparing a number from ten minutes ago against one from last week, and treating the gap as a real problem when it’s really just timing.
Ownership gets blurry
With two systems, it’s usually obvious who owns which data. With six, the same field can be edited in three different places by three different teams, and nobody’s sure which edit should win.
More scripts don’t fix any of this. One pipeline, every source feeds into it, and one place decides what happens when two sources disagree.
How We Built The AWS Data Integration Pipeline: Five Phases
We split the build into five phases, run mostly in order, since each one depended on what we learned in the last.
| Phase
| What It Covered
| Main AWS Tool
| Rough Timeline
|
|---|
| 1. Source Audit and Schema Mapping
| Cataloging fields, formats, and naming across all six systems
| Manual audit
| 2 weeks
|
| 2. Ingestion Layer
| Pulling raw data from each source on its own schedule
| AppFlow, DMS, Lambda + EventBridge
| 3 weeks |
| 3. Transformation and Standardization
| Cleaning, deduplicating, resolving conflicts
| AWS Glue
| 3 weeks |
| 4. Orchestration and Error Handling
| Running each source independently, retrying failures
| Step Functions | 2 weeks
|
| 5. Validation and Monitoring
| Catching bad or stale data early
| CloudWatch, custom checks
| 2 weeks
|
Phase 1: Source Audit and Schema Mapping
We went through all six systems field by field. What each one stored, how it named things, what format it used for dates and currency.
This took close to two weeks, longer than the client expected. We sat with whoever actually used each tool daily, since the documentation rarely matched what the system had turned into over the years.
What we documented for each field:
- Data type and format
- Whether it was required or optional
- Which team actually edited it day to day
- Whether the same concept existed under a different name somewhere else
Skip this phase, and you end up rebuilding the pipeline three months later because a field got missed.
Phase 2: Ingestion Layer
Each source needed its own way of getting pulled out:
There is no single connector that handles six different systems, which is why most AWS integration services’ work starts here rather than at the transformation layer. Each source needed its own way of getting pulled out:
- SaaS platforms with decent APIs: Amazon AppFlow, no custom connector needed
- Older warehouse database: AWS Database Migration Service, for ongoing replication from a system never built with the cloud in mind
- Anything with custom logic: Lambda functions triggered through EventBridge
Right tool per source. That’s the whole idea behind this phase.
Raw data from six systems is messy in six different ways. AWS Glue jobs handled the cleanup:
- Standardized date formats
- Normalized currency
- Matched customer and product IDs across systems
- Stripped duplicates from sales logged twice
This is also where we resolved the conflicts flagged during the audit. When two systems disagreed on a customer’s status, we set a clear rule for which source wins and documented why.
Phase 4: Orchestration and Error Handling
Six sources means six different things that can break on any given day. AWS Step Functions kept them independent, so one failure didn’t take the other five down with it.
If the marketing platform’s API timed out, the rest still finished, and the failed one retried on its own. That retry logic is the first step toward self-healing data pipelines, where a source recovers without anyone opening a ticket.
In the old manual setup, a broken export sat broken until someone noticed by hand, usually after the bad number had already landed in a report.
Phase 5: Validation and Monitoring
The phase most projects skip, and the one that keeps a pipeline trustworthy long term. Automated checks ran after every load, comparing record counts and totals against expected ranges.
CloudWatch flagged a stale source or a bad transformation within minutes, instead of three weeks later at month-end close. It is not the only option, and the right choice among AWS monitoring tools depends on how many sources you are watching and how loud you want the alerts to be.
The Issues We Caught: What They Were and Where They Came From
We found a long list of small conflicts across the six sources. Four are worth walking through.
- Same customer, three IDs: The CRM, the e-commerce platform, and the support tool each had their own customer ID for the same person, with nothing linking them. Order history and lifetime value reports only ever saw a third of the picture.
- Inventory drifting apart: The ERP and the warehouse database tracked inventory independently, and a sync job between them had failed silently. Purchasing decisions were being made off numbers that no longer matched what was actually on the shelves.
- Duplicate orders:Both the e-commerce platform and the CRM recorded the same order, but a few minutes apart, and our initial algorithm for the ingestion schedule retrieved data from both before the records had landed. Revenue reports would have shown inflated totals.
- Stale marketing data: The marketing platform changed a field name during an API update, and the old mapping kept pulling blank values instead of erroring out. Campaign attribution would have quietly gone empty for two weeks with nothing flagging it.
- Mismatched currency fields: Prices were stored in the e-commerce platform in cents, and in the ERP in decimal dollars. This difference had not been reported anywhere. Without fixing, revenue would have been reported a hundred times greater or a hundred times less, depending on which source of the reports was used.
- Orphaned support tickets: If a customer record was merged or deleted from the CRM, the support tool continued to have the old support ID and failed to update itself. Any tickets that were associated with that old ID were no longer appearing under the customer’s current profile, making their support history appear shorter than it actually was for customers who had changed their email address or had been combined with other accounts into a household.
None of these were dramatic on their own. Together, they explain exactly why this company’s reports never lined up from one system to the next.
Worth noting, none of these showed up as errors. Every one of them looked like a normal, successful sync. The data was just wrong, and nothing in the old setup was built to catch wrong data that didn’t come with an error message attached.
What We Handed Off: The Repeatable System
Getting six sources connected was the short term win. A team that could run it without us was the actual goal.
A monitoring dashboard: One screen showing every source, its last sync time, and whether the last quality check passed or failed. Checked once a day now, instead of getting discovered when a report already looks wrong.
A runbook for adding new sources: A seventh system will show up eventually. We documented the audit questions to ask and how to wire a new source into the existing Step Functions setup, so adding one doesn’t mean starting over.
A Data Quality Checklist:
- New source connected: field mapping documented and reviewed
- New source connected: ID matching tested against existing records
- Schema change on any source: transformation logic re-tested before the next run
- New sync job: alert threshold set and tested with a deliberate failure
- Monthly: totals spot-checked against one source by hand
A short training session. We walked the client’s ops lead through the dashboard and the checklist directly, rather than just handing over a document nobody would open again.
Short list on purpose. A checklist nobody runs does nothing.
Conclusion
All six sources now report through one set of numbers, and reports that used to take days are ready the next morning.
Without help, six systems will always tell six different stories. Our AWS integration services that at the source, instead of leaving your team to clean it up every month at close. Get it right once, and nobody has to double check six dashboards just to trust one numbe
Frequently Asked Questions (FAQs)
Depends on how messy the data already is. Budget a few weeks for the audit and mapping phase alone. The full build, testing included, usually runs a couple of months.
No. Each system keeps doing its own job. You’re building a layer on top that pulls data out, cleans it, and brings it together, without touching how the originals run.
One source failing shouldn’t touch the other five, if it’s set up right. It retries on its own and alerts the team, instead of quietly shipping incomplete reports.
Yes. More sources means more mapping and conflict resolution, but the approach stays the same: audit, ingest, transform, orchestrate, monitor.
It depends on the system. Some older tools support database level access, which works well with AWS Database Migration Service. Others need a scheduled export picked up by a Lambda function instead of a live connection.
It varies by project size, but a scoped integration build is typically a fraction of a full time salary, and it comes with documentation the team can run on their own afterward instead of depending on one person’s knowledge.
