BigQuery validator detects error but Query runs succesfully

Question

UPDATE (3/25/21): Bug has been fixed and is no longer present

I am trying to create a scheduled query that deletes a BigQuery ML model.

However the DDL statement suggested in the documentation produces errors in the UI validator for no reason with a meaningless message :

DROP MODEL IF EXISTS `my_data.sample_model`

training options cannot be empty

If you ignore it and still press run it gets the job done without a problem

This statement deleted the model {project-name}:my_data.sample_model.

But you cannot turn it into a scheduled query because it simply cannot pass the validator ("Cannot schedule invalid queries") which creates a problem. What could be the reason for that error?

EDIT

Workarounds: Use either one of

EXECUTE IMMEDIATE "DROP MODEL IF EXISTS `my_data.sample_model`"

as suggested by Yun Zhang

OR

DROP TABLE IF EXISTS `my_data.sample_model`

Issue filed in Google's issue tracker

Yun Zhang Yun Zhang · Accepted Answer · 2020-09-09T16:43:52

Sounds like a bug. Could you create a bug on BigQuery here.

As a workaround for now, could you try scheduled query with dynamic SQL (to skip validation on the statement)

EXECUTE IMMEDIATE "DROP MODEL IF EXISTS `my_data.sample_model`"

BigQuery validator detects error but Query runs succesfully

1 Answers