NYC taxi analytics with FeatureQL

This tutorial answers one question with a small NYC taxi dataset: which zones should we send drivers to?

You will build the answer in four steps, the same four examples as the FeatureQL homepage: total fares by zone, the same total from an array of trip IDs, a priority-zone rule, and an A/B test of that rule. Each FeatureQL query appears beside equivalent SQL so you can see what the language is replacing. The last section returns to the same zones and trips and shows four slips that SQL would run and FeatureQL refuses before execution.

You only need basic SQL familiarity. Run the page from top to bottom on your first visit because later examples build on the model created earlier. If a notebook restarts, rerun the data and model cells before continuing.

Keep two ideas in mind as you work:

• A feature is a named calculation. := gives it a name, and features can build on other features.
• An entity is a business object such as a zone or a trip. Its typed key prevents unrelated IDs from being mixed accidentally.

Meet the data

The dataset is small enough to check by eye: four taxi zones and ten trips. A third table holds the same trips in a denormalized shape, with one row per zone.

Zones

Each zone has an ID, a name, and a borough.

CREATE OR REPLACE TABLE taxis.dim_zones AS
SELECT
    zone_id::BIGINT AS zone_id,
    name,
    borough
FROM (
    VALUES
        (100, 'Midtown', 'Manhattan'),
        (101, 'Downtown', 'Brooklyn'),
        (102, 'Astoria', 'Queens'),
        (103, 'Yankee Stadium', 'Bronx')
) AS t(zone_id, name, borough);
--
SELECT zone_id, name, borough FROM taxis.dim_zones ORDER BY zone_id;

Trips

Each trip belongs to one zone. It has a fare in dollars, tolls in cents, and a drop-off time. The two amounts use different units on purpose: that is how payment data often arrives, and the model below will record it.

CREATE OR REPLACE TABLE taxis.fct_trips AS
SELECT
    trip_id::BIGINT AS trip_id,
    trip_zone_id::BIGINT AS trip_zone_id,
    fare::DECIMAL(10, 2) AS fare,
    tolls_cents::BIGINT AS tolls_cents,
    dropoff_at
FROM (
    VALUES
        (1001, 100, 42.50, 0, TIMESTAMP '2025-06-01 08:00:00'),
        (1002, 100, 38.00, 0, TIMESTAMP '2025-07-15 14:45:00'),
        (1003, 101, 18.50, 0, TIMESTAMP '2025-08-20 17:30:00'),
        (1004, 101, 24.00, 0, TIMESTAMP '2025-10-05 16:20:00'),
        (1005, 100, 52.00, 0, TIMESTAMP '2025-11-12 11:30:00'),
        (1006, 102, 70.00, 694, TIMESTAMP '2025-11-15 14:30:00'),
        (1007, 102, 45.50, 694, TIMESTAMP '2026-01-21 10:00:00'),
        (1008, 101, 21.50, 0, TIMESTAMP '2026-01-30 09:45:00'),
        (1009, 103, 58.00, 300, TIMESTAMP '2026-01-10 12:00:00'),
        (1010, 103, 61.50, 0, TIMESTAMP '2026-01-28 09:30:00')
) AS t(trip_id, trip_zone_id, fare, tolls_cents, dropoff_at);
--
SELECT trip_id, trip_zone_id, fare, tolls_cents, dropoff_at FROM taxis.fct_trips ORDER BY trip_id;

A denormalized view of each zone

The last table stores, for each zone, its latest trip and the array of all its trip IDs. We will use it to answer the fare question a second time, from nested data instead of a fact table.

CREATE OR REPLACE TABLE taxis.agg_zones_obt AS
SELECT
    zone_id::BIGINT AS zone_id,
    last_trip_id::BIGINT AS last_trip_id,
    trips::BIGINT[] AS trips
FROM (
    VALUES
        (100, 1005, [1001, 1002, 1005]),
        (101, 1008, [1003, 1004, 1008]),
        (102, 1007, [1006, 1007]),
        (103, 1010, [1009, 1010])
) AS t(zone_id, last_trip_id, trips);
--
SELECT zone_id, last_trip_id, trips FROM taxis.agg_zones_obt ORDER BY zone_id;

You can edit the VALUES rows and rerun these three statements. Keep the IDs aligned across tables so every trip still points to a valid zone.

Turn tables into a feature model

FeatureQL needs to know what the data means, not only where it lives. The next statement declares zones and trips as entities, gives each one a typed key, and maps physical columns to features with EXTERNAL_COLUMNS().

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'
    )
;

Four details make the rest of the tutorial possible:

• BIGINT#zones and BIGINT#trips are different types, even though both keys are stored as integers. trip_zone_id is a BIGINT#zones: it lives in the trips table but identifies a zone.
• DECIMAL@CURRENCY.USD and BIGINT@CURRENCY.USD_CENT attach a unit to each amount. The fare is in dollars and the tolls are in cents, and the type says so.
• BIND TO identifies the entity key in each physical table.
• KEYSET() tells FeatureQL how to enumerate the available IDs, so later queries can ask for every zone or trip without listing them.

The model is now persisted under fm.taxis. The following queries use business names such as trip_fare without repeating table and column details.

Calculate total fares by zone

The first step toward the decision is a measure of demand: how much fare did the trips in each zone generate?

RELATED() crosses from zones to trips, sums the fares grouped by trip_zone_id, and returns the matching sum to each zone. The FOR clause supplies all zone and trip keys from the keysets created above.

FeatureQL: zone fare total

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)
;

The result has one row per zone. Midtown totals 132.50: 42.50 + 38.00 + 52.00. The result type is still @CURRENCY.USD, because a sum of dollars is in dollars.

Equivalent SQL: zone fare total

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
;

The SQL version makes the mechanics explicit: aggregate trips in a CTE, then join the totals to zones. FeatureQL keeps the same relationship in one expression:

zone_id.RELATED(SUM(trip_fare) GROUP BY (trip_zone_id))

Reach the same answer from an array

The denormalized zone table does not have one row per trip. It has an array of trip IDs. We can still calculate the same total:

1. ZIP() turns the ID array into an array of rows.
2. EXTEND() looks up trip_fare for each trip in the array.
3. ARRAY_SUM() adds the fares.

FeatureQL: zone fare total from arrays

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)
;

Check the output against the previous result: all four totals are identical. The storage shape changed, but the business question did not.

Equivalent SQL: zone fare total from arrays

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
;

In SQL, the same path requires UNNEST, a join to trips, and a second aggregation. FeatureQL keeps those operations together as one composable feature.

Turn the analytics into a decision

Suppose drivers should be sent only to zones with both:

• more than 100 dollars in total fares; and
• a trip within the last 7 days, as of 2026-02-01.

First persist zone_fare_total so the rule can reuse it without redefining the aggregation:

CREATE OR REPLACE FEATURES IN FM.TAXIS AS
PERSIST
    zone_fare_total := zone_id.RELATED(SUM(trip_fare) GROUP BY (trip_zone_id))
;

Now follow last_trip_id to its trip, count the days since its drop-off, and combine that recency with the fare threshold.

FeatureQL: priority zone flag

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 > 100::_@CURRENCY.USD
FROM FM.TAXIS
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset)
;

Each condition rejects at least one zone:

• Yankee Stadium qualifies: 119.50 in fares and a trip 4 days ago.
• Downtown had a trip 2 days ago but only 64.00 in fares.
• Midtown has the highest fares, but its last trip is 81 days old.
• Astoria has enough fares, but its last trip is 11 days old. It misses the cutoff by 4 days.

Two details in the query are worth a look. DATE_DIFF() counts calendar days between the two dates. CAST(100 AS _@CURRENCY.USD) states that the threshold is in dollars: zone_fare_total carries a unit, so the number it is compared with should carry one too.

Equivalent SQL: priority zone flag

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
;

The SQL needs three joins: one for the denormalized table, one for the latest trip, and one for the fare aggregate. In FeatureQL, the persisted feature and the typed relationship hide those mechanics and leave the decision rule visible.

Test a wider activity window

Astoria missed the 7-day cutoff by 4 days. Maybe the cutoff is too strict. We will compare it with a 14-day version without copying the rule.

First persist recency so both experiment arms use the same calculation:

CREATE OR REPLACE FEATURES IN FM.TAXIS AS
PERSIST
    recency := DATE_DIFF(
        TIMESTAMP '2026-02-01',
        last_trip_id.RELATED(trip_dropoff_at),
        'day'
    )
;

MACRO() turns the rule into a template with a threshold input. The control arm uses 7 days and the test arm uses 14. SAMPLE_BOOL() assigns each zone to an arm from a hash of its ID, so rerunning the query does not move zones between arms.

FeatureQL: priority zone A/B test

DEFINE
    threshold := INPUT(BIGINT),
    flag_priority_zone_macro := MACRO(recency <= threshold
    AND zone_fare_total > 100::_@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(
        KEY_TO_SCALAR(zone_id)::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)
;

Astoria is the zone to inspect. It is assigned to the test arm, and its latest trip is 11 days old. It fails the 7-day control rule and passes the 14-day test rule, so its final flag is true. Yankee Stadium is also in the test arm, but it passes both rules. Midtown and Downtown stay in the control arm and keep their earlier result.

Equivalent SQL: priority zone A/B test

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
;

The SQL version repeats the decision logic for both thresholds and implements the hash bucketing by hand. The FeatureQL version names the shared rule once and varies only the input.

Rejected at compile time

The priority rule works. Other people will now build on the same model, and four slips are easy to make on this data. Each one is ordinary SQL that runs on the taxi tables and returns a result. FeatureQL stops with a compiler error, because the storage type is not the whole meaning of the value.

Every query below runs on fm.taxis, the model you built at the start. Nothing new is declared: the errors come from the types, units, and relationships you already wrote down.

Each case has three parts:

1. The SQL, which runs and returns a wrong result.
2. The same intent in FeatureQL, which is refused.
3. The corrected FeatureQL, which runs and returns the right result.

The first two slips happen while extending the model. The last two happen when someone asks for a citywide figure.

Entities: joining on the wrong key

Someone wants the zone name next to each trip. trip_id and zone_id are both integers, so a join written on trip_id instead of trip_zone_id is valid SQL. No trip ID equals a zone ID, so every trip comes back with a NULL name and no error.

SQL: runs, every name is NULL

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
;

FeatureQL: refused

zone_name belongs to zones, and trip_id is a BIGINT#trips. A trip key cannot look up a zone feature, so the query never runs.

RETURN
    trip_id,
    trip_zone_name := trip_id.RELATED(zone_name),
FROM fm.taxis
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;

FeatureQL: corrected

The key that reaches a zone from a trip is trip_zone_id, which the model declares as BIGINT#zones. Change that one name and every trip gets its zone.

RETURN
    trip_id,
    trip_zone_name := trip_zone_id.RELATED(zone_name)
FROM FM.TAXIS
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset)
;

The error tells you the two keys belong to different entities. It does not pick the right key for you: that comes from the model. To compare the stored numbers of two different entities on purpose, drop the entity label with KEY_TO_SCALAR(). That is an explicit choice, not the default.

Units: dollars plus cents

Fares are in dollars and tolls are in cents. Adding the two columns looks like the obvious way to get trip revenue. For trip 1006, SQL adds 70.00 and 694 and returns 764.00. The real revenue is 76.94.

SQL: runs, revenue is inflated

SELECT
    trip_id,
    fare,
    tolls_cents,
    fare + tolls_cents AS trip_revenue
FROM taxis.fct_trips
WHERE tolls_cents > 0
;

FeatureQL: refused

The model declared trip_fare as @CURRENCY.USD and trip_tolls as @CURRENCY.USD_CENT, so dollars plus cents is refused.

RETURN
    trip_id,
    trip_revenue := trip_fare + trip_tolls,
FROM fm.taxis
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;

FeatureQL: corrected

Convert one side before adding. CONVERT_UNIT() knows that a cent is a hundredth of a dollar, so you do not write the division yourself.

RETURN
    trip_id,
    trip_revenue := trip_fare + CONVERT_UNIT(trip_tolls, CURRENCY.USD)
FROM FM.TAXIS
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset)
;

Trip 1006 now shows 76.94. Trips without tolls are unchanged. Adding a bare number to a value with a known unit is refused for the same reason: the compiler will not guess that 1 means one dollar.

Additivity: averaging averages

Someone wants the average fare for the whole city and already has the average fare per zone. Averaging the four zone averages looks reasonable. SQL returns 45.75. The real city average is 43.15: total fares of 431.50 over 10 trips. The two differ because zones do not have the same number of trips.

SQL: runs, returns 45.75

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
;

FeatureQL: refused

Nothing in the model says that averages are special. FeatureQL knows that the output of AVG() is not additive, so it refuses to average it again.

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),
;

FeatureQL: corrected

The error says to recompute from detail. Here that means averaging the trips directly, not the zones. The query gets shorter: the per-zone step disappears, and only trips are bound.

RETURN
    city_avg_fare := AVG(trip_fare)
FROM FM.TAXIS
FOR
    trip_id := @BIND_KEYSET(fct_trips_keyset)
;

The result is 43.15. When a rate arrives precomputed from a source table, mark that column NOT ADDITIVE to get the same protection. UNSAFE AVG() exists when the rollup is intentional, and it marks every downstream value.

Multiplicity: a zone total repeated on every trip

Someone wants two city figures in one query: the number of trips and the total fares. The denormalized table has an array of trips per zone, so they explode it to count trips. That turns four zone rows into ten trip rows, and each zone's fare total now appears once per trip. Midtown's 132.50 is counted three times.

SQL returns 10 trips, which is right, and 1059.50 in fares, which is wrong. The real city total is 431.50: 132.50 + 64.00 + 115.50 + 119.50, the four totals from the first example.

SQL: runs, trips are right and fares are wrong

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
;

FeatureQL: refused

FeatureQL knows that zone_fare_total is one value per zone and that UNNEST() made the rows finer than that, so a bare SUM() is refused.

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),
;

FeatureQL: corrected

The error names the repair: PER FM.TAXIS.ZONE_ID. Adding PER zone_id inside the SUM() contributes each zone once.

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) PER zone_id
FROM FM.TAXIS
FOR
    zone_id := @BIND_KEYSET(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset)
;

The query now returns 10 trips and 431.50. PER ROW is the other legal choice, when every row should count.

Entities, units, additivity, and multiplicity are checked independently. A typed key does not imply a unit, and PER does not make an average additive. The guides go further: Entities and keys , Units and measures , Metric additivity , and Aggregation multiplicity .

What's next

You have mapped physical data to typed features, answered one question from two storage shapes, composed those results into a decision, and tested a variation without duplicating the rule. You have also seen four ways the same data could have produced a wrong number, and why none of them compiles.

Continue with FeatureMesh's homepage companion to run one definition in batch and serving. For a deeper multi-entity model, try E-commerce .