Materialized table with insert / update instead of replace?

[hjelmdal]

Hi, would it be possible to include a version of your materialized table (bigquery-materialize-table-web-vitals.sql) where you don't replace data but only update data or insert data that does not match the existing data in the table ?

I'm in the situation that Bigquery events_ table only keeps data for 30 days, so I loose data whenever I replace them in my materialized table, instead of getting a good long performance data history.

Thanks a lot in advance!

[Tiggerito]

I don't have the time at the moment to upgrade the free solution.

My commercial solution for BigCommerce runs every day and replaces the last 3 days. The basic idea:

1. Determine the day to replace the data from. e.g. 3 days prior to the last date gathered
2. Delete all rows from that day
3. Insert new rows with the latest data from that day until now

Here is the SQL template I use to do that:

https://github.com/Tiggerito/GA4-Scripts/blob/e67b801b89298f4a82a1fc3aaab038802db0c6b8/bigquery-tag-rocket-report-data-incremental2.sql#L170

Things like the dataset and variables will need modifying. And the table it updates is probably different to the one for the article and Looker report.