End-to-End Multi-Source Surge Pricing

Demand + driver supply + weather, traffic, and events → explainable pricing decision

Learning Outcomes and Timeline

• Explain why a pricing decision needs multiple live sources rather than demand alone.
• Join demand, supply, and context streams into zone-level state.
• Read and challenge an explainable surge-pricing formula.
• Use a dashboard to separate a genuine demand shock from a data-quality problem.
• Recommend pricing guardrails based on customer, driver, and regulatory impact.

Section 1: The Business Case

A ride platform must balance two competing outcomes: riders need cars quickly, while drivers need a reason to move toward high-demand zones. A multiplier can help rebalance the market—but a weak rule can create unfair prices, false scarcity, or reputational risk.

The 60-Second Scenario

Section 2: Architecture and Files

ride_requests_17.csv      driver_supply_17.csv      zone_context_17.csv
          |                         |                         |
          v                         v                         v
ride_request_producer   driver_supply_producer     context_producer
          |                         |                         |
ride-requests-l17       driver-supply-l17          zone-context-l17
          \_________________________|_________________________/
                                    |
                                    v
                      surge_pricing_consumer_17.py
                       10-second zone state + formula
                                    |
                                    v
                 MySQL: sda_course_l17 (4 audit tables)
                                    |
                                    v
                         Grafana pricing dashboard

Download Project Files

Section 3: Pricing Logic and Guardrails

For each zone, the consumer counts requests received in the last 10 seconds, combines the latest driver supply and context, then calculates:

Section 4: Run the End-to-End Pipeline

1. Start Existing Infrastructure

docker start sda-mysql grafana

# Kafka and ZooKeeper should also be running from earlier lectures.
pip install kafka-python mysql-connector-python

If MySQL or Grafana has not been created, use the Docker setup from Lecture 16. The consumer creates the new database and grants the existing grafana user read-only access.

2. Generate the Three Sources

python generate_surge_data_17.py

# Expected:
# Ride requests : 735
# Supply updates: 300
# Context rows  : 300

Every run receives a timestamp-based run ID, so event primary keys do not conflict with previous classroom runs.

3. Optional Clean Reset

docker exec -it sda-mysql mysql -uroot -padmin \
  -e "DROP DATABASE IF EXISTS sda_course_l17;"

4. Start Four Terminals

Launch the three producers close together. Each groups records by scenario second, publishes that second's events, then waits one real second. The scenario therefore unfolds live for approximately one minute.

5. Verify MySQL

docker exec sda-mysql mysql -uroot -padmin -e "
SELECT COUNT(*) AS requests FROM sda_course_l17.ride_requests;
SELECT COUNT(*) AS decisions FROM sda_course_l17.pricing_decisions;
SELECT zone, MAX(final_multiplier) AS peak
FROM sda_course_l17.pricing_decisions
GROUP BY zone ORDER BY peak DESC;"

Section 5: Grafana Pricing Control Room

1. Add a MySQL datasource named MySQL — Lecture 17 Surge.
2. Host: sda-mysql:3306 | Database: sda_course_l17.
3. User/password: grafana / grafana; disable TLS for the local lab.
4. Create dashboard Surge Pricing Control Room — Lecture 17.
5. Set range to Last 5 minutes and refresh to 5s.

Variable — Zone

SELECT DISTINCT zone AS __text, zone AS __value
FROM pricing_decisions
ORDER BY zone;

Enable Multi-value and Include All. Use custom All value .*.

Panel A — Current Multiplier by Zone (Bar Gauge)

SELECT p.zone, p.final_multiplier AS value
FROM pricing_decisions p
JOIN (
  SELECT zone, MAX(decision_id) AS latest_id
  FROM pricing_decisions
  GROUP BY zone
) latest ON p.decision_id = latest.latest_id
WHERE p.zone REGEXP '${zone:regex}'
ORDER BY value DESC;

Panel B — Multiplier over Time (Time Series)

SELECT
  decision_time AS time,
  final_multiplier AS value,
  zone AS metric
FROM pricing_decisions
WHERE $__timeFilter(decision_time)
  AND zone REGEXP '${zone:regex}'
ORDER BY decision_time;

Panel C — Demand versus Supply (Time Series)

SELECT
  decision_time AS time,
  demand_window AS `Requests in 10s`,
  available_drivers AS `Available Drivers`
FROM pricing_decisions
WHERE $__timeFilter(decision_time)
  AND zone = '${zone}'
ORDER BY decision_time;

For this panel, select one zone rather than All.

Panel D — Latest Factor Breakdown (Table)

SELECT
  zone,
  demand_supply_ratio,
  demand_adjustment,
  weather_adjustment,
  traffic_adjustment,
  event_adjustment,
  final_multiplier,
  reason
FROM pricing_decisions
WHERE zone REGEXP '${zone:regex}'
ORDER BY decision_id DESC
LIMIT 20;

Panel E — Pricing Decision Audit (Table)

SELECT
  decision_time,
  phase,
  zone,
  demand_window,
  available_drivers,
  raw_multiplier,
  final_multiplier,
  reason
FROM pricing_decisions
WHERE $__timeFilter(decision_time)
  AND zone REGEXP '${zone:regex}'
ORDER BY decision_time DESC
LIMIT 50;

Section 6: Interpret, Challenge, and Govern

What Students Should Observe

1. Normal phase multipliers remain close to 1.0×.
2. The CBD and Railway Station respond more strongly than other zones.
3. The final multiplier moves gradually because each decision is rate-limited.
4. Recovery lags the source change; smoothing trades responsiveness for stability.
5. The audit table explains whether demand, weather, traffic, or an event contributed.

Policy Challenge — Change One Rule

Each group chooses one change, predicts the effect, edits surge_pricing_logic_17.py, reruns the scenario, and compares the dashboard:

• Reduce the cap from 2.5× to 1.8×.
• Remove weather as a direct price adjustment.
• Increase the demand weight from 0.35 to 0.50.
• Slow the maximum change from ±0.20 to ±0.10.
• Require a minimum of 10 requests before any multiplier above 1.0×.

Fairness and Governance Discussion

60-Second Management Briefing

1. Situation: Which zone changed, and when?
2. Evidence: What happened to demand, supply, and context?
3. Decision: Keep, cap, or suspend the multiplier?
4. Guardrail: What customer or driver risk must be monitored?

Troubleshooting