GTFS Schedule: We should check for file-level deletion in type 2 / SCD logic

[lauriemerrell]

In December, we changed URL 0 for SolTrans. The old feed had a feed_info.txt file, while the new feed does not. However, the pipeline did not mark the old feed_info as deleted when that change occurred.

This means that we still show that old feed_info file as active (for example it is still in feed_info_clean with calitp_deleted_at = 2099-01-01, i.e., "active").

This leads to information from that old feed appearing in the reports for SolTrans even though the rest of the feed is correct and updated, because some fields in the report are calculated from feed_info.

We need to figure out why feed_info wasn't marked as deleted in this case.

[lauriemerrell]

AFAICT, we don't check in our pipeline to see if an overall file has been deleted. gtfs_schedule_history2.merge_updates.py makes row-level comparisons for files that are present on both sides, but it looks at processed files. So if you have a file that was deleted, it has no way to know. We do mark feeds as deleted (not entirely sure where but I confirmed that for example if you remove a feed from agencies.yml it gets a calitp_deleted_at date in gtfs_schedule_dim_feeds).

We need to make merge_updates handle deleted files.

[lauriemerrell]

Affected feed is 310.0

[lauriemerrell]

@holly-g, I'm removing the needs investigation label -- I know what's going on here.

We need to add a second check either before or after merge_updates that checks the files present in a feed day over day and, if a file has been deleted, we need to populate its entries' calitp_deleted_at date. (I.e. - I don't think that investigation is needed, the cause here is known, we have a mechanism to find these.)

Affected files are saved in cal-itp-data-infrastaging:laurie_test_general.gtfs_schedule_type2_deleted_files_investigation, they are the results of the following query, run on 2022-03-09:

WITH check_next_file AS (
    SELECT *,
        LEAD(md5_hash)
            OVER (PARTITION BY calitp_itp_id, calitp_url_number, name ORDER BY calitp_extracted_at)
            AS next_md5_hash,
        DATE_ADD(calitp_extracted_at, INTERVAL 1 DAY) as next_day
    FROM `cal-itp-data-infra.gtfs_schedule_history.calitp_files_updates`
),
check_next_feed AS (
SELECT * EXCEPT(calitp_extracted_at),
    calitp_extracted_at AS next_day
FROM `cal-itp-data-infra.gtfs_schedule_history.calitp_feed_status`
),
deleted_files AS (
    SELECT *
    FROM check_next_file
    LEFT JOIN check_next_feed USING(calitp_itp_id, calitp_url_number, next_day)
    WHERE next_md5_hash IS NULL and is_extract_success = true
    ORDER BY calitp_extracted_at DESC
),
today_files AS (
    SELECT name, calitp_itp_id, calitp_url_number, true as is_present_now
    FROM `cal-itp-data-infra.gtfs_schedule_history.calitp_files_updates`
    WHERE calitp_extracted_at = '2022-03-08'
)

SELECT * 
FROM deleted_files 
LEFT JOIN today_files using (calitp_itp_id, calitp_url_number, name)
ORDER BY calitp_extracted_at DESC 

[lauriemerrell]

I wonder if a version of the logic that we use to get the latest feed for GTFS schedule would work here. Example query:

SELECT *
FROM `cal-itp-data-infra.gtfs_schedule_type2.feed_info_clean`
LEFT JOIN `cal-itp-data-infra.gtfs_schedule_history.calitp_feed_status`
    USING(calitp_itp_id, calitp_url_number, calitp_extracted_at)
WHERE calitp_itp_id = 310 and calitp_url_number = 0
ORDER BY calitp_extracted_at DESC

Here, is_latest_load shows as false for the most recent data.
If there's a file that has calitp_deleted_at = 2099-01-01 but is_latest_load is false in this query, that is something that should be marked as deleted, I think.

There may be a preferable way to get this info from a later calitp status type file, I would need to think about it more.

[lauriemerrell]

Update: see #1280 -- the proposal immediately above about is_latest_load is not right. Row-level versioning isn't going to align with feed-level extract dates unless/until we change merge_updates to do versioning at the file level. 😕

[lauriemerrell]

Another update: We need to look at is_interpolated here.... I think a lot of these cases will have is_interpolated = True and we need to investigate to what extent that should be preserved. Like, I don't know why we wouldn't be marking things as deleted in these cases, and why we would ever interpolate....

[lauriemerrell]

We should do this as part of #1259

[o-ram]

This issue was seen again in 2 separate instances when the URLs for both Santa Maria Area Transit and Anaheim Regional Transit were updated. Both agencies switched vendors from Trillium to TripShot resulting in the URL change. The new TripShot feeds do not have a feed_info.txt file and it appears that the pipeline is pulling the old Trillium feed info information instead of updating it to reflect the new feed. The issue seems to be more pronounced for Santa Maria Area Transit, where the pipeline is also pulling from the old Trillium feed for their Fares v2 data as well (the TripShot feed does not include fares data). This may be because Anaheim's name slightly changed in our pipeline while Santa Maria's remained the same.

Example # 1: Anaheim Regional Transit
Screenshot of Metabase

Screenshot of May report

Example # 2: Santa Maria Area Transit
Screenshots from Metabase


Screenshots from May report

[lauriemerrell]

Thanks @o-ram for sharing these examples, it's very helpful to know that this is happening... cc @holly-g & @edasmalchi from a reports data display perspective -- wondering whether we think that this needs to be fixed in current data structure (i.e., within the next like 6 weeks) or whether it can wait until we refactor the versioning in the schedule pipeline (could take a little while but will likely be a more robust fix.)

Tagging this against #1536, I'm actually not sure that we're going to do #1259 under the current pipeline any more

[lauriemerrell]

@evansiroky and @e-lo -- I'm wondering if we should be updating the Transit Tech Stacks Airtable information to note the seeming changes in tech stack that Olivia noted above in #1184 (comment)

[atvaccaro]

Per conversation in sprint planning, going to close this given an example of us handling file deletion properly. Since we version at the feed (aka extract) level, it's easy to see where a base64_url no longer has a file.

select *
from mart_gtfs.dim_schedule_feeds
where key in (
              '4a433f24ee4264e77a1306b4801fe238',
              '4c84830c9f86b096a26dde4df6c8a185'
    )
order by _valid_from
;

select distinct original_filename
from mart_gtfs_quality.fct_schedule_feed_files
where feed_key = '4a433f24ee4264e77a1306b4801fe238'
except
distinct
select distinct original_filename
from mart_gtfs_quality.fct_schedule_feed_files
where feed_key = '4c84830c9f86b096a26dde4df6c8a185'
;

select *
from mart_gtfs.dim_feed_info
where feed_key in (
                   '4a433f24ee4264e77a1306b4801fe238',
                   '4c84830c9f86b096a26dde4df6c8a185'
    )
;

Also closing related Schedule issues as "not planned."

#521
#1344