Dataform Incremental Partitioned Table ; Column name date is ambiguous

[olivier_l]

In GCP Dataform, the destination table of my workflow is an incremental table. For efficiency and cost reduction purposes, I want this table to be partitioned by date with partition filter requirement set to true. Therefore the following configuration and code :

config {
type: “incremental”,
uniqueKey: [“date_country_item_key”],

bigquery: {
partitionBy: “date”,
requirePartitionFilter: true,
clusterBy: [‘country’],
updatePartitionFilter: “date >= CURRENT_DATE()-4 AND date < CURRENT_DATE()”
},

schema: “ga4”,
name: “ww_item_event_counts”
}

SELECT * FROM ${ref(‘205_aggregate’)}

I am running into the following error when running the workflow incremental mode : “Query error: Column name date is ambiguous”.

The error does not happen when I remove the partition requirement from the table and suppress the "updatePartitionFilter: “date >= CURRENT_DATE()-4 AND date < CURRENT_DATE()”.

Could you help me fix the ambiguity around the date field so that I can keep the partition requirement set to TRUE ?

Please note I have checked the format of the date field in source and target table. It is properly set to a date format.

[olivier_l]

The issue is solved when adjusting the “updatePartitionFilter” as follow:

OLD: updatePartitionFilter: “date >= CURRENT_DATE()-4 AND date < CURRENT_DATE()”

NEW: updatePartitionFilter: “date BETWEEN CURRENT_DATE()-4 AND CURRENT_DATE()”

Somehow the merge statement compiled by Dataform for incremental tables is not able the detect the date field appearing twice in the “updatePartitionFilter” config. Using the “between” syntax circumvents the limitation.