Our engine never sees your warehouse password
datahashi

BlogModeling & metrics

E-commerce category tree revenue: safe hierarchical rollups

How retail merchandisers drill down SKU, category, and department revenue without fan-out bugs in a thin semantic layer.

The datahashi team8 min read

The problem

A retail merchandising team wants to analyze revenue contributions across their catalog hierarchy. The lead merchandiser looks at an executive dashboard showing 3320 in gross revenue across all sales in this sample data. But when an operations analyst builds a drill-down report joining product tags, the department total balloons into an impossible number.

The issue stems from taxonomy joins. E-commerce catalogs frequently map single products into multiple promotional tags or overlapping marketing categories. When analysts join order line items to multi-level catalog tables using ad-hoc SQL, rows duplicate. A pair of running shoes linked to both “Footwear” and “Athletic Clearance” produces duplicate line-item records. The resulting calculation double-counts revenue and misleads inventory replenishment decisions.

Merchandisers lose hours cross-referencing spreadsheet exports against warehouse database tables. Inventory planners over-order stock based on inflated category totals, while finance teams reject merchandising commission reports. When separate teams write slightly different join conditions, the numbers drift apart across retail departments.

Merchandisers need a predictable drill path from department to category to individual SKU. That drill path must maintain exact mathematical consistency across every parent and child tier. You need a semantic model that formalizes governed hierarchies and enforces fan-out-safe Join Graph traversals.

+--------------------------------------------------------------+
| Merchandiser asks: "Show revenue by department and category" |
+--------------------------------------------------------------+
                               |
               Unsafe Ad-hoc Tag Joins ✗
                               |
     Many-to-many catalog tags duplicate line-item rows!
        Department revenue balloons and double-counts ✗
                               |
          Governed Semantic Hierarchy Model ✓
                               |
     Explicit 1-to-many drill path: Department -> Category -> SKU
        Parent totals equal sum of child levels (3320) ✓

The data

The e-commerce warehouse structures catalog taxonomy across three relational tables. The categories table defines retail departments and specific merchandise categories. The products table maps individual SKUs to their primary category. The order_items table records purchased quantities and unit prices.

CREATE TABLE categories (
    category_id VARCHAR PRIMARY KEY,
    category_name VARCHAR NOT NULL,
    department VARCHAR NOT NULL
);

CREATE TABLE products (
    product_id VARCHAR PRIMARY KEY,
    sku VARCHAR NOT NULL,
    product_name VARCHAR NOT NULL,
    category_id VARCHAR NOT NULL REFERENCES categories(category_id)
);

CREATE TABLE order_items (
    item_id VARCHAR PRIMARY KEY,
    order_id VARCHAR NOT NULL,
    product_id VARCHAR NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL,
    unit_price DOUBLE NOT NULL
);

INSERT INTO categories VALUES
    ('cat_1', 'Footwear', 'Apparel'),
    ('cat_2', 'Outerwear', 'Apparel'),
    ('cat_3', 'Laptops', 'Electronics'),
    ('cat_4', 'Audio', 'Electronics');

INSERT INTO products VALUES
    ('p_1', 'RUN-001', 'Road Running Shoe', 'cat_1'),
    ('p_2', 'TRL-002', 'Trail Running Shoe', 'cat_1'),
    ('p_3', 'JKT-003', 'Rain Jacket', 'cat_2'),
    ('p_4', 'LAP-004', 'Pro Laptop 16', 'cat_3'),
    ('p_5', 'EAR-005', 'Wireless Earbuds', 'cat_4');

INSERT INTO order_items VALUES
    ('oi_1', 'ord_1', 'p_1', 2, 120.0),
    ('oi_2', 'ord_1', 'p_3', 1, 180.0),
    ('oi_3', 'ord_2', 'p_2', 1, 140.0),
    ('oi_4', 'ord_2', 'p_4', 1, 1200.0),
    ('oi_5', 'ord_3', 'p_5', 2, 80.0),
    ('oi_6', 'ord_4', 'p_1', 1, 120.0),
    ('oi_7', 'ord_4', 'p_4', 1, 1200.0),
    ('oi_8', 'ord_5', 'p_5', 1, 80.0);

The relationship graph links purchased order items up through products to categories:

+-------------+             +----------+             +------------+
| order_items |             | products |             | categories |
+-------------+             +----------+             +------------+
| item_id PK  |             | prod_id  |             | cat_id PK  |
| prod_id FK -+------------>| sku      |             | cat_name   |
| quantity    |   many_to_1 | cat_id --+------------>| department |
| unit_price  |             +----------+   many_to_1 +------------+
+-------------+

The model

The semantic model declares three entities, their foreign key relationships, a gross revenue metric, and a drill-down hierarchy named merchandise_hierarchy.

The hierarchy configures a three-tiered drill path: department, then category_name, then sku. The model compiler validates that every level in the hierarchy corresponds to an accessible dimension along the Join Graph.

source: warehouse_main

entities:
  - name: Category
    table: categories
    primary_key: category_id
    dimensions:
      - name: category_id
        column: category_id
        type: string
      - name: category_name
        column: category_name
        type: string
      - name: department
        column: department
        type: string

  - name: Product
    table: products
    primary_key: product_id
    dimensions:
      - name: product_id
        column: product_id
        type: string
      - name: sku
        column: sku
        type: string
      - name: product_name
        column: product_name
        type: string
      - name: category_id
        column: category_id
        type: string

  - name: OrderItem
    table: order_items
    primary_key: item_id
    dimensions:
      - name: item_id
        column: item_id
        type: string
      - name: order_id
        column: order_id
        type: string
      - name: product_id
        column: product_id
        type: string
      - name: quantity
        column: quantity
        type: int
      - name: unit_price
        column: unit_price
        type: float
    measures:
      - name: revenue
        expr: quantity * unit_price
        agg: sum

relationships:
  - name: item_product
    from: OrderItem
    to: Product
    cardinality: many_to_one
    keys:
      - [product_id, product_id]

  - name: product_category
    from: Product
    to: Category
    cardinality: many_to_one
    keys:
      - [category_id, category_id]

metrics:
  - name: gross_revenue
    expr: revenue
    description: "Total recognized product sales revenue"
    unit: USD
    format: currency
  - name: total_units
    expr: units_sold
    description: "Total merchandise units sold"

hierarchies:
  - name: merchandise_taxonomy
    description: "Department to Category to SKU hierarchy"
    levels:
      - {dimension: department}
      - {dimension: category_name}
      - {dimension: sku}

Notice how merchandise_taxonomy structures the catalog. The root level begins at department on Category, advances to category_name, and terminates at sku on Product. Front-end applications and dashboards navigate this hierarchy without hardcoding custom SQL breadcrumbs.

The hierarchy declaration spans multiple related entities. department and category_name reside on the Category entity, while sku resides on Product. The model compiler uses the product_category relationship to join these entities seamlessly during expansion queries. Client developers do not need to construct recursive CTEs or manage foreign key joins when requesting drill-down paths.

Fan-out protection is maintained across every level of the tree. When querying gross_revenue at the department level, the engine aggregates revenue from OrderItem up through Product to Category. Because the relationship cardinality is many-to-one (OrderItem to Product, and Product to Category), line item amounts are never duplicated or inflated. Revenue sums match accounting totals regardless of which level you inspect.

Consult the model specification for full syntax details on hierarchies.

The queries

Clients submit structured queries defined in the query specification. datahashi has both a dedicated hierarchy expansion endpoint and standard semantic query execution. Where a request sets include_sql, the engine returns compiled SQL only if it was started with ENGINE_ALLOW_SQL=true. MCP never returns SQL.

Query 1: Expanding the top-level hierarchy tier

To populate an interactive merchandising tree, frontends call POST /v1/hierarchies/expand with an empty path:

{
  "hierarchy": "merchandise_taxonomy",
  "path": [],
  "metrics": ["gross_revenue", "total_units"]
}

The engine inspects the hierarchy definition and returns department level summaries:

department gross_revenue total_units
Apparel 680 5
Electronics 2640 5

The user interface renders “Apparel” and “Electronics” as top-level nodes in the navigation tree.

Query 2: Drilling down into the Apparel department

When a merchandiser clicks on “Apparel”, the client calls POST /v1/hierarchies/expand passing the selected path:

{
  "hierarchy": "merchandise_taxonomy",
  "path": ["Apparel"],
  "metrics": ["gross_revenue", "total_units"]
}

The server returns the child categories belonging to Apparel:

category_name gross_revenue total_units
Footwear 500 4
Outerwear 180 1

Query 3: Multi-level hero revenue report

To display the complete catalog breakdown, the dashboard queries gross_revenue grouped across all three hierarchy tiers:

{
  "metrics": ["gross_revenue"],
  "dimensions": [
    {
      "name": "department",
      "entity": "Category"
    },
    {
      "name": "category_name",
      "entity": "Category"
    },
    {
      "name": "sku",
      "entity": "Product"
    }
  ],
  "order": [
    { "ref": "department", "desc": false },
    { "ref": "category_name", "desc": false },
    { "ref": "sku", "desc": false }
  ],
  "include_sql": true
}

The result table details individual SKU contributions without fan-out multiplication:

department category_name sku gross_revenue
Apparel Footwear RUN-001 360
Apparel Footwear TRL-002 140
Apparel Outerwear JKT-003 180
Electronics Audio EAR-005 240
Electronics Laptops LAP-004 2400

Here is the compiled SQL generated by datahashi:

SELECT
  q0."department" AS "department",
  q0."category_name" AS "category_name",
  q0."sku" AS "sku",
  CAST(q0."revenue" AS DOUBLE) AS "gross_revenue"
FROM (
  SELECT
    "Category"."department" AS "department",
    "Category"."category_name" AS "category_name",
    "Product"."sku" AS "sku",
    CAST(SUM(quantity * unit_price) AS DOUBLE) AS "revenue"
  FROM "order_items" AS "OrderItem"
  JOIN "products" AS "Product"
    ON "OrderItem"."product_id" = "Product"."product_id"
  JOIN "categories" AS "Category"
    ON "Product"."category_id" = "Category"."category_id"
  GROUP BY
    "Category"."department",
    "Category"."category_name",
    "Product"."sku"
) AS q0
ORDER BY "department", "category_name", "sku"

Notice that Apparel sub-totals (360 + 140 + 180 = 680) and Electronics sub-totals (240 + 2400 = 2640) sum exactly to the 3320 total in this sample data. No line items were duplicated.

Query 4: Refusal on nonexistent hierarchy

If an AI agent or dashboard requests an undeclared drill path such as brand_hierarchy, the engine halts execution with HTTP 400:

{
  "hierarchy": "brand_hierarchy",
  "path": []
}

The server returns:

{
  "error": "the model has no hierarchy named \"brand_hierarchy\"",
  "code": "invalid",
  "reason": "bad_request"
}

What to notice

  • Additive drill-down integrity. Category totals sum up precisely to parent department numbers. The engine prevents rounding anomalies and duplicate record inflation. Merchandisers trust that revenue totals match at every level of the hierarchy tree.
  • Line-item grain preservation. Joining order_items across products and categories avoids duplicate row expansion because cardinalities are declared explicitly. The engine guarantees fan-out safety even when multiple catalog dimension entities are referenced.
  • Dedicated tree expansion endpoint. The POST /v1/hierarchies/expand endpoint simplifies front-end navigation. BI tools and UI trees pass the current drill breadcrumb path without writing custom SQL predicates or maintaining manual lookup caches.
  • Permission inheritance. Catalog discovery remains secure. Callers are granted a hierarchy only when their role grants every dimension level within that hierarchy. Hidden tiers cannot be bypassed through child navigation.
  • Query Runner credential safety. The Query Runner pushes down aggregation safely. Calculations run directly on the underlying warehouse engine while database connection secrets remain private.

For foundational modeling techniques, see the guide on building your first metric in DuckDB and the post on fan-out safe metrics.

Limits

  • Percent of total. percent_of_total on a query returns each selected additive measure again as <name>_share_of_total, a 0–1 share. The measure must be selected and additive. A ratio metric is still the right tool when the share is not “this row over the total of the same measure.”
  • Strict tree structure requirement. Hierarchies model strict tree structures. Complex many-to-many product categorization graphs require bridge tables collapsed upstream in warehouse transformations.
  • Pre-flattened levels. Recursive self-referential category parent-child trees must be flattened into discrete level columns before modeling.
  • Adjacent drill sequences. The compiler respects declared drill levels. Skipping non-adjacent intermediate tiers requires explicit dimensional queries.
  • Warehouse transformation scope. The semantic layer does not reconcile product returns or adjust inventory ledger counts. Retail transaction transformations remain upstream warehouse responsibilities.

Product notes

An occasional email when we ship something or write something worth reading.