Skip to content
Ephizen logo ephizen / wiki
CS 402

Star Schema and ML Feature Stores

Dimensional modeling for analytics, and why ML feature stores are essentially star schemas with point-in-time correctness bolted on.

SQL II — Analytical advanced #sql #modeling #ml-features

TL;DR

A star schema is the standard analytics layout: a central fact table holding measurements (orders, page views, predictions) with foreign keys to surrounding dimension tables holding descriptive attributes (customers, products, dates, geographies). The shape on a diagram looks like a star.

ML feature stores are a special case of the same idea — fact tables of feature values, dimension tables of entities (users, items), with one extra requirement: point-in-time correctness. When you join a feature to a training event, you must use the feature value as it was at the event’s time, not the latest value. Get that wrong and your model trains on data leakage that makes offline metrics look great and production performance fall off a cliff.

The picture — a simple star

                     dim_dates
                          |
                          v
dim_customers  ←  fact_orders  →  dim_products
                          ^
                          |
                     dim_stores

fact_orders has columns: (order_id, date_id, customer_id, product_id, store_id, quantity, total).

Each foreign key resolves to a dimension. A typical analytics query joins fact + dimensions:

SELECT d.year, d.month,
       p.category,
       SUM(f.total) AS revenue
FROM   fact_orders     f
JOIN   dim_dates       d ON d.date_id     = f.date_id
JOIN   dim_products    p ON p.product_id  = f.product_id
JOIN   dim_customers   c ON c.customer_id = f.customer_id
WHERE  d.year = 2026 AND c.country = 'US'
GROUP  BY d.year, d.month, p.category;

The fact table is huge (billions of rows); dimension tables are small (thousands to millions). The pattern: filter on dimensions, aggregate on facts.

Why this layout

  • Read pattern matches. BI tools, dashboards, and analytics queries almost universally look like “filter by some dimensions, aggregate some facts” — exactly what star schemas optimize.
  • Predictable joins. Always fact ⨝ dimension; never dimension ⨝ dimension. The planner picks hash joins on the small dimension side.
  • Slowly changing dimensions (SCDs) handle the “the customer’s country changed” problem cleanly with versioned rows.
  • Friendly to humans. “What was the revenue from US customers in electronics in Q2?” maps directly to the schema.

Slowly Changing Dimensions (SCDs)

A user’s address, plan tier, or country can change. How do you record it?

  • SCD Type 1: overwrite. The dimension row reflects the current value. Old transactions silently get the new value when joined. Loses history.
  • SCD Type 2: a new row per change, with valid_from / valid_to columns. Old transactions join to the historically-correct row. The canonical choice.
CREATE TABLE dim_customers (
    surrogate_key bigserial PRIMARY KEY,
    customer_id   int       NOT NULL,           -- the natural key
    country       text,
    plan          text,
    valid_from    timestamptz,
    valid_to      timestamptz,
    is_current    boolean
);

Joining a fact to the right version of the dimension:

SELECT f.*, c.country, c.plan
FROM   fact_orders f
JOIN   dim_customers c
       ON c.customer_id = f.customer_id
      AND f.placed_at >= c.valid_from
      AND f.placed_at <  COALESCE(c.valid_to, '9999-12-31');

This is the point-in-time join. It looks up the version of the dimension valid at the fact’s timestamp.

Feature stores — star schemas for ML

A feature store is structurally a star schema:

  • Entity tables = dimensions (users, items, sessions).
  • Feature tables = facts, but indexed by entity_id + timestamp.
  • Training queries join feature tables to event tables with point-in-time correctness.

The canonical schema for a feature value:

CREATE TABLE user_spend_30d (
    user_id     int       NOT NULL,
    computed_at timestamptz NOT NULL,
    value       numeric,
    PRIMARY KEY (user_id, computed_at)
);

Each row is one (user, time) feature value. Multiple rows per user capture how the feature changed over time.

The point-in-time join

Building a training set: for each training event, attach the feature value that was current at the event’s time.

-- training events
CREATE TABLE training_events (
    event_id   bigint  PRIMARY KEY,
    user_id    int     NOT NULL,
    event_time timestamptz NOT NULL,
    label      int
);

-- attach feature values valid at event_time
WITH ranked AS (
    SELECT te.event_id, te.user_id, te.event_time, te.label,
           f.value      AS spend_30d,
           ROW_NUMBER() OVER (
               PARTITION BY te.event_id
               ORDER BY f.computed_at DESC
           ) AS rn
    FROM   training_events te
    LEFT   JOIN user_spend_30d f
           ON f.user_id = te.user_id
          AND f.computed_at <= te.event_time
)
SELECT event_id, user_id, event_time, label, spend_30d
FROM   ranked
WHERE  rn = 1;

For each event, the LEFT JOIN finds all feature rows for that user computed at or before the event’s time; ROW_NUMBER picks the most recent. The result: each training row has the feature value as it was known at training time. No leakage from the future.

This is what Feast, Tecton, Hopsworks automate. Without point-in-time joins, your training set silently uses future information — the user’s “30-day spend” includes purchases that happened after the event you’re trying to predict. Models trained on leaky data look amazing offline and fail in production.

Snapshot frequency vs storage

The trade-off in feature stores: how often do you compute and store feature values?

  • Daily snapshot per user — small storage; misses intra-day events. Fine for slow-moving features (lifetime spend, country).
  • Per-event update — full fidelity; more storage and compute. Necessary for fast-moving features (clicks in last hour).
  • Batch + streaming hybrid — daily batch updates baseline features; streaming pipeline appends recent events. The current best practice.

Online vs offline feature stores

Two consumption patterns, two storage layouts:

  • Offline store — full historical feature values, used to build training sets. Lives in the warehouse (Snowflake, BigQuery, Postgres). Optimized for point-in-time joins over millions of training events.
  • Online store — latest feature value per entity, used for serving. Lives in a key-value store (Redis, DynamoDB) or Postgres. Optimized for sub-millisecond per-key lookup.

The same SQL pipeline writes to both. Skew between them — different transformation logic between offline and online — is one of the most common production ML bugs. Modern feature stores explicitly enforce “single transformation, two destinations” to prevent this.

A worked example — building a feature table

-- Source: orders
-- Goal: feature `spend_30d` for each user, recomputed daily

INSERT INTO user_spend_30d (user_id, computed_at, value)
SELECT u.id,
       d::timestamptz AS computed_at,
       COALESCE(SUM(o.total) FILTER (
           WHERE o.placed >= d::date - INTERVAL '30 days'
             AND o.placed <  d::date
       ), 0) AS value
FROM   users u
CROSS  JOIN generate_series(
              CURRENT_DATE - INTERVAL '90 days',
              CURRENT_DATE,
              '1 day'::interval
            ) AS d
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, d
ON CONFLICT (user_id, computed_at) DO UPDATE
    SET value = EXCLUDED.value;

For each user × day in the past 90 days, compute the trailing-30-day spend. Idempotent (ON CONFLICT DO UPDATE). Incremental: rerunning a recent window updates only those rows.

Common pitfalls

  • Forgetting point-in-time correctness. Joining the current feature value to historical training events. Leakage. Production model fails.
  • Joining facts to facts. Star joins are fact ⨝ dimension. Joining two facts (orders ⨝ events) without aggregating one first usually causes row multiplication.
  • Time-zone sloppiness in feature pipelines. “Daily” features computed in UTC vs user-local time give different values. Pick one, document it.
  • Online/offline skew. Two different code paths compute the same feature differently. Production model sees one feature distribution in training and another at inference.
  • No backfill story. Adding a new feature requires recomputing 90 days of history before training. Pipelines must support backfills (idempotent upserts over date spines).
  • Slowly Changing Dimensions Type 1. Overwriting a dimension loses history. Old facts then join to “wrong” current state. Use SCD2 for anything you’ll join on historically.

Production patterns for ML — three layers

1. Raw events. Append-only, partitioned by time. Postgres or warehouse.

2. Feature tables. One table per feature group, keyed by (entity_id, computed_at). Versioned via computed_at. Materialized in the warehouse via dbt or a feature framework.

3. Online cache. Latest feature value per entity, key-value store (Redis, DynamoDB). Refreshed from the warehouse every N minutes (or streamed).

The training set is built by point-in-time joining feature tables to event labels in the warehouse. The serving path reads from the online cache. The pipeline writing to both is the contract that prevents skew.

Resources