# AQL Best Practices

> Advisory guidance for writing AQL: model design, metric design, performance, and the SQL-to-AQL pitfalls that catch people most often.

This page collects practical advice for writing AQL well. Unlike [AQL Validation Rules](/as-code/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](/reference/aql/where#single-model-condition). Without one, queries are less efficient and more prone to fan-out.

```aml
// ✅ 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.

```aml
// ✅ 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).

```aml
// ❌ 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.

```aml
// ✅ 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.

```aml
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.

```aml
// ✅ 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`.

```aml
// ✅ 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](/docs/aggregate-awareness/quick-start) for the concept, then [Building Multiple PreAggregates](/as-code/aml/use-cases/build-multiple-pre-aggregates) 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.

```aml
// ✅ 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](#data-quality) above.

## See also

- [AQL Validation Rules](/as-code/aql/validation-rules): hard constraints
- [Order of Operations](/as-code/aql/order-of-operations): when filters and metrics evaluate
- [Cookbook](/as-code/aql/cookbook/metrics-by-example): worked examples of the patterns above