We were using client.query() (from Python API of BigQuery) to insert selected data into a table with a specific partition. But the script reported errors like:

google.api_core.exceptions.BadRequest: 400 Some rows belong to different partitions rather than destination partition

This note said it might be the cause of the incorrect date format for the partition. I checked the code but only found the partition format is correct.

The real reason is the input: the “selected data”. The data that will be inserted is from this SQL:

SELECT col1, col2, "2023-01-06" as partition_date FROM my_table;

The partition date set by the Python script bigquery.QueryJobConfig(destination="new_table$20230103") for the destination table is “2023-01-03” but the source data’s partition date is “2023-01-06”. This is why there is the above error.