Epic - Analytics-Driven Warehouse Tables

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: #512
[x] 1. monthly aggregation of fct_daily_scheduled_trips to route-level Context: #512 Jarvus Ticket: https://github.com/cal-itp/data-infra/issues/2234 Exploratory analysis follow-up: #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: ~~https://github.com/cal-itp/data-infra/issues/2237~~ Note: we have enough progress on this from #811
[x] 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: https://github.com/cal-itp/data-infra/issues/2382

GTFS RT speeds

Exploratory Epic: #592

GTFS RT vs Schedule

[x] 1. Daily table for scheduled trips with available RT vehicle position data aggregated to route-level Context: #263 POC v1 warehouse: https://github.com/cal-itp/data-infra/pull/1708 Jarvus Ticket: https://github.com/cal-itp/data-infra/issues/2236
[x] 2. Daily table for scheduled to RT trip alignment with summary stats from RT Jarvus Ticket: ~~https://github.com/cal-itp/data-infra/issues/2381~~ related work on fct_observed_trips: https://github.com/cal-itp/data-infra/issues/2412 https://github.com/cal-itp/data-infra/issues/2413

GTFS RT predictions

[x] 1. #709

cal-itp / data-analyses

Epic - Analytics-Driven Warehouse Tables #634