Holistics AQL learning Lesson 0012

AQLearn 2.3 / Concept

Table vs Scalar outputs

Source lesson: AQLearn 2.3: Table vs Scalar Outputs. Tangible win: predict whether each step in a pipeline produces a table or a single value, and use that to know which function can come next.

Purpose of this lesson

Have you ever tried to write something like "count delivered orders" and gotten a confusing error from AQL?

You hit these pain points:

  1. You wrote products | count(products.id) | filter(products.id > 100) and AQL refused. The error message is not always obvious.
  2. You cannot guess which functions are valid after a count versus after a filter. You try things until one works.
  3. You explain pipelines to a teammate but cannot articulate why some orders of steps are illegal.

This lesson solves it: every AQL function has an input shape and an output shape. There are two shapes only: table and scalar. Once you tag each function, every pipeline becomes legal-by-construction.

Why this matters for your mission: the rest of chapter 2 (filter, select, group, nested aggregations) is just function composition over these two shapes. Get the shapes right and the rest of AQL stops surprising you.

Two shapes, that is the whole language

Table

Rows and columns. The thing AQL passes from one step to the next when you have not aggregated yet.

orders
order_items | filter(...)
products | filter(price > 50)

Scalar

One value per dimension row. Numbers, text, dates. Whatever you would put in a chart's "value" slot.

count(orders.id)
sum(order_items.quantity)
max(orders.created_at)

Each function has an input shape and an output shape

Table orders filter() filter(orders.status == 'delivered') Table narrower row set Table orders count() count(orders.id) Scalar one number Scalar count(orders.id) filter() expects Table filter(orders.id > 100) Type error scalar fed into a table input
Each function has a fixed input and output shape. Compose only when the previous output matches the next input.

Cheat sheet

FunctionInput shapeOutput shape
count, sum, avg, min, max, count_distinctTableScalar
filter()TableTable
select()TableTable
group()TableTable
where()Scalar (a metric)Scalar (filtered metric)
of_all, keep_grains, dimensionalize, excludeScalarScalar
year(), month(), date_trunc()Scalar (datetime)Scalar (datetime)

Two pipelines, one legal and one not

Legal: filter (Table) before count (Scalar)

metric high_id_count = products
  | filter(products.id > 100)
  | count(products.id);
  1. products is a Table.
  2. filter takes a Table and returns a Table.
  3. count takes a Table and returns a Scalar.
  4. Pipeline output: Scalar. Valid.

Illegal: count first, then filter

// Type error: count() output is Scalar but filter() expects Table.
metric high_id_count_bad = products
  | count(products.id)
  | filter(products.id > 100);
  1. products is a Table.
  2. count turns it into a Scalar.
  3. filter wants a Table, gets a Scalar.
  4. AQL rejects the pipeline.
If you ever want to "filter after aggregating", reach for where() on the metric instead. where() attaches a filter to a Scalar metric and returns a Scalar.

Scalar-on-Scalar with where

metric delivered_count = count(orders.id)
  | where(orders.status == 'delivered');

This is the same answer as "filter then count" in many cases, expressed at the metric level. See AQLearn: where vs filter vs explore filters for the deep dive.

Reading a pipeline shape by shape

Walk left to right. Tag the shape after each step.

order_items                              // Table
  | filter(order_items.quantity > 1)     // Table
  | sum(order_items.quantity)            // Scalar

users                                    // Table
  | filter(users.gender == 'Female')      // Table
  | count(users.id)                       // Scalar
  | where(countries.name == 'Vietnam')    // Scalar
Customer-safe explanation: "Each step has an input shape and an output shape. You can only chain when the previous output fits the next input. That is why some orders of steps work and others do not."

Retrieval practice

1. The output of orders | count(orders.id) is a...
2. orders | filter(orders.status == 'delivered') outputs a...
3. Is products | count(products.id) | filter(products.id > 100) valid?
4. Which function lets you attach a filter to a Scalar metric?

Do in AQLearn

  1. Open AQLearn Table vs Scalar Outputs.
  2. Try the legal pipeline products | filter(products.id > 100) | count(products.id). Run and read the result.
  3. Swap the order to products | count(products.id) | filter(...). Read the error AQL gives you.
  4. Now rewrite as count(products.id) | where(products.id > 100) and confirm the value matches the first try.

Next local lesson

Next: Function: select & group (1). The two Table-in, Table-out building blocks that power custom aggregations.