Create a BigQuery view for the 'alerts' tables #245
Description
For a given survey, we store alerts with different schema versions in separate tables. I think this makes sense for us in terms of storage organization but is a hassle for users. We should create a view that joins those tables in order to give end users a single table (view) to work with. Currently, users have to 1) pay attention to when schema versions change and then manually connect to the new table or else they'll stop getting new data, and 2) if they want to query all of the alert data they have to join the tables themselves for every query.
BigQuery supports two types of view: Logical (computed on-the-fly for each query) and Materialized (periodically precomputed and cached). I don't know which will be better for our use cases. There will be differences in cost to us, query time for users, and possibly other things. The second link has a table comparing the features and limitations.
Our ZTF dataset currently has 5 'alerts' tables, so will benefit the most from a view. Our LVK dataset currently only has one table, so that may (or may not) be the easier one to start with. There may also be suitable tables in our testing project if it's helpful to just play around with something first (@hernandezc1 will know more about them). Note that this issue relates only to the alerts tables, named 'alerts_v*' where '*' is the schema version, and not any of the other tables that store things like classifications and metadata.