tracking the history of data in bigquery

Question

I have table (my_table) in bigquery (where i m not admin) whose values changes on daily basis: i.e. my_table is some what:

item: apple quantiy: 20

tomorrow it could be:

item: apple quantiy: 10

and so on!

I want to track the history of the data lets say for coming 7 days. at day 8 (counting from today)

i want to have 7 results in my table like:

item: apple quantiy: 20 date: 27.06.2024

item: apple quantiy: 10 date: 28.06.2024

item: apple quantiy: 20 date: 29.06.2024

. . .

item: apple quantiy: 20 date: 03.07.2024

anybody having any idea how to deal with this!!!!!

Answer 1

An approach that you can use is the BigQuery change history:

SELECT
 item,
 quantity,
 _CHANGE_TYPE AS change_type,
 _CHANGE_TIMESTAMP AS change_time
FROM
 APPENDS(TABLE mydataset.mytable, TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY), CURRENT_TIMESTAMP);

Note this feature is in preview and depends on table time travel window.