Metric YAML Specification

The YAML specification defines the structure and behavior of metrics in Codd AI. Each metric is represented as a YAML document that describes its calculations, dimensions, measures, and data requirements. This specification enables Codd AI to automatically generate optimized SQL queries and ensure consistent metric execution across your organization.

Understanding YAML Metrics

When you view a metric in the Metrics interface and switch to the YAML tab, you see the complete specification that drives the metric's behavior. This YAML structure:

  • Defines the calculation logic: Specifies exactly how the metric is computed from your data
  • Controls dimensional analysis: Determines which dimensions can be used for grouping and filtering
  • Manages data relationships: Describes how tables are joined and related
  • Ensures consistency: Provides a single source of truth for metric definitions

Complete Metric YAML Template

# ===== METRIC CLASSIFICATION =====
category: <category_name>
category_description: <description_of_category>

# ===== CORE METRIC DEFINITION =====
metric:
 # Basic Identification
 name: <metric_identifier>
 title:
 en: <human_readable_title>
 description:
 en: <business_description_of_metric>

 # Metric Classification
 metric_definition:
 primary: <metric_type>
 subtype: <specific_aggregation>
 driver_table: <primary_fact_table>
 calculation_expression: <complete_sql_expression>

 # Measure Definitions
 measures:
 - name: <measure_identifier>
  table: <source_table>
  data_type: <data_type>
  unit_of_measure: <business_unit>
  aggregation_function: <aggregation_method>
  calculation_expression: <field_calculation>

 # Dimensional Analysis
 dimensions:
 - name: <dimension_name>
  type: <dimension_type>
  table: <dimension_table>
  data_type: <column_data_type>
  accessible_via: <table.column_path>
  is_time_dimension: <boolean>
  hierarchy_levels: [<optional_hierarchy>]

 # Complexity Assessment
 complexity:
 time_based: <boolean>
 has_fan_out: <boolean>
 has_chasm_trap: <boolean>
 rollup_behavior: <aggregation_behavior>
 max_recommended_dimensions: <integer>
 supports_multiple_dimensions: <boolean>

 # Data Requirements
 data_requirements:
 fact_tables:
  - <primary_fact_table>
 dimension_tables:
  - <dimension_table_1>
  - <dimension_table_n>
 grain:
  - <atomic_level_definition>
 join_path:
  - from_table: <source_table>
  to_table: <target_table>
  from_column: <foreign_key>
  to_column: <primary_key>
  relationship: <relationship_type>
 filters:
  - column: <table.column>
  operator: <filter_operator>
  value: <filter_value>
  reason: <business_justification>
 default_sort:
  - column: <column_name>
  direction: <asc_or_desc>
  priority: <sort_order>

Key Components

Category & Identification

  • category: Groups related metrics (e.g., Finance, Operations, Customer)
  • name: Unique system identifier using snake_case (e.g., total_revenue, customer_count)
  • title: Human-readable name displayed in the UI
  • description: Business explanation of what the metric measures

Metric Definition

  • primary: Main calculation type (simple, ratio, comparison, statistical)
  • subtype: Specific aggregation method (sum, average, count, etc.)
  • driver_table: Primary fact table containing the base data
  • calculation_expression: Complete SQL expression for the metric

Measures & Dimensions

  • measures: The quantitative values being calculated (amounts, counts, averages)
  • dimensions: The categorical attributes for grouping and filtering
  • aggregation_function: How values are combined (sum, count, avg, min, max)
  • hierarchy_levels: Drill-down paths (e.g., year → quarter → month → day)

Complexity & Data Requirements

  • rollup_behavior: Whether metrics can be aggregated across dimensions
  • supports_multiple_dimensions: Controls multi-dimensional analysis capability
  • join_path: Defines table relationships and join conditions
  • filters: Business rules for data inclusion/exclusion

Critical Design Decisions

When to Support Multiple Dimensions

SUPPORT Multiple Dimensions:

  • SUM, COUNT, MIN, MAX aggregations
  • Additive rollup behavior
  • No complex ratio calculations
  • Values can be safely summed across hierarchies
supports_multiple_dimensions: true
max_recommended_dimensions: 5
rollup_behavior: additive

RESTRICT to Single Dimension:

  • AVERAGE, percentage, ratio calculations
  • Non-additive measures
  • Complex derived calculations
  • Requires recalculation at each level
supports_multiple_dimensions: false
max_recommended_dimensions: 1
rollup_behavior: recalculation_required

Example Metric YAML

category: Revenue
category_description: Revenue and sales metrics

metric:
 name: total_monthly_revenue
 title:
  en: Total Monthly Revenue
 description:
  en: Sum of all revenue from completed orders in a month

 metric_definition:
  primary: simple
  subtype: sum
  driver_table: fact_sales
  calculation_expression: SUM(fact_sales.order_amount)

 measures:
  - name: order_amount
   table: fact_sales
   data_type: decimal
   unit_of_measure: currency
   aggregation_function: sum
   calculation_expression: fact_sales.order_amount

 dimensions:
  - name: order_date
   type: temporal
   table: dim_date
   data_type: date
   accessible_via: dim_date.date_key
   is_time_dimension: true
   hierarchy_levels: [year, quarter, month]

  - name: product_category
   type: categorical
   table: dim_product
   data_type: string
   accessible_via: dim_product.category_name
   is_time_dimension: false

 complexity:
  time_based: true
  has_fan_out: false
  has_chasm_trap: false
  rollup_behavior: additive
  max_recommended_dimensions: 5
  supports_multiple_dimensions: true

 data_requirements:
  fact_tables:
   - fact_sales
  dimension_tables:
   - dim_date
   - dim_product
  grain:
   - fact_sales.order_id
  join_path:
   - from_table: fact_sales
    to_table: dim_date
    from_column: order_date_key
    to_column: date_key
    relationship: many_to_one
   - from_table: fact_sales
    to_table: dim_product
    from_column: product_key
    to_column: product_key
    relationship: many_to_one
  filters:
   - column: fact_sales.order_status
    operator: in
    value: "completed,shipped"
    reason: "Only include fulfilled orders"

This YAML specification provides the foundation for creating mathematically sound, business-relevant metrics that scale effectively across your analytics platform.