i'm trying to define partitioned tables by date, but getting error because dagster tries to run this query:

DELETE FROM `mytable` WHERE
DATE(PARSE_DATETIME('%F %T', date)) >= '2023-08-09 00:00:00' AND DATE(PARSE_DATETIME('%F %T', date)) < '2023-08-10 00:00:00'

do you have a specific error message you can share? I can try to help debug. our test suite that runs a bunch of different partition tests against a real BQ db is looking fine, so it’s hard to say anything for certain without some more info

@jamie

Could not cast literal "2023-08-09 00:00:00" to type DATE at [2:15]

ah ok so this issue here is that you’re comparing a DATE type on the left and a TIMESTAMP type on the right and bigquery can’t automatically convert a TIMESTAMP to a DATE (annoying). So i think if you change the left of your comparison to a TIMESTAMP it should work. I think this would do it:

PARSE_TIMESTAMP('%F %T', date))

yes, but i want this to be a date-partitioned table.

i totally see where your coming from - it’s a bit tricky to do that on the backend though, and at this point would be a breaking change to modify the format of the partition key that’s included in the query, so i dont see us being able to make any changes for a while

i see