malloy

// models/ecommerce.malloy — Ecommerce data model

source: orders is duckdb.table('orders.parquet') extend {
  // Dimensions (attributes to group by)
  dimension:
    order_date is created_at::date
    order_month is created_at.month
    order_year is created_at.year
    is_high_value is amount > 100
    order_size is pick
      'small' when items_count < 3
      'medium' when items_count < 10
      'large'

  // Measures (aggregations)
  measure:
    order_count is count()
    total_revenue is sum(amount)
    avg_order_value is avg(amount)
    unique_customers is count(distinct customer_id)
    revenue_per_customer is total_revenue / unique_customers

  // Reusable named queries (views)
  view: revenue_by_month is {
    group_by: order_month
    aggregate:
      total_revenue
      order_count
      avg_order_value
    order_by: order_month
  }

  view: top_customers is {
    group_by: customer_id
    aggregate:
      total_revenue
      order_count
    order_by: total_revenue desc
    limit: 20
  }

  view: daily_dashboard is {
    group_by: order_date
    aggregate:
      total_revenue
      order_count
      unique_customers
      avg_order_value
    order_by: order_date desc
    limit: 30
  }
}

// queries/analysis.malloy — Analytics queries using the model

import "models/ecommerce.malloy"

// Simple aggregation
run: orders -> {
  aggregate:
    total_revenue
    order_count
    avg_order_value
}

// Group by with filters
run: orders -> {
  where: order_year = 2026
  group_by: order_month
  aggregate:
    total_revenue
    order_count
  order_by: order_month
}

// Nested queries — multiple levels of aggregation in one query
run: orders -> {
  group_by: order_size
  aggregate:
    total_revenue
    order_count
    avg_order_value
  // Nested: for each order_size, show monthly breakdown
  nest: monthly_trend is {
    group_by: order_month
    aggregate: total_revenue
    order_by: order_month
  }
  // Nested: for each order_size, show top customers
  nest: top_customers is {
    group_by: customer_id
    aggregate: total_revenue, order_count
    order_by: total_revenue desc
    limit: 5
  }
}

// Pipeline: chain transformations
run: orders
  -> { where: status = 'completed' }
  -> revenue_by_month                   // Reuse named view
  -> { where: total_revenue > 10000 }   // Filter the result

// models/full_model.malloy — Multi-table model with joins

source: customers is duckdb.table('customers.parquet') extend {
  dimension: signup_month is created_at.month
  measure:
    customer_count is count()
    avg_lifetime_value is avg(lifetime_value)
}

source: products is duckdb.table('products.parquet') extend {
  dimension: price_tier is pick
    'budget' when price < 25
    'mid-range' when price < 100
    'premium'
  measure: product_count is count()
}

source: order_items is duckdb.table('order_items.parquet') extend {
  // Join to related tables
  join_one: orders on order_id = orders.id
  join_one: products on product_id = products.id
  join_one: customers is orders.customer_id = customers.id

  measure:
    total_quantity is sum(quantity)
    item_revenue is sum(quantity * unit_price)

  // Query across joined tables
  view: revenue_by_category is {
    group_by: products.category
    aggregate:
      item_revenue
      total_quantity
    order_by: item_revenue desc
  }

  view: customer_product_matrix is {
    group_by: customers.signup_month
    aggregate: item_revenue
    nest: by_category is {
      group_by: products.category
      aggregate: item_revenue
    }
  }
}

// In Malloy notebook (.malloynb) or VS Code extension

// Malloy auto-renders results as charts when appropriate

// Bar chart — group by with single measure
run: orders -> {
  group_by: status
  aggregate: order_count
}
// # bar_chart

// Line chart — time series
run: orders -> revenue_by_month
// # line_chart

// Dashboard — multiple visualizations from one query
run: orders -> {
  group_by: order_size
  aggregate: total_revenue, order_count
  nest: trend is {
    group_by: order_month
    aggregate: total_revenue
  }
}
// # dashboard

// Connection configuration
// DuckDB (local files)
connection: duckdb is duckdb [
  parquet_path: "./data/"
]

// BigQuery
connection: bq is bigquery [
  project_id: "my-gcp-project"
  dataset: "analytics"
]

// Use BigQuery tables in models
source: events is bq.table('analytics.events') extend {
  measure: event_count is count()
}

# VS Code Extension (recommended)
# Install "Malloy" from VS Code Marketplace

# CLI
npm install -g @malloydata/malloy-cli

# Python package
pip install malloy

# Run a Malloy file
malloy run analysis.malloy

Add Malloy to my Next.js app for the AI chat feature. I want streaming responses.

My Malloy calls are slow and expensive. Help me optimize the setup.