Examples

models:
  - name: orders
    description: Customer orders
    dimensions:
      - name: id
        description: Order ID
        type: string
      - name: order_date
        description: Date the order was placed
        type: date
        searchable: true
      - name: status
        description: Order status
        options: ["pending", "shipped", "delivered", "cancelled"]
    measures:
      - name: amount
        description: Order amount in USD
        type: number
      - name: tax
        description: Tax amount in USD
        type: number
    metrics:
      - name: total_revenue
        expr: "SUM(amount)"
        description: Total revenue from orders
      - name: average_order_value
        expr: "AVG(amount)"
        description: Average order value
      - name: order_count
        expr: "COUNT(*)"
        description: Number of orders
    filters:
      - name: completed_orders
        expr: "status IN ('shipped', 'delivered')"
        description: Orders that have been shipped or delivered
      - name: recent_orders
        expr: "order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL {{days}} DAY)"
        description: Orders from the last N days
        args:
          - name: days
            type: integer
            description: Number of days to look back
    entities:
      - name: customers
        primary_key: id
        foreign_key: customer_id
        cardinality: many-to-one
        description: Customer who placed the order
      - name: order_items
        primary_key: id
        foreign_key: order_id
        cardinality: one-to-many
        description: Items included in this order

  - name: customers
    description: Customer accounts
    dimensions:
      - name: id
        description: Customer ID
        type: string
      - name: name
        description: Customer name
        searchable: true
      - name: email
        description: Customer email address
        searchable: true
      - name: sign_up_date
        description: Date customer signed up
        type: date
    measures:
      - name: lifetime_value
        description: Customer lifetime value in USD
        type: number
    metrics:
      - name: customer_count
        expr: "COUNT(*)"
        description: Number of customers
      - name: average_lifetime_value
        expr: "AVG(lifetime_value)"
        description: Average lifetime value of customers
    entities:
      - name: orders
        primary_key: customer_id
        foreign_key: id
        cardinality: one-to-many
        description: Orders placed by this customer

  - name: products
    description: Products available for sale
    dimensions:
      - name: id
        description: Product ID
        type: string
      - name: name
        description: Product name
        searchable: true
      - name: category
        description: Product category
        options: ["electronics", "clothing", "home", "books"]
    measures:
      - name: price
        description: Product price in USD
        type: number
      - name: cost
        description: Product cost in USD
        type: number
    metrics:
      - name: product_count
        expr: "COUNT(*)"
        description: Number of products
      - name: average_price
        expr: "AVG(price)"
        description: Average product price
      - name: margin
        expr: "SUM(price - cost) / SUM(price)"
        description: Average product margin
    entities:
      - name: order_items
        primary_key: product_id
        foreign_key: id
        cardinality: one-to-many
        description: Order items containing this product

  - name: order_items
    description: Individual items within orders
    dimensions:
      - name: id
        description: Order item ID
        type: string
    measures:
      - name: quantity
        description: Number of items ordered
        type: integer
      - name: unit_price
        description: Price per unit in USD
        type: number
    metrics:
      - name: total_items
        expr: "SUM(quantity)"
        description: Total number of items sold
      - name: average_quantity
        expr: "AVG(quantity)"
        description: Average quantity per order item
    entities:
      - name: orders
        primary_key: id
        foreign_key: order_id
        cardinality: many-to-one
        description: Order containing this item
      - name: products
        primary_key: id
        foreign_key: product_id
        cardinality: many-to-one
        description: Product in this order item

models:
  - name: subscriptions
    description: Customer subscriptions
    dimensions:
      - name: id
        description: Subscription ID
        type: string
      - name: start_date
        description: Date subscription started
        type: date
      - name: end_date
        description: Date subscription ended (null if active)
        type: date
      - name: plan
        description: Subscription plan
        options: ["free", "basic", "pro", "enterprise"]
      - name: billing_interval
        description: Billing frequency
        options: ["monthly", "annual"]
    measures:
      - name: monthly_amount
        description: Monthly subscription amount in USD
        type: number
    metrics:
      - name: mrr
        expr: "SUM(monthly_amount)"
        description: Monthly recurring revenue
      - name: active_subscriptions
        expr: "COUNT(CASE WHEN end_date IS NULL THEN 1 END)"
        description: Number of active subscriptions
      - name: churn_rate
        expr: "COUNT(CASE WHEN end_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE() THEN 1 END) / NULLIF(COUNT(CASE WHEN end_date IS NULL OR end_date > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) THEN 1 END), 0)"
        description: 30-day subscription churn rate
    filters:
      - name: active
        expr: "end_date IS NULL"
        description: Active subscriptions
      - name: plan_tier
        expr: "plan = {{plan}}"
        description: Subscriptions on a specific plan
        args:
          - name: plan
            type: string
            description: Subscription plan
    entities:
      - name: users
        primary_key: id
        foreign_key: user_id
        cardinality: many-to-one
        description: User who owns this subscription

  - name: users
    description: Application users
    dimensions:
      - name: id
        description: User ID
        type: string
      - name: email
        description: User email
        searchable: true
      - name: signup_date
        description: Date user signed up
        type: date
      - name: last_login
        description: Date of last login
        type: timestamp
    metrics:
      - name: user_count
        expr: "COUNT(*)"
        description: Number of users
      - name: active_users
        expr: "COUNT(CASE WHEN last_login >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) THEN 1 END)"
        description: Users active in the last 30 days
    entities:
      - name: subscriptions
        primary_key: user_id
        foreign_key: id
        cardinality: one-to-many
        description: Subscriptions owned by this user
      - name: usage_events
        primary_key: user_id
        foreign_key: id
        cardinality: one-to-many
        description: Usage events by this user

  - name: usage_events
    description: User activity and usage data
    dimensions:
      - name: id
        description: Event ID
        type: string
      - name: event_time
        description: Time the event occurred
        type: timestamp
      - name: event_type
        description: Type of event
        options: ["login", "feature_used", "export", "api_call"]
      - name: feature
        description: Feature that was used (for feature_used events)
        type: string
    measures:
      - name: duration
        description: Duration of the event in seconds
        type: number
    metrics:
      - name: event_count
        expr: "COUNT(*)"
        description: Number of events
      - name: average_duration
        expr: "AVG(duration)"
        description: Average event duration
      - name: feature_usage
        expr: "COUNT(CASE WHEN event_type = 'feature_used' AND feature = {{feature_name}} THEN 1 END)"
        description: Usage count for a specific feature
        args:
          - name: feature_name
            type: string
            description: Name of the feature
    entities:
      - name: users
        primary_key: id
        foreign_key: user_id
        cardinality: many-to-one
        description: User who triggered this event