I am trying to create an incremental partition table over GA4 data by exporting. I am using the “event_timestamp” column for partitioning. The problem is that this column is in the original file in INTEGER format. I put it in the final date format using “DATE(TIMESTAMP_MICROS(event_timestamp)) as event_timestamp”
The config table looks like this:
{{ config(
materialized=“incremental”,
unique_key= [“ROW”, “key”],
partition_by={
“field”: “event_timestamp”,
“data_type”: “DATE”,
“granularity”: “day”
},
require_partition_filter = true
)}}
The WHERE condition is currently done via macros:
{% macro choosing_date() %}
CAST(FORMAT_DATE(‘%g%m%d’, DATE(current_date()-2)) as INT64)
{% endmacro %}
↑ this macro we using to get right format to compare it with suffix of table.
In source im using:
tables:
- name: events_20*
so result of “choosing_date” return result “230718”
{% macro incremental_config() %}
{% if is_incremental() %}
WHERE CAST( _table_suffix as int64) >= {{choosing_date()}} and DATE(TIMESTAMP_MICROS(event_timestamp)) >= DATE(current_date()-2)
{% else %}
WHERE CAST( _table_suffix as int64) >= CAST({{var(‘starting_date’)}} as INT64)
{% endif %}
{% endmacro %}
↑Second part of macro working perfectly, we set var’starting_date’ from the date from which we want to retrieve data. The idea is that on the first run we pull all the data according to the suffix table, which we choose ourselves. Subsequent, already incremental runs should always only take data from the suffix table that has a date 2 days ago.
After trying to run incremental run, this error show up:
Cannot query over table ‘golden-union-306522.datalook.L0I_event_params’ without a filter over column(s) ‘event_timestamp’ that can be used for partition elimination compiled