Cannot update incremental table maybe due to recent update on dataform

Google Cloud Data Analytics
,
1

When I run the query below on Datafrom, I encounter the following error message despite using ‘even_date’ for partition elimination. Has anyone else experienced this issue and found a solution? This error started occurring today so I think there is something to do with some dataform/bigquery updates recently.

——
reason:“invalidQuery” location:“query” message:“Error validating procedure body (add OPTIONS(strict_mode=false) to suppress): Query error: Query error: Cannot query over table ‘ga_events’ without a filter over column(s) ‘event_date’ that can be used for partition elimination at [8:15]”: invalid argument

config {
    type: "incremental"
    , schema: "dp_mart"
}

pre_operations {
    declare event_date_checkpoint default (
        ${when(
            incremental()
            , `select max(event_date) from `
            + self()
            + ` where event_date >= date_sub(current_date(), interval 30 day)`
            , `select date('2023-09-01')`
        )}
    )
}

select
    session_id
    , event_date
from
    ${ref("ga_events")}
where 
    event_date >= event_date_checkpoint
    and event_name = "purchase"
2

There have been some recent updates to Dataform that may be causing this error. The specific error message indicates that BigQuery is unable to perform partition elimination on the event_date column, even though you are using the event_date_checkpoint variable to filter the query.

One possible solution is to add the OPTIONS(strict_mode=false) option to the query. However, this will disable BigQuery’s strict partition elimination checks, which might lead to inefficient queries and potentially higher costs. Use this option with caution.

If you’re considering changing the partitioning scheme, you’d need to recreate the ga_events table with the new partitioning scheme (e.g., weekly or monthly). Once the table is partitioned differently, you can then adjust your queries accordingly.

For example, if you partition by week, your query might look something like this:

…
where
event_week >= extract(week from event_date_checkpoint)
and event_name = “purchase”

However, remember that changing the partitioning scheme requires changes at the table level, not just in the query.

If you are still having trouble updating your incremental table, you can contact Google Cloud support for assistance.

1 Like
3

I didn’t change anything but somehow the error doesn’t occur anymore today. Thanks anyway.

4

How do you pass OPTIONS(strict_mode=false) using dataform? Dataform is generating the SQL

5

Please note: using OPTIONS(strict_mode=false) should be done with caution, as it can lead to inefficient queries and higher costs. Always test your changes in a safe environment before applying them to your production datasets.