Bigquery | Result of ORDER BY queries cannot be partitioned by field

Question

I am using Bigquery Operator for my Airflow Task. I am passing an Sql query to operator and trying to insert result of BQ query into a BQ table. While doing this, getting below error

google.api_core.exceptions.BadRequest: 400 Result of ORDER BY queries cannot be partitioned by field 'batch_date'

Same SQL query works fine if run on BQ console. Table is partitioned on batch_date so the table name I am passing to BQ operator is table_name$batch_date

This error is coming for BQ insert job.

Any help/clue?

Answer 1

Simply remove the ORDER BY clause in the main query and it works

Answer 2

Because If the table creator CREATE TABLE with PARTITION BY batch_date is added to the CREATE TABLE.