> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getcanonic.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Guide: Dutch Railway Network (DuckDB)

> A DuckDB-native example with a geography dimension chain and ratio metrics.

A DuckDB-native example ported from [DuckDB's own blog example](https://github.com/duckdb/duckdb-blog-examples/tree/main/dbt_duckdb/dutch_railway_network). No live database, no credentials: a single committed `.duckdb` file plus a companion dbt manifest.

Compared to the [ecommerce guide](/guides/ecommerce), 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.

<Info>Full source: [`examples/dutch-railway/`](https://github.com/mischuh/canonic/tree/main/examples/dutch-railway)</Info>

## Schema

The fact table is pinned to a single demo day (`service_date = 2024-08-01`): 63,946 service stops across 397 stations.

| Table                   | Rows                  | Grain             |
| ----------------------- | --------------------- | ----------------- |
| `dim_nl_provinces`      | 13 (12 + `unknown`)   | `province_sk`     |
| `dim_nl_municipalities` | 343 (342 + `unknown`) | `municipality_sk` |
| `dim_nl_train_stations` | 397                   | `station_sk`      |
| `fact_services`         | 63,946                | `service_sk`      |

## Setup

Bundled DuckDB file: no server, no credentials.

```bash theme={null}
cd examples/dutch-railway   # canonic commands must run from here
canonic status
# Canonic project: dutch-railway-demo (version 1)
```

## Quickstart

```bash theme={null}
canonic ingest --bootstrap                                    # introspect DuckDB → write semantics/*.yaml
canonic query --metrics service_count --dimensions station_name  # guardrail fires automatically
canonic mcp start                                              # serve: agents call query() + search_knowledge() together
```

`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):

```yaml theme={null}
mcp:
  auth:
    tokens:
      - client_id: ops-cli
        token_ref: env:CANONIC_MCP_TOKEN_OPS
    oauth:
      mode: jwt
      issuer_url: https://sso.ns-demo.example.com
      audience: canonic-mcp-dutch-railway
      jwks_uri: https://sso.ns-demo.example.com/.well-known/jwks.json
```

The static token authenticates a CI pipeline or script without ever contacting the IdP. OAuth (here, `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:

```bash theme={null}
canonic mcp start --transport http --port 7474
canonic mcp status
#   auth:      token, oauth-jwt
```

`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](/mcp-integration/connecting-your-agent#remote-enterprise-deployment) for the full config reference and both modes (`proxy` vs `jwt`).

## Metrics

| Metric                                                | Kind     | Definition                                                                                          |
| ----------------------------------------------------- | -------- | --------------------------------------------------------------------------------------------------- |
| `service_count`                                       | `single` | Realized service stops (guardrailed, excludes cancelled arrivals)                                   |
| `scheduled_stops`                                     | `single` | Same expression as `service_count`, but **ungated**: the unguarded denominator for the rate metrics |
| `cancelled_arrivals` / `cancelled_departures`         | `single` | Sibling metrics, unaffected by the guardrail                                                        |
| `cancelled_arrival_rate` / `cancelled_departure_rate` | `ratio`  | `cancelled_* / scheduled_stops`                                                                     |

### 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`):

```json theme={null}
query({"metrics": ["service_count"], "dimensions": ["province_name"]})
```

A `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:

```json theme={null}
query({"metrics": ["cancelled_arrival_rate"]})
→ {"result": {"columns": [{"name": "cancelled_arrival_rate", "type": "float"}], "rows": [[0.0883]]}}
```

## The dbt connector: a definition connector with no database

Wired into `canonic.yaml` with no credentials at all:

```yaml theme={null}
connections:
  - id: railway_dbt
    type: dbt
    params:
      manifest_path: dbt/manifest.json
```

```bash theme={null}
canonic ingest --connection railway_dbt --dry-run
```

Upstream's dbt project has no MetricFlow semantic-model/metric blocks (it's a dbt-duckdb engineering post, not a semantics one), so the manifest contributes `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's `spatial` extension and Postgres cross-database attach) work as a pure, offline canonic example:

1. **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.
2. **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 nulls `database` on every model node so the two tiers reconcile correctly.

See [`scripts/build.sh`](https://github.com/mischuh/canonic/tree/main/examples/dutch-railway/scripts/build.sh) for the full regeneration recipe (requires network access and `uv`).