Production-ready agents, Prerequisite 2: The business terms and the data behind the agents

Every agent in this series answers questions about one company, Acme Consumer Brands, and one campaign. If you do not understand the business question and the data behind it, the agent code in the later parts will look like plumbing with no purpose. So before Part 1, I want to slow down and look at the business itself.

This post covers the question the agents answer, the business terms both teams use (each with its formula and a real number), the four tables and who owns them, and the rule that decides whether an agent may read a number directly or has to ask its owner. By the end you will be able to recompute the headline finding yourself in plain Python.

If you have not read the Python prerequisite, you can still follow this post. The only code is in the “Try it” section at the end.

If you prefer to watch first, here is the heart of this prerequisite as a short animated explainer.

Video: the business terms, the four tables and the ownership rule behind the agents, in three minutes. The narration is a synthetic voice; the script and animation are mine.

Where are we in the architecture?

This prerequisite does not add a box. It explains the business question and the data (marketing in BigQuery, supply in Databricks) that the system below is built to answer. Part 1.25 walks through the whole picture, layer by layer.

the complete Acme architecture: Maria asks in Gemini Enterprise; the router runs on Agent Runtime behind plugins and Model Armor and calls the marketing and supply agents over A2A; they reach BigQuery and Databricks through Agent Gateway; identity and secrets on the left, models, knowledge and the market watch managed agent for public research on the right, traces, logs and analytics below; above it, shoppers reach Gemini Enterprise for CX, whose conversation insights land in BigQuery, and the Legal industry edition plugs into Gemini Enterprise

What is the business question?

The bottom line: did the campaign work or fail? Both. It created demand that supply could not meet, and you can only see that when marketing’s data and supply’s data sit side by side.

Start with a restaurant that puts a new dish on a big poster outside. The front of house is delighted: the tables are full every night. The kitchen is miserable: it runs out of the main ingredient by eight o’clock, half the orders go out incomplete, and regulars complain. Each side reports a true story. Nobody is lying; they are reading different halves of the same week. The front of house is marketing, the kitchen is supply, and the poster is the campaign.

How does this apply to Acme? Acme sells household products under three brands (product names sold to shoppers): Acme Pure, Verde and Harvest Row. In May and June 2026 the marketing team ran a large advertising campaign (a planned set of adverts with one goal, one budget and fixed dates) called Acme Pure Refill Launch, in Western Europe and Southern Europe.

Marketing says it worked: the adverts were seen tens of millions of times and retailers ordered more Acme Pure. Supply says it was a disaster: deliveries fell short every week, and retailers charged Acme penalties for it.

one campaign, two dashboards: marketing sees EUR 283,590 of spend and a 0.95% click-through rate, supply sees OTIF of 87.4% and EUR 36,026 of penalties; both are looking at the same brand, region and weeks

Both teams are right, because they are looking at different halves of the same event. The campaign created demand, and the supply plan was never told to expect it. Marketing’s data sits in BigQuery (Google Cloud’s data warehouse). Supply’s data sits in Databricks (a separate data platform). Nobody had put the two halves side by side, and that is the job of the agents we build in this series.

What do the marketing terms mean?

The bottom line: spend, impressions and clicks are counts; CTR and CPM are ratios built from those counts; reach and frequency are about people, not showings.

A simple example first (made-up numbers). A bakery pays EUR 20 for a poster in its window for a week. People walk past and look at it 2,000 times; 40 of those looks lead someone to come in. The 2,000 looks are impressions, the 40 visits are clicks, and the share is 40 / 2,000 = 2%, the CTR. The poster cost EUR 20 for 2,000 looks, so EUR 10 per 1,000 looks: the CPM. If those 2,000 looks came from 500 different people, reach is 500 and frequency is 4.

How does this apply to Acme? Marketing measures what the adverts did. Here are the terms, each with the formula and the real value for the Acme Pure Refill Launch in Western Europe over its seven weeks (4 May to 15 June 2026).

• Media spend. The money paid to show the adverts, in euros. It is simply added up.
• Impressions. One impression is one showing of an advert on a screen. It counts showings, not people.
• Clicks. The number of times someone clicked an advert.
• CTR (click-through rate). The share of impressions that led to a click: clicks divided by impressions, times 100. Google Ads defines it the same way.
• CPM (cost per mille). The cost of 1,000 impressions (“mille” is Latin for thousand): spend divided by impressions, times 1,000. It tells you how expensive the audience was.
• Reach. The number of distinct people who saw an advert at least once.
• Frequency. The average number of times each of those people saw it.

the marketing terms: media spend, impressions and clicks are counted; CTR and CPM are ratios computed from them; reach and frequency describe people, not ads, and come from the ad platform

Two rules here catch people out. First, ratios are computed from totals. The seven-week CTR is 527,871 clicks divided by 55,850,066 impressions, which is 0.95%. Averaging the seven weekly CTRs gives a different and wrong answer. Second, reach is not additive. The same person can be reached in week one and week two, so you never add weekly reach together.

Here is the first rule on the seven Refill Launch weeks from the Try it section below:

Weekly CTRs (%):    1.24, 1.00, 0.90, 1.13, 0.91, 0.74, 0.86
Average of those:   0.97%   (wrong)
From the totals:    527,871 clicks / 55,850,066 impressions = 0.95%   (right)
Same trap for CPM:  average of weekly CPMs EUR 5.27, from the totals EUR 5.08

The average is wrong because it gives every week the same weight. The week of 18 May had 9.67 million impressions and the week of 4 May had 5.47 million, yet each counts as one seventh. If the agent ever computes a ratio by averaging rows, it reports 0.97% and nobody spots the error.

And the second rule, on a generic example with three shoppers, A, B and C: week one’s ads reach A and B, and week two’s reach B and C. Adding weekly reach gives 2 + 2 = 4, but only three people were reached across the two weeks, because B was counted twice.

One honest gap: in the demo data, reach and frequency come from a separate table that was generated on its own, so they do not reconcile with the impressions column. In real advertising data, impressions divided by reach gives frequency for the same audience and period.

What do the supply terms mean?

The bottom line: OTIF asks “did the retailer get everything it ordered, on time?”; anything less than a full, on-time delivery counts against it.

Supply measures whether retailers got what they ordered. The unit of work is a unit (one product, such as one bottle of refill).

• Units ordered. How many units retailers asked for in a week.
• Units delivered. How many of those arrived on time and complete.
• OTIF (on time in full). The share of ordered units that arrived on time and in full: delivered divided by ordered, times 100. This is the headline service number in consumer goods, and retailers write it into their contracts.
• Fill rate. The share of ordered units that were shipped at all, with no time condition.
• Unmet units. Units ordered but never delivered: ordered minus delivered.
• Service level. The general name for how well a company meets demand. At Acme, it means weekly OTIF against a target of 95%.

the supply terms: units ordered and delivered are counted; OTIF is delivered over ordered; unmet units are the difference; service level is judged against a 95% OTIF target

OTIF has no single industry definition. McKinsey’s article on the consumer sector describes how companies and retailers measure it differently, for example per order or per order line, and against different delivery dates. The demo keeps it simple: it has one delivery figure per week, and supply’s published formula computes OTIF from units. That makes OTIF and fill rate the same number here, which would not be true in a real supply chain. Supply’s formula rounds to one decimal place, so 104,570 over 119,581 is reported as 87.4%.

To see how the two differ, take a generic order: a retailer orders 100 units, 95 are shipped, and 90 of those arrive on time and complete. Fill rate is 95 / 100 = 95%. OTIF is 90 / 100 = 90%. The five units that shipped late count for fill rate but not for OTIF.

What do the money terms mean?

The bottom line: missing the service target costs Acme twice: a penalty on every unit it failed to deliver, and a premium for rushing what it did deliver.

A simple example first (made-up numbers). A caterer promises a wedding 100 meals and signs a contract: if fewer than 95 arrive on time, it pays EUR 3 for every missing meal. It delivers 90, so it pays 10 × EUR 3 = EUR 30. To get even those 90 out, it hired an extra van at short notice, which cost more than its usual delivery. The EUR 30 is the service penalty, the 95 is the service target, and the extra van is the expedite premium.

How does this apply to Acme? The last group of terms is about money, and all of it sits in one table that only supply and finance can read.

• Retailer service penalty. A fee a retailer charges when a supplier misses its service target. In Acme’s (fictional) contracts, any week with OTIF below 95% is charged on every unmet unit, at a rate per unit that depends on the region: EUR 2.40 in Western Europe, 2.10 in Southern Europe, 1.80 in North America, 1.20 in Latin America and 1.00 in South Asia.
• Cost to serve. What it costs to pick, pack and ship the delivered units, about EUR 0.48 per unit in Western Europe in a normal week.
• Expedite premium. The extra cost of express freight and split loads when supply is stressed. In this data, cost to serve goes up by 22% in any week below the 95% line.
• Trade spend. Money paid to retailers for promotions, displays and discounts. It is a different budget from media spend.
• Net revenue. Sales revenue after discounts and allowances.

the money terms, all in supply's confidential table: a service penalty is unmet units times the regional contract rate in any week below 95% OTIF; cost to serve rises by an expedite premium in stressed weeks

The penalty is easy to check by hand. All seven campaign weeks were below 95%, so every unmet unit was charged: 15,011 × EUR 2.40 = EUR 36,026.40 in Western Europe. Add Southern Europe, where the same campaign ran, and the penalties reach EUR 51,331 in total. The expedite premium is my own estimate from the rule: cost to serve was EUR 61,356.66 over the seven weeks, and removing the 22% uplift leaves about EUR 50,000, so roughly EUR 11,000 was the price of rushing (derived, not a column in the data).

What does the data look like?

The system has four tables, on two platforms, owned by two teams.

Think of two departments in two buildings, each with its own filing cabinets. Some drawers have a sign on them saying “anyone may read this, and here is how to total it”. One drawer is locked, and only its owners and the finance team have the key. Marketing’s building is Google Cloud, supply’s is Databricks, the open drawers are shared tables, and the locked drawer is service_cost.

the schema: marketing's campaign_performance and audience_reach live on Google Cloud as Iceberg tables read by BigQuery; supply's supply_service and confidential service_cost live in Databricks as Delta tables in Unity Catalog, with three metric views; the tables join on brand, region and week_start, and only marketing has campaign_name

On Google Cloud, marketing owns two tables. Both are stored as Apache Iceberg tables (an open table format: data files plus metadata that several engines can read) and queried with BigQuery.

• campaign_performance has one row per campaign, brand, region and week: media_spend_eur, impressions and clicks. It has 151 rows.
• audience_reach has the same keys with reach and frequency.

There is also campaign_calendar, a view that supply owns over the campaign table. It shows when campaigns ran and how big they were, without the spend.

In Databricks, supply owns two tables, both registered in Unity Catalog (Databricks’ governance layer, which decides who can read what).

• supply_service has one row per brand, region and week: units_ordered, units_delivered and otif_pct. It is a Delta table (Databricks’ default table format) with UniForm turned on, which writes Iceberg metadata alongside, so Iceberg readers such as BigQuery can read it without a copy. This is the table supply shares.
• service_cost has the same keys with trade_spend_eur, net_revenue_eur, cost_to_serve_eur and service_penalty_eur. It holds commercial contract terms, so it is granted to supply and finance only and never leaves Databricks.

On top of those tables, supply publishes three metric views (named, governed definitions of business measures in Unity Catalog): service_metrics, penalty_metrics and cost_metrics. Anyone who wants a supply measure asks the metric view, so the definition lives in one place.

What is grain, and why does it matter here?

The bottom line: before you join two tables, check that one row means the same thing on both sides; if it does not, sum first or the numbers multiply.

A simple example first (made-up numbers). A café keeps an order log with one row per order, and a weather log with one row per day. On Monday there were three orders and 10 mm of rain. Join the two on date and the rain row is copied onto all three orders. Add up the rainfall column and Monday had 30 mm of rain. Nothing in the result looks broken; the rain is simply counted three times. The order log and the weather log have different grains, and date is the join key.

How does this apply to Acme? The grain of a table is what one row stands for. Marketing’s grain is one campaign, in one region, in one week. Supply’s grain is one brand, in one region, in one week. The two sides share three join keys (the columns you match rows on): brand, region and week_start.

campaign_name exists only on the marketing side. That is a grain mismatch, and it has two consequences we will meet again. You must filter or sum the campaigns before you join, or one supply row matches several campaign rows and is counted several times. And you can never split a supply number, such as penalties, by campaign, because supply does not know which campaign caused a given order.

Here is the first consequence on real rows. In the week of 4 May, Acme Pure had two campaigns running in Western Europe, so marketing has two rows for that week and supply has one:

Marketing rows, 2026-05-04:  Acme Pure Refill Launch  spend 36,317.41
                             Acme Pure Everyday       spend  3,506.34
Supply row,     2026-05-04:  ordered 17,475  delivered 15,585  unmet 1,890  OTIF 89.18%

Join without summing first:  the supply row matches both campaign rows
  ordered 34,950 (not 17,475)   unmet 3,780 (not 1,890)   OTIF still 89.18%

OTIF looks right because both its top and bottom doubled, which is exactly why the mistake is easy to miss. Every count from supply is twice the truth, and so is any penalty computed from them. This is the café’s 30 mm of rain again: supply’s one weekly row is the weather, and the two campaign rows are the orders.

What do the real rows show?

The bottom line: marketing’s rows look fine and supply’s rows look bad; only lining the weeks up shows that the bad weeks are exactly the campaign weeks. Like the restaurant’s front of house and kitchen, read each side first, then together.

Here are the real marketing rows for Acme Pure in Western Europe. The blue rows are the seven campaign weeks. Around them I have shown one week before and one after, where only the small, always-on Acme Pure Everyday campaign was running. CTR and CPM are computed per row.

real campaign rows for Acme Pure in Western Europe: the seven Refill Launch weeks with CTR and CPM computed per row, framed by an always-on Acme Pure Everyday week before and after, and the seven-week total

Spend is steady at EUR 36,000 to 44,000 a week, and CTR stays between 0.74% and 1.24%. Nothing looks wrong.

Now the supply rows for the same brand, region and weeks, with two weeks either side for contrast:

real supply rows for Acme Pure in Western Europe: OTIF sits around 95.5 to 96.3% in the weeks before and after, drops to between 85.9 and 89.2% in all seven campaign weeks, and every one of those weeks triggers a penalty

In the two weeks before the campaign, OTIF is 96.3% and 95.5%. In the first campaign week it drops to 89.2%, and it stays below 90% for all seven weeks, with a low of 85.9% in the week of 1 June. The week after the campaign ends, it is back at 95.8%. Orders tell the same story: an average of 17,083 units a week during the campaign against 13,983 in the nine weeks before, about 22% more (my calculation from the rows). The campaign worked. Supply simply did not have the stock.

What does the join show?

Put the two sides together on brand, region and week, after filtering marketing’s rows to the Acme Pure Refill Launch:

the join: filter the campaign table to one campaign, match each row to the supply row with the same brand, region and week_start, and put spend and service side by side; across the seven weeks, EUR 283,590 of spend meets 87.4% OTIF and 15,011 unmet units

The headline finding: Acme spent EUR 283,590.25 on the campaign in Western Europe, which pushed orders up by about 22%. OTIF fell to 87.4%, 15,011 units were never delivered, and retailers charged EUR 36,026.40 in penalties. The company paid twice: once to create demand, and again for failing to meet it.

One honest caveat about attribution (deciding which cause gets the credit for a result). The classic illustration: ice cream sales and sunburn rise in the same weeks, but ice cream does not cause sunburn; hot sunny weather drives both. A join can only show you the two columns moving together. Here, the question is whether another campaign could be the sunny weather. Acme Pure Everyday ran in the same region in the same weeks, spending EUR 25,759.26. The data points to the Refill Launch, because Everyday ran for all 26 weeks and OTIF was healthy whenever the Refill Launch was not running. But a join shows that two things happened together; it does not prove that one caused the other. An agent should say so.

Does every campaign break supply?

No. A finding is only convincing when you can show a similar case where the bad outcome did not happen. A medicine trial works the same way: one group gets the new pill, a similar group gets none, and only the difference between them counts. Without that second group, you cannot tell the pill from everything else that happened that month.

How does this apply to Acme? The data includes a control (a comparison case where the suspected cause is present but the bad outcome is not) for exactly this reason. Verde Spring Refresh ran in North America for seven weeks and lifted Verde orders by about 17%. Supply kept up.

the control: Verde Spring Refresh in North America also lifted demand, but supply kept OTIF above 95% in every week, so there were no penalties; the Acme Pure finding is about this campaign, not about campaigns in general

OTIF never dropped below 95%, only 716 units went unmet, and there were no penalties. So the finding is specific: this campaign, in these regions, outran this supply plan.

Federate, delegate or cannot: who may compute each number?

The bottom line: for every number in a question, code decides whether the asker reads it itself, borrows the owner’s recipe, asks the owner to cook it, or says it cannot be done.

A simple example first: four ways to get dinner. You cook your own dish from your own cupboard. A friend lends you their recipe and lets you use their pantry, so you cook their dish yourself. A restaurant will not share its secret sauce, so you order the dish and it arrives ready-made. Or you ask for a dish nobody makes, and the honest answer is “we don’t do that”. These are the four routes below: local, federated, delegated and cannot.

How does this apply to Acme? So far I have joined the tables as if anyone could read everything. In a real company that is not true, and it is the most important design decision in this series. For every number in a question, the system picks one of four routes:

• Local. The asking team owns the number and reads its own table. Media spend is local for marketing.
• Federated. The number belongs to another team, but that team has shared the table and published the formula (said exactly how to compute it). The asking team reads the shared table where it lives, with no copy. Catalog federation is the platform feature that makes this possible: BigQuery reads the Databricks table through its Iceberg metadata, and Databricks reads Google Cloud tables through Lakehouse Federation.
• Delegated. The number belongs to another team, which has not published the formula. The asking team’s agent asks the owner’s agent, and the owner computes the number through its own metric view. That metric view is the only door to the number.
• Cannot. Nobody can reach the number, or it does not exist at the grain the question asks for. The agent says what is missing instead of guessing.

A cannot answer still gives what it can. Asked for penalties by campaign, a good answer reads like this (illustrative wording): “Penalties for Acme Pure in Western Europe were EUR 36,026.40 over the seven campaign weeks. I can’t split them by campaign: supply records orders by brand, region and week, not by campaign.”

How does semantic.yaml decide?

The bottom line: the route is looked up in a file, not guessed by a model. Think of a library catalogue: each card says which department owns the book, which shelf it is on, and whether it may leave the reading room. The librarian does not decide on a whim; she reads the card. In Acme, the card is an entry in semantic.yaml, the shelf is objects, and “may leave the reading room” is published_formula.

The decision is not left to a model. It lives in a shared file called the semantic registry, semantic.yaml, which both agents read. Every measure has an owner, the tables it can be computed from, and a published_formula flag:

otif_pct:
  owner: supply
  objects: [supply_service]
  metric_view: service_metrics
  expr: "ROUND(100.0 * SUM(units_delivered) / NULLIF(SUM(units_ordered), 0), 1)"
  published_formula: true      # supply publishes the formula with the shared table

service_penalty_eur:
  owner: supply
  objects: []                  # no table anyone else can reach
  metric_view: penalty_metrics
  published_formula: false     # the metric view is the only door

The rule in code is short: if the asking team owns the measure, it is local. If another team owns it and published_formula is true with a reachable table, it is federated. If there is no published formula but there is a metric view, it is delegated. Otherwise, cannot. The router in Part 5 runs exactly this check.

Here is a simplified version in plain Python, run on the measures in Maria’s question. The registry holds the two entries above plus marketing’s own spend measure:

registry = {
    "media_spend_eur":     {"owner": "marketing", "objects": ["campaign_performance"],
                            "published_formula": True},
    "otif_pct":            {"owner": "supply", "objects": ["supply_service"],
                            "metric_view": "service_metrics", "published_formula": True},
    "service_penalty_eur": {"owner": "supply", "objects": [],
                            "metric_view": "penalty_metrics", "published_formula": False},
}

def route(measure: str, asking_team: str, by: str | None = None) -> str:
    m = registry[measure]
    if by == "campaign_name" and m["owner"] == "supply":
        return "cannot"                    # supply's grain has no campaign_name
    if m["owner"] == asking_team:
        return "local"
    if m["published_formula"] and m["objects"]:
        return "federated"
    if m.get("metric_view"):
        return "delegated"
    return "cannot"

route("media_spend_eur", "marketing")                          -> local
route("otif_pct", "marketing")                                 -> federated
route("service_penalty_eur", "marketing")                      -> delegated
route("service_penalty_eur", "marketing", by="campaign_name")  -> cannot
route("otif_pct", "supply")                                    -> local

Code made every one of those decisions from the registry, so the same question gets the same routes on every run. The same measure, otif_pct, is federated for marketing and local for supply, because the answer depends on who is asking.

how each measure in one question is routed: code reads semantic.yaml and sends spend and CTR to marketing's own table, OTIF and unmet units to the shared supply table through federation, penalties and cost to serve to the supply agent, and a per-campaign penalty split to cannot

For Acme, that gives a clear split. Impressions, clicks, OTIF, units ordered and unmet units federate: their owners publish the formula, the tables are shared, and the arithmetic is a plain sum or ratio. Service penalties and cost to serve delegate: they depend on confidential retailer contracts and an expedite rule that supply may change at any time, so supply keeps the definition and answers for itself. And “penalties by campaign” is cannot, because of the grain mismatch we saw earlier.

When does federation make sense, and when does delegation?

The bottom line: federation buys speed and row-level detail at the price of depending on someone else’s table; delegation buys control and confidentiality at the price of a slower second call. In the dinner example, borrowing the recipe is quick, but you are stuck if your friend changes it; ordering from the restaurant is slower, but the chef stays in charge of the sauce.

the decision for each measure: if you own it, read it locally; if the owner published the formula on a shared table, federate; if the owner exposes it through its own agent and metric view, delegate; otherwise say it cannot be answered; federation is faster, delegation keeps control with the owner

Federation makes sense when four things are true: the owner publishes the formula, the table is shared and governed, you need row-level data (such as the week-by-week join above, which an aggregate answer cannot give you), and speed matters, because one query is faster and cheaper than a conversation between two agents. The price is coupling. You depend on the shape of someone else’s table, a change to the formula has to be versioned (OTIF is “supply definition v1”), and access is checked on two platforms.

Suppose supply moved OTIF to a per-order-line definition, one of the variants McKinsey describes. Supply’s own dashboard would switch to the new figure. Marketing’s federated query would still run the v1 units expression from semantic.yaml and keep reporting 87.4%. Both numbers are called OTIF, and they no longer agree. The version label is what lets an answer say which definition it used (“OTIF 87.4%, supply definition v1”), so the gap is visible instead of silent.

Delegation makes sense when the owner must keep the definition, when the data is confidential (contract rates should never be visible to marketing), when the logic changes often, or when the answer must respect the asking user’s own permissions. In this system the supply agent queries Databricks as the person who asked, so Unity Catalog applies that person’s grants. The price is a second agent call, which is slower and costs more, and you get totals back rather than rows you can join.

How can Maria get a penalty total if she cannot read service_cost? Because a metric view is a door with its own lock. Databricks’ metric view permissions page says metric views follow “the same permission model as other views”, and the minimum grant to query one is SELECT on the metric view itself (plus USE CATALOG and USE SCHEMA on its catalog and schema). Its views page explains what happens underneath: Unity Catalog checks the view itself and the tables behind it, and on a SQL warehouse it checks the tables using the view owner’s permissions, not the reader’s. The supply agent queries Databricks through a SQL warehouse (Part 7), so this rule applies.

So Acme’s grants are a design decision, written down rather than assumed:

-- supply owns service_cost and penalty_metrics
GRANT SELECT ON TABLE service_cost      TO `supply`, `finance`;
GRANT SELECT ON penalty_metrics         TO `marketing-analysts`;   -- the metric view
GRANT USE CATALOG ON CATALOG acme       TO `marketing-analysts`;
GRANT USE SCHEMA  ON SCHEMA  acme.supply TO `marketing-analysts`;

(The catalog, schema and group names are illustrative.) Maria is in marketing-analysts; Ian the intern is not. Here is what that means for Maria:

1. Maria (or an agent acting as her) runs SELECT * FROM service_cost.
   Unity Catalog refuses: she has no grant on that table.
2. Maria asks the router: "What did the Acme Pure stockouts in Western Europe cost us?"
   Code routes service_penalty_eur to "delegated" and calls the supply agent.
3. The supply agent queries penalty_metrics as Maria and gets one total back.
4. The answer Maria sees: penalties EUR 36,026.40 over the seven campaign weeks.
   It never contains the per-unit rate or the contract rows that produced the total.

Someone without that grant, such as Ian the intern in Part 10, gets no figure and a refusal instead, because the same query run as Ian fails the SELECT check on penalty_metrics. Code chose the route, and Unity Catalog, not the agent, decided what each person could see.

Two caveats from the same views page. First, the view owner (supply) must keep its own access to service_cost, or nobody can read the view. Second, on dedicated compute (formerly single user compute) on Databricks Runtime 15.3 and below, Unity Catalog checks both the owner’s and the reader’s permissions on the underlying tables. There, Maria would also need SELECT on service_cost, which defeats the point. Keep agent queries for metric views on a SQL warehouse, and test it with a user who has no table grant.

My view: start from delegation for anything with money or contracts in it, and federate only the measures whose owners are happy to publish the formula. It is much easier to open a door later than to close one.

The common mistake is to treat federation as the better route because it is faster. Speed is the last question, not the first. A short test, per measure:

1. Does the asking team own it?                              -> local
2. Has the owner published the formula on a shared table?   -> federated (and you get rows
                                                               to join, not just a total)
3. Is it confidential, or does the owner change the logic,
   or must the user's own grants apply?                      -> delegated
4. None of the above, or the grain does not exist?           -> cannot, and say what is missing

On Acme, media_spend_eur stops at line 1 for marketing, otif_pct at line 2, and service_penalty_eur at line 3. The recipe from the dinner example is the published formula; the secret sauce is the penalty rule.

Where the series goes deeper:

• Part 1 introduces the ownership rule and the system we build.
• Part 3 puts guards between the agents and these tables.
• Part 5 builds the router that applies the rule in code.
• Part 6 shows how one agent delegates to another over A2A.
• Part 7 covers acting as the user, so Databricks applies the asking person’s own permissions.

What can go wrong with business data?

• Averaging ratios. The average of seven weekly CTRs is not the seven-week CTR. Sum the parts, then divide.
• Adding reach across weeks. The same person is counted in every week they were reached.
• Joining across a grain mismatch. Two campaigns in one week double the supply numbers unless you filter or sum the campaigns first.
• Reading correlation as cause. A join shows things that happened together. Say so, and point to the control.
• Copying a formula you do not own. If marketing rebuilt the penalty rule from public information, it would drift from the real contracts the first time a rate changed. Suppose marketing hard-coded EUR 2.40 per unmet unit and supply then renegotiated Western Europe to EUR 2.60: marketing would keep reporting EUR 36,026.40 when the real figure was 15,011 × 2.60 = EUR 39,028.60, and nothing would flag the difference.

Try it

This script needs nothing but Python. The rows are copied from the demo data, and it recomputes OTIF, unmet units and the penalty rule, then joins the two sides on week:

"""Recompute the Acme finding from the raw rows: no files, no libraries."""

# Marketing side: campaign_performance, Acme Pure Refill Launch, Western Europe
# (week_start, media_spend_eur, impressions, clicks)
campaign = [
    ("2026-05-04", 36317.41, 5467637, 67774),
    ("2026-05-11", 41270.37, 9015848, 89983),
    ("2026-05-18", 42049.65, 9669827, 86901),
    ("2026-05-25", 39151.93, 6643613, 74808),
    ("2026-06-01", 43689.47, 6577232, 59790),
    ("2026-06-08", 38158.92, 8768940, 65263),
    ("2026-06-15", 42952.50, 9706969, 83352),
]

# Supply side: supply_service, Acme Pure, Western Europe, two weeks either side
# (week_start, units_ordered, units_delivered)
supply = [
    ("2026-04-20", 13223, 12732),
    ("2026-04-27", 14338, 13691),
    ("2026-05-04", 17475, 15585),
    ("2026-05-11", 16679, 14799),
    ("2026-05-18", 17448, 15160),
    ("2026-05-25", 17218, 15104),
    ("2026-06-01", 16325, 14025),
    ("2026-06-08", 17137, 14982),
    ("2026-06-15", 17299, 14915),
    ("2026-06-22", 13228, 12670),
    ("2026-06-29", 13567, 12985),
]

THRESHOLD = 95.0      # retailer contract: below this weekly OTIF, penalties apply
RATE_WE = 2.40        # EUR per unmet unit in Western Europe (fictional contract)


def otif(ordered: int, delivered: int) -> float:
    """Supply's published formula: delivered over ordered, as a percentage."""
    return 100.0 * delivered / ordered


def penalty(ordered: int, delivered: int) -> float:
    """The contract rule. Only supply knows the real rates; this is the demo's rule."""
    if otif(ordered, delivered) >= THRESHOLD:
        return 0.0
    return (ordered - delivered) * RATE_WE


print("week        ordered  delivered   OTIF%  unmet  penalty EUR")
for week, ordered, delivered in supply:
    print(f"{week}  {ordered:>7,}  {delivered:>9,}  {otif(ordered, delivered):6.2f}"
          f"  {ordered - delivered:>5,}  {penalty(ordered, delivered):>11,.2f}")

# The join: same brand and region on both sides, so week_start is the key left
by_week = {week: (o, d) for week, o, d in supply}
spend = imps = clicks = ordered = delivered = fines = 0
for week, s, i, c in campaign:
    o, d = by_week[week]              # one supply row per week: no double counting
    spend, imps, clicks = spend + s, imps + i, clicks + c
    ordered, delivered = ordered + o, delivered + d
    fines += penalty(o, d)

print()
print(f"Refill Launch spend   EUR {spend:,.2f}")
print(f"CTR {100 * clicks / imps:.2f}%   CPM EUR {1000 * spend / imps:.2f}")
print(f"OTIF {otif(ordered, delivered):.1f}%   unmet units {ordered - delivered:,}")
print(f"penalties (demo rule) EUR {fines:,.2f}")

Save it as acme.py and run python3 acme.py. This is the output:

week        ordered  delivered   OTIF%  unmet  penalty EUR
2026-04-20   13,223     12,732   96.29    491         0.00
2026-04-27   14,338     13,691   95.49    647         0.00
2026-05-04   17,475     15,585   89.18  1,890     4,536.00
2026-05-11   16,679     14,799   88.73  1,880     4,512.00
2026-05-18   17,448     15,160   86.89  2,288     5,491.20
2026-05-25   17,218     15,104   87.72  2,114     5,073.60
2026-06-01   16,325     14,025   85.91  2,300     5,520.00
2026-06-08   17,137     14,982   87.42  2,155     5,172.00
2026-06-15   17,299     14,915   86.22  2,384     5,721.60
2026-06-22   13,228     12,670   95.78    558         0.00
2026-06-29   13,567     12,985   95.71    582         0.00

Refill Launch spend   EUR 283,590.25
CTR 0.95%   CPM EUR 5.08
OTIF 87.4%   unmet units 15,011
penalties (demo rule) EUR 36,026.40

The penalties match the service_penalty_eur column in supply’s confidential table to the cent, which is why the rule is delegated: anyone who knows it can work out the contract rates.

Things to try:

• Change THRESHOLD to 90.0 and see how many weeks still attract a penalty.
• Compute the seven-week CTR by averaging the seven weekly CTRs, and compare it with 0.95%.
• Add a second campaign row for the week of 4 May (Acme Pure Everyday spent EUR 3,506.34) and watch that week’s supply numbers be counted twice if you join without summing first.

What I would do now

1. Write down, for each number your agent will quote, its owner, its grain and its formula, before you write any agent code.
2. Find the join keys between your data sources, and list the columns that exist on one side only.
3. Recompute one headline figure by hand, as in the script above, so you know what the right answer looks like.
4. Mark which measures their owners are willing to publish. Everything else is delegated.
5. Look for a control case in your own data before you trust a finding.

What did we learn?

• Marketing and supply are both right about the Acme Pure Refill Launch: it pushed orders up by about 22%, and OTIF fell to 87.4% with 15,011 units never delivered.
• Ratios such as CTR, CPM and OTIF are computed from totals, never by averaging weekly ratios, and reach is never added across weeks.
• The grain of a table is what one row stands for, and the grain mismatch on campaign_name means you filter or sum campaigns before you join, and never split supply numbers by campaign.
• A join shows that two things happened together; the Verde Spring Refresh control shows that this campaign, in these regions, outran this supply plan.
• For every number, code reads semantic.yaml and picks one route: local, federated, delegated or cannot.
• Federate measures whose owners publish the formula on a shared table, and delegate anything that depends on confidential contracts or must keep the owner’s definition.

What comes next?

Prerequisite 3: How a model works explains the model itself: tokens, the context window, the settings that change its answers, and the jobs this series never gives to a model. Then Production-ready agents, Part 1: What an agent really is, and the system we will build starts the series properly: what a model, a tool and an agent are, the three ADK classes every agent runs on, and the full architecture of the system that answers this question. The ownership rule from this post comes back there as the heart of the design.

Sources

• Google Ads Help: clickthrough rate (CTR)
• Google Ads Help: cost-per-thousand impressions (CPM)
• Google Ads Help: impressions
• Google Ads Help: measuring reach and frequency
• McKinsey: Defining “on-time, in-full” in the consumer sector
• Fill rate and Cost to serve (Wikipedia)
• Trade promotion management (Wikipedia)
• Apache Iceberg
• BigQuery: Apache Iceberg managed tables
• Databricks: read Delta tables with Iceberg clients (UniForm)
• Delta Lake: Universal Format (UniForm)
• Databricks: Unity Catalog
• Databricks: Unity Catalog metric views
• Databricks: manage metric views (permissions)
• Databricks: what is a view? (querying views and owner permissions)
• Databricks: Lakehouse Federation

Opinions are my own. Every fact in this post comes from the public documentation linked above. Acme Consumer Brands and all of its data are fictional: the figures come from a generated demo dataset, and the contract rates are invented for teaching.