Build

Metrics

MarkdownAsk ChatGPTAsk Claude

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

Terminal
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.

← ExploreSources →