In Google Cloud BigQuery, the INFORMATION_SCHEMA.TABLES view does not include the LAST_MODIFIED_TIME column. However, you can use the __TABLES_SUMMARY__ meta-table to get the last modified time of a table.
Here's an example of how you can use it:
SELECT
table_id,
TIMESTAMP_MILLIS(last_modified_time) as last_modified_time
FROM
`your_project.your_dataset.__TABLES_SUMMARY__`
This will give you a list of all tables in the dataset along with their last modified time.
Please note that __TABLES_SUMMARY__ is a dataset-level meta-table, so you would need to run this query for each dataset in your project.
If you want to monitor the updates of all tables in a project, you might need to create a script or a scheduled query that periodically runs the above query for all datasets and then sends an alert or logs the result somewhere if a table has been updated.
Also, keep in mind that __TABLES_SUMMARY__ only provides information about native tables, not views or external tables. If you need to monitor updates to views or external tables, you would need a different approach, such as using Cloud Audit Logs to monitor changes to the underlying data sources.
LinkedIn
Twitter
