How an agent builds a semantic model and shows its work

In the last post we handed an agent a Malloy model and asked it questions. That worked because somebody had already done the hard part: deciding what the data means.

This post is about building that model.

A good semantic model isn't a schema with nicer names. Someone has to work out what one row represents, prove which joins are safe, decide what "revenue" and "customer" actually mean, and write those decisions down so everyone downstream gets the same answer.

That judgment starts with the people who know the business. Getting it into a model has always meant handing it to a data modeler — someone with the technical skill to encode what the business expert knows. It requires two people: one who knows the answers, and one who can write them down.

The open-source Malloy modeling skills in Malloy Publisher write the modeler's half down, in enough detail that an agent can follow it. They were written by Talal Assir, who has spent his career building semantic models on enterprise analytics platforms. If you know your business, you can now build, update, and maintain your own model, with a coding agent doing the encoding.

Teaching an agent Malloy syntax isn't really the problem. Models are already good at that, and a compiler catches them when they aren't. The harder part is teaching it what not to guess: query the data before trusting a column name, prove cardinality before declaring a join, flag the business definitions the data can't settle, and write those choices into the model.

The workflow below is what that looks like in practice. We ran it against the public Olist ecommerce data set.

The Workflow

malloy-modeling is the orchestrator. It runs the modeling process in eight phases: first working out what is actually in the data, and eventually deciding what the model should expose.

The proposals all come before the builds. That is why sources appear twice: step 3 decides which tables to model and how they fit together, and step 5 writes the files. The gap is deliberate, because a plan is cheap to change and files are not. Changing your mind at step 3 means editing a sentence. Changing it after step 5 means rewriting every documented source file and every join built on them.

The colors mark who does the work. In the white phases the agent works through the data itself: sampling values, checking whether a key is really a key, proving cardinality before it trusts a join. Every data set has quirks — the column named like an identifier that isn't one, the status nobody documented, the table that silently drops rows on a join. You don't have to arrive knowing them; the agent finds them by querying.

The gold phases are the ones the data can't answer for itself: what this model is for, what revenue and customer mean here, what gets exposed. Those need someone who knows the business, and no amount of querying substitutes for them. Skipping one doesn't make the decision go away: the agent takes its best shot, tells you it did, and writes the assumption into the model where the next person will see it.

The pauses are the point, because these are meant to be governed models. Governed means every definition in the model traces to a decision someone accountable made — or to a flagged assumption still waiting on that person — and the model itself controls what it exposes. A model can compile, reconcile, and still be ungoverned; what governs it is the expert's judgment, recorded where every query has to pass through it. That is the trade: you bring the business context, and the agent finds out what the data actually does.

If you have modeled data before, you will recognize every part of this. Settling the grain before anything else is Kimball's oldest rule. Proving keys and joins before trusting them is ordinary data profiling. Base sources feeding joined sources is the layering dbt teams build as staging and marts. Putting definitions in the query path is the argument for a semantic layer, and a plan reviewed before the build is how modeling teams already work. The method isn't new — the skills' contribution is writing it down in enough detail that an agent follows it on every table, every time.

This is the schema-first path: start from the data and work toward a model. You can also come at it from the other direction, answering questions first, then moving the joins, measures, and definitions worth keeping into the model. We'll cover that path in detail in a later post.

What the Data Actually Contains

This is the part a data modeler spends most of their time on, and enjoys least. Before you can decide anything, you have to find out what you are looking at: read the schema, guess which tables matter, then start querying to check whether the names mean what they say. Count the rows. See if the key is unique. Join two tables and find out whether the total moved. It is slow, it is unglamorous, and none of it requires knowing anything about the business — it is pure toil, and it is exactly the part the skills hand to the agent.

So the agent does it, with one blunt rule: never assume from a column name. Against a real warehouse it starts by narrowing the field, because there are too many tables to query blindly — Publisher searches the schema in plain English and ranks the ones that look relevant. That step only reads names and types — enough to decide where to look. The Olist ecommerce data set is a handful of tables, so the agent skipped straight to the queries.

It counted rows: 99,441 orders, 112,650 line items, 103,886 payments. Then it went after grain. Orders came back clean. Line items did not.

order_item_id is named like an identifier, and it isn't one: all 112,650 rows share just 21 distinct values, because it's a line number within an order. Treat it as a key and every total that crosses a join comes out wrong. The real grain is the order plus that line number, established by checking for duplicates rather than by reading the name.

Proving the Joins

Joins have the same problem as column names: they can look reasonable and still be wrong. So the skill makes the agent prove three things with data — is the key unique where you think it is, do the values on both sides actually match, and does anything fail to join?

Payments failed the first test. An order isn't paid for exactly once: a shopper can split the bill across two cards, or cover part of it with a voucher. Line items work the same way, one row per unit bought. So an order has many items and many payments. The other two checks were less dramatic: every line item found a product, every order found a customer, and 775 orders had no line items at all.

That's where totals go wrong. Join an order with three items and two payments to both tables at once and you get six rows back, not one: every item paired with every payment. Each item's price is now sitting on two of those rows, so adding the column up counts every price twice.

Malloy doesn't have that problem. Its aggregates know which table each value came from, so line-item revenue comes back at 13,591,644 reais no matter what else the query joined in. Take the same join into plain SQL and sum the column and you get 14,209,250, which is 4.5% high. Payments come out 27.9% high.

Both queries run. Both answers look like money. The agent took neither on trust: discovery is where it proved the grain, proved the join cardinality, and then checked that the totals reconciled.

Recording the Problems

Discovery also records the problems it finds as it goes, because every one becomes a modeling decision later. Those 775 item-less orders are one example. The delivery timestamps are another — null on any order that never reached the customer, so a naive delivery-time average silently drops the worst cases. And the Portuguese product_category_name is blank on 1,603 line items, a gap the model has to decide how to fill rather than pass through. None of this is in a data dictionary. It only shows up if something queries for it.

The Olist ecommerce data set is tidy by a real warehouse's standards. A warehouse is where this phase gets genuinely tedious. A column that holds JSON, so the shape has to be sampled and the keys read out rather than inferred from a type. A timestamp that changed format the year the pipeline was rewritten, so a date filter silently drops everything before the cutover. A status field carrying four spellings of the same state, because four systems wrote to it. A nullable foreign key that is null often enough to change which join is correct. Every one is found the same way: query it, look at what comes back, write down what is true.

What the Data Can't Settle

Not every question discovery raises can be answered with a query. Two came up here.

What counts as revenue? Four totals, each defensible:

An 18.6% spread. Shipping alone is worth 16.6% of merchandise, so the small-sounding question "does freight count?" moves the headline number by millions.

Who is a customer? The customers file has two identifier columns, and nothing but their names tells them apart. Keyed on customer_id (one per order), this marketplace has 99,441 customers and a repeat rate of exactly zero. Keyed on customer_unique_id it has 96,096 people, 2,997 of whom came back: a 3.1% repeat rate. Both interpretations are defensible, but they tell you very different things about the business.

Nobody was there to answer, so the agent did what the workflow requires when a decision goes unanswered: it took a position on each, wrote it down, and flagged it for review. Revenue is merchandise excluding cancelled and unavailable orders, with shipping broken out so it can be added back. A customer is a person. And a third that is pure convention, labeled as such:

True when this person ordered within the 180 days before the data ends. This is the working definition of an active customer; it is a choice, not something the data settles.

That's the difference between an assumption you can audit and one you inherit.

Where the Judgment Gets Encoded

Every decision from the last section now has to be written where a query can't go around it. For the Olist ecommerce data set, the model comes out in two layers. Base sources wrap one table each and declare only what is true of that table alone. Anything whose definition spans more than one table goes in a joined source.

source: order_analysis is orders extend {
  join_one: customers with customer_id
  join_many: order_items on order_id = order_items.order_id

  measure:
    #(doc) Merchandise only, excluding cancelled and unavailable
    # currency
    net_product_revenue is order_items.price.sum() {
      where: counts_as_sale
    }
}

net_product_revenue is the reason the split exists. The money comes from order_items and counts_as_sale comes from orders, so neither table can own it.

That shape isn't fixed. A star or snowflake warehouse works well with those two layers. A normalized application database often needs a third: base sources for the raw entities, modeled sources where each relationship is defined once, then query sources built around the grain people actually analyze. Working out which shape the data has is part of discovery too.

Every field ships with a #(doc) string, and those aren't just comments for whoever opens the file next. They're written to be searched. Publisher indexes them alongside the field names, so when an agent calls getContext with a plain-English question before writing a query, it matches on the documentation as much as on the names. That's how a question about revenue lands on the measure whose docs say it's the headline figure, rather than one of the three other revenue totals this model also holds.

Deciding What to Expose

One decision is left once every field works, and it is the other half of governance: what the model offers, and what it keeps internal.

Discovery logged the raw Portuguese product_category_name blank on 1,603 line items, and the model had to do something about it. A category dimension resolves it to English and fills the gaps, so the raw column goes internal: — still read by the model, no longer one of the options. The package manifest does the same job a level up, naming the two analytical domains as the entry points rather than the nine files behind them.

Starting From What You Already Have

You don't have to start from raw tables. If your team has modeled this data before, those decisions were governance too — someone who knew the business settled what a metric meant, and it stuck. The agent reads that as prior art rather than restarting the argument. malloy-lookml-review handles LookML, and is explicit that this is not a blind conversion. Where live data is available those inherited decisions still get tested, and the workflow reports what carried over structurally and how the numbers compare against the model you already have. The migration guide has the mechanics.

Definitions are scattered across plenty of other places too. The metrics doc, the one dashboard everyone actually trusts, a catalog export, a README nobody has opened in a year: all of it is evidence of decisions your team has already made. The agent can work from that evidence instead of rediscovering every definition from scratch.

Getting that evidence in front of the agent is mostly logistics. Where a system speaks MCP or has an API, connect it and the agent reads the source directly. Where it doesn't, put copies in the project directory — export the metrics doc, screenshot the dashboard, drop in the catalog file — and tell the agent what you added. The skills fold it into the workflow from there: the definitions show up in the proposals, and wherever live data can check them, they get tested like any other assumption.

The important part is getting those definitions into the model. A metrics doc can go stale for months before anyone notices, because nothing depends on it being right. The model is the opposite: every answer depends on it.

Building the first model is only the start. In a later post we'll look at how to evaluate and evolve one against the questions people actually ask.

Try It

One command writes a package around a data file, a Publisher config, the agent wiring, and the skills:

# a new directory: the config lands here, the package in ./shop
mkdir my-model && cd my-model

# writes the package, server config, .mcp.json and the skills
# keep @latest, and the extra -- so npm forwards --data
npm create @malloy-publisher/malloy-package@latest shop \
  -- --data ~/orders.csv

# serves the package and watches for model edits
npm start

--data takes one file. Copy the rest into shop/data/ — the scaffolder tells you which siblings it spotted — and the agent picks them up as sources; there's no connection to configure.

Start your agent there and say "model my data". Lead with a question instead and you'll get exploration, with an offer to formalize afterwards.

It walks you through what it finds and stops to ask about the decisions the data can't make. Anything you don't answer comes back as an assumption — written into the model with a doc string saying so, like the active-customer definition above.

The skills are just files. .claude/skills/ holds them, so you can read what your agent follows, rewrite what's wrong for your business, and commit the result. On an agent that doesn't read skill files, Publisher serves the same skill content over MCP.

• Browse the skills: the skills/ directory in malloydata/publisher
• Install without cloning: @malloy-publisher/skills on npm
• Share what you built, and join the community: Malloy community Slack

Four defensible revenue totals isn't a data problem you can query your way out of. It's a governance question: somebody who knows the business has to answer it once, in a place the next person will find it. The skills exist to make sure it gets asked.