Currently Sippy Test Analsysis relies on materialized views providing 7 and 2 day period analysis. Additionally Feature Gates utilize the the test analysis pages.

The update times for materialized views continue to increase as more tests / jobs are added. Additionally we have received feedback from use of the Feature Gates pages that they want to be able to see the job runs comprising the summarized result stored in the matviews including pass, fail and flakes as well as change the day range being evaluated. Another issues is that the period changess between 2,7 and 30+ day views of the data as we navigate from the summary to the tests analysis and then to the test results making it difficult to navigate

Enhancments for Risk Analysis are investigating moving to BQ for realtime or scheduled queries providing results similar to the test analysis materialized views.

There has been discussion about the benefits of switching existing sippy test analysis over to new BQ queries to replace the materialized views vs. developing new UI elements within component readiness.  Some of this depends on our ability to continue patching the materialized view refresh process along with the resulting design for the risk analysis enhancements. 

Another discussion is around using scheduled queries vs live queries

Scheduled Query Pros

• When we load data currently are we filtering / transforming data that would benefit from running once in a scheduled query
• Separate the filter / joins logic from the queries and perform only once
• We could have a representation of all the jobs / tests that would be available for additional queries for that period of time

Live Query Pros

• Real time results
• Possibly simplified logic though the api query would likely be larger
• Flexible time windows
• Others?

In either case we should consider paging / flexible date ranges for real time BQ queries to reduce the amount of data we query / load at a time

Creating this spike to note the relationships between these separate initiatives and track progress as well as future design considerations. A successful outcome for this spike would be to provide

• An implementation plan to either replace the matviews with BQ queries that the existing Test Report / Test Analysis sippy views can use
• Or if not feasible to swap out the backends, a plan for new views that could eventually replace the classic sippy Test Report / Analysis UI and materialized views