A transit ridership fact table must support chronological trip loads and frequent lookups by route_id for ops queries. How should the indexes be designed?

Transit ridership facts typically load in chronological order and are queried both by date ranges and by route for operations. A clustered index on trip_date physically orders rows to favor date-oriented loads and range scans used in ridership dashboards, while a nonclustered index on route_id provides a selective path for ops lookups that ask “show today’s trips for route X” without scanning the entire day’s leaf pages by date alone. Together they support mixed access without pretending one structure answers every municipal question. Leaving the table as a heap with only a nonclustered date index fails to give the table a durable clustering order for chronological loads and still leaves route lookups dependent on residual predicates or lookups that do not scale cleanly. Clustering solely on route_id optimizes route locality but scatters chronological inserts and weakens date-range scans that dominate ridership loads. Replacing needed B-tree lookup indexes with a blanket columnstore design optimizes analytics at the expense of the selective operational seeks transit dispatch still requires. Caveat: if the table later grows into pure warehouse reporting with few point lookups, a columnstore (possibly in addition to or instead of some B-trees) may become appropriate—choose based on measured mix, not fashion. Operational check: capture plans for a date-range load/scan and for a route_id equality query after index creation; confirm the clustered index supports the range path and the nonclustered index is used for route seeks with acceptable key lookups.