.duckdb file plus a companion dbt manifest.
Compared to the ecommerce guide, this project exercises a different schema shape: a geography dimension chain (station → municipality → province) resolved by spatial joins at build time, a dimension with a synthetic unknown fallback row, and a fact table pinned to a single demo day.
Full source:
examples/dutch-railway/Schema
The fact table is pinned to a single demo day (service_date = 2024-08-01): 63,946 service stops across 397 stations.
Setup
Bundled DuckDB file: no server, no credentials.Quickstart
canonic status, canonic ingest --bootstrap, canonic query, and canonic mcp start never call the LLM: every table here has a declared primary key, so grain is inferred deterministically. CANONIC_LLM_API_KEY and llm: in canonic.yaml only matter for low-confidence grain-drafting on schemas with undeclared keys, which this one doesn’t need.
MCP auth: token and OAuth side by side
canonic.yaml configures both auth mechanisms at once, to demonstrate they compose rather than being an either-or choice (AMENDMENT-oauth-mcp-auth):
jwt mode: a client presents a JWT already issued by NS’s own identity provider, no proxy redirect) authenticates interactive human operators against the organization’s SSO. canonic mcp start --transport http checks a request’s bearer token against the static map first (fast, no network call), and only falls through to OAuth verification if it doesn’t match. Either one succeeding is enough, and neither is required to configure the other. canonic mcp status reports both as active:
issuer_url here is a placeholder domain (this example ships offline, with no real IdP behind it). Point it at your organization’s actual IdP to make the OAuth side functional. See Connecting your agent for the full config reference and both modes (proxy vs jwt).
Metrics
Why scheduled_stops exists as a separate metric
exclude-cancelled-arrivals is a mandatory_filter guardrail matched by measure name, scoped to fact_services.service_count. scheduled_stops has the same underlying expression (count(service_sk)) under a different measure name, so the guardrail doesn’t touch it, deliberately. cancelled_arrival_rate needs an unguarded total in its denominator. Reusing the guarded service_count there would silently exclude cancellations from both sides of the ratio and always yield the same value regardless of cancellations.
Example queries
Three-hop join (fact_services → dim_nl_train_stations → dim_nl_municipalities → dim_nl_provinces):
ratio-kind metric. Ratios compile to a CTE per component and divide after aggregating, and they combine freely with other metrics and dimensions in the same call:
The dbt connector: a definition connector with no database
Wired intocanonic.yaml with no credentials at all:
RelationSchema + model evidence (descriptions, grain, FK-derived joins) rather than measure/dimension evidence. The real measures live in the hand-authored semantics/railway_duckdb/*.yaml. Modeling-tier evidence from dbt still outranks live DuckDB introspection wherever the two overlap.
What got adapted from upstream
Two build-time fixups make the upstream project (which relies on DuckDB’sspatial extension and Postgres cross-database attach) work as a pure, offline canonic example:
- Geometry → WKT text: canonic has no geometry type, and the joins already live in surrogate keys, so polygon/point geometry becomes plain WKT text at build time: a centroid point for the two polygon dimensions, the exact point for stations.
- dbt manifest relation names: dbt-duckdb’s compiled manifest tags every model with a
database: <catalog>field, but canonic’s DuckDB connector introspects relations without a catalog prefix. The build nullsdatabaseon every model node so the two tiers reconcile correctly.
scripts/build.sh for the full regeneration recipe (requires network access and uv).