Feature groups, backfill, and drift
Materialize customer purchase features, wait for a bounded backfill, and inspect the resulting rows.
The practical problem and model
A customer model often needs reusable aggregates such as purchase totals and counts. A feature group gives those calculations a named definition and an offline destination, so training and other consumers can read a consistent feature shape.
The example aggregates five events for three customers, excludes a refund, and materializes the results. Backfill recomputes a bounded historical interval; drift monitoring compares feature behavior across observations.
The names customer_30d and total_spend_30d are illustrative. The supplied SQL does not implement a rolling 30-day filter: the backfill covers a fixed April interval, while the materialization query aggregates matching purchases in its input. A real rolling-window definition needs an explicit time predicate.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: Feature Groups — Offline Materialisation + Backfill + Drift. Download the complete SQL.
The feature store must be enabled and the tenant background identity must have the required source and destination access. It defaults to enabled unless explicitly disabled. See background workloads.
Step 1: Create events and the offline target
The target is created explicitly with the feature columns and epoch-millisecond event timestamp. The refund must not contribute to purchase aggregates.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.features;
CREATE OR REPLACE TABLE tutorial.features.events (
customer_id BIGINT,
event_ts TIMESTAMP,
event_type VARCHAR,
amount DOUBLE
);
INSERT INTO tutorial.features.events VALUES
(1, TIMESTAMP '2026-04-01 10:00', 'purchase', 50.0),
(1, TIMESTAMP '2026-04-02 11:00', 'purchase', 80.0),
(2, TIMESTAMP '2026-04-01 09:00', 'refund', 20.0),
(2, TIMESTAMP '2026-04-03 14:00', 'purchase', 30.0),
(3, TIMESTAMP '2026-04-02 16:00', 'purchase', 60.0);
CREATE OR REPLACE TABLE tutorial.features.cust_feat_offline (
customer_id BIGINT,
total_spend_30d DOUBLE,
purchase_count BIGINT,
event_ts BIGINT
);
Step 2: Register the reusable calculation
The refresh interval is 300 seconds. The offline table and selected output column names are part of the materialization contract.
CREATE OR REPLACE FEATURE GROUP tutorial.features.customer_30d (customer_id)
MATERIALIZED FROM (
SELECT
customer_id,
SUM(amount) AS total_spend_30d,
COUNT(*) AS purchase_count,
CAST(EXTRACT(EPOCH FROM MAX(event_ts)) * 1000 AS BIGINT) AS event_ts
FROM tutorial.features.events
WHERE event_type = 'purchase'
GROUP BY customer_id
)
REFRESH EVERY 300s
OFFLINE TABLE tutorial.features.cust_feat_offline;
SHOW FEATURE GROUPS;
Step 3: Wait for backfill and inspect actual output
The fixed interval is April 1–6, 2026 in UTC. wait = true waits for completion so the final read does not race the background work. Inspect progress and failures as well as the target rows.
BACKFILL FEATURE GROUP tutorial.features.customer_30d
FROM 1775001600000 TO 1775433600000
WITH (
chunk_size_ms = 432000000,
wait = true
);
SHOW BACKFILL PROGRESS tutorial.features.customer_30d;
SHOW FEATURE DRIFT tutorial.features.customer_30d;
SELECT * FROM tutorial.features.cust_feat_offline ORDER BY customer_id;
What to check in the results
Expect one completed backfill chunk and zero failed chunks. The offline table should contain exactly:
| Customer | Purchase total | Purchase count | Event timestamp (epoch ms) |
|---|---|---|---|
| 1 | 130 | 2 | 1775127600000 |
| 2 | 30 | 1 | 1775224800000 |
| 3 | 60 | 1 | 1775145600000 |
A drift result needs a meaningful baseline and subsequent observations. An empty initial drift listing does not prove stability. Separate lifecycle validation exercised scheduled refresh, drift boundaries, and restart recovery beyond this short SQL sequence.
Adapt it to real data
Define window boundaries, late-event handling, entity keys, and feature timestamps before operational use. For historical training, join features as they were available at the prediction time rather than using later information.
Size backfill chunks to the source workload and inspect failed chunks before retrying. Grant the background identity access only to the sources and output tables required by the feature definition.