d06 — Feature Store (Online + Offline)

A fully worked design. Closest to your background, so it should be one of your fastest — and the one where an interviewer will push hardest, because they will assume you know it.

The hard part is not storage. It is point-in-time correctness, and the failure it prevents is a model that looks excellent offline and is mediocre in production.

Table of Contents

• The Prompt
• 1. Requirements and Scope
• 2. Scale Numbers
• 3. API Surface
• 4. Data Model
• 5. High-Level Architecture
• 6. Deep Dive A: Point-in-Time Correctness
• 7. Deep Dive B: Training/Serving Skew
• 8. Failure and Recovery
• 9. Bottlenecks and Evolution
• 10. Tradeoffs Explicitly Rejected
• The Hostile Critique
• The Revision
• References

The Prompt

"Our ML teams keep shipping models that score great offline and disappoint in production. They're also each maintaining their own feature pipelines, and the same feature is computed three different ways depending on who wrote it. Build them a feature store."

The prompt contains its own diagnosis and most candidates miss it. "Great offline, disappointing in production" is the signature of label leakage — the training data contained information that would not have been available at prediction time. "The same feature computed three ways" is training/serving skew. Those are the two deep dives, and they are stated in the prompt.

Naming them back in the first two minutes is the strongest opening available here.

1. Requirements and Scope

Clarifying questions asked

"When you say the same feature is computed three ways — is that three implementations of one definition, or three different definitions?" Both, and they need different fixes: one definition/one implementation solves the first; a registry with ownership solves the second.

"What's the online latency budget?" Assumed p99 < 10 ms for a batch of ~200 features, because it sits inside a ranking request that has ~100 ms total.

"How fresh must online features be?" Assumed tiered: some features are real-time (seconds), some hourly, some daily. Treating them uniformly is a design error — the freshest tier is 100× the cost of the daily one.

"Do you need to reproduce a training set from six months ago?" Assumed yes — model debugging and regulatory review both require it, and it constrains retention and versioning.

Functional

1. Register a feature once: definition, owner, freshness, type, transformation.
2. Serve features online by entity key, low latency, batched.
3. Generate training sets with point-in-time correct joins.
4. Backfill a new feature over history.
5. Monitor freshness, drift, and null rates.

Non-functional

Explicitly out of scope

• Model training, serving, and the experiment tracker.
• Feature selection — we serve what is registered.
• Real-time (in-request) feature computation from raw events; we serve precomputed values plus cheap on-read transformations.

2. Scale Numbers

Online. 500k vector reads/s × 200 features = 100M feature lookups/s. At 8 B per value that is 800 MB/s of reads. That number is why the online store must be memory-resident and co-located, and why the read must be one batched round trip, not 200.

Online storage. 100M entities × 500 features × 8 B = 400 GB. Fits in a sharded in-memory store. Notice: this is not a storage problem.

Offline. 1B training rows × 500 features. A naive per-row point-in-time lookup is 1B × 500 = 5×10¹¹ lookups — impossible. It must be a sorted merge join, which is the whole content of deep dive A.

Offline storage. 2 years of daily snapshots at 100M entities × 500 features × 8 B = 400 GB/day × 730 = 290 TB. Columnar + compressed (~5×) ≈ 60 TB. Fine in object storage, and it means the offline format choice is worth ~230 TB.

Backfill. A new feature over 2 years of history at 100M entities/day = 73B values. At 1M values/s that is 20 hours — so backfill is a first-class scheduled job with progress and resumption, not a script someone runs.

The latency budget breakdown, worth stating because it drives the architecture:

10 ms p99 total
  0.5 ms  client → store network
  1 ms    store lookup (memory)
  0.5 ms  return
  ~8 ms   headroom for GC pauses, tail effects, and being wrong

One round trip for all 200 features. At 0.5 ms per hop, 200 sequential lookups is 100 ms — 10× the entire budget. This is the fan-out-don't-chain rule, and here it is the difference between feasible and not.

3. API Surface

# ---- registration (the control plane) ----
register_feature(
    name="user.purchases_30d",
    entity="user_id",
    dtype="int64",
    source=StreamSource("purchases", timestamp="event_time"),
    transform="COUNT(*) OVER (PARTITION BY user_id RANGE 30 DAYS)",
    freshness="streaming",            # streaming | hourly | daily
    owner="growth-team",
    ttl_days=730,
)

# ---- online (the hot path) ----
get_online_features(
    entities={"user_id": [1, 2, 3], "item_id": [10, 11]},
    features=["user.purchases_30d", "item.ctr_7d", ...],
) -> FeatureVector           # ONE round trip, batched across entities

# ---- offline ----
get_historical_features(
    entity_df,                # MUST contain an event_timestamp column
    features=[...],
) -> DataFrame                # point-in-time correct by construction

backfill(feature="user.purchases_30d", start=..., end=...) -> job_id

Three choices worth defending:

• entity_df must carry event_timestamp, and the API rejects it if absent. This is the single most important line in the design: it makes point-in-time correctness impossible to opt out of by accident. A "get me these features for these users" API with no timestamp is a leakage generator, and most homegrown feature stores have exactly that.
• One call, many entities, many features. The API shape enforces the batching the latency budget requires.
• The transform is registered, not written by the caller. One definition, one implementation — which is half the answer to the prompt's second complaint.

4. Data Model

FEATURE REGISTRY (small, versioned, the source of truth)
  name, version, entity, dtype, source, transform, freshness,
  owner, created_at, deprecated_at
  → every training set records the (name, version) it used

ONLINE STORE                        latest value only
  key:   {entity_type}:{entity_id}
  value: hash of feature_name → (value, event_time, ingest_time)
  sharded by entity_id; memory-resident; TTL per feature

OFFLINE STORE                       full history, columnar
  partitioned by (feature_group, date)
  columns: entity_id, event_time, ingest_time, value...
  SORTED BY (entity_id, event_time)      ← this sort IS the design

Two timestamps per value, and this is the crux of the whole problem:

A purchase at 10:00 that our pipeline processes at 10:45 has event_time=10:00 and ingest_time=10:45. A model predicting at 10:30 could not have known about it — even though its event_time precedes the prediction. Joining on event_time alone produces a training set containing information the production system did not have. That is leakage, and it is the mechanism behind "great offline, disappointing in production."

Sorting the offline store by (entity_id, event_time) is what turns the point-in-time join from an impossible per-row lookup into a linear merge. That sort is not an optimization; it is the reason the design works at all.

5. High-Level Architecture

  Streaming sources ──┐                    Batch sources ──┐
   (Kafka, CDC)       │                     (warehouse)    │
                      ▼                                    ▼
          ┌───────────────────────┐          ┌──────────────────────┐
          │ Stream transform      │          │ Batch transform      │
          │ (Flink)               │          │ (Spark, scheduled)   │
          └───────┬───────┬───────┘          └────┬────────┬────────┘
                  │       │                       │        │
                  │       └───────────┬───────────┘        │
                  ▼                   ▼                    ▼
        ┌──────────────────┐   ┌────────────────────────────────┐
        │  ONLINE STORE    │   │  OFFLINE STORE                 │
        │  latest only     │   │  full history, columnar,       │
        │  memory, sharded │   │  sorted by (entity, event_time)│
        └────────┬─────────┘   └───────────────┬────────────────┘
                 │ p99 < 10 ms                 │ point-in-time join
                 ▼                             ▼
          model serving                  training sets
                 │                             │
                 └──────────┬──────────────────┘
                            ▼
                 ┌─────────────────────────┐
                 │  FEATURE REGISTRY       │  ONE definition,
                 │  + monitoring           │  ONE transform, two sinks
                 └─────────────────────────┘

The structural decision: the transform is written once and its output is written to both stores. That is what eliminates skew at the source rather than detecting it afterwards.

The two hard parts — say these at minute 10:

1. Point-in-time correctness — making leakage structurally impossible.
2. Training/serving skew — making the two paths agree, and proving it.

6. Deep Dive A: Point-in-Time Correctness

The failure, concretely

You are training a churn model. Label: did this user churn in the next 30 days? One row:

user_id=42, prediction_time=2026-03-01, label=churned

You join user.support_tickets_30d. The naive join takes the current value: 8 tickets.

But 7 of those were filed after March 1st — because they were churning. The model learns "many support tickets ⇒ churn", achieves excellent offline AUC, and in production sees the value as of prediction time — 1 ticket — and predicts nothing useful.

The model learned to read the future. Offline metrics are excellent because the leaked signal is genuinely predictive; production is mediocre because the signal is not there.

The correct join

For each training row (entity, event_timestamp), take the feature value from the latest version whose event_time ≤ event_timestamp AND whose ingest_time ≤ event_timestamp.

SELECT e.entity_id, e.event_timestamp, e.label, f.value
FROM entity_df e
ASOF JOIN feature_values f
  ON f.entity_id = e.entity_id
 AND f.event_time  <= e.event_timestamp
 AND f.ingest_time <= e.event_timestamp     -- ← the one people forget

Both conditions are required. event_time alone still leaks, because it admits values our pipeline had not yet computed at prediction time. This is the single most valuable sentence in this design and it distinguishes someone who has debugged a leaking model from someone who has read about feature stores.

Making it feasible

An ASOF JOIN is a predecessor query — the same shape as the versioned KV problem. Per row it is O(log n); at 1B rows × 500 features it is still 5×10¹¹ operations.

So do not do it per row. Because both sides are sorted by (entity_id, timestamp), it becomes a merge join: one linear pass, O(N + M).

entity_df   sorted by (entity_id, event_timestamp)
features    sorted by (entity_id, event_time)

Walk both; for each entity, advance the feature cursor while
event_time <= event_timestamp; the last one passed is the answer.

That is why the offline store is sorted by (entity_id, event_time). The sort turns an impossible problem into a single scan, and it is the answer to "how does this work at a billion rows".

Partition-level pruning helps further: a training set for March only reads March partitions plus the last value before March 1 per entity (a small "carry-in" per partition, precomputed).

The three ways leakage sneaks in anyway

Worth listing, because the ASOF join is necessary and not sufficient:

1. Aggregations computed over the wrong window. purchases_30d computed as "30 days ending now" rather than "30 days ending at event_time" leaks at the source, before the join. The transform must be windowed relative to event_time.
2. Late-arriving data reprocessed in place. If a batch job overwrites yesterday's values with corrected ones, historical training sets silently change. The offline store must be append-only, with corrections as new rows carrying a later ingest_time.
3. The label window overlapping the feature window. A "churn in the next 30 days" label with a feature computed over "the last 30 days" including days after prediction. A design guard: the registry records each feature's window, and the training-set builder fails loudly if a feature's window extends past the row's event_timestamp.

That third guard is the kind of thing that makes a feature store worth building rather than just a convenient cache: it makes a class of error impossible rather than merely documented.

7. Deep Dive B: Training/Serving Skew

The failure

The same feature computed differently in two places. Three flavours, in increasing subtlety:

The third is the nastiest because both implementations are "correct" and the values still differ.

The fix: one definition, one implementation, two sinks

@feature(name="user.purchases_30d", entity="user_id", freshness="streaming")
def purchases_30d(purchases: Stream) -> int:
    return purchases.window(days=30).count()

That definition compiles to both a streaming job (writing the online store) and a batch job (writing the offline store). They are generated from one source, so they cannot drift by accident.

The honest limitation, and you should raise it before the interviewer does: compiling one definition to two engines does not guarantee identical semantics. Spark and Flink disagree on null handling, on window boundary inclusivity, and on floating-point accumulation order. The compilation reduces skew; it does not prove its absence. So you also need:

Continuous skew detection

For a sample of entities (say 0.1%), every hour:
  online_value  = read from the online store
  offline_value = recompute from the offline store at that instant
  assert |online - offline| < tolerance
  → alarm on drift, per feature

This is the second-most-valuable component after the ASOF join, and it is the one nobody builds until after their first bad launch. It catches: a streaming job silently falling behind, a batch job writing a different type, a null-handling divergence, a schema change applied to one path.

Log the served values. Every online read is logged with its feature values and version. Then you can (a) build training sets from exactly what production saw, which eliminates skew by construction for those rows, and (b) reconstruct why a specific prediction was made.

The strongest version of this design: for models where it matters, train on logged served features rather than on recomputed history. Skew becomes structurally impossible because there is only one computation. The cost is that you can only train on features you were already serving — so you cannot evaluate a new feature without backfilling it, which is why you need both paths. Saying this tradeoff out loud is a strong signal.

Freshness tiers, and why uniform freshness is wrong

Making everything streaming is ~100× the cost of daily for features whose value changes weekly. The registry records the freshness tier, and the monitoring alarms per tier — a daily feature that is 25 hours old is broken; a streaming feature that is 25 hours old is a catastrophe, and the same alarm cannot serve both.

8. Failure and Recovery

Deliberately accepted: a streaming feature can be up to ~30 s stale, and during a stream outage it degrades to whatever the last written value was, with a staleness flag rather than a failure. I accept that because failing a prediction request because a feature is 40 s old is worse than predicting with a slightly stale value — but the model must receive the staleness as a signal, so it can be trained to handle it, rather than being lied to.

That last clause is the interesting part: serving a stale value silently is a form of skew. Serving it with a staleness indicator is honest, and the model can learn from it.

9. Bottlenecks and Evolution

1. The online read fan-out. 200 features across 5 entity types is up to 5 shard groups. Fix: co-locate all features for one entity type on one shard (already in the key design), and issue the ≤5 lookups in parallel, so p99 is the slowest of five rather than the sum. Beyond that, a per-request cache for entities appearing repeatedly in a batch — common in ranking, where one user appears against 200 items.

2. The point-in-time join, at 10×. The merge join is linear but the sort is O(n log n) and dominates. Fix: keep the offline store permanently sorted (Iceberg/Delta with sort-order metadata and z-ordering), so joins never re-sort. This is worth more than any compute optimization.

3. Backfill contention. A 20-hour backfill competing with serving-path pipelines. Fix: a separate resource pool with a hard cap, and treat backfill as preemptible.

4. Registry as a hot dependency. Every online read needs feature metadata. Fix: push the registry to every serving process at startup and on change; never look it up per request. A stale registry is far better than a registry on the hot path.

5. Feature sprawl — the real long-term problem. After two years you have 5,000 features, 3,000 unused, and nobody knows which. Fix: usage tracking per feature per model, deprecation warnings, and a policy that an unused feature's pipeline is turned off after N days. This is an organizational problem the system can support but not solve, and saying that is more honest than pretending otherwise.

At 100×: the online store becomes the constraint, and the answer is to shift from "fetch features" to "push feature vectors" — precompute and cache the whole vector per entity, updated on change. That trades write amplification for read simplicity, and it is the right trade when reads are 500k/s and writes are far fewer.

10. Tradeoffs Explicitly Rejected

Rejected: one store for both online and offline. Attractive — no skew by construction. Rejected because the access patterns are irreconcilable: online is point lookups at p99 10 ms on the latest value; offline is full scans over history with a sorted merge join. A single store is either too slow online or too expensive offline. Flip condition: at small scale (< 1M entities, < 10k reads/s) a single Postgres with the history table and an index genuinely is better, and I would not build two.

Rejected: computing features on read from raw events. No storage, always fresh, zero skew. Rejected on the latency budget: a 30-day aggregation per request is far beyond 10 ms. Flip condition: cheap features over a tiny window (last 5 events) are better computed on read than maintained.

Rejected: overwriting values on late-arriving data. Simpler, and the online store does do this (it holds latest-only). Rejected for the offline store because it silently changes historical training sets, which destroys reproducibility and makes a leakage bug undebuggable. Append-only with ingest_time is the price of being able to answer "what did the model see?".

Rejected: joining on event_time alone. The obvious ASOF join, and it is what most homegrown implementations do. Rejected because it admits values whose computation postdates the prediction — leakage that survives a correct-looking join. This is the most common real-world bug in this space and it is worth naming as such.

Rejected: making every feature streaming. Uniform freshness is simpler to reason about. Rejected on cost — ~100× for features that change weekly — and because it makes the freshness alarm useless (see §7). Flip condition: if all features genuinely were fast-moving, tiering would be complexity for nothing.

Rejected: letting teams write their own transforms with the store just providing storage. Rejected because it does not solve the prompt's second complaint at all — the same feature would still be computed three ways. The registry owning the transform is the point.

The Hostile Critique

C1. "Your ASOF join uses ingest_time <= event_timestamp. Your streaming pipeline has 30 seconds of lag. So for a prediction at 10:00:00, you exclude anything ingested after 10:00:00 — including the event that happened at 09:59:50 and was ingested at 10:00:15. But in production at 10:00:00 you also didn't have it. So are you correct, or are you systematically training on less data than production sees?"

C2. "You log served features to eliminate skew. At 500k reads/s × 200 features that's 100M values/s logged. What does that cost, and what happens to it?"

C3. "One definition compiles to Flink and Spark. Show me what happens with AVG(x) WHERE x IS NULL in both, and then tell me again that skew is eliminated."

C4. "Your skew detector samples 0.1% hourly. A feature is wrong for one specific segment — users in Japan — which is 0.5% of traffic. Does your detector find it?"

C5. "Append-only offline store, 2-year retention, corrections as new rows. A GDPR deletion request arrives for a user. Walk me through it."

C6. "You said co-locate features for an entity type on one shard. One entity type is user_id and it's 90% of your features and 95% of your reads. What does that shard look like?"

The Revision

R1 — Point-in-time must reproduce serving lag, not eliminate it (answers C1)

The critique is sharp and correct, and the resolution matters: the goal is not to exclude late data, it is to reproduce exactly what production had.

ingest_time <= event_timestamp does that only if the offline ingest_time equals the time the online store received the value. If offline ingest is a nightly batch, its ingest_time is hours later than online's, and the join then excludes data production genuinely had — training on less than production sees, which is the mirror-image error.

Change: record online_available_time — the moment the value became readable in the online store — as a distinct third timestamp, and join on that.

ASOF JOIN ON f.entity_id = e.entity_id
        AND f.online_available_time <= e.event_timestamp

• The streaming writer stamps it at online-store write.
• The batch writer stamps it at the batch's publish time.
• Backfilled values get the online_available_time they would have had — computed from the pipeline's SLA, and flagged as estimated so a training set built from backfilled data is known to be approximate.

So the timestamps are now three, each with a distinct job: event_time (when it became true), ingest_time (when we learned it — for lateness monitoring), online_available_time (when a model could have read it — for joins).

Cost: one more column, and backfilled history has an estimated availability time. That is honest and flagged, versus the previous version which was subtly and silently wrong in one direction. And the general lesson: point-in-time correctness means reproducing production's information set, not minimizing it.

R2 — Log at the vector level, sampled and referenced (answers C2)

The critique is right that 100M values/s of logging is absurd — it is larger than the serving traffic it describes.

Change: log a reference, not the values.

Per prediction, log:  (request_id, entity_ids, feature_set_version,
                       store_read_timestamp, hash_of_returned_vector)

• ~100 bytes per prediction instead of ~1.6 KB — 16× less.
• The values are reconstructable from the offline store using online_available_time <= store_read_timestamp, which R1 made exact.
• The hash is the verification: recompute the vector from the offline store, hash it, compare. A mismatch is skew, detected exactly, on real traffic.

Full-value logging is retained for a sampled 0.1%, as ground truth for debugging and for the skew detector.

Cost: reconstructing a training set is now a join rather than a read, and a mismatch tells you that the vector differed without saying which feature. Mitigated because the sampled full logs localize it. This trades a little debuggability for a 16× cost reduction and it is the right trade at this volume.

R3 — Semantic conformance tests, not just shared code (answers C3)

The critique is correct and I already conceded the point in §7 — but conceding is not a design.

Change: every registered feature gets a generated conformance suite that runs both implementations against adversarial fixtures and asserts equality.

Fixtures generated per feature from its type and window:
  empty input · all nulls · single row · boundary timestamps (window edge,
  inclusive/exclusive) · duplicates · out-of-order arrival · numeric extremes
  (overflow, denormals) · unicode keys · late data beyond the window

A feature CANNOT be promoted to production until both engines agree on all of them.

Specifically for the critique's case: AVG over an empty set returns NULL in Spark SQL and can return 0 in a naive Flink aggregation. The empty-input fixture catches it at registration, and the registry forces the author to declare the intended semantics (default_on_empty), which then compiles identically to both.

Cost: feature registration becomes slower and stricter, which teams will complain about. That is the correct place for the friction — a semantic divergence found at registration costs an hour; found after launch it costs a retrain and a lost quarter of a model's credibility.

R4 — Stratify the skew detector (answers C4)

The critique identifies a real blind spot: uniform 0.1% sampling of a 0.5% segment gives ~5 samples/hour, so a segment-specific bug is invisible for a long time.

Change, three parts:

1. Stratified sampling by the dimensions that matter — region, tier, entity age, traffic source — with a minimum absolute sample per stratum (say 100/hour), not a fixed percentage. Small segments get proportionally more sampling, which is the whole point.
2. The vector-hash check from R2 runs on 100% of traffic, because it is cheap. It does not say which feature diverged, but it detects that something did, on every segment, immediately. The stratified full-value sample then localizes it.
3. Distribution monitoring per feature per stratum — null rate, mean, p50/p99 — compared to a trailing baseline. A feature that is wrong for one segment usually shows up as a distribution shift there before it shows up as a metric regression.

Cost: more monitoring state — features × strata, which is a big cross-product. Bounded by limiting strata to a handful of registered dimensions rather than anything a team wants.

R5 — Deletion in an append-only store (answers C5)

The critique names a genuine conflict: append-only is what makes reproducibility work, and GDPR erasure requires deletion. Both are non-negotiable.

Change: crypto-shredding.

• Every entity's feature values are encrypted at rest with a per-entity key, held in a key store.
• A deletion request destroys the key. The data remains, in place, and is permanently unreadable.
• The offline store's structure is untouched, so partitions, sort order, and reproducibility for every other entity are unaffected.

What this costs, and it is not nothing:

• Training sets built after the deletion cannot include that entity's rows — which is correct and required.
• Training sets built before it are not bit-reproducible any more. That is unavoidable: reproducing them would mean reproducing deleted data. The honest design records, per training set, how many rows are now unreadable, so an auditor sees a documented gap rather than silently different numbers.
• Key-store availability becomes a hard dependency of offline reads. Mitigated by caching keys in the compute layer for the duration of a job.

And the operational necessity: a deletion SLA (30 days) means a scheduled job, an audit log, and a test that proves the data is genuinely unreadable afterwards. "We'll delete it" without a tested mechanism is not a compliance posture.

R6 — Shard by entity ID, not by entity type (answers C6)

The critique catches a real modelling error. "Co-locate an entity type on one shard" was sloppy — it makes user_id a single hot shard holding 90% of the data and 95% of the reads.

Change: shard by hash(entity_type, entity_id), so:

• All features for one entity instance are on one shard → still one lookup per entity, which is what the latency budget needed.
• The user_id space spreads across every shard → no hot shard by construction.
• A read for 5 entities touches ≤5 shards, in parallel.

Plus a hot-key path, because entity popularity is Zipfian: track per-key read rates and replicate the top-N entities to every shard, served from a local cache. For a ranking workload where one user is read against 200 items, that turns 200 lookups into one local read plus 200 item lookups.

Cost: replicating hot keys means their writes fan out to every shard. Bounded by keeping N small (a few thousand) and by the fact that hot entities are hot precisely because they are read far more than written.

References

• ../WARMUP.md — partitioning, hot keys, and the failure taxonomy
• ../../coding/WARMUP.md#chapter-1-predecessor-queries-and-versioned-state — the ASOF join is a predecessor query, and the single-node version is here
• d09-search-serving.md — the other design closest to your background
• Uber. Michelangelo: Machine Learning Platform. https://www.uber.com/blog/michelangelo-machine-learning-platform/ — the original industrial feature store, and the paper that named the online/offline split
• Feast documentation — point-in-time joins and entity dataframes. https://docs.feast.dev/
• Tecton / Databricks. Feature Store concepts — freshness tiers, materialization
• Sculley et al. Hidden Technical Debt in Machine Learning Systems. NeurIPS 2015 — training/serving skew, entanglement, and why the ML code is the small part
• Breck et al. Data Validation for Machine Learning. SysML 2019 — the distribution-monitoring approach in R4
• Kleppmann, M. Designing Data-Intensive Applications, 2nd ed. — Ch. 10–11 (batch and stream processing, and the unification of the two)