Dynamic pricing is not a new idea. Airlines have been doing it for decades. What has changed is the granularity — modern systems reprice every few seconds in response to signals that did not exist when batch ETL was state of the art. Ride-sharing zones, hotel inventory blocks, e-commerce SKUs: all of them now reprice faster than a human can refresh a page.
The hard part of dynamic pricing is the model. Getting the demand elasticity right, choosing the right optimization objective, managing the feedback loops between prices and observed demand — that is months of work. But there is a subtler prerequisite problem that kills pricing accuracy before the model ever runs: the features are stale.
This post is about that problem and how to solve it with streaming SQL.
What Makes Pricing Dynamic
Pricing signals fall into two categories based on how quickly they change.
Slow signals are things the model could reasonably read from a data warehouse or a cached API response: historical price elasticity by segment, competitor base rates, seasonal indices, long-run average demand by hour of week. These change on the order of hours or days. A nightly batch job is fine.
Fast signals are the ones that determine whether right now is a surge moment or a slack moment: how many drivers are available in a two-kilometer radius, how many booking requests have come in during the past ten minutes, whether cancellations are accelerating, how far away the concert ends. These change on the order of seconds to minutes. A fifteen-minute batch job is not fine.
The consequence of stale fast signals is predictable. Your surge pricing fires fifteen minutes after the surge has already passed, charging customers who booked at normal demand levels. Or it misses the surge entirely, leaving revenue on the table. Either way, the model is making decisions based on a world that no longer exists.
The fix is to compute fast signals as a continuously updated materialized view over the event stream. Every booking event, every driver status change, every competitor price update refreshes the signal automatically. The pricing model always reads current state.
Key Real-Time Pricing Features
Before looking at SQL, it helps to name the signals concretely. Four categories cover most dynamic pricing use cases.
Supply-demand ratio is the foundational signal. In ride-sharing it is available drivers divided by pending requests per zone. In hotels it is remaining inventory divided by arrival-rate. In airlines it is remaining seats divided by booking rate in the past window. A ratio above one means supply exceeds demand; the model should probably not surge. A ratio below one means demand is outpacing supply; the model has pricing power.
Booking velocity and acceleration tells you not just the current ratio but which direction it is moving. A supply-demand ratio of 0.8 is very different if bookings have been accelerating for the past twenty minutes versus decelerating. Acceleration is the derivative of velocity — the difference between bookings in the most recent window and bookings in the prior window of equal length.
Cancellation pressure is the supply-side mirror of booking velocity. A spike in cancellations compresses effective supply fast. In hotel pricing, a wave of last-minute cancellations on a sold-out night creates genuine urgency. In ride-sharing, driver cancellations in a zone are an early signal of a coming supply crunch. Models that ignore cancellation events and only look at current available supply are always one step behind.
Time pressure captures the fundamental asymmetry of perishable inventory. A hotel room tonight is worth more than a hotel room next Tuesday, and a flight seat two hours before departure is worth something completely different than the same seat two weeks out. Time-to-event features need to be computed at query time, but the underlying event schedule needs to be available in a low-latency store.
Data Architecture
You need three event streams to build these features properly.
Supply events describe the state of inventory. For ride-sharing, these are driver status transitions: going available, going offline, accepting a trip, completing a trip. For hotels, they are inventory updates: room released, room sold, room cancelled. The key property is that they are edge events — you get a signal when something changes, not a heartbeat every second.
Demand events describe inbound requests. Booking requests, search queries with high purchase intent, quote requests. For ride-sharing, a trip request is a demand event the moment a rider taps the app, before any driver accepts.
Competitor price events are the hardest to collect but matter enormously in markets with price-sensitive customers. These typically come from web scrapers or rate-shopping APIs that push updates whenever a competitor changes price. Treat them as an event stream and join them to your own pricing decisions.
The join key across all three streams is usually a (product_id, zone_id, timestamp) tuple. In ride-sharing that is a (zone_id, timestamp) pair. In hotels it is a (property_id, room_type_id, stay_date, timestamp). The materialized views aggregate over these keys in rolling time windows.
SQL Implementation
Supply-Demand Balance
The most fundamental view tracks available supply against pending demand in each zone, refreshed continuously over a five-minute rolling window.
-- Supply-demand balance by zone (updated continuously)
CREATE MATERIALIZED VIEW zone_supply_demand AS
SELECT
zone_id,
city,
COUNT(*) FILTER (WHERE entity_type = 'driver' AND status = 'available') AS available_supply,
COUNT(*) FILTER (WHERE entity_type = 'request' AND status = 'pending') AS pending_demand,
COUNT(*) FILTER (WHERE entity_type = 'request' AND status = 'pending')::float /
NULLIF(COUNT(*) FILTER (WHERE entity_type = 'driver' AND status = 'available'), 0) AS demand_supply_ratio
FROM supply_demand_events
WHERE event_time >= NOW() - INTERVAL '5 minutes'
GROUP BY zone_id, city;
The NULLIF guard on the denominator handles the case where supply is zero — in that situation, the demand-supply ratio is undefined, not infinite, and you want your pricing model to handle that explicitly rather than receiving a division error.
The five-minute window is a tuning parameter. A shorter window makes the ratio more reactive but also more noisy. A longer window is more stable but lags the market. In practice, ride-sharing companies use different window lengths for different model features — a one-minute ratio for the surge trigger and a fifteen-minute ratio as a smoothed context feature.
Booking Velocity and Acceleration
This view computes both the raw count and the acceleration signal — whether demand is growing or shrinking.
-- Booking velocity (how fast are bookings accelerating?)
CREATE MATERIALIZED VIEW booking_velocity AS
SELECT
product_id,
COUNT(*) AS bookings_last_1h,
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '15 minutes') AS bookings_last_15min,
COUNT(*) FILTER (WHERE event_time BETWEEN NOW() - INTERVAL '30 minutes' AND NOW() - INTERVAL '15 minutes') AS bookings_prior_15min,
-- Acceleration: is demand growing or shrinking?
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '15 minutes') -
COUNT(*) FILTER (WHERE event_time BETWEEN NOW() - INTERVAL '30 minutes' AND NOW() - INTERVAL '15 minutes') AS demand_acceleration
FROM booking_events
WHERE event_time >= NOW() - INTERVAL '1 hour'
GROUP BY product_id;
The demand_acceleration column is the key signal. A value of +40 means forty more bookings in the current fifteen-minute window than in the prior one — demand is accelerating. A value of -20 means demand is decelerating. Combined with the supply-demand ratio, this tells a much richer story: a ratio of 0.7 with positive acceleration is a different pricing situation than a ratio of 0.7 with negative acceleration.
Cancellation Pressure
Track cancellation spikes separately from the supply-demand view. Cancellations often cluster — a weather event or a transport disruption triggers many cancellations at once — and you want to detect that pattern before it shows up in the available inventory count.
-- Cancellation pressure: spike detection over rolling windows
CREATE MATERIALIZED VIEW cancellation_pressure AS
SELECT
product_id,
zone_id,
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '15 minutes') AS cancellations_15min,
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '1 hour') AS cancellations_1h,
-- Normalized rate: cancellations per booking in the same window
COUNT(*) FILTER (WHERE event_time >= NOW() - INTERVAL '15 minutes')::float /
NULLIF(
(SELECT COUNT(*) FROM booking_events b
WHERE b.product_id = cancellation_events.product_id
AND b.event_time >= NOW() - INTERVAL '15 minutes'),
0
) AS cancellation_rate_15min
FROM cancellation_events
WHERE event_time >= NOW() - INTERVAL '1 hour'
GROUP BY product_id, zone_id;
Time-to-Event Features
Time pressure features are a function of the current timestamp, so they do not need a rolling window — but they do need the event schedule to be available in a low-latency store that the pricing service can query directly.
-- Time pressure: minutes until departure or check-in
CREATE MATERIALIZED VIEW time_pressure AS
SELECT
p.product_id,
p.event_time AS departure_or_checkin,
EXTRACT(EPOCH FROM (p.event_time - NOW())) / 60 AS minutes_to_event,
CASE
WHEN EXTRACT(EPOCH FROM (p.event_time - NOW())) / 60 < 30 THEN 'critical'
WHEN EXTRACT(EPOCH FROM (p.event_time - NOW())) / 60 < 120 THEN 'high'
WHEN EXTRACT(EPOCH FROM (p.event_time - NOW())) / 60 < 480 THEN 'medium'
ELSE 'low'
END AS time_pressure_bucket
FROM product_schedule p
WHERE p.event_time >= NOW()
AND p.event_time <= NOW() + INTERVAL '24 hours';
The pressure bucket is useful because most pricing models treat time pressure as a categorical rather than a continuous feature — the relationship between time-to-event and price is not linear, and bucketing captures the sharp inflection points at typical decision thresholds.
Feeding Features into the Pricing Model
At request time, the pricing service needs to assemble features from multiple materialized views and pass them to the model in a single low-latency lookup.
The pattern is a fan-out read: for a given (zone_id, product_id) pair, read from zone_supply_demand, booking_velocity, cancellation_pressure, and time_pressure simultaneously. Because all four views are maintained incrementally by the stream processor, each read is a point query on a pre-aggregated result, not an on-the-fly aggregation. Latency is in the single-digit milliseconds range.
async def assemble_pricing_features(zone_id: str, product_id: str) -> dict:
supply_demand, velocity, cancellation, time_p = await asyncio.gather(
db.fetchrow(
"SELECT available_supply, pending_demand, demand_supply_ratio "
"FROM zone_supply_demand WHERE zone_id = $1", zone_id
),
db.fetchrow(
"SELECT bookings_last_15min, bookings_prior_15min, demand_acceleration "
"FROM booking_velocity WHERE product_id = $1", product_id
),
db.fetchrow(
"SELECT cancellations_15min, cancellation_rate_15min "
"FROM cancellation_pressure WHERE product_id = $1", product_id
),
db.fetchrow(
"SELECT minutes_to_event, time_pressure_bucket "
"FROM time_pressure WHERE product_id = $1", product_id
),
)
return {
"available_supply": supply_demand["available_supply"],
"demand_supply_ratio": supply_demand["demand_supply_ratio"],
"demand_acceleration": velocity["demand_acceleration"],
"cancellation_rate_15min": cancellation["cancellation_rate_15min"],
"minutes_to_event": time_p["minutes_to_event"],
"time_pressure_bucket": time_p["time_pressure_bucket"],
}
The assembled feature vector is then passed to the pricing model — a gradient boosted tree, a neural network, or a simpler rule-based system — which returns a price multiplier or an absolute price.
Handling Feature Staleness
Every production pricing system eventually encounters a broken signal. The competitor price scraper goes down. The driver telemetry stream falls behind. The booking event consumer hits a lag spike. You need a strategy for each case.
Set freshness expectations explicitly. Each materialized view should carry a last_updated timestamp. The pricing service should check this timestamp before using the feature and fall back to a safe default if the signal is too stale. What counts as too stale depends on the feature: a supply-demand ratio that is two minutes old is probably fine; one that is thirty minutes old should not trigger surge pricing.
Define safe defaults per feature. For supply-demand ratio, the safe default when the signal is unavailable is typically 1.0 — assume balance and do not surge. For time pressure, the safe default is to read directly from the product schedule table, which is a slow-moving source and does not need the stream. For competitor prices, fall back to the last known price and flag the pricing decision as low-confidence.
Monitor lag actively. Stream processing systems expose consumer group lag as a metric. Wire this to your alerting stack and page before the lag reaches the staleness threshold that degrades pricing quality. A pricing outage is a revenue outage; treat it accordingly.
-- Freshness monitoring: alert when any feature view is lagging
SELECT
view_name,
MAX(event_time) AS latest_event_processed,
NOW() - MAX(event_time) AS lag,
CASE WHEN NOW() - MAX(event_time) > INTERVAL '2 minutes' THEN 'STALE' ELSE 'OK' END AS status
FROM feature_view_watermarks
GROUP BY view_name;
A/B Testing Pricing Models with Streaming Features
Pricing model A/B tests require careful treatment because prices create feedback loops. If model A charges more in zone 5 and demand drops, you observe lower conversion — but you cannot tell whether that reflects genuine price elasticity or selection bias from the routing logic.
The standard approach is to randomize at the user session level and ensure the feature pipeline feeds the same real-time signals to both variants. The mistake is to run separate feature pipelines per variant — that introduces confounders where one model is seeing slightly staler data than the other.
With streaming SQL, the right architecture is a single set of materialized views consumed by all variants. The variant assignment happens in the pricing service at request time; the feature values are read from shared views. This guarantees that differences in model output are due to the model, not to differences in input data freshness.
-- Log pricing decisions with variant assignment for A/B analysis
CREATE MATERIALIZED VIEW pricing_experiment_results AS
SELECT
pe.session_id,
pe.variant_id,
pe.price_offered,
pe.demand_supply_ratio_at_decision,
pe.demand_acceleration_at_decision,
CASE WHEN b.session_id IS NOT NULL THEN 1 ELSE 0 END AS converted,
pe.decision_time
FROM pricing_experiment_log pe
LEFT JOIN booking_events b ON b.session_id = pe.session_id
WHERE pe.decision_time >= NOW() - INTERVAL '7 days';
Querying this view gives you live experiment metrics — conversion rate by variant, revenue per request by variant — without waiting for a nightly batch run. You can detect a bad variant and kill it within hours instead of days.
FAQ
Do I need a separate feature store, or can the materialized views serve as my feature store?
For pricing features that are always computed at request time against current market state, materialized views in a streaming database are a complete solution. You do not need a separate feature store layer. Feature stores add value when you need point-in-time correct feature retrieval for model training — the ability to reconstruct what the feature value was at any historical moment. For that use case, log the feature values alongside each pricing decision and use those logs for training, rather than trying to replay the stream.
How do I handle the cold start problem when a new product or zone has no historical data?
Treat cold start as a separate case in the pricing service. When a product_id returns null from any of the velocity or pressure views, fall back to a hierarchical prior — the average for the zone, or the average for the city, or a global base rate. Streaming SQL makes this easy: add a COALESCE on the zone-level aggregate as a fallback for product-level nulls.
What is the right window length for the booking velocity feature?
It depends on your typical booking rate. A window should contain enough events to be statistically meaningful. If a product typically gets two bookings per hour, a five-minute window will often have zero events and the velocity signal is meaningless. Start with a window that contains at least twenty to thirty events at typical demand levels, then experiment.
How does this approach handle demand that is highly seasonal within a day?
Include hour-of-week as a static feature alongside the real-time signals. The model learns that a demand-supply ratio of 0.8 at 8 PM on a Friday is a different situation than the same ratio at 2 PM on a Tuesday. Real-time signals tell you where you are relative to the current baseline; the model's learned hour-of-week coefficients provide the baseline context.
Can I use these features for price elasticity estimation, not just surge pricing?
Yes, but you need to be careful about endogeneity. If your pricing model has been running for a while, the demand you observe is already shaped by the prices you charged. Estimating elasticity from observational data under dynamic pricing requires instrumental variable techniques or natural experiments. The streaming feature layer is neutral here — it gives you accurate signals, but interpreting those signals for elasticity estimation is a causal inference problem, not a feature engineering problem.
Dynamic pricing at scale is an infrastructure problem as much as it is a modeling problem. The models are only as good as the features they consume, and features are only as good as the pipeline that computes them. Streaming SQL collapses the gap between event time and feature availability — the supply-demand ratio the model reads reflects what is happening now, not what happened fifteen minutes ago. That is the prerequisite for pricing that actually responds to the market.
