Improving BigQuery Queries: Tips for Avoiding SQL Errors
Jules Belveze (Dust) Maxime Cattet I noticed a pattern with my BigQuery query tables I mentioned yesterday where the generated SQL fails a few times due to a lack of understanding of BigQuery syntax or constraints. It'll always figure it out eventually, but a little guidance seems to fix it, so maybe that's something that's easy to fix by default. The screenshot shows how it'll try a few times with mismatched types, or by using an INTERVAL that's not supported with TIMESTAMP_SUB, but if you add a little info to the data description, it seems to get things right enough on one shot. Something like this seems to make it work on the first shot:
When writing queries, carefully note the column data types to ensure comparisons are valid. Consider BigQuery limitations for TIMESTAMP_SUB as WEEK, MONTH, YEAR intervals are not supported.