investigate loading validation (JSON) data into bigquery

[machow]

Right now the gtfs-validator returns a JSON payload. This notebook shows how it can be turned into a table, with 1 row per notice (see tidy_notice_details table), and either..

• a column of JSON entries
• many columns

A big question though is how BigQuery likes to deal with these kinds of tables. AFAIK there are three potential options:

• many columns (with mostly NULL values)
• 1 column of nested data
• separate tables for details generated by different error codes
  • (I think bigquery queries can treat many tables like one in some cases..)

I'm guessing one of the first two makes most sense.

[hunterowens]

it's also more important to track validator results over time

so I think we might want something like

{each file} -> {check if same SHAHASH of zip compared to prior day run ->if new GTFS , save validator with run ID

[machow]

Ah--that's helpful to hear! Maybe a place to start is a validator_latest table, and then a gtfs_schedule_change table with one row per agency x change in zip (that could later be unpacked into more details).

(I can run checks on data changes in notebooks first to get a feel for how often it's changing, etc..)

[machow]

Alright, maybe there are two stages to do this in. Will edit in a bit more detail, but wanted to put here for now.

1. low-hanging fruit

Questions answered:

• validation reporting: validator is currently reporting these X serious messages
• validation sanity check: validator is currently reporting X, why does the data look like Y?
• specific aggregate validation history: your agency had N severity level "ERROR" codes on Y date

Technical:

• upload validator latest tables, so we've got latest everything
• run change detection on tables, store as a csv
  • create a table summarizing when datasets changed
  • create a table summarizing when validation changed
• upload some aggregate validation history table

2. full table history

Questions answered:

• validator report changed for X agency, what changed in their data?

Technical:

• will likely need to use an external table in bigquery
• can only use single wildcard, so need to move changed tables into their own bucket

[hunterowens]

@machow let's close this?