FeatureQL Formula language for business logic

AI writes your business logic as small, composable formulas.
The compiler checks that it is consistent. You check that it is right.

Define once, use in analytics, ML, and production systems.

Try in interactive docs

Runs FeatureQL queries in your browser. Nothing to install.

or

pip install featuremesh

Includes local registry and serving backend. No account required.

Why use FeatureQL?

No more business logic expressed in different languages across queries, models, and services.
FeatureQL unifies calculations with a unique name, semantic 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, cacheable, safe to refactor.

Composable

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

→ Single source of truth, no duplication.

Entity-centric

Think in terms of customers and orders, not joins and tables.

→ Business concepts map directly to code.

Semantic types

CUSTOMER_ID ≠ ORDER_ID, even if both are BIGINT. Dollars can't be added to cents.

→ Semantic errors caught at compile time.

Universal execution

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

→ No skew between training and serving.

FeatureQL abstracts SQL

FeatureQL applies to clean tables with keys (gold layer).
Data engineers define core features once.
Everyone else composes on top.

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  │ tolls_cents │ dropoff_at          │
├─────────┼──────────────┼───────┼─────────────┼─────────────────────┤
│ 1001    │ 100          │ 42.50 │ 0           │ 2025-06-01 08:00:00 │
│ 1002    │ 100          │ 38.00 │ 0           │ 2025-07-15 14:45:00 │
│ 1003    │ 101          │ 18.50 │ 0           │ 2025-08-20 17:30:00 │
│ 1004    │ 101          │ 24.00 │ 0           │ 2025-10-05 16:20:00 │
│ 1005    │ 100          │ 52.00 │ 0           │ 2025-11-12 11:30:00 │
│ 1006    │ 102          │ 70.00 │ 694         │ 2025-11-15 14:30:00 │
│ 1007    │ 102          │ 45.50 │ 694         │ 2026-01-21 10:00:00 │
│ 1008    │ 101          │ 21.50 │ 0           │ 2026-01-30 09:45:00 │
│ 1009    │ 103          │ 58.00 │ 300         │ 2026-01-10 12:00:00 │
│ 1010    │ 103          │ 61.50 │ 0           │ 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
PERSIST
    -- Entities
    zones := ENTITY(),
    trips := ENTITY(),

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

    -- Zones
    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],

    -- Trips
    tables.fct_trips := EXTERNAL_COLUMNS(
        trip_id BIGINT#trips BIND TO trip_id,
        trip_zone_id BIGINT#zones,
        fare DECIMAL@CURRENCY.USD,
        tolls_cents BIGINT@CURRENCY.USD_CENT,
        dropoff_at TIMESTAMP
        FROM TABLE(taxis.fct_trips)
    ),
    trip_zone_id := tables.fct_trips[trip_zone_id],
    trip_fare := tables.fct_trips[fare],
    trip_tolls := tables.fct_trips[tolls_cents],
    trip_dropoff_at := tables.fct_trips[dropoff_at],

    -- Denormalized zones
    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: where to find the keys of each entity
    dim_zones_keyset := KEYSET(
        zones,
        'SELECT zone_id AS "fm.taxis.zone_id"
         FROM taxis.dim_zones'
    ),
    fct_trips_keyset := KEYSET(
        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 zone from the fact table

FeatureQL
RETURN
    zone_id,
    zone_name,
    zone_fare_total := zone_id.RELATED(
        SUM(trip_fare)
        GROUP BY (trip_zone_id)
    ),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
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,
    z.name AS zone_name,
    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_idzone_namezone_fare_total
100Midtown132.50
101Downtown64.00
102Astoria115.50
103Yankee Stadium119.50

Zone fare from array

Compute the same zone fare total from an array of trip ids

FeatureQL
DEFINE
    zone_trips_details := EXTEND(
        ZIP(zone_trips AS trip_id)
        WITH trip_fare AS trip_fare
        VIA trip_id BIND TO trip_id
    )
RETURN
    zone_id,
    zone_name,
    zone_fare_total_arr := ARRAY_SUM(zone_trips_details[trip_fare]),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
;
Hand-written equivalent SQL
WITH trips_unnested AS (
    SELECT
        obt.zone_id,
        UNNEST(obt.trips) AS trip_id
    FROM taxis.agg_zones_obt obt
),
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
    z.zone_id,
    z.name AS zone_name,
    SUM(f.fare) AS zone_fare_total_arr
FROM taxis.dim_zones z
LEFT JOIN trips_with_fare f
    ON z.zone_id = f.zone_id
GROUP BY z.zone_id, z.name
;
Join on arrays of foreign keys using ZIP() and EXTEND()
Result Same output from both queries
zone_idzone_namezone_fare_total_arr
100Midtown132.50
101Downtown64.00
102Astoria115.50
103Yankee Stadium119.50

Priority zone

Flag zones above $100 in fares with a trip in the last 7 days

FeatureQL
RETURN
    zone_id,
    zone_name,
    zone_fare_total,
    recency := DATE_DIFF(
        TIMESTAMP '2026-02-01',
        last_trip_id.RELATED(trip_dropoff_at),
        'day'
    ),
    flag_priority_zone :=
        recency <= 7
        AND zone_fare_total > CAST(100 AS _@CURRENCY.USD),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
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,
    z.name AS zone_name,
    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 > 100.00 AS flag_priority_zone
FROM taxis.dim_zones z
LEFT JOIN taxis.agg_zones_obt obt
    ON z.zone_id = obt.zone_id
LEFT JOIN taxis.fct_trips t
    ON obt.last_trip_id = t.trip_id
LEFT JOIN zone_fare_total fare
    ON z.zone_id = fare.trip_zone_id
;
Combine a persisted aggregate and a typed lookup in one decision rule
Result Same output from both queries
zone_idzone_namezone_fare_totalrecencyflag_priority_zone
100Midtown132.5081false
101Downtown64.002false
102Astoria115.5011false
103Yankee Stadium119.504true

Priority A/B experiment

Compare a 7-day window with a 14-day one without copying the rule

FeatureQL
DEFINE
    threshold := INPUT(BIGINT),
    flag_priority_zone_macro := MACRO(
        recency <= threshold
        AND zone_fare_total > CAST(100 AS _@CURRENCY.USD)
        USING threshold
    ),
    flag_priority_zone_control := flag_priority_zone_macro(7),
    flag_priority_zone_test := flag_priority_zone_macro(14),
    expose_to_test := SAMPLE_BOOL(
        CAST(KEY_TO_SCALAR(zone_id) AS VARCHAR) || 'SALT',
        0.5e0
    ),
RETURN
    zone_id,
    zone_name,
    recency,
    expose_to_test,
    flag_priority_zone_control,
    flag_priority_zone := IF(
        expose_to_test,
        flag_priority_zone_test,
        flag_priority_zone_control
    ),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
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
        obt.zone_id,
        DATE_DIFF('day', CAST(t.dropoff_at AS DATE), DATE '2026-02-01') AS recency
    FROM taxis.agg_zones_obt obt
    LEFT JOIN taxis.fct_trips t
        ON obt.last_trip_id = t.trip_id
),
zone_exposure AS (
    SELECT
        zone_id,
        CAST(
            CAST(
                ('0x' || SUBSTRING(MD5(zone_id::VARCHAR || 'SALT'), 1, 15)) AS BIGINT
            ) AS DOUBLE
        ) / POW(2, 60) < 0.5 AS expose_to_test
    FROM taxis.dim_zones
)
SELECT
    z.zone_id,
    z.name AS zone_name,
    r.recency,
    e.expose_to_test,
    r.recency <= 7
        AND fare.zone_fare_total > 100.00 AS flag_priority_zone_control,
    CASE
        WHEN e.expose_to_test
            THEN (r.recency <= 14 AND fare.zone_fare_total > 100.00)
        ELSE (r.recency <= 7 AND fare.zone_fare_total > 100.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
LEFT JOIN zone_exposure e
    ON z.zone_id = e.zone_id
;
MACRO() names the shared rule once; SAMPLE_BOOL() assigns each zone to an arm
Result Same output from both queries
zone_idzone_namerecencyexpose_to_testflag_priority_zone_controlflag_priority_zone
100Midtown81falsefalsefalse
101Downtown2falsefalsefalse
102Astoria11truefalsetrue
103Yankee Stadium4truetruetrue

FeatureQL extends SQL

Mix both in one query: SQL around FeatureQL for reporting, SQL inside FeatureQL for sources

SQL around FeatureQL
SELECT
    COALESCE(borough, 'All boroughs') AS borough,
    COUNT(*) AS zones,
    SUM(fare_total) AS fare_total
FROM FEATUREQL(
    RETURN
        borough := tables.dim_zones[borough],
        fare_total := zone_fare_total,
    FROM fm.taxis
    FOR
        zone_id := @BIND_KEYSET(dim_zones_keyset),
        trip_id := @BIND_KEYSET(fct_trips_keyset),
)
GROUP BY ROLLUP (borough)
ORDER BY GROUPING(borough), borough
;
SQL inside FeatureQL
DEFINE
    tables.zone_activity := EXTERNAL_COLUMNS(
        zone_id BIGINT#zones BIND TO zone_id,
        trips_2026 BIGINT
        FROM SQL(
            SELECT
                trip_zone_id AS zone_id,
                COUNT(*) AS trips_2026
            FROM taxis.fct_trips
            WHERE dropoff_at >= TIMESTAMP '2026-01-01'
            GROUP BY trip_zone_id
        )
    ),
    zone_trips_2026 := COALESCE(tables.zone_activity[trips_2026], 0),
RETURN
    zone_id,
    zone_name,
    zone_fare_total,
    zone_trips_2026,
    is_active_2026 := zone_trips_2026 > 0,
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
FEATUREQL(...) is a table in any SQL query; FROM SQL(...) turns any keyed query into a source
Result: SQL around FeatureQL FeatureQL computes the features, SQL adds the rollup
boroughzonesfare_total
Bronx1119.50
Brooklyn164.00
Manhattan1132.50
Queens1115.50
All boroughs4431.50
Result: SQL inside FeatureQL A keyed SQL query becomes a source that features compose with
zone_idzone_namezone_fare_totalzone_trips_2026is_active_2026
100Midtown132.500false
101Downtown64.001true
102Astoria115.501true
103Yankee Stadium119.502true

Additional safety

SQL runs these and returns a wrong result.
FeatureQL rejects them before execution.

Entities

trip_id and zone_id are both integers. SQL joins them and returns a NULL name for every trip. FeatureQL refuses a trip key looking up a zone feature.

FeatureQL Try yourself →
RETURN
    trip_id,
    trip_zone_name := trip_id.RELATED(zone_name),
FROM fm.taxis
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
SQL that would run
SELECT
    t.trip_id,
    z.name AS trip_zone_name
FROM taxis.fct_trips t
LEFT JOIN taxis.dim_zones z
    ON t.trip_id = z.zone_id
;
Compiler error
UE/RELATED-ENTITY-MISMATCH
RELATED() VIA ... requires the same entity family on both sides when entity annotations are present; field 'FM.TAXIS.TRIP_ID' has entity family 'FM.TAXIS.TRIPS' on the base side but entity family 'FM.TAXIS.ZONES' on the bound side when defining feature TRIP_ZONE_NAME.

Units

Fares are dollars and tolls are cents. SQL adds them and returns 764.00 for trip 1006. The real revenue is 76.94. FeatureQL refuses the addition.

FeatureQL Try yourself →
RETURN
    trip_id,
    trip_revenue := trip_fare + trip_tolls,
FROM fm.taxis
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
SQL that would run
SELECT
    trip_id,
    fare,
    tolls_cents,
    fare + tolls_cents AS trip_revenue
FROM taxis.fct_trips
WHERE tolls_cents > 0
;
Compiler error
UE/UNIT-LABEL-MISMATCH
Unit labels must match for ADD(); got USD and USD cent. Annotate or convert one operand so both share the same unit when defining feature TRIP_REVENUE.

Additivity

SQL averages the four zone averages and returns 45.75. The real city average is 43.15. FeatureQL refuses to average a non-additive average.

FeatureQL Try yourself →
DEFINE
    zone_avg_fare := zone_id.RELATED(
        AVG(trip_fare)
        GROUP BY (trip_zone_id)
    ),
RETURN
    city_avg_fare := AVG(zone_avg_fare),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
SQL that would run
WITH zone_avg AS (
    SELECT
        trip_zone_id,
        AVG(fare) AS zone_avg_fare
    FROM taxis.fct_trips
    GROUP BY (trip_zone_id)
)
SELECT CAST(AVG(zone_avg_fare) AS DECIMAL(10, 2)) AS city_avg_fare
FROM zone_avg
;
Compiler error
UE/SUMMARIZABILITY-NOT-ADDITIVE
Summing function AVG() cannot consume NOT ADDITIVE feature ZONE_AVG_FARE when defining CITY_AVG_FARE. Recompute AVG() from detail, divide SUM(total) by SUM(count), or use UNSAFE AVG(ZONE_AVG_FARE) when the override is intentional.

Multiplicity

Exploding the trip array repeats each zone total once per trip. SQL returns 10 trips and 1059.50 in fares. The real city total is 431.50. FeatureQL refuses the sum.

FeatureQL Try yourself →
DEFINE
    zone_trip := UNNEST(ZIP(zone_trips AS trip_id)),
RETURN
    city_trips := COUNT(DISTINCT zone_trip[trip_id]),
    city_fare_total := SUM(zone_fare_total),
FROM fm.taxis
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;
SQL that would run
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_trip AS (
    SELECT
        obt.zone_id,
        UNNEST(obt.trips) AS trip_id
    FROM taxis.agg_zones_obt obt
)
SELECT
    COUNT(DISTINCT zt.trip_id) AS city_trips,
    SUM(f.zone_fare_total) AS city_fare_total
FROM zone_trip zt
LEFT JOIN zone_fare_total f
    ON zt.zone_id = f.trip_zone_id
;
Compiler error
UE/STATIC-ANALYSIS-AGGREGATE-COARSER-GRAIN-THAN-SPINE
Multiplicity-sensitive aggregate CITY_FARE_TOTAL must state how feature FM.TAXIS.ZONE_FARE_TOTAL contributes over a finer spine. FM.TAXIS.ZONE_FARE_TOTAL has grain FM.TAXIS.ZONES and the spine has grain FM.TAXIS.TRIPS, FM.TAXIS.ZONES plus occurrence grain ZONE_TRIP. Use PER FM.TAXIS.ZONE_ID to count each fm.taxis.zone once, or PER ROW to weight by mixed-spine multiplicity.

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 persists 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.

or

Try in interactive docs