Problem
Scheduled query with multiple DML statements failing with the following error:
Dataset specified in the query ('') is not consistent with Destination dataset DATASET_ID
Environment
- BigQuery
- Scheduled queries with >1 DML statement
Solution
Recreate the scheduled query and make sure that the Target/Destination dataset (--target_dataset) value is not explicitly being set.
Cause
Destination/Target dataset can only be set up for scheduled queries with one single DML statement. When two DML statements are present (for example, DELETE FROM followed by a INSERT statement), the second DML statement will not pick up the correct destination/target dataset and will throw an error.