Generalize handling of multiple GTFS-RT feeds referencing the same GTFS schedule in fct_observed_trip

[fsalemi]

Description

We’ve identified a recurring pattern where multiple GTFS-Realtime feeds reference the same GTFS schedule feed, which can result in trips from different RT feeds resolving to the same trip_instance_key. When this happens, trip-level metrics in downstream tables (e.g., fct_observed_trip) and dashboards may unintentionally accumulate data from multiple RT feeds.

We’ve seen this scenario occur in multiple agencies (e.g., cases where agencies are transitioning systems, running parallel feeds, or exposing both customer-facing and non-customer-facing/test feeds). In at least one historical case, this was addressed via a hard-coded exclusion of a specific RT feed in the ETL/model logic feeding fct_observed_trip (Torrance Transit).

While that approach resolves the immediate issue, it does not scale well and requires maintaining one-off exclusions by agency or feed identifier.

Proposed direction

Instead of hard-coded exclusions, we should generalize the filtering logic in the formation of fct_observed_trip by:

• Systematically excluding non-customer-facing RT feeds using existing metadata/flags (e.g., customer_facing, private_dataset, or equivalent), and
• Applying this logic uniformly so that trip-level metrics are derived only from the intended customer-facing feed(s).

This would make the pipeline more robust to similar scenarios in the future and reduce the need for agency-specific exceptions.

Open questions

• Which flag(s) should be considered authoritative for determining whether an RT feed should contribute to fct_observed_trip?
• Should this filtering be enforced strictly at the fct_observed_trip layer, or earlier in the pipeline, (eg. fct_trip_updates_trip_summaries)?

Considerations / Caveats

In some cases, we intentionally define a GTFS-RT feed as a temporary or test feed for research or exploratory analysis (e.g., feeds where the private_dataset flag is set to true). Automatically excluding these feeds from fct_observed_trip could limit analysts’ ability to perform certain analyses or validations that rely on test data.

Because of this, any generalized filtering approach should consider:

• whether test / private datasets need to be accessible for analysis in specific contexts, and
• whether there should be a documented way to opt in to including these feeds (e.g., via alternative tables, query overrides, or explicit flags) when needed for research or debugging.

This suggests that the filtering logic should be systematic but flexible, rather than a hard exclusion with no escape hatch.

[vevetron]

I don't think we should work on this at all. The premise is faulty. It should be one gtfs, one set of GTFS-RT feeds. We are trying to replicate OTP or apple maps or Google.

From what i'm seeing, all the cases where we set this up previously were unnecessary. We should have removed their feeds after a point or not have supported this dual system.

What is the benefit of merging feeds? I don't see any.

We should just set up a proxy url (for the gtfs) and trick our warehouse (@evan Siroky’s idea, i was thinking copying the GTFS to a bucket and monitoring) for situations where we need this and make additional airtable entries.

Just because our warehouse can support multiple RT feed per GTFS doesn't mean we should. It increases complexity in multiple places. Better to treat it as another gtfs at that agency.

[tiffanychu90]

I'll add that I don't think we should filter out feeds using another dataset (dim_gtfs_datasets) -- I think we found a case where we did it in fct_trip_updates_trip_summaries, but I don't think we can reliably filter using any Airtable column across all the tables without opening a giant can of worms. (doing it in fct_observed_trips - what will happen when it's joined to fct_scheduled_trips, what will happen when scheduled trips is used against stop_times / daily stops to find trips in service on a given day, and so on, the list of tables impacted is truly long / on things we use a lot).

I'm curious why the quartet can't allow for the same trip_instance_key to be created? I can think of maybe dbt tests failing on uniqueness.

• Torrance with 2 RT feeds, resolving to the same trip_instance_key for schedule, wouldn't the fct_scheduled_trips + fct_observed_trips join have created the m:1 relationship we want anyway?

That all said, I'm also fairly hesitant towards using dim_gtfs_datasets columns to filter any of the daily tables, mostly because it's really hard to diagnose when an operator drops out. Is it because it's missing a feed? Is it because it's a private dataset? Has been marked to regional precursor? TDQ uses customer facing, Data Science uses analysis_name, all these columns interact in ways that are difficult to untangle.

I do think we need a solution that allows all the feeds to go through to all the mart tables, and then based on specific use cases, bring in the specific column from dim_gtfs_datasets to filter to the appropriate rows for visualization. For fct_observed_trips, bringing in int_transit_database__urls_to_gtfs_datasets In the near future, fct_observed_trips will not be "endpoint" for visualizations.

[lauriemerrell]

trip_instance_key not being unique is indeed the challenge -- the current setup is causing issues in the RT coverage dashboard, although maybe those can be addressed by adding the customer-facing flag consistently as a filter on the Metabase side (discussed this offline with @fsalemi).

If trip_instance_key isn't unique in fct_observed_trips I would think we should at least document clearly why and how to handle it. And we might need to update the uniqueness test on that column accordingly.

[lauriemerrell]

But I think @tiffanychu90's points are well-taken and with documentation updates maybe the status quo is fine. I'd still wonder about removing the ad-hoc filter for the Terrence feed just for consistency.

[fsalemi]

Thanks everyone for the discussion. Just to restate the original motivation for this ticket and where I think there’s still an open thread.

The question that originally triggered this issue was whether it is valid for a single GTFS schedule feed to be referenced by multiple GTFS-RT feeds, which then results in the same trip_instance_key being produced by more than one RT feed (e.g., customer-facing and non-customer-facing) in fct_observed_trips. (We have answered that question in this discussion now. Also see link for details!)

While digging into that question, I noticed that we’ve historically handled at least one instance of this scenario via a hard-coded exclusion of a specific RT feed in the ETL/model logic feeding fct_observed_trips (Torrance Transit). That discovery is what prompted opening this ticket, so we could discuss the pattern more deliberately rather than continuing to rely on one-off exclusions.

I agree with the direction discussed above that we can leave the overall behavior as-is and address downstream issues (e.g., RT coverage dashboards) by consistently applying the customer-facing flag at the visualization layer, as @lauriemerrell mentioned.

That said, if this is the approach we’re taking, I think it would still be good to resolve the remaining inconsistency: having all agencies and RT feeds flow through the formation of fct_observed_trips, rather than maintaining a single agency-specific hard-coded exclusion in the model definition. Keeping that one exception feels undesirable and inconsistent with the general approach we’re describing here.

Even if the broader behavior remains unchanged, cleaning up that historical special case (or at least documenting it clearly) would make the pipeline behavior easier to reason about going forward.

Reopening this issue to resolve an existing discrepancy.