Bug: Long Beach Transit (170) extracted at/deleted at fields empty in gtfs_schedule_fact_daily_trips

[edasmalchi]

Describe the bug When filtering for Long Beach Transit (itp id 170), calitp_extracted_at and calitp_deleted_at are empty in gtfs_schedule_fact_daily_trips

To Reproduce Steps to reproduce the behavior:

1. Open gtfs_schedule_fact_daily_trips in Metabase and filter for itp id 170, view blank columns for calitp_extracted_at and calitp_deleted_at
2. Or using siuba: tbl.views.gtfs_schedule_fact_daily_trips() >> filter(_.calitp_itp_id == 170)

Expected behavior calitp_extracted_at and calitp_deleted_at data present, which is helpful for analysts to run faster/smaller queries on a time period of interest.

Additional context itp_ids 16, 48, 208, and 372 may have a similar issue.

[Nkdiaz]

Research Journey:

• Verified that calitp_id 170 is missing from the views.gtfs_schedule_fact_daily_trips. The calitp_extracted_at/deleted_At columns are null for all rows.
• Using the feed key I looked at the gtfs_schedule_fact_daily_feeds and verified that for this test date of 2021-10-30 the extraction was success.
• Looking at how the extracted_at/deleted_at column is populated in the gtfs_schedule_fact_daily_trips, it appears to be a subtable daily_service_trips where stg_daily_service_keyed is joined to views.gtfs_schedule_dim_trips where calitp_id_extrated at is <= service date and calitp_deleted_at > service_date.
• I verified that calitp_extracted_at/deleted_at are populated correctly in gtfs_schedule_dim_trips. Both tables have the data type of date and there are no leading/lagging white spaces I can discern from the JSON output.
• Looking further at stg_daily_service_keyed subtable, it is populated from gtfs_schedule_stg_daily_service and gtfs_schedule_dim_feeds. Verified that extracted_at/deleted_at is populated correctly in gtfs_schedule_dim_feeds but it is missing from gtfs_schedule_stg_daily_service. So The issue is stg_daily_service is not being populated with calitp_id 170
• further digging down, stg_daily_service is dependent on gtfs_schedule_stg_calendar_long, where if you query for calitp_id = 170, it returns no results. So why is it not in stg_calendar_long?
• stg_calendar_long is depended on gtfs_Schedule_type2.calendar_clean which does not have a calitp_itp_id of 170, but it is in CALENDAR DATES....
• Verified that itp_id 170 is in both calendar_dates and calendar_dates_clean but is not in calendar or calendar_clean
• looking at calendar.yml and calendar_dates.yml, both have calitp_Extracted_at as type date. The only difference I could see is that calendar_dates has name:date with type:STRING compared to calendar which has name:start_date as type: STRING and end_date as type:string
• so looking at the gc bucket gtfs-data for 2021-10-30, it appears for calitp 170, we receive calendar_dates.txt but not calendar.txt

For the purposes of this ticket I then started to research calitp_id 16, 48, 208, 372:

• Calitp_id 16 and 48 are in calendar_dates but not calendar similar to above
• calitp_id 208 and 372 are present in calendar and calendar_dates which means it is probably an unrelated issue
• I verified that for calitp_id In the gc bucket, we are receiving calendar_dates.txt rather than calendar.txt for both of them.

So it seems for calitp_id 170, 16, and 48 we are recieving calendar_dates.txt rather than calendar.txt which populates calitp_id_extracted and calitp_id_deleted at as null in gtfs_schedule_fact_daily_trips

[Nkdiaz]

After discussing at the standup, per the gtfs guidelines, agencies are only required to send either the calendar.txt or calendar_dates.txt. https://developers.google.com/transit/gtfs/reference#calendartxt so it is something we should handle on our end. So looking deeper into the select statements in the date_includion from the calendar_dates, we do no select calitp_extracted_at/deleted_at as part of the data to include so it is not present on the right side of the join which means that it does not get populated in the full join lower down. So by changing adding those to the above subtable then 170 gets populated