Skip to main content
A complete, self-contained data-warehouse example: a SaaS subscription business modeled Kimball-style as a Business Vault (dimensions + facts, including a monthly snapshot fact) with a condensed Data Mart layer on top, all in a single bundled DuckDB file. This is the broadest example in the set: it exercises every metric binding kind canonic supports, all three guardrail kinds, finality/restrict-source, query-based assertions, and a global knowledge base, in one project.
Full source: examples/saas-analytics/

​
Schema

Business Vault (8 dimensions, 10 facts) + 4 data marts:
TableRowsDescription
dim_customer12One row per customer account (conformed dimension)
dim_plan / dim_geo / dim_industry4 / 5 / 5Plan catalogue, geography, industry
dim_sales_rep / dim_campaign / dim_feature6 / 5 / 8Reps, acquisition campaigns, product features
dim_date731Conformed daily calendar (2024–2025)
fct_mrr_snapshot144Snapshot, MRR position per customer per month
fct_subscription_events18Lifecycle events (new/expansion/contraction/churn)
fct_invoices / fct_invoices_rt131 / 3Final billing + provisional intraday estimates
fct_usage / fct_feature_usage130 / 842Account usage rollup + per-feature usage
fct_support_tickets / fct_opportunities24 / 20Support tickets, sales pipeline
fct_nps_responses / fct_payments18 / 130NPS survey, payment transactions
mart_*n/aPre-aggregated monthly MRR, cohort retention, account health, rep quota

​
Quickstart

cd examples/saas-analytics
bash scripts/build.sh   # (optional) rebuild the warehouse from setup.sql

canonic query --metrics ending_mrr --dimensions snapshot_month
canonic query --metrics arpu --dimensions snapshot_month
canonic query --metrics customer_ltv --dimensions customer_id

canonic assert       # runs the contract assertions, gates on accuracy
canonic mcp start
No LLM is required: this example ships hand-curated semantics and contracts.

​
Metric catalogue: all 7 binding kinds

KindMetric(s)Binding highlight
singlegross_revenue, expansion_mrr, support_tickets, pipeline_value, settled_payments, avg_csatsource + measure
ratioarpu, churn_rate, win_rate, cac, npsnumerator / denominator (both single)
weighted_avgblended_discountweighted_sum / weight
semi_additiveending_mrrcollapse_dimension: snapshot_month, collapse_agg: last
distinct_countactive_subscribers, active_featuresdistinct_on + population_filter
percentilemedian_contract_value, p90_resolution_time, median_deal_sizecolumn + quantile
opaquecustomer_ltvnative_grain: [customer_id], served at customer grain only
Ratio and weighted-avg components must themselves be single-kind metrics, so a set of small helper metrics (mrr_total, active_accounts, churned_customers, new_customers, won_opportunities, total_opportunities, campaign_spend, discount_value_sum, total_contract_value, nps_net, nps_responses) provide those building blocks. See The semantic compiler for how each kind actually compiles.

​
Guardrails: all three kinds

  • revenue-excludes-refunds / revenue-excludes-trials: mandatory_filter (error). Injects status != 'refunded' and is_trial = false into every gross_revenue query.
  • board-reporting-final-only: restrict_source (error). In the board_reporting context, confines gross_revenue to the final fct_invoices source, paired with a finality rule (finality-revenue) that declares the final/provisional realizations and coalescing rule.
  • ending-mrr-requires-month: required_dimension (warn). Declared and surfaced, but (as noted in Contracts & guardrails) required_dimension isn’t yet enforced by the compiler.
// refunds + trials silently excluded; provisional rows excluded under board_reporting
query({"metrics": ["gross_revenue"], "context": "board_reporting"})

// opaque grain guard: this errors with UNSUPPORTED_MEASURE
query({"metrics": ["customer_ltv"], "dimensions": ["segment"]})

​
Assertions

Query-based, with expected values derived from the deterministic seed:
  • gross-revenue-2025-q1 → 17814.00 (paid, non-trial, non-refunded invoices, Q1)
  • active-subscribers-2025-03 → 12
canonic assert runs them and reports accuracy (expected: 100%). See canonic assert.

​
Knowledge

knowledge/global/: bound to semantic entities via sl_refs:
  • mrr-definition (definition, with a live {{ sl:… }} template)
  • semi-additive-mrr-caveat (caveat): never sum MRR across months
  • revenue-excludes-refunds-trials-caveat (caveat)
  • revenue-finality-policy (policy): final vs. provisional revenue
  • ltv-methodology (policy): why customer_ltv is opaque
  • vault-vs-mart (reference): when to use vault facts vs. data marts

​
Regenerating the warehouse

bash scripts/build.sh   # rebuilds saas.duckdb from setup.sql (idempotent)
setup.sql is plain, deterministic DDL + seed data and can also be run directly through any DuckDB client: duckdb saas.duckdb < setup.sql.