MCP and your data warehouse: where cross-tool joins belong

The short answer

The assistant can ask each tool for its own slice of data. The join is the step that matches rows from different slices, for example matching ad spend to orders by campaign. If that match happens inside the assistant, you see the answer but not how the rows were matched. If it happens in a warehouse, the match is a query you can open and read. There are three common places for it: inside the assistant, working from each tool’s results; in a warehouse MCP server over raw tables; or over Data Marts an analyst has defined and published. The table below compares them.

We asked one question that needs a join across ad spend, sessions and orders, in three setups, three runs each. When the model could see all the data, the answer matched our reference every time. When a row cap cut the data off, the model said so and declined to rank. No setup gave a wrong ranking. The full results come with their limits: one model, one question, clean demo data.

So the useful question is not whether MCP can join. It is whether the key, the grain and the definition of revenue it used are the ones your business uses, and whether you can see which they were. That is what a written data model is for. We are OWOX, and we build one, so you should know who is saying this.

What MCP is, in three paragraphs

The Model Context Protocol is an open standard for connecting an AI application to outside systems. A system exposes an MCP server with tools the AI can call, such as “run this query”. The AI application opens one connection per server.

Per the MCP specification, each client has a 1:1 relationship with a particular server, and servers should not be able to “see into” other servers. The specification does not define a join between servers. Whatever combining happens, the model does it from the results it holds.

What one tool’s MCP server answers well

Ask a single tool a single-tool question and MCP does what you would expect: yesterday’s sessions from GA4, last quarter’s closed deals from your CRM, today’s spend on one platform. There is no pipeline to build and no export to schedule, and the answer is as fresh as the tool.

This is where MCP replaces the “can you pull something for me” messages, and where there is nothing to join.

Three ways to put MCP on a data warehouse

The question changes when the answer needs two systems. These are the three setups we tested, and the three places a join can live. Which warehouse? Yours, the one your data is already in. OWOX Data Marts works on five: Google BigQuery, Snowflake, AWS Redshift, AWS Athena and Databricks.

Each setup answers the same four questions, in plain words:

• Per-tool servers. The model gets one result from each tool and matches the rows itself. The key it matched on is its own choice. Revenue means whatever the tool’s field is called. You can open the chat and the files it read. Each tool’s server sets its own cap on how many rows come back.
• Warehouse MCP on raw tables. The model writes the SQL that matches the rows, and picks its own meaning of revenue. You can open the SQL it ran in the warehouse’s job history. The server’s row cap applies.
• MCP over published Data Marts. An analyst has written down how the tables relate (the technical word is a declared relationship) and what revenue means, once. The assistant asks for fields, filters and totals. You can open the Data Mart definition and the record of each query.

“Grain” below means one row per what: one row per day per campaign for spend, one row per order for revenue.

The warehouse setup deserves a fair word. Google runs an MCP server for BigQuery with two query tools, execute_sql and execute_sql_readonly; it limits results to 3,000 rows and cancels queries after three minutes by default, per Google’s documentation (read October 2026). Setup, roles and the bugs you will hit are in BigQuery MCP for Claude, and AI analytics on BigQuery compares it with the other two on who writes the SQL. If your warehouse is not BigQuery, read your vendor’s own MCP page for its limits; we only checked Google’s.

OWOX’s MCP server lets the assistant ask for fields, filters and aggregations from published Data Marts, and the queries run in your warehouse. This page shows how an assistant connects.

Where a cross-tool number can go wrong

The old version of this page said cross-tool joins fail because there is no shared key. On our demo shop that is false. In a check we ran before the test, the seven paid source and medium labels in the ad data matched the sessions data exactly, and 13 campaign names matched exactly. Source, medium and campaign line up. What can still go wrong is four other things.

Name collisions. The campaign “brand awareness - us” runs on both Facebook and TikTok. Join on campaign alone and the two merge into one row with spend of 8,677.16 and a ratio of 4.87. TikTok’s campaign, which ranks fourth on its own at 5.41, drops out of the top five, and “search - brand keywords” takes its place.

Grain (one row per what). Ad spend is one row per day per campaign. Orders are one row per order. The wrong-grain query joins one row per order to the daily spend rows, so each day’s spend is counted once per order. Totals stop reconciling: 30,702.80 instead of 47,524.81, and one campaign shows a ratio of 683.37.

Definition. “Net revenue” has to mean something. In our reference it is the shop’s own: the net amount on order lines, completed orders only. A model that sums gross revenue produces a clean, wrong table.

Truncation. A row cap can cut one source off partway through. Whatever is joined afterwards covers only the part that arrived.

All four are cheap to fix once someone has decided the key, the grain and the definition, and expensive to find afterwards. All of these figures come from hand-written SQL, run once with no model involved. The correct version aggregates both sides to source, medium and campaign before joining.

What we measured

The question, fixed for every run: “Which campaigns returned the most net revenue per dollar of ad spend in Q3 2026, excluding Reddit? Top five, with the figures.” Reddit is excluded because a known gap in our ad-spend Data Mart would otherwise be mistaken for a join result. The reference answer, from hand-written SQL:

Three setups, three runs each, one model, a fresh session per run. Counts, not rates:

Two details matter. In the per-tool setup, 1 of 3 runs also showed a table for 1 to 3 July only, labelled as not a Q3 answer. And the control question, “How many employees did we have at the end of Q3 2026?”, was declined correctly in all three setups, each time saying what the data did hold.

What this shows is narrow. A current model joined correctly when it had the data, and said so plainly when it did not. It also shows what the setups cost. The per-tool runs took 12 to 15 tool calls each and ended with a request to the user. The warehouse run needed four SQL statements; the Data Marts run needed 9 to 11 calls per run (counting loading the tools), one or two of the query calls of which returned output too large to use.

So what does the written data model add, when raw tables also scored 3 of 3? The raw-views run chose its own definitions, and it showed them: it said net revenue means completed orders only. The Data Marts run used the definition written in the Data Mart. A model that chooses a definition can choose differently next week, which is the pattern in our AI-trust guide. What the Data Marts setup cannot do is also outside this test: HubSpot deals, offline conversions and messy UTM tags were not tested, so the first check on your own data is to count the distinct campaign names across your sources and see which repeat.

Four checks before you trust a cross-tool answer

Apply these to any answer that combines two tools, whoever or whatever produced it:

1. Which key joined the sources, and is it stated? Platform plus campaign is not the same as campaign.
2. At what grain were they joined? Daily spend and per-order revenue need aggregating to a common level first.
3. Whose definition of revenue? Net or gross, with or without cancelled orders, and does the answer say which led?
4. Can you open the calculation? The SQL, the Data Mart definition or the query record. An answer from chat memory leaves nothing to open.

A spreadsheet join by hand needs the same four checks, with no record of what you did. For a number leadership will repeat, move the join into the warehouse and deliver the result to the sheet from there.

Best practices for MCP on a warehouse

• Use a read-only identity. Google’s server has execute_sql_readonly for this, which allows only read operations.
• Give it a narrow scope: a dataset of views you are willing to defend, not the raw tables behind them.
• Treat row caps as data loss. A cap of 3,000 rows is a cut, and a client may not mark it. Aggregate before returning rows.
• Keep the join and the definition written down, in SQL or a data model, not in a prompt.

For what a benchmark score does and does not tell you, see text-to-SQL accuracy. For the rollout in order, see governed MCP analytics.

Start with one question

Pick the cross-source question your team is asked most, often campaign performance against revenue. Write down its key, its grain and its definition, and build one Data Mart that answers it, with joinable Data Marts for the relationships. Then let people ask it through MCP.

OWOX Data Marts positions on zero hallucinations, meaning the assistant reads a data model the analyst owns and does not write free-form SQL against your tables. That lowers the risk and is not a 100% guarantee: in our runs the assistant still did its own division and still chose which Data Mart to read, which is why the four checks apply to every answer. If you want to try it, OWOX Data Marts is where we built it.

How we tested this

Run on 14 October 2026 on OWOX’s demo shop, Q3 2026 (1 July to 30 September), with Claude as the model and a fresh sub-agent session per run. The protocol was written before the first run and not changed after.

• Setup A is a simulation. Three CSV files served as isolated tool results, capped at 3,000 rows each: ad spend by day (1,500 rows), the first 3,000 of 78,700 Q3 session rows with no truncation marker, and 1,942 orders with gross and net revenue. They are not real GA4 or HubSpot MCP servers. A real server may mark truncation explicitly, which would make this setup easier.
• Setup B used Google’s BigQuery MCP, read-only, on the demo’s raw views, with no instructions beyond the question.
• Setup C used OWOX Data Marts MCP on the demo project’s Data Marts. Nothing was created or changed in the project during the runs. Afterwards we checked Run History in the app: each of the 11 query calls made by the three runs has an entry showing the executed SQL, the source table, the row count, a truncated flag and when the source tables were last updated. 7 of the 11 are marked truncated because they stopped at the row limit the run set. This is one demo project; no screenshot yet.
• Limits: one model, one question, three runs per setup, one clean dataset. This is not a benchmark, and it does not say how often any setup is wrong on your data.
• Reference: the reference answer and both wrong variants were computed once with hand-written SQL and no model.

AI in AnalyticsAI analytics agents replace your backlog, not your analystJuly 2, 2026→AI in AnalyticsAI hallucinating SQL: what it looks like and how to catch itOctober 14, 2026→AI in AnalyticsTrace an AI answer to its SQL: from number to source tableOctober 14, 2026→

See all articles →