# Metrics

Define each metric once in a `metrics.yaml` next to your dashboards. The same
definition powers dashboard tiles, the CLI, agents over MCP, and exports to other BI
tools, so nobody re-derives revenue in raw SQL.

```yaml
source: {type: duckdb, attach_files: true}

relations:
  orders:
    table: orders

metrics:
  revenue:
    title: Revenue
    description: Total order revenue in USD
    relation: orders
    expr: SUM(amount)
    format: currency
    synonyms: [sales, turnover]
    time_dimension: {name: order_date, grain: day}
    dimensions: [{name: region, description: Sales region}, {name: category}]

  order_count:
    relation: orders
    expr: COUNT(*)
    time_dimension: {name: order_date, grain: day}
    dimensions: [{name: region}, {name: category}]

  avg_order_value:
    title: Average order value
    derived: "{revenue} / NULLIF({order_count}, 0)"
    format: currency

  trailing_28d_revenue:
    relation: orders
    expr: SUM(amount)
    window: 28 days
    time_dimension: {name: order_date, grain: day}
```

## Defining a metric

Every metric starts from one base, which is a `relation:` from the `relations:` map, a
`table:`, or `sql:`. It then aggregates with `expr:` (any SQL aggregate) or with
`derived:`, a formula over other metrics such as
`"{revenue} / NULLIF({order_count}, 0)"`.

Add `time_dimension: {name, grain}` to allow time series. Grains are `hour`, `day`,
`week`, `month`, `quarter`, and `year`.

`dimensions:` lists what the metric may be grouped or filtered by. Queries can only
use declared dimensions, and that is the governance. `filters:` holds SQL conditions
that always apply, such as `status = 'complete'`.

`cumulative: true` turns a metric into a running total, and `window: 28 days` makes it
a trailing aggregate per bucket. Both need a `time_dimension`.

A `derived` metric combines plain metrics on one relation. A component that carries
`filters`, `window`, or `cumulative` is refused, because only its `expr` is inlined.

### Time zones on Snowflake

On Snowflake, a time dimension over a `TIMESTAMP_TZ` column needs `timezone: session`.

```yaml
    time_dimension: {name: created_at, grain: month, timezone: session}
```

Snowflake truncates a `TIMESTAMP_TZ` at each row's own offset, so without it a month of
rows written from three time zones comes back as three buckets for that month. With it,
sqldash reads the column as `TIMESTAMP_LTZ` before bucketing, and every row is cut in
the session time zone. Leave it off for `DATE`, `TIMESTAMP_NTZ`, and `TIMESTAMP_LTZ`
columns, which already bucket correctly, and on Postgres and DuckDB, which bucket a
`timestamptz` that way on their own. `sqldash lint --strict` reads the column types and
warns about a `TIMESTAMP_TZ` time dimension that does not set it.

### Time buckets inside an expression

When an expression needs a time bucket, write `SQLDASH_TRUNC('<grain>', <expr>)` instead
of a warehouse's own function.

```yaml
  active_weeks:
    relation: orders
    expr: "COUNT(DISTINCT SQLDASH_TRUNC('week', order_date))"
```

sqldash spells it for whichever warehouse the file points at, so the metric keeps
working after you point `source:` somewhere else. It works in `expr`, `filters`,
dimension and time dimension `expr`, and a relation's `sql:`, with the usual grains.
It is sqldash's own spelling and will not run if you paste it into a tile's SQL or
`run_sql`, but `export lookml` and `export cortex` write the target's real function.

`title`, `description`, `format`, `synonyms`, and `owners` help people and agents find
the right metric.

## In dashboards

A tile names a metric instead of carrying SQL.

```yaml
tiles:
  - title: Revenue by region
    metric: {name: revenue, grain: month, dimensions: [region]}
    chart: {type: line, group_by: region}

  - title: Total revenue
    metric: revenue
    compare: previous_period
```

Dashboard filters bind to metric dimensions by name, and a `daterange` filter becomes
the metric's time range. `compare:` takes `previous_period` or `yoy`.

A dashboard can also define `metrics:` inline so a single file stays portable. An inline
metric runs on the dashboard's own `source:`, so its base has to be in the dashboard file
too. `relation:` resolves against the dashboard's own `relations:`, never against
`metrics.yaml`, so either declare the relation in the dashboard or name the `table:`
directly. A metric that should reuse a project relation belongs in `metrics.yaml`, where
any dashboard can reference it by name.

## From the terminal

```bash
sqldash metric list                              # the semantic layer at a glance
sqldash metric show revenue                      # full definition
sqldash metric query revenue -d region -g month --start -90d
sqldash metric query revenue --start -30d --end today --compare previous_period
sqldash metric query revenue -p region=eu -f json
```

Every `list` and `show` takes `--json`.

## One namespace per project

Metric names live in one namespace per project. The project `metrics.yaml` is
canonical. A dashboard may define metrics inline in its own `metrics:` block, and an
inline definition overrides the project one within that dashboard only. A name that
two dashboards each define inline belongs to neither. sqldash refuses it rather than
picking one, `--dashboard` on the CLI says which you mean, and `sqldash lint` fails on
the collision so CI catches it first.

## Names in, SQL out

Callers, whether the UI, the CLI, or an agent, pass metric names, dimension names,
and filter values. They never pass SQL. The compiler binds every value as a query
parameter, so the only verbatim SQL in a query is what the metric's author wrote in
the YAML. Agents reach the same compiler through `query_metric`, described in
[MCP](/docs/mcp/#tools).