ML Feature & Feature-Store Modeling

A machine learning model learns from examples. Each example is a row of numbers about one thing, plus the answer we want the model to guess.

The numbers are called features. The answer is called the label.

The hard part is not the model. The hard part is giving each feature the right value for the right day.

Our task: guess if a customer places an order in the next 10 days. We use the shop from earlier chapters: customers 1 to 5 (Asha, Ben, Carla, Dev and Omar) and orders 101 to 106.

In Temporal & Historical Modeling, you learned the as-of join. It picks the row that was true on a given day. This chapter uses the same idea for features.

What you will learn

1. What a feature is, and why every value needs a time.
2. The point-in-time join, and the leak you get without it.
3. How to backfill feature history from past orders.
4. Training-serving skew: one feature, two answers.
5. The feature store, with an offline and an online store.
6. Freshness and TTL.

An entity is the thing a feature describes. In our task, the entity is a customer. Its key is customer_id.

A feature is one number about an entity. We use two: orders_so_far and paid_so_far.

The first counts all orders. The second adds up the amounts of paid orders.

A feature value is only true as of a day. Asha had 1 order on 2026-01-05. She had 2 orders on 2026-01-12.

So we store a time with each value. We call it feature_ts. It is the day the value was ready.

A daily job counts the orders overnight. So the value for an order is ready the day after it. This is the feature history:

Asha has two rows, because she placed two orders. Order 105 is pending and has no amount, so Carla’s paid_so_far stays at 15.50.

Omar has no rows. He has no orders yet. Ben has one order, but it was refunded, so his paid_so_far is 0.00.

Now the label. It is 1 if the customer places an order in the 10 days that start on label_date. The label date is the day we make the guess.

We pick the label dates. Asha gets two, and everyone else gets 2026-01-12. These are the rows we pick:

This query finds the label for each row:

SELECT l.customer_id,
       l.label_date,
       CASE WHEN EXISTS (
         SELECT 1
         FROM orders AS o
         WHERE o.customer_id = l.customer_id
           AND o.order_date >= l.label_date
           AND o.order_date <  l.label_date + INTERVAL '10 days'
       ) THEN 1 ELSE 0 END AS label
FROM label_dates AS l
ORDER BY l.customer_id, l.label_date;

A training row for label date D may use only feature rows with feature_ts on or before D. Nothing later is allowed.

This is the as-of join. For features, it is also called the point-in-time join. For each label row, you take the latest feature row that is not after the label date.

This query builds the training set the right way:

SELECT l.customer_id,
       l.label_date,
       COALESCE(f.orders_so_far, 0)    AS orders_so_far,
       COALESCE(f.paid_so_far, 0.00) AS paid_so_far
FROM label_dates AS l
LEFT JOIN feature_history AS f
  ON  f.customer_id = l.customer_id
  AND f.feature_ts = (
        SELECT MAX(f2.feature_ts)
        FROM feature_history AS f2
        WHERE f2.customer_id = l.customer_id
          AND f2.feature_ts <= l.label_date)
ORDER BY l.customer_id, l.label_date;

Now remove the bound. This query takes the latest feature row for each customer, whatever its date:

SELECT l.customer_id,
       l.label_date,
       COALESCE(f.orders_so_far, 0)    AS orders_so_far,
       COALESCE(f.paid_so_far, 0.00) AS paid_so_far
FROM label_dates AS l
LEFT JOIN feature_history AS f
  ON  f.customer_id = l.customer_id
  AND f.feature_ts = (
        SELECT MAX(f2.feature_ts)
        FROM feature_history AS f2
        WHERE f2.customer_id = l.customer_id)
ORDER BY l.customer_id, l.label_date;

Compare the two results. Three rows leak, because the join picked a feature row dated after the label date. Here is why each row leaks:

• Asha on 2026-01-05 sees 2 orders and 65.00. Order 102, the order we want to guess, is already counted.
• Carla sees 2 orders. Orders 104 and 105 are both after the label date.
• Dev sees 80.00. That is order 106, the very order that makes his label 1.

This is data leakage. The model sees the future in training. It scores well offline, and then fails in production, where the future does not exist yet.

The honest set looks worse. Dev has 0 orders and the label is 1.

No model can guess that from his history. That is the true difficulty of the task.

A second leak: the window

A rolling window can leak too. Suppose the feature is orders in the last 30 days. Take Carla on 2026-01-15, the day she places order 104.

SELECT
  SUM(CASE WHEN order_date <= DATE '2026-01-15' THEN 1 ELSE 0 END) AS ends_on_label_day,
  SUM(CASE WHEN order_date <  DATE '2026-01-15' THEN 1 ELSE 0 END) AS ends_before_label_day
FROM orders
WHERE customer_id = 3
  AND order_date >= DATE '2026-01-15' - INTERVAL '30 days';

Always end the window before the label day. Use the half-open range from Temporal & Historical Modeling: it includes the start day but not the end day.

When you add a new feature, the past has no values. A backfill computes them for old days. It builds the rows of feature_history from the orders.

This query writes one row for each order. The window function keeps a running total for each customer:

SELECT customer_id,
       order_date + 1 AS feature_ts,
       COUNT(*) OVER w AS orders_so_far,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0.00 END) OVER w AS paid_so_far
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, feature_ts;

Each row gets its own time: the day after its order. The backfill does not use the day it runs.

Suppose a backfill ran on 2026-02-01 and stamped all six rows with that day. Every feature_ts is then after every label date.

The point-in-time join finds no row, so every feature becomes 0. The model then learns nothing from these features.

Training is when the model learns from old rows. Serving is when the model scores a live customer. Training-serving skew means the two paths compute a feature in two different ways.

The model learns one meaning, and in production it gets another. Nothing fails. The accuracy slowly gets worse.

Take Carla’s orders_so_far on 2026-01-22. The training query counts every order. A developer wrote the serving code and counted only paid orders:

SELECT 'training' AS path, COUNT(*) AS orders_so_far
FROM orders
WHERE customer_id = 3
  AND order_date < DATE '2026-01-22'
UNION ALL
SELECT 'serving' AS path, COUNT(*) AS orders_so_far
FROM orders
WHERE customer_id = 3
  AND status = 'paid'
  AND order_date < DATE '2026-01-22';

The model learned from a 2 and now receives a 1. Both queries look correct on their own.

Three habits remove most skew:

• Write each feature once, in one definition. Training and serving both use it.
• Use the same rules for NULL values, units and time zones on both paths.
• Run a parity check. Compare the feature rows for the same customers and times on both paths.

A feature store keeps one definition of each feature and serves it to training and to serving. A feature view is that definition: the entity, the features, the source table and the time column.

Here is the feature view for our task:

The store keeps two copies of the values. The offline store holds the full history, in the warehouse. It is what you read to build training sets.

The online store holds only the latest row for each customer. It sits in a key-value store, where you look up one key and get one row. Serving can read one customer in a few milliseconds.

This query shows what the online store holds for our six feature rows:

SELECT f.customer_id, f.feature_ts, f.orders_so_far, f.paid_so_far
FROM feature_history AS f
WHERE f.feature_ts = (
  SELECT MAX(f2.feature_ts)
  FROM feature_history AS f2
  WHERE f2.customer_id = f.customer_id)
ORDER BY f.customer_id;

These are the values the leaky join used. The latest value is right for scoring a customer today. It is wrong for training on the past.

That is why there are two stores. The offline store answers “as of a past day”. The online store answers “now”.

Materialization is the job that copies the latest values from the offline store to the online store. It runs on a schedule.

Because both stores come from one definition, they compute each feature the same way. The online copy can still be older, as the next section shows.

NoSQL & Access-Pattern Modeling explains how to design the key.

Feature stores you may meet. Each line is one fact:

• Feast is open source. Its get_historical_features call runs the point-in-time join, and get_online_features serves the latest values.
• Tecton is a managed feature platform.
• Databricks has a feature store inside its platform, and it supports point-in-time lookups.
• Vertex AI Feature Store is the managed option on Google Cloud.

Freshness is how old the online value can be when the model reads it. The online store only knows what the last job wrote.

Take a made-up order. Order 360: Omar pays 30.00 on 2026-01-25 at 14:00. The daily job runs at 02:00 the next day.

For 12 hours the model scores Omar with 0 orders, though he has 1. An order at 00:00 waits 26 hours, until 02:00 the next day.

Decide the freshness the model needs, then check the schedule meets it. If the model needs values under one hour old, a daily job is too slow.

You need a faster job or streaming. Batch vs Streaming vs Micro-Batch shows the choice.

Also watch the job itself. A missed run makes every value older, with no error. Data Observability & Monitoring shows how to alert on late data.

TTL means time to live. It is the maximum age of a value that you may use. A row older than the TTL is not used.

In Feast, ttl on a feature view limits how far back the point-in-time join looks. Say the TTL is 30 days.

Take a training row for Asha with label date 2026-02-15. Her last row, from 2026-01-11, is 35 days old on that day, so the join returns no value.

Feature bugs are time bugs. Each one gives the model a wrong value for a day, and none of them raises an error.

Test yourself. Cover the page and answer each question:

• What is an entity, and what is a feature value as of a day?
• Why does the leaky join change exactly three of the six rows?
• Why must a backfill use each row’s own time?
• How can two correct queries cause training-serving skew?
• What does the offline store hold, and what does the online store hold?
• Why is a short TTL wrong for a running total?

Each exercise opens a drawing board. You draw the tables, and the review checks them against sample rows.

Start with “Fields, sensors, and missing readings”. Its leak is in the timestamps, as in this chapter.