by Holistics
Try with anfra

Capability report · 20 Oct 2026

Five semantic layers, eighteen capabilities

AQL, Databricks, Snowflake, dbt and Cube, scored on whether each has a named construct for the job. The split runs through level of detail and time intelligence. Fan-out separates them most sharply.

See the matrix
First-class, out of 18named construct for the job
  • Holistics AQL171 constrained
  • Databricks Metric Views86 constrained · 3 absent · 1 undocumented
  • Snowflake Semantic Views74 constrained · 3 absent · 4 undocumented
  • dbt MetricFlow75 constrained · 3 absent · 3 undocumented
  • Cube (Tesseract)135 constrained

The matrix

18 capability rows, scored on whether the layer has a named construct for the job. Five more rows describe shape and carry no verdict. Every dbt and Cube cell marked tested was run locally on the same data. Turn layers off to compare two at a time.

first-classFirst-class
a named construct for the job
constrainedConstrained
reachable, but a caveat limits it: a required group-by, a second object, warehouse SQL
absentAbsent
no path inside the layer
undocumentedUndocumented
nothing in the docs, weaker than no
tested
confirmed by a local run
Capabilities: 18 rows scored
CapabilityAQLDatabricksSnowflakedbtCube
Date trunc on a dimension at query timefirst-classabsentfirst-classfirst-classfirst-class
Cross-model dimension expressionfirst-classconstrainedfirst-classabsenttestedfirst-classtested
Multi-fact measuresfirst-classconstrainedfirst-classfirst-classtestedfirst-classtested
Fan-out handlingfirst-classconstrainedabsentabsenttestedfirst-classtested
Subquery filter (table condition)first-classundocumentedundocumentedconstrainedtestedconstrainedtested
Dimensionalize an aggregatefirst-classfirst-classfirst-classconstrainedconstrainedtested
Nested aggregationfirst-classabsentconstrainedundocumentedfirst-classtested
Drop a dimension from the grainfirst-classconstrainedconstrainedundocumentedfirst-class
Previous periodfirst-classfirst-classconstrainedconstrainedtestedfirst-classtested
Arbitrary periodfirst-classabsentabsentabsenttestedfirst-classtested
Running totalfirst-classfirst-classundocumentedfirst-classtestedfirst-classtested
Trailing periodfirst-classfirst-classundocumentedconstrainedtestedfirst-classtested
Period to datefirst-classfirst-classundocumentedfirst-classtestedfirst-classtested
Date hierarchy awarefirst-classconstrainedabsentfirst-classtestedfirst-classtested
Semi-additive measureconstrainedfirst-classfirst-classfirst-classtestedconstrainedtested
Window function around a metricfirst-classconstrainedconstrainedundocumentedconstrainedtested
Window function in a dimensionfirst-classfirst-classfirst-classfirst-classtestedconstrainedtested
Materialization and aggregate awarenessfirst-classfirst-classfirst-classconstrainedfirst-class
Shape: how each layer spells it, no verdict
FeatureAQLDatabricksSnowflakedbtCube
Defining languageAML + AQLYAMLSQL DDLYAMLYAML / JS
Query interfaceexplore { }MEASURE()AGG()CLI, JDBC, GraphQLREST, SQL, GraphQL
Modeling semanticgraphstar / snowflakegraphgraphgraph + rooted views
Cardinality supported+ many_to_many, range, filtersmany_to_one, one_to_many+ one_to_one, ASOF, rangeinferred, to-one onlyone_to_one, one_to_many, many_to_one
Targeting a table in the semantic layertiered resolutiondot pathnamed + USINGpath in the nameshortest path, join_path

What the matrix says

Six conclusions from the matrix, including where AQL is behind.

Cube is the closest peer

Cube has a named construct for 13 of 18 rows, and the tests back most of them. Its multi-stage measures cover nested aggregation, fixed grains, filter overrides and time shifts. Its fan-out rewrite is the same distinct-key idea as AQL’s. It falls short of AQL on the dimension side. A sub_query dimension takes the grain of its cube’s primary key, and a window function cannot sit in a dimension.

Fan-out sorts the five into three groups

AQL and Cube rewrite the query: they count the one side once per group key, from the distinct keys that survive the join. Databricks answers only through a second view rooted at the fine grain. Snowflake and dbt return an error. dbt calls fan-out support “pending”, and the request in metricflow#963 has been open since January 2024.

See the detail

Level of detail is still the structural gap

Databricks and Snowflake compute a level of detail as a SQL window function, so the value lands on every joined row. dbt has no construct. AQL’s group() | select() and Cube’s multi-stage grain build a real inner table and aggregate it, so one metric works grouped by year, by country or by nothing.

See the detail

Every layer names the time shapes; two read the grain

Databricks pins offsets to declared fields. Snowflake declares no shape. dbt names every shape, but windows and offsets need metric_time in the query. Cube and AQL read the report’s grain. Under a mid-month date filter no layer limits the window to the filtered rows; Cube even reads past the end of the range.

See the detail

Where AQL is behind

Semi-additive measures are one block in Databricks, Snowflake and dbt, and a composition in AQL and Cube. Snowflake’s ASOF join needs only a start date; AQL’s range relationship needs both bounds. Every other layer speaks SQL, JDBC or GraphQL to outside tools. Retail calendars have no AQL construct.

See the detail

Addressing the visualization axes is AQL’s alone

AQL window functions can partition and order by 'rows', 'columns', 'x_axis' or 'legend'. That removes the window-around-a-metric constraint. Databricks and Snowflake share that constraint, and Cube shows it as a GROUP BY error. dbt has no window over a metric at all.

See the detail

Capability by capability

Open a row to see how each layer spells it, in its own syntax. Where the report ran a worked example, it sits under the tabs.

Surface

How you declare the layer, and how a consumer asks it a question.

Defining languageAQLAML + AQLDatabricksYAMLSnowflakeSQL DDLdbtYAMLCubeYAML / JS

Holistics AQLAML + AQL

Two languages. AML is a typed block language for structure; AQL is the expression language inside it, marked by @aql.

Model orders {
  dimension id {}
  dimension created_at {}
  measure total_orders {
    definition: @aql count(orders.id) ;;
  }
}
Query interfaceAQLexplore { }DatabricksMEASURE()SnowflakeAGG()dbtCLI, JDBC, GraphQLCubeREST, SQL, GraphQL

Holistics AQLexplore expression

explore {
  dimensions {
    orders.status,
    orders.created_at | month()
  }
  measures {
    total_orders: count(orders.id)
  }
  filters {
    orders.created_at matches @1995
  }
}

The explore expression is the compiler IR. The drag-and-drop UI emits one, and it picks the source tables from the fields you named. Drop to pipe form when you want control: orders | group(users.name) | select(…). There is no SQL entry point into the dataset; consumers come in through the UI, the API, or the MCP server.

Date trunc on a dimension at query timeAQLfirst-classDatabricksabsentSnowflakefirst-classdbtfirst-classCubefirst-class

Holistics AQLfirst-class

Truncation is a function you apply at query time, so one date dimension serves every grain.

orders.created_at | month()
orders.created_at | quarter()
orders.created_at | date_trunc('week')

Against Databricks, one created_at replaces five pre-declared copies of it, and the time functions read the report's grain from the query.

Join graph

What shapes the model can hold, and how a metric reaches a table across them.

Modeling semanticAQLgraphDatabricksstar / snowflakeSnowflakegraphdbtgraphCubegraph + rooted views
ROOTED TREE · DATABRICKS orders customers products shipments a second fact needs its own metric view GRAPH · SNOWFLAKE AND AQL orders shipments customers where a metric starts solid: many_to_one dashed: not expressible in one view
Both sides model the same business: two facts against a shared customers dimension. With a root, every metric starts at orders, and shipments has nowhere to hang without its own metric view or a bridge table. In a graph each metric starts from its own model and walks to the shared dimension, so adding a third fact changes nothing that already exists.

Holistics AQLgraph

A dataset is a set of models plus relationships between them. Each metric starts from its own source model and walks the graph from there, so nothing in the dataset is the root.

Cardinality supportedAQL+ many_to_many, range, filtersDatabricksmany_to_one, one_to_manySnowflake+ one_to_one, ASOF, rangedbtinferred, to-one onlyCubeone_to_one, one_to_many, many_to_one

Holistics AQLfour types, three knobs

relationship(
  cities.country_code > countries.code,
  true, 'two_way', nullable=false
)
// >  many_to_one
// -  one_to_one

Short form covers a single-column equality join. Long form adds composite keys (an on block per extra pair), relationship filters (where.from, where.to), and two non-equality types whose match is a Boolean AQL expression holding the full join condition:

RelationshipConfig {
  rel: RangeRelationship {
    type: 'range'
    from: FieldRef { model: 'dl_quotes', field: 'id' }
    to: FieldRef { model: 'dl_price_bands', field: 'id' }
    match: @aql
      dl_quotes.currency == dl_price_bands.currency
        and dl_quotes.quoted_at >= dl_price_bands.valid_from
        and dl_quotes.quoted_at < dl_price_bands.valid_to
    ;;
  }
  active: true
}

RangeRelationship matches each source row to at most one target row. ManyToManyRelationship has the same shape and allows several, for example a subscription matched to every period it overlaps. Per relationship, direction decides whether filters and grouping flow back to the fact side, nullable=false asserts referential integrity so the compiler emits an INNER JOIN, and rlp_propagation routes row-level permission filters independently of direction.

Gap. No ASOF keyword. A range match needs both bounds on the target row, so a table with only a start date needs a derived end date before it can join as of a date.

The relationship you need is

AQLfirst-class

Declarable, with composite keys in long form, and reusable across datasets.

relationship(orders.customer_id > customers.id, true)
Databricksfirst-class

Declarable. The only shape it has, and the one it is built around.

joins:
  - name: customers
    on: source.o_custkey = customers.c_custkey
Snowflakefirst-class

Declarable, with composite keys.

orders (o_custkey)
  REFERENCES customers (c_custkey)
dbtfirst-class

Declarable as entities: 

- name: customer_id
  entity: {type: foreign, name: customer}
Cubefirst-class

Declarable. One join per pair of cubes.

- name: customers
  relationship: many_to_one
  sql: "{CUBE}.customer_id = {customers.id}"
Targeting a table in the semantic layerAQLtiered resolutionDatabricksdot pathSnowflakenamed + USINGdbtpath in the nameCubeshortest path, join_path

Holistics AQLtiered resolution

Relationships are named objects, reusable across datasets, and a metric activates the ones it needs:

sum(order_items.revenue)
| with_relationships(
    order_items.order_id > orders.id,
    order_items.country_id > countries.id
  )

Close to Snowflake's USING, with fewer restrictions. When several paths exist, the compiler ranks them, and on a tie it raises an error that names the ambiguity.

Role playing is the same idea as the Snowflake alias, spelled with extend:

const customer_region = regions.extend({})
const supplier_region = regions.extend({})
Cross-model dimension expressionAQLfirst-classDatabricksconstrainedSnowflakefirst-classdbtabsentCubefirst-class

Holistics AQLfirst-class

Same as Snowflake. Reference the field, the compiler resolves the join, and multi-hop is fine while every hop is many-to-one. The many side is a collection rather than a value, so it has to be collapsed first:

// error: many rows
orders | select(orders.discount * order_items.value)

// fine
orders | select(orders.discount * sum(order_items.value))

From an order_items query, reference

AQLfirst-class

Works.

order_items | select(order_items.id, products.name)
Databricksfirst-class

Works, as long as products hangs off the root as the "one" side.

Snowflakefirst-class

Works. The relationship and its direction are enough.

dbtfirst-class

Works as a group-by: product__name.

Cubefirst-class

Works. {products.name} in a dimension or as a group-by.

Multi-fact measuresAQLfirst-classDatabricksconstrainedSnowflakefirst-classdbtfirst-classCubefirst-class

Holistics AQLfirst-class

This follows from the graph model. Each metric names its source model, walks its own path to the shared dimensions, and the compiler assembles one CTE per branch and joins on the shared grain. Nothing about the dataset changes when a third fact arrives, and no table has to be nominated as root.

Fan-out handlingAQLfirst-classDatabricksconstrainedSnowflakeabsentdbtabsentCubefirst-class

Count users, slice by order status. The join multiplies each user row by their orders, so a naive COUNT(users.id) counts the same person once per order. Every layer has to answer this, and the five answers fall into three kinds: AQL and Cube rewrite the query, Databricks needs a second view, and the Snowflake and dbt queries fail with an error.

Holistics AQLfirst-class

AQL resolves it automatically for measures defined in AML. When the measure sits on the coarser model, the compiler aggregates it inside that model first, then inner-joins it to the distinct keys that survive the fan-out join.

WITH total_active_users AS (
  SELECT id AS user_id, COUNT(id) AS total_users
  FROM users GROUP BY 1
), distinct_user_id AS (
  SELECT DISTINCT u.id AS user_id
  FROM users u LEFT JOIN orders o ON u.id = o.user_id
  WHERE o.status IS NOT NULL
)
SELECT d.user_id, t.total_users
FROM total_active_users t
INNER JOIN distinct_user_id d USING (user_id)

The report you want

AQLfirst-class

One query in the same dataset. Users are counted inside users, then inner-joined to the distinct user ids that survive the join.

explore {
  dimensions { orders.status }
  measures { count(users.id) }
}
Databricksconstrained

Not from a customer-rooted view. Make customer the source and orders has to join one_to_many, which is forbidden in fields, so o_orderstatus cannot be a dimension at all. No measure fixes that; the dimension is unavailable.

Answering it takes a different metric view, defined separately and rooted the other way round:

# a second metric view, rooted at orders
source: samples.tpch.orders
joins:
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey
fields:
  - name: order_status
    expr: o_orderstatus
measures:
  - name: customers
    expr: COUNT(DISTINCT o_custkey)

That second view answers the question correctly. It is also its own object, with its own name to keep in sync, its own grants, and a DISTINCT that you maintain by hand.

Snowflakeabsent

Compilation error. A second view does not help, because the rule applies to the pair of metric and dimension.

[42601][10956] SQL compilation error:
Invalid dimension specified: The dimension
entity 'ORDERS' has a higher level of
granularity than the base metric entity
'CUSTOMERS'.
dbtabsent

Error. MetricFlow lists no orders dimension for a users metric. tested

Cubefirst-class

Rewritten, correct: cancelled 2 users / 4000, completed 2 / 3000. tested

Filtering

Choosing which rows a metric counts, when the condition lives outside the metric's own model.

Subquery filter (table condition)AQLfirst-classDatabricksundocumentedSnowflakeundocumenteddbtconstrainedCubeconstrained

Filter a metric by whether a key turns up in a table you derive on the spot. This is the general mechanism that the semi-additive composition is built out of.

Holistics AQLfirst-class

The right side of in is any table expression, so it can filter, walk relationships and aggregate before the metric sees a single row.

sum(orders.amount) | where(
  orders.user_id in users
  | filter(users.country == 'US')
  | select(users.id)
)

Simpler conditions compile to the same shape. A single-model condition becomes a table condition on the primary key; a cross-model condition becomes one on every dimension it touches. Comparing a dimension against a metric compiles to the same shape, so where(balances.date == max(balances.record_date)) works.

Writing the table condition yourself decides between two readings. Averaging only the items with quantity over 3 is not the same as averaging every item in an order that contains one, and writing the table condition yourself is how you say which you meant. See where: table condition.

Level of detail

Computing a metric at a grain the report is not using. Databricks and Snowflake express level of detail as a SQL window function, so the answer lands on every joined row instead of collapsing to a value. AQL and Cube build an inner aggregate. dbt has no construct for it.

Dimensionalize an aggregateAQLfirst-classDatabricksfirst-classSnowflakefirst-classdbtconstrainedCubeconstrained

Databricks, Snowflake and AQL are much the same here. Each takes a partition list, the fields the aggregate is computed over, and the result stops following the query's grouping. Only the syntax differs. dbt and Cube are narrower. dbt computes per entity only inside a filter, and Cube only at the grain of the cube that holds the dimension.

Holistics AQLfirst-class

dimension customer_lifetime_value {
  definition: @aql
    sum(orders.amount) | dimensionalize(users.id)
  ;;
}

Partition list: the argument, users.id. Databricks, Snowflake and AQL all return a dimension, and in each the value is distributed across the rows it was computed over.

Nested aggregationAQLfirst-classDatabricksabsentSnowflakeconstraineddbtundocumentedCubefirst-class

Holistics AQLfirst-class

An inner table is built, aggregated, and then aggregated again. The inner dimension never has to appear in the query.

metric avg_monthly_acquisition {
  definition: @aql
    users
    | group(users.created_at | month())
    | select(new_customers)
    | average()
  ;;
}

Drop that into a report grouped by year and you get average monthly acquisition per year; group by country instead and you get it per country. The metric has no year logic and adds no requirement to the query. Cube's multi-stage grain is the only other construct that builds an inner table this way.

Show average monthly acquisition

AQLfirst-class

The metric is unchanged. Only the report changes.

explore {
  dimensions { orders.created_at | year() }
  measures { avg_monthly_acquisition }
}
Databricksabsent

Cannot express it. The inner monthly figure has to be read alongside an aggregate, so there is no way to aggregate it again.

Snowflakeconstrained

The right number, at the wrong grain. The finer date goes in the EXCLUDING list at definition time, and the query is then obliged to group by it anyway. On a real view:

orders.avg_order_count AS AVG(order_count)
  OVER (PARTITION BY EXCLUDING orders.order_date)

select order_date, order_year,
       agg(orders.avg_order_count)
from tpch_rev_analysis group by 1, 2;
Order yearOrder dateAvg order count
19921992-08-06620.461
19921992-05-26620.461
19921992-08-19620.461
19921992-10-08620.461
1992and 500 more dates620.461

One row per order date. The same value on every one.

620.461 is the correct average with order_date excluded. It is also repeated on every one of several hundred date rows, because the dimension the metric excluded is the one the query is forced to carry. The report asked for a row per year and got a row per date, all identical.

dbtundocumented

No construct.

Cubefirst-class

One multi-stage measure with the month in grain.include; the year stays in the outer query.

Drop a dimension from the grainAQLfirst-classDatabricksconstrainedSnowflakeconstraineddbtundocumentedCubefirst-class

Holistics AQLfirst-class

Four related functions, with an explicit argument for whether query filters apply.

// deny a model or dimension from the grain
count(orders.id) | of_all(countries)
count(orders.id) | of_all(orders.status, keep_filters: true)

// the inverse: allow only these
metric | keep_grains(users)

// viz-aware shorthand
percent_of_total(orders.count, 'row_total')

// branch on which pivot level is active
case(
  when: is_at_level(cities.name),
  then: sum(sales.amount) / (sum(sales.amount) | of_all(cities.name)),
  when: is_at_level(countries.name),
  then: sum(sales.amount) / (sum(sales.amount) | of_all(countries.name)),
  else: 1
)

Databricks has no keep_filters parameter. Without it you get percent of all time; with it you get percent of the visible set, which is what "these five countries account for 54% of sales, and the US is 21% of those five" needs.

Time

Five period shapes, hierarchy awareness, and semi-additivity. Databricks spells the shapes as <code>window:</code> blocks pinned to declared fields; Snowflake declares none; dbt uses cumulative and derived metrics that need <code>metric_time</code> in the query; Cube uses <code>rolling_window</code> and <code>time_shift</code>; AQL has a named function per shape that reads the report's grain.

Previous periodAQLfirst-classDatabricksfirst-classSnowflakeconstraineddbtconstrainedCubefirst-class

Holistics AQLfirst-class

sum_revenue
| relative_period(orders.created_at, interval(-1 year))

// or, relative to the report's own grain
count(orders.id)
| relative_period(orders.created_at, -1)

One definition covers every grain. Viewing March 2024 gives March 2023, viewing Q3 2024 gives Q3 2024 minus a year. Build the comparison on top with arithmetic and the YoY percentage adapts too:

metric revenue_yoy_pct {
  definition: @aql
    safe_divide(
      (sum_revenue - revenue_previous_year) * 100.0,
      revenue_previous_year
    )
  ;;
}

Last four periods against a year earlier, viewed

AQLfirst-class

One metric.

sum_revenue
| relative_period(orders.created_at, interval(-1 year))
Databricksfirst-class

One measure, pinned to the declared month field.

window:
  - order: month
    range: current
    offset: -12 month
Snowflakeconstrained

The query fetches sixteen months to show four. LAG(revenue, 12) sits in the metric, which makes month mandatory in the query; the twelve months it needed to reach back are discarded by a WHERE outside the CTE.

dbtconstrained

One derived metric, with metric_time__month required in the query. tested

Cubefirst-class

One measure. time_shift: [{interval: 1 year, type: prior}]. tested

Arbitrary periodAQLfirst-classDatabricksabsentSnowflakeabsentdbtabsentCubefirst-class

Holistics AQLfirst-class

count(orders.id)
| exact_period(orders.created_at,
               @2022-07-01 - 2022-09-01)

Compare anything against a named window: back-to-school season, a launch fortnight, last fiscal year. It overrides the report's time filter rather than intersecting with it, which is the difference between exact_period() and where() and is the behaviour you want for a baseline. Datetime literals cover @2022, @2022-04-01, and @(last 2 weeks). The dashboard period-comparison UI compiles down to this and relative_period().

Cube is the only other layer that overrides the query filter. dbt combines the two filters, so the baseline disappears under any other date range. Databricks and Snowflake have nothing here.

Running totalAQLfirst-classDatabricksfirst-classSnowflakeundocumenteddbtfirst-classCubefirst-class

Holistics AQLfirst-class

orders.total_orders
| running_total(orders.created_at)

// multiple running dimensions
running_total(orders.total_orders,
              orders.created_at, orders.status)

// fill the holes a pivot leaves behind
running_total(orders.total_orders,
              orders.created_at, fill_missing: true)

keep_filters defaults to true here (the opposite of of_all), so a running total respects the visible range unless you opt out. And fill_missing: true exists because a pivot has no row for a status that saw no orders that day, so the running total would flatline; this fills those cells. Omit the running dimension entirely and it runs along whatever date dimensions the report has.

Trailing periodAQLfirst-classDatabricksfirst-classSnowflakeundocumenteddbtconstrainedCubefirst-class

Holistics AQLfirst-class

count(orders.id)
| trailing_period(orders.created_at, interval(3 months))

// or in the report's own grain units
count(orders.id)
| trailing_period(orders.created_at, 3)

It re-times the metric instead of averaging it. Trailing 3-month cancellation rate is cancelled orders in the window divided by orders in the window, which is not the mean of three monthly rates. That distinction is what makes it correct on ratio metrics, and it follows because the whole metric is evaluated in a shifted context.

Period to dateAQLfirst-classDatabricksfirst-classSnowflakeundocumenteddbtfirst-classCubefirst-class

Holistics AQLfirst-class

count(orders.id)
| period_to_date('year', orders.created_at)

One call, one date dimension, no pre-declared month and year fields, because truncation happens at query time. The docs specify the behaviour when the report grain and the reset period differ. A YTD metric shown monthly anchors on the last date in the current period, so March 2022 covers 1 Jan to 31 Mar; a MTD metric shown yearly anchors on the last month with data.

Date hierarchy awareAQLfirst-classDatabricksconstrainedSnowflakeabsentdbtfirst-classCubefirst-class

Holistics AQLfirst-class

Hierarchy awareness shows up in three places rather than one construct. Every time function documents how it behaves when the report grain is finer or coarser than its period. rollup() in the explore dimensions produces the subtotal rows. And is_at_level() lets a metric ask which level it is currently being evaluated at, which is what makes per-level percentages (city of country, country of continent, continent of total) a single metric definition.

explore {
  dimensions {
    rows { rollup(order_items.created_at | month()) }
  }
  measures {
    orders: count(order_items.order_id)
  }
}
Semi-additive measureAQLconstrainedDatabricksfirst-classSnowflakefirst-classdbtfirst-classCubeconstrained

Holistics AQLconstrained

The one row where AQL is behind. There is no declarative construct; you compose it out of the table condition, and you own the correctness. The naive version is two functions:

sum(balances.bank_balance)
| where(dim_dates.date == max(balances.record_date))

That is right per customer and wrong in the total, because "the latest date" resolves per group. Which fix you want depends on the question. Last balance date across all customers:

sum(balances.bank_balance) | where(
  dim_dates.date == (
    max(balances.record_date) | of_all(balances.user_name)
  )
)

Or each customer's own last balance date, summed:

unique(balances.user_name)
| select(
    user_name: balances.user_name,
    sum_balance: sum(balances.bank_balance)
      | where(dim_dates.date == max(balances.record_date))
  )
| sum(sum_balance)

Inferring the last date from the rest of the expression would be a guess, and the two spellings above differ by exactly that guess. Databricks, Snowflake and dbt take the guess for you inside a keyword. AQL makes you write down which one you meant. It takes three lines, and the naive version gives the wrong total.

Windows

Secondary calculations: a window wrapped around a metric, and a window computed inside a dimension.

Window function around a metricAQLfirst-classDatabricksconstrainedSnowflakeconstraineddbtundocumentedCubeconstrained

Holistics AQLfirst-class

Eighteen window functions across navigation (previous, next, first_value, last_value, nth_value), ranking (rank, dense_rank, ntile, percent_rank), and framed aggregation (window_sum, window_avg, window_min, window_max, window_count, plus the four variance and deviation forms).

window_avg(count(orders.id), -2..0,
           order: orders.created_at,
           partition: orders.status)

partition and order can also address the visualization axes instead of dimensions, which no other layer offers:

window_sum(orders.total_revenue, order: 'rows')
window_sum(orders.total_revenue, order: 'rows',
                                 partition: 'columns')
window_sum(orders.total_revenue, order: 'rows' | desc())

This avoids MISSING_AGGREGATION. The window addresses the pivot's row and column axes, so nothing has to be added to the group by and the metric stays reusable. Aliases 'x_axis' and 'legend' read better in chart contexts.

And when the window's own ordering or partitioning dimension is missing from the report's grain, AQL computes a specified answer. It differs by function family; both cases are worked below.

One current limit: window aggregate functions accept only aggregate metrics as input. The other layers have the same restriction.

Window ordered or partitioned by a dimension the report is not grouped by

AQLfirst-class

Adds the dimension, then computes. The missing dimension joins the level-of-detail context first, so the window still has rows to average over.

window_avg(count(orders.id), 0..0,
           order: orders.created_at | quarter())
GrainOrdersWindow avg
2021 Q1120120
2021 Q2160160
2021 Q3200200
2021 Q4240240
2021 subtotal720180
2022 subtotal1040260
Grand total1760220

Illustrative. 2021's subtotal is the mean of its four quarters, not of 720.

In a report grouped by year, the 2021 subtotal comes back as the average of 2021's four quarters, and the grand total as the average of every quarter, even though the grand total's own context is empty. See dimensions not available in the LoD context.

Databricksabsent

Error. The report is grouped by year, the window orders by quarter, and quarter is not in the group by.

[MISSING_AGGREGATION] The non-aggregating
expression "order_month" is based on columns
which are not participating in the GROUP BY
clause. SQLSTATE: 42803
Snowflakeabsent

Error. Same rule as in nested aggregation: a dimension used in a window-function metric has to be requested in the query.

dbtundocumented

No window over a metric.

Cubeabsent

Error. LAG(...) OVER (ORDER BY {sold_at}) fails with a GROUP BY error. tested

Window function in a dimensionAQLfirst-classDatabricksfirst-classSnowflakefirst-classdbtfirst-classCubeconstrained

Holistics AQLfirst-class

The same behaviour. A window function in a dimension evaluates before filters, so a rank dimension ranks the whole table. AQL publishes its order of operations, so the behaviour is predictable.

Materialization

What the layer does when the fact table gets big.

Materialization and aggregate awarenessAQLfirst-classDatabricksfirst-classSnowflakefirst-classdbtconstrainedCubefirst-class

Holistics AQLfirst-class

Yes, as PreAggregates plus query rewriting. See Aggregate Awareness.

Pitfalls found in testing

Each returns a wrong or surprising number with no error, or fails with an error on an ordinary query. All ran on dbt-core 1.12.6 with MetricFlow 0.213.0, and Cube 1.8.8 with Tesseract, over one DuckDB file.

dbt MetricFlow

  1. A cumulative metric with a date filter and no time group-by sums its daily values.

    6205 against 223Row

  2. A previous-period metric needs metric_time in the group-by; a date filter alone fails with an error.

    errorRow

  3. A month offset at year grain joins day by day and drops month-end days.

    302 against 303Row

  4. A cumulative metric at month or year grain shows the first day of the period by default.

    March 142 against 193Row

  5. A semi-additive measure without window_groupings takes the latest date across all entities.

    March 120 against 200; Alice null by userRow

  6. A metric's fixed date filter is added to the query's date filter.

    March baseline null under an April filterRow

  7. A one-side metric grouped by a many-side dimension fails at resolution.

    errorRow

Cube (Tesseract)

  1. A rolling window under a date range reads past the range end.

    April 293 includes Apr 20; range ends Apr 10Row

  2. time_shift with no time dimension and no date filter does nothing.

    333, the unshifted totalRow

  3. Month to date at year grain sums the whole year.

    298Row

  4. Adding a relationship changes the join path of an existing query.

    3 / 2 becomes 1 / 1Row

  5. A calendar-mapped time_shift returned no shift (v1.8.8).

    prior year equals the same dayRow

  6. A window function in a dimension, or a window ordered by a dimension, fails.

    GROUP BY errorRow

  7. A one-side dimension that reads a many-side column fans out with no error.

    Alice appears 3 timesRow

  8. A rank whose partition dimension is missing from the query returns 1 for every row.

    rank 1 per countryRow

Beyond the matrix: where AQL is behind

A field-by-field read of the dbt and Cube specs, checked against AML and the AMQL compiler. One clear modeling edge remains, retail calendars. The rest concern surface, caching and metadata.

Has it: dbt

Conversion metrics

AMQL: a compositiontested

dbt declares “visit, then purchase of the same product within 7 days” in one block. Its SQL is a range join plus a pick of the closest earlier visit. AMQL reaches the same numbers with a many_to_many relationship and a dimensionalize. By visit product: dbt A 1, B 1; AMQL A 1, B 1. The plain count gives A 2, B 1.

dimension purchases.last_visit_at =
  dimensionalize(max(visits.visited_at), purchases.id);
metric attributed = count(purchases.id)
  | where(visits.visited_at == purchases.last_visit_at);

Has it: dbt, Cube

Retail calendars

AMQL: partial

A fiscal year that starts on a fixed date is an offset. Shift the date dimension, and the AQL time functions follow it. A 4-5-4 retail calendar has 4- and 5-week months and 53-week years. No offset produces it, and AQL time functions step by calendar intervals only.

Has it: Cube

Calendar-mapped time shift

AMQL: missingtested

A calendar cube maps each date to a comparison date, so Fri 2024-03-01 compares with Fri 2023-03-03. In Cube 1.8.8 the mapped column replaced the date in the join, the filter and the group-by, so the shift cancelled and returned the same day (33, not 22).

Has it: dbt

Gap filling

AMQL: partial

dbt join_to_timespine returns a row per date and fill_nulls_with: 0 coalesces it. The AMQL compiler has a time_spine function, not yet documented.

Has it: Cube, dbt

SQL query endpoint

AMQL: missing

Cube speaks the Postgres wire protocol, and dbt offers JDBC and GraphQL. AQL is reached through the UI, the REST API and the MCP server.

Has it: Cube

Cache keyed on data change

AMQL: missing

refresh_key: {sql: SELECT MAX(updated_at) FROM orders} drops the cache when data changes. The Holistics cache is time-based per report.

Has it: Cube

Query filters in model SQL

AMQL: partial

FILTER_PARAMS injects a filter on an ordinary dimension into the cube’s base SQL, for partition pruning. AMQL query models do this for param fields only.

Has it: Cube

Access policies per member

AMQL: partial

access_policy hides or masks members per group, also in the metadata API. AMQL has row-level permission and per-field masking; hidden is not a security feature.

Has it: Cube, dbt

Defaults, exports, metadata

AMQL: partial or missing

Cube view default_filters switch off when the user filters the member. dbt saved queries write a named table with exports. Both accept free-form meta and tags. AML has none of the three.

What this is based on

Vendor docs, a live Snowflake semantic view, and local runs on the same small dataset.

Databricks, Snowflake and AQL

From an earlier review on 19 Oct 2026. These columns rest on the vendor docs, a live Snowflake semantic view, and the Holistics reference docs.

dbt and Cube

Docs read on 19 and 20 Oct 2026: dbt joins, cumulative, derived, conversion, metric filters; Cube measures, joins, multi-fact views, calendar cubes.

Local runs: dbt-core 1.12.6 with MetricFlow 0.213.0, and Cube 1.8.8 with Tesseract, over one DuckDB file. The data is four users (one with no orders), six orders, three tickets, ten sales across 2023 and 2024, seven balance snapshots, and visit and purchase events. The AMQL conversion composition ran through the AMQL compiler on Postgres.

Scoring

First-class means a named construct exists for the job. Constrained means you can get there, but a caveat changes what the metric can be used for: a required group-by, a missing parameter, a second object, or warehouse-specific SQL. Absent means no path inside the layer. Undocumented means the docs show nothing, which is weaker than no.

Model it once, then ask anything

Read the AMQL docsTry in playground

Go deeper: AQL, AML, and learn AQL