Add qtag_filter as a materialized column rather than a view column. This adds to the base table and also adds a backfill mechanism. The backfill mechanism runs inline with the query history maintenance task and updates a day per run starting at the most recent day and moving backwards in time. The slow update smooths out the extra cost of the update and keeps the update size relatively small to prevent unnecessary locking. With a 1 hour frequency it will take 2 days to cover all the time range that our reports usually cover and about 15 days to go back a year.
We incur an extra cost by doing this. It takes 5-20 seconds to update a day on our accounts leading to max 2 hrs of extra compute ($4.5 in serverless tasks). Once the backfill is complete the update takes <1s so is negligible once backfill is done.
We will remove the backfill task after about a month.
Checklist:
[x] I have verified that my change is functionally correct (unit tests are great).
[x] Queries against newly-added, user-facing objects can be expected to complete in 1's of seconds.
[x] My change will not have a substantial increase the user's cost to have the app installed (did you add any new tasks?).
Pull Request Template
Description
Add qtag_filter as a materialized column rather than a view column. This adds to the base table and also adds a backfill mechanism. The backfill mechanism runs inline with the query history maintenance task and updates a day per run starting at the most recent day and moving backwards in time. The slow update smooths out the extra cost of the update and keeps the update size relatively small to prevent unnecessary locking. With a 1 hour frequency it will take 2 days to cover all the time range that our reports usually cover and about 15 days to go back a year.
We incur an extra cost by doing this. It takes 5-20 seconds to update a day on our accounts leading to max 2 hrs of extra compute ($4.5 in serverless tasks). Once the backfill is complete the update takes <1s so is negligible once backfill is done.
We will remove the backfill task after about a month.
Checklist: