Skip to content

Instantly share code, notes, and snippets.

@dbose

dbose/lightdash-macro-system.md

Last active July 7, 2025 00:02
Show Gist options
  • Save dbose/a410c396ddae58973906ce4bab407909 to your computer and use it in GitHub Desktop.
Save dbose/a410c396ddae58973906ce4bab407909 to your computer and use it in GitHub Desktop.
Download ZIP
Lightdash-Inspired Measure and Metric System for dbt-core
Raw
lightdash-macro-system.md

Design Document: Lightdash-Inspired Measure and Metric System for dbt-core

A unified metrics layer implementation leveraging dbt-core's meta properties and macro system

This design document presents a comprehensive approach to implementing a measure and metric system on dbt-core, inspired by Lightdash's architecture while working within dbt-core's constraints. The system uses YAML-based configurations through dbt's meta properties, combined with a macro-based SQL generation layer to create a powerful metrics framework.

Core Architecture Overview

The proposed system follows Lightdash's unified approach to metrics, where everything is considered a "metric" but distinguished by behavior: aggregate metrics perform SQL aggregations (sum, count, average), while non-aggregate metrics perform calculations on already-aggregated values. This distinction aligns perfectly with Lightdash's model while remaining implementable in dbt-core.

The architecture consists of three main components:

  1. YAML Configuration Layer: Defines metrics using dbt's meta properties
  2. Macro Processing Layer: Generates SQL from metric definitions
  3. Integration Layer: Enables external tool consumption via manifest.json

YAML Structure for Defining Measures and Metrics

Column-Level Metric Definition

version: 2
models:
  - name: orders
    description: "Orders fact table with transactional data"
    meta:
      # Model-level configuration
      metrics_config:
        enable_metrics: true
        default_time_dimension: order_date
      # Group definitions for organizing metrics
      group_details:
        revenue:
          label: "Revenue Metrics"
          description: "All revenue-related calculations"
        operations:
          label: "Operational Metrics"
          description: "Order processing and fulfillment metrics"
    
    columns:
      - name: order_id
        description: "Unique order identifier"
        meta:
          dimension:
            type: string
            hidden: false
          metrics:
            order_count:
              type: count_distinct
              label: "Total Orders"
              description: "Count of unique orders"
              groups: ["operations"]
              
      - name: revenue
        description: "Order revenue in USD"
        meta:
          dimension:
            type: number
            format: "currency"
          metrics:
            total_revenue:
              type: sum
              label: "Total Revenue"
              description: "Sum of all order revenue"
              format: "$#,##0.00"
              groups: ["revenue"]
              filters:
                - dimension: status
                  operator: "="
                  value: "completed"
            
            avg_order_value:
              type: average
              label: "Average Order Value"
              description: "Average revenue per order"
              format: "$#,##0.00"
              groups: ["revenue", "operations"]

Model-Level Metric Definition (Non-Aggregate)

models:
  - name: orders
    meta:
      metrics:
        # Non-aggregate metrics reference other metrics
        revenue_per_customer:
          type: number
          sql: ${total_revenue} / ${unique_customers}
          label: "Revenue per Customer"
          description: "Average revenue generated per unique customer"
          format: "$#,##0.00"
          groups: ["revenue"]
          
        is_high_value_day:
          type: boolean
          sql: ${total_revenue} > 10000
          label: "High Value Day"
          description: "Indicates if daily revenue exceeds $10,000"
          
        revenue_growth_rate:
          type: number
          sql: |
            CASE 
              WHEN ${previous_period_revenue} = 0 THEN NULL
              ELSE (${total_revenue} - ${previous_period_revenue}) / ${previous_period_revenue}
            END
          label: "Revenue Growth Rate"
          format: "0.00%"

Aligning with Lightdash's Config/Meta Elements

Comprehensive Metric Configuration Schema

metrics:
  metric_name:
    # Core Properties (Required)
    type: sum                          # Metric type (sum, count, average, etc.)
    
    # Display Properties (Optional)
    label: "User-Friendly Name"        # Display name in UI
    description: "Detailed explanation" # Metric documentation
    format: "$#,##0.00"               # Number formatting
    compact: thousands                 # Compact display (thousands, millions)
    round: 2                          # Decimal places
    
    # Organization (Optional)
    groups: ["group1", "group2"]      # Sidebar grouping
    hidden: false                     # Visibility control
    
    # Advanced Properties (Optional)
    sql: "custom SQL expression"      # Custom calculation logic
    filters:                          # Metric-level filters
      - dimension: column_name
        operator: "="
        value: "filter_value"
    show_underlying_values:           # Drill-down fields
      - dimension1
      - dimension2
    required_attributes:              # Access control
      is_admin: "true"

Dimension Configuration

columns:
  - name: column_name
    meta:
      dimension:
        type: string              # Dimension type
        label: "Display Name"     
        hidden: false
        format: "string"          # Format specification
        time_intervals:           # For time dimensions
          - DAY
          - WEEK
          - MONTH
          - YEAR
      # Additional dimensions derived from base dimension
      additional_dimensions:
        column_name_upper:
          type: string
          sql: UPPER(${column_name})
          label: "Uppercase Value"

Implementation Approach for dbt-core

1. Macro-Based Metric Calculation Engine

Create a comprehensive macro library that processes metric definitions and generates SQL:

-- Simplified calculate_metric macro - Model-level only, supports multi-column metrics
{% macro calculate_metric(metric_name, metric_config, all_metrics={}) %}
  {% if metric_config.get('method') == 'macro' %}
    {# Process macro-based metric #}
    {% set macro_name = metric_config.get('macro') %}
    {% set parameters = metric_config.get('parameters', {}) %}
    {{ call_metric_macro(macro_name, parameters) }}
  {% else %}
    {# Process regular metric #}
    {% set metric_type = metric_config.get('type', 'number') %}
    {% set filters = metric_config.get('filters', []) %}
    {% set sql_expression = metric_config.get('sql', '') %}
    {% set column_name = metric_config.get('column') %}
    
    {%- if metric_type in ['sum', 'average', 'count', 'count_distinct', 'min', 'max'] -%}
      {# Aggregate metric #}
      {{ _generate_aggregate_sql(metric_type, column_name, filters) }}
    {%- elif metric_type in ['number', 'boolean', 'string', 'date'] and sql_expression -%}
      {# Non-aggregate metric with SQL expression - process metric references #}
      {{ _process_metric_sql_references(sql_expression, all_metrics) }}
    {%- elif column_name -%}
      {# Simple column reference #}
      {{ column_name }}
    {%- else -%}
      {{ exceptions.raise_compiler_error("Invalid metric configuration for '" ~ metric_name ~ "': missing column or SQL expression") }}
    {%- endif -%}
  {% endif %}
{% endmacro %}

-- Generate aggregate SQL with optional filters and multi-column support
{% macro _generate_aggregate_sql(metric_type, column_expr, filters) %}
  {% set base_aggregation = _get_aggregate_function(metric_type, column_expr) %}
  
  {% if filters %}
    CASE 
      WHEN {% for filter in filters -%}
        {{ _process_filter_condition(filter) }}
        {%- if not loop.last %} AND {% endif -%}
      {%- endfor %}
      THEN {{ base_aggregation }}
      ELSE NULL
    END
  {% else %}
    {{ base_aggregation }}
  {% endif %}
{% endmacro %}

-- Get aggregate function SQL - supports multi-column expressions
{% macro _get_aggregate_function(metric_type, column_expr) %}
  {%- if metric_type == 'sum' -%} SUM({{ column_expr }})
  {%- elif metric_type == 'average' -%} AVG({{ column_expr }})
  {%- elif metric_type == 'count' -%} COUNT(*)
  {%- elif metric_type == 'count_distinct' -%} COUNT(DISTINCT {{ column_expr }})
  {%- elif metric_type == 'min' -%} MIN({{ column_expr }})
  {%- elif metric_type == 'max' -%} MAX({{ column_expr }})
  {%- endif -%}
{% endmacro %}

-- Process filter conditions
{% macro _process_filter_condition(filter) %}
  {% set dimension = filter.get('dimension') %}
  {% set operator = filter.get('operator', '=') %}
  {% set value = filter.get('value') %}
  
  {{ dimension }} {{ operator }} '{{ value }}'
{% endmacro %}

-- Process metric references in SQL expressions - ${metric_name} replacement
{% macro _process_metric_sql_references(sql_expression, all_metrics) %}
  {% set processed_sql = sql_expression %}
  
  {# Find and replace all ${metric_name} references #}
  {% set metric_refs = _extract_metric_references(sql_expression) %}
  
  {% for metric_ref in metric_refs %}
    {% set replacement_sql = _resolve_metric_reference(metric_ref, all_metrics) %}
    {% set processed_sql = processed_sql.replace('${' ~ metric_ref ~ '}', replacement_sql) %}
  {% endfor %}
  
  {{ processed_sql }}
{% endmacro %}

-- Extract metric references from SQL expression
{% macro _extract_metric_references(sql_expression) %}
  {% set metric_refs = [] %}
  {% set remaining_sql = sql_expression %}
  
  {# Simple parsing for ${...} patterns #}
  {% for i in range(20) %}  {# Max 20 metric references #}
    {% set start_pos = remaining_sql.find('${') %}
    {% if start_pos == -1 %}
      {% break %}
    {% endif %}
    {% set end_pos = remaining_sql.find('}', start_pos) %}
    {% if end_pos == -1 %}
      {% break %}
    {% endif %}
    {% set metric_ref = remaining_sql[start_pos + 2:end_pos] %}
    {% do metric_refs.append(metric_ref) %}
    {% set remaining_sql = remaining_sql[end_pos + 1:] %}
  {% endfor %}
  
  {{ return(metric_refs) }}
{% endmacro %}

-- Resolve metric references to actual column names (assumes pre-calculated)
{% macro _resolve_metric_reference(metric_name, all_metrics) %}
  {% if metric_name in all_metrics %}
    {% set referenced_metric = all_metrics[metric_name] %}
    {# Return the metric name as column - it should be pre-calculated in aggregation step #}
    {{ return(metric_name) }}
  {% else %}
    {# If metric not found, assume it's a column name #}
    {{ log("Warning: Metric reference '" ~ metric_name ~ "' not found, treating as column", info=true) }}
    {{ return(metric_name) }}
  {% endif %}
{% endmacro %}

-- Get model-level metrics only
{% macro get_model_metrics(model_name) %}
  {% set model_node = graph.nodes.get('model.' ~ project_name ~ '.' ~ model_name) %}
  {% if not model_node %}
    {{ exceptions.raise_compiler_error("Model '" ~ model_name ~ "' not found") }}
  {% endif %}
  
  {{ return(model_node.meta.get('metrics', {})) }}
{% endmacro %}

-- Get metrics by group
{% macro get_metrics_by_group(model_name, group_name) %}
  {% set all_metrics = get_model_metrics(model_name) %}
  {% set grouped_metrics = {} %}
  
  {% for metric_name, metric_config in all_metrics.items() %}
    {% set groups = metric_config.get('groups', []) %}
    {% if group_name in groups %}
      {% do grouped_metrics.update({metric_name: metric_config}) %}
    {% endif %}
  {% endfor %}
  
  {{ return(grouped_metrics) }}
{% endmacro %}

-- Macro-based metrics support
{% macro call_metric_macro(macro_name, parameters) %}
  {% set available_macros = {
    'tenure_calculator': 'tenure_calculator',
    'age_calculator': 'age_calculator',
    'percentage_calculator': 'percentage_calculator', 
    'growth_rate_calculator': 'growth_rate_calculator',
    'days_between_calculator': 'days_between_calculator',
    'multi_column_calculator': 'multi_column_calculator'
  } %}
  
  {% if macro_name not in available_macros %}
    {{ exceptions.raise_compiler_error("Macro '" ~ macro_name ~ "' not found") }}
  {% endif %}
  
  {% if macro_name == 'tenure_calculator' %}
    {{ tenure_calculator(parameters.input_date) }}
  {% elif macro_name == 'age_calculator' %}
    {{ age_calculator(parameters.birth_date) }}
  {% elif macro_name == 'percentage_calculator' %}
    {{ percentage_calculator(parameters.numerator, parameters.denominator) }}
  {% elif macro_name == 'growth_rate_calculator' %}
    {{ growth_rate_calculator(parameters.current_value, parameters.previous_value) }}
  {% elif macro_name == 'days_between_calculator' %}
    {{ days_between_calculator(parameters.start_date, parameters.end_date) }}
  {% elif macro_name == 'multi_column_calculator' %}
    {{ multi_column_calculator(parameters.columns, parameters.operation) }}
  {% endif %}
{% endmacro %}

-- Multi-column calculator macro
{% macro multi_column_calculator(columns, operation) %}
  {% if operation == 'sum' %}
    ({{ columns | join(' + ') }})
  {% elif operation == 'product' %}
    ({{ columns | join(' * ') }})
  {% elif operation == 'average' %}
    (({{ columns | join(' + ') }}) / {{ columns | length }})
  {% elif operation == 'concat' %}
    CONCAT({{ columns | join(', ') }})
  {% else %}
    {{ exceptions.raise_compiler_error("Unknown multi-column operation: " ~ operation) }}
  {% endif %}
{% endmacro %}

3. Metric Model Generation Pattern

Create standardized metric models that leverage the macro system:

-- models/metrics/orders_metrics.sql
{{ config(
    materialized='view',
    meta={
        'metric_model': true,
        'source_model': 'orders'
    }
) }}

{% set source_model = 'orders' %}
{% set metrics = get_model_metrics(source_model) %}

WITH base AS (
    SELECT * FROM {{ ref(source_model) }}
),

aggregated AS (
    SELECT
        -- Include dimension columns for grouping
        {% for dim in get_model_dimensions(source_model) %}
        {{ dim.name }},
        {% endfor %}
        
        -- Calculate all aggregate metrics
        {% for metric_name, metric_config in metrics.items() %}
        {% if metric_config.type in ['sum', 'average', 'count', 'count_distinct', 'min', 'max'] %}
        {{ calculate_metric(metric_name, metric_config, source_model) }} AS {{ metric_name }},
        {% endif %}
        {% endfor %}
        1 as _dummy  -- Ensure valid SQL even with no metrics
    FROM base
    GROUP BY ALL
),

final AS (
    SELECT
        *,
        -- Calculate non-aggregate metrics
        {% for metric_name, metric_config in metrics.items() %}
        {% if metric_config.type in ['number', 'boolean', 'string', 'date'] %}
        {{ calculate_metric(metric_name, metric_config, source_model) }} AS {{ metric_name }},
        {% endif %}
        {% endfor %}
        1 as _dummy2
    FROM aggregated
)

SELECT * FROM final

4. Integration Layer Implementation

Create a Python package to process manifest.json and expose metrics:

# dbt_metrics_core/manifest_processor.py
import json
from pathlib import Path
from typing import Dict, List, Optional

class MetricsManifest:
    def __init__(self, manifest_path: str):
        self.manifest_path = Path(manifest_path)
        self.manifest = self._load_manifest()
        self.metrics = self._extract_all_metrics()
    
    def _load_manifest(self) -> dict:
        with open(self.manifest_path, 'r') as f:
            return json.load(f)
    
    def _extract_all_metrics(self) -> Dict[str, Dict]:
        """Extract all metrics from manifest"""
        metrics = {}
        
        for node_id, node in self.manifest['nodes'].items():
            if node['resource_type'] != 'model':
                continue
                
            model_name = node['name']
            
            # Extract model-level metrics
            model_metrics = node.get('meta', {}).get('metrics', {})
            for metric_name, metric_config in model_metrics.items():
                metrics[f"{model_name}.{metric_name}"] = {
                    'model': model_name,
                    'name': metric_name,
                    'config': metric_config,
                    'type': 'model_level'
                }
            
            # Extract column-level metrics
            for column_name, column_info in node.get('columns', {}).items():
                column_metrics = column_info.get('meta', {}).get('metrics', {})
                for metric_name, metric_config in column_metrics.items():
                    metrics[f"{model_name}.{metric_name}"] = {
                        'model': model_name,
                        'column': column_name,
                        'name': metric_name,
                        'config': metric_config,
                        'type': 'column_level'
                    }
        
        return metrics
    
    def get_metric_sql(self, metric_id: str) -> str:
        """Generate SQL for a specific metric"""
        metric = self.metrics.get(metric_id)
        if not metric:
            raise ValueError(f"Metric {metric_id} not found")
        
        # Generate SQL based on metric configuration
        return self._generate_metric_query(metric)

Examples of Measure and Metric Usage

Example 1: E-commerce Analytics

models:
  - name: fct_orders
    meta:
      group_details:
        sales:
          label: "Sales Metrics"
        customer:
          label: "Customer Metrics"
      metrics:
        # Non-aggregate metrics
        aov:
          type: number
          sql: ${total_revenue} / ${order_count}
          label: "Average Order Value"
          format: "$#,##0.00"
          groups: ["sales"]
        
        repeat_purchase_rate:
          type: number
          sql: ${repeat_customers} / ${total_customers}
          label: "Repeat Purchase Rate"
          format: "0.00%"
          groups: ["customer"]
    
    columns:
      - name: order_total
        meta:
          metrics:
            total_revenue:
              type: sum
              label: "Total Revenue"
              format: "$#,##0.00"
              groups: ["sales"]
      
      - name: customer_id
        meta:
          metrics:
            total_customers:
              type: count_distinct
              label: "Total Customers"
              groups: ["customer"]
            
            repeat_customers:
              type: count_distinct
              label: "Repeat Customers"
              filters:
                - dimension: order_number
                  operator: ">"
                  value: "1"
              groups: ["customer"]

Example 2: SaaS Metrics

models:
  - name: fct_subscriptions
    meta:
      metrics:
        # MRR calculation
        mrr:
          type: number
          sql: ${active_subscriptions_value} / 12
          label: "Monthly Recurring Revenue"
          format: "$#,##0"
          groups: ["revenue", "growth"]
        
        # Churn rate
        churn_rate:
          type: number
          sql: ${churned_customers} / ${total_customers_start_of_period}
          label: "Customer Churn Rate"
          format: "0.00%"
          groups: ["retention"]
        
        # LTV calculation
        customer_ltv:
          type: number
          sql: ${average_revenue_per_user} / ${churn_rate}
          label: "Customer Lifetime Value"
          format: "$#,##0"
          groups: ["customer", "revenue"]

Example 3: Usage with dbt run

# Standard dbt workflow
dbt run --select +orders_metrics

# The metric model will:
# 1. Read metric definitions from meta properties
# 2. Generate appropriate SQL using macros
# 3. Create a view/table with all metrics calculated
# 4. Update manifest.json with metric metadata

Validation and Testing Strategy

1. YAML Schema Validation

# tests/schema_validation.yml
version: 2
models:
  - name: orders_metrics
    tests:
      - dbt_utils.expression_is_true:
          expression: "total_revenue >= 0"
      - dbt_utils.expression_is_true:
          expression: "order_count >= 0"
      
    columns:
      - name: total_revenue
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0

2. Metric Logic Testing

-- tests/metrics/test_revenue_calculations.sql
{% set test_cases = [
    {'revenue': 100, 'quantity': 2, 'expected_total': 200},
    {'revenue': 50, 'quantity': 3, 'expected_total': 150}
] %}

{% for test_case in test_cases %}
SELECT 
    {{ calculate_metric('total_revenue', {'type': 'sum'}, 'test_model') }} as calculated,
    {{ test_case.expected_total }} as expected
WHERE calculated != expected
{% endfor %}

Migration Path from Lightdash

For teams currently using Lightdash who want to implement this system:

  1. Export existing metric definitions from Lightdash projects
  2. Transform metric configurations to match the proposed schema
  3. Gradually migrate by running both systems in parallel
  4. Validate metric parity between systems before full cutover

The system maintains compatibility with Lightdash's configuration approach while leveraging dbt-core's native capabilities, providing a smooth transition path for teams looking to implement a metrics layer directly in dbt-core.

Advanced Features: Reusable Metric Templates and Enhanced Documentation

1. Reusable Metric Macros System

For metrics that repeat across models with different input columns (like Tenure, Age calculations), implement a macro-based approach:

Reusable Metric Macros Definition

-- macros/metrics/reusable_metrics.sql

{% macro tenure_calculator(input_date) %}
  {#- Calculate years between a date and current date -#}
  CASE 
    WHEN {{ input_date }} IS NULL THEN NULL
    ELSE DATEDIFF('year', {{ input_date }}, CURRENT_DATE())
  END
{% endmacro %}

{% macro age_calculator(birth_date) %}
  {#- Calculate age from birth date with validation -#}
  CASE 
    WHEN {{ birth_date }} IS NULL THEN NULL
    WHEN {{ birth_date }} > CURRENT_DATE() THEN NULL
    ELSE DATEDIFF('year', {{ birth_date }}, CURRENT_DATE())
  END
{% endmacro %}

{% macro percentage_calculator(numerator, denominator) %}
  {#- Calculate percentage from numerator and denominator -#}
  CASE 
    WHEN {{ denominator }} = 0 OR {{ denominator }} IS NULL THEN NULL
    ELSE ({{ numerator }}::FLOAT / {{ denominator }}::FLOAT) * 100
  END
{% endmacro %}

{% macro growth_rate_calculator(current_value, previous_value) %}
  {#- Calculate growth rate between two periods -#}
  CASE 
    WHEN {{ previous_value }} = 0 OR {{ previous_value }} IS NULL THEN NULL
    ELSE (({{ current_value }} - {{ previous_value }})::FLOAT / {{ previous_value }}::FLOAT) * 100
  END
{% endmacro %}

{% macro days_between_calculator(start_date, end_date) %}
  {#- Calculate days between two dates -#}
  CASE 
    WHEN {{ start_date }} IS NULL OR {{ end_date }} IS NULL THEN NULL
    ELSE DATEDIFF('day', {{ start_date }}, {{ end_date }})
  END
{% endmacro %}

{% macro business_days_calculator(start_date, end_date) %}
  {#- Calculate business days between two dates (excluding weekends) -#}
  CASE 
    WHEN {{ start_date }} IS NULL OR {{ end_date }} IS NULL THEN NULL
    ELSE DATEDIFF('day', {{ start_date }}, {{ end_date }}) - 
         DATEDIFF('week', {{ start_date }}, {{ end_date }}) * 2 -
         CASE WHEN DAYOFWEEK({{ start_date }}) = 1 THEN 1 ELSE 0 END -
         CASE WHEN DAYOFWEEK({{ end_date }}) = 7 THEN 1 ELSE 0 END
  END
{% endmacro %}

{% macro quartile_calculator(value_column, quartile_number) %}
  {#- Calculate quartile for a given column -#}
  PERCENTILE_CONT({{ quartile_number * 0.25 }}) WITHIN GROUP (ORDER BY {{ value_column }})
{% endmacro %}

{% macro moving_average_calculator(value_column, window_size) %}
  {#- Calculate moving average with specified window -#}
  AVG({{ value_column }}) OVER (
    ORDER BY created_at 
    ROWS BETWEEN {{ window_size - 1 }} PRECEDING AND CURRENT ROW
  )
{% endmacro %}

{% macro z_score_calculator(value_column) %}
  {#- Calculate z-score for standardization -#}
  ({{ value_column }} - AVG({{ value_column }}) OVER()) / 
  STDDEV({{ value_column }}) OVER()
{% endmacro %}

Using Macros in Model Definitions

# models/hr/schema.yml
version: 2
models:
  - name: dim_employees
    meta:
      metrics:
        # Using tenure macro
        employee_tenure:
          method: macro
          macro: tenure_calculator
          parameters:
            input_date: hire_date
          label: "Employee Tenure"
          description: "Years since employee was hired"
          format: "0"
          suffix: " years"
          groups: ["hr", "tenure"]
          
        employee_age:
          method: macro
          macro: age_calculator
          parameters:
            birth_date: date_of_birth
          label: "Employee Age"
          description: "Current age of employee"
          format: "0"
          suffix: " years old"
          groups: ["hr", "demographics"]
          
        department_tenure:
          method: macro
          macro: tenure_calculator
          parameters:
            input_date: department_start_date
          label: "Department Tenure"
          description: "Years in current department"
          format: "0"
          suffix: " years"
          groups: ["hr", "tenure"]

  - name: fct_sales_performance
    meta:
      metrics:
        quota_achievement_rate:
          method: macro
          macro: percentage_calculator
          parameters:
            numerator: actual_sales
            denominator: sales_quota
          label: "Quota Achievement Rate"
          description: "Percentage of sales quota achieved"
          format: "0.00%"
          groups: ["sales", "performance"]
          
        yoy_growth_rate:
          method: macro
          macro: growth_rate_calculator
          parameters:
            current_value: current_year_sales
            previous_value: previous_year_sales
          label: "Year-over-Year Growth"
          description: "Sales growth compared to previous year"
          format: "0.00%"
          groups: ["sales", "growth"]

Enhanced Calculate Metric Macro

-- macros/metrics/calculate_metric.sql
{% macro calculate_metric(metric_name, metric_config, model_ref) %}
  {% if metric_config.get('method') == 'macro' %}
    {# Process macro-based metric #}
    {% set macro_name = metric_config.get('macro') %}
    {% set parameters = metric_config.get('parameters', {}) %}
    {{ call_metric_macro(macro_name, parameters) }}
  {% else %}
    {# Process regular metric #}
    {% set metric_type = metric_config.get('type') %}
    {% set column_name = metric_config.get('column', metric_name) %}
    {% set filters = metric_config.get('filters', []) %}
    
    {%- if metric_type in ['sum', 'average', 'count', 'count_distinct', 'min', 'max'] -%}
      {{ _generate_aggregate_sql(metric_type, column_name, filters) }}
    {%- elif metric_type in ['number', 'boolean', 'string', 'date'] -%}
      {{ metric_config.get('sql', column_name) }}
    {%- else -%}
      {{ exceptions.raise_compiler_error("Unknown metric type: " ~ metric_type) }}
    {%- endif -%}
  {% endif %}
{% endmacro %}

{% macro call_metric_macro(macro_name, parameters) %}
  {# Dynamic macro calling with parameter validation #}
  {% set available_macros = {
    'tenure_calculator': 'tenure_calculator',
    'age_calculator': 'age_calculator', 
    'percentage_calculator': 'percentage_calculator',
    'growth_rate_calculator': 'growth_rate_calculator',
    'days_between_calculator': 'days_between_calculator',
    'business_days_calculator': 'business_days_calculator',
    'quartile_calculator': 'quartile_calculator',
    'moving_average_calculator': 'moving_average_calculator',
    'z_score_calculator': 'z_score_calculator'
  } %}
  
  {% if macro_name not in available_macros %}
    {{ exceptions.raise_compiler_error("Macro '" ~ macro_name ~ "' not found. Available macros: " ~ available_macros.keys() | join(', ')) }}
  {% endif %}
  
  {# Build the macro call dynamically #}
  {% set macro_call = macro_name ~ '(' %}
  {% for param_name, param_value in parameters.items() %}
    {% set macro_call = macro_call ~ param_value %}
    {% if not loop.last %}
      {% set macro_call = macro_call ~ ', ' %}
    {% endif %}
  {% endfor %}
  {% set macro_call = macro_call ~ ')' %}
  
  {# Execute the macro call #}
  {{ context[macro_name](**parameters) }}
{% endmacro %}

Advanced Usage Example

-- Example usage in a model
-- models/metrics/employee_analytics.sql
{{ config(materialized='view') }}

WITH employee_metrics AS (
  SELECT 
    employee_id,
    first_name,
    last_name,
    hire_date,
    date_of_birth,
    department_start_date,
    salary,
    
    -- Using macro-based metrics
    {{ calculate_metric('employee_tenure', {
         'method': 'macro',
         'macro': 'tenure_calculator',
         'parameters': {'input_date': 'hire_date'}
       }) }} as employee_tenure,
       
    {{ calculate_metric('employee_age', {
         'method': 'macro', 
         'macro': 'age_calculator',
         'parameters': {'birth_date': 'date_of_birth'}
       }) }} as employee_age,
       
    {{ calculate_metric('dept_tenure', {
         'method': 'macro',
         'macro': 'tenure_calculator', 
         'parameters': {'input_date': 'department_start_date'}
       }) }} as department_tenure,
       
    -- Direct macro calls are also possible
    {{ tenure_calculator('hire_date') }} as tenure_direct,
    {{ age_calculator('date_of_birth') }} as age_direct,
    {{ percentage_calculator('salary', 'market_rate') }} as salary_to_market_ratio
    
  FROM {{ ref('dim_employees') }}
)

SELECT * FROM employee_metrics

2. Enhanced dbt-docs Integration

Custom Documentation Generation

-- macros/docs/generate_metrics_docs.sql (Model-level only)
{% macro generate_metrics_documentation() %}
  {% set metrics_docs = {} %}
  
  {% for node_id, node in graph.nodes.items() %}
    {% if node.resource_type == 'model' %}
      {% set model_name = node.name %}
      {% set model_metrics = node.meta.get('metrics', {}) %}
      
      {% if model_metrics %}
        {% set documented_metrics = [] %}
        
        {# Process all model-level metrics #}
        {% for metric_name, metric_config in model_metrics.items() %}
          {% set metric_doc = {
            'name': metric_name,
            'model': model_name,
            'type': metric_config.get('type', 'calculated'),
            'label': metric_config.get('label', metric_name),
            'description': metric_config.get('description', ''),
            'groups': metric_config.get('groups', []),
            'format': metric_config.get('format', ''),
            'method': metric_config.get('method', 'sql'),
            'macro': metric_config.get('macro', ''),
            'is_macro_based': metric_config.get('method') == 'macro',
            'is_aggregate': metric_config.get('type') in ['sum', 'average', 'count', 'count_distinct', 'min', 'max'],
            'column': metric_config.get('column', ''),
            'sql': metric_config.get('sql', ''),
            'filters': metric_config.get('filters', []),
            'parameters': metric_config.get('parameters', {})
          } %}
          {% do documented_metrics.append(metric_doc) %}
        {% endfor %}
        
        {% do metrics_docs.update({model_name: documented_metrics}) %}
      {% endif %}
    {% endif %}
  {% endfor %}
  
  {{ return(metrics_docs) }}
{% endmacro %}

-- Generate model documentation with metrics sections
{% macro generate_model_docs_with_metrics(model_name) %}
  {% set model_node = graph.nodes.get('model.' ~ project_name ~ '.' ~ model_name) %}
  {% set metrics = get_model_metrics(model_name) %}
  {% set group_details = model_node.meta.get('group_details', {}) %}
  
  {% if metrics %}
## 📊 Metrics Available in This Model

### Aggregate Metrics
{% for metric_name, metric_config in metrics.items() %}
  {% if metric_config.get('type') in ['sum', 'average', 'count', 'count_distinct', 'min', 'max'] %}
**{{ metric_config.get('label', metric_name) }}**
- **Type**: {{ metric_config.get('type', 'N/A') }}
- **Column**: {{ metric_config.get('column', 'N/A') }}
- **Description**: {{ metric_config.get('description', 'No description provided') }}
- **Format**: {{ metric_config.get('format', 'Default') }}
- **Groups**: {{ metric_config.get('groups', []) | join(', ') }}
{% if metric_config.get('filters') %}
- **Filters**: 
  {% for filter in metric_config.get('filters', []) %}
  - {{ filter.dimension }} {{ filter.operator }} {{ filter.value }}
  {% endfor %}
{% endif %}

  {% endif %}
{% endfor %}

### Calculated Metrics
{% for metric_name, metric_config in metrics.items() %}
  {% if metric_config.get('type') in ['number', 'boolean', 'string', 'date'] and metric_config.get('sql') %}
**{{ metric_config.get('label', metric_name) }}**
- **Type**: {{ metric_config.get('type', 'N/A') }}
- **Description**: {{ metric_config.get('description', 'No description provided') }}
- **Format**: {{ metric_config.get('format', 'Default') }}
- **Groups**: {{ metric_config.get('groups', []) | join(', ') }}
- **SQL**: `{{ metric_config.get('sql') }}`

  {% endif %}
{% endfor %}

### Macro-Based Metrics
{% for metric_name, metric_config in metrics.items() %}
  {% if metric_config.get('method') == 'macro' %}
**{{ metric_config.get('label', metric_name) }}** (Macro: {{ metric_config.get('macro') }})
- **Macro Description**: {{ get_macro_description(metric_config.get('macro')) }}
- **Parameters**: 
  {% for param_name, param_value in metric_config.get('parameters', {}).items() %}
  - `{{ param_name }}`: {{ param_value }}
  {% endfor %}
- **Groups**: {{ metric_config.get('groups', []) | join(', ') }}
- **Usage**: `{{ metric_config.get('macro') }}({{ metric_config.get('parameters', {}).values() | join(', ') }})`

  {% endif %}
{% endfor %}

### Metric Groups
{% for group_name, group_config in group_details.items() %}
**{{ group_config.get('label', group_name) }}**
- {{ group_config.get('description', 'No description') }}
- Metrics: {% for metric_name, metric_config in metrics.items() %}{% if group_name in metric_config.get('groups', []) %}{{ metric_config.get('label', metric_name) }}{% if not loop.last %}, {% endif %}{% endif %}{% endfor %}

{% endfor %}

---
  {% endif %}
{% endmacro %}

{% macro get_macro_description(macro_name) %}
  {% set macro_descriptions = {
    'tenure_calculator': 'Calculates years between a date and current date',
    'age_calculator': 'Calculates age from birth date with validation',
    'percentage_calculator': 'Calculates percentage from numerator and denominator',
    'growth_rate_calculator': 'Calculates growth rate between two periods',
    'days_between_calculator': 'Calculates days between two dates',
    'multi_column_calculator': 'Performs operations across multiple columns'
  } %}
  
  {{ return(macro_descriptions.get(macro_name, 'Custom macro calculation')) }}
{% endmacro %}

dbt-docs Custom Plugin Structure

<script>
// Enhanced debugging version
document.addEventListener('DOMContentLoaded', function() {
    console.log('DOM loaded, waiting for Angular...');
    setTimeout(function() {
        initializeMetricsIntegration();
    }, 1000);
});

function initializeMetricsIntegration() {
    console.log('Attempting to initialize metrics integration...');
    
    var angularElement = document.querySelector('[ng-app="dbt"]');
    if (!angularElement) {
        console.log('❌ dbt Angular app not found, retrying...');
        setTimeout(initializeMetricsIntegration, 500);
        return;
    }
    
    try {
        var $injector = angular.element(angularElement).injector();
        if (!$injector) {
            console.log('❌ Angular injector not ready, retrying...');
            setTimeout(initializeMetricsIntegration, 500);
            return;
        }
        
        var projectService = $injector.get('project');
        console.log('✅ Got project service:', projectService);
        
        // Wait for project data to be ready
        projectService.ready(function() {
            console.log('✅ dbt project data loaded, initializing metrics...');
            console.log('📄 Manifest data:', projectService.files.manifest);
            
            try {
                // Initialize metrics catalog with debugging
                var metricsCatalog = new MetricsCatalog(projectService);
                console.log('📊 Metrics extracted:', metricsCatalog.metrics);
                
                // Add metrics tab to navigation - SIMPLIFIED VERSION
                addSimpleMetricsTab(metricsCatalog);
                
                // Enhance current page if it's a model page
                enhanceCurrentPage(metricsCatalog);
                
                console.log('✅ Metrics integration complete!');
                
            } catch (error) {
                console.error('❌ Error in metrics initialization:', error);
            }
        });
        
    } catch (error) {
        console.error('❌ Error accessing Angular services:', error);
        setTimeout(initializeMetricsIntegration, 500);
    }
}

// Simplified Metrics Catalog Class with debugging
function MetricsCatalog(projectService) {
    console.log('🔧 Creating MetricsCatalog...');
    this.projectService = projectService;
    this.manifest = projectService.files.manifest;
    
    console.log('📋 Raw manifest:', this.manifest);
    
    this.metrics = {};
    this.models = {};
    
    this.extractMetrics();
    
    console.log('✅ Metrics extraction complete. Found:', {
        models: Object.keys(this.models).length,
        totalMetrics: Object.values(this.metrics).reduce((sum, arr) => sum + arr.length, 0)
    });
}

MetricsCatalog.prototype.extractMetrics = function() {
    console.log('🔍 Extracting metrics from manifest...');
    
    var nodes = this.manifest.nodes || {};
    console.log('📦 Total nodes in manifest:', Object.keys(nodes).length);
    
    var modelCount = 0;
    var metricsCount = 0;
    
    Object.keys(nodes).forEach(function(nodeId) {
        var node = nodes[nodeId];
        
        if (node.resource_type === 'model') {
            modelCount++;
            var modelName = node.name;
            var modelMeta = node.meta || {};
            var modelMetrics = modelMeta.metrics || {};
            
            console.log(`📋 Model: ${modelName}, Meta:`, modelMeta, 'Metrics:', modelMetrics);
            
            if (Object.keys(modelMetrics).length > 0) {
                console.log(`✅ Found ${Object.keys(modelMetrics).length} metrics in model: ${modelName}`);
                
                this.models[modelName] = {
                    name: modelName,
                    description: node.description || '',
                    groupDetails: modelMeta.group_details || {},
                    metricCount: Object.keys(modelMetrics).length
                };
                
                this.metrics[modelName] = Object.keys(modelMetrics).map(function(metricName) {
                    var config = modelMetrics[metricName];
                    metricsCount++;
                    
                    return {
                        name: metricName,
                        model: modelName,
                        type: config.type || 'calculated',
                        label: config.label || metricName,
                        description: config.description || '',
                        groups: config.groups || [],
                        format: config.format || '',
                        method: config.method || 'sql',
                        macro: config.macro || '',
                        column: config.column || '',
                        sql: config.sql || '',
                        filters: config.filters || [],
                        parameters: config.parameters || {},
                        is_macro_based: config.method === 'macro',
                        is_aggregate: ['sum', 'average', 'count', 'count_distinct', 'min', 'max'].includes(config.type)
                    };
                });
            }
        }
    }.bind(this));
    
    console.log(`📊 Extraction complete: ${modelCount} models, ${metricsCount} metrics`);
};

// SIMPLIFIED tab addition - just add a visible element first
function addSimpleMetricsTab(metricsCatalog) {
    console.log('📑 Adding metrics tab...');
    
    // Try multiple selectors to find navigation
    var navSelectors = [
        '.nav.nav-tabs',
        'nav ul',
        '.app-links ul',
        '.nav',
        'ul.nav'
    ];
    
    var navTabs = null;
    for (var i = 0; i < navSelectors.length; i++) {
        navTabs = document.querySelector(navSelectors[i]);
        if (navTabs) {
            console.log(`✅ Found navigation using selector: ${navSelectors[i]}`);
            break;
        }
    }
    
    if (!navTabs) {
        console.log('❌ No navigation found. Available elements:');
        console.log('- nav elements:', document.querySelectorAll('nav'));
        console.log('- ul elements:', document.querySelectorAll('ul'));
        console.log('- elements with "nav":', document.querySelectorAll('[class*="nav"]'));
        
        // Create a floating metrics button instead
        createFloatingMetricsButton(metricsCatalog);
        return;
    }
    
    var metricsTab = document.createElement('li');
    metricsTab.innerHTML = '<a href="#" class="metrics-tab-link" style="color: #667eea; font-weight: bold;">📊 Metrics</a>';
    navTabs.appendChild(metricsTab);
    
    console.log('✅ Metrics tab added to navigation');
    
    // Handle clicks on metrics tab
    metricsTab.addEventListener('click', function(e) {
        e.preventDefault();
        console.log('🖱️ Metrics tab clicked');
        showSimpleMetricsCatalog(metricsCatalog);
    });
}

// Create a floating button if nav not found
function createFloatingMetricsButton(metricsCatalog) {
    console.log('🎈 Creating floating metrics button...');
    
    var button = document.createElement('div');
    button.innerHTML = '📊 Metrics';
    button.style.cssText = `
        position: fixed;
        top: 20px;
        right: 20px;
        background: #667eea;
        color: white;
        padding: 10px 15px;
        border-radius: 25px;
        cursor: pointer;
        z-index: 1000;
        font-weight: bold;
        box-shadow: 0 4px 12px rgba(0,0,0,0.2);
    `;
    
    button.addEventListener('click', function() {
        console.log('🖱️ Floating metrics button clicked');
        showSimpleMetricsCatalog(metricsCatalog);
    });
    
    document.body.appendChild(button);
    console.log('✅ Floating metrics button created');
}

// SIMPLIFIED metrics catalog display
function showSimpleMetricsCatalog(metricsCatalog) {
    console.log('📊 Showing metrics catalog...');
    
    var totalMetrics = Object.values(metricsCatalog.metrics).reduce(function(sum, modelMetrics) {
        return sum + modelMetrics.length;
    }, 0);
    
    console.log(`📈 Total metrics to display: ${totalMetrics}`);
    
    // Create simple HTML
    var catalogHtml = `
        <div style="padding: 20px; max-width: 1200px; margin: 0 auto;">
            <h1>📊 Metrics Catalog</h1>
            <p>Found <strong>${totalMetrics}</strong> metrics across <strong>${Object.keys(metricsCatalog.metrics).length}</strong> models</p>
            
            <div style="display: grid; grid-template-columns: repeat(auto-fill, minmax(300px, 1fr)); gap: 20px; margin-top: 30px;">
                ${generateSimpleMetricsCards(metricsCatalog)}
            </div>
        </div>
    `;
    
    // Find main content area
    var contentSelectors = [
        '.app-main',
        'main',
        '.container',
        '.content',
        '[ui-view]',
        'body > div'
    ];
    
    var mainContent = null;
    for (var i = 0; i < contentSelectors.length; i++) {
        mainContent = document.querySelector(contentSelectors[i]);
        if (mainContent) {
            console.log(`✅ Found content area using selector: ${contentSelectors[i]}`);
            break;
        }
    }
    
    if (mainContent) {
        mainContent.innerHTML = catalogHtml;
        console.log('✅ Metrics catalog displayed');
    } else {
        console.log('❌ No content area found. Creating new div...');
        var newDiv = document.createElement('div');
        newDiv.innerHTML = catalogHtml;
        document.body.appendChild(newDiv);
    }
}

function generateSimpleMetricsCards(metricsCatalog) {
    var html = '';
    
    Object.keys(metricsCatalog.metrics).forEach(function(modelName) {
        var modelMetrics = metricsCatalog.metrics[modelName];
        
        modelMetrics.forEach(function(metric) {
            var cardStyle = `
                border: 2px solid #e1e5e9;
                border-radius: 8px;
                padding: 15px;
                background: white;
                box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            `;
            
            html += `
                <div style="${cardStyle}">
                    <h4 style="margin: 0 0 10px 0; color: #2d3748;">${metric.label}</h4>
                    <p style="margin: 5px 0; color: #4a5568; font-size: 14px;">
                        <strong>Type:</strong> ${metric.type} | 
                        <strong>Model:</strong> ${metric.model}
                    </p>
                    <p style="margin: 5px 0; color: #4a5568; font-size: 14px;">
                        ${metric.description || 'No description'}
                    </p>
                    ${metric.column ? `<p style="margin: 5px 0; color: #4a5568; font-size: 12px;"><strong>Column:</strong> <code>${metric.column}</code></p>` : ''}
                    <button onclick="copyToClipboard('{{ calculate_metric(\\'${metric.name}\\', {...}) }}')" 
                            style="margin-top: 10px; padding: 5px 10px; border: 1px solid #d2d6dc; border-radius: 4px; background: white; cursor: pointer;">
                        📋 Copy Usage
                    </button>
                </div>
            `;
        });
    });
    
    return html;
}

// Simple enhance current page function
function enhanceCurrentPage(metricsCatalog) {
    console.log('🔧 Checking if current page can be enhanced...');
    console.log('Current URL:', window.location.href);
    console.log('Current hash:', window.location.hash);
    
    // Check if we're on a model page
    if (window.location.hash.includes('model')) {
        setTimeout(function() {
            var modelNameElements = [
                document.querySelector('h1 .break'),
                document.querySelector('h1'),
                document.querySelector('[ng-bind="model.name"]')
            ];
            
            var modelNameElement = modelNameElements.find(function(el) { return el; });
            
            if (modelNameElement) {
                var modelName = modelNameElement.textContent.trim();
                console.log(`📋 Found model page: ${modelName}`);
                
                if (metricsCatalog.metrics[modelName]) {
                    console.log(`✅ Model has metrics, enhancing page...`);
                    addSimpleMetricsSection(modelName, metricsCatalog);
                } else {
                    console.log(`ℹ️ Model has no metrics`);
                }
            }
        }, 1000);
    }
}

function addSimpleMetricsSection(modelName, metricsCatalog) {
    var modelMetrics = metricsCatalog.metrics[modelName];
    
    var metricsHtml = `
        <div style="margin-top: 30px; padding: 20px; background: #f8fafc; border-radius: 8px; border-left: 4px solid #667eea;">
            <h3>📊 Available Metrics (${modelMetrics.length})</h3>
            <div style="display: grid; grid-template-columns: repeat(auto-fill, minmax(250px, 1fr)); gap: 15px; margin-top: 15px;">
                ${modelMetrics.map(function(metric) {
                    return `
                        <div style="background: white; padding: 10px; border-radius: 4px; border: 1px solid #e2e8f0;">
                            <strong>${metric.label}</strong><br>
                            <small style="color: #4a5568;">${metric.type} | ${metric.description || 'No description'}</small>
                        </div>
                    `;
                }).join('')}
            </div>
        </div>
    `;
    
    // Find where to inject
    var targetElements = [
        document.querySelector('.model-description'),
        document.querySelector('.tab-content'),
        document.querySelector('.app-main'),
        document.querySelector('main')
    ];
    
    var target = targetElements.find(function(el) { return el; });
    
    if (target) {
        var metricsDiv = document.createElement('div');
        metricsDiv.innerHTML = metricsHtml;
        target.appendChild(metricsDiv);
        console.log('✅ Metrics section added to model page');
    }
}

// Simple copy function
function copyToClipboard(text) {
    if (navigator.clipboard) {
        navigator.clipboard.writeText(text).then(function() {
            alert('Copied to clipboard!');
        });
    } else {
        var textArea = document.createElement('textarea');
        textArea.value = text;
        document.body.appendChild(textArea);
        textArea.select();
        document.execCommand('copy');
        document.body.removeChild(textArea);
        alert('Copied to clipboard!');
    }
}
</script>

CSS Styling for Metrics Catalog

/* Metrics Catalog Styles */
        .metrics-catalog {
            padding: 20px;
            max-width: 1200px;
            margin: 0 auto;
        }
        
        .metrics-summary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 20px;
            border-radius: 12px;
            margin-bottom: 30px;
            display: flex;
            justify-content: space-around;
            text-align: center;
        }
        
        .stat-card {
            flex: 1;
        }
        
        .stat-number {
            display: block;
            font-size: 2em;
            font-weight: bold;
        }
        
        .stat-label {
            display: block;
            font-size: 0.9em;
            opacity: 0.9;
        }
        
        .metrics-filters {
            display: flex;
            gap: 15px;
            margin-bottom: 30px;
            flex-wrap: wrap;
        }
        
        .filter-input {
            padding: 10px;
            border: 2px solid #e1e5e9;
            border-radius: 6px;
            font-size: 14px;
        }
        
        .metrics-grid {
            display: grid;
            grid-template-columns: repeat(auto-fill, minmax(350px, 1fr));
            gap: 25px;
        }
        
        .metric-card {
            border: 2px solid #e1e5e9;
            border-radius: 12px;
            padding: 20px;
            background: white;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
            transition: all 0.3s ease;
        }
        
        .metric-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 8px 25px rgba(0,0,0,0.15);
            border-color: #667eea;
        }
        
        .metric-header {
            display: flex;
            justify-content: space-between;
            align-items: flex-start;
            margin-bottom: 15px;
        }
        
        .metric-badges {
            display: flex;
            gap: 5px;
            flex-wrap: wrap;
        }
        
        .badge {
            padding: 3px 8px;
            border-radius: 4px;
            font-size: 11px;
            font-weight: bold;
            color: white;
        }
        
        .badge-aggregate { background: #059669; }
        .badge-calculated { background: #7c3aed; }
        .badge-macro { background: #8b5cf6; }
        .badge-sum { background: #dc2626; }
        .badge-average { background: #ea580c; }
        .badge-count { background: #16a34a; }
        .badge-count_distinct { background: #0891b2; }
        
        .metric-details {
            font-size: 14px;
            color: #4a5568;
            line-height: 1.4;
        }
        
        .metric-actions {
            margin-top: 15px;
            display: flex;
            gap: 10px;
        }
        
        .btn-metrics {
            padding: 6px 12px;
            border: 1px solid #d2d6dc;
            border-radius: 4px;
            background: white;
            cursor: pointer;
            font-size: 12px;
            transition: all 0.2s ease;
        }
        
        .btn-metrics:hover {
            background: #667eea;
            color: white;
            border-color: #667eea;
        }
        
        /* Model page metrics section */
        .model-metrics-section {
            margin-top: 30px;
            padding: 20px;
            background: #f8fafc;
            border-radius: 8px;
            border-left: 4px solid #667eea;
        }
        
        .metrics-table {
            width: 100%;
            border-collapse: collapse;
            margin: 15px 0;
        }
        
        .metrics-table th,
        .metrics-table td {
            text-align: left;
            padding: 8px 12px;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .metrics-table th {
            background: #edf2f7;
            font-weight: 600;
        }
        
        /* Toast notifications */
        .metrics-toast {
            position: fixed;
            bottom: 20px;
            right: 20px;
            background: #48bb78;
            color: white;
            padding: 12px 20px;
            border-radius: 6px;
            box-shadow: 0 4px 12px rgba(0,0,0,0.2);
            z-index: 1001;
            animation: slideIn 0.3s ease;
        }
        
        @keyframes slideIn {
            from { transform: translateX(100%); }
            to { transform: translateX(0); }
        }

3. Enhanced Macro Testing and Validation

Unit Testing for Metric Macros

-- tests/unit/test_metric_macros.sql
{% macro test_tenure_calculator() %}
  {% set test_cases = [
    {
      'input_date': "'2020-01-01'",
      'expected_min': 4,
      'expected_max': 5,
      'description': 'Employee hired 4+ years ago'
    },
    {
      'input_date': "'2023-06-15'", 
      'expected_min': 1,
      'expected_max': 2,
      'description': 'Employee hired 1+ years ago'
    },
    {
      'input_date': "NULL",
      'expected_min': 'NULL',
      'expected_max': 'NULL', 
      'description': 'NULL input should return NULL'
    }
  ] %}
  
  {% for test_case in test_cases %}
  SELECT 
    '{{ test_case.description }}' as test_description,
    {{ tenure_calculator(test_case.input_date) }} as calculated_tenure,
    {{ test_case.expected_min }} as expected_min,
    {{ test_case.expected_max }} as expected_max,
    CASE 
      WHEN {{ tenure_calculator(test_case.input_date) }} IS NULL AND {{ test_case.expected_min }} = 'NULL' THEN 'PASS'
      WHEN {{ tenure_calculator(test_case.input_date) }} BETWEEN {{ test_case.expected_min }} AND {{ test_case.expected_max }} THEN 'PASS'
      ELSE 'FAIL'
    END as test_result
  {% if not loop.last %} UNION ALL {% endif %}
  {% endfor %}
{% endmacro %}

{% macro test_percentage_calculator() %}
  {% set test_cases = [
    {'numerator': 75, 'denominator': 100, 'expected': 75.0},
    {'numerator': 0, 'denominator': 100, 'expected': 0.0},
    {'numerator': 100, 'denominator': 0, 'expected': 'NULL'},
    {'numerator': 150, 'denominator': 100, 'expected': 150.0}
  ] %}
  
  {% for test_case in test_cases %}
  SELECT 
    'Testing {{ test_case.numerator }}/{{ test_case.denominator }}' as test_description,
    {{ percentage_calculator(test_case.numerator, test_case.denominator) }} as calculated_percentage,
    {{ test_case.expected }} as expected_percentage,
    CASE 
      WHEN {{ percentage_calculator(test_case.numerator, test_case.denominator) }} IS NULL AND '{{ test_case.expected }}' = 'NULL' THEN 'PASS'
      WHEN ABS({{ percentage_calculator(test_case.numerator, test_case.denominator) }} - {{ test_case.expected }}) < 0.01 THEN 'PASS'
      ELSE 'FAIL'
    END as test_result
  {% if not loop.last %} UNION ALL {% endif %}
  {% endfor %}
{% endmacro %}

Integration Testing for Model Metrics

-- tests/integration/test_model_metrics.sql
{% macro test_model_metrics_integration() %}
  -- Test that all macro-based metrics in a model compile and execute
  
  WITH metric_test AS (
    SELECT 
      employee_id,
      {{ calculate_metric('employee_tenure', {
           'method': 'macro',
           'macro': 'tenure_calculator', 
           'parameters': {'input_date': 'hire_date'}
         }) }} as tenure_test,
      {{ calculate_metric('employee_age', {
           'method': 'macro',
           'macro': 'age_calculator',
           'parameters': {'birth_date': 'date_of_birth'}
         }) }} as age_test
    FROM {{ ref('dim_employees') }}
    LIMIT 10
  )
  
  SELECT 
    COUNT(*) as total_rows,
    COUNT(tenure_test) as tenure_non_null,
    COUNT(age_test) as age_non_null,
    AVG(tenure_test) as avg_tenure,
    AVG(age_test) as avg_age
  FROM metric_test
{% endmacro %}

Macro Documentation and Usage Examples

-- macros/metrics/reusable_metrics.sql (Enhanced with documentation)

{% docs tenure_calculator %}
### Tenure Calculator Macro

Calculates the number of years between a given date and the current date.

#### Parameters:
- `input_date`: Date column or expression to calculate tenure from

#### Returns:
- Integer representing years of tenure
- NULL if input_date is NULL

#### Usage Examples:
```sql
-- Basic usage
SELECT {{ tenure_calculator('hire_date') }} as employee_tenure
FROM employees;

-- With metric system
employee_tenure:
  method: macro
  macro: tenure_calculator
  parameters:
    input_date: hire_date

Edge Cases:

  • NULL input_date returns NULL
  • Future dates return negative values
  • Handles leap years correctly {% enddocs %}

{% docs age_calculator %}

Age Calculator Macro

Calculates age from birth date with validation for impossible dates.

Parameters:

  • birth_date: Birth date column or expression

Returns:

  • Integer representing age in years
  • NULL if birth_date is NULL or in the future

Usage Examples:

-- Basic usage
SELECT {{ age_calculator('date_of_birth') }} as current_age
FROM people;

-- With validation
SELECT 
  {{ age_calculator('date_of_birth') }} as age,
  CASE 
    WHEN {{ age_calculator('date_of_birth') }} IS NULL THEN 'Invalid birth date'
    ELSE 'Valid'
  END as validation_status
FROM people;

{% enddocs %}

{% macro tenure_calculator(input_date) %} {{ doc('tenure_calculator') }} CASE WHEN {{ input_date }} IS NULL THEN NULL ELSE DATEDIFF('year', {{ input_date }}, CURRENT_DATE()) END {% endmacro %}

{% macro age_calculator(birth_date) %} {{ doc('age_calculator') }} CASE WHEN {{ birth_date }} IS NULL THEN NULL WHEN {{ birth_date }} > CURRENT_DATE() THEN NULL ELSE DATEDIFF('year', {{ birth_date }}, CURRENT_DATE()) END {% endmacro %}


#### Custom Documentation Command
```bash
# Create a custom script to enhance dbt docs
#!/bin/bash
# scripts/build_docs_with_metrics.sh

echo "Building dbt docs with metrics catalog..."

# Generate standard dbt docs
dbt docs generate

# Run custom metrics extraction
dbt run-operation generate_metrics_documentation --target prod

# Copy custom assets to docs directory
cp docs_plugins/metrics_catalog.js target/
cp docs_plugins/metrics_catalog.css target/

# Inject custom assets into index.html
python scripts/inject_metrics_assets.py

echo "Enhanced dbt docs with metrics catalog ready!"

Asset Injection Script

# scripts/inject_metrics_assets.py
import os
import re

def inject_metrics_assets():
    """Inject custom metrics assets into dbt docs"""
    
    docs_path = 'target/index.html'
    
    if not os.path.exists(docs_path):
        print("dbt docs not found. Run 'dbt docs generate' first.")
        return
    
    with open(docs_path, 'r') as f:
        content = f.read()
    
    # Inject CSS
    css_injection = '''
    <link rel="stylesheet" href="metrics_catalog.css">
    </head>
    '''
    content = content.replace('</head>', css_injection)
    
    # Inject JavaScript
    js_injection = '''
    <script src="metrics_catalog.js"></script>
    </body>
    '''
    content = content.replace('</body>', js_injection)
    
    with open(docs_path, 'w') as f:
        f.write(content)
    
    print("✅ Metrics catalog assets injected successfully!")

if __name__ == "__main__":
    inject_metrics_assets()

Key Features Delivered:

  1. Reusable Metric Macros: Native dbt macros for commonly used calculations (tenure, age, percentages, growth rates)
  2. Enhanced dbt-docs Integration: Custom metrics catalog with search, filtering, and detailed metric documentation
  3. Macro-Based Metrics: Parameterized macros that can be reused across models with different input columns
  4. Comprehensive Documentation: Automated generation of metric documentation integrated into model pages
  5. Interactive Metrics Catalog: Searchable, filterable catalog of all metrics with macro information

Advanced Capabilities:

  • Native dbt integration with macro-based reusable calculations
  • Visual indicators for macro-based metrics in documentation
  • Grouped metric organization for better navigation
  • Copy-to-clipboard functionality for metric references
  • Search and filter capabilities in the metrics catalog
  • Integration with existing dbt workflow without breaking changes
  • Macro usage examples and parameter documentation

Local dbt Metrics Testing Setup with DuckDB

This guide shows you how to test the metrics framework locally in a dev container using DuckDB (no remote database required).

1. Dev Container Setup

.devcontainer/devcontainer.json

{
  "name": "dbt Metrics Testing",
  "image": "python:3.11-slim",
  "features": {
    "ghcr.io/devcontainers/features/git:1": {},
    "ghcr.io/devcontainers/features/github-cli:1": {}
  },
  "postCreateCommand": "pip install dbt-core dbt-duckdb pandas",
  "customizations": {
    "vscode": {
      "extensions": [
        "bastienboutonnet.vscode-dbt",
        "ms-python.python",
        "redhat.vscode-yaml"
      ]
    }
  },
  "forwardPorts": [8080],
  "remoteUser": "root"
}

requirements.txt

dbt-core==1.7.0
dbt-duckdb==1.7.0
pandas==2.1.0

2. dbt Project Structure

dbt_metrics_test/
├── dbt_project.yml
├── profiles.yml
├── macros/
│   ├── metrics/
│   │   ├── reusable_metrics.sql
│   │   ├── calculate_metric.sql
│   │   └── get_metrics.sql
│   └── tests/
│       └── test_macros.sql
├── models/
│   ├── schema.yml
│   ├── staging/
│   │   └── stg_employees.sql
│   └── marts/
│       └── employee_metrics.sql
├── seeds/
│   ├── raw_employees.csv
│   └── raw_sales.csv
└── tests/
    └── test_metric_calculations.sql

3. Configuration Files

dbt_project.yml

name: 'dbt_metrics_test'
version: '1.0.0'
config-version: 2

profile: 'dbt_metrics_test'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"

models:
  dbt_metrics_test:
    staging:
      +materialized: view
    marts:
      +materialized: table

vars:
  # Test configurations
  test_mode: true

profiles.yml

dbt_metrics_test:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './dbt_metrics_test.duckdb'
      threads: 4

4. Sample Test Data

seeds/raw_employees.csv

employee_id,first_name,last_name,hire_date,date_of_birth,department,salary,department_start_date
1,John,Doe,2020-01-15,1985-03-22,Engineering,75000,2020-01-15
2,Jane,Smith,2019-06-01,1990-07-14,Sales,65000,2021-03-01
3,Bob,Johnson,2021-11-30,1988-12-05,Marketing,58000,2021-11-30
4,Alice,Brown,2018-04-10,1982-09-18,Engineering,82000,2020-06-15
5,Charlie,Wilson,2022-02-14,1995-01-30,Sales,55000,2022-02-14
6,Diana,Davis,2017-08-20,1987-11-12,HR,62000,2019-01-10
7,Frank,Miller,2023-05-01,1992-04-08,Engineering,68000,2023-05-01
8,Grace,Garcia,2020-09-15,1986-06-25,Marketing,61000,2022-01-01

seeds/raw_sales.csv

sale_id,employee_id,sale_date,amount,quota,region
1,2,2024-01-15,12000,10000,North
2,5,2024-01-20,8500,10000,South
3,2,2024-02-10,15000,10000,North
4,5,2024-02-15,9200,10000,South
5,2,2024-03-05,11000,10000,North
6,5,2024-03-12,10500,10000,South

5. Macro Implementation

macros/metrics/reusable_metrics.sql

-- Tenure Calculator Macro
{% macro tenure_calculator(input_date) %}
  CASE 
    WHEN {{ input_date }} IS NULL THEN NULL
    ELSE EXTRACT('year' FROM age(CURRENT_DATE, {{ input_date }}::DATE))
  END
{% endmacro %}

-- Age Calculator Macro
{% macro age_calculator(birth_date) %}
  CASE 
    WHEN {{ birth_date }} IS NULL THEN NULL
    WHEN {{ birth_date }}::DATE > CURRENT_DATE THEN NULL
    ELSE EXTRACT('year' FROM age(CURRENT_DATE, {{ birth_date }}::DATE))
  END
{% endmacro %}

-- Percentage Calculator Macro
{% macro percentage_calculator(numerator, denominator) %}
  CASE 
    WHEN {{ denominator }} = 0 OR {{ denominator }} IS NULL THEN NULL
    ELSE ({{ numerator }}::FLOAT / {{ denominator }}::FLOAT) * 100
  END
{% endmacro %}

-- Growth Rate Calculator Macro
{% macro growth_rate_calculator(current_value, previous_value) %}
  CASE 
    WHEN {{ previous_value }} = 0 OR {{ previous_value }} IS NULL THEN NULL
    ELSE (({{ current_value }} - {{ previous_value }})::FLOAT / {{ previous_value }}::FLOAT) * 100
  END
{% endmacro %}

-- Days Between Calculator
{% macro days_between_calculator(start_date, end_date) %}
  CASE 
    WHEN {{ start_date }} IS NULL OR {{ end_date }} IS NULL THEN NULL
    ELSE ({{ end_date }}::DATE - {{ start_date }}::DATE)
  END
{% endmacro %}

macros/metrics/calculate_metric.sql

{% macro calculate_metric(metric_name, metric_config) %}
  {% if metric_config.get('method') == 'macro' %}
    {# Process macro-based metric #}
    {% set macro_name = metric_config.get('macro') %}
    {% set parameters = metric_config.get('parameters', {}) %}
    {{ call_metric_macro(macro_name, parameters) }}
  {% else %}
    {# Process regular metric #}
    {% set metric_type = metric_config.get('type', 'number') %}
    {% set column_name = metric_config.get('column', metric_name) %}
    
    {%- if metric_type in ['sum', 'average', 'count', 'count_distinct', 'min', 'max'] -%}
      {%- if metric_type == 'sum' -%} SUM({{ column_name }})
      {%- elif metric_type == 'average' -%} AVG({{ column_name }})
      {%- elif metric_type == 'count' -%} COUNT(*)
      {%- elif metric_type == 'count_distinct' -%} COUNT(DISTINCT {{ column_name }})
      {%- elif metric_type == 'min' -%} MIN({{ column_name }})
      {%- elif metric_type == 'max' -%} MAX({{ column_name }})
      {%- endif -%}
    {%- else -%}
      {{ metric_config.get('sql', column_name) }}
    {%- endif -%}
  {% endif %}
{% endmacro %}

{% macro call_metric_macro(macro_name, parameters) %}
  {% set available_macros = {
    'tenure_calculator': 'tenure_calculator',
    'age_calculator': 'age_calculator',
    'percentage_calculator': 'percentage_calculator', 
    'growth_rate_calculator': 'growth_rate_calculator',
    'days_between_calculator': 'days_between_calculator'
  } %}
  
  {% if macro_name not in available_macros %}
    {{ exceptions.raise_compiler_error("Macro '" ~ macro_name ~ "' not found") }}
  {% endif %}
  
  {# Call the macro with parameters #}
  {% if macro_name == 'tenure_calculator' %}
    {{ tenure_calculator(parameters.input_date) }}
  {% elif macro_name == 'age_calculator' %}
    {{ age_calculator(parameters.birth_date) }}
  {% elif macro_name == 'percentage_calculator' %}
    {{ percentage_calculator(parameters.numerator, parameters.denominator) }}
  {% elif macro_name == 'growth_rate_calculator' %}
    {{ growth_rate_calculator(parameters.current_value, parameters.previous_value) }}
  {% elif macro_name == 'days_between_calculator' %}
    {{ days_between_calculator(parameters.start_date, parameters.end_date) }}
  {% endif %}
{% endmacro %}

6. Model Implementation

models/staging/stg_employees.sql

{{ config(materialized='view') }}

SELECT 
    employee_id,
    first_name,
    last_name,
    hire_date::DATE as hire_date,
    date_of_birth::DATE as date_of_birth,
    department,
    salary,
    department_start_date::DATE as department_start_date,
    CURRENT_DATE as snapshot_date
FROM {{ ref('raw_employees') }}

models/schema.yml

version: 2

models:
  - name: stg_employees
    description: "Staging table for employee data"
    columns:
      - name: employee_id
        description: "Unique employee identifier"
        tests:
          - unique
          - not_null

  - name: employee_metrics
    description: "Employee metrics with macro-based calculations"
    meta:
      metrics:
        # Macro-based metrics
        employee_tenure:
          method: macro
          macro: tenure_calculator
          parameters:
            input_date: hire_date
          label: "Employee Tenure"
          description: "Years since employee was hired"
          format: "0"
          groups: ["hr", "tenure"]
          
        employee_age:
          method: macro
          macro: age_calculator
          parameters:
            birth_date: date_of_birth
          label: "Employee Age"
          description: "Current age of employee"
          format: "0"
          groups: ["hr", "demographics"]
          
        department_tenure:
          method: macro
          macro: tenure_calculator
          parameters:
            input_date: department_start_date
          label: "Department Tenure"
          description: "Years in current department"
          format: "0"
          groups: ["hr", "tenure"]

        # Regular aggregate metrics
        avg_salary:
          type: average
          column: salary
          label: "Average Salary"
          description: "Average salary across all employees"
          format: "$#,##0"
          groups: ["hr", "compensation"]
          
        total_employees:
          type: count
          label: "Total Employees"
          description: "Total number of employees"
          groups: ["hr", "headcount"]

    columns:
      - name: employee_id
        description: "Employee identifier"
      - name: employee_tenure
        description: "Years of service"
      - name: employee_age
        description: "Current age"
      - name: department_tenure
        description: "Years in current department"

models/marts/employee_metrics.sql

{{ config(materialized='table') }}

WITH employee_calculations AS (
  SELECT 
    employee_id,
    first_name,
    last_name,
    department,
    hire_date,
    date_of_birth,
    department_start_date,
    salary,
    
    -- Using macro-based metrics
    {{ calculate_metric('employee_tenure', {
         'method': 'macro',
         'macro': 'tenure_calculator',
         'parameters': {'input_date': 'hire_date'}
       }) }} as employee_tenure,
       
    {{ calculate_metric('employee_age', {
         'method': 'macro',
         'macro': 'age_calculator',
         'parameters': {'birth_date': 'date_of_birth'}
       }) }} as employee_age,
       
    {{ calculate_metric('department_tenure', {
         'method': 'macro',
         'macro': 'tenure_calculator',
         'parameters': {'input_date': 'department_start_date'}
       }) }} as department_tenure,
       
    -- Direct macro calls for comparison
    {{ tenure_calculator('hire_date') }} as tenure_direct,
    {{ age_calculator('date_of_birth') }} as age_direct,
    {{ days_between_calculator('hire_date', 'CURRENT_DATE') }} as days_employed
    
  FROM {{ ref('stg_employees') }}
)

SELECT 
  *,
  -- Calculated metrics
  CASE 
    WHEN employee_tenure >= 5 THEN 'Senior'
    WHEN employee_tenure >= 2 THEN 'Mid-level'
    ELSE 'Junior'
  END as seniority_level,
  
  CASE 
    WHEN employee_age < 30 THEN 'Gen Z'
    WHEN employee_age < 45 THEN 'Millennial'
    WHEN employee_age < 60 THEN 'Gen X'
    ELSE 'Boomer'
  END as generation
  
FROM employee_calculations

7. Testing Framework

macros/tests/test_macros.sql

-- Test macro functionality
{% macro test_tenure_calculator() %}
  {{ log("Testing tenure_calculator macro...", info=true) }}
  
  WITH test_data AS (
    SELECT 
      '2020-01-01'::DATE as test_date,
      {{ tenure_calculator("'2020-01-01'::DATE") }} as calculated_tenure
  )
  
  SELECT 
    test_date,
    calculated_tenure,
    CASE 
      WHEN calculated_tenure BETWEEN 4 AND 5 THEN 'PASS'
      ELSE 'FAIL'
    END as test_result
  FROM test_data
{% endmacro %}

{% macro test_age_calculator() %}
  {{ log("Testing age_calculator macro...", info=true) }}
  
  WITH test_data AS (
    SELECT 
      '1990-01-01'::DATE as birth_date,
      {{ age_calculator("'1990-01-01'::DATE") }} as calculated_age
  )
  
  SELECT 
    birth_date,
    calculated_age,
    CASE 
      WHEN calculated_age BETWEEN 33 AND 35 THEN 'PASS'
      ELSE 'FAIL'
    END as test_result
  FROM test_data
{% endmacro %}

{% macro test_percentage_calculator() %}
  {{ log("Testing percentage_calculator macro...", info=true) }}
  
  SELECT 
    75 as numerator,
    100 as denominator,
    {{ percentage_calculator(75, 100) }} as calculated_percentage,
    CASE 
      WHEN {{ percentage_calculator(75, 100) }} = 75.0 THEN 'PASS'
      ELSE 'FAIL'
    END as test_result
{% endmacro %}

tests/test_metric_calculations.sql

-- Test that all employees have valid metrics
SELECT 
  employee_id,
  employee_tenure,
  employee_age,
  department_tenure
FROM {{ ref('employee_metrics') }}
WHERE 
  employee_tenure IS NULL 
  OR employee_age IS NULL 
  OR employee_tenure < 0
  OR employee_age < 18
  OR employee_age > 100

8. Local Testing Commands

# Initialize the project
cd dbt_metrics_test

# Install dependencies
pip install -r requirements.txt

# Load test data
dbt seed

# Test macros individually
dbt run-operation test_tenure_calculator
dbt run-operation test_age_calculator  
dbt run-operation test_percentage_calculator

# Run staging models
dbt run --select staging

# Run metrics models
dbt run --select employee_metrics

# Run all tests
dbt test

# Check specific test
dbt test --select test_metric_calculations

# Generate docs
dbt docs generate
dbt docs serve --port 8080

# Debug individual metrics
dbt run --select employee_metrics --vars '{"debug": true}'

9. Quick Validation Queries

-- Connect to DuckDB and run these queries to validate
.open dbt_metrics_test.duckdb

-- Check basic metrics
SELECT 
  employee_id,
  first_name,
  employee_tenure,
  employee_age,
  department_tenure,
  seniority_level
FROM main.employee_metrics
ORDER BY employee_tenure DESC;

-- Validate macro calculations
SELECT 
  'Tenure Test' as test_type,
  AVG(employee_tenure) as avg_tenure,
  MIN(employee_tenure) as min_tenure,
  MAX(employee_tenure) as max_tenure
FROM main.employee_metrics
UNION ALL
SELECT 
  'Age Test' as test_type,
  AVG(employee_age) as avg_age,
  MIN(employee_age) as min_age,
  MAX(employee_age) as max_age
FROM main.employee_metrics;

10. Getting Started

  1. Create the dev container and files above
  2. Open in VS Code with dev containers extension
  3. Run the setup commands
  4. Start testing your macros!

This setup gives you a complete local environment to test the metrics framework without needing any external database connections. DuckDB handles all the SQL processing locally and supports most of the functions needed for the metrics calculations.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment