I have set up a large number of tables as biglake tables connected to cloud storage uri's, and then I have set up a large number of materialised views on top of the biglake tables.
The problem I have seen is that when the "last modified date" of the biglake table is later than that of the materialised view linked to it, it fails to read from it and I get a Metadata cache unused reasons: OTHER REASON followed by the table name.
This is manageable, as I can set the biglake and materialised view refreshing to Manual to control when I refresh them to ensure that the materialised view reads from the biglake table.
But, sometimes, I get an issue where I would create the biglake table and then create the materialised view a bit later, meaning it works and uses the metadata cache successfully. For a while. And then exactly one hour after the biglake table was created its "last modified date" gets updated. And I can't find any CALL BQ.REFRESH_EXTERNAL_METADATA_CACHE calls in the project history at these timestamps. Once this happens the biglake table last modified date is later than that of the materialised view linked to it, it fails to read from it and I get a Metadata cache unused reasons: OTHER REASON followed by the table name.
Has anyone run into this issue and is there a way to prevent the biglake tables from updating their last modified dates exactly one hour after they're created without indication [no visibility in project history]?
LinkedIn
Twitter
