Batch analytics

By defining features on top of analytics tables (DuckDB, Trino), FeatureMesh acts as a semantic layer for your data warehouse. Batch analytics is a workload profile, not a separate FeatureQL dialect: the same language also runs on DataFusion serving, while batch queries typically bind larger populations and optimize for throughput. Features compose across both profiles, and VARIANT() lets you test alternatives without duplicating logic.

How it works

CREATE FEATURES AS
PERSIST
    CUSTOMERS := ENTITY(),
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
    CURRENT_DATE := INPUT(DATE),

    TABLES.CUSTOMERS_HISTORY := EXTERNAL_COLUMNS(
        customer_id BIGINT#CUSTOMERS BIND TO CUSTOMER_ID,
        country_code VARCHAR(2),
        orders_details ARRAY(ROW(order_id BIGINT, amount DECIMAL, order_date DATE))
        FROM TABLE(analytics.customers_history)
    ),
    COUNTRY_CODE := TABLES.CUSTOMERS_HISTORY[country_code],
    ORDERS_DETAILS := TABLES.CUSTOMERS_HISTORY[orders_details]
;

Then define the features you need:

CREATE FEATURES AS
    PERSIST
    LIFETIME_VALUE := ARRAY_SUM(ORDERS_DETAILS[amount]),
    DAYS_SINCE_LAST_ORDER := DATE_SUBTRACT(CURRENT_DATE, ARRAY_MAX(ORDERS_DETAILS[order_date]), 'day'),
    AD_THRESHOLD := 90,
    CUSTOMER_SEGMENT := CASE
        WHEN DAYS_SINCE_LAST_ORDER < AD_THRESHOLD THEN
            CASE
                WHEN LIFETIME_VALUE > 10000 THEN 'VIP'
                WHEN LIFETIME_VALUE > 2000 THEN 'Premium'
                ELSE 'Standard'
            END
        ELSE 'Inactive'
    END
;

Now anyone can query these features from anywhere (Python, SQL clients, BI tools):

RETURN
    CUSTOMER_ID,
    CUSTOMER_SEGMENT
FOR
    CUSTOMER_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM customers WHERE country = 'US'))
;

FeatureQL transpiles to SQL that runs on your warehouse. No data movement, no new infrastructure. Inspect the generated SQL when a workload is expensive.

Composition is where it gets interesting

So far, this looks like what any semantic layer can do. The difference is what comes next.

Reuse customer segment to qualify orders. The hop goes through the order's customer key, and the sum is a feature with its own GROUP BY, not a query clause:

DEFINE
    ORDERS := ENTITY(),
    ORDER_ID := INPUT(BIGINT#ORDERS),
    TABLES.ORDERS := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        customer_id BIGINT#CUSTOMERS,
        amount DECIMAL
        FROM TABLE(analytics.orders)
    ),
    ORDER_CUSTOMER_ID := TABLES.ORDERS[customer_id],
    ORDER_AMOUNT := TABLES.ORDERS[amount],
    ORDER_SEGMENT := RELATED(CUSTOMER_SEGMENT VIA ORDER_CUSTOMER_ID),
RETURN
    ORDER_SEGMENT,
    REVENUE := SUM(ORDER_AMOUNT) GROUP BY (ORDER_SEGMENT)
FOR
    ORDER_ID := BIND_COLUMNS(order_id FROM TABLE(analytics.orders));

Create a variant to test a different activity threshold. The original CUSTOMER_SEGMENT stays unchanged:

DEFINE
    ORDERS := ENTITY(),
    ORDER_ID := INPUT(BIGINT#ORDERS),
    TABLES.ORDERS := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        customer_id BIGINT#CUSTOMERS,
        amount DECIMAL
        FROM TABLE(analytics.orders)
    ),
    ORDER_CUSTOMER_ID := TABLES.ORDERS[customer_id],
    ORDER_AMOUNT := TABLES.ORDERS[amount],
    ORDER_SEGMENT_30DAYS := VARIANT(
        RELATED(CUSTOMER_SEGMENT VIA ORDER_CUSTOMER_ID)
        REPLACING AD_THRESHOLD WITH 30
    ),
RETURN
    ORDER_SEGMENT_30DAYS,
    REVENUE := SUM(ORDER_AMOUNT) GROUP BY (ORDER_SEGMENT_30DAYS)
FOR
    ORDER_ID := BIND_COLUMNS(order_id FROM TABLE(analytics.orders));

Or make the threshold dynamic per country. COUNTRY_CODE is the customer feature persisted above:

DEFINE
    ORDERS := ENTITY(),
    ORDER_ID := INPUT(BIGINT#ORDERS),
    TABLES.ORDERS := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        customer_id BIGINT#CUSTOMERS,
        amount DECIMAL
        FROM TABLE(analytics.orders)
    ),
    ORDER_CUSTOMER_ID := TABLES.ORDERS[customer_id],
    ORDER_AMOUNT := TABLES.ORDERS[amount],
    AD_THRESHOLD_BY_COUNTRY := CASE
        WHEN COUNTRY_CODE = 'US' THEN 30
        WHEN COUNTRY_CODE = 'FR' THEN 60
        ELSE 90
    END,
    ORDER_SEGMENT_BY_COUNTRY := VARIANT(
        RELATED(CUSTOMER_SEGMENT VIA ORDER_CUSTOMER_ID)
        REPLACING AD_THRESHOLD WITH AD_THRESHOLD_BY_COUNTRY
    ),
RETURN
    ORDER_SEGMENT_BY_COUNTRY,
    REVENUE := SUM(ORDER_AMOUNT) GROUP BY (ORDER_SEGMENT_BY_COUNTRY)
FOR
    ORDER_ID := BIND_COLUMNS(order_id FROM TABLE(analytics.orders));

None of this required modifying the original CUSTOMER_SEGMENT definition. The three examples build on each other, reuse the same dependency graph, and would each take significantly more SQL to express. This depth of composition is what separates FeatureMesh from traditional semantic layers.

How it compares

Traditional semantic layers (LookML, Cube, dbt metrics) define metrics in YAML or config files. Several of them can derive a metric from another metric. The boundary is what can be composed: usually metrics, under that system's aggregation rules. FeatureMesh names the dependency, so a later feature can call an earlier one, replace one input with VARIANT(), or cross an entity boundary with RELATED().

FeatureMesh also differs in how it validates changes. The registry checks type compatibility, dependency existence, and consistency on every mutation. You cannot persist a feature that would break something downstream. Traditional semantic layers rely on CI/CD and code review to catch these errors; FeatureMesh catches them at the language level.

Adoption is gradual. FeatureMesh works alongside your existing SQL. Start with one feature, add more over time.

Last update at: 2026/10/13 08:32:21

On this page

How it works Composition is where it gets interesting How it compares