Gaur Docs

Combine revenue and supplies cost into a single contract using the model-first pattern.

Use case

Finance wants daily revenue and daily supplies cost in one contract so they can chart gross margin over time. The two are independent facts (orders are per-ticket, supplies cost is per-line-item) so combining them inside a single contract would either fan out (the chasm rule rejects this) or be silently wrong.

The fix is the model-first pattern: aggregate each fact to a shared grain (day), join in a model, then build a single-source contract on the result.

Data modeling

The model aggregates orders to day-grain revenue and items+supplies to day-grain cost, then joins on ordered_date:

models/daily_orders_and_costs.sql

WITH revenue_per_day AS (
  SELECT
    ordered_date,
    SUM(subtotal_usd) AS revenue_usd
  FROM orders_clean
  GROUP BY ordered_date
),
cogs_per_day AS (
  SELECT
    CAST(o.ordered_at AS DATE)            AS ordered_date,
    SUM(COALESCE(sc.total_cogs_usd, 0))   AS cogs_usd
  FROM raw_items i
  INNER JOIN orders_clean o ON i.order_id = o.order_id
  LEFT  JOIN sku_costs    sc ON i.sku      = sc.sku
  GROUP BY 1
)
SELECT
  COALESCE(r.ordered_date, c.ordered_date) AS ordered_date,
  COALESCE(r.revenue_usd, 0)               AS revenue_usd,
  COALESCE(c.cogs_usd,    0)               AS cogs_usd
FROM revenue_per_day r
FULL OUTER JOIN cogs_per_day c USING (ordered_date)

Primary key ordered_date. Each fact is aggregated to one row per day before the join, so the cross-product chasm was warning about can't happen.

The contract over the model is then trivially single-fact:

contracts/daily_revenue_and_cost.json

{
  "name": "daily_revenue_and_cost",
  "sources": [
    { "source_type": "model", "name": "daily_orders_and_costs", "alias": "d" }
  ],
  "dimensions": {
    "ordered_date": {
      "sql": "d.ordered_date",
      "type": "date",
      "semantic_type": "time",
      "time_grains": ["day", "week", "month", "quarter", "year"]
    }
  },
  "measures": {
    "revenue_usd": { "sql": "sum(d.revenue_usd)", "type": "number", "additivity": "additive" },
    "cogs_usd":    { "sql": "sum(d.cogs_usd)",    "type": "number", "additivity": "additive" },
    "cogs_ratio":  { "behavior": "derived", "type": "number",
                     "numerator": "cogs_usd", "denominator": "revenue_usd" }
  }
}

Query

{
  "contract_name": "daily_revenue_and_cost",
  "query": {
    "dimensions": [{ "name": "ordered_date", "time_grain": "month" }],
    "measures":   ["revenue_usd", "cogs_usd", "cogs_ratio"],
    "filters": [
      { "field": "ordered_date", "operator": "gte", "value": "2026-01-01" },
      { "field": "ordered_date", "operator": "lt",  "value": "2026-05-01" }
    ],
    "sorts": [{ "field": "ordered_date", "direction": "asc" }]
  }
}

Result

{
  "data": {
    "pagination": {
      "items": [
        { "ordered_date": "2026-01-01", "revenue_usd": 4860420.10, "cogs_usd": 1620140.30, "cogs_ratio": 0.3333 },
        { "ordered_date": "2026-02-01", "revenue_usd": 4612080.55, "cogs_usd": 1540216.40, "cogs_ratio": 0.3340 }
      ]
    }
  }
}

cogs_ratio is recomputed at the requested grain: querying by day gives daily ratios, by month gives monthly. Both are correct.

Notes

Whenever you need two facts in one contract, the answer is always the same:

Pick the lowest grain at which both facts are independently meaningful (often a date, sometimes a date + a key).
Aggregate each fact to that grain in a model.
FULL OUTER JOIN on the shared key.
Build a single-source contract on the model.

Source code

This model and contract are illustrative; they aren't shipped in the sample dataset. The closest in-dataset reference is customer_lifetime, which uses the same pattern (per-customer pre-aggregate, then a single-source contract on top).

Multi-fact via models