Models

A model is a reusable object defined by SQL on top of your tables and other models. Where a table is raw, a model is shaped. Models are how you clean, rename, conform, and pre-aggregate data so the contracts built on them stay simple, correct, and DRY.

When to use a model

Reach for a model whenever the data isn't already in the shape a contract needs. Common cases:

• Cleaning. Convert types, standardise units, drop bad rows.
• Reshaping. Rename columns, derive new ones, restructure a dataset.
• Conforming. Align two datasets onto a shared grain and key.
• Pre-aggregating. Roll a fine-grained table up to a coarser grain (often to collapse a fan-out before a contract joins it).
• Multi-fact remediation. Combine more than one fact into a single conformed dataset so a downstream contract stays single-fact.

If the data is already clean and contract-ready, a contract can read a table directly without a model in between.

How a model is defined

A model is created from a SELECT over existing tables and models. You also declare a primary key: the column or columns that uniquely identify a row in the result. Gaur runs the SQL, infers the resulting schema, verifies the primary key against the data, and registers the model as a reusable object.

A model carries a state:

Only an Active model can be used as a contract source.

Jaffle Shop's models

The Jaffle Shop sample dataset ships with five models, each illustrating a different reason to write one.

orders_clean: unit conversion and derived columns (1:1 reshape)

raw_orders stores money as integer cents. Every downstream contract would otherwise repeat the /100.0 conversion in every measure. The model does it once.

-- orders_clean
SELECT
  o.id                                AS order_id,
  o.customer                          AS customer_id,
  o.store_id,
  o.ordered_at,
  CAST(o.ordered_at AS DATE)          AS ordered_date,
  date_trunc('month', o.ordered_at)   AS ordered_month,
  EXTRACT(HOUR FROM o.ordered_at)     AS ordered_hour,
  EXTRACT(DOW  FROM o.ordered_at)     AS ordered_dow,
  CASE WHEN EXTRACT(DOW FROM o.ordered_at) IN (0,6) THEN TRUE ELSE FALSE END
                                      AS is_weekend,
  o.subtotal     / 100.0              AS subtotal_usd,
  o.tax_paid     / 100.0              AS tax_paid_usd,
  o.order_total  / 100.0              AS order_total_usd
FROM raw_orders o

Primary key: order_id. Used by every contract that touches order data.

sku_costs: collapsing a fan-out

raw_supplies is a bill-of-materials. Its natural key is (id, sku), not id alone, because each supply (like "napkin") appears in many rows, one per product it's used in. Joining items to raw_supplies on sku fans out by 5-7x. Every measure on top of that join is silently inflated.

The fix: pre-aggregate to one row per sku, then contracts join many-to-one and there's no fan-out left to multiply.

-- sku_costs
SELECT
  sku,
  SUM(CASE WHEN perishable     THEN cost ELSE 0 END) / 100.0
      AS ingredient_cost_usd,
  SUM(CASE WHEN NOT perishable THEN cost ELSE 0 END) / 100.0
      AS packaging_cost_usd,
  SUM(cost) / 100.0
      AS total_cogs_usd
FROM raw_supplies
GROUP BY sku

Primary key: sku. product_performance joins to it many-to-one, trusting that one SKU produces exactly one cost row.

The other three

• products_clean: renames type to category, converts price cents to USD. 1:1 with raw_products. PK sku.
• stores_enriched: adds days_since_open, is_mature, tax_rate_pct, opened_month. 1:1 with raw_stores. PK store_id.
• customer_lifetime: one row per customer with first_order_at, cohort_month, lifetime_orders, lifetime_revenue_usd. Used by the cohort/LTV contract. PK customer_id.

Models in contracts

A contract uses a model exactly as it uses a table, by listing it as a source with source_type set to model:

{ "source_type": "model", "name": "orders_clean", "alias": "o" }

If the contract joins the model many_to_one or one_to_one, the join's on clause has to reference the model's primary key. Same rule as for tables. See the join-key rule.

Models and multi-fact analysis

Models are how you build contracts that span more than one fact (more than one set of measurable events).

A contract compiles to a single combined query, so joining two independent fact datasets inside one contract is rejected (the chasm rule). The fix is the model-first pattern:

Do the multi-fact work once, deliberately, in a model. Then govern a simple contract on top.

Two practical rules when writing model SQL

Prefer LEFT JOIN over INNER for fact-to-lookup joins. If a fact row references a lookup that's missing (a customer that's been deleted, a store that's been retired), INNER silently drops the fact row. The contract then under-counts revenue without raising an error. LEFT keeps the fact row and surfaces missing lookups as nulls, which the contract or caller can handle visibly.

If a downstream contract will RLS over the model, SELECT the tenant column out. RLS conditions reference columns the contract can see, and the contract can only see columns the model exposes. A model that uses store_id internally but doesn't project it can't be scoped per store downstream.

Limitations

• A model is only as correct as its SQL. Gaur validates the shape of a contract, but model SQL expresses your intent directly. If it computes the wrong thing, the model returns the wrong thing. There's no semantic guard inside a model. Review model SQL carefully and verify it with exploration before building contracts on top.
• Models must be Active to be used. A Failed model can't be a contract source until its SQL is fixed.
• Models aren't consumable on their own. Applications query contracts, never models directly.
• Reuse before you duplicate. A near-identical second model drifts away from the first. Extend the existing one instead.
• Models depend on their sources. A model is defined over specific tables and models; changes there can affect it.

Next