Rill Metrics View

by rilldataa0a211654875No licenseListed Oct 8, 2026Updated Oct 8, 2026

Detailed instructions and examples for developing metrics view resources in Rill

Instructions onlyData & Analytics Software Development

AI-generated overview

Guides authoring Rill metrics view YAML resources that define dimensions, measures, security and explore dashboards.

What it does: This skill provides instructions and annotated examples for writing Rill metrics view resources, the semantic layer that defines queryable business metrics over a table in an OLAP database. It covers the model or table source, timeseries, dimensions, measures, format presets, inline explore dashboards, security policies such as access and row filters, and advanced features like annotations, unnest, caching, tags and rollups. It also documents dialect-specific SQL notes for DuckDB, ClickHouse and Druid, plus a full JSON schema reference.
When to use it: Use it when creating or editing a metrics view resource in a Rill project, for example to expose business metrics for explore dashboards, alerts, reports or custom APIs. It is also relevant when adding row-level security or rollups to an existing metrics view.
Requirements: No scripts are shipped; it is instructions only. It assumes a Rill project with an OLAP connector and an underlying model or external table, and knowledge of the relevant SQL dialect.

Instructions for developing a metrics view in Rill

Introduction

Metrics views are resources that define queryable business metrics on top of a table in an OLAP database. They implement what other business intelligence tools call a "semantic layer" or "metrics layer".

Metrics views are lightweight resources that only perform validation when reconciled. They are typically found downstream of connectors and models in the project's DAG. They power many user-facing features:

Explore dashboards: Interactive drill-down interfaces for data exploration
Canvas dashboards: Custom chart and table components
Alerts: Notifications when data meets certain criteria
Reports: Scheduled data exports and summaries
Custom APIs: Programmatic access to metrics

Core Concepts

Table source

The model: property specifies the underlying table that powers the metrics view. It can reference:

A model in the project: Just use the model name (e.g., model: events)
An external table: Specify the table name as it exists in the OLAP connector

yaml

# Referencing a model in the projectmodel: events
# Referencing an external table (connector defaults to project's default OLAP)connector: clickhouse  # Optional: specify if different from defaultmodel: my_external_table # Note: Doesn't support dot syntax for database/schema name. Use the separate `database:` or `database_schema:` keys for that if relevant (but try without first and see if that works).

# Referencing a model in the projectmodel: events
# Referencing an external table (connector defaults to project's default OLAP)connector: clickhouse  # Optional: specify if different from defaultmodel: my_external_table # Note: Doesn't support dot syntax for database/schema name. Use the separate `database:` or `database_schema:` keys for that if relevant (but try without first and see if that works).

Note: The table: property is a legacy alias for referencing external tables. Always prefer model: in new metrics views.

Timeseries

The timeseries: property identifies the timestamp column used for time-based filtering and line charts. This column must be a time/timestamp type in the underlying table.

If the timeseries column is not listed in dimensions:, Rill automatically adds it as a time dimension. You can optionally configure additional time-related settings:

yaml

timeseries: event_timesmallest_time_grain: hour      # Minimum granularity users can selectfirst_day_of_week: 7           # Sunday (1=Monday, 7=Sunday)first_month_of_year: 4         # April (fiscal year starting in April)

timeseries: event_timesmallest_time_grain: hour      # Minimum granularity users can selectfirst_day_of_week: 7           # Sunday (1=Monday, 7=Sunday)first_month_of_year: 4         # April (fiscal year starting in April)

It is strongly recommended that you add a primary timeseries to every metrics view you create (it makes for a much better dashboard experience).

Dimensions

Dimensions are attributes you can group by or filter on. They are typically categorical (strings, enums) or temporal (dates, timestamps). Rill infers the dimension type from the underlying SQL data type:

Categorical: String, enum, boolean columns
Time: Timestamp, date, datetime columns
Geospatial: Geometry or geography columns

Define dimensions using either a direct column reference or a SQL expression:

yaml

dimensions:  # Simple column reference  - name: country    column: country
  # Computed expression  - name: device_category    expression: CASE WHEN device_type IN ('phone', 'tablet') THEN 'Mobile' ELSE 'Desktop' END
  # With display name and description  - name: campaign_name    display_name: Campaign    description: Marketing campaign that drove the traffic    column: campaign_name

dimensions:  # Simple column reference  - name: country    column: country
  # Computed expression  - name: device_category    expression: CASE WHEN device_type IN ('phone', 'tablet') THEN 'Mobile' ELSE 'Desktop' END
  # With display name and description  - name: campaign_name    display_name: Campaign    description: Marketing campaign that drove the traffic    column: campaign_name

Naming: Each dimension needs a name (stable identifier used in APIs and references), which defaults to column: if provided. The display_name: is optional, and defaults to a humanized version of name if not specified.

Type: Rill can infer the dimension type (categorical, time, geo) from the underlying SQL data type. Do not set type: explicitly unless you have a specific reason to override the inferred type.

Clickable URLs: The optional uri: property marks a dimension as a clickable link for single-click navigation. It accepts either a boolean (when the dimension's own value is already a URL) or a SQL expression that produces the URL. It is a distinct property from column: and expression: and can be combined with column::

yaml

dimensions:  # The column's values are already URLs  - name: page_url    column: page_url    uri: true
  # Build the URL from another column while still grouping by that column  - name: user_uri    column: user_name    uri: concat('https://example.com/', user_name)

dimensions:  # The column's values are already URLs  - name: page_url    column: page_url    uri: true
  # Build the URL from another column while still grouping by that column  - name: user_uri    column: user_name    uri: concat('https://example.com/', user_name)

Measures

Measures are aggregation expressions that compute numeric values when grouped by dimensions. They must use aggregate functions like SUM(), COUNT(), AVG(), MIN(), MAX().

yaml

measures:  - name: total_revenue    display_name: Total Revenue    expression: SUM(revenue)    description: Sum of all revenue in USD    format_preset: currency_usd
  - name: unique_users    display_name: Unique Users    expression: COUNT(DISTINCT user_id)    format_preset: humanize
  - name: conversion_rate    display_name: Conversion Rate    expression: SUM(conversions) / NULLIF(SUM(visits), 0)    format_preset: percentage    valid_percent_of_total: false  # Disable % of total for ratios

measures:  - name: total_revenue    display_name: Total Revenue    expression: SUM(revenue)    description: Sum of all revenue in USD    format_preset: currency_usd
  - name: unique_users    display_name: Unique Users    expression: COUNT(DISTINCT user_id)    format_preset: humanize
  - name: conversion_rate    display_name: Conversion Rate    expression: SUM(conversions) / NULLIF(SUM(visits), 0)    format_preset: percentage    valid_percent_of_total: false  # Disable % of total for ratios

Format presets: Control how values are displayed:

none: Raw number
humanize: Round to K, M, B (e.g., 1.2M)
currency_usd: Dollar format with 2 decimals ($1,234.56)
currency_eur: Euro format
percentage: Multiply by 100 and add % sign
interval_ms: Convert milliseconds to human-readable duration

For custom formatting, use format_d3 with a d3-format string:

yaml

format_d3: "$,.2f"  # $1,234.56format_d3: ".1%"    # 12.3%format_d3: ",.0f"   # 1,235 (rounded, with thousands separator)

format_d3: "$,.2f"  # $1,234.56format_d3: ".1%"    # 12.3%format_d3: ",.0f"   # 1,235 (rounded, with thousands separator)

Best practices for dimensions and measures

Naming conventions:

Use snake_case for the name field (e.g., total_revenue, unique_users)
Only add display_name and description if they provide meaningful context beyond what name conveys (display names auto-humanize from the name by default)
Ensure measure names don't collide with column names in the underlying table

Getting started with measures:

Start with a COUNT(*) measure as a baseline (e.g., total_records or total_events)
Add SUM() measures for numeric columns that represent quantities or values
Use humanize as the default format preset unless the data has a specific format requirement
Keep initial measures simple using only COUNT, SUM, AVG, MIN, MAX aggregations
Add more complex expressions (ratios, conditional aggregations) only when needed

Dimension selection:

Include all categorical columns (strings, enums, booleans) that users might want to filter or group by
Start with 5-10 dimensions; add more based on user needs

Timeseries:

If there is any date/timestamp column in the underlying table, pick the primary or most interesting one and add it under dimensions:
It is also strongly recommended that you configure a primary time dimension using timeseries:

Inline explore

New metrics views should set version: 1 and include an explore: block, which makes Rill emit an explore dashboard for the metrics view (named after the metrics view unless name: is set):

yaml

version: 1explore:  display_name: Sales Dashboard  dimensions: '*'      # Optional: dimensions to expose ('*', a list, or {exclude: [...]}); defaults to all  measures: '*'        # Optional: measures to expose ('*', a list, or {exclude: [...]}); defaults to all  defaults:    time_range: P7D    measures:      - total_revenue      - order_count

version: 1explore:  display_name: Sales Dashboard  dimensions: '*'      # Optional: dimensions to expose ('*', a list, or {exclude: [...]}); defaults to all  measures: '*'        # Optional: measures to expose ('*', a list, or {exclude: [...]}); defaults to all  defaults:    time_range: P7D    measures:      - total_revenue      - order_count

An empty block (explore: {}) is enough to enable the dashboard with all dimensions and measures. Note that explore: with no value (null) does NOT enable it. Set explore: {skip: true} to create a metrics view without a dashboard.

Legacy behavior: Files without version: (or version: 0) auto-generate an explore even without an explore: block. Files with version: 1 only get an explore if an explore: block is present.

Full Example

Here is a complete, annotated metrics view:

yaml

# metrics/orders.yamlversion: 1type: metrics_view
# Display metadatadisplay_name: Orders Analyticsdescription: Analyze order performance by various dimensions
# Data source - references the 'orders' model in the projectmodel: orders
# Time column for time-series charts and filteringtimeseries: order_datesmallest_time_grain: day
# Dimensions for grouping and filteringdimensions:  - name: order_date    display_name: Order Date    column: order_date
  - name: country    display_name: Country    column: shipping_country
  - name: product_category    display_name: Product Category    column: category    description: High-level product grouping
  - name: customer_segment    display_name: Customer Segment    expression: |       CASE        WHEN lifetime_value > 1000 THEN 'High Value'        WHEN lifetime_value > 100 THEN 'Medium Value'        ELSE 'Low Value'      END
  - name: is_repeat_customer    display_name: Repeat Customer    expression: CASE WHEN order_number > 1 THEN 'Yes' ELSE 'No' END
# Measures for aggregationmeasures:  - name: total_orders    display_name: Total Orders    expression: COUNT(*)    format_preset: humanize
  - name: total_revenue    display_name: Total Revenue    expression: SUM(order_total)    format_preset: currency_usd    description: Gross revenue before refunds
  - name: average_order_value    display_name: Avg Order Value    expression: SUM(order_total) / NULLIF(COUNT(*), 0)    format_preset: currency_usd    valid_percent_of_total: false
  - name: unique_customers    display_name: Unique Customers    expression: COUNT(DISTINCT customer_id)    format_preset: humanize
  - name: items_per_order    display_name: Items per Order    expression: SUM(item_count) / NULLIF(COUNT(*), 0)    format_d3: ",.1f"    valid_percent_of_total: false
# Explore dashboard for this metrics viewexplore:  display_name: Orders Dashboard

# metrics/orders.yamlversion: 1type: metrics_view
# Display metadatadisplay_name: Orders Analyticsdescription: Analyze order performance by various dimensions
# Data source - references the 'orders' model in the projectmodel: orders
# Time column for time-series charts and filteringtimeseries: order_datesmallest_time_grain: day
# Dimensions for grouping and filteringdimensions:  - name: order_date    display_name: Order Date    column: order_date
  - name: country    display_name: Country    column: shipping_country
  - name: product_category    display_name: Product Category    column: category    description: High-level product grouping
  - name: customer_segment    display_name: Customer Segment    expression: |       CASE        WHEN lifetime_value > 1000 THEN 'High Value'        WHEN lifetime_value > 100 THEN 'Medium Value'        ELSE 'Low Value'      END
  - name: is_repeat_customer    display_name: Repeat Customer    expression: CASE WHEN order_number > 1 THEN 'Yes' ELSE 'No' END
# Measures for aggregationmeasures:  - name: total_orders    display_name: Total Orders    expression: COUNT(*)    format_preset: humanize
  - name: total_revenue    display_name: Total Revenue    expression: SUM(order_total)    format_preset: currency_usd    description: Gross revenue before refunds
  - name: average_order_value    display_name: Avg Order Value    expression: SUM(order_total) / NULLIF(COUNT(*), 0)    format_preset: currency_usd    valid_percent_of_total: false
  - name: unique_customers    display_name: Unique Customers    expression: COUNT(DISTINCT customer_id)    format_preset: humanize
  - name: items_per_order    display_name: Items per Order    expression: SUM(item_count) / NULLIF(COUNT(*), 0)    format_d3: ",.1f"    valid_percent_of_total: false
# Explore dashboard for this metrics viewexplore:  display_name: Orders Dashboard

Security Policies

Security policies control who can access a metrics view and what data they can see. This is a powerful feature for multi-tenant dashboards and role-based access control.

Basic access control

The access: property controls whether users can view the metrics view at all:

yaml

security:  # Allow access for everyone  access: true
  # Deny access for everyone (useful for draft dashboards)  access: false
  # Conditional access based on user attributes  access: "'{{ .user.admin }}' = 'true'"

security:  # Allow access for everyone  access: true
  # Deny access for everyone (useful for draft dashboards)  access: false
  # Conditional access based on user attributes  access: "'{{ .user.admin }}' = 'true'"

The expression syntax should be a DuckDB expression, which will be evaluated in a sandbox without access to any tables.

Row-level security

The row_filter: property restricts which rows a user can see. It's a SQL expression that references user attributes via templating:

Common user attributes:

{{ .user.email }}: User's email address
{{ .user.domain }}: Email domain (e.g., "acme.com")
{{ .user.admin }}: Boolean admin flag
Custom attributes configured in Rill Cloud

The row filter should use the SQL syntax of the metrics view's model, and can reference other tables in the model's connector.

Complex row filters

Use logical operators for sophisticated access patterns:

yaml

security:  access: true  row_filter: >    {{ .user.admin }}    OR '{{ .user.domain }}' = 'acme.com'    {{ if hasKey .user "tenant_id" }}    OR tenant_id = '{{ .user.tenant_id }}'    {{ end }}

security:  access: true  row_filter: >    {{ .user.admin }}    OR '{{ .user.domain }}' = 'acme.com'    {{ if hasKey .user "tenant_id" }}    OR tenant_id = '{{ .user.tenant_id }}'    {{ end }}

Hiding dimensions and measures

The exclude: property conditionally hides specific dimensions or measures from certain users:

yaml

security:  access: true  exclude:    - if: "NOT {{ .user.admin }}"      names:        - cost_per_acquisition  # Hide sensitive cost data from non-admins        - internal_notes

security:  access: true  exclude:    - if: "NOT {{ .user.admin }}"      names:        - cost_per_acquisition  # Hide sensitive cost data from non-admins        - internal_notes

Advanced Features

Annotations

Annotations overlay contextual information (like events or milestones) on time-series charts:

yaml

annotations:  - name: product_launches    model: product_launches  # Must have 'time' and 'description' columns    measures:      - total_revenue        # Only show on these measures
  # Optional columns in annotation model:  # - time_end: For range annotations  # - grain: Show only at specific time grains (day, week, etc.)

annotations:  - name: product_launches    model: product_launches  # Must have 'time' and 'description' columns    measures:      - total_revenue        # Only show on these measures
  # Optional columns in annotation model:  # - time_end: For range annotations  # - grain: Show only at specific time grains (day, week, etc.)

Unnest for array dimensions

When a column contains arrays, use unnest: true to flatten it at query time:

yaml

dimensions:  - name: tags    display_name: Tags    column: tags    unnest: true  # Allows filtering by individual array elements

dimensions:  - name: tags    display_name: Tags    column: tags    unnest: true  # Allows filtering by individual array elements

Cache configuration

Configure caching for slow metrics views that use external tables:

You should not add a cache: config when the metrics view references a model inside the project since Rill does automatic cache management in that case.

Tags on dimensions and measures

Add tags: (free-form labels) to a dimension or measure to group and filter the dropdowns and pivot tables:

yaml

dimensions:  - name: campaign_name    column: campaign_name    tags: [marketing]measures:  - name: total_spend    expression: SUM(spend)    tags: [marketing, finance]

dimensions:  - name: campaign_name    column: campaign_name    tags: [marketing]measures:  - name: total_spend    expression: SUM(spend)    tags: [marketing, finance]

Rollups

Rollups back a metrics view with pre-aggregated tables. When a query's grain, dimensions, measures, time range, and filters match a rollup, Rill reads the smaller table instead of the base table for faster results. Requires a timeseries:.

yaml

rollups:  - model: events_daily           # Pre-aggregated model    time_grain: day               # Required    dimensions: [country]         # Optional; defaults to all    measures: [total_events]      # Optional; defaults to all    data_time_range: -90D to now  # Optional; indicates rollup data time range if set otherwise min/max queries are done on the timeseries column to figure out rollup's data time range

rollups:  - model: events_daily           # Pre-aggregated model    time_grain: day               # Required    dimensions: [country]         # Optional; defaults to all    measures: [total_events]      # Optional; defaults to all    data_time_range: -90D to now  # Optional; indicates rollup data time range if set otherwise min/max queries are done on the timeseries column to figure out rollup's data time range

Dialect-Specific Notes

SQL expressions in dimensions and measures use the underlying OLAP database's dialect.

DuckDB

DuckDB is the default OLAP engine for local development.

Conditional aggregation with FILTER:

yaml

# DuckDB supports FILTER clause for conditional aggregationexpression: COUNT(*) FILTER (WHERE status = 'completed')

# DuckDB supports FILTER clause for conditional aggregationexpression: COUNT(*) FILTER (WHERE status = 'completed')

ClickHouse

ClickHouse is recommended for production workloads with large datasets.

Conditional aggregation:

yaml

# ClickHouse uses IF or CASE inside aggregationsexpression: countIf(status = 'completed')expression: sumIf(revenue, status = 'completed')

# ClickHouse uses IF or CASE inside aggregationsexpression: countIf(status = 'completed')expression: sumIf(revenue, status = 'completed')

Date functions:

yaml

expression: toYear(order_date)expression: toStartOfMonth(order_date)expression: toYYYYMMDD(order_date)

expression: toYear(order_date)expression: toStartOfMonth(order_date)expression: toYYYYMMDD(order_date)

Array functions:

Druid

Approximate distinct counts:

Reference documentation

Here is a full JSON schema for the metrics view syntax:

allOf:    - properties:        ai_instructions:            description: Extra instructions for [AI agents](/guide/ai/mcp). Used to guide natural language question answering and routing.            type: string        annotations:            description: Used to define annotations that can be displayed on charts            items:                properties:                    connector:                        description: Refers to the connector to use for the annotation                        type: string                    database:                        description: Refers to the database to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified                        type: string                    database_schema:                        description: Refers to the schema to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified                        type: string                    measures:                        $ref: '#/definitions/field_selector_properties'                        description: Specifies which measures to apply the annotation to. Applies to all measures if not specified                    model:                        description: Refers to the model powering the annotation (either table or model is required). The model must have 'time' and 'description' columns. Optional columns include 'time_end' for range annotations and 'grain' to specify when the annotation should appear based on dashboard grain level.                        type: string                    name:                        description: A stable identifier for the annotation. Defaults to model or table names when not specified                        type: string                    table:                        description: Refers to the table powering the annotation, should be used instead of model for annotations from external OLAP tables (either table or model is required)                        type: string                type: object            type: array        cache:            description: |                Enable caching of query results for metrics views backed by externally-managed tables (e.g. in Snowflake, BigQuery). These settings have no effect for metrics views backed by Rill models (where queries are automatically cached and invalidated when the model is refreshed).                Each cache entry is keyed by a hash of the query combined with the latest result of `key_sql`. Cached results stay valid as long as `key_sql` returns the same value; when its result changes, prior results become unreachable. `key_sql` itself runs at most once per `key_ttl`, decoupling freshness checks from query traffic.                Example: a `key_sql` of `SELECT MAX(updated_at) FROM orders` with `key_ttl: 5m` checks for new data every 5 minutes but only invalidates cached results when new data has actually landed.            properties:                enabled:                    description: Whether to cache query results for this metrics view. Defaults to false for metrics views backed by externally-managed tables and to true for metrics views backed by a Rill model.                    type: boolean                key_sql:                    description: SQL returning a single value used in the cache key, typically a max timestamp, version, or row count. Cached results are invalidated when this value changes. Optional; defaults to the metrics view's watermark expression (which itself defaults to `MAX(<time dimension>)`).                    type: string                key_ttl:                    description: How often `key_sql` is re-evaluated, as a Go duration string (e.g. `30s`, `5m`, `1h`). The previous result is reused between evaluations. Defaults to `60s`.                    type: string                timestamps_ttl:                    description: TTL for caching the min/max timestamp queries used to populate a metrics view's rollups. Only takes effect when the metrics view has rollups defined and query result caching (`enabled`) is off — otherwise rollup timestamps are cached alongside other query results under `key_ttl`. Go duration string (e.g. `5m`). Defaults to `5m`.                    type: string            type: object        connector:            description: Refers to the connector type for the metrics view, see [OLAP engines](/developers/build/connectors/olap) for more information            type: string        data_time_range:            description: Optional [rilltime](https://docs.rilldata.com/reference/time-syntax) expression describing the base table's time coverage (e.g. `-5Y to now`). When set, Rill skips the `min`/`max` OLAP probe for the base table and uses the declared bounds for coverage checks. The start must be bounded; `inf` and `earliest` are rejected. To declare full history, use a concrete early bound such as `-100Y to now` or omit this field to probe the table.            type: string        database:            description: Refers to the database to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified            type: string        database_schema:            description: Refers to the schema to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified            type: string        description:            description: Refers to the description for the metrics view            type: string        dimensions:            description: Relates to exploring segments or dimensions of your data and filtering the dashboard            items:                anyOf:                    - required:                        - column                    - required:                        - expression                properties:                    column:                        description: A categorical column.                        type: string                    description:                        description: a freeform text description of the dimension                        type: string                    display_name:                        description: a display name for your dimension                        type: string                    expression:                        description: A non-aggregate expression such as string_split(domain, '.'). One of column and expression is required, but cannot have both at the same time.                        type: string                    lookup_default_expression:                        description: an optional SQL expression used as a fallback value when no match is found in the dictionary (maps to `dictGetOrDefault`)                        type: string                    lookup_key_column:                        description: the primary key column in the lookup dictionary that corresponds to the dimension's `column` in the fact table                        type: string                    lookup_table:                        description: The name of a ClickHouse dictionary to use for query-time lookups. Use `database.dictionary_name` for dictionaries in a non-default database. All three `lookup_*` fields (`lookup_table`, `lookup_key_column`, `lookup_value_column`) must be specified together. See [Query-Time Joins](/developers/build/metrics-view/dimensions/lookup) for details.                        type: string                    lookup_value_column:                        description: the attribute column in the lookup dictionary whose values will be displayed for this dimension                        type: string                    name:                        description: a stable identifier for the dimension                        type: string                    tags:                        description: optional list of tags for categorizing the dimension (defaults to empty)                        items:                            type: string                        type: array                    type:                        description: 'Dimension type: "geo" for geospatial dimensions, "time" for time dimensions, or "categorical" for categorical dimensions. Default is undefined and the type will be inferred instead.'                        type: string                    unnest:                        description: If true, allows multi-valued dimensions to be unnested (such as lists), and filters will automatically switch to "contains" instead of exact match.                        type: boolean                    uri:                        description: Enable if your dimension is a clickable URL to enable single-click navigation (boolean or valid SQL expression).                        type:                            - string                            - boolean                type: object            type: array        display_name:            description: Refers to the display name for the metrics view            type: string        explore:            $ref: '#/definitions/explore_properties'            description: Defines an optional inline explore view for the metrics view. If not specified a default explore will be emitted unless `skip` is set to true.            required:                - type        first_day_of_week:            description: Refers to the first day of the week for time grain aggregation (for example, Sunday instead of Monday). The valid values are 1 through 7 where Monday=1 and Sunday=7            type: integer        first_month_of_year:            description: Refers to the first month of the year for time grain aggregation. The valid values are 1 through 12 where January=1 and December=12            type: integer        max_query_time_range:            description: The maximum time span any single query against this metrics view may cover, expressed as an ISO 8601 duration with day-or-larger granularity (e.g. `P90D`, `P3M`, `P1Y`). Sub-day durations such as `PT12H` are not supported. Applies independently to the primary and comparison time ranges. If unset, no limit is enforced.            type: string        measures:            description: Used to define the numeric aggregates of columns from your data model            items:                properties:                    description:                        description: a freeform text description of the measure                        type: string                    display_name:                        description: the display name of your measure.                        type: string                    expression:                        description: a combination of operators and functions for aggregations                        type: string                    format_d3:                        description: 'Controls the formatting of this measure using a [d3-format](https://d3js.org/d3-format) string. If an invalid format string is supplied, the measure will fall back to `format_preset: humanize`. A measure cannot have both `format_preset` and `format_d3`. If neither is provided, the humanize preset is used by default. Example: `format_d3: ".2f"` formats using fixed-point notation with two decimal places. Example: `format_d3: ",.2r"` formats using grouped thousands with two significant digits. (optional)'                        type: string                    format_d3_locale:                        description: |                            Locale configuration passed through to D3, enabling changes to the currency symbol and other formatting options. For details, see the docs for D3's formatLocale.                              ```yaml                              format_d3: "$,"                              format_d3_locale:                                grouping: [3, 2]                                currency: ["₹", ""]                              ```                        properties:                            currency:                                description: the currency symbol                                type: array                            grouping:                                description: Grouping for the currency symbol.                                type: array                        type: object                    format_preset:                        description: |                            Controls the formatting of this measure using a predefined preset. Measures cannot have both `format_preset` and `format_d3`. If neither is supplied, the measure will be formatted using the `humanize` preset by default.
                              Available options:                              - `humanize`: Round numbers into thousands (K), millions(M), billions (B), etc.                              - `none`: Raw output.                              - `currency_usd`: Round to 2 decimal points with a dollar sign ($).                              - `currency_eur`: Round to 2 decimal points with a euro sign (€).                              - `percentage`: Convert a rate into a percentage with a % sign.                              - `interval_ms`: Convert milliseconds into human-readable durations like hours (h), days (d), years (y), etc. (optional)                        type: string                    label:                        description: a label for your measure, deprecated use display_name                        type: string                    lower_is_better:                        description: When true, decreases in this measure are favorable (e.g. bounce rate, latency, error count). UI surfaces that render comparison deltas (KPIs, big numbers, leaderboards, pivot tables, time-series tooltips) swap their positive/negative coloring accordingly.                        type: boolean                    name:                        description: a stable identifier for the measure                        type: string                    per:                        $ref: '#/definitions/field_selectors_properties'                        description: Dimensions to partition the measure by.                    requires:                        $ref: '#/definitions/field_selectors_properties'                        description: Uses an available measure or dimension in your metrics view to set a required parameter. Cannot be used with simple measures. See [referencing measures](/developers/build/metrics-view/measures/referencing) for more information.                    tags:                        description: optional list of tags for categorizing the measure (defaults to empty)                        items:                            type: string                        type: array                    treat_nulls_as:                        description: Configures the value to fill in for missing time buckets. This also works generally as COALESCE over non-empty time buckets.                        type: string                    type:                        description: 'Measure calculation type: "simple" for basic aggregations, "derived" for calculations using other measures, or "time_comparison" for period-over-period analysis. Defaults to "simple" unless dependencies exist.'                        type: string                    valid_percent_of_total:                        description: Indicates whether percent-of-total values should be rendered for this measure.                        type: boolean                    window:                        anyOf:                            - description: 'Shorthand: `time` or `true` means time-partitioned, `all` means non-partitioned.'                              enum:                                - time                                - "true"                                - all                              type: string                            - additionalProperties: false                              description: Detailed window configuration for measure calculations, allowing control over partitioning, ordering, and frame definition.                              properties:                                frame:                                    description: Defines the window frame boundaries for calculations, specifying which rows are included in the window relative to the current row.                                    type: string                                order:                                    $ref: '#/definitions/field_selectors_properties'                                    description: Specifies the fields to order the window by, determining the sequence of rows within each partition.                                    type: string                                partition:                                    description: Controls whether the window is partitioned. When true, calculations are performed within each partition separately.                                    type: boolean                              type: object                        description: A measure window can be defined as a keyword string (e.g. 'time' or 'all') or an object with detailed window configuration. For more information, see the [window functions](/developers/build/metrics-view/measures/windows) documentation.                required:                    - name                type: object            type: array        model:            description: Refers to the model powering the dashboard (either model or table is required)            type: string        parent:            description: Refers to the parent metrics view from which this metrics view is derived. If specified, this will inherit properties from the parent metrics view            type: string        parent_dimensions:            $ref: '#/definitions/field_selector_properties'            description: Optional field selectors for dimensions to inherit from the parent metrics view.        parent_measures:            $ref: '#/definitions/field_selector_properties'            description: Optional field selectors for measures to inherit from the parent metrics view.        rollups:            description: Pre-aggregated rollup tables that can be used to accelerate queries. When a query's dimensions, measures, time grain, and time range match a rollup, the query is automatically routed to the rollup table instead of the base table.            items:                properties:                    data_time_range:                        description: Optional [rilltime](https://docs.rilldata.com/reference/time-syntax) expression describing the rollup's time coverage (e.g. `-1Y to now`, `-5Y to -1Y`). When set, Rill skips the `min`/`max` OLAP probe for this rollup and uses the declared bounds for coverage checks. The start must be bounded; `inf` and `earliest` are rejected. To declare full history, use a concrete early bound such as `-100Y to now` or omit this field to probe the table.                        type: string                    database:                        description: Refers to the database to use in the OLAP engine                        type: string                    database_schema:                        description: Refers to the schema to use in the OLAP engine                        type: string                    dimensions:                        $ref: '#/definitions/field_selector_properties'                        description: Optional field selectors for dimensions to include in the rollup from the base metrics view. If not specified, all dimensions are included.                    measures:                        $ref: '#/definitions/field_selector_properties'                        description: Optional field selectors for measures to include in the rollup from the base metrics view. If not specified, all measures are included.                    model:                        description: Refers to the model or table powering the rollup (required)                        type: string                    time_grain:                        description: 'The time grain of the rollup (required). Valid values are: millisecond, second, minute, hour, day, week, month, quarter, year'                        type: string                    time_zone:                        description: IANA timezone of the rollup table (e.g. America/New_York). For day+ grains, queries are only routed to the rollup if the query timezone matches.                        type: string                required:                    - model                    - time_grain                type: object            type: array        security:            $ref: '#/definitions/security_policy_properties'            description: Defines a security policy for the dashboard        smallest_time_grain:            description: 'Refers to the smallest time granularity the user is allowed to view. The valid values are: millisecond, second, minute, hour, day, week, month, quarter, year'            type: string        table:            description: Refers to the table powering the dashboard, should be used instead of model for dashboards create from external OLAP tables (either table or model is required)            type: string        timeseries:            description: Refers to the timestamp column from your model that will underlie x-axis data in the line charts. If not specified, the line charts will not appear            type: string        type:            const: metrics_view            description: Refers to the resource type and must be `metrics_view`            type: string        version:            description: The version of the metrics view schema            type: string        watermark:            description: A SQL expression that tells us the max timestamp that the measures are considered valid for. Usually does not need to be overwritten            type: string      title: Properties      type: object    - $ref: '#/definitions/common_properties'description: In your Rill project directory, create a metrics view, `<metrics_view>.yaml`, file in the `metrics` directory. Rill will ingest the metric view definition next time you run `rill start`.id: metrics-viewstitle: Metrics View YAMLtype: object

allOf:    - properties:        ai_instructions:            description: Extra instructions for [AI agents](/guide/ai/mcp). Used to guide natural language question answering and routing.            type: string        annotations:            description: Used to define annotations that can be displayed on charts            items:                properties:                    connector:                        description: Refers to the connector to use for the annotation                        type: string                    database:                        description: Refers to the database to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified                        type: string                    database_schema:                        description: Refers to the schema to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified                        type: string                    measures:                        $ref: '#/definitions/field_selector_properties'                        description: Specifies which measures to apply the annotation to. Applies to all measures if not specified                    model:                        description: Refers to the model powering the annotation (either table or model is required). The model must have 'time' and 'description' columns. Optional columns include 'time_end' for range annotations and 'grain' to specify when the annotation should appear based on dashboard grain level.                        type: string                    name:                        description: A stable identifier for the annotation. Defaults to model or table names when not specified                        type: string                    table:                        description: Refers to the table powering the annotation, should be used instead of model for annotations from external OLAP tables (either table or model is required)                        type: string                type: object            type: array        cache:            description: |                Enable caching of query results for metrics views backed by externally-managed tables (e.g. in Snowflake, BigQuery). These settings have no effect for metrics views backed by Rill models (where queries are automatically cached and invalidated when the model is refreshed).                Each cache entry is keyed by a hash of the query combined with the latest result of `key_sql`. Cached results stay valid as long as `key_sql` returns the same value; when its result changes, prior results become unreachable. `key_sql` itself runs at most once per `key_ttl`, decoupling freshness checks from query traffic.                Example: a `key_sql` of `SELECT MAX(updated_at) FROM orders` with `key_ttl: 5m` checks for new data every 5 minutes but only invalidates cached results when new data has actually landed.            properties:                enabled:                    description: Whether to cache query results for this metrics view. Defaults to false for metrics views backed by externally-managed tables and to true for metrics views backed by a Rill model.                    type: boolean                key_sql:                    description: SQL returning a single value used in the cache key, typically a max timestamp, version, or row count. Cached results are invalidated when this value changes. Optional; defaults to the metrics view's watermark expression (which itself defaults to `MAX(<time dimension>)`).                    type: string                key_ttl:                    description: How often `key_sql` is re-evaluated, as a Go duration string (e.g. `30s`, `5m`, `1h`). The previous result is reused between evaluations. Defaults to `60s`.                    type: string                timestamps_ttl:                    description: TTL for caching the min/max timestamp queries used to populate a metrics view's rollups. Only takes effect when the metrics view has rollups defined and query result caching (`enabled`) is off — otherwise rollup timestamps are cached alongside other query results under `key_ttl`. Go duration string (e.g. `5m`). Defaults to `5m`.                    type: string            type: object        connector:            description: Refers to the connector type for the metrics view, see [OLAP engines](/developers/build/connectors/olap) for more information            type: string        data_time_range:            description: Optional [rilltime](https://docs.rilldata.com/reference/time-syntax) expression describing the base table's time coverage (e.g. `-5Y to now`). When set, Rill skips the `min`/`max` OLAP probe for the base table and uses the declared bounds for coverage checks. The start must be bounded; `inf` and `earliest` are rejected. To declare full history, use a concrete early bound such as `-100Y to now` or omit this field to probe the table.            type: string        database:            description: Refers to the database to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified            type: string        database_schema:            description: Refers to the schema to use in the OLAP engine (to be used in conjunction with table). Otherwise, will use the default database or schema if not specified            type: string        description:            description: Refers to the description for the metrics view            type: string        dimensions:            description: Relates to exploring segments or dimensions of your data and filtering the dashboard            items:                anyOf:                    - required:                        - column                    - required:                        - expression                properties:                    column:                        description: A categorical column.                        type: string                    description:                        description: a freeform text description of the dimension                        type: string                    display_name:                        description: a display name for your dimension                        type: string                    expression:                        description: A non-aggregate expression such as string_split(domain, '.'). One of column and expression is required, but cannot have both at the same time.                        type: string                    lookup_default_expression:                        description: an optional SQL expression used as a fallback value when no match is found in the dictionary (maps to `dictGetOrDefault`)                        type: string                    lookup_key_column:                        description: the primary key column in the lookup dictionary that corresponds to the dimension's `column` in the fact table                        type: string                    lookup_table:                        description: The name of a ClickHouse dictionary to use for query-time lookups. Use `database.dictionary_name` for dictionaries in a non-default database. All three `lookup_*` fields (`lookup_table`, `lookup_key_column`, `lookup_value_column`) must be specified together. See [Query-Time Joins](/developers/build/metrics-view/dimensions/lookup) for details.                        type: string                    lookup_value_column:                        description: the attribute column in the lookup dictionary whose values will be displayed for this dimension                        type: string                    name:                        description: a stable identifier for the dimension                        type: string                    tags:                        description: optional list of tags for categorizing the dimension (defaults to empty)                        items:                            type: string                        type: array                    type:                        description: 'Dimension type: "geo" for geospatial dimensions, "time" for time dimensions, or "categorical" for categorical dimensions. Default is undefined and the type will be inferred instead.'                        type: string                    unnest:                        description: If true, allows multi-valued dimensions to be unnested (such as lists), and filters will automatically switch to "contains" instead of exact match.                        type: boolean                    uri:                        description: Enable if your dimension is a clickable URL to enable single-click navigation (boolean or valid SQL expression).                        type:                            - string                            - boolean                type: object            type: array        display_name:            description: Refers to the display name for the metrics view            type: string        explore:            $ref: '#/definitions/explore_properties'            description: Defines an optional inline explore view for the metrics view. If not specified a default explore will be emitted unless `skip` is set to true.            required:                - type        first_day_of_week:            description: Refers to the first day of the week for time grain aggregation (for example, Sunday instead of Monday). The valid values are 1 through 7 where Monday=1 and Sunday=7            type: integer        first_month_of_year:            description: Refers to the first month of the year for time grain aggregation. The valid values are 1 through 12 where January=1 and December=12            type: integer        max_query_time_range:            description: The maximum time span any single query against this metrics view may cover, expressed as an ISO 8601 duration with day-or-larger granularity (e.g. `P90D`, `P3M`, `P1Y`). Sub-day durations such as `PT12H` are not supported. Applies independently to the primary and comparison time ranges. If unset, no limit is enforced.            type: string        measures:            description: Used to define the numeric aggregates of columns from your data model            items:                properties:                    description:                        description: a freeform text description of the measure                        type: string                    display_name:                        description: the display name of your measure.                        type: string                    expression:                        description: a combination of operators and functions for aggregations                        type: string                    format_d3:                        description: 'Controls the formatting of this measure using a [d3-format](https://d3js.org/d3-format) string. If an invalid format string is supplied, the measure will fall back to `format_preset: humanize`. A measure cannot have both `format_preset` and `format_d3`. If neither is provided, the humanize preset is used by default. Example: `format_d3: ".2f"` formats using fixed-point notation with two decimal places. Example: `format_d3: ",.2r"` formats using grouped thousands with two significant digits. (optional)'                        type: string                    format_d3_locale:                        description: |                            Locale configuration passed through to D3, enabling changes to the currency symbol and other formatting options. For details, see the docs for D3's formatLocale.                              ```yaml                              format_d3: "$,"                              format_d3_locale:                                grouping: [3, 2]                                currency: ["₹", ""]                              ```                        properties:                            currency:                                description: the currency symbol                                type: array                            grouping:                                description: Grouping for the currency symbol.                                type: array                        type: object                    format_preset:                        description: |                            Controls the formatting of this measure using a predefined preset. Measures cannot have both `format_preset` and `format_d3`. If neither is supplied, the measure will be formatted using the `humanize` preset by default.
                              Available options:                              - `humanize`: Round numbers into thousands (K), millions(M), billions (B), etc.                              - `none`: Raw output.                              - `currency_usd`: Round to 2 decimal points with a dollar sign ($).                              - `currency_eur`: Round to 2 decimal points with a euro sign (€).                              - `percentage`: Convert a rate into a percentage with a % sign.                              - `interval_ms`: Convert milliseconds into human-readable durations like hours (h), days (d), years (y), etc. (optional)                        type: string                    label:                        description: a label for your measure, deprecated use display_name                        type: string                    lower_is_better:                        description: When true, decreases in this measure are favorable (e.g. bounce rate, latency, error count). UI surfaces that render comparison deltas (KPIs, big numbers, leaderboards, pivot tables, time-series tooltips) swap their positive/negative coloring accordingly.                        type: boolean                    name:                        description: a stable identifier for the measure                        type: string                    per:                        $ref: '#/definitions/field_selectors_properties'                        description: Dimensions to partition the measure by.                    requires:                        $ref: '#/definitions/field_selectors_properties'                        description: Uses an available measure or dimension in your metrics view to set a required parameter. Cannot be used with simple measures. See [referencing measures](/developers/build/metrics-view/measures/referencing) for more information.                    tags:                        description: optional list of tags for categorizing the measure (defaults to empty)                        items:                            type: string                        type: array                    treat_nulls_as:                        description: Configures the value to fill in for missing time buckets. This also works generally as COALESCE over non-empty time buckets.                        type: string                    type:                        description: 'Measure calculation type: "simple" for basic aggregations, "derived" for calculations using other measures, or "time_comparison" for period-over-period analysis. Defaults to "simple" unless dependencies exist.'                        type: string                    valid_percent_of_total:                        description: Indicates whether percent-of-total values should be rendered for this measure.                        type: boolean                    window:                        anyOf:                            - description: 'Shorthand: `time` or `true` means time-partitioned, `all` means non-partitioned.'                              enum:                                - time                                - "true"                                - all                              type: string                            - additionalProperties: false                              description: Detailed window configuration for measure calculations, allowing control over partitioning, ordering, and frame definition.                              properties:                                frame:                                    description: Defines the window frame boundaries for calculations, specifying which rows are included in the window relative to the current row.                                    type: string                                order:                                    $ref: '#/definitions/field_selectors_properties'                                    description: Specifies the fields to order the window by, determining the sequence of rows within each partition.                                    type: string                                partition:                                    description: Controls whether the window is partitioned. When true, calculations are performed within each partition separately.                                    type: boolean                              type: object                        description: A measure window can be defined as a keyword string (e.g. 'time' or 'all') or an object with detailed window configuration. For more information, see the [window functions](/developers/build/metrics-view/measures/windows) documentation.                required:                    - name                type: object            type: array        model:            description: Refers to the model powering the dashboard (either model or table is required)            type: string        parent:            description: Refers to the parent metrics view from which this metrics view is derived. If specified, this will inherit properties from the parent metrics view            type: string        parent_dimensions:            $ref: '#/definitions/field_selector_properties'            description: Optional field selectors for dimensions to inherit from the parent metrics view.        parent_measures:            $ref: '#/definitions/field_selector_properties'            description: Optional field selectors for measures to inherit from the parent metrics view.        rollups:            description: Pre-aggregated rollup tables that can be used to accelerate queries. When a query's dimensions, measures, time grain, and time range match a rollup, the query is automatically routed to the rollup table instead of the base table.            items:                properties:                    data_time_range:                        description: Optional [rilltime](https://docs.rilldata.com/reference/time-syntax) expression describing the rollup's time coverage (e.g. `-1Y to now`, `-5Y to -1Y`). When set, Rill skips the `min`/`max` OLAP probe for this rollup and uses the declared bounds for coverage checks. The start must be bounded; `inf` and `earliest` are rejected. To declare full history, use a concrete early bound such as `-100Y to now` or omit this field to probe the table.                        type: string                    database:                        description: Refers to the database to use in the OLAP engine                        type: string                    database_schema:                        description: Refers to the schema to use in the OLAP engine                        type: string                    dimensions:                        $ref: '#/definitions/field_selector_properties'                        description: Optional field selectors for dimensions to include in the rollup from the base metrics view. If not specified, all dimensions are included.                    measures:                        $ref: '#/definitions/field_selector_properties'                        description: Optional field selectors for measures to include in the rollup from the base metrics view. If not specified, all measures are included.                    model:                        description: Refers to the model or table powering the rollup (required)                        type: string                    time_grain:                        description: 'The time grain of the rollup (required). Valid values are: millisecond, second, minute, hour, day, week, month, quarter, year'                        type: string                    time_zone:                        description: IANA timezone of the rollup table (e.g. America/New_York). For day+ grains, queries are only routed to the rollup if the query timezone matches.                        type: string                required:                    - model                    - time_grain                type: object            type: array        security:            $ref: '#/definitions/security_policy_properties'            description: Defines a security policy for the dashboard        smallest_time_grain:            description: 'Refers to the smallest time granularity the user is allowed to view. The valid values are: millisecond, second, minute, hour, day, week, month, quarter, year'            type: string        table:            description: Refers to the table powering the dashboard, should be used instead of model for dashboards create from external OLAP tables (either table or model is required)            type: string        timeseries:            description: Refers to the timestamp column from your model that will underlie x-axis data in the line charts. If not specified, the line charts will not appear            type: string        type:            const: metrics_view            description: Refers to the resource type and must be `metrics_view`            type: string        version:            description: The version of the metrics view schema            type: string        watermark:            description: A SQL expression that tells us the max timestamp that the measures are considered valid for. Usually does not need to be overwritten            type: string      title: Properties      type: object    - $ref: '#/definitions/common_properties'description: In your Rill project directory, create a metrics view, `<metrics_view>.yaml`, file in the `metrics` directory. Rill will ingest the metric view definition next time you run `rill start`.id: metrics-viewstitle: Metrics View YAMLtype: object

Source and attribution

Source:rilldata/agent-skillsinskills/rill-metrics-viewat commita0a2116

License: No license

Content belongs to its original authors. SourceWeft indexes it from a public repository.

Report or request removal

Rill Metrics View

Instructions for developing a metrics view in Rill

Introduction

Core Concepts

Table source

Timeseries

Dimensions

Measures

Best practices for dimensions and measures

Inline explore

Full Example

Security Policies

Basic access control

Row-level security

Complex row filters

Hiding dimensions and measures

Advanced Features

Annotations

Unnest for array dimensions

Cache configuration

Tags on dimensions and measures

Rollups

Dialect-Specific Notes

DuckDB

ClickHouse

Druid

Reference documentation

Source and attribution

More from rilldata/agent-skills

Rill Theme

Rill Explore

Rill Canvas

Rill Analysis

More in Data & Analytics

Spanner Basics

Gke Cost Analysis

Datalineage Bigquery Asset Impact Analysis

Bigquery Troubleshooting

Bigquery Optimization

Bigquery Bigframes