Malloy Model As You Go

<!--
Copyright (c) Credible Data Inc.
SPDX-License-Identifier: MIT
-->

Model as you go

An analysis that lives in a chat transcript is not reproducible. The numbers were right, and six weeks later nobody can say what "revenue" excluded, which of four timestamps was the order date, or whether the last period was complete. The work is unauditable, so it gets redone.

The fix is to write the assumptions down while the query is still in front of you, in the most durable place your session can write. Answer the question, codify what the answer assumed, answer the next one. After a handful of questions there is a model, or a notebook, where every definition exists because a real question needed it, and every judgment call is on the record.

This skill is the codify step. skill:malloy-analysis answers the question; this skill decides what to write down afterwards, and where.

Tool names are bare here - get_context, execute_query, search_database_schema. The exact prefixed name depends on the host; match against the tools you actually have.

The loop

QUESTION → ANSWER → CODIFY  ⟲
              ↑___________|

The user gets a real answer on question one. If three tool calls have gone by without producing an insight they can read, you have drifted into modelling for its own sake. Stop and answer something.

1. Answer the question, with skill:malloy-analysis

This skill starts when someone asks a data question. That question is the unit of work, and it is theirs. Do not widen it into a modelling project, and do not swap it for a more interesting one you found on the way.

A broad ask is still an ask. "Analyse the sales data" is a question whose subject is given and whose metric is not. Pick the most obvious question about that subject, say in one line which one you picked, and answer it. Never open with a row-count table and a menu of "analytical domains"; the menu is worth less than the first real answer would have been.

Load skill:malloy-analysis and follow it: discover the model, construct the query, run it, verify it, present it. Two of its rules matter most here:

• Every number you present comes out of a query. Adding up the rows of a limit: 15 table by hand drops everything below the cut, and nobody can re-run it.
• Your first result is a draft. Work step 5 of skill:malloy-analysis (verify before trusting) before presenting.

Name the decisions the answer rests on

This is what makes the analysis auditable, and it is the step most easily skipped.

Almost every query needs at least one judgment call the data cannot settle. Which of four timestamps is "the order date". Whether returns count as revenue. Whether to count lines or orders. Whether a partial final period belongs in a trend.

A judgment call made silently becomes a hidden assumption. It will be wrong for someone eventually, and by then nobody remembers it was a choice rather than a fact. So make the call, run with it, and say what you chose and what you rejected, with the other number attached whenever it is cheap to get:

Revenue here excludes cancelled and returned orders - that's $8.10M. Counting returns as revenue and netting them separately gives $9.18M. I went with the stricter one; say if your reporting does it the other way.

Not every choice rises to this. Raise it when any of these holds:

Otherwise state it in a clause and move on. Asking about everything is as bad as asking about nothing; it turns the loop into a form and trains the reader to skim past it. And give the number under each option, not an abstract question: "$8.10M excluding them, $9.18M including" lets the user answer in one word.

2. When there is no model yet

If the tables have no model, define only enough to run the first query. One line is normal:

source: order_items is my_conn.table('ecommerce.order_items')

Read the columns with run: source -> { select: * limit: 3 }. Prefer it to a schema listing: it returns the columns and real values, and the values are what catch the surprises a column list hides - a date stored as a string, a total that excludes tax, a metric column that is null on every row.

If a schema tool returns something you cannot explain (no columns, or no tables for a filter you can see matches), that is a bug in the tool. Report it. Do not write the workaround into your model or your notes as though it were a property of the data.

No primary_key:, no dimensions, no measures, no joins, not yet. Those arrive in CODIFY, each one paid for by a question that needed it. Adding fields because the table has them is the habit this skill exists to break.

Add a join only when this question cannot be answered without it, then verify its cardinality before trusting any aggregate (group_by: fk, aggregate: n is count(), having: n > 1).

If a model already exists, ground yourself in it with get_context and reuse what is there.

3. CODIFY: write the answer's assumptions down

Run this after every answered question, while the query is still in front of you. Skipping it is how a session ends with a transcript and nothing else.

Where it goes depends on what you can write

The same assumption lands in a different place depending on the session. Pick the highest rung your tools allow, and never skip codifying because the top rung is out of reach.

Tell the rungs apart by the tools you have, not by guessing at the user's role: a file-write or compile tool means the top rung; a report or notebook tool without model edits means the middle one; neither means the bottom.

What to codify

Look at the query you just ran. For each dimension, measure, join, filter, and for the shape of the query itself, ask in this order:

The first row is the one that matters. A definition is worth keeping less because it saves typing than because it stops the next reader misreading the number.

Codify thin, and leave the ad-hoc behind: filters tied to one finding, calculations that answered exactly one question. A question that needed one measure codifies one measure, not the neighbouring columns as well. An empty CODIFY is a fine outcome; say so and move on.

Then say what you did, in one line, every time:

Codified: revenue is sum(sale_price) excluding cancelled and returned, category via the products join. Left ad-hoc: the where: created_at > @2023 - just this question's window.

That line shows the model growing and gives the reader a place to object. Never codify silently.