Import
dbt logo

Jaffle Shop, worked: what orders.status means

dbt Labs' sample project, with its five-value status column annotated so an agent looks it up instead of guessing. Every field is on the page.

Start with this recipe →

No warehouse credential. No dbt Cloud token. Read-only.

For agents, no sign-in: /recipe/jaffle-shop-status.json · /recipe/jaffle-shop-status.md

A worked instance of the agent-grounding recipe on jaffle-shop-classic, the dbt Labs sample project most analytics engineers run first. Its docs tell a human what the five orders.status values mean. They do not tell an agent that paid and cancelled do not exist, that shipped is not revenue, or that stg_payments has no status column at all. This recipe fills exactly that layer (meaningNote, commonMistake, doNotUseFor, permitted values and an owner), then shows the SQL an agent writes before and after it can read them. Import the same manifest into your own workspace and fill the same fields.

What you do

  1. Run dbt parse on jaffle-shop-classic and import target/manifest.json; each accepted_values list, orders.status included, becomes a governed vocabulary.
  2. Fill meaningNote, commonMistake and doNotUseFor on the columns an agent gets wrong. The annotated set is below.
  3. Point your assistant at the read-only endpoint and ask: what was completed revenue last month?

The worked example: Jaffle Shop · orders.status

Built on jaffle-shop-classic, dbt Labs' sample project (Apache-2.0). Four models:

  • customers (mart): One row per customer, with derived facts from that customer's orders and payments.
  • orders (mart): One row per order, with payment amounts by method. Carries status.
  • stg_orders (staging): Cleaned orders from the raw_orders seed. Carries the same status column and the same accepted_values test.
  • stg_payments (staging): Cleaned payments from the raw_payments seed: payment_method and amount. No status column.

The question

What was completed revenue last month?

A text-to-SQL assistant that cannot look the column up guesses a status list, and the guess is usually some mix of shipped, completed and paid.

Before: the agent guesses

select sum(amount) as revenue
from orders
where order_date >= date_trunc('month', current_date - interval '1 month')
  and order_date < date_trunc('month', current_date)
  and status in ('shipped', 'completed', 'paid');
  • paid is not a permitted value. It matches no rows, so the query runs cleanly and nobody learns the guess was wrong.
  • shipped is not revenue: the customer has not received the goods.
  • The currency is unstated. The column is AUD.

After: the agent looks it up

select sum(amount) as completed_revenue_aud
from orders
where order_date >= date_trunc('month', current_date - interval '1 month')
  and order_date < date_trunc('month', current_date)
  and status = 'completed';

orders.status has five permitted values and no paid. Its doNotUseFor says revenue means filtering status = 'completed'. orders.amount says the unit is AUD.

orders.status, fully annotated

Permitted values (accepted_values on orders.status and stg_orders.status; descriptions from the orders_status docs block in models/docs.md)
ValueMeaning
placedThe order has been placed but has not yet left the warehouse.
shippedThe order has been shipped to the customer and is currently in transit.
completedThe order has been received by the customer.
return_pendingThe customer has indicated that they would like to return the order, but it has not yet been received at the warehouse.
returnedThe order has been returned by the customer and received at the warehouse.
definition
Fulfillment state of a single order. Five values, mutually exclusive. The values and their descriptions come from the orders_status docs block in jaffle-shop-classic.
permittedValues
placed · shipped · completed · return_pending · returned
meaningNote
This is the fulfillment state of one order, not a customer lifecycle flag and not a payment result. Only completed means the customer received the goods. placed and shipped are open. return_pending and returned come after the fact: they are not kinds of open and not kinds of successful. There is no cancelled, pending, paid, active or failed value in this column.
commonMistake
Inventing values the column does not have (pending, cancelled, active, paid, success). Treating shipped plus completed as successful orders. Reading status as a property of the customer rather than the order. Looking for a status on payments: stg_payments has none in this project.
doNotUseFor
Revenue on its own (filter amount where status = 'completed'). Active-customer counts. Payment success or failure. Inventory on hand. Any query that IN-lists values you did not look up here.
agentGuidance
Before writing SQL against orders.status, read permittedValues. If the question says revenue, completed or sold, filter status = 'completed'. If it says open or in flight, use placed or shipped. If it says returns, use return_pending or returned. Never invent a sixth value.
owner
Jordan Hale (Finance lead · example person)
steward
Maya Chen (Analytics engineer · example person)
source
models/schema.yml · accepted_values · docs block orders_status

The other columns an agent gets wrong

stg_orders.status

definition
The staging copy of the same field. Same vocabulary as orders.status.
permittedValues
placed · shipped · completed · return_pending · returned
meaningNote
Identical permitted values to orders.status. Prefer the mart column in analyst questions unless the question is about the staging model itself.
commonMistake
Declaring a different accepted_values list here than on orders.status. Both tests can pass. That is how two definitions of one column survive in a dbt project.
doNotUseFor
A second, private meaning of status. If staging and mart disagree, that is drift, not a new definition.
owner
Maya Chen (Analytics engineer · example person)
source
models/staging/schema.yml · accepted_values

orders.amount

definition
Total amount of the order in Australian dollars: the sum of the credit_card, coupon, bank_transfer and gift_card payment amounts.
unit
AUD
meaningNote
Present on every order whatever its status. It is not revenue. Completed revenue is sum(amount) where status = 'completed'. The raw_payments seed stores cents; by the time it reaches this column it is dollars.
commonMistake
sum(amount) across all statuses, labelled revenue. Assuming USD. Adding raw_payments.amount (cents) to it. Treating coupon_amount as a discount rather than a payment method.
doNotUseFor
Completed revenue without a status filter. USD reporting. Margin: there is no cost column in this project.
owner
Jordan Hale (Finance lead · example person)
source
models/schema.yml

customers.customer_lifetime_value

definition
Lifetime sum of payments for this customer's orders, all statuses. customers.sql emits this column as customer_lifetime_value; models/schema.yml documents the same figure under the name total_order_amount, which the model does not produce.
unit
AUD
meaningNote
Lifetime gross payments, not completed-only value and not current-period revenue, whatever the name suggests.
commonMistake
Querying total_order_amount: it is documented in schema.yml but is not a column of the built table. Calling this figure what the customer paid and kept: it includes placed, shipped, return_pending and returned orders.
doNotUseFor
Period revenue. Completed-only customer value. Churn or active-customer definitions.
owner
Jordan Hale (Finance lead · example person)
source
models/customers.sql

customers.number_of_orders

definition
Count of the orders this customer has placed, all statuses.
meaningNote
Includes return_pending and returned orders. A customer with one placed order and one returned order has number_of_orders = 2.
commonMistake
Using it as a count of completed purchases, or as a proxy for active.
doNotUseFor
Completed-order counts. Active-customer flags.
owner
Maya Chen (Analytics engineer · example person)
source
models/schema.yml

customers.first_name

definition
Customer's first name. Marked PII in the upstream schema.yml.
pii
true
meaningNote
Personal data. The seed values are fictional, which is the only reason this example shows the column at all.
commonMistake
Echoing names into agent logs, eval traces or answers as if they were a dimension.
doNotUseFor
Joins, aggregations, or anything an agent might print. Use customer_id.
owner
Maya Chen (Analytics engineer · example person)
source
models/schema.yml

customers.last_name

definition
Customer's last name. Marked PII in the upstream schema.yml.
pii
true
meaningNote
Personal data. The seed values are fictional.
commonMistake
Same as first_name.
doNotUseFor
Joins, aggregations, or anything an agent might print. Use customer_id.
owner
Maya Chen (Analytics engineer · example person)
source
models/schema.yml

stg_payments.payment_method

definition
How this payment was tendered. One row per payment; an order can have several.
permittedValues
credit_card · coupon · bank_transfer · gift_card
meaningNote
coupon is a payment method in this project, not a discount. There is no payment status column in jaffle-shop-classic.
commonMistake
Inventing cash or paypal. Treating coupon as a markdown against amount. Looking for a payments status column: it does not exist here.
doNotUseFor
Order fulfillment state (that is orders.status). Discount analysis. A second status vocabulary.
owner
Maya Chen (Analytics engineer · example person)
source
models/staging/schema.yml · accepted_values

stg_payments.amount

definition
Payment amount in dollars. The raw_payments seed stores cents; stg_payments.sql divides by 100.
unit
AUD
meaningNote
Already converted: this column and orders.amount are both dollars. Only the raw_payments seed is in cents.
commonMistake
Dividing by 100 a second time. Reading the raw_payments seed directly and summing cents as dollars.
doNotUseFor
Revenue on its own: join to orders and filter status = 'completed'. Mixing with raw_payments.amount without converting.
owner
Maya Chen (Analytics engineer · example person)
source
models/staging/stg_payments.sql

Point your agent at it

Import the manifest into your own workspace, fill the fields, then connect your assistant to the read-only MCP endpoint. There is no public demo project to connect to; your agent reads your copy.

MCP client config (mcp.json)

{
  "mcpServers": {
    "coremodels": {
      "url": "https://go.coremodels.io/mcp",
      "headers": {
        "Authorization": "Bearer <your CoreModels API key>"
      }
    }
  }
}

Claude Code

claude mcp add --transport http coremodels https://go.coremodels.io/mcp

Setup for Cursor and other clients, as documented: MCP Server. Then ask it: What was completed revenue last month?

Attribution

Jaffle Shop is dbt Labs' sample project jaffle-shop-classic, licensed Apache-2.0. The models, the columns, the accepted_values lists and the five status descriptions are theirs (models/schema.yml, models/docs.md). The annotations on this page (meaningNote, commonMistake, doNotUseFor, owners) are CoreModels example content, not part of the upstream project. schema.yml · docs.md · Apache-2.0 license

Maya Chen and Jordan Hale are example people, invented for this recipe. They are not customers or staff.

Guides for this recipe

← All dbt recipesStart with this recipe →