TABLE_STORAGE_TIMELINE view details

[noak]

Hey, 
We can't find any information about this view (TABLE_STORAGE_TIMELINE), anyone can give us some more details about it? 
For more context we're trying to get some information about queries and changes in the dwh, 
We mainly use the query_history for this, but in case of a CREATE_TABLE_AS_SELECT we can't find any information in the query history about the row_count change, though we see we have some information about it in theTABLE_STORAGE_TIMELINE, so we'd like to know a bit more about the view (when is it being updated, what's its originally meant for, etc), the reason is that we see new rows there even when there's no change from time to time 

Thanks in advance! 

[ms4446]

The dwh_relations_timeline view is not a standard feature of BigQuery; it appears to be a custom or internal tool created to address specific needs around tracking changes in our data warehouse. This includes monitoring table creations, schema updates, deletions, and importantly, modifications in row counts following operations like CREATE_TABLE_AS_SELECT. These types of changes are not typically captured in the standard query_history, which might explain why you're seeing additional details in dwh_relations_timeline.

As for when this view is updated, it depends on how it was configured during its creation. The updates might be real-time, periodic, or triggered on-demand. To understand this better, including the view’s intended use, I recommend consulting with our BigQuery administrators or the data team. They can provide detailed insights into why and how the view was implemented.

To get a clearer picture of the view’s structure and its data sources, you can examine its definition directly if you have the necessary access. Running the following SQL command will show you the configuration:

SHOW CREATE VIEW `project.dataset.dwh_relations_timeline`; 

While utilizing this view can offer valuable insights into specific data warehouse activities, please be cautious with undocumented features as they can sometimes lead to unexpected behaviors or may not be supported long-term.

For a more robust solution, consider using BigQuery’s audit logs. These logs capture data definition language (DDL) operations and can be accessed through the Cloud Console or the bigquery audit command. You can filter them to focus on row count changes and other relevant events. Alternatively, implementing a custom logging mechanism or creating table snapshots could also serve as reliable methods to monitor row count changes and ensure data integrity.