OLTP
Understand the operational source and its constraints.
OPERATIONAL DATA → MACHINE LEARNING
OLTP records what happened. Feature engineering reconstructs what was knowable.
The path from a checkout transaction to a model feature is not a SELECT statement placed beside production. It is a time-aware data system: capture change, preserve history, define entities and events, compute reproducibly, and deliver the same meaning to training and prediction.
History
Preserve change instead of seeing only the latest state.
Time
Prevent future information from entering training rows.
Feature
Publish a tested, reusable model input.
01 · THE COMPLETE PATH
Do not confuse the source of truth with the system for learning from it.
OLTP protects business transactions. The analytical and ML path preserves history and reorganizes it for observation. Each stage has a different responsibility.
02 · TWO DIFFERENT WORKLOADS
OLTP and feature computation optimize for different questions.
The database can technically execute an analytical query. That does not mean production is the right place to run repeated full-history joins and rolling windows.
Can this transaction complete correctly now?
- Short reads and writes
- Current operational state
- Concurrency and constraints
- Low latency per transaction
≠
What pattern was visible before each decision?
- Large scans and joins
- Historical states and windows
- Repeatable snapshots
- Batch or streaming aggregation
Why not compute everything on OLTP?
Heavy feature queries compete with customer transactions for CPU, memory, I/O, locks and connection capacity. They may also see only current state, making historical training impossible to reproduce. A read replica reduces contention but does not automatically solve history, semantics or point-in-time correctness.
03 · STATE IS NOT HISTORY
An update can erase the evidence a model needs.
Operational tables often keep the latest address, status or balance. Model training may need to know what those values were at an earlier prediction time.
04 · THREE CLOCKS
“When” has more than one meaning in a data pipeline.
Late-arriving events make event time and ingestion time diverge. Feature correctness depends on which clock the model could actually observe.
Event time
When the business event occurred: purchase at 09:07.
Ingestion time
When the data platform received it: perhaps 09:14 after a network delay.
Prediction time
When the model decision was made: 09:10.
The difficult case
An event occurred at 09:07 but arrived at 09:14. Should a model prediction at 09:10 have access to it? For an online system, usually no—the platform had not received it. Historical features may therefore need both event_time and available_at/ingestion_time.
05 · CAPTURE CHANGE DELIBERATELY
CDC moves changes; the data model gives those changes meaning.
Change Data Capture can read database logs and emit inserts, updates and deletes with low source overhead. It does not by itself define entities, deduplicate business events or choose a feature window.
OLTP LOG
→UPDATE orders SET status='paid'CHANGE EVENT
→before: pending
after: paid
source_ts: 09:07HISTORICAL MODEL
valid_from · valid_to
event_time · loaded_atLog-based CDC
Captures changes from transaction logs with limited query load; requires connector operations, ordering and recovery design.
Incremental extract
Queries rows newer than a watermark. Simpler, but timestamp quality, updates, deletes and clock boundaries require care.
Snapshot
Copies state at intervals. Useful for reconciliation but expensive and unable to reveal every transition between snapshots.
06 · SQL LAB: POINT-IN-TIME FEATURES
Join each prediction only to facts available before it.
This simplified SQL computes customer activity for the 30 days before each prediction. Production implementations must address late data, time zones, duplicate events and query scale.
-- Grain: one row per customer per prediction_time
SELECT
p.customer_id,
p.prediction_time,
COUNT(o.order_id) AS orders_30d,
COALESCE(SUM(o.amount), 0) AS spend_30d,
MAX(o.ordered_at) AS last_order_time
FROM ml.prediction_events AS p
LEFT JOIN history.orders AS o
ON o.customer_id = p.customer_id
AND o.ordered_at >= p.prediction_time - INTERVAL '30 days'
AND o.ordered_at < p.prediction_time
AND o.available_at <= p.prediction_time
AND o.status = 'completed'
GROUP BY p.customer_id, p.prediction_time;t − 30 dayseligible historyprediction time (t)
future: forbidden
Two safeguards
ordered_at < prediction_time prevents future business events; available_at <= prediction_time prevents late-arriving data from being treated as if it had arrived earlier.07 · FAILURE MODES
A pipeline can run successfully and still produce dishonest features.
Technical success means the query completed. Data correctness means the result represents what the model was allowed to know.
Production contention
Feature scans slow customer transactions or exhaust replicas and connections.
Current-state bias
The latest customer segment is copied into old training rows.
Target leakage
Cancellation or fraud review occurring after prediction enters the feature set.
Duplicate change events
Retry or replay counts one business event more than once.
Entity mismatch
Customer, account and household IDs are joined as if they represent the same entity.
Training-serving skew
Notebook logic differs from the online service implementation.
08 · ENGINEERING DECISIONS
Separate workloads, preserve time, and make the handoff testable.
There is no single mandatory architecture. The principles remain: protect operations, retain sufficient history, state feature semantics and reproduce values at the required time.
QuestionDesign evidence
Can features read OLTP directly?Only for controlled, low-impact cases with measured load and reproducibility. Prefer a replica or analytical copy for repeated computation.
CDC, extract or snapshot?Choose from latency, delete/update capture, source capability, recovery, volume and operational skill—not fashion.
Warehouse model or feature store?A tested warehouse model may be enough for batch ML. Add a feature store when reuse, historical retrieval, online parity and governance justify platform cost.
THE CENTRAL IDEA
The operational database tells us what is true now. Good feature engineering preserves what was knowable then.
- Protect OLTP workload
- Capture change
- Preserve event time
- Model entities explicitly
- Test point-in-time joins
- Reconcile source and features