Epic - Analytics-Driven Warehouse Tables

[tiffanychu90]

After receiving a research request, use this template to plan and track your work. Be sure to also add the appropriate project-level label to this issue (eg gtfs-rt, DLA).

Epic Information - Analytics-Driven Warehouse Tables

Summary

Track requests and exploratory work related to iterating over analyst-driven dbt warehouse models.

Detailed

• Will open epics to do exploratory work within each distinct pipeline and distribute research tasks and track findings.
• This acts as high-level project management to track Jarvus tickets

Issues

GTFS schedule

• Exploratory Epic: Epic - GTFS Schedule Aggregations #512

• 1. monthly aggregation of fct_daily_scheduled_trips to route-level
  Context: Epic - GTFS Schedule Aggregations #512
  Jarvus Ticket: User Story: aggregated GTFS schedule service hours by month-day_type-time_of_day data-infra#2234
  Exploratory analysis follow-up: Research Request - Transit service and speeds by CalEnviroScreen tracts  #673

• 2. New table at shape-stop level
  Context: RT stop-to-stop segments for v2. Since stop_sequence is important, we need to keep it at shape-level. We always want a stop segments version of speeds, since that remains fully GTFS world. Our road segments would leave GTFS.
  Jarvus Ticket: User Story: Python dbt model to produce shape-stop grain table data-infra#2237
  Note: we have enough progress on this from Research Request - Plug in gaps in stop segments #811

• 3. monthly aggregation of fct_monthly_routes and attach a shape for that route
  Context: analyst uses monthly aggregated service_hours for a route....need a way to select the right shape_id to represent it. Pick the shape_id for that route that has the most trips or most days present that month as the line geometry to visualize.
  Jarvus Ticket: User Story: monthly aggregation for mart_gtfs.fct_daily_scheduled_shapes data-infra#2382

GTFS RT speeds

• Exploratory Epic: Epic - Daskify RT segment speeds #592

GTFS RT vs Schedule

• 1. Daily table for scheduled trips with available RT vehicle position data aggregated to route-level
  Context: Research Task - RT Trips Ran vs Scheduled  #263
  POC v1 warehouse: Add RT VP vs Sched Table data-infra#1708
  Jarvus Ticket: User Story: daily schedule vs RT number and percent of trips data-infra#2236
• 2. Daily table for scheduled to RT trip alignment with summary stats from RT
  Jarvus Ticket: User Story: add summary stats for to mart_gtfs.fct_observed_trips data-infra#2381
  related work on fct_observed_trips:
  Add count of messages to fct_observed_trips data-infra#2412
  GTFS RT: Investigate trips with different identifier attributes but same trip ID  data-infra#2413

GTFS RT predictions

• 1. Research Request - RT Predictions / Real-Time Information POC metrics #709

[tiffanychu90]

Closing - winding down Jarvus contract