How to Build a Data Workflow Across Multiple Sources

Every company I work with can answer questions about one system. Revenue by month? Easy, it is in the orders database. Ad spend by campaign? Easy, it is in the ads tool. Ticket volume by product? Easy, it is in the support tool.

Then someone asks the question that actually matters - which campaigns bring customers who stay and do not complain? - and the room goes quiet. Not because the data is missing. Every piece of that answer exists. The three pieces just live in three systems that have never spoken to each other, and nobody owns the space between them.

That space is a data workflow. Here is how to build one.

What a Workflow Really Is

Drop the jargon and a data workflow is four things in order: collect, clean, merge, show. You pull data out of the systems that hold it. You clean up each one so it can be compared to the others. You join them on something they share. You put the result somewhere a person can read it.

Each step has a way of going wrong that quietly ruins the numbers later on, and nobody warns you in advance. So let us take them one at a time, and spend the most time on the step everyone thinks is easy.

Step 1: Collect - Get the Data Out Without Breaking It

The first idea is usually to write a script. Call the API, pull the rows, dump them somewhere. It works on Tuesday and it is broken by Friday, because real data sources fight back in ways a quick script never expects.

APIs send data one page at a time, and the second page is where quick scripts start losing rows. They cap how much you can ask for, and the cap is not a simple request count - heavy queries burn through it faster than light ones. They time out halfway through a big pull and leave you with half the data that looks like all of it. They rename a field in a small update. Databases are friendlier, but not free: run a query that reads a huge table in the middle of the workday, and your app team will want a word.

So the first real decision is not how to pull the data. It is how much, how often, and what happens when it fails.

• Pull data in time slices. Take one week at a time, not the whole history. Then walk backwards to fill in the past. If week seven fails, you retry week seven, not everything.
• Make a rerun safe. Running the same slice twice should give the same result, not double it. This one property is what makes retries safe, and it is the thing most homemade pipelines are missing.
• Say how fresh the data is. Every number a person reads should show when it was last synced. A dashboard that quietly shows yesterday is worse than one that says yesterday.

BI.P.EYE handles this layer for you - paging, rate limits, retries, time slices, and a visible sync time on every view - across databases, APIs, warehouses and platforms like Shopify. Not because the work is exciting, but because it has to be right before anything above it can be.

Step 2: Clean - Fix Each Source Before You Merge It

Here is the mistake I see most. Someone connects three sources, joins all three right away, then tries to clean up the mess afterwards. It never works, because a join multiplies whatever problems went into it.

Clean each source first, on its own, until it means one thing. That usually takes a few boring steps:

• Throw out rows early. Drop the test orders, the staff accounts, the cancelled rows. Every row you remove before a join is a row that cannot mess it up.
• Match the formats. Two systems often disagree because one stores a date as a number and the other stores it as text. They will not join. They will not even show an error - they will just match nothing.
• Flatten what is nested. Order data is rarely flat. Tags come as lists. Line items come as lists. Decide now whether one row means one order or one line item, and you will avoid a very confusing chart later.
• Roll it up to the level you need. If the question is about campaigns, you do not need one row per click. Roll it up before you join.

That last point needs a name, because it is what separates a workflow that lasts from one that falls over.

Decide what one row means, and write it down

For any table, ask what a single row stands for. One order. One order line. One customer per day. One campaign per day. It sounds like a small thing until you join two tables that count different things and your revenue triples.

Before you write a single join, finish this sentence for each input: "one row is one ___." If you cannot finish it, that source is not ready to be joined.

Step 3: Merge - Where the Numbers Go Wrong

Joining is sold as the simple part. Two tables, one key, done. In practice this is where good data turns into bad data, and it happens for reasons that never show an error.

The trap: one row becomes four

You join orders to order lines on order_id. One order has four lines. The shipping cost, which was stored once on the order, now shows up on four rows. Add up that column and your shipping cost is four times too high.

Nothing failed. No row was lost. The total is simply wrong, and it is wrong in a way that looks believable, which is the dangerous kind. The fix is to know, before you run it, how many rows on one side match a row on the other. If one order matches many lines, roll the lines up first, then join. Do not join first and try to fix it in the chart.

If you cannot say whether one row on the left matches one row on the right, or many, you do not know what the result means. Find out before you build anything on top of it.

Keys that almost match

The second problem is harder to spot. The join key exists in both systems, and it is almost the same. One system lowercases the email and the other does not. One puts the region in front of the customer ID. One trims the extra spaces, one keeps them. The join runs, returns fewer rows than it should, and the missing rows are invisible because nobody knows how many there should have been.

Count how many rows matched. Every time. If you join a hundred thousand orders to customers and ninety-four thousand match, that six percent is not rounding. It is a group of customers you are about to make decisions without.

Data that shows up late, or changes

A refund arrives three weeks after the order. An ad tool corrects yesterday's spend tomorrow, once it strips out fake clicks. A customer changes their country. If your workflow only ever adds new rows, all three of those changes are lost, and your history slowly drifts away from the source system until someone notices and nobody can explain why.

The fix is to redo the recent past on every run. Rebuild the last seven days, or the last thirty, so late corrections get picked up. It costs more compute. It costs less trust.

Step 4: Show - Connect It to a Dashboard

By the time data reaches a chart, the interesting decisions are already made. That is the point. A dashboard should be a thin, fast read over data that is already correct, not a place where business rules hide inside a chart setting that one person understands.

This is where the earlier roll-up pays off. If the workflow already rolled the data up to the level of the question, the dashboard does not read millions of rows to draw one bar. It reads a small, ready table. Filters respond right away. Drill-downs open without a spinner. The difference between an analytics tool people use and one they give up on is very often just this.

It is also where the workflow stops being yours alone. Data that is correct and fast can be handed to people who will never open the workflow that built it:

• Charts and filters a non-technical person can change without asking anyone.
• Plain-language questions, answered against the ready data instead of the raw rows. That is why answers come back in seconds rather than minutes, at a fraction of the cost of pointing an AI model at raw data.
• Dashboards placed straight inside your own product, so your customers see their numbers without you hiring a reporting team.

Make the Workflow Something You Can See

A workflow built as a chain of scripts is a workflow only one person can safely change. Once one dataset feeds another, and that one feeds two more, the chain is real whether or not anyone has drawn it. The only question is whether you can look at it.

Drawn out, a workflow is a map: sources at the top, the steps that change the data in the middle, dashboards at the bottom. Making that map visible changes what you can do with it. You can see what breaks if a source goes down. You can see why a number changed, by walking backwards from the chart to the step. You can tell a dataset to rebuild only after the ones it depends on finish, instead of guessing at times and hoping the order holds.

In BI.P.EYE that map is the screen you work in. Each dataset says what it depends on, the platform works out the order, and running a parent runs its children. Nobody keeps a wall chart of which job has to run before which other job at 3am.

An Example, Start to Finish

Say you want to know if your campaigns actually make money.

1. Collect. Orders from the store or the orders database. Spend from the ads tool. Refunds from wherever returns really get recorded, which is often not where you first assume.
2. Clean. Roll orders up to one row per campaign per day, with gross revenue. Roll refunds up to one row per campaign per day. Spend already arrives as one row per campaign per day. Three sources, one row means the same thing in all of them.
3. Merge. Join all three on (campaign_id, date). Because you fixed the rows first, each one matches exactly one other, so nothing gets multiplied. Then count the matches on campaign_id. If spend has campaigns that orders has never seen, you have found either a tracking gap or a campaign that sold nothing.
4. Show. Net revenue is gross minus refunds. Profit is net minus spend. Now the dashboard can show profit by campaign, and open up the day a campaign went negative, and someone can ask "which campaigns lost money last month" in plain English and get an answer.

What makes this work is not the join. It is that steps two and three happened in the right order, and somebody wrote down what a row meant before combining anything.

The Things That Bite

A short list, learned the hard way:

• Time zones. Two systems, two ideas of what "yesterday" means. Pick one time zone for the whole workflow and convert at the edges, not in the middle.
• Currency. An order paid at one exchange rate and refunded at another will not cancel out to zero. That gap is real, not a bug. Show it instead of hiding it.
• Deleted rows. Most workflows notice new rows and changed rows. They do not notice deleted ones. If the source really deletes data, an add-only workflow will keep remembering things that no longer exist.
• Quiet changes at the source. A system adds a column, renames one, changes a type. The pipeline keeps running. The numbers stop meaning what they used to mean.
• The one clever step. Every workflow ends up with one step that made sense to the person who wrote it and to nobody since. Write down why it is there, in the workflow, next to the step.

Start With the Question

With a new data platform, it is tempting to connect everything first and work out the questions later. It feels productive. It gives you a warehouse full of tables nobody trusts.

Work backwards instead. Write down the question you actually need answered. Find the smallest set of sources that can answer it. Clean only those, join only those, and put exactly that on a dashboard. You will have something useful in an afternoon, and the second workflow will be faster than the first, because by then you will know what your data does when nobody is watching.

Collecting data stopped being the hard part a long time ago. Making the pieces agree is the whole job.