Bigquery: Table not found after creation/DML commands

[ahcamachod-lore]

The issue I've been facing is related to the sudden vanishing of tables after creating them or after running a DML command. For example:

1. I create a  table in my project called `project.dataset.tbl_1`.  It is successfully created, and i can see its schema, details, etc. Then I run a INSERT statement into it, or a DELETE, or UPDATE, and the first time it runs the full statement, it recognizes the table and executes the DML command, but a few seconds after running the command, the table vanishes from the project, and it cannot be queried. I can recreate the table, and it will exist as long as I don't run a DML command to modify it. So, if I have a procedure that inserts and then updates the data (considering same schema for both tables), like:

```sql
BEGIN

Insert into `project.dataset.tbl_1`
select * from `project.dataset_1.tbl_1`;

Update `project.dataset.tbl_1` set <whatever variable> WHERE <whatever condition>;

END;
```
The output will be something like:
INSERT successful.
UPDATE failed: Reason:  table `project.dataset.tbl_1`not found.

2. All of a a sudden,  batch routines started to fail. When looking at the logs, the reason was Table not found. This is odd, because none of the tables had expiration dates, and all of them were created at the same specific location, which is EU. 

I have been struggling to understand why something like this would happen, considering that the tables are created in the same location (EU), with no expiration date. 

Has anybody faced something like this?

[caryna · Accepted solution]

Hi @ahcamachod-lore,

Welcome to Google Cloud Community!

Here are the possible solutions to resolve your issue:

• Review your Cloud Audit Logs for detailed information and to help you in identifying the possible root cause. You also need to ensure you have the required roles to view the logs.
• You need to avoid creating exact SQL query jobs simultaneously using different users to ensure this error doesn’t occur.
• Check also if you have the permission to access your project and dataset in BigQuery.

If the issue persists, please contact Google Cloud Support. When reaching out, include detailed information and relevant screenshots of the errors you’ve encountered. This will assist them in diagnosing and resolving your issue more efficiently.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.