Skip to content

Cross-source analytics without a warehouse migration

6 min readUpdated

Cross-source analytics answers a single question using data that lives in several systems, for example comparing pipeline in a CRM against invoiced revenue in an ERP. Doing it safely requires a certified join key between the sources, agreement on currency and fiscal calendar, and a grain check so that joining across a one-to-many relationship does not double-count. Where those are absent, the correct behavior is to decline rather than return a plausible number.

The question that has no home

"Which of our largest open opportunities have overdue invoices?" is an ordinary commercial question. It is also unanswerable inside any single system: the opportunities are in the CRM, the invoices are in the ERP, and nothing in either system knows that account 4471 and customer NEXATRON-CUSTOMER-259 are the same company.

The conventional answer is a pipeline: move both into a warehouse, model them, and ask there. That works, and it costs months. The question is whether it is required before anyone can get an answer.

What has to be agreed before a cross-source answer is trustworthy

  • A certified keyAn explicit, approved statement that this field in system A identifies the same entity as that field in system B. Matching on company name is a guess, and a guess that is right most of the time is worse than one that is obviously wrong.
  • GrainWhether the relationship is one-to-one. Aggregating across a one-to-many link without accounting for it inflates every total, quietly and consistently.
  • Currency and calendarTwo systems can both be right and still disagree, because one reports in local currency on a fiscal calendar and the other does not.
  • What to do when they are missingDecline, and say which of the four is absent. A cross-source total produced without them has no defined meaning, even as an estimate.

How Nexatron approaches it

Nexatron can federate across connected sources using an embedded DuckDB engine, so a question spanning a CRM and an ERP can be answered without first consolidating them into a warehouse. Federation is on by default for new workspaces, and a workspace admin can turn it off.

The governance matters more than the speed. By default, a cross-source question does not need a certified entity link between its sources. A workspace admin can require one, and from then on, where the link is absent or could multiply rows at the requested grain, the platform declines and names what is missing instead of returning a number that would look reasonable and be wrong. Certified metrics are held to a stricter rule: one is not combined across sources unless the workspace opts in and the plan is shown to return the exact certified number.