FeatureQL Business logic as executable formulas

FeatureQL is a formula language for business logic that humans and AI can read, write, and execute unambiguously.

It uses familiar SQL syntax, but you write formulas on columns, not queries on tables.

pip install featuremesh

Includes local registry and serving backend. No account required.

Why use FeatureQL?

Business logic tends to spread across queries, models, and services. FeatureQL gives each calculation a name, a type, and explicit dependencies.

Declarative

Specify what, not how. No boilerplate. Logic is decoupled from storage or optimization.

→ Readable by non-engineers as specs.

Pure functions

Deterministic transformations, no side effects.

→ Unit-testable, safe to refactor, predictable.

Composable

Similar to spreadsheets where cells reference other cells, features reference other features.

→ Single source of truth, no duplication.

Entity-centric

Think in customers and orders, not joins and tables.

→ Business concepts map directly to code.

Semantically typed entities

CUSTOMER_ID ≠ ORDER_ID, even if both are BIGINT.

→ Join bugs caught at compile time.

Universal execution

Transpiles to multiple SQL dialects (batch) or DataFusion (serving).

→ No skew between training and serving.

FeatureQL vs SQL

Data engineers define foundational features once.
Everyone else composes on top of them.

taxis.dim_zones
┌─────────┬────────────────┬───────────┐
│ zone_id │ name           │ borough   │
├─────────┼────────────────┼───────────┤
│ 100     │ Midtown        │ Manhattan │
│ 101     │ Downtown       │ Brooklyn  │
│ 102     │ Astoria        │ Queens    │
│ 103     │ Yankee Stadium │ Bronx     │
└─────────┴────────────────┴───────────┘

taxis.fct_trips
┌─────────┬───────────────┬─────────┬─────────────────────┐
│ trip_id │ trip_zone_id  │ fare    │ dropoff_at          │
├─────────┼───────────────┼─────────┼─────────────────────┤
│ 1001    │ 100           │ 450.00  │ 2025-06-01 08:00:00 │
│ 1002    │ 100           │ 380.00  │ 2025-07-15 14:45:00 │
│ 1003    │ 101           │ 600.00  │ 2025-08-20 17:30:00 │
│ 1004    │ 101           │ 550.00  │ 2025-10-05 16:20:00 │
│ 1005    │ 100           │ 520.00  │ 2025-11-12 11:30:00 │
│ 1006    │ 102           │ 1200.00 │ 2025-11-15 14:30:00 │
│ 1007    │ 102           │ 300.00  │ 2026-01-20 10:00:00 │
│ 1008    │ 101           │ 400.00  │ 2025-12-25 09:45:00 │
│ 1009    │ 103           │ 850.00  │ 2026-01-10 12:00:00 │
│ 1010    │ 103           │ 400.00  │ 2026-01-28 09:30:00 │
└─────────┴───────────────┴─────────┴─────────────────────┘

taxis.agg_zones_obt
┌─────────┬──────────────┬─────────────────────┐
│ zone_id │ last_trip_id │ trips               │
├─────────┼──────────────┼─────────────────────┤
│ 100     │ 1005         │ [1001, 1002, 1005]  │
│ 101     │ 1008         │ [1003, 1004, 1008]  │
│ 102     │ 1007         │ [1006, 1007]        │
│ 103     │ 1010         │ [1009, 1010]        │
└─────────┴──────────────┴─────────────────────┘

CREATE OR REPLACE FEATURES IN fm.taxis AS
SELECT
    -- Entities
    zones := ENTITY(),
    trips := ENTITY(),

    -- Primary keys
    zone_id := INPUT(BIGINT#zones),
    trip_id := INPUT(BIGINT#trips),

    -- Zone dimensions
    tables.dim_zones := EXTERNAL_COLUMNS(
        zone_id BIGINT#zones BIND TO zone_id,
        name VARCHAR,
        borough VARCHAR
        FROM TABLE(taxis.dim_zones)
    ),
    zone_name := tables.dim_zones[name],

    -- Trip facts
    tables.fct_trips := EXTERNAL_COLUMNS(
        trip_id BIGINT#trips BIND TO trip_id,
        trip_zone_id BIGINT#zones,
        fare DECIMAL,
        dropoff_at TIMESTAMP
        FROM TABLE(taxis.fct_trips)
    ),
    trip_fare := tables.fct_trips[fare],
    trip_zone_id := tables.fct_trips[trip_zone_id],
    trip_dropoff_at := tables.fct_trips[dropoff_at],

    -- Zone facts aggregations (OBT)
    tables.agg_zones_obt := EXTERNAL_COLUMNS(
        zone_id BIGINT#zones BIND TO zone_id,
        last_trip_id BIGINT#trips,
        trips ARRAY(BIGINT#trips),
        FROM TABLE(taxis.agg_zones_obt)
    ),
    last_trip_id := tables.agg_zones_obt[last_trip_id],
    zone_trips := tables.agg_zones_obt[trips],

    -- Keysets: Define where to find the keys for each entity.
    dim_zones_keyset := KEYSET(
        'all', zones,
        'SELECT zone_id AS "fm.taxis.zone_id"
         FROM taxis.dim_zones'
    ),
    fct_trips_keyset := KEYSET(
        'all', trips,
        'SELECT trip_id AS "fm.taxis.trip_id"
         FROM taxis.fct_trips'
    ),
;

This maps your tables to reusable features. Features like zone_name, trip_fare, or zone_trips are defined once and used everywhere.

Zone fare total

Sum trip fares per pickup zone from relational data

FeatureQL

SELECT
    zone_id,
    zone_fare_total := zone_id.RELATED(
        SUM(trip_fare)
        GROUP BY trip_zone_id
    ),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(all, zones),
    trip_id := @BIND_KEYSET(all, trips),
;

Hand-written equivalent SQL

WITH trips_agg AS (
    SELECT
        trip_zone_id,
        SUM(fare) AS zone_fare_total
    FROM taxis.fct_trips
    GROUP BY trip_zone_id
)
SELECT
    z.zone_id,
    t.zone_fare_total
FROM taxis.dim_zones z
LEFT JOIN trips_agg t
    ON z.zone_id = t.trip_zone_id
;

GROUP BY then LEFT JOIN in one operation: RELATED(... GROUP BY ...)

Result Same output from both queries

zone_id	zone_fare_total
100	1350.00
101	1550.00
102	1500.00
103	1250.00

Zone fare from array

Compute the same zone fare total from an array of trip ids found in a "One Big Table" (OBT) or key value store

FeatureQL

WITH
    zone_trips_details := EXTEND(
        ZIP(zone_trips AS trip_id)
        WITH trip_fare AS trip_fare
        VIA trip_id BIND TO trip_id
    )
SELECT
    zone_id,
    zone_fare_total_arr := ARRAY_SUM(zone_trips_details[trip_fare]),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(all, zones),
;

Hand-written equivalent SQL

WITH trips_unnested AS (
    SELECT
        z.zone_id,
        UNNEST(z.trips) AS trip_id
    FROM taxis.agg_zones_obt z
),
trips_with_fare AS (
    SELECT
        u.zone_id,
        t.fare
    FROM trips_unnested u
    LEFT JOIN taxis.fct_trips t
        ON u.trip_id = t.trip_id
)
SELECT
    zone_id,
    SUM(fare) AS zone_fare_total_arr
FROM trips_with_fare
GROUP BY zone_id
;

Join on arrays of foreign keys using ZIP() and EXTEND()

Result Same output from both queries

zone_id	zone_fare_total_arr
100	1350.00
101	1550.00
102	1500.00
103	1250.00

Priority zone

Flag high-fare zones with recent trips for driver positioning

FeatureQL

SELECT
    zone_id,
    zone_fare_total,
    recency := DATE_SUBTRACT(
        TIMESTAMP '2026-02-01',
        last_trip_id.RELATED(trip_dropoff_at),
        'day'
    ),
    flag_priority_zone :=
        recency <= 7 AND zone_fare_total > 1000::DECIMAL,
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(all, zones),
    trip_id := @BIND_KEYSET(all, trips),
;

Hand-written equivalent SQL

WITH zone_fare_total AS (
    SELECT
        trip_zone_id,
        SUM(fare) AS zone_fare_total
    FROM taxis.fct_trips
    GROUP BY trip_zone_id
)
SELECT
    z.zone_id,
    fare.zone_fare_total,
    DATE_DIFF('day', CAST(t.dropoff_at AS DATE), DATE '2026-02-01') AS recency,
    DATE_DIFF('day', CAST(t.dropoff_at AS DATE), DATE '2026-02-01') <= 7
        AND fare.zone_fare_total > 1000.00 AS flag_priority_zone
FROM taxis.dim_zones z
LEFT JOIN taxis.agg_zones_obt agg
    ON z.zone_id = agg.zone_id
LEFT JOIN taxis.fct_trips t
    ON agg.last_trip_id = t.trip_id
LEFT JOIN zone_fare_total fare
    ON z.zone_id = fare.trip_zone_id
;

Combine multiple aggregations and lookups in a single query

Result Same output from both queries

zone_id	zone_fare_total	recency	flag_priority_zone
100	1350.00	81	false
101	1550.00	38	false
102	1500.00	12	false
103	1250.00	4	true

Priority A/B experiment

Parameterize the active window and bucket zones with HASH01()

FeatureQL

WITH
    threshold := INPUT(BIGINT),
    flag_priority_zone_macro := MACRO(
        recency <= threshold AND zone_fare_total > 1000.
        USING threshold
    ),
    flag_priority_zone_control := flag_priority_zone_macro(7),
    flag_priority_zone_test := flag_priority_zone_macro(14),
    expose_to_test :=
        HASH01(UNSAFE_CAST(zone_id AS VARCHAR) || 'SALT')
        BETWEEN.5 0e0 AND 0.5e0, -- .5 sets comparison precision
SELECT
    zone_id,
    flag_priority_zone_control,
    expose_to_test,
    flag_priority_zone := CASE
        WHEN expose_to_test THEN flag_priority_zone_test
        ELSE flag_priority_zone_control
    END,
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(all, zones),
    trip_id := @BIND_KEYSET(all, trips),
;

Hand-written equivalent SQL

WITH zone_fare_total AS (
    SELECT
        trip_zone_id,
        SUM(fare) AS zone_fare_total
    FROM taxis.fct_trips
    GROUP BY trip_zone_id
),
zone_recency AS (
    SELECT
        z.zone_id,
        DATE_DIFF('day', t.dropoff_at, DATE '2026-02-01') AS recency
    FROM taxis.dim_zones z
    LEFT JOIN taxis.agg_zones_obt agg
        ON z.zone_id = agg.zone_id
    LEFT JOIN taxis.fct_trips t
        ON agg.last_trip_id = t.trip_id
)
SELECT
    z.zone_id,
    r.recency <= 7
        AND fare.zone_fare_total > 1000.00 AS flag_priority_zone_control,
    CAST(
        CAST(
            ('0x' || SUBSTRING(
                MD5(ARRAY_TO_STRING(
                    ARRAY[z.zone_id::VARCHAR, 'SALT'], ''
                )), 1, 15
            )) AS BIGINT
        ) AS DOUBLE
    ) / POW(2, 60) BETWEEN 0 AND 0.50 AS expose_to_test,
    CASE
        WHEN expose_to_test
            THEN (r.recency <= 14 AND fare.zone_fare_total > 1000.00)
        ELSE (r.recency <= 7 AND fare.zone_fare_total > 1000.00)
    END AS flag_priority_zone
FROM taxis.dim_zones z
LEFT JOIN zone_recency r
    ON z.zone_id = r.zone_id
LEFT JOIN zone_fare_total fare
    ON z.zone_id = fare.trip_zone_id
;

MACRO() parametrizes features by replacing dependencies with inputs

Result Same output from both queries

zone_id	flag_priority_zone_control	expose_to_test	flag_priority_zone
100	false	false	false
101	false	false	false
102	false	true	true
103	true	true	true

Rejected at compile time

Both columns are BIGINT. SQL runs this and returns a result. FeatureQL rejects it before execution.

FeatureQL

WITH
    customers := ENTITY(),
    orders := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    order_id := INPUT(BIGINT#orders)
SELECT
    wrong_lookup := customer_id.RELATED(order_id)
;

SQL that would run

SELECT
    c.customer_id,
    o.order_id
FROM customers c
JOIN orders o
    ON c.customer_id = o.order_id
;

Compiler error

UE/RELATED-ENTITY-MISMATCH
RELATED() VIA … requires the same entity family on both sides when entity annotations are present; field 'CUSTOMER_ID' has entity family 'CUSTOMERS' on the base side but entity family 'ORDERS' on the bound side when defining feature WRONG_LOOKUP.

FeatureMesh: registry, governance, serving

FeatureQL is the language. FeatureMesh is the platform that stores features, validates changes against their dependents, and serves them at request latency.

A semantic layer for analytics

By defining features on top of analytics tables, FeatureMesh becomes a semantic layer for your data analytics and BI.

Define your business metrics once in FeatureQL and serve it to every BI tool without drift.

A feature platform for production

By defining features on top of operational data sources, FeatureMesh becomes a feature platform for production APIs and online ML inference.

Low latency serving without discrepancy between training and serving.

An executable memory for AI Agents

An agent works out a calculation once and persist it in the registry. Every later call reuses that definition instead of re-deriving it, so the answer is the same every time.

LLMs read, write, and compose features the same way your team does.

Try FeatureQL

Give your LLM the reference docs.featuremesh.com/llms.txt before asking it to write or review FeatureQL.

Try in interactive docs