AQL Best Practices

This page collects practical advice for writing AQL well. Unlike AQL Validation Rules (which lists hard constraints), these are recommendations. Following them keeps your semantic layer maintainable as it grows.

Model design

Define a primary key on every model

Not strictly required, but strongly recommended. A primary key lets AQL determine the grain of your data, drop unnecessary GROUP BY clauses, and support Single Model Conditions. Without one, queries are less efficient and more prone to fan-out.

// ✅ Good
Model orders {
  dimension id {
    type: 'number'
    primary_key: true
  }
}

// ⚠️ Works, but no grain information
Model orders {
  dimension id { type: 'number' }
}

Metric design

Don't reference dimensions without aggregating them

A metric that returns a raw dimension value is fragile. It only works when that dimension is also in the explore, and end users typically expect a metric to be a single number. Always aggregate.

// ✅ Good
metric total_revenue {
  definition: @aql sum(orders.amount) ;;
}

// ❌ Bad: returns raw values, breaks when dimension isn't grouped
metric total_revenue {
  definition: @aql orders.amount ;;
}

Don't hard-code time periods or dimensions into metric definitions

Metrics should adapt to whatever grouping the end user chooses. Hard-coding a group(... | month()) inside the metric turns it into a table rather than a scalar and locks downstream reports into that grain. Use grouping inside a metric only when the grouping is essential to the calculation itself (e.g., nested aggregation).

// ❌ Bad: returns a table of monthly values, not a single number
metric monthly_sales {
  definition: @aql
    orders
    | group(orders.created_at | month())
    | select(sum(orders.amount))
  ;;
}

// ✅ Good: let the user pick the time grain in the report
metric sales {
  definition: @aql sum(orders.amount) ;;
}

// ✅ Good: grouping is essential because we want avg-of-monthly-totals
metric avg_monthly_sales {
  definition: @aql
    orders
    | group(orders.created_at | month())
    | select(sum(orders.amount))
    | avg()
  ;;
}

Reuse metrics instead of duplicating logic

Build complex metrics from simpler ones. One source of truth means one place to fix bugs.

// ✅ Good
metric revenue {
  definition: @aql sum(order_items.amount) ;;
}

metric revenue_last_month {
  definition: @aql revenue | relative_period(orders.created_at, interval(-1 month)) ;;
}

metric revenue_vs_last_month {
  definition: @aql revenue - revenue_last_month ;;
}

// ❌ Bad: duplicating the base logic
metric revenue_vs_last_month {
  definition: @aql
    sum(order_items.amount)
    - (sum(order_items.amount) | relative_period(orders.created_at, interval(-1 month)))
  ;;
}

Document complex logic

Add a description for any metric whose definition isn't self-explanatory. Future you, and every downstream analyst, will thank you.

metric customer_churn_rate {
  label: 'Customer Churn Rate'
  description: 'Percentage of customers who cancelled in the period'
  definition: @aql
    safe_divide(
      (count(users.id) | where(users.status == 'churned')) * 100,
      count(users.id) | where(users.created_at < first(users.period_param))
    )
  ;;
}

Data quality

Use safe_divide instead of / for ratios

safe_divide returns null when the denominator is zero. Plain / raises a runtime error.

// ✅ Good
metric average_order_value {
  definition: @aql safe_divide(revenue, order_count) ;;
}

// ❌ Bad: explodes when order_count is 0
metric average_order_value {
  definition: @aql revenue / order_count ;;
}

Wrap counts in coalesce when "no rows matched" should mean zero

Unlike SQL, AQL returns null (not 0) when a filter matches no rows. If your visualization expects 0, use coalesce.

// ✅ Good: returns 0 when no completed orders exist
metric completed_orders {
  definition: @aql
    coalesce(
      count(orders.id) | where(orders.status == 'completed'),
      0
    )
  ;;
}

// ❌ Bad: returns null, which charts handle inconsistently
metric completed_orders {
  definition: @aql count(orders.id) | where(orders.status == 'completed') ;;
}

Performance

Use PreAggregates for frequently accessed or expensive metrics

PreAggregates pre-compute and store aggregated data, dramatically reducing query latency. They're especially valuable for dashboard metrics over large fact tables, or for metrics that involve complex multi-step calculations.

Start with Aggregate Awareness for the concept, then Building Multiple PreAggregates for the patterns.

Common pitfalls

Don't pick a wrong source table when one is needed

In most cases, AQL infers the source table from the expression and you don't need to specify it. When you do specify one (because the expression mixes models), make sure it matches the grain you're measuring.

// ✅ Good: let AQL infer
metric order_count {
  definition: @aql count(orders.id) ;;
}

// ✅ Good: explicit because fields span two models
metric revenue {
  definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}

// ❌ Bad: explicit source contradicts what's being counted
metric order_count {
  definition: @aql count(order_items, orders.id) ;;
  // Counts order_items rows, so an order with 5 items is counted 5 times
}

Rule of thumb: let AQL infer unless fields come from multiple models. When you do specify, the source table is the grain you're aggregating over.

Don't assume null behaves like SQL

AQL returns null (not 0) when a filter matches nothing. Plan for that with coalesce or safe_divide where it matters: see Data quality above.

Model design​

Metric design​

Data quality​

Performance​

Common pitfalls​

Don't pick a wrong source table when one is needed​

Don't assume null behaves like SQL​

See also​