KPI Dictionary · Semantic models
The switchboard as YAML
Three Unity Catalog metric views that carry nine of the dictionary's metrics into a Genie space — the definition, its switch settings, and the vocabulary people actually ask in, stored where a natural-language interface reads them instead of guessing. Every model states the gold fact it assumes, and one of them is translated into the Snowflake semantic view Cortex Analyst consumes. Each carries a number — S-01, S-02, S-03 — and the sections below refer to them that way.
Model definitions below are current to the date above — verify against current Databricks and Snowflake documentation before you build.
Why a semantic layer
The KPI Dictionary makes the case that a supply chain metric is a set of decisions rather than a formula. That argument has an executable form. A Unity Catalog metric view stores the decisions — the grain, the date basis, the exclusions — as SQL expressions with names and comments attached, and Databricks Genie reads that view natively. Ask a Genie space for OTIF and it answers from the definition you declared, not from whichever columns looked plausible.
That is the whole value of the layer, and it is worth being precise about what it does and does not fix. It does not make a natural-language interface correct; it removes the largest source of being confidently wrong, which is a model inferring a metric from raw columns. The definition still has to be right, and the switch ledger on each model below is how you check that it matches the entry it claims to implement.
The second thing the layer buys is that the definition stops living in a dashboard. A metric view is a governed object: one definition, granted like any other view, read the same way by Genie, AI/BI dashboards, notebooks, and anything speaking SQL. A ratio recomputed at every scope from summed components — the rule this dictionary repeats everywhere — is enforced within the query block rather than by the discipline of whoever writes the next query.
And “within the query block” is a boundary, not a flourish. A subquery or CTE that re-aggregates MEASURE()output returns a wrong number with no warning — the engine's own nested-aggregate error text even steers toward exactly that construction (verified in one workspace on 2026-08-27). Push filters into the block that calls MEASURE(); never wrap its output in another aggregation.
The full split — what the engine enforces, what remains convention, how each gap fails, and the acceptance checklist to run before a space is trusted — is written out in Semantic Layer Guarantees.
Runtime requirements. The YAML dialect used here is version 1.1, which needs Databricks Runtime 17.2 or later. The synonyms field — the vocabulary routing that makes these models useful in a Genie space — needs 17.3 or later, alongside display_name and format. On an older runtime the views still create if you strip the synonyms, but a space without them will keep asking what DIFOT means.
What these models assume about your gold layer
These models assume the gold layer this library has been building toward: component-level facts with no stored ratios, conformed dimensions around them. Specifically, they assume the three facts already published in the dashboard patterns — the same names, the same grains, and the columns listed below.
Nobody's gold layer matches another's column for column, and that is fine: every column a model reads is confined to that model's single CREATE statement, so renaming is a find-and-replace inside one YAML block rather than a rebuild. Do replace all rather than the first — the shared population predicate repeats columns like first_confirmed_date once per rate, on purpose, so each measure states its own scope. What is not negotiable is the grain. A metric view aggregates one grain at a time, so a fact at a different grain than the one stated needs its measures rewritten, not its columns renamed.
fct_order_line_fulfillment — one row per order line
order_number, customer_key, product_key, ship_from_key, requested_date, first_confirmed_date, current_promise_date, first_ship_date, arrival_date, ordered_qty, canceled_qty, first_pass_shipped_qty, shipped_qty, delivery_count, order_entry_date, damage_free_flag, invoice_accurate_flag, order_all_lines_perfect
Fourteen columns, through delivery_count, come straight from the published fact and the OTIF entry's landing pattern — first_pass_shipped_qty is that pattern's first-attempt-complete component, kept as a quantity so unit fill and line fill both read from it. The last four are additions this model needs: order_entry_date as the lead-time clock start, two component flags for the perfect-order set, and one order-grain flag stamped on every line of the order, because a metric view aggregates a single grain at a time. Every one of them is read by a measure or named in a comment.
fct_forecast_vs_actual — one row per item × location × plan month × lag
item_key, location_key, lag_key, plan_month, plan_qty, actual_qty
Exactly the published fact. The lag attributes — lag_months and is_scored_lag — live on dim_lag, so the scored-lag convention is set once in the pipeline that builds the dimension and every consumer reads the same answer.
fct_inventory_position — one row per item × stocking location × snapshot date
item_key, location_key, snapshot_date, on_hand_qty, on_hand_value, unit_cost, target_qty, trailing_13wk_avg_weekly_demand, trailing_13wk_cogs_value, is_slow_moving, is_obsolete
The published fact plus the trailing consumption value the turns numerator needs. Both trailing components are stamped on the snapshot rather than computed at query time, so every consumer uses the same window — 13 weeks here, printed in the measure comments.
The OTIF entry's landing pattern works through how the first of these facts is built from bronze and silver, in four ERPs. This page starts where that one finishes.
S-01 — Order fulfillment
S-01 · Semantic model
<catalog>.gold.mv_order_fulfillment
one row per order line
Did the customer get what they ordered, when they were told, complete — under one declared setting of the six OTIF switches, readable by name?
The order-line fulfillment fact is the busiest fact in the gold layer: OTIF, fill rate, perfect order, and the customer-facing lead-time clocks are all projections of the same components. That is exactly why it deserves one metric view rather than four — every one of those metrics has to agree about which lines were in scope, which promise date counted, and what a first attempt was.
Every measure below is one setting of the switches the dictionary lays out. On-time is arrival basis against the first-confirmed promise with a zero-day window — the dictionary's default, because that is where the customer measures — with ship-basis variants beside it for warehouse accountability. In-full is first-pass shipped against the original ordered quantity, and OTIF is the joint test of the two evaluated on the same line, never the product of the two rates. Changing a setting means editing one expression in this YAML and replacing the view — not re-engineering a pipeline, and not silently changing what a saved question means.
Two population rules are written into every rate rather than into a global filter, because the count measures need the rows the rates exclude. Fully canceled lines leave the rate denominators, but Order Line Count keeps them and Cancel Rate % is built from them — a global filter would delete the evidence that the exclusion is happening at all. The same is true of the maturity rule: a line promised for next month has not failed yet, so it stays out of the rates and inside the counts.
That maturity rule is a scoring convention, and conventions are only honest when they are stated. A rate here scores a line once its first-confirmed promise date has passed. Without that predicate every open order in the book would sit in the denominator with nothing in the numerator, and the current month would read as a collapse in service that is really just a calendar.
Source fact: fct_order_line_fulfillment — one row per order line.
The switch ledger
Every setting below is the dictionary's, restated as an expression. Each switch links back to the entry that argues it out.
| Metric & measures | Switch | Setting in this model |
|---|---|---|
| OTIFOn-Time % · On-Time % (ship) · In-Full % · OTIF % · OTIF % (ship) · Split Shipment Rate % | Grain | Line — the fact's own grain; order and unit views roll up from it |
| Date basis | First-confirmed promise (first_confirmed_date) | |
| Ship vs arrival | Arrival basis on the headline measures (arrival_date); the (ship) variants are the warehouse-accountability cut | |
| First attempt vs cumulative | First attempt — first_pass_shipped_qty, with Split Shipment Rate % published beside it | |
| Tolerance | Zero-early / zero-late — widen with date_add() inside the FILTER | |
| Exclusions | Fully canceled and not-yet-due lines excluded from the rates; Cancel Rate % published beside | |
| Fill RateFill Rate % · Cumulative Fill Rate % · In-Full % | Weighting | Unit fill as Fill Rate %; line fill as In-Full % |
| First pass vs cumulative | First pass as Fill Rate %; Cumulative Fill Rate % beside it as the contrast | |
| Demand basis | Original ordered quantity — canceled quantity stays in the denominator | |
| Perfect Order RatePerfect Order % (line) · Perfect Order % (order) | Component set | On time, in full, damage-free, invoice-accurate — the full classical set |
| Grain | Both published — components at line grain, the classical test at order grain | |
| Joint test vs multiplied rates | Joint — one FILTER clause, never a product of component rates | |
| Lead TimeMedian Lead Time Days · P90 Lead Time Days | Which clock | Customer order-to-ship — transit excluded |
| Start and stop events | order_entry_date to first_ship_date, named in both comments | |
| Statistic | Median and P90, recomputed from the pooled lines at the queried scope | |
| Calendar vs business days | Calendar days — datediff() with no working calendar applied |
The metric view
Replace <catalog> and run it in a SQL editor or a notebook. As written it needs Databricks Runtime 17.3 or later, because every dimension and measure carries synonyms; strip those lines and it creates on 17.2, at the cost of the vocabulary routing that makes the space work.
1 parameter not filled: <catalog>
CREATE OR REPLACE VIEW <catalog>.gold.mv_order_fulfillment
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
comment: "Customer service metrics at order-line grain. Practice defaults: line grain, first-confirmed promise, arrival basis with ship-basis variants beside, first attempt, zero tolerance. Every rate scores only lines that are past their promise date and not fully canceled; the count measures deliberately keep those lines."
source: <catalog>.gold.fct_order_line_fulfillment
joins:
- name: customer
source: <catalog>.gold.dim_customer
on: source.customer_key = customer.customer_key
- name: product
source: <catalog>.gold.dim_product
on: source.product_key = product.product_key
- name: ship_from
source: <catalog>.gold.dim_ship_from
on: source.ship_from_key = ship_from.ship_from_key
dimensions:
- name: Order Month
expr: DATE_TRUNC('MONTH', first_confirmed_date)
comment: The date-basis switch, as a dimension. Anchoring the trend on the first-confirmed promise means a rescheduled line stays in the month it was originally promised for. Swap current_promise_date to see how much lateness rescheduling absorbs, or requested_date to score the ask as the customer placed it.
synonyms: ['month', 'promise month', 'period']
- name: Customer
expr: customer.sold_to
comment: Sold-to party from the conformed customer dimension.
synonyms: ['account', 'sold to', 'customer name']
- name: Channel
expr: customer.channel
comment: Route to market — retail, distributor, direct.
- name: Compliance Program
expr: customer.compliance_program
comment: Names the customer scorecard a line is scored against, where one applies. The program sets its own switches, so a program cut is context rather than a like-for-like comparison.
synonyms: ['scorecard', 'vendor compliance', 'mabd program']
- name: Category
expr: product.category
comment: Product category from the conformed product dimension.
synonyms: ['product category', 'product family']
- name: Ship-From Site
expr: ship_from.site
comment: Warehouse accountability cut — the ship-basis measures are the ones this dimension can fairly be read against.
synonyms: ['site', 'dc', 'shipping site', 'warehouse']
measures:
- name: Order Line Count
expr: COUNT(1)
comment: "Every line in scope, including fully canceled lines and lines not yet due. This is not the denominator of the rates below, and the difference between the two is the point: the rates exclude those lines, this measure and Cancel Rate % are how you see how many were excluded."
synonyms: ['lines', 'order lines', 'line count']
- name: Order Lines Scored
expr: COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date())
comment: "The shared denominator of every rate below, and the scoring convention made visible: a line counts once its first-confirmed promise date has passed and it was not fully canceled. Partially canceled lines stay, with their original ordered quantity intact."
synonyms: ['scored lines', 'lines in scope']
- name: Cancel Rate %
expr: 100.0 * COUNT(1) FILTER (WHERE canceled_qty >= ordered_qty) / NULLIF(COUNT(1), 0)
comment: Published beside the service rates because excluding cancels raises every one of them. A service improvement that arrives with a rising cancel rate is not an improvement. Built from the lines the rates drop, which is why the exclusion is a per-measure FILTER and not a global one.
synonyms: ['cancellation rate', 'cancels']
- name: On-Time %
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND arrival_date <= first_confirmed_date) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: Arrival basis, first-confirmed promise, zero-day tolerance, line grain — the dictionary default, because arrival is where the customer measures. A line past its promise date with no arrival recorded is a miss, not an exclusion. See On-Time % (ship) for the warehouse cut.
synonyms: ['otd', 'on time delivery', 'on time rate', 'on time arrival']
- name: On-Time % (ship)
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND first_ship_date <= first_confirmed_date) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: The same test on the ship date instead of the arrival date — what the warehouse controls, transit excluded. It reads higher than On-Time % by roughly the transit time, and the gap between the two is where retail compliance charges live.
synonyms: ['on time ship', 'otd ship basis', 'ship on time']
- name: In-Full %
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND first_pass_shipped_qty >= ordered_qty) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: First pass against the original ordered quantity. This is also line fill rate, under the weighting switch in the fill rate entry — the same test, two names in the wild.
synonyms: ['in full', 'line fill', 'line fill rate', 'complete rate']
- name: OTIF %
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND arrival_date <= first_confirmed_date AND first_pass_shipped_qty >= ordered_qty) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: "The joint test on arrival basis, evaluated on the same line. Never On-Time % multiplied by In-Full %: late lines are disproportionately short lines, so the product overstates this number and no correction factor repairs it."
synonyms: ['otif', 'difot', 'on time in full']
- name: OTIF % (ship)
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND first_ship_date <= first_confirmed_date AND first_pass_shipped_qty >= ordered_qty) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: The joint test on ship basis — the warehouse-accountability cut of OTIF %, and the number a shipping site can fairly be held to. Report it as ship basis every time; the two are not interchangeable.
synonyms: ['otif ship basis', 'difot ship']
- name: Split Shipment Rate %
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND delivery_count > 1) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: "The share of scored lines that took more than one shipment. First-attempt scoring is only honest with this number beside it: it says how often the first attempt was never going to be the whole line."
synonyms: ['split shipments', 'split lines', 'partial shipments']
- name: Fill Rate %
expr: 100.0 * SUM(first_pass_shipped_qty) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()) / NULLIF(SUM(ordered_qty) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: Unit fill, first pass, original-ordered denominator. Summed numerator over summed denominator at every scope — never an average of per-line percentages.
synonyms: ['fill rate', 'unit fill', 'quantity filled']
- name: Cumulative Fill Rate %
expr: 100.0 * SUM(shipped_qty) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()) / NULLIF(SUM(ordered_qty) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: "The same denominator with everything that eventually shipped in the numerator. Published only as the contrast to Fill Rate %: it approaches 100 percent by construction, so the gap between the two is the backorder recovery, not a better version of the metric."
synonyms: ['cumulative fill', 'eventual fill rate']
- name: Perfect Order % (line)
expr: 100.0 * COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND arrival_date <= first_confirmed_date AND first_pass_shipped_qty >= ordered_qty AND damage_free_flag AND invoice_accurate_flag) / NULLIF(COUNT(1) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: All four declared components passing on the same line. If damage or invoice accuracy is not measurable in your gold layer, drop the term from this expression and rename the measure — a shorter component set is a different metric.
synonyms: ['perfect order line rate']
- name: Perfect Order % (order)
expr: 100.0 * COUNT(DISTINCT order_number) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date() AND order_all_lines_perfect) / NULLIF(COUNT(DISTINCT order_number) FILTER (WHERE ordered_qty > canceled_qty AND first_confirmed_date <= current_date()), 0)
comment: "The classical order-grain test, read from an order-grain flag stamped on every line of the order. Declared convention: an order enters the population as soon as one of its lines is scored, and the same cancel and maturity exclusions apply. A metric view aggregates one grain at a time, so the all-lines-pass rollup has to be computed in the fact, not here."
synonyms: ['perfect order', 'perfect order rate', 'pof']
- name: Median Lead Time Days
expr: MEDIAN(DATEDIFF(first_ship_date, order_entry_date)) FILTER (WHERE first_ship_date IS NOT NULL)
comment: "Customer order-to-ship clock: order_entry_date to first_ship_date, calendar days. Durations exist only for shipped lines — the FILTER is redundant to the engine and kept as an explicit statement that an open line contributes no duration rather than a zero. Recomputed from the pooled lines at whatever scope is queried; a median of medians is a different number."
synonyms: ['lead time', 'median lead time', 'order to ship']
- name: P90 Lead Time Days
expr: PERCENTILE(DATEDIFF(first_ship_date, order_entry_date), 0.9) FILTER (WHERE first_ship_date IS NOT NULL)
comment: The statistic safety stock is actually sized against, on the same shipped-lines-only population as the median. Recomputed from the pooled lines at every scope — a mean of site-level P90s is not the network P90.
synonyms: ['p90', '90th percentile lead time', 'lead time tail']
$$;Genie space instructions
Paste this into the space's instructions. The measures carry the definitions; this text carries the routing and the rules that a definition alone cannot enforce.
Rules for <catalog>.gold.mv_order_fulfillment — customer service metrics
at order-line grain.
Definitions here are fixed by the metric view. Answer from the measures
below; do not compute service metrics from raw columns.
The scoring convention, which belongs in every answer:
- A rate scores a line only once its first-confirmed promise date has passed
and the line was not fully canceled. Open lines promised for a future date
are not failures and are not in any rate.
- Say so when a question covers the current month, because part of that month
is not scored yet. Order Lines Scored is how many lines the rate actually
counted; report it beside any rate a decision hangs on.
Default reading of an unqualified question:
- "OTIF", "DIFOT" -> MEASURE(`OTIF %`), which is arrival basis. State the
settings in the answer: line grain, first-confirmed promise, arrival basis,
first attempt, zero-day tolerance.
- "Service level" is deliberately not routed. It names OTIF %, Fill Rate %,
and cycle service level (a per-replenishment-cycle event count this view
does not compute) in different organizations. Ask which is meant before
answering, and name the measure used in the answer.
- A question about the warehouse, a shipping site, or "did we ship on time"
-> the (ship) variants. Name the basis every time; the two differ by transit
and are not interchangeable.
- A question about timing ("late", "missed the date") -> On-Time %.
- A question about quantity ("short", "filled", "complete") -> Fill Rate % for
units, In-Full % for lines. Say which weighting you used.
- "Perfect order" -> Perfect Order % (order). Name the four components.
- "Lead time" -> Median Lead Time Days with P90 Lead Time Days beside it. Never
report a mean lead time; the tail is the decision-relevant part.
Rules that must never be broken:
- Never multiply On-Time % by In-Full % to approximate OTIF %. The joint test
is its own measure; the product overstates it.
- Never average a percentage across groups, periods, or sites. Every rate here
is recomputed from summed components at the grain being asked for.
- Never present a percentile as an average of percentiles.
- Never mix an arrival-basis and a ship-basis number in one comparison.
- Cumulative Fill Rate % is context for Fill Rate %, never a substitute: it
approaches 100 percent by construction.
- When a question filters on Compliance Program, add that the program scores
against its own switch settings, so the number is a cut of the practice
definition and not the customer's scorecard.
- If asked for a definition, quote the measure comment rather than describing
the SQL.
Date handling: Order Month is the first-confirmed promise month, not the ship
month or the arrival month. If a question means shipments, say which basis you
used.Trusted-asset candidates
Saving a query as a trusted asset in the space pins the question to this answer, so the phrasing below stops being re-derived every time someone asks it.
OTIF and its two components, by month
“What was our OTIF last quarter, and was the miss timing or quantity?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. The rates side by side answer almost every
-- unqualified service question: OTIF, which half failed, and the ship-basis
-- cut that says how much of the miss was transit rather than the warehouse.
-- Order Lines Scored travels with them because the newest month is only
-- partly scored - lines promised for a future date are not failures yet.
SELECT
`Order Month`,
MEASURE(`OTIF %`) AS otif_pct_arrival,
MEASURE(`OTIF % (ship)`) AS otif_pct_ship,
MEASURE(`On-Time %`) AS on_time_pct,
MEASURE(`In-Full %`) AS in_full_pct,
MEASURE(`Order Lines Scored`) AS lines_scored
FROM <catalog>.gold.mv_order_fulfillment
WHERE `Order Month` >= DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE(), -12))
GROUP BY ALL
ORDER BY `Order Month`;Worst customers by OTIF, with the cancel rate beside it
“Which customers had the worst OTIF this year?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. Cancel Rate % travels with every ranked service
-- cut, because excluding cancels raises OTIF and the rank would otherwise
-- reward the accounts whose short lines were cut rather than shipped.
SELECT
`Customer`,
`Channel`,
MEASURE(`OTIF %`) AS otif_pct,
MEASURE(`Cancel Rate %`) AS cancel_rate_pct,
MEASURE(`Order Lines Scored`) AS lines_scored
FROM <catalog>.gold.mv_order_fulfillment
WHERE `Order Month` >= DATE_TRUNC('YEAR', CURRENT_DATE())
GROUP BY ALL
HAVING MEASURE(`Order Lines Scored`) >= 50
ORDER BY otif_pct
LIMIT 20;The lead-time distribution by shipping site
“How long are we taking to ship, and where is the tail?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. Both statistics are recomputed from the pooled
-- lines of each site; neither is derived from the other, and neither is a
-- mean. The gap between them is what safety stock is sized against.
SELECT
`Ship-From Site`,
MEASURE(`Median Lead Time Days`) AS median_days,
MEASURE(`P90 Lead Time Days`) AS p90_days,
MEASURE(`Order Lines Scored`) AS lines_scored
FROM <catalog>.gold.mv_order_fulfillment
WHERE `Order Month` >= DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE(), -6))
GROUP BY ALL
ORDER BY p90_days DESC;Notes on this model
- Why the population rules are per-measure and not a global filter — Every rate here shares one predicate — not fully canceled, past its promise date — so a global `filter:` line would compute the same numbers for them. It would also delete the two measures that exist to keep the exclusions honest. Order Line Count reports the whole book including canceled and not-yet-due lines, and Cancel Rate % is built entirely from the lines the rates drop; both go to zero under a global filter. Keeping the predicate inside each measure is what lets a reader see the size of the exclusion next to the number it improves.
- The maturity rule right-censors the newest lines, on purpose — Scoring a line only after its promise date has passed means the current month is always partly unscored, and a service trend read to the last day will look thin at the right-hand edge. That is the honest failure mode: the alternative — counting an open line promised for next month as a miss because nothing has arrived yet — reports a collapse that has not happened. Publish Order Lines Scored beside any rate someone acts on, so the size of the scored population is visible rather than assumed.
- The joins are LEFT OUTER, and that is load-bearing — Metric view joins carry LEFT OUTER semantics: every fact row survives, and an unmatched dimension column comes back NULL. A line whose customer key has not landed in dim_customer still counts in OTIF % — it just falls into a NULL Customer bucket. That is the correct behavior for a service metric, and it is worth saying out loud, because it means a dimension-load failure shows up as an unlabeled bucket rather than as a silently improved score.
- Joins must be many-to-one — If a dimension is not unique on its join key, only the first matching row is used and the aggregate skews with no error raised. Assert uniqueness on dim_customer, dim_product, and dim_ship_from in the pipeline that builds them, not here.
- Changing a switch is a view replacement — Editing the promise column, the tolerance window, or the exclusion predicate and re-running CREATE OR REPLACE VIEW changes what every saved question in the space means, retroactively. Treat it as a restatement: announce it, and keep the old definition as a second view for as long as anyone is still comparing to last year's numbers.
S-02 — Forecast vs actual
S-02 · Semantic model
<catalog>.gold.mv_forecast_accuracy
one row per item × location × plan month × lag
How far off was the forecast, which way did it lean, and at which lag — with the lag stated in every answer instead of assumed?
Forecast accuracy and forecast bias are the same components aggregated two ways — absolute error over actuals, and signed error over actuals — so they belong in one metric view. Built separately they eventually disagree about what the actuals were, which is the one argument a forecast review cannot afford.
The lag is the part a semantic layer has to defend. This fact carries every lag, which means an unfiltered query silently averages a lag-1 forecast with a lag-6 forecast and reports a number that describes no planning cycle at all. The Scored Lag dimension exists so the instruction block can make one filter mandatory, and Absolute Error Qty carries a comment saying it must never be summed across lags, because each lag restates the same plan month's error.
WAPE is the default error metric here for the reason the dictionary gives: it is volume-weighted, it has no division-by-zero hole, and an average of per-item MAPEs is a different and worse number. MAPE is deliberately absent rather than provided as a convenience — a measure that exists in a Genie space will eventually be asked for. One spelling note: WAPE is the same formula this library's showcase writes as WMAPE — summed absolute error over summed actuals — one construction under two names in circulation.
Source fact: fct_forecast_vs_actual — one row per item × location × plan month × lag.
The switch ledger
Every setting below is the dictionary's, restated as an expression. Each switch links back to the entry that argues it out.
| Metric & measures | Switch | Setting in this model |
|---|---|---|
| Forecast AccuracyWAPE % · Absolute Error Qty · Forecast Qty · Actual Qty | Error metric | WAPE — summed absolute error over summed actuals; MAPE not exposed |
| Lag | The scored lag, set once on dim_lag and made mandatory in the instructions | |
| Evaluation grain | Item × location × plan month — the fact's grain; rollups re-divide, never re-average | |
| Forecast BiasBias % · Attainment % | Metric form | Signed percentage error; the streak count is a downstream view, not a measure |
| Sign convention | Forecast minus actual — positive is over-forecast, stated in the comment | |
| Window | Chosen at query time by the Plan Month filter; the view stores no window |
The metric view
Replace <catalog> and run it in a SQL editor or a notebook. As written it needs Databricks Runtime 17.3 or later, because every dimension and measure carries synonyms; strip those lines and it creates on 17.2, at the cost of the vocabulary routing that makes the space work.
1 parameter not filled: <catalog>
CREATE OR REPLACE VIEW <catalog>.gold.mv_forecast_accuracy
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
comment: "Forecast accuracy and bias at item x location x plan month x lag. Practice defaults: WAPE as the error metric, the scored lag as the reporting lag, forecast minus actual as the sign convention."
source: <catalog>.gold.fct_forecast_vs_actual
joins:
- name: item
source: <catalog>.gold.dim_item
on: source.item_key = item.item_key
- name: location
source: <catalog>.gold.dim_location
on: source.location_key = location.location_key
- name: lag
source: <catalog>.gold.dim_lag
on: source.lag_key = lag.lag_key
dimensions:
- name: Plan Month
expr: plan_month
comment: The month being scored, not the month the forecast was made in. The two differ by exactly the lag.
synonyms: ['month', 'period', 'forecast month']
- name: Lag Months
expr: lag.lag_months
comment: How old the forecast was when the month started. Every accuracy number is meaningless without it, which is why it is a first-class dimension rather than a filter buried in a query.
synonyms: ['lag', 'horizon', 'forecast age']
- name: Scored Lag
expr: lag.is_scored_lag
comment: True on the one lag the business commits supply at. Filtering to it is the difference between an accuracy figure and an average of incomparable ones.
synonyms: ['committed lag', 'reporting lag']
- name: Planner
expr: item.planner
comment: The accountability cut — every error lands on a named worklist.
synonyms: ['demand planner', 'owner']
- name: Product Family
expr: item.family
comment: Family rollup from the conformed item dimension.
synonyms: ['family', 'product family', 'category']
- name: ABC Class
expr: item.abc_class
comment: Volume classification. Accuracy read by ABC class is the fastest way to see whether a headline WAPE is being carried by the C tail.
synonyms: ['abc', 'class', 'abc code']
- name: Site
expr: location.site
comment: Forecast where you fulfill — item and location together are the grain planners own.
synonyms: ['location', 'dc', 'plant']
measures:
- name: Forecast Qty
expr: SUM(plan_qty)
comment: The plan quantity as of the queried lag. The column is plan_qty on the published fact; the measure is named for what a planner calls it.
synonyms: ['forecast', 'plan', 'plan quantity', 'forecast units']
- name: Actual Qty
expr: SUM(actual_qty)
comment: Demand actuals for the plan month, restated identically on every lag row of that month.
synonyms: ['actuals', 'demand', 'actual demand']
- name: Absolute Error Qty
expr: SUM(ABS(plan_qty - actual_qty))
comment: "The additive workhorse — rank planners, families, and items by summed absolute error before showing anyone a percentage. A sum, not a mean: mean absolute deviation is a different statistic and is not defined here. Never sum across lags, because each lag restates the error for the same plan month."
synonyms: ['absolute error', 'error units', 'summed error']
- name: WAPE %
expr: 100.0 * SUM(ABS(plan_qty - actual_qty)) / NULLIF(SUM(actual_qty), 0)
comment: "Volume-weighted absolute percentage error, recomputed from summed components at every scope. This is an error measure: accuracy is 100 minus it, and it is reported as error so nobody has to guess whether a floor was applied. An average of per-item MAPEs is a different, worse number and is not available here."
synonyms: ['wape', 'wmape', 'forecast error', 'weighted mape']
- name: Bias %
expr: 100.0 * SUM(plan_qty - actual_qty) / NULLIF(SUM(actual_qty), 0)
comment: Signed error over actuals, forecast minus actual — positive means over-forecast. Nets to near zero at portfolio level even when every item is biased, so read it at the grain someone acts on.
synonyms: ['bias', 'forecast bias', 'signed error', 'over forecast']
- name: Attainment %
expr: 100.0 * SUM(actual_qty) / NULLIF(SUM(plan_qty), 0)
comment: "The plan-denominator view the supply side asks for — same components as Bias %, different denominator. Above 100% means demand exceeded the plan. 'Attainment' has no published definition to inherit — full-text scans of the CSCMP glossary, the industry's main process-reference model, and the ASCM Dictionary return nothing for it — so this is the house construction: actuals over plan, uncapped, at the queried lag."
synonyms: ['plan attainment', 'forecast attainment', 'demand attainment']
$$;Genie space instructions
Paste this into the space's instructions. The measures carry the definitions; this text carries the routing and the rules that a definition alone cannot enforce.
Rules for <catalog>.gold.mv_forecast_accuracy — forecast accuracy and bias
at item x location x plan month x lag.
Every answer from these measures names the lag it used. An accuracy figure
without a stated lag is not a metric.
Mandatory filter:
- When a question asks for forecast accuracy, error, or bias without naming a
lag, filter `Scored Lag` = true and say in the answer which lag that is.
- When a question compares periods, planners, or families, the lag must be the
same on both sides of the comparison. If it cannot be, say so instead of
answering.
Routing:
- "Accuracy", "error", "how far off" -> MEASURE(`WAPE %`). Report accuracy as
100 minus WAPE only if asked for accuracy, and say it can go negative at item
grain.
- "Bias", "running high/low", "over-forecasting" -> MEASURE(`Bias %`).
Positive is over-forecast (forecast minus actual). State the convention.
- "Attainment", "did we hit the plan" -> MEASURE(`Attainment %`). Say that no
published definition of attainment exists and this is the house construction:
actuals over plan, uncapped, at the lag used.
- "Which planners/families are worst" -> rank by MEASURE(`Absolute Error Qty`)
first, then show WAPE % beside it. A percentage ranking is dominated by small
items.
Rules that must never be broken:
- Never sum Absolute Error Qty across more than one lag. Each lag restates the
same plan month.
- Never average WAPE % or Bias % across groups or months; both are recomputed
from summed components at the grain asked for.
- MAPE is not available in this space. If asked for it, say WAPE is what is
defined here and why the two are not comparable.
- Accuracy and bias fail independently. When one is asked for and the other
looks unusual, report both.Trusted-asset candidates
Saving a query as a trusted asset in the space pins the question to this answer, so the phrasing below stops being re-derived every time someone asks it.
Accuracy and bias by month at the scored lag
“How accurate has the forecast been this year?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. The Scored Lag filter is the whole point: without
-- it this query averages every lag on the fact and describes no planning
-- cycle at all.
SELECT
`Plan Month`,
MEASURE(`WAPE %`) AS wape_pct,
MEASURE(`Bias %`) AS bias_pct,
MEASURE(`Actual Qty`) AS actual_qty
FROM <catalog>.gold.mv_forecast_accuracy
WHERE `Scored Lag`
AND `Plan Month` >= DATE_TRUNC('YEAR', CURRENT_DATE())
GROUP BY ALL
ORDER BY `Plan Month`;Planners ranked by summed error, not by percentage
“Which planners have the biggest forecast problem?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. Ranked on absolute error units so the list is not
-- dominated by slow movers with three-digit percentage errors; WAPE % and
-- Bias % ride along as context, each recomputed at planner grain.
SELECT
`Planner`,
MEASURE(`Absolute Error Qty`) AS abs_error_qty,
MEASURE(`WAPE %`) AS wape_pct,
MEASURE(`Bias %`) AS bias_pct
FROM <catalog>.gold.mv_forecast_accuracy
WHERE `Scored Lag`
AND `Plan Month` >= DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE(), -6))
GROUP BY ALL
ORDER BY abs_error_qty DESC
LIMIT 20;How accuracy decays with lag
“How much better is the near-term forecast than the committed one?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate, and the one query in this space that is supposed
-- to span lags: each row is one lag, and no measure is summed across them.
SELECT
`Lag Months`,
MEASURE(`WAPE %`) AS wape_pct,
MEASURE(`Bias %`) AS bias_pct
FROM <catalog>.gold.mv_forecast_accuracy
WHERE `Plan Month` >= DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE(), -12))
GROUP BY ALL
ORDER BY `Lag Months`;Notes on this model
- Why the lag lives on a dimension and not in a filter — Putting is_scored_lag on dim_lag means the convention is set once, in the pipeline that builds the dimension, and every consumer — this view, the dashboards, the notebooks — reads the same answer. A lag filter written into the metric view's `filter:` line would hide the other lags entirely, and the lag-decay query above is one of the most useful things this fact can answer.
- The sign convention is in the comment for a reason — Forecast minus actual and actual minus forecast are both defensible, and a room where half the audience assumes one and half the other will spend twenty minutes discovering it agrees. Genie surfaces the measure comment when asked what a number means, so the convention travels with the number.
- Accuracy floors are a presentation decision, not a measure — One minus WAPE goes negative as soon as absolute error exceeds demand, which happens routinely at item grain. This view publishes the error, not the floored accuracy, so nobody has to reverse-engineer whether a 0% item was clipped.
S-03 — Inventory position
S-03 · Semantic model
<catalog>.gold.mv_inventory_position
one row per item × stocking location × snapshot date
How much are we holding, how long would it last, and how much of it has no forward demand — read one snapshot at a time?
A periodic snapshot fact is the one shape a natural-language interface reliably gets wrong. Stock is semi-additive: it sums across items and locations and it does not sum across dates. Ask an unguarded model for on-hand value this year and it will happily add up 365 daily snapshots and report a number roughly 365 times too large — and it will look plausible.
So the first instruction in this space is not a definition, it is a date rule: pin one snapshot date, default to the latest, and only span dates when the question is explicitly a trend. Latest Snapshot Date exists as a helper measure so a query can find that date without a subquery over the raw fact.
The ratios follow the dictionary's recomputation rule exactly. Days of supply is pooled stock over pooled demand, never an average of item-level coverage; SLOB % is flagged slow-and-obsolete value over total value at whatever scope is asked for; turns is annualized consumption over the on-hand value at the pinned snapshot. Each of those conventions is named in its comment, because each of them is a switch someone else sets differently.
Source fact: fct_inventory_position — one row per item × stocking location × snapshot date.
The switch ledger
Every setting below is the dictionary's, restated as an expression. Each switch links back to the entry that argues it out.
| Metric & measures | Switch | Setting in this model |
|---|---|---|
| Days of Supply (DOS / DIO)Days of Supply · Weeks of Supply · On-Hand Qty | Demand basis | Trailing 13-week average demand, stated in the column name and the comment |
| Units vs value | Units — the value read is On-Hand Value beside it, never mixed into this ratio | |
| Zero-demand items | Pooled denominators, so no item-level infinity can enter a scope; a scope with no demand at all returns NULL rather than a large number | |
| Inventory TurnsInventory Turns · On-Hand Value | Numerator | Annualized trailing cost of goods — cost against cost |
| Denominator | On-hand value at the pinned snapshot, not an average of period ends — named in the comment | |
| Valuation | Whatever unit_cost on the fact is struck at; both halves of the ratio use it | |
| Excess & Obsolete (E&O)Excess Value · Obsolete Value · SLOB Value · SLOB % | Excess definition | Coverage beyond the target quantity the fact carries — the horizon behind target_qty is declared upstream |
| Obsolete trigger | The is_obsolete flag, set upstream from lifecycle status corroborated by zero demand | |
| Gross vs net of reserve | Gross value — the operational exposure; the net-of-reserve view is a finance measure this fact does not carry |
The metric view
Replace <catalog> and run it in a SQL editor or a notebook. As written it needs Databricks Runtime 17.3 or later, because every dimension and measure carries synonyms; strip those lines and it creates on 17.2, at the cost of the vocabulary routing that makes the space work.
1 parameter not filled: <catalog>
CREATE OR REPLACE VIEW <catalog>.gold.mv_inventory_position
WITH METRICS
LANGUAGE YAML
AS $$
version: 1.1
comment: "Inventory position at item x stocking location x snapshot date. Semi-additive: pin one snapshot date unless the question is a trend. Ratios are recomputed from summed components at every scope."
source: <catalog>.gold.fct_inventory_position
joins:
- name: item
source: <catalog>.gold.dim_item
on: source.item_key = item.item_key
- name: location
source: <catalog>.gold.dim_location
on: source.location_key = location.location_key
dimensions:
- name: Snapshot Date
expr: snapshot_date
comment: The snapshot the row belongs to. Every measure below is read at one value of this dimension unless the question is explicitly a trend.
synonyms: ['date', 'as of', 'snapshot', 'period end']
- name: Site
expr: location.site
comment: Site rolls up warehouses — the cut most inventory reviews start with.
synonyms: ['plant', 'dc', 'location']
- name: Warehouse
expr: location.warehouse
comment: Stocking location beneath the site.
synonyms: ['wh', 'storage location']
- name: Location Type
expr: location.location_type
comment: Separates the stocking locations a coverage question means from the transit, quarantine, and consignment buckets it usually does not.
synonyms: ['stock type', 'bucket']
- name: ABC Class
expr: item.abc_class
comment: Volume classification from the conformed item dimension.
synonyms: ['abc', 'class']
- name: Planner
expr: item.planner
comment: Who owns the replenishment decision for the item.
synonyms: ['owner', 'supply planner']
measures:
- name: Latest Snapshot Date
expr: MAX(snapshot_date)
comment: Helper. Lets a question find the current snapshot without a subquery over the raw fact — read it first, then filter Snapshot Date to it.
synonyms: ['as of date', 'current snapshot']
- name: On-Hand Qty
expr: SUM(on_hand_qty)
comment: Semi-additive. Sums across items and locations; across dates take the period end or the average, never the sum.
synonyms: ['on hand', 'stock', 'inventory units', 'quantity on hand']
- name: On-Hand Value
expr: SUM(on_hand_value)
comment: Semi-additive, same rule as On-Hand Qty. Valued at whatever unit_cost is struck at upstream — keep any cost-based ratio on the same valuation.
synonyms: ['inventory value', 'stock value', 'on hand dollars']
- name: Days of Supply
expr: 7.0 * SUM(on_hand_qty) / NULLIF(SUM(trailing_13wk_avg_weekly_demand), 0)
comment: Trailing basis, 13-week window, units, expressed in days. Pooled stock over pooled demand at every scope — never the average of item-level coverage, which is dominated by whichever items happen to have thin demand. A scope with no trailing demand at all returns NULL rather than a large number.
synonyms: ['dos', 'days on hand', 'doh', 'coverage', 'days of stock']
- name: Weeks of Supply
expr: SUM(on_hand_qty) / NULLIF(SUM(trailing_13wk_avg_weekly_demand), 0)
comment: "The same ratio in the unit weekly planning cycles are run in: Days of Supply is exactly 7 times this. Published as its own measure rather than as a synonym, so an answer given in weeks can never be read as days."
synonyms: ['wos', 'weeks of supply', 'weeks on hand', 'weeks of stock']
- name: Inventory Turns
expr: 4.0 * SUM(trailing_13wk_cogs_value) / NULLIF(SUM(on_hand_value), 0)
comment: "Annualized trailing 13-week cost of goods over on-hand value at the pinned snapshot. Declared deviation: the dictionary default denominator is an average of period-end balances, and this fact is a snapshot, so a single pinned date is what a metric view can compute honestly here. Read it at period ends for comparability, or land an avg_on_hand_value column on the snapshot upstream and divide by that instead."
synonyms: ['turns', 'turnover', 'ito', 'inventory turnover']
- name: Excess Value
expr: SUM(GREATEST(on_hand_qty - target_qty, 0) * unit_cost)
comment: Value held above the stocking target, computed per row and then summed. The coverage horizon behind target_qty is a pipeline decision — print it wherever this number appears, because moving the horizon moves the number with nothing physically changing.
synonyms: ['excess', 'excess inventory', 'overstock']
- name: Obsolete Value
expr: SUM(on_hand_value) FILTER (WHERE is_obsolete)
comment: "Reported separately from Excess Value on purpose: excess is a planning problem with an operational answer, obsolete is a write-off with an accounting answer, and one blended number hides both. Add the two for the dictionary E&O total; do not use SLOB Value for that, which is a different population."
synonyms: ['obsolete', 'dead stock', 'write off exposure']
- name: SLOB Value
expr: SUM(on_hand_value) FILTER (WHERE is_slow_moving OR is_obsolete)
comment: Value on rows flagged slow-moving or obsolete, gross of any reserve — a flag-based population, not the sum of Excess Value and Obsolete Value. Excess Value is target-based and can sit on a fast-moving item, so the two overlap without containing each other. The dictionary total is Excess Value plus Obsolete Value; this measure is the slow-and-dead exposure, reported under its own name.
synonyms: ['slob', 'slow moving', 'e and o', 'excess and obsolete']
- name: SLOB %
expr: 100.0 * SUM(on_hand_value) FILTER (WHERE is_slow_moving OR is_obsolete) / NULLIF(SUM(on_hand_value), 0)
comment: SLOB Value over total on-hand value, recomputed from both sums at whatever scope is queried. Same flag-based population as SLOB Value, so it does not reconcile to Excess Value plus Obsolete Value.
synonyms: ['slob percent', 'slob share', 'e and o percent']
$$;Genie space instructions
Paste this into the space's instructions. The measures carry the definitions; this text carries the routing and the rules that a definition alone cannot enforce.
Rules for <catalog>.gold.mv_inventory_position — inventory position at
item x stocking location x snapshot date.
This is a periodic snapshot. Stock does not add up across dates.
The date rule comes before every definition:
- Pin exactly one `Snapshot Date` unless the question is explicitly a trend
("over time", "by month", "trend", "since"). Default to the latest snapshot,
which MEASURE(`Latest Snapshot Date`) returns.
- For a trend, one row per snapshot date — never a sum across dates. Say which
snapshots the series is built from.
- State the snapshot date in every answer.
Routing:
- "How much inventory", "stock", "what are we holding" -> On-Hand Value, with
On-Hand Qty if the question is operational rather than financial.
- "How long will it last", "coverage", "days of supply" -> Days of Supply.
"Weeks of supply", "WOS" -> Weeks of Supply. Never convert one into the other
in prose; use the measure that matches the unit asked for. Say it is a
trailing 13-week demand basis, and that it describes the recent past rather
than the plan.
- "Turns", "turnover" -> Inventory Turns. Name the numerator basis, and say the
denominator is the pinned snapshot rather than an average of period ends.
- "Excess" -> Excess Value, which is stock above the stocking target.
"Obsolete", "dead" -> Obsolete Value. "Slow moving", "SLOB", "E&O" -> SLOB
Value or SLOB %. These are two different populations: excess is target-based
and SLOB is flag-based, so SLOB Value is not the total of the other two. The
E&O total is Excess Value plus Obsolete Value.
Rules that must never be broken:
- Never sum any On-Hand measure across snapshot dates.
- Never average Days of Supply, Inventory Turns, or SLOB % across items,
locations, or dates — each is recomputed from summed components at the grain
asked for.
- Targets are internal goals. Do not describe any number here as a benchmark or
an industry comparison.
- If the answer would divide by zero demand, the coverage measures return NULL.
Report the stock value and say coverage is undefined for that scope rather
than reporting a large number.Trusted-asset candidates
Saving a query as a trusted asset in the space pins the question to this answer, so the phrasing below stops being re-derived every time someone asks it.
The current position by site
“How much inventory are we holding and how long will it last?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. Note the pinned snapshot: the subquery reads the
-- latest date from the view itself, so the query is correct on any refresh
-- cadence and can never sum two snapshots together.
-- The subquery is a scalar filter on a dimension, not a re-aggregation of a
-- measure: wrapping MEASURE() output in an outer aggregate is the one
-- construction a metric view does not protect against.
SELECT
`Site`,
MEASURE(`On-Hand Value`) AS on_hand_value,
MEASURE(`Days of Supply`) AS days_of_supply,
MEASURE(`SLOB %`) AS slob_pct
FROM <catalog>.gold.mv_inventory_position
WHERE `Snapshot Date` = (
SELECT MEASURE(`Latest Snapshot Date`)
FROM <catalog>.gold.mv_inventory_position
GROUP BY ALL
)
GROUP BY ALL
ORDER BY on_hand_value DESC;Excess and obsolete as two numbers, by planner
“Who owns the excess, and how much of it is actually dead?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate. Excess and obsolete stay separate because they
-- have different owners and different answers. SLOB Value is a third,
-- flag-based population - not the total of the other two, which is why it is
-- read as its own column rather than as a check figure.
SELECT
`Planner`,
MEASURE(`Excess Value`) AS excess_value,
MEASURE(`Obsolete Value`) AS obsolete_value,
MEASURE(`SLOB Value`) AS slob_value
FROM <catalog>.gold.mv_inventory_position
WHERE `Snapshot Date` = (
SELECT MEASURE(`Latest Snapshot Date`)
FROM <catalog>.gold.mv_inventory_position
GROUP BY ALL
)
GROUP BY ALL
ORDER BY slob_value DESC
LIMIT 20;The position over time
“Is the inventory position improving?”
1 parameter not filled: <catalog>
-- Trusted-asset candidate, and the only shape in which this view may span
-- dates: one row per snapshot, nothing summed across them.
SELECT
`Snapshot Date`,
MEASURE(`On-Hand Value`) AS on_hand_value,
MEASURE(`Days of Supply`) AS days_of_supply,
MEASURE(`Inventory Turns`) AS turns
FROM <catalog>.gold.mv_inventory_position
WHERE `Snapshot Date` >= ADD_MONTHS(CURRENT_DATE(), -12)
GROUP BY ALL
ORDER BY `Snapshot Date`;Notes on this model
- Why the experimental window mechanism is not used here — Metric views can carry a `window:` block with `semiadditive: last`, which would make an on-hand measure resolve to the period-end value automatically when Snapshot Date is not in the GROUP BY. It is the right idea and it is still experimental — it pins the YAML to version 0.1, and it makes the semi-additivity invisible to whoever reads the query later. The date rule in the instruction block does the same job explicitly, and an explicit rule is what this library is for. Revisit when the mechanism leaves experimental status.
- What sets is_slow_moving and is_obsolete — Both flags are pipeline decisions stamped on the snapshot row, not derivations this view makes. The practice default behind them is the dictionary default: obsolete is a lifecycle end-of-life status corroborated by zero forward demand, and slow-moving is no issue movement across a declared window — the same window the trailing demand column is built over, so the two cannot disagree. Whatever your rule is, declare it next to the number, because a flag nobody can describe is a number nobody can act on.
- The target behind Excess Value is an internal goal — target_qty encodes a coverage horizon someone chose. Label it as an internal goal wherever the number surfaces, never as a benchmark, and treat a change to the horizon as a restatement of every excess figure that came before it.
- Stock age is not in this model — Aging buckets are a FIFO replay of the movement ledger, not a column on a snapshot fact — a derivation over a different grain than this view's. Keep it as its own fact and its own view rather than approximating it from last-movement dates here.
Standing the Genie space up
The three views belong in one space, not three. Genie can route across several metric views in a room, and a supply chain question rarely respects a fact boundary — “did service drop because we ran out of stock” reads two of these models.
- Create the three views. Run each
CREATE OR REPLACE VIEWabove against your catalog. They are views: creating them costs nothing and reads nothing until queried. - Check one query by hand. Run a trusted-asset candidate in a SQL editor before any of it reaches a space. A metric view that returns the wrong number is a modeling bug, and it is far cheaper to find here than through a chat answer.
- Add all three to one Genie space as the space's data assets, and give the space a name that says what it answers rather than which fact it sits on.
- Paste the instruction blocks into the space instructions, one after another. They are written to compose: each opens by naming the view it governs.
- Save the example queries as trusted assets, each under the question it answers. A trusted asset pins a phrasing to a known-good answer, which is what stops a room re-deriving last quarter's number three different ways.
- Ask the questions you already know the answers to. Before anyone else is invited in, ask the space ten questions whose answers you can check against an existing report. The disagreements are the point — each one is either a bug in the model or a definition someone has been reading differently.
One thing to watch in a shared space: the three models share vocabulary. “Site”, “DC”, “month”, “category”, and “planner” are synonyms on more than one of these views, and they do not mean the same thing: a shipping site is not a stocking location, and the order month is a promise month while the plan month is a forecast month. Each instruction block opens by naming the view it governs, which is what gives an answer somewhere to be anchored — but when a question could land in two of them, phrase it with the measure name (“OTIF % by ship-from site”, “days of supply by site”) rather than with the bare dimension. If a room turns out to ask ambiguous questions constantly, the fix is to rename the dimension in the YAML, not to add more instructions.
One habit worth keeping from day one: when a switch setting changes, the view changes for every question ever asked of it, retroactively. Announce it the way you would announce a restatement, and keep the previous definition as a second view for as long as anyone is still comparing against last year.
The same model in Snowflake Cortex Analyst
The practice builds on Databricks first, and metric views are what Genie consumes natively. Snowflake is supported, and the same model translates — Cortex Analyst reads a semantic view, defined in YAML, and the concepts line up closely enough that the translation is mechanical once two differences are understood.
The first is layering. A metric view has dimensions and measures; a Snowflake semantic view separates row-level expressions (facts) from aggregations (metrics), and hangs both off a table entry that names its base table and primary key. The second is that every table is declared, dimensions included: where the metric view reaches a planner attribute through a join alias, the semantic view declares dim_item as its own table entry with its own dimensions, and a relationship joins the two by column pair. So the model below carries four table entries — the fact and the three conformed dimensions — where the Databricks version carried one source and three joins.
One thing worth stating because it dates quickly: the older semantic model file dialect — the one whose tables carried a measures block — is superseded. What follows targets the current semantic view YAML specification that Cortex Analyst consumes today.
name: forecast_vs_actual
description: >-
Forecast accuracy and bias at item x location x plan month x lag.
Practice defaults: WAPE as the error metric, the scored lag as the
reporting lag, forecast minus actual as the sign convention.
tables:
- name: forecast_vs_actual
description: One row per item, location, plan month, and forecast lag.
base_table:
database: <database>
schema: GOLD
table: FCT_FORECAST_VS_ACTUAL
primary_key:
columns:
- ITEM_KEY
- LOCATION_KEY
- PLAN_MONTH
- LAG_KEY
time_dimensions:
- name: plan_month
synonyms:
- month
- period
- forecast month
description: >-
The month being scored, not the month the forecast was made in.
The two differ by exactly the lag.
expr: PLAN_MONTH
data_type: DATE
sample_values:
- '2026-01-01'
- '2026-02-01'
facts:
- name: plan_qty
synonyms:
- forecast quantity
- plan quantity
description: Forecast quantity for the row, at the lag of the row.
expr: PLAN_QTY
data_type: NUMBER
- name: actual_qty
synonyms:
- actual quantity
- demand
description: >-
Demand actuals for the plan month, restated identically on every
lag row of that month.
expr: ACTUAL_QTY
data_type: NUMBER
- name: absolute_error_qty
synonyms:
- absolute error
description: >-
Row-level absolute error, forecast against actual. A row-level
expression, which is what a fact is for; the metrics below sum it.
expr: ABS(PLAN_QTY - ACTUAL_QTY)
data_type: NUMBER
metrics:
- name: forecast_qty
synonyms:
- total forecast
- total plan
description: Forecast quantity summed over the queried scope.
expr: SUM(plan_qty)
- name: actual_qty_total
synonyms:
- total actuals
- total demand
description: Actual demand summed over the queried scope.
expr: SUM(actual_qty)
- name: absolute_error_qty_total
synonyms:
- total absolute error
description: >-
Summed absolute error, built from the row-level fact above. A sum,
not a mean. Never sum across more than one lag: each lag restates
the error for the same plan month.
expr: SUM(absolute_error_qty)
- name: wape_pct
synonyms:
- wape
- wmape
- forecast error
- weighted mape
description: >-
Volume-weighted absolute percentage error, recomputed from summed
components at every scope. An error measure: accuracy is 100 minus
it. An average of per-item MAPEs is a different, worse number and
is not defined here.
expr: 100.0 * SUM(absolute_error_qty) / NULLIF(SUM(actual_qty), 0)
- name: bias_pct
synonyms:
- bias
- forecast bias
- signed error
description: >-
Signed error over actuals, forecast minus actual. Positive means
over-forecast.
expr: 100.0 * SUM(plan_qty - actual_qty) / NULLIF(SUM(actual_qty), 0)
- name: attainment_pct
synonyms:
- plan attainment
- demand attainment
description: >-
Actuals over plan. Above 100 percent means demand exceeded the
plan.
expr: 100.0 * SUM(actual_qty) / NULLIF(SUM(plan_qty), 0)
- name: dim_lag
description: >-
One row per forecast lag. Carries the scored-lag convention, so it is
set once in the pipeline and every consumer reads the same answer.
base_table:
database: <database>
schema: GOLD
table: DIM_LAG
primary_key:
columns:
- LAG_KEY
dimensions:
- name: lag_months
synonyms:
- lag
- horizon
- forecast age
description: >-
How old the forecast was when the month started. Every accuracy
number is meaningless without it.
expr: LAG_MONTHS
data_type: NUMBER
- name: scored_lag
synonyms:
- committed lag
- reporting lag
description: >-
True on the one lag the business commits supply at. Filter to it
whenever a question does not name a lag.
expr: IS_SCORED_LAG
data_type: BOOLEAN
- name: dim_item
description: Conformed item dimension - ownership and classification.
base_table:
database: <database>
schema: GOLD
table: DIM_ITEM
primary_key:
columns:
- ITEM_KEY
dimensions:
- name: planner
synonyms:
- demand planner
- owner
description: The accountability cut for forecast error.
expr: PLANNER
data_type: VARCHAR
- name: product_family
synonyms:
- family
- product family
description: Family rollup from the conformed item dimension.
expr: FAMILY
data_type: VARCHAR
- name: abc_class
synonyms:
- abc
- class
description: Volume classification.
expr: ABC_CLASS
data_type: VARCHAR
is_enum: true
- name: dim_location
description: Conformed location dimension - forecast where you fulfill.
base_table:
database: <database>
schema: GOLD
table: DIM_LOCATION
primary_key:
columns:
- LOCATION_KEY
dimensions:
- name: site
synonyms:
- location
- plant
description: Site the item is forecast and fulfilled at.
expr: SITE
data_type: VARCHAR
relationships:
- name: forecast_to_lag
left_table: forecast_vs_actual
right_table: dim_lag
relationship_columns:
- left_column: LAG_KEY
right_column: LAG_KEY
- name: forecast_to_item
left_table: forecast_vs_actual
right_table: dim_item
relationship_columns:
- left_column: ITEM_KEY
right_column: ITEM_KEY
- name: forecast_to_location
left_table: forecast_vs_actual
right_table: dim_location
relationship_columns:
- left_column: LOCATION_KEY
right_column: LOCATION_KEY
verified_queries:
- name: accuracy_and_bias_by_month_at_scored_lag
question: How accurate has the forecast been this year?
verified_at: 1787443200
verified_by: Summit Analytics
sql: |
SELECT
f.PLAN_MONTH,
100.0 * SUM(ABS(f.PLAN_QTY - f.ACTUAL_QTY)) / NULLIF(SUM(f.ACTUAL_QTY), 0) AS WAPE_PCT,
100.0 * SUM(f.PLAN_QTY - f.ACTUAL_QTY) / NULLIF(SUM(f.ACTUAL_QTY), 0) AS BIAS_PCT,
SUM(f.ACTUAL_QTY) AS ACTUAL_QTY
FROM <database>.GOLD.FCT_FORECAST_VS_ACTUAL AS f
JOIN <database>.GOLD.DIM_LAG AS l
ON l.LAG_KEY = f.LAG_KEY
WHERE l.IS_SCORED_LAG
AND f.PLAN_MONTH >= DATE_TRUNC('YEAR', CURRENT_DATE())
GROUP BY f.PLAN_MONTH
ORDER BY f.PLAN_MONTH
- name: planners_ranked_by_summed_error
question: Which planners have the biggest forecast problem?
verified_at: 1787443200
verified_by: Summit Analytics
sql: |
SELECT
i.PLANNER,
SUM(ABS(f.PLAN_QTY - f.ACTUAL_QTY)) AS ABS_ERROR_QTY,
100.0 * SUM(ABS(f.PLAN_QTY - f.ACTUAL_QTY)) / NULLIF(SUM(f.ACTUAL_QTY), 0) AS WAPE_PCT
FROM <database>.GOLD.FCT_FORECAST_VS_ACTUAL AS f
JOIN <database>.GOLD.DIM_LAG AS l
ON l.LAG_KEY = f.LAG_KEY
JOIN <database>.GOLD.DIM_ITEM AS i
ON i.ITEM_KEY = f.ITEM_KEY
WHERE l.IS_SCORED_LAG
AND f.PLAN_MONTH >= DATEADD(MONTH, -6, DATE_TRUNC('MONTH', CURRENT_DATE()))
GROUP BY i.PLANNER
ORDER BY ABS_ERROR_QTY DESC
LIMIT 20
custom_instructions: >-
Every answer names the lag it used. When a question does not name one,
filter scored_lag to true and say which lag that is. Never sum absolute
error across more than one lag. Never average wape_pct or bias_pct across
groups or months. MAPE is not defined here.Field by field
| Databricks metric view | Snowflake semantic view | Note |
|---|---|---|
| source | tables[].base_table (database / schema / table) | Three-level namespace either way; Snowflake splits it into three keys and wants a primary_key beside it. |
| dimensions[] | dimensions[] and time_dimensions[] | Date columns move to time_dimensions, which is also where sample_values earns its keep for date-format grounding. Dimensions reached through a join move to the joined table's own entry rather than staying on the fact. |
| measures[] (aggregate expr) | metrics[] | Aggregations live in metrics. WAPE stays one expression over summed components — the recomputation rule survives the translation intact. |
| no equivalent | facts[] | Row-level expressions have no counterpart in a metric view, where every measure aggregates. The model below uses one: absolute_error_qty names the per-row error, and absolute_error_qty_total and wape_pct are both built by summing it. |
| synonyms | synonyms | Same idea, same purpose: the vocabulary a question is actually asked in. Snowflake takes them on tables, dimensions, facts, and metrics alike. |
| comment | description | The definition the interface quotes back. Both are the right place for the switch settings, and both surface to the user. |
| joins[] (name / source / on) | relationships[] (left_table / right_table / relationship_columns) | The semantic view spec infers the relationship type from the declared column pairs — there is no join_type or relationship_type to set here. Uniqueness on the right-hand table is still the modeler's responsibility, exactly as it is on Databricks. |
| Genie instructions | custom_instructions | The same rules text, moved inside the model file. Snowflake can also split it into module_custom_instructions for SQL generation and question categorization. |
| Genie trusted assets | verified_queries | Both pin a question to a known-good SQL answer. Snowflake's carry verified_at as a Unix timestamp and verified_by, so the verification itself is auditable. |
| filter (global WHERE) | tables[].filters[] | Neither model here uses one — the exclusions are inside the measures on purpose, and that decision translates unchanged. |
- What does not translate — A metric view is a Unity Catalog object: it is queried with MEASURE(), governed with GRANT, and read by AI/BI dashboards and notebooks as well as by Genie. A Snowflake semantic view is a model definition Cortex Analyst reads — the verified queries above are ordinary SQL joining the base tables, not calls into a shared metric layer. The definitions match; the enforcement does not, and neither engine removes the need for discipline: a metric view enforces the definition only within a single query block — re-aggregating MEASURE() output through a subquery returns a wrong number silently (verified in one workspace on 2026-08-27) — while on Snowflake the discipline is carried by review of the model file rather than by the engine at all.
- Keep one authored source — Two hand-maintained copies of the same definition drift within a quarter. If both platforms are live, treat one file as the authored artifact and generate the other from it — the same reason this page's Databricks YAML is serialized from typed data rather than typed twice.
What these models deliberately do not cover
Nine of the dictionary's thirteen metrics are covered above. The remaining four are missing for reasons worth stating, because these are exactly the gaps a semantic layer is usually asked to paper over.
- Cash-to-cash spans three facts and has no shared grain. Inventory days come from the snapshot fact; receivables and payables days come from ledgers that carry no product and no site. A metric view has one source, so a cash-to-cash view would mean either joining three facts at incompatible grains or allocating receivables to products — which manufactures a number rather than measuring one. The honest shape is three entity-level figures computed separately and subtracted in the presentation layer, with the seam labeled. The one thing that must not drift is the inventory leg: it should read the same days-of-supply computation as S-03, not a second one.
- Schedule attainment now has a published fact, but not a metric-view model. The production pattern publishes
fct_production_schedule_performance, with the frozen schedule version stored beside completions. That removes the old modeling blocker; the next semantic-library release can now add the view against a named source contract rather than guessing. - First pass yield is newer than the current semantic bundle, and its blocker is upstream of modeling. A metric view can only encode first-pass logic over a fact that preserved every operation attempt — rework overwriting its original result is a landing-layer defect no semantic layer can repair. The dictionary entry names the data contract; a model can follow once a production-quality fact with attempt grain has a published shape to stand on.
- Supplier on-time delivery arrived after the current semantic bundle. The supplier-scorecard fact and its simulated reference build already carry original- and current-promise receipt counters. A future model can expose the original-promise rate directly; it should not be folded into the customer-order fulfillment view, because purchase receipts and customer deliveries are different facts with different ownership.
- Window measures are deliberately unused. The
window:mechanism would let the inventory measures resolve semi-additively on their own — a period-end value instead of a sum when the snapshot date is not grouped on. It is the right idea, it is still experimental, and it pins the YAML to the older 0.1 dialect. More to the point, it hides the semi-additivity from whoever reads the query next. The explicit date rule in S-03's instruction block does the same job in a form a person can audit. - Nothing here computes a benchmark. No measure compares your number to an industry figure, and the targets these facts carry are internal goals. Label them that way wherever they surface.
Everything on this page is free to copy, adapt, and ship — no attribution required, no email needed. If a model disagrees with the dictionary entry it claims to implement, the entry is the source of truth and the model is the bug.