Gaur Docs

A contract over 5 sources that collapses a BOM fan-out and exposes a derived ratio measure.

Use case

We want per-SKU performance for product managers and finance: units sold, revenue, ingredient and packaging cost, and gross-margin ratio, sliceable by product, category, store, and time. The complication is that the bill-of-materials (raw_supplies) has multiple rows per sku, so joining it directly into a contract fans out by 5-7x and inflates every cost measure.

Data modeling

Collapse the fan-out in a model first. sku_costs aggregates supplies to one row per sku, splitting cost by the perishable boolean (not by string-matching supply names):

models/sku_costs.sql

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.

The contract joins raw_items (the fact) to four dimension models, each many_to_one on its declared primary key:

contracts/product_performance.json

{
  "name": "product_performance",
  "sources": [
    { "source_type": "table", "name": "raw_items",       "alias": "i" },
    { "source_type": "model", "name": "orders_clean",    "alias": "o",
      "join_type": "inner", "cardinality": "many_to_one",
      "on": "i.order_id = o.order_id" },
    { "source_type": "model", "name": "products_clean",  "alias": "p",
      "join_type": "inner", "cardinality": "many_to_one",
      "on": "i.sku = p.sku" },
    { "source_type": "model", "name": "sku_costs",       "alias": "sc",
      "join_type": "left",  "cardinality": "many_to_one",
      "on": "i.sku = sc.sku" },
    { "source_type": "model", "name": "stores_enriched", "alias": "s",
      "join_type": "left",  "cardinality": "many_to_one",
      "on": "o.store_id = s.store_id" }
  ],
  "dimensions": {
    "sku":          { "sql": "i.sku",          "type": "string" },
    "product_name": { "sql": "p.product_name", "type": "string" },
    "category":     { "sql": "p.category",     "type": "string" },
    "store_name":   { "sql": "s.store_name",   "type": "string" },
    "ordered_at":   { "sql": "o.ordered_at",   "type": "timestamp",
                      "semantic_type": "time",
                      "time_grains": ["day","week","month","quarter","year"] }
  },
  "measures": {
    "units_sold":     { "sql": "count(*)",                           "type": "number", "additivity": "additive" },
    "revenue_usd":    { "sql": "sum(p.price_usd)",                   "type": "number", "additivity": "additive" },
    "total_cogs_usd": { "sql": "sum(coalesce(sc.total_cogs_usd,0))", "type": "number", "additivity": "additive" },
    "cogs_ratio":     { "behavior": "derived", "type": "number",
                        "numerator": "total_cogs_usd",
                        "denominator": "revenue_usd" }
  }
}

Query

{
  "contract_name": "product_performance",
  "query": {
    "dimensions": [
      { "name": "category" },
      { "name": "ordered_at", "time_grain": "month" }
    ],
    "measures": ["revenue_usd", "total_cogs_usd", "cogs_ratio"],
    "sorts":    [{ "field": "ordered_at", "direction": "asc" }]
  }
}

Result

{
  "data": {
    "pagination": {
      "items": [
        { "category": "jaffle",   "ordered_at": "2026-04-01T00:00:00", "revenue_usd": 184220.50, "total_cogs_usd": 78215.10, "cogs_ratio": 0.4246 },
        { "category": "beverage", "ordered_at": "2026-04-01T00:00:00", "revenue_usd":  91040.25, "total_cogs_usd": 16498.32, "cogs_ratio": 0.1812 }
      ]
    }
  }
}

Notes

Gross margin in USD (revenue_usd - total_cogs_usd) and gross margin % (1 - cogs_ratio) aren't measures of this contract: derived measures only express ratios, not differences. Compute either in the caller from the two component measures.

Source code

The contract and the four models ship in the Jaffle Shop sample dataset:

contracts/product_performance.json
models/sku_costs.sql, models/orders_clean.sql, models/products_clean.sql, models/stores_enriched.sql

See Rules and limitations for the join-key and single-fact rules this contract satisfies.

Multi-source contract