AQL Overview

The paradigm shift: metrics as first-class objects

In most BI tools, a metric is a SQL string with some metadata. Useful for slicing and grouping, but the moment you need a period comparison, a cohort, a percent-of-total, or any composition of metrics, the abstraction breaks. You fall back to writing more SQL, building more models, or copying logic into spreadsheets. This is what causes the semantic ceiling.

AQL (Analytics Query Language) takes a different approach. Metrics are first-class composable objects, defined once, then combined, transformed, and reused. Period comparisons, level-of-detail modifiers, ratios across grains, nested aggregations: these stay inside the metric layer instead of leaking into derived tables.

This paradigm shift is what makes the semantic layer expressive. And expressiveness is what makes it possible for both AI and human users to reason from your governed business definitions instead of raw schema. See Why Holistics for the structural argument, and Why Holistics AI is reliable for how AQL specifically enables trustworthy AI analytics.

What AQL is

AQL is both a query language and a metric definition language. It uses the data semantic model defined with AML to query data at a higher level of abstraction, with composable metric-based queries as the centerpiece.

What you get:

• Reusable metrics: define once, use everywhere.
• Context-aware: metrics adapt automatically to filters, grouping, and time periods.
• Composable: build complex metrics from simpler ones.
• Business-friendly: express logic in terms users understand.

With AQL, you can implement analytics use cases that are typically painful in SQL: percent of total, nested aggregations, levels of detail, period-over-period comparisons, cohort retention, and running totals. All without falling back to custom SQL.

When executed, AQL compiles deterministically to SQL and works with most SQL databases. Currently supported: PostgreSQL, Amazon Redshift, Google BigQuery, Snowflake, Databricks, Microsoft SQL Server, Clickhouse, with more being added. The compiled SQL is inspectable, which makes it useful both for debugging and for building trust in AI-generated queries.

For a deeper structural argument on AQL versus SQL, see AQL vs SQL.

A quick example

Here's a simple AQL metric that calculates revenue:

metric revenue {
  label: 'Revenue'
  type: 'number'
  definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}

This metric can be used in any report, automatically adapting to whatever dimensions, filters, or time periods are applied to it.

Why AQL

In the previous version of Holistics' semantic layer (3.0), metrics were defined using SQL. This approach had the advantage of being familiar to data analysts and addressed SQL's scalability and reusability issues. However, our experience with common yet more advanced analytics use cases (such as time comparisons and metrics with varied levels of detail) reveals that the effort required is still not ideal.

To solve those use cases, instead of working directly with the SQL definition of a metric, users often need to return to build additional models and datasets as a foundation for the metric to function properly. This not only increases the maintenance burden for data analysts but also requires business users to remember to use the correct dataset for each specific use case. This adds mental friction to the adoption of self-service analytics.

The root cause of the problem is due to the limitation of SQL in terms of composability. Ideally, we want a solution where metrics can be easily composed to solve common analytics use cases above, without the need to constantly go back to SQL to build additional models and datasets. It should be simple enough that even more savvy business users can use. Yet, it needs to be able to compile to SQL to fit with Holistics' SQL-based approach. AQL was conceived as that ideal solution.

Design principles

• Locality: Common metrics-based analytics use cases can be done locally within the metric definition, without the need to modify the underlying data models and datasets using custom SQL.
• Composability: Metrics logic can be broken down into smaller components and incrementally composed. This enhances maintainability and reusability.
• Built-in analytical abilities: Most common metrics analytics use cases can be done using a single pre-built AQL function; more complex ones are composed from a few functions.
• Instant feedback: A strong type system and IDE allow a faster feedback loop during development compared to SQL.
• Query performance: Semantic understanding of metrics enables more fine-grained performance optimization.
• Complement, not a replacement of SQL: AQL is not designed to replace SQL, but to cover SQL's gap when it comes to metrics.

Learning roadmap

If you want the firehose version, start with AQL in 30 Minutes: a skim-friendly tour of every concept.

For the slow path, work through these pages in order. Each one introduces a single concept with a small, working example. Every page also has a matching hands-on exercise in the AQL Playground.

Foundations

1. What AQL is for: the destination: metrics defined once, queried anywhere.
2. The pipe operator: | chains steps left-to-right.
3. Filtering: where() vs filter().
4. Grouping and aggregation: turn many rows into one number per group.
5. Cross-model queries: reference columns from related models without writing joins.
6. Defining a metric: name an aggregation so you can reuse it.
7. Metric context: why the same metric returns different numbers in different reports.

Advanced patterns

8. Level of detail: of_all(), exclude(), dimensionalize().
9. Nested aggregation: aggregate already-aggregated data.
10. Time comparisons: period-over-period without rewriting the metric.

Then Where to Define AQL for the model-vs-dataset-vs-ad-hoc decision. Coming from SQL? Read AQL vs SQL and Learn AQL from SQL Background.

For copy-paste-ready patterns, jump to Examples & Recipes. For function and operator signatures, see the Reference top-nav.