Metric Views

This guide explains how to define and organize metric views in your Kelp project. Metric views in Databricks provide a consistent way to define business metrics and KPIs that can be used across analytics, dashboards, and reporting tools.

What Are Metric Views?

Metric views in Databricks are semantic layer objects that define:

• Fields - Grouping attributes (e.g., customer, region, time period)
• Measures - Aggregated metrics (e.g., revenue, customer count, conversion rate)
• Source tables - Underlying tables containing measure and field data

They provide a single source of truth for business metrics across your organization, enabling consistent reporting and analytics.

Configure Paths and Defaults

Add dedicated paths for metric views to kelp_project.yml:

kelp_project:
  metrics_path: "./kelp_metadata/metrics"
  metric_views:
    +catalog: ${ catalog }
    +schema: ${ metric_schema }
    +tags:
      kelp_managed: ""
      layer: analytics

vars:
  catalog: analytics_prod
  metric_schema: metrics

The + prefix applies defaults to all metric views. You can create nested hierarchies for different metric domains:

kelp_project:
  metrics_path: "./kelp_metadata/metrics"
  metric_views:
    +catalog: ${ catalog }
    +schema: ${ metric_schema }
    customer:
      +tags:
        domain: customer
    product:
      +tags:
        domain: product
    finance:
      +tags:
        domain: finance

Define Metric Views

The definition block follows the Databricks metric view YAML reference verbatim and is passed through to DDL as-is - including its comment. The only Kelp extension is an optional tags map on field/measure entries, which Kelp manages via ALTER statements and strips from the DDL body.

The spec's dimensions synonym is accepted but canonicalized to fields when definitions are loaded (Unity Catalog currently returns stored definitions with dimensions even when created with fields, so canonicalizing keeps local and remote state comparable).

Basic Structure

kelp_metric_views:
  - name: customer_revenue_metrics
    catalog: ${ catalog }
    schema: ${ metric_schema }

    definition:
      version: 1.1
      source: ${ catalog }.gold.customer_orders
      comment: Customer-level revenue metrics by period

      measures:
        - name: total_revenue
          comment: Total revenue generated
          expr: SUM(amount)
        - name: transaction_count
          comment: Number of transactions
          expr: COUNT(*)
        - name: avg_transaction_value
          comment: Average transaction value
          expr: AVG(amount)

      fields:
        - name: customer_id
          expr: customer_id
        - name: order_date
          expr: order_date
        - name: region
          expr: region

    tags:
      domain: customer
      sla: high

Key Components:

• name - Metric view identifier
• catalog - Unity Catalog name
• schema - Schema name
• definition - Metric view specification per the Databricks YAML reference
• version - Specification version (required)
• source - Underlying table, view, or SQL query (required)
• comment - Documentation for the metric view
• measures - Aggregated metrics (with name, comment, SQL expression)
• fields - Grouping attributes (with name and SQL expression)
• tags - Metadata tags (Kelp-managed, also allowed per field/measure entry)

Revenue Metrics Example

kelp_metric_views:
  - name: monthly_revenue
    catalog: analytics
    schema: metrics

    definition:
      version: 1.1
      source: analytics.gold.orders_with_customers
      comment: Monthly revenue across all products

      measures:
        - name: gross_revenue
          comment: Total revenue before discounts
          expr: SUM(gross_amount)
        - name: net_revenue
          comment: Revenue after discounts and returns
          expr: SUM(net_amount)
        - name: discount_amount
          comment: Total discounts given
          expr: SUM(gross_amount - net_amount)
        - name: order_count
          comment: Number of orders
          expr: COUNT(DISTINCT order_id)

      fields:
        - name: year_month
          expr: TO_DATE(order_date, 'YYYY-MM')
        - name: product_category
          expr: product_category
        - name: region
          expr: customer_region

    tags:
      domain: financial
      frequency: monthly

Customer Cohort Metrics

kelp_metric_views:
  - name: customer_cohort_metrics
    catalog: ${ catalog }
    schema: ${ metric_schema }

    definition:
      version: 1.1
      source: ${ catalog }.gold.customer_cohorts
      comment: Customer metrics grouped by acquisition cohort

      measures:
        - name: customer_count
          expr: COUNT(DISTINCT customer_id)
        - name: total_ltv
          comment: Total lifetime value
          expr: SUM(lifetime_value)
        - name: avg_ltv
          comment: Average lifetime value per customer
          expr: AVG(lifetime_value)
        - name: churn_rate
          comment: Percentage of churned customers
          expr: SUM(CASE WHEN churned THEN 1 ELSE 0 END) / COUNT(*) * 100

      fields:
        - name: acquisition_cohort
          comment: Customer acquisition month-year
          expr: DATE_TRUNC('MONTH', acquisition_date)
        - name: country
          expr: country
        - name: plan_type
          expr: subscription_plan

    tags:
      domain: customer
      sla: critical

Organizing Metric Views

Create a clear structure for managing metric views:

kelp_metadata/metrics/
├── customer_metrics.yml      # Customer-related metrics
├── product_metrics.yml       # Product/SKU metrics
├── financial_metrics.yml     # Revenue and profitability
├── operational_metrics.yml   # KPIs and performance metrics
└── by_domain/
    ├── customer/
    │   └── cohort_metrics.yml
    ├── product/
    │   └── sales_metrics.yml
    └── financial/
        └── bookings_metrics.yml

Group related metrics by business domain for maintainability.

Using Metric Views in Analysis

SQL Queries

Query metric views using standard SQL with aggregation:

SELECT 
  order_date,
  region,
  SUM(total_revenue) as total_revenue,
  SUM(transaction_count) as total_transactions,
  AVG(avg_transaction_value) as avg_transaction
FROM analytics_prod.metrics.customer_revenue_metrics
GROUP BY order_date, region
ORDER BY order_date DESC, total_revenue DESC;

Databricks SQL

Use metric views in Databricks SQL dashboards for reporting:

SELECT 
  acquisition_cohort,
  plan_type,
  customer_count,
  total_ltv,
  churn_rate
FROM analytics_prod.metrics.customer_cohort_metrics
WHERE acquisition_cohort >= DATE_TRUNC('YEAR', CURRENT_DATE())
ORDER BY total_ltv DESC;

Python/PySpark

Access metric views from PySpark code:

import kelp.pipelines as kp
from pyspark.sql import SparkSession

spark = SparkSession.active()

# Read metric view as DataFrame
df = spark.read.table("analytics_prod.metrics.customer_revenue_metrics")

# Use metric view in transformations
monthly_summary = (
    spark.read.table(kp.ref("customer_revenue_metrics"))
    .groupBy("order_date", "region")
    .agg({"total_revenue": "sum", "transaction_count": "sum", "avg_transaction_value": "avg"})
    .orderBy("order_date")
)

monthly_summary.display()

Syncing Metric Views to Catalog

Metric views must be synced to Unity Catalog after they're defined in your metadata.

Sync All Metric Views

import kelp.catalog as kc

kc.init("kelp_project.yml", target="prod")

for query in kc.sync_metric_views():
    print(f"Executing: {query}")
    spark.sql(query)

Sync Specific Metric Views

for query in kc.sync_metric_views(view_names=["customer_revenue_metrics", "monthly_revenue"]):
    spark.sql(query)

Automatic Syncing with Catalog

When using sync_catalog(), metric views are synced after tables (which they depend on) but before ABAC policies:

for query in kc.sync_catalog():
    spark.sql(query)
# Order: tables → metric views → ABAC policies

Versioning and Evolution

Adding New Measures

Add new measures without breaking existing queries:

definition:
  measures:
    # Existing measures
    - name: total_revenue
      expr: SUM(amount)

    # New measure for reporting
    - name: revenue_growth_pct
      comment: Period-over-period revenue growth percentage
      expr: (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY date)) / LAG(SUM(amount)) OVER (ORDER BY date) * 100

Renaming Fields

Use aliases to support field renames without breaking downstream usage:

definition:
  fields:
    - name: order_month           # New name
      expr: DATE_TRUNC('MONTH', order_date)

    - name: order_date_month     # Old name (deprecated)
      expr: DATE_TRUNC('MONTH', order_date)

Deprecating Metrics

Document deprecated metrics in comments:

definition:
  measures:
    - name: old_metric
      comment: "DEPRECATED: Use new_metric instead"
      expr: SUM(legacy_column)

    - name: new_metric
      comment: "Improved calculation using updated data"
      expr: SUM(correct_column)

Best Practices

1. Document clearly - Add comments explaining what each measure and field represents.

2. Use consistent naming - Follow naming conventions for measures and fields (e.g., snake_case).

3. Define at the right layer - Base metric views on gold/aggregated tables, not raw data.

4. Version your metrics - Track metric definition changes in git for auditability.

5. Test expressions - Validate metric calculations against known values before deployment.

6. Organize by domain - Group related metrics by business area (customer, product, financial).

7. Use appropriate tags - Tag metric views with domain, SLA, and frequency information:

tags:
  domain: customer
  sla: critical
  frequency: daily
  owner: analytics-team

1. Handle NULL values - Consider NULL handling in measure expressions:

measures:
  - name: safe_avg
    expr: AVG(CASE WHEN value IS NOT NULL THEN value ELSE NULL END)

1. Cache expensive queries - Use materialized metric views for complex calculations.

2. Monitor performance - Track metric view query performance and optimize source tables as needed.

Common Patterns

Funnel Metrics

Track progression through multi-stage processes:

definition:
  measures:
    - name: visits
      expr: COUNT(DISTINCT session_id)
    - name: signups
      expr: COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END)
    - name: first_purchase
      expr: COUNT(DISTINCT CASE WHEN event = 'first_purchase' THEN user_id END)
    - name: signup_conversion
      expr: COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) / COUNT(DISTINCT session_id) * 100
    - name: ftp_conversion
      expr: COUNT(DISTINCT CASE WHEN event = 'first_purchase' THEN user_id END) / COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) * 100

Running Totals

Cumulative metrics over time:

measures:
  - name: cumulative_revenue
    expr: SUM(revenue) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  - name: mau
    comment: Monthly Active Users
    expr: COUNT(DISTINCT user_id)

Year-over-Year Comparisons

measures:
  - name: revenue
    expr: SUM(amount)
  - name: revenue_prior_year
    expr: SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE()) - 1 THEN amount ELSE 0 END)
  - name: yoy_growth
    expr: (SUM(amount) - SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE()) - 1 THEN amount ELSE 0 END)) / SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE()) - 1 THEN amount ELSE 0 END) * 100

See Also

• Project Configuration - Configuring metric paths and hierarchies
• Sync Metadata with Your Catalog - Syncing metric views to Unity Catalog
• Spark Declarative Pipelines - Using metric views in SDP
• Databricks Metric View YAML Reference