NYC taxi analytics with FeatureQL

This page is the interactive companion to the FeatureQL homepage examples. It walks through the same four patterns on a tiny synthetic NYC taxi dataset: zone fare totals, array-based enrichment, priority zones for driver positioning, and an A/B test on the active-window threshold.

If you know SQL, use this mental map:

:= names a feature, much like AS names a SQL expression.
ENTITY() declares a business object; INPUT(BIGINT#ZONES) declares its typed key.
EXTERNAL_COLUMNS() maps physical columns to features. BIND TO identifies the key column.
FOR supplies concrete entity keys for a query; it is binding, not a Python loop.
RELATED() replaces common join-and-aggregate or foreign-key lookup patterns.
CREATE FEATURES persists reusable definitions; WITH keeps definitions local to one query.

Run the page from top to bottom the first time. In a notebook, if a later cell fails after a restart or an out-of-order run, rerun setup, data, and model cells before the analytical query.

Set up the data

Three tables back the tutorial: a zone dimension, a trip fact table, and a pre-aggregated "One Big Table" (OBT) that stores each zone's last trip and an array of trip IDs. Each statement is a single CREATE OR REPLACE TABLE … AS SELECT … VALUES; change the VALUES rows to try different data.

Zones

FeatureQL

/* SQL */
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);

Result

Trips

FeatureQL

/* SQL */
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,
    dropoff_at
FROM (
    VALUES
        (1001, 100, 450.00, TIMESTAMP '2025-06-01 08:00:00'),
        (1002, 100, 380.00, TIMESTAMP '2025-07-15 14:45:00'),
        (1003, 101, 600.00, TIMESTAMP '2025-08-20 17:30:00'),
        (1004, 101, 550.00, TIMESTAMP '2025-10-05 16:20:00'),
        (1005, 100, 520.00, TIMESTAMP '2025-11-12 11:30:00'),
        (1006, 102, 1200.00, TIMESTAMP '2025-11-15 14:30:00'),
        (1007, 102, 300.00, TIMESTAMP '2026-01-20 10:00:00'),
        (1008, 101, 400.00, TIMESTAMP '2025-12-25 09:45:00'),
        (1009, 103, 850.00, TIMESTAMP '2026-01-10 12:00:00'),
        (1010, 103, 400.00, TIMESTAMP '2026-01-28 09:30:00')
) AS t(trip_id, trip_zone_id, fare, dropoff_at);

Result

Zone OBT

FeatureQL

/* SQL */
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);

Result

Define entities and map external data

Every FeatureQL model starts by declaring entities and mapping table columns to features. This CREATE FEATURES statement sets up two entities (zones and trips), their primary key inputs, and the EXTERNAL_COLUMNS() mappings that connect each table column to a named feature.

FeatureQL

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

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.TAXIS.ZONES	CREATED	Feature created as not exists
FM.TAXIS.TRIPS	CREATED	Feature created as not exists
FM.TAXIS.ZONE_ID	CREATED	Feature created as not exists
FM.TAXIS.TRIP_ID	CREATED	Feature created as not exists
FM.TAXIS.TABLES.DIM_ZONES	CREATED	Feature created as not exists
FM.TAXIS.ZONE_NAME	CREATED	Feature created as not exists
FM.TAXIS.TABLES.FCT_TRIPS	CREATED	Feature created as not exists
FM.TAXIS.TRIP_FARE	CREATED	Feature created as not exists
FM.TAXIS.TRIP_ZONE_ID	CREATED	Feature created as not exists
FM.TAXIS.TRIP_DROPOFF_AT	CREATED	Feature created as not exists
FM.TAXIS.TABLES.AGG_ZONES_OBT	CREATED	Feature created as not exists
FM.TAXIS.LAST_TRIP_ID	CREATED	Feature created as not exists
FM.TAXIS.ZONE_TRIPS	CREATED	Feature created as not exists
FM.TAXIS.DIM_ZONES_KEYSET	CREATED	Feature created as not exists
FM.TAXIS.FCT_TRIPS_KEYSET	CREATED	Feature created as not exists

Notice the BIND TO clauses — they tell FeatureQL which column is the key for each entity. The KEYSET() definitions at the bottom declare how to enumerate all keys (the feature name is the keyset identity), which lets later queries pin them with @BIND_KEYSET(dim_zones_keyset) instead of listing IDs manually.

Zone fare total with RELATED()

RELATED() aggregates data across entity boundaries in a single expression. Here, it sums trip_fare grouped by trip_zone_id and joins the result back to each zone — replacing the CTE + LEFT JOIN pattern you'd write in SQL.

FeatureQL: zone fare total

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(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;

Result

fm.taxis.zone_id BIGINT	zone_fare_total DECIMAL
100	1350.0
101	1550.0
102	1500.0
103	1250.0

Equivalent SQL: zone fare total

FeatureQL

/* 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
;

Result

zone_id BIGINT	zone_fare_total DECIMAL
100	1350.0
101	1550.0
102	1500.0
103	1250.0

The SQL version requires a CTE with GROUP BY, then a LEFT JOIN. FeatureQL expresses the same logic in one line: zone_id.RELATED(SUM(trip_fare) GROUP BY trip_zone_id).

Zone fare total from arrays

When data is stored in a denormalized format — arrays of foreign keys in an OBT or key-value store — FeatureQL handles it with ZIP() and EXTEND(). ZIP() unpacks a scalar array into an array of rows, EXTEND() enriches each row with data from another entity, and ARRAY_SUM() aggregates back to a scalar.

FeatureQL: zone fare total from arrays

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

Result

fm.taxis.zone_id BIGINT	zone_fare_total_arr DECIMAL
100	1350.0
101	1550.0
102	1500.0
103	1250.0

Equivalent SQL: zone fare total from arrays

FeatureQL

/* 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
;

Result

zone_id BIGINT	zone_fare_total_arr DECIMAL
100	1350.0
101	1550.0
102	1500.0
103	1250.0

The SQL version requires UNNEST, a JOIN, and re-aggregation — three separate steps for what FeatureQL chains in a single expression.

Priority zone: send drivers where demand is active

Features compose naturally. Here, we persist zone_fare_total so it can be reused across queries, then flag priority zones for driver positioning: high fare volume and a trip in the last 7 days (as of 2026-02-01).

FeatureQL

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

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.TAXIS.ZONE_FARE_TOTAL	CREATED	Feature created as not exists

The RELATED() call on last_trip_id follows the foreign key to look up the trip's dropoff timestamp, and DATE_SUBTRACT() computes recency. The boolean flag_priority_zone combines both conditions.

FeatureQL: priority zone flag

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(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;

Result

fm.taxis.zone_id BIGINT	fm.taxis.zone_fare_total DECIMAL	recency BIGINT	flag_priority_zone BOOLEAN
100	1350.0	80	false
101	1550.0	37	false
102	1500.0	11	false
103	1250.0	3	true

Equivalent SQL: priority zone flag

FeatureQL

/* 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
;

Result

zone_id BIGINT	zone_fare_total DECIMAL	recency BIGINT	flag_priority_zone BOOLEAN
100	1350.0	81	false
101	1550.0	38	false
102	1500.0	12	false
103	1250.0	4	true

A/B test with MACRO() and HASH01()

MACRO() turns any expression into a parameterized template. Here, the priority-zone rule becomes a macro with a configurable active-window threshold. HASH01() provides deterministic bucketing — the same zone always lands in the same experiment arm, with no external randomization service needed.

First, persist the recency feature so both experiment arms can reference it:

FeatureQL

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

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.TAXIS.RECENCY	CREATED	Feature created as not exists

The macro flag_priority_zone_macro accepts a threshold input and produces a boolean. It's instantiated twice — once with 7 days (control) and once with 14 days (test). HASH01() assigns each zone to an arm based on a deterministic hash of their ID.

FeatureQL: priority zone A/B test

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,
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(dim_zones_keyset),
    trip_id := @BIND_KEYSET(fct_trips_keyset),
;

Result

fm.taxis.zone_id BIGINT	flag_priority_zone_control BOOLEAN	expose_to_test BOOLEAN	flag_priority_zone BOOLEAN
100	false	false	false
101	false	false	false
102	false	true	true
103	true	true	true

Equivalent SQL: priority zone A/B test

FeatureQL

/* 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
;

Result

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

The SQL version duplicates the business logic for each arm and manually implements hash-based bucketing. The FeatureQL version defines the logic once and parameterizes the difference.

What's next

This tutorial covered the core homepage workflow: map external data with EXTERNAL_COLUMNS(), aggregate across entities with RELATED(), work with arrays using ZIP() and EXTEND(), create reusable logic with MACRO(), and run experiments with HASH01().

For a deeper look at a multi-entity retail data model, see E-commerce . For language fundamentals, start with Hello World .

Last update at: 2026/08/04 11:25:38

On this page

Set up the data Zones Trips Zone OBT Define entities and map external data Zone fare total with RELATED()FeatureQL: zone fare total Equivalent SQL: zone fare total Zone fare total from arrays FeatureQL: zone fare total from arrays Equivalent SQL: zone fare total from arrays Priority zone: send drivers where demand is active FeatureQL: priority zone flag Equivalent SQL: priority zone flag A/B test with MACRO() and HASH01()FeatureQL: priority zone A/B test Equivalent SQL: priority zone A/B test What's next