Designing a Reporting Architecture for Multiple Sources

The symptom before the architecture problem

You inherit a reporting setup that someone built in a hurry. A dashboard queries the ERP directly. A second dashboard queries the CRM directly. Both get emailed to the same sales head every Monday morning. The numbers do not match, and nobody can say why in less than a phone call.

This is not a bug in either report. It is the predictable result of querying two systems directly and expecting their outputs to reconcile on their own. They were never designed to agree. The ERP counts an order as booked the moment it is entered. The CRM counts it as booked when the sales rep marks the deal closed. Both are correct, for their own system. Neither is wrong. But a reporting setup built on direct queries has no place to resolve that difference, because there is no layer between the source systems and the report where that decision gets made once.

This post is about the decision that technical leads keep deferring: when a direct query to the source database is still the right answer, and when it is time to build a reporting layer that sits between the source systems and every report that reads from them. If the answer needs a phone call, it's not a system. The goal of a reporting layer is to make that phone call unnecessary.

When is direct querying still the right call?

Direct querying is correct when there is exactly one source system for the data in question, and the people reading the report trust that system as the single record. If your production reporting runs off one ERP instance and nothing else writes production data anywhere, query it directly. Do not build a reporting layer for the sake of having one.

A reporting layer built before it is needed is its own cost. It adds a pipeline to maintain, a second data model to keep in sync with the source schema, and a refresh schedule that becomes a new source of "why is this number stale" questions. If one of your current reports is ninety seconds slow because it is a heavy query against a production table, the right fix is usually an index, a materialised view, or a read replica, not a reporting layer. Scope the fix to the actual problem, the same way you would scope an MVP for an internal operations tool by counting what decision it removes, not by counting infrastructure.

When do you actually need a reporting layer?

You need a reporting layer the moment a second system starts contributing data to a report that someone uses to make a decision. Two systems means two definitions of the same entity, two refresh cycles, and two places where "today's number" can mean something slightly different. A reporting layer is the one place where those differences get resolved, documented, and applied consistently, instead of being re-argued every time someone builds a new dashboard.

The test is simple: can you name, in one sentence, who owns the definition of "order" or "customer" or "open complaint" across your systems? If yes, and there is one system, direct querying works. If you have to check with two different teams to answer that question, you already need a reporting layer. You are just finding out the expensive way, through a report that does not match another report in front of a plant head or a board.

(decision tree)

What does a reporting layer actually look like?

A reporting layer is a separate store, fed by scheduled extraction jobs, that holds a reconciled version of data from every source system. Reports and dashboards query this store, never the source systems directly.

The mechanics are not exotic. Each source system (ERP, CRM, field app) keeps writing to its own operational database, exactly as it does today. A set of extraction jobs runs on a schedule, pulls the relevant tables or calls the relevant APIs, and writes the result into a reporting database with its own schema. That schema encodes the business decisions: what counts as an order, how a customer record from the CRM maps to a customer record from the ERP, what happens when a field app logs a visit against a customer that does not yet exist in the ERP. Reports read only from this reporting database.

(architecture flow)

This is the same discipline that makes role-based access control work from the org chart rather than from a feature list: you write down the real-world rule once, in one place, and every downstream consumer inherits it instead of re-implementing it.

How do you handle data that disagrees between sources?

You resolve it with an explicit rule written down once in the reporting layer, not case by case in each report. Every reconciliation rule has an owner who can answer for it.

Going back to the order example: if the ERP and the CRM disagree on when an order is "booked", someone in the business, not the engineering team, has to decide which definition the company reports against, and why. That decision belongs to whoever owns sales operations or finance, not to whichever developer built the first dashboard. Once that decision is made, it is written into the extraction and transformation logic for the reporting layer exactly once. Every report after that uses the same definition automatically.

The discipline that matters here is not the mapping logic itself, which is mechanical. It is forcing the business decision to happen before the code is written, instead of letting each report author quietly make their own assumption. This is the same principle behind not automating a bad process: a reporting layer that silently picks a tiebreaker rule nobody agreed to is not a system, it is a guess with a dashboard on top.

What changes when a third source gets added?

A reporting layer that was built for two sources should absorb a third without a redesign, provided the extraction and reconciliation logic was written generically rather than hard-coded to two specific systems.

This is the real test of whether your reporting architecture is sound. If adding the field app as a third source means rewriting dashboards, you built reports against sources, not against a reporting layer. If it means writing one new extraction job and one new mapping rule, the architecture is doing its job. The field app will introduce its own version of the same problem: a site visit logged against a prospect that does not exist yet in the CRM, or a stock count from a handheld scanner that disagrees with the ERP's book stock. The reporting layer is where that gets reconciled, the same way order status was reconciled between ERP and CRM.

Criterion	Direct query still works	Reporting layer needed
Number of source systems	One	Two or more
Who reads the report	One team, trusts the source	Multiple teams, cross-checking numbers
Refresh tolerance	Real-time required	Daily or hourly is acceptable
Definition ownership	Obvious, undisputed	Needs a named owner and a documented rule
History and trending	Not required	Required across source-system changes

Does this apply if we are migrating off a legacy system too?

Yes, and it gets more urgent, not less. During a phased migration, the old and new systems both hold live data for the same entities, which is exactly the two-source problem described above, except temporary and under time pressure. If you are migrating off a legacy ERP without stopping the shop floor, build the reporting layer before cutover, not after. It is the only place where "old system says 40 pending orders, new system says 37" gets resolved into one number the plant head can act on.

What this decision actually costs either way

Building a reporting layer too early costs you a pipeline, a schema, and a maintenance burden for a problem you do not have yet. Building it too late costs you months of reports that quietly disagree, decisions made on the wrong number, and a rebuild under pressure once the second system is already live and nobody can turn it off to fix the plumbing. We understand how your business actually works first, then we design the system, and that includes deciding honestly which side of this line you are actually on before writing a single extraction job.

What to do next

List every report currently in use and, next to each, write down how many systems feed it. Any report fed by two or more systems is already a reporting-layer candidate, whether or not the numbers have disagreed yet. Pick the owner for each contested definition before you write any code. If you are choosing the stack to build this on, start with how to choose a tech stack the business has to live with rather than the newest option on the market. For a full view of how this fits into building operations software, see the building software hub, or talk to us directly through our custom software development team before you commit to an architecture.