Menu
Detailed instructions and examples for developing metrics view resources in Rill
The review-first prompt is selected by default. You can switch to a direct command or download a local copy.
Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
npx skills add https://github.com/rilldata/clickhouse-system-analytics --skill rill-metrics-viewThe command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
Based on SOC occupation classification
Showing SKILL.md
| name | rill-metrics-view |
| description | Detailed instructions and examples for developing metrics view resources in Rill |
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:
The model: property specifies the underlying table that powers the metrics view. It can reference:
model: events)# Referencing a model in the project
model: events
# Referencing an external table (connector defaults to project's default OLAP)
connector: clickhouse # Optional: specify if different from default
model:
Note: The table: property is a legacy alias for referencing external tables. Always prefer model: in new metrics views.
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.
timeseries: event_time
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:
timeseries: event_time
smallest_time_grain: hour # Minimum granularity users can select
first_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 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:
Define dimensions using either a direct column reference or a SQL expression:
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.
Measures are aggregation expressions that compute numeric values when grouped by dimensions. They must use aggregate functions like SUM(), COUNT(), AVG(), MIN(), MAX().
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 numberhumanize: Round to K, M, B (e.g., 1.2M)currency_usd: Dollar format with 2 decimals ($1,234.56)currency_eur: Euro formatpercentage: Multiply by 100 and add % signinterval_ms: Convert milliseconds to human-readable durationFor custom formatting, use format_d3 with a d3-format string:
format_d3: "$,.2f" # $1,234.56
format_d3: ".1%" # 12.3%
format_d3: ",.0f" # 1,235 (rounded, with thousands separator)
Naming conventions:
snake_case for the name field (e.g., total_revenue, unique_users)display_name and description if they provide meaningful context beyond what name conveys (display names auto-humanize from the name by default)Getting started with measures:
COUNT(*) measure as a baseline (e.g., total_records or total_events)SUM() measures for numeric columns that represent quantities or valueshumanize as the default format preset unless the data has a specific format requirementCOUNT, SUM, AVG, MIN, MAX aggregationsDimension selection:
Timeseries:
dimensions:timeseries:When you create a metrics view, Rill automatically generates an explore dashboard with the same name, exposing all dimensions and measures. To customize the explore (you usually should not need to), add an explore: block:
explore:
display_name: Sales Dashboard
defaults:
time_range: P7D
measures:
- total_revenue
- order_count
Legacy behavior: Files with version: 1 do NOT auto-generate an explore. Omit version: in new metrics views to get the auto-generated explore.
Here is a complete, annotated metrics view:
# metrics/orders.yaml
type: metrics_view
# Display metadata
display_name: Orders Analytics
description: Analyze order performance by various dimensions
# Data source - references the 'orders' model in the project
model: orders
# Time column for time-series charts and filtering
timeseries: order_date
smallest_time_grain: day
# Dimensions for grouping and filtering
dimensions:
- 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 aggregation
measures:
- 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
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.
The access: property controls whether users can view the metrics view at all:
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.
The row_filter: property restricts which rows a user can see. It's a SQL expression that references user attributes via templating:
security:
access: true
row_filter: domain = '{{ .user.domain }}'
Common user attributes:
{{ .user.email }}: User's email address{{ .user.domain }}: Email domain (e.g., "acme.com"){{ .user.admin }}: Boolean admin flagThe row filter should use the SQL syntax of the metrics view's model, and can reference other tables in the model's connector.
Use logical operators for sophisticated access patterns:
security:
access: true
row_filter: >
{{ .user.admin }}
OR '{{ .user.domain }}' = 'acme.com'
{{ if hasKey .user "tenant_id" }}
OR tenant_id = '{{ .user.tenant_id }}'
{{ end }}
The exclude: property conditionally hides specific dimensions or measures from certain users:
security:
access: true
exclude:
- if: "NOT {{ .user.admin }}"
names:
- cost_per_acquisition # Hide sensitive cost data from non-admins
- internal_notes
Annotations overlay contextual information (like events or milestones) on time-series charts:
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.)
When a column contains arrays, use unnest: true to flatten it at query time:
dimensions:
- name: tags
display_name: Tags
column: tags
unnest: true # Allows filtering by individual array elements
Configure caching for slow metrics views that use external tables:
cache:
enabled: true
key_ttl: 5m
key_sql: SELECT MAX(updated_at) FROM orders
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.
SQL expressions in dimensions and measures use the underlying OLAP database's dialect.
DuckDB is the default OLAP engine for local development.
Conditional aggregation with FILTER:
# DuckDB supports FILTER clause for conditional aggregation
expression: COUNT(*) FILTER (WHERE status = 'completed')
ClickHouse is recommended for production workloads with large datasets.
Conditional aggregation:
# ClickHouse uses IF or CASE inside aggregations
expression: countIf(status = 'completed')
expression: sumIf(revenue, status = 'completed')
Date functions:
expression: toYear(order_date)
expression: toStartOfMonth(order_date)
expression: toYYYYMMDD(order_date)
Array functions:
expression: arrayJoin(tags) # Unnest arrays
Approximate distinct counts:
expression: APPROX_COUNT_DISTINCT_DS_HLL(user_id)
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
connector:
description: Refers to the connector type for the metrics view, see [OLAP engines](/developers/build/connectors/olap) for more information
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 categorial dimensions. Default is undefined and the type will be inferred instead'
type: string
unnest:
description: if true, allows multi-valued dimension 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 changing the currency symbol among other things. 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: the grouping of 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: for per dimensions
requires:
$ref: '#/definitions/field_selectors_properties'
description: using 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: used to configure what value to fill in for missing time buckets. This also works generally as COALESCING 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: a boolean indicating 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:
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-views
title: Metrics View YAML
type: object