Holistics AQL learning Side quest 0008

Side quest / AQL semantics

Order of operations of an AQL query

Source: Holistics docs: AQL order of operations. Tangible win: predict, line by line, when each part of an AQL query runs, and explain it in SQL terms.

Purpose of this lesson

Have you ever tried to debug "why does my filter not change this metric"?

You hit these pain points:

  1. You added orders.country == 'US' in filters {}, but a "percent of total" metric did not move. You suspect a bug, but the metric is doing exactly what it should at a different step.
  2. You wrote a window function like previous() or rank() in a dimension and a downstream filter unexpectedly changed the ranks.
  3. You explained to a customer "we filter, then we aggregate", but the actual answer is more nuanced and you cannot point at the rule.

This lesson solves it: walk the seven-step AQL pipeline in SQL terms, with diagrams, so you can name the step that controls each behavior and stop guessing.

Why this matters for your mission: presales calls and customer questions about "why is this number wrong" almost always reduce to a step in this pipeline. Knowing the order replaces hand-waving with precise answers.

SQL refresher: logical query order

SQL is written top-down (SELECT ... FROM ... WHERE ...) but executed in a different logical order. Most engines run the clauses like this:

  1. FROM and JOIN: build the row set.
  2. WHERE: filter rows.
  3. GROUP BY: collapse rows into groups.
  4. HAVING: filter on aggregated values.
  5. SELECT: compute final columns (including window functions).
  6. ORDER BY and LIMIT: shape output.

Two filters, before and after aggregation. AQL has the same idea, with explicit names.

AQL pipeline in 7 steps

1. Setup 2. Pre-aggregation 3. Aggregation and post 1 Create model CTEs Materialize each model as a CTE the rest of the query reads. SQL: FROM, JOIN 2 Apply query params Resolve query-model parameters, if the dataset has any. SQL: template substitution 3 Execute AQL dimensions Compute every dimension expression, including window functions like rank(), previous(), ntile(). SQL: SELECT (computed cols) 4 Drop excluded-dim filters For metrics using of_all or exclude, any filter on the excluded dimension is removed for that metric. SQL: per-metric WHERE rewrite 5 Apply dimension filters Remaining filters on dimensions are applied to the row set before any aggregate runs. SQL: WHERE 6 Aggregations and metrics Evaluate measures: sum, count, avg, metric formulas, ratios. Groups are formed by dimensions. SQL: GROUP BY + agg funcs 7 Apply measure filters Filter conditions that reference measures or metric outputs run on the aggregated result. SQL: HAVING Result rows returned Ordering and limit (if any) shape the final output.
The pipeline runs left to right across three phases: setup, pre-aggregation, aggregation and post.

AQL step to SQL clause

StepAQL actionSQL analogue
1Create model CTEsFROM + JOIN (driven by relationships)
2Apply query paramsTemplate substitution before run
3Execute dimensions, including window funcsSELECT computed columns and window functions
4Drop filters on excluded dimensions per metricPer-metric WHERE rewrite (no SQL equivalent in one place)
5Apply filters on dimensionsWHERE
6Run aggregations and metric logicGROUP BY with aggregate functions
7Apply filters on measures or metricsHAVING

The big two: dimension filter vs metric filter

This is the rule that solves the most "why did my number not change" questions:

Filter at step 5 (dimension)

explore {
  measures {
    revenue: order_items | sum(order_items.revenue),
  }
  dimensions {
    countries.name,
  }
  filters {
    countries.name == 'US',
  }
}

SQL equivalent: WHERE countries.name = 'US'. US-only rows feed the sum.

Filter at step 7 (measure)

explore {
  measures {
    revenue: order_items | sum(order_items.revenue),
  }
  dimensions {
    countries.name,
  }
  filters {
    revenue > 1000000,
  }
}

SQL equivalent: HAVING SUM(revenue) > 1000000. All countries aggregate; only those above the threshold survive.

Gotcha: step 4 drops filters when of_all excludes a dimension

A metric that uses of_all(model) or exclude(model) wants to "ignore that model when calculating". As a result, any filter that references the excluded dimension is dropped for that metric only. The dimension still appears in the result, but the metric is calculated as if the filter were not there. 
metric percent_of_total {
  definition: @aql revenue * 1.0 / (revenue | of_all(countries));;
}

explore {
  measures { percent_of_total }
  dimensions { countries.name }
  filters { countries.name in ['US', 'UK'] }
}

What happens:

Two workarounds

1. Pre-filter as a metric.

Promote the condition into a metric of its own and filter on the metric at step 7. Step 7 sees the post-aggregation world.

2. Embed the condition.

Move the condition inside the metric via case() or where() so it is part of the aggregation, not an outer filter.

Window functions in dimensions: always at step 3

Functions like rank(), ntile(), previous() used inside dimensions { } run at step 3, before any dimension filter at step 5. That means a top-N filter on a rank does not change the ranks themselves; ranks are already computed across the full dataset.

explore {
  dimensions {
    countries.name,
    rev_rank: rank(revenue) | over(),
  }
  measures { revenue }
  filters {
    rev_rank <= 5,
  }
}

Reading: rev_rank is computed across every country at step 3. The filter then keeps only the rows where rev_rank is at most 5. You see the top 5 globally, not the top 5 among an already filtered subset.

Putting it together: read a query like the pipeline

  1. Identify the source model(s). That is steps 1 and 2.
  2. Read every dimension. Computed and window dimensions are evaluated up front (step 3).
  3. List filters. Mark each one: dimension or measure.
  4. Drop dimension filters that target excluded models in metrics that use of_all or exclude (step 4).
  5. Apply remaining dimension filters (step 5).
  6. Aggregate with measures and metrics (step 6).
  7. Apply remaining measure filters (step 7).
Customer-safe explanation: "Some filters are applied before we count, some after. If a metric ignores a dimension on purpose, the matching filter is ignored too. That is how AQL keeps share-of-total math correct."

Retrieval practice

1. filters { orders.status == 'delivered' } alongside a sum(orders.amount) measure runs at which step?
2. filters { revenue > 1_000_000 } on a metric runs at which step?
3. A metric uses of_all(countries). The explore also has filters { countries.name == 'US' }. For that metric the filter is...
4. A window function in a dimension is computed over...

Read next

Primary source: Holistics docs: AQL order of operations. Related: Metric context, Level of detail.

Back to the spine

Return to Lesson 0007 - Relationships or the course map.