Full source:
examples/coffeehouse/At a glance
Schema
- Multi-line baskets next to single-line orders. Ben’s January order has four lines and a total of 200.00.
- Repeat buyers. Anna orders almost every week.
- Orders that are not sales. Refunds, a cancellation, and a 600.00 grinder order from
QA Test.
Setup
coffeehouse.duckdb read-only and builds its marts into its own file, dbt/coffeehouse_dbt.duckdb. bash scripts/compare.sh runs both sides for every question below. It needs canonic, dbt and mf on the PATH.
Two different ideas of a metric
The two layers look alike from a distance: both have measures, metrics and dimensions in YAML, and both compile a query into SQL. They differ in what a definition is allowed to mean. In dbt, a metric is an aggregation the modeler chose. A measure is a column plus an aggregate function (sum, average, count_distinct, …), a metric points at a measure or combines metrics, and filters are part of each metric. MetricFlow compiles that choice faithfully at any grain the caller asks for. It does not ask whether the aggregate is still correct at that grain, because the definition carries no information to answer the question with.
In canonic, a metric is a contract with a compilation strategy. The binding kind decides how the number is computed at any grain: a ratio aggregates numerator and denominator first and divides last, a distinct_count is recomputed from base rows for every grain, a semi_additive measure collapses its time dimension instead of summing it. Around the metric sit three more things dbt has no place for:
The three cases below show what that difference does to a number.
Average order value
The definitions
The numbers
Why they differ
fct_order_lines has one row per order line, and order_total is an order-level value repeated on every line. MetricFlow compiles the measure into exactly what it says, mf query ... --explain shows it:
Could dbt get this right?
Yes. Aratio metric of revenue and a count_distinct measure on order_id returns 59, 74, 23 and 52 in MetricFlow, the same as canonic. dbt can express the correct definition.
What dbt cannot do is tell the two apart. agg: average is a valid aggregation on any column, and MetricFlow has no notion that order_total belongs to a coarser grain than the table it sits in. Both metrics can live side by side in the same project, average_order_value and aov_ratio, and an agent choosing by name will pick the one that sounds right. The correct answer depends on the modeler having known the trap, and on nobody adding the other version later.
In canonic the ratio is not a pattern someone has to know, it is the kind of binding. There is one canonical average_order_value, and aov, average basket and basket size resolve to it as aliases.
Active customers
The definitions
The numbers
Why they differ
agg_daily_active_customers is a typical dashboard table: one row per day with the number of distinct buying customers. Each of those daily numbers is correct. MetricFlow rolls the measure up with its aggregate, SUM(active_customers), so a month is the sum of its days and Anna is counted once for every day she bought. The roll-up is wrong at every grain above a day.
revenue_per_customer, a ratio of revenue and active_customers, inherits the error. dbt returns 780.00 / 15 = 52.00 for Q1, canonic 780.00 / 5 = 156.00.
canonic binds active_customers to the orders themselves. A distinct_count is never derived from partial counts, it is recomputed from base rows at whatever grain the query asks for: a month, a quarter, a channel, or all of them at once.
Could dbt get this right?
Partly. Acount_distinct measure directly on the line table is correct at every grain, and the example ships one, distinct_customers, which returns 5 for Q1 in MetricFlow, the same as canonic.
The pre-aggregated table itself cannot be repaired from inside the semantic layer. Distinct counts do not add up, so no aggregate over daily distinct counts yields a monthly one. MetricFlow’s tool for non-additive measures, non_additive_dimension, makes a measure semi-additive: with window_choice: max it returns the value of the last day in each period, which gives 2, 1 and 1 here. That is the right tool for an account balance, not for a distinct count. And nothing in MetricFlow refuses the sum either, it is a valid aggregation, so the dashboard table quietly becomes the source of a monthly KPI.
Guardrails
The numbers
Why they differ
In dbt the sales rule, completed orders by real customers only, is afilter on each metric:
units_sold, added later by someone else. MetricFlow compiles it without the filter and counts the units of two refunds, one cancellation and the 600.00 test order.
In canonic the rule belongs to the data, not to a metric. Two mandatory_filter guardrails apply to every measure on order_items, and two twins to every measure on orders:
units_sold names no filter and still gets both. The compiler joins orders and customers to reach the predicates, and the answer says so:
Could dbt get this right?
There are two workarounds, and both have a cost.- Repeat the filter on every metric. That is the approach in this example. It holds exactly as long as everyone who adds a metric remembers it, and the reviewer notices when they do not.
- Filter in the mart. A
fct_sales_linesmodel without refunds, cancellations and test orders makes every metric on it correct. The refunded orders are then gone from that model, so a refund rate or a cancellation count needs a second model, and the next metric built on the unfilteredfct_order_linesis back to square one.
Knowledge
Five pages underknowledge/global/: a policy for what counts as a sale, definitions for average order value and active customers, and two caveats. A search for either metric returns its definition, and the caveats bound to the same sources ride along:
search_knowledge and is told to relay the caveats with its answer. The pages reference measures by name, and {{ sl:….expr }} renders the live definition, so the prose cannot quote a formula that has since changed.
In dbt the closest place for this is the description of a model, column or metric. It is documentation for people browsing the catalog, not context that reaches the caller at question time.
Assertions
Four hand-checked values undercontracts/assertions/ gate every change to the definitions:
order_count to count lines instead of distinct orders, a plausible one-line edit:
provisional with the reason untested (no assertion), so an agent knows to caveat it.
dbt tests check tables and columns: uniqueness, not-null, accepted values, or a custom SQL query. A singular test can recompute an AOV in SQL, but that tests the SQL in the test, not the metric as MetricFlow serves it. dbt Core has no built-in way to say “average_order_value for January is 59.00” against the semantic layer and fail the build when it is not.
And Apache Ossie?
Apache Ossie is an interchange format for semantic models, not a query engine. A metric is a SQL expression with a description andai_context, and a consumer decides how to execute it. The ossie-retail example defines average order value the way most people would write it:
order_items JOIN orders, COUNT(orders.order_id) counts lines, not orders. On the coffeehouse data that is 295.00 / 9 = 32.78 for January instead of 59.00, the same wrong number the broken assertion above catches. The expression carries no additivity, so a consumer cannot know that numerator and denominator have to be aggregated separately.
The other two cases do not have a place in the model either. A rule like “exclude refunds” lives in ai_context as prose, an instruction an agent may or may not follow, not a filter anyone enforces. And like dbt, the format has no way to assert a metric’s result.
canonic can import an Ossie model. It turns that expression into a ratio of two independently planned metrics, and flags plain averages with avg_suggests_ratio for review, so the import already avoids the trap.
What canonic does not do
To keep the comparison fair:- A wrong expression still computes. A semantic measure
avg(order_total)on a line table that repeats the order total would return the same 102.22 as dbt. canonic makes the right strategy the default building block, flags suspicious imports, and catches regressions with assertions. It does not make a wrong definition impossible. - Raw SQL is raw. Guardrails apply to every query compiled from metrics.
canonic sqlruns the statement it is given, and that includes the refunds. - The model still has to be written. Somebody has to decide that AOV is a ratio and that test customers are not customers. canonic’s job is to make sure that decision, once made, is applied to every answer and visible in it.